0

When i was reading the excel by using oledb C#, it is retrieving the data successfully. 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.

I guess Oledb converting the # char to ..

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
KarthiK
  • 9
  • 5
  • @Poiter, thanks for your reply. How to read using OpenXml, because my excel having multiple sheets in it. Also could you please post that code? – KarthiK Jun 11 '15 at 11:13
  • @user3048363, but when am reading the file through OpenXml, In the first line of code itself it gives the following error."File contains corrupted data." – KarthiK Jun 11 '15 at 11:31

2 Answers2

0

OpenXML is the best way out. Your problem will not occur only if there is "#" but will occur for any sheet which is renamed.

A3006
  • 1,051
  • 1
  • 11
  • 28
  • http://stackoverflow.com/questions/2624333/how-do-i-read-data-from-a-spreadsheet-using-the-openxml-format-sdk – A3006 Jun 11 '15 at 10:07
0

Karthik,

Could you please post your code so that we can check?

I tried with following and it worked well. 

DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

foreach (DataRow dr in dtSheet.Rows)
            {
                string sheetName = dr["TABLE_NAME"].ToString();

               cmd.CommandText = "SELECT * FROM [" + sheetName + "]";

                DataTable dt = new DataTable();
                dt.TableName = sheetName;

                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(dt);

                ds.Tables.Add(dt);
            }

Also note that the code will not read the sheet if you miss to put table (sheet) name inside brackets "[]"

A3006
  • 1,051
  • 1
  • 11
  • 28