利用 POI 从JSP表格中生成excel文件_java poi把jsp转为excel-CSDN博客

本文链接：https://blog.csdn.net/TopestCoder/article/details/1967092

我研究了一天，终于得到了比较满意的效果。其中遇到了各种问题，着实耗费了不少时间。

在项目中，你可能会遇到将JSP中表格的数据生成报表的应用，下面我介绍下我的做法。

因为我的这个项目是用STRUTS 2.0做的，在需要生成报表的这个页面，我新建了一个Button，用来生成excel文件

代码如下:

< table border = " 0 " cellpadding = " 0 " cellspacing = " 0 " width = " 100% " >

< tr >

< td align = center >

< INPUT class = button type = " button " value = " 生成Excel文件 " name = " ebotton " onClick = " submitform(); " >

</ td >

</ tr >

</ table >

注意到我处理点击的函数是submitform();它的定义在JS里面。如下：

function submitform()

{

window.document.myform.action = 'export.jsp';

myform.submit();

}

这里的myform.submit()将form又提交了一遍，对，这很有用，因为一般一个form有一个提交。我这里action后面跟了个静态页面（其实也是可以跟动态action的，原先我就是这样，想尽量把JAVA代码从页面剥离出来，但后来遇到了一个问题，只好改成页面了），而这个页面就是处理“生成excel”这个动作的。

下面是这个页面的代码：

<% @ page language = " java " contentType = " text/html; charset=gb2312 "

pageEncoding = " gb2312 " %>

<% @ page language = " java " import = " java.util.*,

org.apache.poi.hssf.usermodel.HSSFWorkbook,

org.apache.poi.hssf.usermodel.HSSFSheet,

org.apache.poi.hssf.usermodel.HSSFRow,

org.apache.poi.hssf.usermodel.HSSFCell,

java.text.DecimalFormat,

com.justinmobile.payease.admin.commons.excel.XLSExport " %>

response.reset();

response.setContentType( " application/ms-excel " );

response.setHeader( " Content-disposition " , " attachment;filename=untitled.xls " ); // 定义文件名

DecimalFormat f = new DecimalFormat( " #,##0.00 " );

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet( " sheet1 " );

String[] sellCoName = request.getParameterValues( " sellCoName " );

String[] signAdd = request.getParameterValues( " signAdd " );

String[] implementYear = request.getParameterValues( " implementYear " );

String[] sellLicence = request.getParameterValues( " sellLicence " );

String[] signTime = request.getParameterValues( " signTime " );

String[] buySheetSymbol = request.getParameterValues( " buySheetSymbol " );

String[] goodsName = request.getParameterValues( " goodsName " );

String[] goodModel = request.getParameterValues( " goodModel " );

String[] unit = request.getParameterValues( " unit " );

String[] amount = request.getParameterValues( " amount " );

String[] examinantLX = request.getParameterValues( " examinantLX " );

String[] examinantParentCompany = request.getParameterValues( " examinantParentCompany " );

String[] buySheetID = request.getParameterValues( " buySheetID " );

String[] coName = request.getParameterValues( " coName " );

String[] examinantChildCompany = request.getParameterValues( " examinantChildCompany " );

// XLSExport e = new XLSExport(response);

XLSExport e = new XLSExport(response);

int row = 0 ; // 标示行数

e.createRow( 0 );

row ++ ;

e.setCell( 0 , " 销售单位 " );

e.setCell( 1 , " 签订地址 " );

e.setCell( 2 , " 执行年度 " );

e.setCell( 3 , " 销售许可证号 " );

e.setCell( 4 , " 签订时间 " );

e.setCell( 5 , " 合同编号 " );

e.createRow( 1 );

row ++ ;

for ( int j = 0 ;j < sellCoName.length;j ++ ) {

e.setCell(0, sellCoName[j]);

e.setCell(1, signAdd[j]);

e.setCell(2, implementYear[j]);

e.setCell(3, sellLicence[j]);

e.setCell(4, signTime[j].toString());

e.setCell(5, buySheetSymbol[j]);

}

e.createRow( 2 );

row ++ ;

e.createRow( 3 );

row ++ ;

e.setCell( 0 , " 产品名称 " );

e.setCell( 1 , " 规格型号 " );

e.setCell( 2 , " 计量单位 " );

e.setCell( 3 , " 数量 " );

