一.sxpss项目中导出Excel原理
1.项目中导出excel原理:项目中自定义工具类ExcelUtil<T> 其方法exportExcel创建Excel数据并导出。
headers:头部信息
ExportBean:一行excel数据代表一个ExportBean对象。ExportBean简单类,属性及get、set。
List<ExportBean> lists: 多行excel数据。
fileName:Excel文件名称。
public class ExcelUtil<T> {
public void exportExcel(String[] headers, List<ExportBean> lists,
String fileName, HttpServletResponse res) {...
2.应用如下
导出数据如下图:
第2行: headers,第2~15行: lists,第一行: fileName。
3.工具类ExcelUtil源码
package com.futuresoftware.rms.util;
import com.futuresoftware.rms.domain.ExportBean;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.Region;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Method;
import java.util.List;
public class ExcelUtil<T> {
public void exportExcel(String[] headers, List<ExportBean> lists,
String fileName, HttpServletResponse res) {
HSSFWorkbook wb = new HSSFWorkbook(); //创建一个Excel文件
HSSFSheet sheet = wb.createSheet("统计报表");//创建一个Excel的Sheet
HSSFRow row = sheet.createRow((int) 0);// 创建Sheet的一行,((short)0)参数代表行数0代表第一行,1代表第二行。。。。
HSSFCell cell = null; //创建单元格
HSSFCellStyle style = wb.createCellStyle();//创建一个Excel 样式对象
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
cell = row.createCell((short) 0);
cell.setCellValue(new HSSFRichTextString(fileName));
cell.setCellStyle(style);
Region region = new Region(0, (short)0, 0, (short)(headers.length-1)); //单元格范围
sheet.addMergedRegion(region); //合并单元格
//在sheet中添加表头第0行,老版本poi对excel行数列数有限制short
row = sheet.createRow((int) 1);
//设置表头
for (int i = 0; i < headers.length; i++) {
sheet.setColumnWidth((short)i, (short)5000);//设置列宽
cell = row.createCell((short) i);
cell.setCellValue(headers[i]);
cell.setCellStyle(style);
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
InputStream is=null;
ServletOutputStream out =null;
try {
Class<?> c = Class.forName("com.futuresoftware.rms.domain.ExportBean");
// 循环将数据写入Excel
for (int i = 0; i < lists.size(); i++) {
row = sheet.createRow((int) i + 2);
ExportBean list = lists.get(i);
// 创建单元格,设置值
for (int j = 0; j < headers.length; j++) {
//第一个参数写的是方法名
Method method=c.getMethod("getData"+j);
//invoke是执行该方法
String str2= (String) method.invoke(list, new Object[]{});
cell=row.createCell((short) j);
cell.setCellValue(str2);
cell.setCellStyle(style);
}
}
wb.write(os);
byte[] content = os.toByteArray();
is = new ByteArrayInputStream(content);
// 设置response参数,可以打开下载页面
res.reset();
res.setContentType("application/vnd.ms-excel;charset=utf-8");
res.setHeader("Content-Disposition", "attachment;filename="
+ new String((fileName + ".xls").getBytes(), "iso-8859-1"));
out = res.getOutputStream();
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bis != null){
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(is!=null){
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (bos != null){
try {
bos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(out!=null){
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}