Export to Excel is one of the most important features that business users request. This requirement is quite frequently observed in reporting world. The requirement could be that we click on any button/link and after processing the request on server open up an Excel with desired data on the client machine. On the other hand, the requirement could be that we first display the data in the desired format to the users on the client tool like IE for the web application, and on clicking any link/button, we export that data to Excel.
Well, honestly speaking this is less of an article or code snippet but actually an attempt to put across the solution for a situation that troubled me for quite some time. I am using client side VBScript to Export to Excel mechanism to export the data of the DataGrid. The problem started when our clients upgraded their machines from Office 2003 to Office 2007 and my standard script stopped working. Here we are going to briefly understand the server side and client approaches for Export to Excel and then we will focus on the VBScript that can be used to Export DataGrid data into Excel.
Depending on the requirement, we can process the request at the Server and send the response back as Excel data, Or, we can first render the data and then export the same on any user event. Let us have brief overview of both these approaches.
In this type of approach we transform the data using server side code and send the excel to the client. There are several ways to do it, such as:
- Simply specify the ContentType property of Response Object to specify the type of file the client can expect. We can specify the file name as well. User will get the data in the Excel Sheet.
- Use Components like OWC to create an Excel object, fill the desired data in desired format and send that object to the client side.
If you are more interested click here to see the MS Knowledge Base article for the same.
In this type of approach we first render the data in desired format on the client like IE of the end user's machine and then Export the same, with full formatting like font, color etc, to Excel. Important point to note here is that client side script can be used to Export the rendered data to Excel only if the client machines has got MS Excel installed on their machines. If the client machines do not have the Excel installed, we should use the server side method to Export to Excel.
With Client Side Script, we can do this using ActiveX Components through any client side script. With this approach we can guarantee that we are exporting only the desired data which the user has already reviewed, or even probably processed on the client side itself, thus ensuring that user would get what it wants. And for such requirements client side Export to Excel is best suited and we are actually saving a Server Side hit when we use client side script to export the data into excel.
Well, In this article we are going to focus only on Client Side Script to Export to Excel the DataGrid/GridView data which is already been displayed on IE.
As discussed we can use client side script to export the rendered tabular data into Excel. We are now going to see the same using VBScript. Well, we need to use two different scripts here as per the Office version we have on the client machines. If the clients are using Office 2003 or prior versions of Office, then we can safely use the first script. But if the client machines have upgraded to Office 2007 we have to use the second script for achieving the same.
Let us first have a look at the script serves our purpose and then we will get into its details. So here is the VBScript for the same.
<script language="vbscript">
Function ExportToExcel2003(objToExport)
Dim sHTML, oExcel, oBook
sHTML = document.all.item(objToExport).outerhtml
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
oBook.HTMLProject.HTMLProjectItems("Sheet1").Text = sHTML
oBook.HTMLProject.RefreshDocument
oExcel.Visible = true
oExcel.UserControl = true
End Function
</script>
The script above is pretty straightforward and surely most of us must have used it in one or the other application. In the script, we just need to provide the HTML Object which has been rendered on the screen to the function and the same will be exported to the Excel using the Office which is already present on client machines. Simply put, we first create an ActiveX object of Excel.Application then add a Workbook to it. Then to the first WorkSheet, i.e. Sheet1, of the workbook, we place the HTML contents which the Excel application can recognize using HTMLProject and HTML ProjectItems. Excel thus parses that HTML and renders on "Sheet1" . An important thing to note here would be that it is not actually the Excel Binary data file which users will be seeing in the Excel, it is the HTML data which is parsed and rendered by Excel Application.
Well, the HTMLProject is no longer supported in Office 2007, and with that our script mentioned above gets into trouble. With Office 2007, we get the error at the line oBook.HTMLProject.HTMLProjectItems("Sheet1").Text = sHTML with the script error message "This method or property is no longer supported in this version of Excel.".
Well after struggling with this issue for quite some time, I got one of the alternative for the same. Let us have a look at it.
The script that we are going to use for Export To Excel when users have upgraded to Office 2007 is as follows. Let us first look at the script and then we will see how it is working for us.
<script language="vbscript">
Function Export(objToExport)
ON ERROR RESUME NEXT
DIM sHTML, oExcel, fso, filePath
sHTML = document.all(objToExport).outerHTML
SET fso = CreateObject("Scripting.FileSystemObject")
filePath = fso.GetSpecialFolder(2) & "\MyExportedExcel.xls"
fso.CreateTextFile(filePath).Write(sHTML)
DIM i
SET i = 0
DO WHILE err.number > 0
err.Clear()
filePath = fso.GetSpecialFolder(2) & "\MyExportedExcel" & i & ".xls"
i = i + 1
LOOP
SET oExcel = CreateObject("Excel.Application")
IF err.number>0 OR oExcel =NULL THEN
msgbox("You need to have Excel Installed and Active-X Components Enabled on your System.")
EXIT FUNCTION
END IF
oExcel.Workbooks.open(filePath)
oExcel.Workbooks(1).WorkSheets(1).Name = "My Excel Data"
oExcel.Visible = true
Set fso = Nothing
End Function
</script>
Believe me it is not as complex as it looks. Let us see what we are doing here. In nutshell, the above code creates a file with filename MyExportedExcel.xls and saving the same in TEMP folder of Windows and then opens and serves the same to the user. Now let us get into details of important code pieces of this script.
fso = CreateObject("Scripting.FileSystemObject") is used to return a TextStream object that can be read from or written to. Click here for more details.
fso.GetSpecialFolder(2) gets the complete folder path of TEMP folder. Click here for more details.
fso.CreateTextFile(filePath).Write(sHTML) creates the file with the desired file name. Click here for more details.
oExcel.Workbooks.open Opens the specified file. It expects path of the file to open.
- The
DO WHILE Loop. If the user has open one instance of the file but still clicks once again for Export, then CreateTextFile Errors out. To avoid such issues, we first see if we can create and save a new file; If not, then we append the name of the file with number and then save it. We store the reference of the name in the variable filepath and use it in later part of the code. We repeatedly try the same in the loop to ensure that even if user has opened several instance including some with number as part of its name, then also it should not error out.Well, this mechanism should work for prior versions of Excel as well. This script has been tested on machines where the machines were earlier having Office 2003 and later on upgraded to Office 2007. It is highly advised to test the script on the desired machines on which office 2007 is freshly installed.
Key Point to solution for Export to Excel 2007
Key Point to this solution is the file with the extension .xls. Well, if we provide the filename with the extension .xlsx it will not work. To test this out try something like this:
- Create a simple text file with the following contents
<table>
<tr>
<td>A1</td><td>A2</td>
</tr>
<tr>
<td>B1</td><td>B2</td>
</tr>
</table>
- Save this file by the name Sample.xls and double click to open in Office 2007. You may get the message, select yes and then it should open up with two rows of data.
- Now Rename this file to Sample.xlsx and double click to open in Office 2007. It should error out saying that File Format or File Extension is not valid, and thus could not open.
Export to Excel is quite a common requirement. Analyze and see whether Server Side or Client Side approach suits your requirement. I am sure that the Script for Excel 2007 mentioned above is just one of the solution, there could be many more. The script for Excel 2007 is fully dependent on the key point mentioned above, i.e., it all depends on the extension of the file with which we save the file in the TEMP folder of the client machine, so be careful while using it.
Please spare some time to rate and provide feedback about this article. Your couple of minutes can help in enhancing the quality of this article.
If interested, Click here to view all my published articles.
