Reading Excel Open XML, OOXML, OpenXML is ignoring blank columns o cells

2017-04-13

Question

_{Thursday, April 13, 2017 11:33 PM}

Hello everyone

I need to read the value of columns A and E.

I also need the values of the intermediate columns.

I have searched for several solutions but they do not work for me,
this is my example code with excel 2013 and vb.net 2015.

Using documento As SpreadsheetDocument = SpreadsheetDocument.Open("d:\marcos.xlsx", False)

            workbookPart = documento.WorkbookPart
            Console.WriteLine(workbookPart.Workbook.Count)
            Console.WriteLine(workbookPart.WorksheetParts.Count)
            Dim indice As Int16 = 0

            Dim nom_hoja As String

            For indice = 0 To workbookPart.WorksheetParts.Count - 1

                nom_hoja = workbookPart.Workbook.Descendants(Of Sheet).ElementAt(indice).Name.ToString
                Console.WriteLine(nom_hoja)
                worksheetPart = GetWorksheetPartByName(workbookPart, nom_hoja)

                If Not IsNothing(worksheetPart) Then

                    worksheet = worksheetPart.Worksheet
                    For Each sheetData In worksheet.Elements(Of SheetData)

                        Console.WriteLine(sheetData.Elements(Of Row).Count)                     

                        If sheetData.Elements(Of Row).Count = 0 Then Exit For

                        Dim filas As IEnumerable(Of Row) = sheetData.Descendants(Of Row)

                        For Each fila As Row In filas

                            Dim colIndex As Int16 = 0
                            For Each celda As Cell In fila.Descendants(Of Cell)

                                If colIndex = 0 Then
                                    Console.WriteLine(GetCellValue(celda, workbookPart))
                                ElseIf colIndex = 4 Then
                                    Console.WriteLine(GetCellValue(celda, workbookPart))
                                End If
                                colIndex += 1
                            Next
                        Next

                    Next

                End If

            Next

        End Using

Works fine if all cells are with data.

All replies (5)

_{Friday, April 14, 2017 6:38 AM}

Hi MarcosPP,

According to your description, you want to read data from Excel, I suggest you use SELECT * FROM [Sheet1$] to get all data and then fill these data to Datatable, I use DataGridView to show these data, please refer to.

Code:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadDate()
    End Sub
    Private Sub LoadDate()
        Dim _filename As String = "d:\Date.xlsx"
        Dim _conn As String
        Dim dt As New DataTable
        _conn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & _filename & ";" & "Extended Properties='Excel 12.0 Xml;HDR=YES';"
        Dim _connection As OleDbConnection = New OleDbConnection(_conn)
        Dim da As OleDbDataAdapter = New OleDbDataAdapter()
        Dim _command As OleDbCommand = New OleDbCommand()
        _command.Connection = _connection
        _command.CommandText = "SELECT * FROM [Sheet1$]"
        da.SelectCommand = _command
        Try

            da.Fill(dt)
            MessageBox.Show("The import is complete!")
            Me.DataGridView1.DataSource = dt
            'Me.DataGridView1.DataMember = "sheet1"
        Catch e1 As Exception
            MessageBox.Show("Import Failed, correct Column name in the sheet!")
        End Try
End Sub

Hope it is helpful to you.

Best Regards,

Cherry Bu

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

_{Friday, April 14, 2017 12:56 PM | 1 vote}

Hello,

If you want to iterate cells via Open XML look at the free library Spreadsheet light. All of the code samples are in C# but easy enough to transpose to vb.net.

The following was C# taken from this page. It could easily be simplified but wanted to keep the code as presented from this site although I did exclude the export to xml and pushed read data to a DataGridView.

Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Spreadsheet
Imports SpreadsheetLight
Public Class Form2
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim fileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ExportToDataTableExample.xlsx")

        If Not IO.File.Exists(fileName) Then
            MessageBox.Show("Not located")
            Exit Sub
        End If

        Dim dtStrongTyping As New DataTable("ProductStrongTyping")
        Dim dtSchwarzeneggerTyping As New ProductTable("ProductSchwarzeneggerTyping")

        ' this basically makes it equivalent to ProductTable,
        ' but we're "manually" doing it in code, instead of having
        ' all this done by a "proper" class.
        dtStrongTyping.Columns.Add(New DataColumn("ProductID", GetType(Integer)))
        dtStrongTyping.Columns.Add(New DataColumn("ProductDescription", GetType(String)))
        dtStrongTyping.Columns.Add(New DataColumn("DateAdded", GetType(Date)))
        dtStrongTyping.Columns.Add(New DataColumn("Price", GetType(Decimal)))

        Dim row As Integer
        Dim prodrow As ProductRow


        Using sl As New SLDocument(fileName, "Sheet1")
            Dim stats As SLWorksheetStatistics = sl.GetWorksheetStatistics()
            Dim iStartColumnIndex As Integer = stats.StartColumnIndex

            ' NumberOfCells consist of all populated cells
            Console.WriteLine($"Start col index: {iStartColumnIndex} col count: {stats.NumberOfCells}")

            ' I'll assume that the "first" row is always the header row.
            ' Notice that the StartRowIndex returned isn't necessarily the
            ' first row of the worksheet, it's the first row that has any data.

            ' WARNING: the statistics object notes down any non-empty cells.
            ' This includes cells with say a background colour but doesn't have
            ' cell data. So if you have an empty row just after your worksheet
            ' tabular data, but the row is coloured light blue, the EndRowIndex
            ' will be one more than you need.
            ' It is suggested that you know more about the input Excel file you're
            ' using...

            ' I'm also not using any variables to store the intermediate returned
            ' cell data. This makes each code segment independent of each other,
            ' and also makes it such that it's easier for you to see what you
            ' actually have to type.

            For row = stats.StartRowIndex + 1 To stats.EndRowIndex

                dtStrongTyping.Rows.Add(
                    sl.GetCellValueAsInt32(row, iStartColumnIndex),
                    sl.GetCellValueAsString(row, iStartColumnIndex + 1),
                    sl.GetCellValueAsDateTime(row, iStartColumnIndex + 2),
                    sl.GetCellValueAsDecimal(row, iStartColumnIndex + 3))

            Next


            For row = stats.StartRowIndex + 1 To stats.EndRowIndex
                prodrow = dtSchwarzeneggerTyping.CreateNewRow()
                prodrow.ProductID = sl.GetCellValueAsInt32(row, iStartColumnIndex)
                prodrow.ProductDescription = sl.GetCellValueAsString(row, iStartColumnIndex + 1)
                prodrow.DateAdded = sl.GetCellValueAsDateTime(row, iStartColumnIndex + 2)
                prodrow.Price = sl.GetCellValueAsDecimal(row, iStartColumnIndex + 3)
                dtSchwarzeneggerTyping.Rows.Add(prodrow)
            Next
        End Using


        DataGridView1.DataSource = dtSchwarzeneggerTyping
    End Sub
End Class
Public Class ProductRow
    Inherits DataRow

    Public Property ProductID() As Integer
        Get
            Return CInt(Fix(MyBase.Item("ProductID")))
        End Get
        Set(ByVal value As Integer)
            MyBase.Item("ProductID") = value
        End Set
    End Property

    Public Property ProductDescription() As String
        Get
            Return CStr(MyBase.Item("ProductDescription"))
        End Get
        Set(ByVal value As String)
            MyBase.Item("ProductDescription") = value
        End Set
    End Property

    Public Property DateAdded() As Date
        Get
            Return CDate(MyBase.Item("DateAdded"))
        End Get
        Set(ByVal value As Date)
            MyBase.Item("DateAdded") = value
        End Set
    End Property

    Public Property Price() As Decimal
        Get
            Return CDec(MyBase.Item("Price"))
        End Get
        Set(ByVal value As Decimal)
            MyBase.Item("Price") = value
        End Set
    End Property

    Friend Sub New(ByVal builder As DataRowBuilder)
        MyBase.New(builder)
        Me.ProductID = 0
        Me.ProductDescription = String.Empty
        Me.DateAdded = Date.Now
        Me.Price = 0
    End Sub
End Class

Public Class ProductTable
    Inherits DataTable

    Public Sub New(ByVal TableName As String)
        Me.TableName = TableName
        Me.Columns.Add(New DataColumn("ProductID", GetType(Integer)))
        Me.Columns.Add(New DataColumn("ProductDescription", GetType(String)))
        Me.Columns.Add(New DataColumn("DateAdded", GetType(Date)))
        Me.Columns.Add(New DataColumn("Price", GetType(Decimal)))
    End Sub
    Public Function CreateNewRow() As ProductRow
        Return CType(NewRow(), ProductRow)
    End Function
    Protected Overrides Function GetRowType() As Type
        Return GetType(ProductRow)
    End Function
    Protected Overrides Function NewRowFromBuilder(ByVal builder As DataRowBuilder) As DataRow
        Return New ProductRow(builder)
    End Function
End Class

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

_{Friday, April 14, 2017 9:13 PM}

Thank you very much, but I need to strictly use these components:
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

_{Monday, April 17, 2017 10:46 AM}

Thank you very much, your solution works very well.

_{Monday, April 17, 2017 12:27 PM}

Good to hear this :-)

שתף באמצעות

Reading Excel Open XML, OOXML, OpenXML is ignoring blank columns o cells

Question

All replies (5)

משאבים נוספים