Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to extract data to an xl sheet

 
Abhilash Boinapally
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • 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 ]
 
Avneet Singh
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
  • 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
  • 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.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64629
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • 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
  • 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
 
Richard Green
Ranch Hand
Posts: 536
  • Mark post as helpful
  • send pies
  • 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)..
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic