这是一个通过配置模板导出报表的JAR包,只适用于小数据量的报表导出,如果数据量超过Excel最大行数(65536)需要自行添加文件打包后下载的功能。
依赖 POI-3.0.jar
package com.goma.exportexcel;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.poifs.filesystem.POIFSFileSystem;
/**
* DESC: 读取模板导出Excel报表<BR>
* AUTHOR: GuoMM ( Goma ) oma1989@yeah.net<BR>
* VERSION: 1.0<BR>
* DATE:2011-11-7 14:23:51<BR>
*/
public class ExportExcel {
private String fileUrl;
/**
*
* @param fileUrl 模板路径<br>
* DESC:创建实例,并初始化模板<br>
*/
public ExportExcel(String fileUrl){
this.fileUrl = fileUrl;
}
/**
*
* @param rows 模板内配置所占行数<br>
* @param cols 模板内配置所占列数<br>
* @param result 要导出报表的数据集<br>
* @return excel报表<br>
*
*/
public InputStream export(int rows,int cols,List<?> result){
try{
File file = new File(fileUrl);
FileInputStream fint = new FileInputStream(file);
POIFSFileSystem poiFileSystem = new POIFSFileSystem(fint);
HSSFWorkbook wb = new HSSFWorkbook(poiFileSystem);
HSSFSheet sheet = wb.getSheetAt(0);
GetProperty gp = new GetProperty();
HSSFRow namesRow = sheet.getRow(rows-2);
HSSFRow typeRow = sheet.getRow(rows-1);
for(int i=0;i<result.size();i++){
HSSFRow row = sheet.getRow(rows+i);
if(row==null){
row = sheet.createRow(rows+i);
}
for(int j=0;j<cols;j++){
HSSFCell cell = row.getCell((short)j);
if (cell == null){
cell = row.createCell((short)j);
}
//获得方法名
HSSFCell nameCell = namesRow.getCell((short)j);
HSSFCell typeCell = typeRow.getCell((short)j);
String name = nameCell.getRichStringCellValue().getString();
String type = typeCell.getRichStringCellValue().getString();
//java反射机制获得方法值
Object obj = gp.getProperty(name, result.get(i).getClass(), result.get(i));
//写入Excel中
if("Number".equals(type)){
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
if(obj!=null&&!"".equals(obj)){
cell.setCellValue(Double.valueOf(String.valueOf(obj)));
}else{
cell.setCellValue(0);
}
}else{
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if(obj==null){
cell.setCellValue(new HSSFRichTextString(""));
}else{
cell.setCellValue(new HSSFRichTextString(String.valueOf(obj)));
}
}
}
}
sheet.shiftRows(rows, result.size()+rows, -2);
System.out.println("WRITE EXCEL REPORT IS OK..");
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
return is;
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return null;
}
}
}
package com.goma.exportexcel;
import java.lang.reflect.Method;
/**
* DESC:通过java反射机制执行类方法<BR>
* author:GuoMM(Goma) oma1989@yeah.net<BR>
* version:1.0<BR>
* Date:2011-11-7 14:22:21<BR>
*/
public class GetProperty {
/**
*
* @param propertyName 方法名<br>
* @param cls 类Class<br>
* @param obj 对象<br>
* @return 返回对象obj.propertyName的执行结果<br>
*/
public Object getProperty(String propertyName,Class<?> cls,Object obj){
try{
if(propertyName!=null &&!"".equals(propertyName)){
Method method = cls.getMethod(propertyName);
return method.invoke(obj);
}
return null;
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return null;
}
}
}
List<TeamModelVO> ls = dao.getData(sql.toString(), TeamModelVO.class); System.out.println(ls.size()+"::::Export:::::"); String fileUrl = request.getSession().getServletContext().getRealPath( "/console/Resources/platformat/型号盈利能力分析.xls"); ExportExcel ee = new ExportExcel(fileUrl); InputStream is = ee.export(6, 53, ls); response.addHeader("Content-Disposition","attachment;filename=Report.xls"); ServletOutputStream out = response.getOutputStream(); byte[] buffer=new byte[1024]; int count; while((count=is.read(buffer))!=-1){ out.write(buffer, 0, count); } return null;