Win a copy of Learn Spring Security (video course) this week in the Spring forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Servlet output result to Excel, how? Must POI?

 
Ken Shamrock
Ranch Hand
Posts: 139
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello all

If I need to output data as Excel format for users in front of their browser (it includes more than 1 spreadsheet), must I make use of POI?

If so, how? The process: User submit the search criteria, Servlet operate and call POI, POI generate file on the server (am i right?), then how can it popup the excel files in front of the browser??

If there's some sample code on how Servlet use POI, it should be great help, I can't find such sample code on net, thanks very much

Ken
 
Hemanth Pallavajula
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hai Ken,

I am not able to know what is meant by POI. Can you please give me its full form, so that I can do some R&D, if it is out of my knowledge, and help you.
 
William Brogden
Author and all-around good cowpoke
Rancher
Posts: 13055
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
POI is the Apache Software Foundation project for Java access to Microsoft file formats. This page is a good starting point. Several file formats, including Excel, are addressed.
If your spreadsheet is relatively simple, consider comma separated variable (CSV) text output which Excel can easily read.
Bill
 
Ken Shamrock
Ranch Hand
Posts: 139
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Hemanth Pallavajula, William Brogden had pointed out the official site of POI

There's a few ref link I found:
http://www.devx.com/Java/Article/17301?
http://www.unc.edu/~smyre/poi/poi_jj.html
http://jakarta.apache.org/poi/hssf/how-to.html

What I need is user inputed search criteria, Servlet go to Oracle to get the result and output reports in excel format, since output are report, they do need formatting. Also, one important issue why I need more than one spreadsheet is because Excel have 65535 rows limit, if data is more than 65535 rows, then it will have problem..
 
Ken Shamrock
Ranch Hand
Posts: 139
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's powerful! Below is a code for creating Excel via Servlet, please improve it together, do you think there's any room for improvement? Like need any threading?

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

public class HSSFExcelCreator extends HttpServlet {
public void init(ServletConfig config) throws ServletException {
super.init(config);
}

public void destroy() {
}

/** Processes requests for both HTTP GET and POST methods.
* @param request servlet request
* @param response servlet response
*/

protected void processRequest(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {

response.setContentType("application/vnd.ms-excel");
//response.setHeader("Content-Disposition", "inline; filename=\"my.xls\"");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.
HSSFRow row = sheet.createRow((short)0);

// Create a cell and put a value in it.
HSSFCell cell = row.createCell((short)0);

cell.setCellValue(1);

// Or do it on one line.
row.createCell((short)1).setCellValue(1.2);
row.createCell((short)2).setCellValue("This is a string");
row.createCell((short)3).setCellValue(true);
// Write the output
OutputStream out = response.getOutputStream();
wb.write(out);
out.close();
}

/** Handles the HTTP <code>GET</code> method.
* @param request servlet request
* @param response servlet response
*/

protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
processRequest(request, response);
}

/** Handles the HTTP POST method.
* @param request servlet request
* @param response servlet response
*/

protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
processRequest(request, response);
}

/** Returns a short description of the servlet.
*/

public String getServletInfo() {
return "Example to create a workbook in a servlet using HSSF";
}
}
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic