aspose file tools*
The moose likes Other Open Source Projects and the fly likes JSP to Excel Conversion Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "JSP to Excel Conversion" Watch "JSP to Excel Conversion" New topic
Author

JSP to Excel Conversion

Subhradip Podder
Ranch Hand

Joined: Aug 27, 2007
Posts: 36
Hi All,

I'm writting a program of jsp to Excel conversion with Using POI.
My problem is i'm unable to put multiple data into excel,means only one line of row will come and it's repeted multiple time.like-----

001 subhradip manager
001 subhradip manager
001 subhradip manager
...........

please please help me.
Also my code like this----------

<%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFPrintSetup"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFRow"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFCell"%>
<%@ page import="java.io.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.text.*" %>

PreparedStatement ps=null;
Connection con=null;
ResultSet rs=null;
int m=0;
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection("jdbcracle:thin:@172.16.90.139:1531:PROD","apps","apps");
String f_date=request.getParameter("f_date");
String t_date=request.getParameter("t_date");

ps=con.prepareStatement("SELECT PV.VENDOR_NAME VNAME,FV.DESCRIPTION STATE,to_char(AIA.GL_DATE,'dd-mon-yyyy') GL_DATE,to_char(AIA.INVOICE_DATE,'dd-mon-yyyy') INV_DATE,AIA.INVOICE_NUM INV_NUM,AID.DISTRIBUTION_LINE_NUMBER LINE_NO,AID.AMOUNT AMOUNT, "+
"SUM(ROUND((NVL(AID.AMOUNT,0)*NVL((JIT.TAX_RATE),0)*0.01),0)) WCT FROM AP_INVOICE_DISTRIBUTIONS_ALL AID,AP_INVOICES_ALL AIA, "+
"JA_IN_TAX_CODES JIT,PO_VENDORS PV,FND_FLEX_VALUES_VL FV,GL_CODE_COMBINATIONS GLCC WHERE AIA.INVOICE_ID=AID.INVOICE_ID "+
"AND ");
.............
..............
ps.setString(1,f_date);
ps.setString(2,t_date);
rs=ps.executeQuery();

while(rs.next())
try{
HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet("new sheet");
HSSFSheet sheet1 = hwb.createSheet("second sheet");

for(int i=0;i<1;i++)
{
HSSFRow row = sheet.createRow((short)0+i);
HSSFCell cell = row.createCell((short)0); cell.setCellValue(rs.getString(1));
HSSFCell cell1 = row.createCell((short)1); cell1.setCellValue(rs.getString(2));
HSSFCell cell2 = row.createCell((short)2); cell2.setCellValue(rs.getString(3));
HSSFCell cell3 = row.createCell((short)3); cell3.setCellValue(rs.getString(4));
HSSFCell cell4 = row.createCell((short)4); cell4.setCellValue(rs.getString(5));
HSSFCell cell5 = row.createCell((short)5); cell5.setCellValue(rs.getString(6));
HSSFCell cell6 = row.createCell((short)6); cell6.setCellValue(rs.getString(7));
HSSFCell cell7 = row.createCell((short)7); cell7.setCellValue(rs.getString(8));


}

FileOutputStream fileOut = new FileOutputStream("c:\\excel\\wct.xls");
hwb.write(fileOut);
fileOut.close();
}
catch ( Exception ex )
{
}
out.println("Your excel file has been generated");
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39549
    
  27

The code for creating the sheets should not be inside of the while loop - you'll be creating a new sheet for every row of the resultset.


Since i only ever takes the value of 0, all resultset rows will be saved in the first row. You don't need a loop here. Just keep an int i that gets incremented with each resultset row.


You should almost never completely ignore exceptions, especially when performing I/O. How are you going to know if there are exception?


Ping & DNS - updated with new look and Ping home screen widget
Subhradip Podder
Ranch Hand

Joined: Aug 27, 2007
Posts: 36
Thank you Ulf Dittmer.............

I'm successful to generate excel file from jsp.

Without your help it was not possible for me.


Thank you very much again sir...........
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: JSP to Excel Conversion
 
Similar Threads
Web Page to Excel Sheet
How to save a file in client machine
Identifier Expected,Misplaced Constructs
how to implement a jsp by inputing the external excel
Issue with Download using ZipOutputStream