How to read a data row from Excel with more than 255 characters

Question

The issue is When I set up the DataSet as shown below, the default reads only 255 characters from the spreadsheet cell and places them into the table.

DataSet exData = new DataSet();
string connectionString = String.Format(ConnectionString, path);
OleDbDataAdapter ex = new OleDbDataAdapter(ExcelQuery, connectionString);
ex.Fill(exData);

I'm using Extended Properties=Excel 8.0 and Microsoft.Jet.OLEDB.4.0, there is no problem connecting to the excel sheet.

From my reading it follows that it is due to the Jet.OLEDB provider, what should I be using?

And I may be unable to update to new provider of Jet, is there any workaround? Any workaround would would be restricted on not being able to directly modify the Excel document to contain two cells or more for data over 255 chars.

Thanks.

Answer 1

Use ExcelDataReader and add reference in your project. and use below code to read Excel more than 255 columns...

FileStream stream = File.Open(strFileName, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
excelReader.Close();
return result.Tables[0];