aspose file tools*
The moose likes Servlets and the fly likes Servlet output result to Excel, how? Must POI? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "Servlet output result to Excel, how? Must POI?" Watch "Servlet output result to Excel, how? Must POI?" New topic
Author

Servlet output result to Excel, how? Must POI?

Ken Shamrock
Ranch Hand

Joined: Jan 23, 2002
Posts: 139
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


Thanks everyone who helped me
Hemanth Pallavajula
Ranch Hand

Joined: Oct 07, 2004
Posts: 38
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.


Cheers,<br />Hemanth...<br />(When opportunity doesn't knock, build a door.)
William Brogden
Author and all-around good cowpoke
Rancher

Joined: Mar 22, 2000
Posts: 12835
    
    5
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

Joined: Jan 23, 2002
Posts: 139
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

Joined: Jan 23, 2002
Posts: 139
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";
}
}
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Servlet output result to Excel, how? Must POI?