Problem reading Excel Sheet name which contains special char

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". 

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();
}
}

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