• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JSP to Excel Conversion

 
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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("jdbc:oracle: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");
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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...........
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic