Win a copy of Emmy in the Key of Code this week in the General Computing forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Paweł Baczyński
  • Piet Souris
  • Vijitha Kumara

JSP to Excel Conversion

 
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("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: 43011
76
  • 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...........
 
Opportunity is missed by most people because it is dressed in overalls and looks like work - Edison. Tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!