Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
 New Feature: Community Sites:
Create your own .NET community website and start earning from Google AdSense !
It's Free !
|
Client Side Script to Export to Excel 2007
Posted Date: 25 Apr 2008 Resource Type: Articles Category: General
|
Posted By: Neeraj Saluja Member Level: Gold
Rating:  Points: 50
|
Introduction
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.
Client Side Export to Excel Approach
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.
Script for Export to Excel 2007
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.
1. fso = CreateObject("Scripting.FileSystemObject") is used to return a TextStream object that can be read from or written to. Visit http://msdn2.microsoft.com/en-us/library/z9ty6h50.aspx for more details.
2. fso.GetSpecialFolder(2) gets the complete folder path of TEMP folder. Visit http://msdn2.microsoft.com/en-us/library/a72y2t1c.aspx for more details.
3. fso.CreateTextFile(filePath).Write(sHTML) creates the file with the desired file name. Visit http://msdn2.microsoft.com/en-us/library/5t9b5c0c.aspx for more details.
4. oExcel.Workbooks.open Opens the specified file. It expects path of the file to open.
5. 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:
Step 1. 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>
Step 2. 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.
Step 3. 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.
|
Responses
|
| Author: senthil 25 Apr 2008 | Member Level: Silver Points : 0 | I was searching this code and documentation for long time. i am very happy to see this unexpectedly. Thank you very much.
| | Author: Sebastian 13 Jun 2008 | Member Level: Gold Points : 1 | This is very informative. Thanks for sharing the details.
|
|