How to get blank row in excel file using Open XML in vb.net 2013 mvc

See more:

VB

OpenXML

MVC5

Hi to all,

I would like to ask on how could I get the blank row in excel file using open xml in vb.net
my existing code doesn't get the blank row where my excel file has 10 records including the blank row. Open xml only gets 9 records of my excel file, it ignores the blank row or null row.

Is anyone could help me on how could I get the blank record in excel.

Here's my existing code (Using vb.net MVC and open xml)





Public Function ReadExcel(file As HttpPostedFileBase) As ExcelData
Dim data = New ExcelData()

' Check if the file is excel
If file.ContentLength <= 0 Then
data.Status.Message = "You uploaded an empty file"
Return data
End If

If file.ContentType <> "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" Then
data.Status.Message = "Please upload a valid excel file of version 2007 and above"
Return data
End If

' Open the excel document
Dim workbookPart As WorkbookPart
Dim rows As List(Of Row)
Try
Dim document = SpreadsheetDocument.Open(file.InputStream, False)
workbookPart = document.WorkbookPart


Dim sheets = workbookPart.Workbook.Descendants(Of Sheet)()
Dim sheet = sheets.First()
data.SheetName = sheet.Name

Dim workSheet = DirectCast(workbookPart.GetPartById(sheet.Id), WorksheetPart).Worksheet
Dim columns = workSheet.Descendants(Of Columns)().FirstOrDefault()
data.ColumnConfigurations = columns

'Dim rowsx = sheet.Descendants(Of Row).Count

Dim sheetData = workSheet.Elements(Of SheetData)().First()
rows = sheetData.Elements(Of Row)().ToList()
Catch e As Exception
data.Status.Message = "Unable to open the file"
Return data
End Try


' Read the header
Dim headCounter As Integer
If rows.Count > 0 Then
Dim row = rows(0)

Dim cellEnumerator = GetExcelCellEnumerator(row)

For Each item In cellEnumerator
Dim cell = item
Dim text = ReadExcelCell(cell, workbookPart).Trim()
data.Headers.Add(text)
Next
headCounter = cellEnumerator.Count

End If

' Read the sheet data
If rows.Count > 1 Then
For i As Integer = 1 To rows.Count - 1
Dim dataRow = New List(Of String)()
data.DataRows.Add(dataRow)
Dim row = rows(i)
Dim cellEnumerator = GetExcelCellEnumerator(row)
Dim tagg As Boolean = False

For Each item In cellEnumerator
Dim cell = item
Dim text = ReadExcelCell(cell, workbookPart).Trim()
If text = Nothing Or text = "" Then
dataRow.Add("empty")
Else
dataRow.Add(text)
End If
Next
If cellEnumerator.Count <> headCounter Then
For iCtr As Integer = cellEnumerator.Count To headCounter - 1
dataRow.Add("empty")
Next


End If

Next
End If

Return data
End Function



I hope someone could help me on this.
Thank you in advance and God bless.