Read Excel 2007 file with Open XML - Identify blank cell

Topics: Developer Forum, User Forum
May 29, 2013 at 1:52 PM
Hi

I am working on a .NET 4.0 console application. I am trying to read the data from MS excel 2007 document. (file format in Xlsx)

I use the below code to load the data into datatable. It works fine if all the cells have values. But it does not work when one of the column has blank values.

Data gets shifted to left in the datatable. I will want to fill blank value for the column "Code" in the datatable.

Data in TestFile.xlsx
FULL Name PanID Period Year Code Type
Name1 1 201301 2013 1
Name2 2 201302 2014 2
Name3 3 201303 2015 3
Name4 4 201304 2016 4


WindowsBase version 4.0 and DocumentFormat.OpenXML dll reference added

using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;

public void Read2007Xlsx()
    {
        try
        {
    DataTable dt = new DataTable();
    using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(@"D:\2007\Testfile.xlsx", false))
        {
            WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
            IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
            string relationshipId = sheets.First().Id.Value;
            WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
            Worksheet workSheet = worksheetPart.Worksheet;
            SheetData sheetData = workSheet.GetFirstChild<SheetData>();
            IEnumerable<Row> rows = sheetData.Descendants<Row>();
            foreach (Cell cell in rows.ElementAt(0))
            {
                dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
            }
            foreach (Row row in rows) //this will also include your header row...
            {
                DataRow tempRow = dt.NewRow();
                for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                {
                   // tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i-1));
                    tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
                }
                dt.Rows.Add(tempRow);
            }
        }
        dt.Rows.RemoveAt(0); //...so i'm taking it out here.
        }
        catch (Exception ex)
        { 

        }

    }
    public static string GetCellValue(SpreadsheetDocument document, Cell cell)
    {
        SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
        if (cell.CellValue ==null)
        {
        return "";
        }
        string value = cell.CellValue.InnerXml;
        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
        }
        else
        {
            return value;
        }
    }
How to identify if a cell has blank value while reading Xlsx file using Open XML?

How to convert data in Xlsx file into dataset that has blank values using Open XML?

Thanks
Ashok