File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JSP and the fly likes how to extract data to an xl sheet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Java » JSP
Bookmark "how to extract data to an xl sheet" Watch "how to extract data to an xl sheet" New topic
Author

how to extract data to an xl sheet

Abhilash Boinapally
Greenhorn

Joined: Jun 13, 2006
Posts: 8
how to extract data from web pages which is in tabular form to an xl sheet

like data is in the form of tabular form
<table>
<tr>
name
</tr>
like this hoe to store that name data into xl sheet programatically on clicking some button
[ June 26, 2006: Message edited by: Bear Bibeault ]
Avneet Singh
Ranch Hand

Joined: Apr 25, 2006
Posts: 51
Hey
I hope you can figure out how to read data from the table on the webpage.

Once you are done with the reading part below is the approach for writing an excel sheet

just read the data from the web page and write it on the excel sheet by setting the content type to ms-excel eg - response.setContentType("application/vnd.ms-excel")

Now to write it on the excel you have two options -
1. do it like u write a table dynamically in jsp, but in this case the format of excel sheet will not be in your control. trust me this is easy but of no good.

2. Use Apache poi project for excel sheet . download the api give it classpath and then use the api in the servlet. Why servlet because jsp will not allow you to write the data on the excel sheet even though it wont give an error. Writing a servlet wont be tough.

below is the eg. for writing excel using servlet -

import java.io.* ;
import javax.servlet.* ;
import javax.servlet.http.* ;
import org.apache.poi.hssf.usermodel.* ;
import org.apache.poi.hssf.dev.* ;
import org.apache.poi.hssf.util.* ;


public class ExcelFile extends HttpServlet{

public void doPost(HttpServletRequest request , HttpServletResponse response) throws ServletException, IOException{

response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition","attachment;filename=Web_Transact_Report.xls");
System.out.println("Going to create Excel file");
int rownum= 0 ;
HSSFWorkbook wb= new HSSFWorkbook();
HSSFSheet sheet= wb.createSheet("Transaction Report");
HSSFSheet sheet1= wb.createSheet("Net Banking");
System.out.println("Going to create rows");
HSSFRow row = sheet.createRow((short)rownum);
rownum++;
HSSFCellStyle headerStyle= wb.createCellStyle();
HSSFFont headerfont= wb.createFont();
headerfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerfont.setColor((short)9);
headerfont.setFontName("Arial");
headerStyle.setFont(headerfont);
headerStyle.setFillBackgroundColor((short)18);
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFCellcel ;
cel = row.createCell((short)0);
cel.setCellValue("Service");
cel.setCellStyle(headerStyle);
cel = row.createCell((short)1 );
cel.setCellValue("TXN ID");
cel.setCellStyle(headerStyle);
System.out.println("writing data to response");
wb.write(response.getOutputStream());
}
Abhilash Boinapally
Greenhorn

Joined: Jun 13, 2006
Posts: 8
Thankz for ur reply since my application is in jsp i am doing in jsp,i am getting the follwing error when i am running this code

<%@page import="java.io.*"%>
<%@page import="java.sql.*"%>
<%@page import="org.apache.poi.hssf.usermodel.*"%>
<%@page import="org.apache.poi.hssf.dev.*"%>
<%@page import="org.apache.poi.hssf.util.*"%>
<html>
<body>
<table border=1>
<tr>
<td>slno</td>
<td>name</td>
</tr>
<tr>
<td>1</td>
<td>Abhilash</td>
</tr>
<tr>
<td>2</td>
<td>Sarvana</td>
</tr>
</th>
</table>
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition","attachment;filename=Web_Transact_Report.xls");
//FileOutputStream out1 = new FileOutputStream("workbook.xls");
System.out.println("Going to create Excel file");
int rownum = 0 ;
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Transaction Report");
System.out.println("Going to create rows");
HSSFRow row = sheet.createRow((short)rownum);
rownum++;
HSSFCell cel ;
cel = row.createCell((short)0);
cel.setCellValue("slno");
cel = row.createCell((short)1 );
cel.setCellValue("Name");
System.out.println("writing data to response");
//wb.write(out1);
wb.write(response.getOutputStream())
%>
</body>
</html>

i am getting this error

java.lang.IllegalStateException: getOutputStream() has already been called for this response
org.apache.coyote.tomcat5.CoyoteResponse.getWriter(CoyoteResponse.java:599)

wht i have done is i created fileoutputstream out1=new FileOutputStream("workbook.xls");

wb.write(out1)
its working but unnecessarly i am creating workbook.xls
so,i dont want this how to write plz explain.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61764
    
  67

Originally posted by Abhilash Boinapally:
Thankz for ur reply


Abhilash Boinapally, JavaRanch is a community of people from all over the world, many of who are not native English speakers. While using abbreviations like "u" instead of spelling out "you" is convenient when text messaging your friends on a cell phone or in a chat room, it presents an extra challenge to those that are already struggling with English. Additionally, such shortcuts may confound automated translation tools that patrons of the Ranch may be making use of.

I would like to ask for your help in making the content of JavaRanch a little easier to read for everybody that visits here by not using such abbreviations.

Please read this for more information.

thanks,
bear
JavaRanch Sheriff


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Abhilash Boinapally
Greenhorn

Joined: Jun 13, 2006
Posts: 8
if i am executing the below program i am getting the following error

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import org.apache.poi.hssf.usermodel.* ;
import org.apache.poi.hssf.dev.* ;
import org.apache.poi.hssf.util.* ;


public class FirstServlet extends HttpServlet
{
public void doGet(HttpServletRequest req,HttpServletResponse res)
throws ServletException,IOException
{
res.setContentType("application/vnd.ms-excel");
res.setHeader("Content-disposition","attachment;filename=Web_Transact_Report.xls");
//res.setContentType("text/html"); //MIME
PrintWriter out = res.getWriter(); //Character Stream to Browser
out.println("<HTML>");
out.println("<body>");
out.println("<H1>Welcome</h1>");
out.println("<table border=1>");
out.println("<tr>");
out.println("<td>slno</td>");
out.println("<td>name</td>");
out.println("</tr>");
out.println("<tr>");
out.println("<td>1</td>");
out.println("<td>Abhilash</td>");
out.println("</tr>");
out.println("<tr>");
out.println("<td>2</td>");
out.println("<td>Sarvana</td>");
out.println("</tr>");
out.println("</th>");
out.println("</table>");
System.out.println("Going to create Excel file");
int rownum = 0 ;
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Transaction Report");
System.out.println("Going to create rows");
HSSFRow row = sheet.createRow((short)rownum);
rownum++;
HSSFCell cel ;
cel = row.createCell((short)0);
cel.setCellValue("Slno");
cel = row.createCell((short)1 );
cel.setCellValue("Name");
System.out.println("writing data to response");
wb.write(res.getOutputStream());
out.println("</body></html>");
}
}


error:
exception

javax.servlet.ServletException: Servlet execution threw an exception


root cause

java.lang.NoSuchMethodError: org.apache.poi.hssf.usermodel.HSSFSheet.createRow(S)Lorg/apache/poi/hssf/usermodel/HSSFRow;
FirstServlet.doGet(FirstServlet.java:41)
javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

please tell me how to solve this problem
Richard Green
Ranch Hand

Joined: Aug 25, 2005
Posts: 536
Does it need to be an excel file (*.xls)? Can it be a csv file instead?

CSV files (Comma Seperated Values) are easy to generate (you do not need Apache POI for that)..


MCSD, SCJP, SCWCD, SCBCD, SCJD (in progress - URLybird 1.2.1)
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: how to extract data to an xl sheet