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

    Question

  • Hello everyone

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

    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.

    Thursday, April 13, 2017 11:33 PM

All replies

  • 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 6:38 AM
    Moderator
  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, April 14, 2017 12:56 PM
    Moderator
  • Thank you very much, but I need to strictly use these components:
    Imports DocumentFormat.OpenXml.Packaging
    Imports DocumentFormat.OpenXml.Spreadsheet
    Friday, April 14, 2017 9:13 PM
  • Thank you very much, your solution works very well.
    Monday, April 17, 2017 10:46 AM
  • Good to hear this :-)


    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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, April 17, 2017 12:27 PM
    Moderator