SQL table data to Excel and open Excel on client side

Introduction

I have come across many concerns and queries related to exporting SQL data to Excel, or opening up an Excel file on the client machine etc., when Excel isn't installed on the server. Well there are a lot of answers on the web, but just thought of sharing a really simple piece.

Background 

Took me some time to get this and the most important part OfficeOpenXML which is part of ExcelPackage. and you can download the dll from here.  

You can do a whole load of Excel stuffs with the above, however, these are not explained in this. 

Using the code 

After you download the dll, drop the contents of it into your bin folder in your ASPX website. Well, you would actually need to paste two files from the into your bin folder:- EPPlus.dll and EPPlus.xml.

And that's it. Next put down this code. This code assumes that you have a table with data in an SQL server and you have all the server connections set out in your webconfig. 

Imports OfficeOpenXml 

Then the below code as a sub or function

Sub getExcel(ByVal filename As String, ByVal data As System.Data.DataTable)

    Using excelPackage As New ExcelPackage()

        Dim ws As ExcelWorksheet = excelPackage.Workbook.Worksheets.Add("MYEXCEL")

        If data.Columns.Count <= 0 Then
            ' Label1.Text = "No Records"
        Else
            For l As Integer = 1 To data.Columns.Count - 1
                ws.Cells(1, l).Value = HttpUtility.HtmlDecode(data.Columns(l).ToString)
            Next

            Using rng As ExcelRange = ws.Cells(1, 1, 1, data.Columns.Count - 1) 
                rng.Style.Font.Bold = True
                rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid
                rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(192, 192, 192))
            End Using

            Dim totalRecords As Integer = data.Rows.Count

            For r As Integer = 0 To totalRecords - 1
                For s As Integer = 1 To data.Columns.Count - 1
                    ws.Cells(r + 2, s).Value = HttpUtility.HtmlDecode(data.Rows(r)(s).ToString)
                Next
            Next
            excelPackage.SaveAs(New FileInfo(filename))
            excelPackage.Dispose()
        End If
    End Using
End Sub  

In the above code, the file name is passed later and the entire data from the SQL is passed in as a datatable you can see the part of "rng" that is to choose the header of the excel file and give it a grey fill colour. 

Now the below code passes all the data into the above code, saves it in your server and ask the client (user) with an option to "Save" or "Open".

Sub createexcel()
    Dim sqlConn As SqlConnection
    Dim sqlComm As SqlCommand
    Dim dt As New DataTable

    Try

        sqlConn = New SqlConnection(constr)

    sqlComm = New SqlCommand("SELECT * from YOURTABLENAME", sqlConn)
    Dim adapter As SqlDataAdapter = New SqlDataAdapter(sqlComm)
    adapter.Fill(dt)
    Dim filePath As String = Server.MapPath("~/temp/")
    Dim newTempFile As String = "MYEXCEL.xlsx"
    Dim myfile As New FileInfo(filePath & newTempFile)
    If myfile.Exists Then
        myfile.Delete()
    End If
    myfile = Nothing
     getExcel(filePath + newTempFile, dt) 'creates the excel
        'below code opens up the excel on the cient and then delete it from the server
    Response.Clear()
    Response.ClearHeaders()
    Response.AddHeader("content-disposition", "attachment; filename=" + newTempFile)
    Response.AddHeader("content-length", New FileInfo(filePath + newTempFile).Length.ToString())
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    Response.WriteFile(Server.MapPath("~/temp/") + newTempFile)
    Response.Flush()
    File.Delete(Server.MapPath("~/temp/") + newTempFile)
    Response.End()
    Catch ex As Exception
        Label5.Text = ex.Message
    End Try

End Sub  

Remember to create a "temp" folder in your  application path in the server and also note that the code above would open up a XLSX file and not a XLS file. 

Hope the above code serves useful. 

History 

1st note on 18-Jan-2013