• 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

how to extract data to an xl sheet

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
Abhilash Boinapally
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 536
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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)..
 
Don't destroy the earth! That's where I keep all my stuff! Including this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic