How to Detect Empty Rows Programmatically Using Open XML and SAX?

Question

I am working on an application that parses rows from a sheet in a xlsx file using Open XML with SAX. However, I was testing my implementation with a spreadsheet that has empty rows here and there. I would like to skip all the empty rows that I may find while I am looping through the rows in a sheet.

For example, I have the following spreadsheet

I opened up the spreadsheet using the Open XML Productivity tool and I found out that rows with empty cells all over have only one cell in the markup. That cell has an attribute called "s" that the other cells with cell values do not seem to have.

I was wondering if there is a way in code to find out if a given row is empty. So far what I am doing to go through the rows in the sheet is the following:

do{
if (reader.ElementType == typeof(Row))
{
    reader.ReadFirstChild();
    Type generatedType = typeBuilder.CreateType();
    object generatedObject = Activator.CreateInstance(generatedType);

    PropertyInfo[] properties = generatedType.GetProperties();

    int propertiesCounter = 0;
    // Read all the cells in the row.

    do
    {
        if (reader.ElementType == typeof(Cell))
        {
            c = (Cell)reader.LoadCurrentElement();

            if (c.DataType != null
                && c.DataType.HasValue
                && c.DataType == CellValues.SharedString
                && int.Parse(c.CellValue.InnerText) < ssTable.ChildElements.Count)
            {
                value = ssTable.ChildElements[int.Parse(c.CellValue.InnerText)].InnerText ?? string.Empty;
            }
            else
            {
                if (c.CellValue != null && c.CellValue.InnerText != null)
                {
                    value = c.CellValue.InnerText;
                }
                else
                {
                    value = string.Empty;
                }
            }
            Debug.WriteLine(value);
            properties[propertiesCounter].SetValue(generatedObject, value, null);
            propertiesCounter++;
        }
    } while (reader.ReadNextSibling());
    listObjectsCustomClasses.Add(generatedObject);
}} while (reader.Read() && reader.ElementType == typeof(Row));

Thank you very much for your kind help!

EDIT: I just found this answer in SO that explains that "The s attribute refers to a style". However, I am still interested in a way to find out programatically if a row is totally void of information using Open XML.

If a row is void (ie has no data,style,formulas etc...) there's no reason to include it in the XML file. — Panagiotis Kanavos, Oct 10 '19 at 15:40

score 0 · Answer 1 · answered Oct 10 '19 at 15:36

can you share the excel file which you are using for testing ? As per openXML, If any row is empty, then the respective row tag will not be available.

as per below data, row with index 2 is not at all generated. So you can loop through the row tags and if their attribute r is not matching with loop index , then that row is empty.

<row r="1" x14ac:dyDescent="0.35" spans="1:4">
<c r="A1" t="s">
<v>0</v>
</c>
<c r="B1" t="s">
<v>1</v>
</c>
<c r="C1" t="s">
<v>2</v>
</c>
<c r="D1" t="s">
<v>3</v>
</c>
</row>
<row r="3" x14ac:dyDescent="0.35" spans="1:4">
<c r="A3">
<v>1</v>
</c>
<c r="B3">
<v>2</v>
</c>
<c r="C3">
<v>3</v>
</c>
<c r="D3">
<v>4</v>
</c>
</row>

How to Detect Empty Rows Programmatically Using Open XML and SAX?

1 Answers1