Missing column parsing Excel file with OpenXml

Question

I discovered that using OpenXml my columns don't always line up. For instance look at this example

This is the header row column 37 = "Exchange Only" column 38 = "Qty On Hand" column 39 = "Cost Method" column 40 = "Cost Method Amount"

Now, in the next row - the one that has actual data in both 37 and 38 are blank but it omits column 38. so my data looks like this.

column 37 = "" (should be blank) column 38 = "Set Amount" (Should be blank cause it should line up with 'Qty On Hand') column 39 = 0

Notice that it dropped out the real column / cell 38 and now my parser no longer lines up. both 37 and 38 are blank but it doesn't lose 37.

Here is some code to show how I am getting to the string array - it's really just a modified example from MSDN.

public InventoryItemLoadProxy CreateInventoryItemFromSpreadsheetRow(Row row, SharedStringTable sharedStrings)
     {
         var invItem = new InventoryItemLoadProxy();
         var theCells = row.Descendants<Cell>();

         var textValues =
             from cell in row.Descendants<Cell>()
             select(cell.CellValue == null ? string.Empty : ((cell.DataType != null
                             && cell.DataType.HasValue
                              && cell.DataType == CellValues.SharedString) ? sharedStrings.ChildElements[int.Parse(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText));

          if(textValues.Any(x => x != string.Empty))
          {
             var textArray = textValues.ToArray();


             invItem.PartNumber = textArray[0].ToStrippedPartNumber();
             invItem.DisplayPartNumber = textArray[0];
             //More parsing...
         }
    }

You can see I am saying if it's null just make it string.empty (yeah - that crazy linq statement will get refactored at some point).

Answer 1

As it turns out other people have answered this question. see reading Excel Open XML is ignoring blank cells. I used a bit more of a "brute force and ignorance" method although I may refactor once I have had a chance to unit test the code from the other answer. This serves my needs at the moment though.

private int GetIndexFromCellRef(Cell cell)
        {
            var colname = cell.CellReference.Value;
            colname = Regex.Replace(colname, "[0-9]", "");
            var indexList = new Dictionary<string, int>
                {
                    {"A", 0},
                    {"B", 1},
                    {"C", 2},
                    {"D", 3},
                    {"E", 4},
                    {"F", 5},
                    {"G", 6},
                    {"H", 7},
                    {"I", 8},
                    {"J", 9},
                    {"K", 10},
                    {"L", 11},
                    {"M", 12},
                    {"N", 13},
                    {"O", 14},
                    {"P", 15},
                    {"Q", 16},
                    {"R", 17},
                    {"S", 18},
                    {"T", 19},
                    {"U", 20},
                    {"V", 21},
                    {"W", 22},
                    {"X", 23},
                    {"Y", 24},
                    {"Z", 25},
                    {"AA", 26},
                    {"AB", 27},
                    {"AC", 28},
                    {"AD", 29},
                    {"AE", 30},
                    {"AF", 31},
                    {"AG", 32},
                    {"AH", 33},
                    {"AI", 34},
                    {"AJ", 35},
                    {"AK", 36},
                    {"AL", 37},
                    {"AM", 38},
                    {"AN", 39},
                    {"AO", 40},
                    {"AP", 41},
                    {"AQ", 42},
                    {"AR", 43},
                    {"AS", 44},
                    {"AT", 45},
                    {"AU", 46},
                    {"AV", 47},
                    {"AW", 48},
                    {"AX", 49},
                    {"AY", 50},
                    {"AZ", 51},
                    {"BA", 52},
                    {"BB", 53},
                    {"BC", 54},
                    {"BD", 55},
                    {"BE", 56},
                    {"BF", 57},
                    {"BG", 58},
                    {"BH", 59},
                    {"BI", 60},
                    {"BJ", 61},
                    {"BK", 62},
                    {"BL", 63},
                    {"BM", 64},
                    {"BN", 65},
                    {"BO", 66},
                    {"BP", 67},
                    {"BQ", 68}
                };

            if (indexList.ContainsKey(colname))
                return indexList.First(x => x.Key.Equals(colname)).Value;
            return -1;
        }