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
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)
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