Problem reading Excel Sheet name which contains special char

Question

when i was reading the excel by using oledb, it is retrieving the data sucessfully. But when the sheet name contains '#' , it does not reading the data.
It gives the following error.
" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

Khan Abrar Ahmed · Answer

Hi if you want to read the sheet which is containing # tag or special character then u have to rename the sheet wile reading to do this first read the sheet name and replace all the special character and then use the sheet to load the data.

Karthi keyan · Answer

Hi  The problem is not about reading sheetnames it is about reading data from the sheet which contains # in the sheet name. For example i couldn't read the data from the work sheet ABCD#EFGH.

Khan Abrar Ahmed · Answer

Try below code and change accordingly public String[] GetExcelSheetNames() { DataTable dt = null; bool flag = false; try { cn.Open(); // Get the data table containing the schema dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables null); if (dt == null) { return null; } ArrayList arrayExcelSheets = new ArrayList(); // Add the sheet name to the string array. foreach (DataRow row in dt.Rows) { flag = true; string strSheetTableName = row[TABLE_NAME].ToString(); //$$$ Updates: To remove the extra characters that appears in with SheetNames. if (strSheetTableName.EndsWith($)) { strSheetTableName = strSheetTableName.Substring(0 strSheetTableName.Length - 1); } else if (strSheetTableName.EndsWith($')) { strSheetTableName = strSheetTableName.Substring(1 strSheetTableName.Length - 3); } else flag = false; //$$$ Updates: To remove some extra sheets which are coming as Print_Area sheets if (!strSheetTableName.Contains(Print_Area) && flag) arrayExcelSheets.Add(strSheetTableName.Trim()); } String[] excelSheets = arrayExcelSheets.ToArray(typeof(string)) as string[]; return excelSheets; } catch (Exception) { throw; } finally { // Clean up. cn.Close(); } }

Rajeesh Menoth · Answer

Hi OleDB converts the following characters: . becomes # ! becomes _ ` becomes _ [ becomes ( ] becomes ) All the other characters are read correctly. Hope this helps someone. Check this ref: 1) http://stackoverflow.com/questions/823511/problem-in-reading-excel-sheet-with-special-character-in-c-sharp 2) https://social.msdn.microsoft.com/Forums/en-US/8017d345-59be-4a3a-9711-86fbf57ee0e6/how-to-read-excel-sheet-names-which-contains-special-characters?forum=exceldev

Karthi keyan · Answer

How to rename the sheet name dynamically.