To export the dynamic contents into Microsoft Excel’s multiple sheets.
JSP:
Through JSP the data could be added to the xls by setting the content type as “vnd.ms-excel” as below
response.setContentType("application/vnd.ms-excel");
But the data could only be appended to single sheet of the spreadsheet. Formatting of data will be difficult
JavaScript:
Java script dose not have any ready to access objects for working with xls.
VBScript:
The VBScript helps in adding contents to the spreadsheet in addition to that it also help us to add the contents in different sheets in workbook with the “Excel.Application” object, which contains predefined methods. It also helps in addition of data in specific color and also in well formatted column.
The Microsoft Excel has a limitation of holding only 65536 rows and 256 columns for a single sheet in a workbook. By dynamically checking the row count and splitting the data values between multiple sheets in a workbook helps in solving the error / exception which happens while trying to add more content a single sheet in a workbook.
Dynamic data can be loaded into the Microsoft Excels by combining AJAX and VBScript.
Code Snippet
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> New Document </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">
<script type="text/vbscript">1:
2: sub savexls()
3: MsgBox "Generating Sampletest Excel in C drive"
4: Dim MyArray(15)
5: MyArray(0) = "January"
6: MyArray(1) = "February"
7: MyArray(2) = "March"
8: MyArray(3) = "April"
9: MyArray(4) = "May"
10: MyArray(5) = "June"
11: MyArray(6) = "July"
12: MyArray(7) = "August"
13: MyArray(8) = "September"
14: MyArray(9) = "October"
15: MyArray(10) = "November"
16: MyArray(11) = "December"
17: MyArray(12) = "January"
18: MyArray(13) = "February"
19: MyArray(14) = "March"
20: bookcnt=1
21: strExcelPath = "c:\Sampletest.xls"
22: On Error Resume Next
23: set objExcel = CreateObject("Excel.Application")
24: if Err.Number <> 0 then
25: Wscript.Echo "Excel application not installed."
26: Wscript.Quit
27: End if
28: On Error GoTo 0
29: Dim objSheet
30: objExcel.Workbooks.Add
31: set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
32: objSheet.Name = "TestSheet" & bookcnt
33: objSheet.Cells(1, 1).Value = "MONTHS IN YEAR"
34: objSheet.Range("A1:D1").Font.Bold = True
35: rowcnt=1
36: rowlmt=65536
37: for each x in MyArray
38: if rowcnt = rowlmt then
39: set objSheet = objExcel.ActiveWorkbook.Worksheets(bookcnt)
40: objSheet.Name = "TestSheet" & bookcnt
41: objSheet.Cells(1, 1).Value = "MONTHS IN YEAR"
42: objSheet.Range("A1:D1").Font.Bold = True
43: rowcnt=1
44: bookcnt = bookcnt + 1
45: end if
46: rowcnt=rowcnt + 1
47: objSheet.Cells(rowcnt,1).Value = x
48: next
49: objExcel.ActiveWorkbook.SaveAs strExcelPath
50: objExcel.ActiveWorkbook.Close
51: objExcel.Application.Quit
52: MsgBox "Excel Generated and placed in C drive"
53: end sub</script>
</HEAD>
<BODY onload="vbscript:savexls()">
</BODY>
</HTML>
Output:
Data were stored in different xls workbooks as per the requirement.
Workbook – Sheet1:
Workbook – Sheet2:
Benefits:
- Could be used along with any application developed in J2EE or .Net
- Much robust as it avoids server side processing.
- Allows distribution of contents on multiple workbooks based on the requirement
- Dynamic data could be added to the Excels based on the requirement.
- Easy to format content.
Related Posts
- Why Upgrade to Microsoft SQL Server 2008?
- ORALOAD Oracle Utility for Bulk Data Load
- SQL TIPS : Writing Multiple Column Subqueries
Tags: Excel Functionality in VBScript, Excel using VBScript, Export to Excel, Export to Excel in HTML
JavaScript in the form of JScript can be used to access Excel just as VBScript can.
Your Code Snippet window is very poorly formatted. The indentation is lost, and the lines are several times the height of the text. Makes the code very hard to read and understand. Then, copying the text out includes the line numbers, which is not very useful.
HI Todd
Thanks for the comment, I was working on using better code snipet, Right now i am using code snipet plugin for Windows Live Writer to post code snippets. If you know any other better way to post Code Snippet in Wordpress Blog using Windows Live Writer kindly let me know
Thanks