问题描述
大家好,
我想问一下如何在vb.net中使用open xml获取excel文件中的空白行
我的现有代码没有得到我的excel文件有10条记录的空白行,包括空白行。打开xml只能得到我的excel文件的9条记录,它会忽略空白行或空行。
有人可以帮助我如何获得空白记录excel。
这是我现有的代码(使用vb.net MVC并打开xml)
公共函数ReadExcel(文件为HttpPostedFileBase)作为ExcelData
昏暗的数据=新ExcelData()
'检查文件是否优秀
如果file.ContentLength< = 0那么
data.Status.Message =你上传了一个空文件
返回数据
结束如果
如果file.ContentType<> application / vnd.openxmlformats-officedocument.spreadsheetml.sheet然后
data.Status.Message =请上传2007及以上版本的有效excel文件
返回数据
结束如果
'打开excel文件
Dim workbookPart As WorkbookPart
Dim rows As List(Of Row)
尝试
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例外
data.Status.Message =无法打开文件
返回数据
结束尝试
'阅读标题
Dim headCounter As Integer
如果rows.Count> 0然后
Dim row = rows(0)
Dim cellEnumerator = GetExcelCellEnumerator(row)
对于每个项目在cellEnumerator中
Dim cell = item
Dim text = ReadExcelCell(cell,workbookPart).Trim()
data.Headers.Add(text)
下一页
headCounter = cellEnumerator.Count
结束如果
'阅读表格数据
如果rows.Count> 1然后
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 =错误
每个项目在cellEnumerator中
Dim cell = item
Dim text = ReadExcelCell(cell,workbookPart ).Trim()
如果text = Nothing或text =那么
dataRow.Add(空)
Else
dataRow.Add(text)
结束如果
下一页
如果是cellEnumerator.Count<> headCounter然后
for iCtr As Integer = cellEnumerator.Count to headCounter - 1
dataRow.Add(empty)
Next
结束如果
下一页
结束如果
返回数据
结束功能
我希望有人能帮助我。
提前谢谢你,上帝保佑。
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.
推荐答案
这篇关于如何在vb.net 2013 mvc中使用Open XML获取excel文件中的空白行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!