e.setCell( 4 , " 供货单位 " );

int i = 0 ;

for (i = 0 ; i < goodsName.length; i ++ ) {

e.createRow(i+4);

e.setCell(0, goodsName[i] );

e.setCell(1, goodModel[i] );

e.setCell(2, unit[i]);

e.setCell(3, amount[i]);

e.setCell(4, coName[i]);

row++;

}

e.createRow(row + 1 );

e.setCell( 0 , " 联兴批准人 " );

e.setCell( 1 , " 总公司确认人 " );

e.setCell( 2 , " 分公司确认人 " );

e.createRow(row + 2 );

for ( int k = 0 ;k < examinantLX.length;k ++ ) {

e.setCell(0, examinantLX[k]);

e.setCell(1, examinantParentCompany[k]);

e.setCell(2, examinantChildCompany[k]);

}

e.exportXLS();

wb.write(response.getOutputStream());

response.getOutputStream().flush();

response.getOutputStream().close();

out.clear();

out = pageContext.pushBody();

<! DOCTYPE html PUBLIC " -//W3C//DTD HTML 4.01 Transitional//EN " " http://www.w3.org/TR/html4/loose.dtd " >

< html >

< head >

< meta http - equiv = " Content-Type " content = " text/html; charset=ISO-8859-1 " >

< title > Insert title here </ title >

</ head >

< body >

</ body >

</ html >

代码比较长，希望你有耐心。这里新建了XLSExport的一个对象（这个类呆会给出），调用这里面已经写好的向excel表中添数据的方法能比较清晰的进行添表操作。注意，这几行

    response.getOutputStream().flush();
    response.getOutputStream().close();

    out.clear();
    out = pageContext.pushBody();

一定不要掉，因为JSP的内建对象out与 response.getOutputStream()会产生冲突，使用完out必须要清除缓存的数据，不加便会出现异常。

下面是XLSExport。这个总是有用

package com.justinmobile.payease.admin.commons.excel;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.PrintWriter;

import java.util.Calendar;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.jsp.JspWriter;

import javax.servlet.jsp.PageContext;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/** */

/**

* 生成导出Excel文件对象

public class XLSExport {

// 设置cell编码解决中文高位字节截断

private static short XLS_ENCODING = HSSFWorkbook.ENCODING_UTF_16;

// 定制日期格式

private static String DATE_FORMAT = " m/d/yy ";

// 定制浮点数格式

private static String NUMBER_FORMAT = " #,##0.00 ";

private HSSFWorkbook workbook;

private HSSFSheet sheet;

private HSSFRow row;

private HttpServletResponse response;

/**

* 初始化Excel

* 导出文件名

public XLSExport(HttpServletResponse response) {

//this.xlsFileName = fileName;

this.response=response;

this.workbook = new HSSFWorkbook();

this.sheet = workbook.createSheet();

}

/** */

/**

* 导出Excel文件

* @throws IOException

* @throws XLSException

public void exportXLS() throws IOException {

response.setContentType("application/vnd.ms-excel");

response.addHeader("Content-Disposition", "attachment; filename=untitled.xls");

workbook.write(response.getOutputStream());

response.getOutputStream().flush();

response.getOutputStream().close();

}

/**

* 增加一行

* 行号

public void createRow(int index) {

this.row = this.sheet.createRow(index);

}

/**

* 设置单元格

* 单元格填充值

public void setCell(int index, String value) {

HSSFCell cell = this.row.createCell((short) index);

cell.setCellType(HSSFCell.CELL_TYPE_STRING);

cell.setEncoding(XLS_ENCODING);

cell.setCellValue(value);

}

/**

* 设置单元格

* 单元格填充值

public void setCell(int index, Calendar value) {

HSSFCell cell = this.row.createCell((short) index);

cell.setEncoding(XLS_ENCODING);

cell.setCellValue(value.getTime());

HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式

cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式

cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式

}

/**

* 设置单元格

* 单元格填充值

public void setCell(int index, int value) {

HSSFCell cell = this.row.createCell((short) index);

cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

cell.setCellValue(value);

}

/**

* 设置单元格

* 单元格填充值

public void setCell(int index, double value) {

HSSFCell cell = this.row.createCell((short) index);

cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

cell.setCellValue(value);

HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式

HSSFDataFormat format = workbook.createDataFormat();

cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式

cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式

}

这样应该很清楚了！