Output Formatted Excel from DataSet

• Download source - 17.3 KB

Introduction

Very frequently, one might need to create a report in Excel. To my dismay, I found out that there is still no option to create an Excel file with some simple formatting along with formula fields etc. Hence I decided to make an assembly which accepts a DataSet and fields to be summarized. It returns an Excel which automatically adjusts width and data formatting etc.

Using the code

For displaying the data from the Jobs table of the Pubs database, all one has to write is:

Private Sub cmdShow_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles cmdShow.Click 
     Dim lObjExcel As New Excelling.OfficeExcel.ExcelGenerator
     Dim lObjDataSet As DataSet 
     Dim arr() As Integer = {2, 3}

     'GetDataSet function returns a dataset 
     lObjDataSet = GetDataSet("select * from Jobs")
     lObjExcel.SummaryColumnIndexes = arr 
     lObjExcel.CreateExcel(lObjDataSet, "B5") 
End Sub

The rest of the things are handled by the assembly Excelling.dll.

Inside out

Public Function CreateExcel(ByRef lObjDataSet As DataSet, _
                Optional ByVal lsStartCell As String = "A1")
    Dim colIndex As Integer 
    'The value will be constant throughout this function. 
    Dim STARTROWINDEX As Integer = Microsoft.VisualBasic.Right(lsStartCell, 1)
    Dim STARTCOLUMNINDEX As Integer = _
        ConvertToInteger(Microsoft.VisualBasic.Left(lsStartCell, 1)) 
    'The value will be constant throughout this function. 
    Dim RECORDCOUNT As Integer
    'The value will be constant throughout this function. 
    Dim COLUMNCOUNT As Integer
    Dim Range As Excel.Range 
    Dim lObjExcelApp As New Excel.Application()

    lObjExcelApp.SheetsInNewWorkbook = lObjDataSet.Tables.Count

    Dim lObjWorkBook As Excel.Workbook = lObjExcelApp.Workbooks.Add()
    For liTableCount As Integer = 0 To lObjDataSet.Tables.Count - 1 
        Dim lObjDataTable As System.Data.DataTable = _
                          lObjDataSet.Tables(liTableCount) 
        Dim lObjWorkSheet As Excel.Worksheet = _
                          lObjWorkBook.Sheets(liTableCount + 1) 

        RECORDCOUNT = lObjDataSet.Tables(liTableCount).Rows.Count 
        COLUMNCOUNT = lObjDataSet.Tables(liTableCount).Columns.Count 
        lObjWorkSheet.Name = lObjDataTable.TableName.ToString

        'lObjExcelApp.Visible = True
        For colIndex = 0 To COLUMNCOUNT - 1
            Range = lObjWorkSheet.Range(lObjWorkSheet.Cells(STARTROWINDEX + 1, _
                    STARTCOLUMNINDEX + colIndex), _
                    lObjWorkSheet.Cells(STARTROWINDEX + RECORDCOUNT, _
                    STARTCOLUMNINDEX + colIndex))
            Range.Value2 = CreateArray(lObjDataTable.Rows(0).Item(colIndex), _
                                       lObjDataTable, colIndex)
            If UCase(lObjDataTable.Columns(colIndex).DataType.ToString) = _
                                           "SYSTEM.DATETIME" Then 
                Range.NumberFormat = "dd/MM/yyyy" 
            ElseIf UCase(lObjDataTable.Columns(colIndex).DataType.ToString) = _
                         "SYSTEM.DECIMAL" Then
                Range.NumberFormat = lsDecimalFormat 
            End If
        Next colIndex
        ' For formating the Column Headings. 
        Dim lArrayColumnHeader(1, COLUMNCOUNT) As String
        For colIndex = 0 To COLUMNCOUNT - 1
            lArrayColumnHeader(0, colIndex) = _
                   lObjDataTable.Columns(colIndex).ColumnName.ToString
        Next

        Range = lObjWorkSheet.Range(lsStartCell, _
                lObjWorkSheet.Cells(STARTROWINDEX, _
                STARTCOLUMNINDEX + COLUMNCOUNT - 1))
        Try 
            Range.Value2 = lArrayColumnHeader
            Range.BorderAround(Excel.XlLineStyle.xlContinuous, _
                  Excel.XlBorderWeight.xlThick, _
                  Excel.XlColorIndex.xlColorIndexAutomatic)
            Range.Font.Bold = True 
        Catch ex As System.Runtime.InteropServices.COMException
        End Try

        ' For Creating Formula 
        For liarrIndex As Integer = 0 To lArrSummaryColumns.Length - 1
            Range = lObjWorkSheet.Range(lObjWorkSheet.Cells(STARTROWINDEX + _
                    RECORDCOUNT + 1, STARTCOLUMNINDEX + _
                    lArrSummaryColumns(liarrIndex)), _ 
                    lObjWorkSheet.Cells(STARTROWINDEX + RECORDCOUNT + 1, _
                    STARTCOLUMNINDEX + lArrSummaryColumns(liarrIndex)))
            Try 
                Range.Formula = "=SUM(" & ConvertToAlpha(STARTCOLUMNINDEX + _ 
                   lArrSummaryColumns(liarrIndex)) & STARTROWINDEX + 1 & _ 
                   ":" & ConvertToAlpha(STARTCOLUMNINDEX + _
                   lArrSummaryColumns(liarrIndex)) & _
                   STARTROWINDEX + RECORDCOUNT & ")"
                Range.Font.Bold = True
            Catch ex As System.Runtime.InteropServices.COMException
            End Try
        Next

        Range = lObjWorkSheet.Range(lObjWorkSheet.Cells(STARTROWINDEX + _
                RECORDCOUNT + 1, STARTCOLUMNINDEX), _
                lObjWorkSheet.Cells(STARTROWINDEX + RECORDCOUNT + 1, _
                STARTCOLUMNINDEX + COLUMNCOUNT - 1))
        Try
            Range.BorderAround(Excel.XlLineStyle.xlContinuous, _
                  Excel.XlBorderWeight.xlThick, _
                  Excel.XlColorIndex.xlColorIndexAutomatic)
        Catch ex As System.Runtime.InteropServices.COMException
        End Try
        Range = lObjWorkSheet.Range(lsStartCell, _
                lObjWorkSheet.Cells(STARTROWINDEX + RECORDCOUNT, _
                STARTCOLUMNINDEX + COLUMNCOUNT - 1))
        Try
            Range.Borders.Weight = Excel.XlBorderWeight.xlThin
            Range.BorderAround(Excel.XlLineStyle.xlContinuous, _
                  Excel.XlBorderWeight.xlThick, _
                  Excel.XlColorIndex.xlColorIndexAutomatic)
            Range.EntireColumn.AutoFit()
            Catch ex As System.Runtime.InteropServices.COMException
        End Try
    Next liTableCount

    lObjExcelApp.Visible = True
    lObjExcelApp = Nothing
End Function

I found the Generics concept to be quite useful when I wanted to pass the data in a data column as an array to Excel range. Below is the function I used for this:

Private Function CreateArray(Of CusDataType)(ByVal CusDataSample As CusDataType, _
        ByRef lObjDataTable As DataTable, _
        ByVal liColumnIndex As Integer) As CusDataType(,)
        
    Dim lArray(lObjDataTable.Rows.Count, 1) As CusDataType
    Dim liArrayIndex As Integer = 0

    For Each lObjDataRow As DataRow In lObjDataTable.Rows
        If TypeOf (lObjDataRow.Item(liColumnIndex)) _
                                    Is CusDataType Then
            lArray(liArrayIndex, 0) = _
                   lObjDataRow.Item(liColumnIndex)
        End If
        liArrayIndex = liArrayIndex + 1
    Next 
    Return lArray 
End Function