C# ExcelDataReader and OpenXML both skipping empty columns

269 Views Asked by At

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:

enter image description here

0

There are 0 best solutions below