vb.net - Include empty spaces when selecting from row in Open XML -

my excel looks this

    b    c    d       1    2    3 

i use this,

dim row documentformat.openxml.spreadsheet.row = sheetdata.descendants(of documentformat.openxml.spreadsheet.row)().firstordefault(function(y) y.rowindex.value = 1) 

i 3 cells (b,c,d) in result. how include blank spaces?

excel file contains cells filled addresses. empty cells "virtual".

you can check address cells, "missing" cells . translate address (which in "a1" style) number index, can use function (credit: codeproject article: read , write microsoft excel open xml sdk):

dim regexcolname = new regex("[a-za-z]+", regexoptions.compiled)  private function convertcellreferencetonumber(cellreference string) integer     dim colletters = regexcolname.match(cellreference).value.tochararray()     array.reverse(colletters)      dim convertedvalue = asc(colletters(0)) - 65      = 1 colletters.length - 1         dim current = asc(colletters(i)) - 64         convertedvalue += current * math.pow(26, i)     next      return convertedvalue end function 

with function can simulate empty cells:

dim row row = sheetdata.descendants(of row)().firstordefault(function(y) y.rowindex.value = 2)  dim cells = row.descendants(of cell).todictionary(     function(cell) convertcellreferencetonumber(cell.cellreference),     function(cell) cell)  = 0 cells.keys.max()     dim c cell     if (cells.trygetvalue(i, c))         console.writeline(c.cellvalue) 'need hanle special values      else         console.writeline("empty")     end if next