我研究了一天,终于得到了比较满意的效果。其中遇到了各种问题,着实耗费了不少时间。
在项目中,你可能会遇到将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()
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
...
{
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
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
++
;
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
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
;
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
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
);
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
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
>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
</
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;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
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;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
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;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
/** */
/** */
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
/** */
/**
* 生成导出Excel文件对象
*
*
*/
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
public
class
XLSExport
...
{
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
// 设置cell编码解决中文高位字节截断
private static short XLS_ENCODING = HSSFWorkbook.ENCODING_UTF_16;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
// 定制日期格式
private static String DATE_FORMAT = " m/d/yy ";
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
// 定制浮点数格式
private static String NUMBER_FORMAT = " #,##0.00 ";
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
private HSSFWorkbook workbook;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
private HSSFSheet sheet;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
private HSSFRow row;
private HttpServletResponse response;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* 初始化Excel
* 导出文件名
*/
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public XLSExport(HttpServletResponse response) ...{
//this.xlsFileName = fileName;
this.response=response;
this.workbook = new HSSFWorkbook();
this.sheet = workbook.createSheet();
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//** */
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* 导出Excel文件
* @throws IOException
* @throws XLSException
*/
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public void exportXLS() throws IOException ...{
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
response.setContentType("application/vnd.ms-excel");
response.addHeader("Content-Disposition", "attachment; filename=untitled.xls");
workbook.write(response.getOutputStream());
response.getOutputStream().flush();
response.getOutputStream().close();
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* 增加一行
* 行号
*/
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public void createRow(int index) ...{
this.row = this.sheet.createRow(index);
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* 设置单元格
* 单元格填充值
*/
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
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);
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* 设置单元格
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
* 单元格填充值
*/
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
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日期的显示格式
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* 设置单元格
* 单元格填充值
*/
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public void setCell(int index, int value) ...{
HSSFCell cell = this.row.createCell((short) index);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(value);
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* 设置单元格
* 单元格填充值
*/
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
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浮点数的显示格式
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
}
这样应该很清楚了!