reading Excel Open XML is ignoring blank cells

akantro_4653 0
Viewed 52534

I am using the accepted solution here to convert an excel sheet into a datatable. This works fine if I have "perfect" data but if I have a blank cell in the middle of my data it seems to put the wrong data in each column.

I think this is because in the below code:

row.Descendants<Cell>().Count()

is number of populated cells (not all columns) AND:

GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));

seems to find the next populated cell (not necessarily what is in that index) so if the first column is empty and i call ElementAt(0), it returns the value in the second column.

Here is the full parsing code.

DataRow tempRow = dt.NewRow();

for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
{
    tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
    if (tempRow[i].ToString().IndexOf("Latency issues in") > -1)
    {
        Console.Write(tempRow[i].ToString());
    }
}
openxml-sdk openxml datatable excel
15 Answers

it run success with this code: 

            string filePath = "test.xlsx"//your file path 

            //Open the Excel file using ClosedXML.
            using (XLWorkbook workBook = new XLWorkbook(filePath))
            {
                //Read the first Sheet from Excel file.
                IXLWorksheet workSheet = workBook.Worksheet(1);

                //Create a new DataTable.
                DataTable dt = new DataTable();

                //Loop through the Worksheet rows.
                bool firstRow = true;
                foreach (IXLRow row in workSheet.Rows())
                {
                    //Use the first row to add columns to DataTable.
                    if (firstRow)
                    {
                        foreach (IXLCell cell in row.Cells())
                        {
                            dt.Columns.Add(cell.Value.ToString());
                        }
                        firstRow = false;
                    }
                    else
                    {

                        //Add rows to DataTable.
                        dt.Rows.Add();
                        int i = 0;
                        //for (IXLCell cell in row.Cells())
                        for (int j = 1; j <= dt.Columns.Count; j++)
                        {
                            if (string.IsNullOrEmpty(row.Cell(j).Value.ToString()))
                                dt.Rows[dt.Rows.Count - 1][i] = "";
                            else
                                dt.Rows[dt.Rows.Count - 1][i] = 
                            row.Cell(j).Value.ToString();
                            i++;
                        }
                    }
                }
            }
user_87931250

Using ClosedXML.Excel Instead of OpenXML:

    public DataTable ImportTable(DataTable dt, string FileName)
    {
        Statics.currentProgressValue = 0;
        Statics.maxProgressValue = 100;
        Statics.cancelProgress = false;
        try
        {
            bool fileExist = File.Exists(FileName);
            if (fileExist)
            {
                using (XLWorkbook workBook = new XLWorkbook(FileName))
                {
                    IXLWorksheet workSheet = workBook.Worksheet(1);

                    var rowCount = workSheet.RangeUsed().RowCount();
                    if (rowCount > 0)
                    {
                        var colCount = workSheet.Row(1).CellsUsed().Count();
                        if (dt.Columns.Count < colCount)
                            throw new Exception($"Expects at least {dt.Columns.Count} columns.");

                        //Loop through the Worksheet rows.
                        Statics.maxProgressValue = rowCount;
                        for (int i = 1; i < rowCount; i++)
                        {
                            Statics.currentProgressValue += 1;
                            dt.Rows.Add();
                            for (int j = 2; j < dt.Columns.Count; j++)
                            {
                                var cell = (workSheet.Rows().ElementAt(i).Cell(j));
                                if (!string.IsNullOrEmpty(cell.Value.ToString()))
                                    dt.Rows[i - 1][j] = cell.Value.ToString().Trim();
                                else
                                    dt.Rows[i - 1][j] = "";
                            }
                            if (Statics.cancelProgress == true)
                                break;
                        }
                    }

                    return dt;
                }
            }
        }
        catch (Exception ex)
        {
            Statics.cancelProgress = true;
            throw new Exception("Error exporting data." +
                Environment.NewLine + ex.Message);
        }
        return dt;
    }
user_68532630
Related