0

I am using the library DocumentFormat.OpenXml to read values of a table in Excel with specific column names, for example "Name", "CAS" etc. An snippet of my code is as follows:

Dim cookie1 As New HttpCookie("cn" & j + 1)
cookie1.Value = dt.Rows(j)("Name").ToString()
cookie1.Expires = DateTime.Now.AddDays(2)
Response.Cookies.Add(cookie1)

j is an integer iterating from 0 to number of columns -1. Even though the columns are explicitly named, it reads wrong values, for example in cookie cn2 instead of reading the second row of the column Name it reads the second row of columns CAS. dt is a DataTable() object. Any help to solve this would be appreciated.

Carol
  • 61
  • 1
  • 1
  • 3
  • If you check the index of the column, e.g. see [get index of DataTable column with name](https://stackoverflow.com/q/11340264/1115360), does it give you the expected result? I'm wondering if the column names are off by one. – Andrew Morton Oct 04 '17 at 11:20
  • Thank you for your reply. It seems that the problem in this case was caused because Open Xml ignores blank cells, and in the input file there are a lot of blank cells. The solution is to fill entries which should normally be blank with -1 (in my code) – Carol Oct 04 '17 at 13:25
  • Possible duplicate of [C# OPEN XML: empty cells are getting skipped while getting data from EXCEL to DATATABLE](https://stackoverflow.com/questions/36100011/c-sharp-open-xml-empty-cells-are-getting-skipped-while-getting-data-from-excel) – Andrew Morton Oct 05 '17 at 08:08

1 Answers1

0

It seems that the problem in this case was caused because Open Xml ignores blank cells, and in the input file there are a lot of blank cells. The code below:

For Each cell As Cell In row.Descendants(Of Cell)()
dt.Columns.Add(GetValue(doc, cell))
Next

considers only the populated cells, thus it puts wrong data into columns following the first blank entry. The solution is to fill entries which should normally be blank with -1 (and modify the code to consider them as blank)

Carol
  • 61
  • 1
  • 1
  • 3