C# - Using OpenXML how to read blank spaces on excel files

Question

I was searching for some solutions but failed to find one, currently I have a problem on reading an excel file using OpenXML. With perfect data, there won't be any problem, but with data with blanks, the columns seems to be moving to the left, producing an error saying that the index was not right since it actually moved to the left. I found a solution wherein you can place in cells in between, but when I tried it, an error saying that an object reference was not set to an instance of an object while reading the certain cell with this code (source is from the answer in here for inserting cells How do I have Open XML spreadsheet "uncollapse" cells in a spreadsheet?)

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[Int32.Parse(value)].InnerText;
    }
    else if (cell == null)
    {
        return null;
    }
    else
    {
        return value;
    }
}

any other ways wherein I can read blank cells as blank without moving the data to the left?

All help will be appreciated! :)

Thanks!

Jayant Varshney · Answer 1 · 2012-08-17T05:21:58.577

In Open XML, xml file does not contain an entry for the blank cell that's why blank cells are skipped. I faced the same problem. The only solution is apply some logic.

For Example:

When we read a cell we can get its ColumnName (A,B,C etc.) by the following code

string cellIndex = GetColumnName( objCurrentSrcCell.CellReference );

where

 public static string GetColumnName(string cellReference)
        {
            // Create a regular expression to match the column name portion of the cell name.
            Regex regex = new Regex("[A-Za-z]+");
            Match match = regex.Match(cellReference);
            return match.Value;
        }

you can store these cells in a Hashtable where key can be the cell ColumnName and value can be the object of the cell. And when writing fetch cells from the hash object serially on some basis or your logic like...

you may loop from A to Z and read the cells at particular key like

if(objHashTable.Contains(yourKey))
{
    Cell objCell = (Cell) objHashTable[yourKey];
    //Insertcell or process cell   
}
else
{
   //do process for the empty cell like you may add a new blank cell
   Cell objCell = new Cell();
   //Insert cell or process cell
}

This is the only way to work with open xml. adding a blank cell during reading is a waste of time. You can add more logic according to you
try this. this will definitely work. or if you find a better solution, do tell me Have a nice day :)

C# - Using OpenXML how to read blank spaces on excel files

1 Answers1