Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JSP to Excel Conversion

 
Subhradip Podder
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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?
 
Subhradip Podder
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...........
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic