I am making online examination application
using JSP and MSSQLServer 2000. In which I want to export whole
ResultSet Table of MSSQL to Microsoft Excel.
Export to a CSV file i.e, Comma-separated values file
Just put a comma(,) between the values of a row.
Give the file a .csv extension
Of course its possible to create a .xls file with
some API, I feel this is very simple.
This is a simple one. just draw your html table. then add this to your jsp code
//exporting to excel
response.setContentType("application/vnd.ms-excel");
//setting the exported file name
response.setHeader("Content-Disposition", "attachment;filename=myExcel.xls");
just that. Your page would be exported in Excel
formated. Simple one isn't it
Found the solution:
Here's code in VB script if anyone need it:
<HTML>
<HEAD>
<SCRIPT LANGUAGE=VBScript>
Dim objExcel
Sub Btn1_onclick()
call OpenWorkbook("c:\download\Excel_Test\test.xls")
End Sub
Sub OpenWorkbook(strLocation)
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = true
objExcel.Workbooks.Open strLocation
objExcel.UserControl = true
End Sub
</SCRIPT>
<TITLE>Launch Excel</Title>
</HEAD>
<BODY>
<P>Click the button to open the Excel Spreadsheet</P>
<INPUT TYPE=BUTTON NAME=Btn1 VALUE="Open Excel
File">
</BODY>
</HTML> ........
Using this you can directly call your excel file
just change your path.
More simple xample is below:
Just overlook the varables and enter a string there.
public void PrintXL() throws IOException{
java.util.Date dater = new java.util.Date();
SimpleDateFormat myDateFormat =
new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss");
String dates = myDateFormat.format(dater);
String name = "d:/overallReport/report_"+dates+".xls";
FileWriter outputFileReader
= new FileWriter(name);
PrintWriter out = new
PrintWriter(outputFileReader);
String forPrint = null;
forPrint = "<TABLE BORDER=1>";
forPrint = forPrint+"<TR><TD><TABLE
BORDER=1><TR><TD><B><CENTER><font
size=3>CONSOLIDATED REPORT</TABLE>";
forPrint = forPrint+"<TR><TD><TABLE
BORDER=1><TR><TD><B><font size=2>Template
id ="+ "</TABLE>";
forPrint = forPrint+"<TR><TD><TABLE
BORDER=1><TR><TD>Volume=<TD>Short Term
Moving Average=<TD>Long Term Moving Average=</TABLE>";
forPrint = forPrint+"<TR><TD><TABLE
BORDER=1><TR>";
forPrint = forPrint+"<TD><TABLE
BORDER=1 BGCOLOR='#A3A8AD'><TR
><TD><TABLE></TABLE>";//for date; stock
id; exchg id
forPrint = forPrint+"<TR BGCOLOR=#F7F087><TD><TABLE
BORDER=1><TR><TD><b>date<TD><b>stock
id<TD><b>Exchange ID";
int j=0;
while(j<overAll.length){
this.volMomResultObject = overAll[j].getVolumeMomentumResults();
this.fundamentalResulObject
= overAll[j].getFundamentalResult();
this.relativeResult = overAll[j].getRelativeStrengthResults();
this.testSystemResultObject
= overAll[j].getTestSystemResults();
for(int i=0; i<volMomResultObject.length;i++){
String datess = volMomResultObject[i].getDate().toString();
String stkid = volMomResultObject[i].getStockid();
String exchangeID =
volMomResultObject[i].getStockexchangeid();
forPrint = forPrint+"<tr
BGCOLOR='white'><TD>"+datess+"<TD><a
href="+stkid+".xls>"+stkid+"<TD>"+exchangeID;
}
forPrint = forPrint+"</TABLE>";
forPrint = forPrint+"</TABLE>";
forPrint = forPrint+"<TD><TABLE
BORDER=1 BGCOLOR='#A3A8AD'><TR
BGCOLOR='#A3A8AD'><TD><TABLE><TR align=center><TD
align=center><CENTER><B>Points</TABLE>";//for
points
forPrint = forPrint+"<TR
BGCOLOR='#F7F087'><TD><TABLE
BORDER=1><TR><TD><B>Vol-Mom<TD><B>RSI<TD><B>Fundamental";
for(int i=0; i<volMomResultObject.length;i++){
double volVal = volMomResultObject[i].getTotalStrength();
double fundaVal = fundamentalResulObject.getTotalPoints();
double RSIro = relativeResult[i].getOverall_Rating();
String volstkID = volMomResultObject[i].getStockid();
String fundastockID
= fundamentalResulObject.getStockid();
forPrint = forPrint+"<TR BGCOLOR='white'><TD>"+volVal+"<TD>";
Date RSIDate = relativeResult[i].getDate();
Date volDate = volMomResultObject[i].getDate();
if(RSIDate.compareTo(volDate)==1)
{
forPrint = forPrint+RSIro;
}
else{
forPrint = forPrint+"<i>NA</i>";
}
if(volstkID.equals(fundastockID)){
forPrint = forPrint+"<TD>"+fundaVal;
}
else
// System.out.println(volstkID);
forPrint = forPrint+"<TD><i>NA</i>";
}
forPrint = forPrint+"</TABLE>";
forPrint = forPrint+"</TABLE>";
forPrint = forPrint+"<TD><TABLE
BORDER=1 BGCOLOR='#A3A8AD'><TR
BGCOLOR='#A3A8AD'><TD><TABLE><TR align=center><TD
align=center><CETER><B>Test
Results</TABLE>";//for test results
forPrint = forPrint+"<TR
BGCOLOR='#F7F087'><TD><TABLE BORDER=1><TR><TD><B>%
Gain 4 Week<TD><B>Max % Gain <TD><B>Gain/Day";
forPrint = forPrint+"";
this.testSystemResultObject = overAll[j].getTestSystemResults();
System.out.print("Test
System Object Length= ");
System.out.println(testSystemResultObject.length);
for(int i=0; i<testSystemResultObject.length;i++){
double fourWeekGain =
testSystemResultObject[i].getFourweeksGain();
double maxGain = testSystemResultObject[i].getFinalGain();
double perDayGain =
testSystemResultObject[i].getGainPerDay();
forPrint = forPrint+"<TR
BGCOLOR='WHITE'><TD>" +
fourWeekGain
+
"<TD>"
+
maxGain
+
"<TD>"
+
perDayGain;
}
forPrint = forPrint+"</TABLE>";
forPrint = forPrint+"</TABLE>";
j++;
}//End While
out.print(forPrint);
out.flush();
try{
Runtime.getRuntime().exec(new
String[]{"cmd","/C",name});
}
catch(Exception e){
e.printStackTrace();
}
}
Quick Links:
Do you have
a Java Question?
Best Regards,
Java Programming Hints and Tips
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
All product names are trademarks of their respective
companies.
The site www.erpgreat.com is not affiliated with or endorsed
by any company listed at this site.
Every effort is made to ensure the content integrity.
Information used on this site is at your own risk.
The content on this site may not be reproduced
or redistributed without the express written permission of
www.erpgreat.com or the content authors.