VB.net code using DocumentFormat.OpenXml reads excel table rows incorrectly

Question

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.

If you check the index of the column, e.g. see get index of DataTable column with name, does it give you the expected result? I'm wondering if the column names are off by one. — Andrew Morton
– Andrew Morton, Commented Oct 4, 2017 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
– Carol, Commented Oct 4, 2017 at 13:25
Possible duplicate of C# OPEN XML: empty cells are getting skipped while getting data from EXCEL to DATATABLE — Andrew Morton
– Andrew Morton, Commented Oct 5, 2017 at 8:08

Carol · Accepted Answer · 2017-10-04 13:29:40Z

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)

answered Oct 4, 2017 at 13:29

Carol

711 gold badge1 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VB.net code using DocumentFormat.OpenXml reads excel table rows incorrectly

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related