Alternate methods for working with Excel .xlsx file

[kareninstructor]

Many struggle with various aspects of writing code to interface with Microsoft Excel. Even simple task such as reading data from a WorkSheet can be problematic or working with formatting for formulas using common methods via Excel automation or DataTables. Using third party libraries is a viable solution yet if the task are small then many veer away from purchasing a library.

With that I found a good library which works with 2007 format (does not work with .xls files) using a library SpreadSheetLight (make sure to download the help file in chm format). The only real downside to this library is all examples are in C#. With that I created a MSDN code sample which demonstrates common task for work with Excel from exporting a DataGridView (where the data source is a DataTable but the pattern will also work with a DataGridView with no data source by following the basic pattern), importing from Excel to a DataTable (follow the pattern for a List(Of T)), importing a text file into an Excel WorkSheet, reading columns with mixed data types and more).

Most important thing when using this library (get it via NuGet) is it requires a specific version (also on Nuget) of DocumentFormat.OpenXml where current stable release is 2.7.1, SpreadSheetLight uses 2.5.0. My guess is the author does not update the free version (which has zero restrictions) and only updates the paid version.

The following are just a few examples in my code sample which I update over time.

To open a close an excel file to a specific worksheet.

Public Sub Demo(ByVal pFileName As String, pWorkSheet As String)
    Using sl As New SLDocument(pFileName, pWorkSheet)

    End Using
End Sub

Get used cells in each column in the format of a letter

Public Function UsedCellsInCustomerWorkSheet(ByVal pFileName As String, pWorkSheet As String) As String()
    Using sl As New SLDocument(pFileName, pWorkSheet)
        Dim stats As SLWorksheetStatistics = sl.GetWorksheetStatistics
        Dim ColumnNames As IEnumerable(Of String) =
            Enumerable.Range(1, stats.EndColumnIndex).Select(Function(cellIndex) SLConvert.ToColumnName(cellIndex))
        Return ColumnNames.ToArray
    End Using
End Function

Get all sheet names in ordinal position (unlike OleDb A-Z sort)

Public Function SheetNames(ByVal pFileName As String) As List(Of String)
    Using sl As New SLDocument(pFileName)
        Return sl.GetSheetNames(False)
    End Using
End Function

See if a Sheet exists

Public Function SheetExists(ByVal pFileName As String, ByVal pSheetName As String) As Boolean
    Using sl As New SLDocument(pFileName)
        Return sl.GetSheetNames(False).Any(Function(sheetName) sheetName.ToLower = pSheetName.ToLower)
    End Using
End Function

Add a sheet

Public Function AddNewSheet(ByVal pFileName As String, ByVal pSheetName As String) As Boolean
    Using sl As New SLDocument(pFileName)
        If Not sl.GetSheetNames(False).Any(Function(sheetName) sheetName.ToLower = pSheetName.ToLower) Then
            sl.AddWorksheet(pSheetName)
            sl.Save()
            Return True
        Else
            Return False
        End If
    End Using
End Function

Remove a sheet following Excel rules

Public Function RemoveWorkSheet(ByVal pExcelFileName As String, ByVal pSheetName As String) As Boolean
    Using sl As New SLDocument(pExcelFileName)
        Dim workSheets = sl.GetSheetNames(False)
        If workSheets.Any(Function(sheetName) sheetName.ToLower = pSheetName.ToLower) Then
            '
            ' The current worksheet can not be renamed, we check for this and change
            ' the current worksheet if it's the current worksheet.
            '
            If workSheets.Count > 1 Then
                Dim sheet = sl.GetSheetNames.FirstOrDefault(Function(sName) sName.ToLower <> pSheetName.ToLower)
                sl.SelectWorksheet(sl.GetSheetNames.FirstOrDefault(Function(sName) sName.ToLower <> pSheetName.ToLower))
            ElseIf workSheets.Count = 1 Then
                Throw New Exception("Can not delete the sole worksheet")
            End If

            sl.DeleteWorksheet(pSheetName)
            sl.Save()

            Return True
        Else
            Return False
        End If
    End Using

End Function

Insert new row at top rather than bottom

Public Function InsertNewRow(ByVal pFileName As String, ByVal pSheetName As String, ByVal pCellValues As List(Of String)) As Boolean
    Try
        Using sl As New SLDocument(pFileName, pSheetName)
            sl.InsertRow(1, 1)
            sl.SetCellValue("A1", pCellValues(0))
            sl.SetCellValue("B1", pCellValues(1))
            sl.SetCellValue("C1", pCellValues(2))
            sl.Save()
            Return True
        End Using
    Catch ex As Exception
        theException = ex
        Return False
    End Try
End Function

Sample which sets styles

Public Function SimpleFormatting(ByVal FileName As String) As Boolean

    Using doc As New SLDocument(FileName, "Sheet1")

        Dim Style1 As SLStyle = doc.CreateStyle
        Style1.Font.FontColor = Color.Pink
        Style1.Font.Strike = False
        Style1.Font.Underline = DOS.UnderlineValues.None
        Style1.Font.Bold = True
        Style1.Font.Italic = False
        Style1.Fill.SetPattern(DOS.PatternValues.Solid, Color.Black, Color.Pink)
        Style1.Alignment.Horizontal = DOS.HorizontalAlignmentValues.Right

        doc.SetCellStyle("H2", Style1)
        doc.SetCellStyle("I2", Style1)

        doc.SetCellValue("H2", "Karen")
        doc.SetCellValue("I2", "Payne")


        Dim currencyStyle As SLStyle = doc.CreateStyle
        currencyStyle.FormatCode = "$#,##0.000"

        doc.SetCellValue("H3", 100.3)
        doc.SetCellValue("I3", 200.5)
        doc.SetCellStyle("H3",currencyStyle)
        doc.SetCellStyle("I3", currencyStyle)

        Dim dateStyle As SLStyle = doc.CreateStyle
        dateStyle.FormatCode = "mm-dd-yyyy"


        Dim dictDates As New Dictionary(Of String, Date) From
            {
                {"H4", #1/1/2017#},
                {"H5", #1/2/2017#},
                {"H6", #1/3/2017#},
                {"H7", #1/4/2017#}
            }

        For Each dateItem In dictDates
            If doc.SetCellValue(dateItem.Key, dateItem.Value) Then
                doc.SetCellStyle(dateItem.Key, dateStyle)
                doc.SetColumnWidth(dateItem.Key, 12)
            End If

        Next

        doc.Save()

    End Using

    Return True

End Function

If you are a C# developer, there are plenty of code samples on the SpreadSheetLight site.