Private Function fGetExcelData(ByVal filName As String) As DataSet
Dim ds As New DataSet
Dim dt As New System.Data.DataTable("dtExcel")
Dim cellvalue As String = ""
Dim j As UInteger = 0
Dim spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(filName, True)
Using (spreadSheet)
Dim sheets As IEnumerable(Of Sheet) = spreadSheet.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().Elements(Of Sheet)().Where(Function(s) s.Name = "Sheet1")
' Dim sheets As IEnumerable(Of Sheet) = spreadSheet.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().Elements(Of Sheet)()
' Dim relationshipId As String = sheets.First().Id.Value
Dim relationshipId As String = sheets.FirstOrDefault.Id.Value
Dim worksheetPart As WorksheetPart = DirectCast(spreadSheet.WorkbookPart.GetPartById(relationshipId), WorksheetPart)
Dim workSheet As Worksheet = worksheetPart.Worksheet
Dim sheetData As SheetData = workSheet.GetFirstChild(Of SheetData)()
Dim rows As IEnumerable(Of Row) = sheetData.Descendants(Of Row)()
For Each cell As Cell In rows.ElementAt(0)
dt.Columns.Add(GetCellValue(spreadSheet, cell))
Next
For Each row As Row In rows
If j <> 0 Then
'this will also include your header row...
Dim tempRow As DataRow = dt.NewRow()
cellvalue = GetCellValue(spreadSheet, row.Descendants(Of Cell)().ElementAt(0))
If cellvalue = "" Or cellvalue Is Nothing Then Continue For
For i As Integer = 0 To row.Descendants(Of Cell)().Count() - 1
tempRow(i) = GetCellValue(spreadSheet, row.Descendants(Of Cell)().ElementAt(i))
Next
dt.Rows.Add(tempRow)
End If
j = j + 1
Next
End Using
ds.Tables.Add(dt)
Return ds
End Function
Public Shared Function GetCellValue(ByVal document As SpreadsheetDocument, ByVal cell As Cell) As String
Dim stringTablePart As SharedStringTablePart = document.WorkbookPart.SharedStringTablePart
If Not cell.CellValue Is Nothing Then
Dim value As String = cell.CellValue.InnerXml
If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
Return stringTablePart.SharedStringTable.ChildElements(Int32.Parse(value)).InnerText
Else
Return value
End If
End If
Return ""
End Function
Private Function InsertSharedStringItem(ByVal text As String, ByVal shareStringPart As SharedStringTablePart) As Integer
' If the part does not contain a SharedStringTable, create one.
If (shareStringPart.SharedStringTable Is Nothing) Then
shareStringPart.SharedStringTable = New SharedStringTable
End If
Dim i As Integer = 0
' Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
For Each item As SharedStringItem In shareStringPart.SharedStringTable.Elements(Of SharedStringItem)()
If (item.InnerText = text) Then
Return i
End If
i = (i + 1)
Next
' The text does not exist in the part. Create the SharedStringItem and return its index.
shareStringPart.SharedStringTable.AppendChild(New SharedStringItem(New DocumentFormat.OpenXml.Spreadsheet.Text(text)))
shareStringPart.SharedStringTable.Save()
Return i
End Function
Private Function InsertCellInWorksheet(ByVal columnName As String, ByVal rowIndex As UInteger, ByVal worksheetPart As WorksheetPart) As Cell
Dim worksheet As Worksheet = worksheetPart.Worksheet
Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()
Dim cellReference As String = (columnName + rowIndex.ToString())
' If the worksheet does not contain a row with the specified row index, insert one.
Dim row As Row
If (sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).Count() <> 0) Then
row = sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).First()
Else
row = New Row()
row.RowIndex = rowIndex
sheetData.Append(row)
End If
' If there is not a cell with the specified column name, insert one.
If (row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = columnName + rowIndex.ToString()).Count() > 0) Then
Return row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = cellReference).First()
Else
' Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
Dim refCell As Cell = Nothing
For Each cell As Cell In row.Elements(Of Cell)()
If (String.Compare(cell.CellReference.Value, cellReference, True) > 0) Then
refCell = cell
Exit For
End If
Next
Dim newCell As Cell = New Cell
newCell.CellReference = cellReference
row.InsertBefore(newCell, refCell)
worksheet.Save()
Return newCell
End If
End Function