JSP (servlet) uses POI to export Excel data file

Exporting data through Excel has become a basic feature of various web sites, and below is a detailed introduction to how to export Excel files using the Poi.jar method. The steps are as follows:

1, create a website, below I create a site called test, and then put the POI jar package into the Lib folder. I am here to download the link poi-2.5.1.jar.

The structure directory is as follows:

2. Then we can create a JSP interface to access (mainly submitted to servlet, optional)

The code is as follows:

<%@ page language= "java" import= "java.util.*" pageencoding= "Utf-8"%><! DOCTYPE HTML PUBLIC "-//w3c//dtd HTML 4.01 transitional//en" >
3, the prior creation of a good operation document (can be downloaded) excelfilegenerator
/** * System data Export Excel Generator * @version 1.0 */package com.whp.test;import java.io.outputstream;import java.util.ArrayList; Import Org.apache.poi.hssf.usermodel.hssfcell;import Org.apache.poi.hssf.usermodel.hssfcellstyle;import Org.apache.poi.hssf.usermodel.hssffont;import Org.apache.poi.hssf.usermodel.hssfrow;import Org.apache.poi.hssf.usermodel.hssfsheet;import Org.apache.poi.hssf.usermodel.hssfworkbook;import Org.apache.poi.hssf.util.hssfcolor;public class Excelfilegenerator {private final int split_count = 1500;// The number of rows in Excel per workbook private ArrayList fieldName = null; Excel header Data Set private ArrayList fieldData = null; Excel data content private Hssfworkbook WorkBook = null;/** * constructor * @param fieldName result set field name * @param data */public Excelfilegener Ator (ArrayList fieldName, ArrayList fieldData) {this.fieldname = Fieldname;this.fielddata = FieldData;} /** * Create Hssfworkbook Object * @return hssfworkbook */public hssfworkbook createworkbook () {workBook = new Hssfworkbook (); int ro WS = Fielddata.size (); int sheetnum = 0;if (rows% Split_count = = 0) {sheetnum = Rows/split_count;} else {sheetnum = Rows/split_count + 1;} for (int i = 1; I <= sheetnum; i++) {Hssfsheet sheet = workbook.createsheet ("page" + i); Hssfrow Headrow = Sheet.createrow ((short) 0); for (int j = 0; J < Fieldname.size (); j + +) {Hssfcell cell = Headrow.createcell ((short) j);//Add Style Cell.setcelltype (HSSFCE ll. cell_type_string); cell.setencoding (hssfcell.encoding_utf_16);//Add style//set width of all cells sheet.setcolumnwidth ((short) J, ( Short) 6000)//Create a style (created with the work of the object) Hssfcellstyle CellStyle = Workbook.createcellstyle ();//Create a Font object Hssffont font = Workbook.createfont ();//font Bold font.setboldweight (hssffont.boldweight_bold);//Set font Color short = Hssfcolor.red.index;font.setcolor (color);//Place the newly set font property in the style Cellstyle.setfont (font); if (Fieldname.get (j) = null) { Cell.setcellstyle (CellStyle); Cell.setcellvalue ((String) Fieldname.get (j)); Else{cell.setcellstyle (CellStyle); Cell.setcellvalue ("-");}} for (int k = 0; k < (Rows < Split_count rows:split_count); k++) {Hssfrow row = Sheet.createrow ((short) (k + 1));//Place data contents into Excel cell ArrayList rowlist = (ArrayList) fielddata.get ((i-1) * SPL It_count + K); for (int n = 0, n < rowlist.size (); n++) {Hssfcell cell = Row.createcell ((short) n); Cell.setencoding (HSSF CELL.ENCODING_UTF_16); if (Rowlist.get (n) = null) {Cell.setcellvalue (String) rowlist.get (n). toString ()); Else{cell.setcellvalue ("");}}} return WorkBook;} public void Expordexcel (OutputStream os) throws Exception {WorkBook = Createworkbook (); workbook.write (OS); Os.close ();}}


4. Create a servlet called Excel. ------simulate tabular data with getfieldname () and GetFieldData ()
Package Com.whp.test;import Java.io.ioexception;import Java.io.outputstream;import java.io.printstream;import Java.io.printwriter;import Java.util.arraylist;import Javax.servlet.servletexception;import Javax.servlet.http.httpservlet;import Javax.servlet.http.httpservletrequest;import Javax.servlet.http.httpservletresponse;public class Excel extends HttpServlet {public void doget (httpservletrequest Request, HttpServletResponse response) throws Servletexception, IOException {try {//Initialize Fieldname,fielddatearraylist    Fieldname=getfieldname ();    Excel header Data set ArrayList Fielddata=getfielddata (); Excel data Content String myexcel= "myexcel";//go back to output stream outputstream out=response.getoutputstream ();//Reset Output stream Response.reset (); /Set export form of export Excel report Response.setcontenttype ("application/vnd.ms-excel"); Response.setheader ("Content-disposition", "Attachment;filename=" +myexcel+ ". xls"); Excelfilegenerator efg=new excelfilegenerator (FieldName, FieldData); Efg.expordexcel (out);//Set Output form system.setout ( New PrintStream (out));/Refresh Output stream Out.flush ();//Close output stream if (out!=null) {out.close ();}} catch (Exception e) {//TODO auto-generated catch Blocke.printstacktrace ();}} public void DoPost (HttpServletRequest request, httpservletresponse response) throws Servletexception, IOException {thi S.doget (request, response);} Impersonation provides the header dataset in Excel public ArrayList GetFieldName () {String str[]={"name", "study number", "Gender"}; ArrayList list=new ArrayList (); for (int i=0;i<str.length;i++) {list.add (str[i]);} return list;} Simulates providing header data content in Excel public ArrayList GetFieldData () {ArrayList list1=new ArrayList ();    String str[][]={{"Wang", "01", "Male"},{"Hai", "02", "Male"},{"ping", "03", "Female"}; for (int i=0;i<str.length;i++) {ArrayList list=new ArrayList (), for (int j=0;j<str[0].length;j++) {List.add (str[i ][J]);} List1.add (list);} return list1;}}

5. Test:
You can get the downloaded file by localhost:8080/test/excel.jsp Access and then clicking the button.
The results of the test data are as follows:



JSP (servlet) uses POI to export Excel data file