Click here to Skip to main content
Click here to Skip to main content

SQL table data to Excel and open Excel on client side

, 18 Jan 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
Export SQL table data into Excel and open the excel file on the 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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

joe_j
Software Developer
United Arab Emirates United Arab Emirates
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 18 Jan 2013
Article Copyright 2013 by joe_j
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid