Exporting data to Excel Part 1

There are several ways to export to excel. The most primitive way is to use vbscript to generate call excel and feed excel directly. The example below is done in ASP classic but can be easily converted to ASP.Net.

To begin, create a button and add the event on click to trigger it.

The link can be something like this:

<a href='#' OnClick='GenerateExcel()'>Export to Excel</a>

The GenerateExcel is the vbscript which you can define as follows:

<SCRIPT LANGUAGE="VBScript">
Sub GenerateExcel()
On Error Resume Next

...

End Sub
</SCRIPT>

Next is to create a excel object as follows:

 Set Excel = CreateObject("Excel.Application")
 Set ExcelWBk = Excel.Workbooks.Add
           Set ExcelWS = ExcelWBk.Worksheets(1)

 Excel.visible = True
 ExcelWS.name = "Data"

Assuming that you are populating from a resultset, you can then start looping through the result set as follows:

<%
 row = 2
        oRs.MoveFirst
' --------------------------
      For i = 0 To oRs.Fields.Count - 1 %>
          ExcelWS.Cells(1, <% =(i + 1) %>) = "<% =oRs.Fields(i).Name %>"
<%    Next
' --------------------------
        Do While Not oRs.EOF
            For i = 0 To oRs.Fields.Count - 1 %>
'                ExcelWS.Cells(1, <% =(i + 1) %>) = "<% =oRs.Fields(i).Name %>"
                ExcelWS.Cells(<% =row %>, <% =(i + 1) %>) = "<% =oRs.Fields(i).Value %>"
<%            Next
            row = row + 1
            oRs.MoveNext
        Loop
%>

Some additional that can be done is to check on the number of rows in the Result set before looping.

This approach has many flaws and such as:

1. It only runs on Windows and in IE with excel installed.
2. You need to open up the security in your IE to allow executing of unsigned script (recommended to set it to prompt).
3. It will create a huge script on the browser (so will use memory on the client).
4. It will generate the script irrespecive if the script is used again.

I will cover other approaches in the next article but I am sure you will find this approach else where to.