Get Number of active data columns per Row in Excel using Open XML

Question

Using Interop.Excel I use:

CInt(oExcel.ActiveSheet.Cells(1, oExcel.ActiveSheet.Columns.Count).End(Microsoft.Office.Interop.Excel.XlDirection.xlToLeft).Column()) 

To get the number of active columns on row 1.

But how does one implement this in openxml?

Answer 1

I'm not very familiar with Interop.Excel but that code looks like you're getting the last active cell rather than the number of active cells in the sheet (although I could be way off the mark).

I think you can achieve either using OpenXML with something like this:

Public Shared Sub GetActiveColumns(filename As String, rowNumber As Integer)
    'open the document in read-only mode
    Using spreadsheetDocument__1 As SpreadsheetDocument = SpreadsheetDocument.Open(filename, False)
        'get the workbookpart
        Dim workbookPart As WorkbookPart = spreadsheetDocument__1.WorkbookPart

        'get the correct sheet from the workbookview
        Dim workbookView As WorkbookView = workbookPart.Workbook.Descendants(Of WorkbookView)().First()
        Dim index As Integer = If(workbookView.ActiveTab IsNot Nothing AndAlso workbookView.ActiveTab.HasValue, CInt(workbookView.ActiveTab.Value), 0)
        Dim sheet As Sheet = workbookPart.Workbook.Descendants(Of Sheet)().ElementAt(index)

        If sheet IsNot Nothing Then
            'get the corresponding worksheetpart
            Dim worksheetPart As WorksheetPart = TryCast(workbookPart.GetPartById(sheet.Id), WorksheetPart)

            'get the row
            Dim row As Row = worksheetPart.Worksheet.Descendants(Of Row)().Where(Function(r) r.RowIndex = rowNumber).FirstOrDefault()

            'get the last cell
            Dim activeCells As IEnumerable(Of Cell) = row.Descendants(Of Cell)().Where(Function(c) Not String.IsNullOrEmpty(c.InnerText))

            Dim cell As Cell = activeCells.LastOrDefault()
            Console.WriteLine("The last cell is {0} ", cell.CellReference)
            Console.WriteLine("There are {0} cells with data in them", activeCells.Count())
        End If
    End Using
End Sub

Given the following sheet

the output from the above code is when passing 1 as the rowNumber is:

The last cell is F1
There are 4 cells with data in them

The CellReference is the reference you would use in a formula (e.g. A1) so you might need to parse that if you want the column as a number. The code also assumes the cells are in order which I'm pretty certain Excel ensures although I don't think the OpenXML schema mandates it. If this causes an issue it could be fixed by iterating the cells and keeping track of the largest column.

Note: the VB might not be idiomatic as I used the Telerik Converter to convert it from C# to VB.