Exporting to Excel in JSP

Exporting to Excel in JSP

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();
   }

}

Return to : 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.