I'm trying to read data from an Excel file into a datatable. Some of the cells in the Excel may be empty.
Both the libraries are skipping empty cells and hence the order in datatable is wrong. For example, if the Excel is like this:
Name Contact Gender A M B C1 F C M
The result in datatable is:
Name => A
Contact => M
whereas it should be:
Name => A
Contact =>
Gender => M
Here is the code:
ExcelDataReader
using (var stream = new FileStream(filePath, FileMode.Open))
{
if (extension == ".xls")
reader = ExcelReaderFactory.CreateBinaryReader(stream);
else
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet ds = new DataSet();
ds = reader.AsDataSet();
reader.Close();
if (ds != null && ds.Tables.Count > 0)
{
return ds.Tables[0];
}
}
OpenXML
public static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
string value = cell.CellValue.InnerXml;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
return stringTablePart.SharedStringTable.ChildElements[int.Parse(value)].InnerText;
}
else
{
return value;
}
}
There are few fixes I found but none of them worked for me.
A sample Excel is: