1.需要导包
2.工具类
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
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.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.hssf.util.CellRangeAddress;
public class ExportExcel<T> {
public HSSFWorkbook exportExcel(String title,Collection<T> dataset) {
return exportExcel("orderForm",title, null, dataset, "yyyy-MM-dd HH:mm:ss");
}
public HSSFWorkbook exportExcel(String title,String[] headers, Collection<T> dataset) {
return exportExcel("orderForm",title, headers, dataset, "yyyy-MM-dd HH:mm:ss");
}
public HSSFWorkbook exportExcel(String title,String[] headers, Collection<T> dataset,
String pattern) {
return exportExcel("orderForm",title, headers, dataset, pattern);
}
public HSSFWorkbook exportExcel(String title,String titleStr , String[] headers,
Collection<T> dataset, String pattern) {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
//标题样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 14);//设置字体大小
cellStyle.setFont(font);
//设置标题
HSSFRow firstHeaderRow=sheet.createRow(0);// 第一行
sheet.addMergedRegion(new CellRangeAddress(0,0,0,headers.length-1));
HSSFCell yearCell=firstHeaderRow.createCell(0);
yearCell.setCellValue(title);
yearCell.setCellStyle(cellStyle);
// 产生表格标题行
HSSFRow row = sheet.createRow(1);
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
// cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
// 遍历集合数据,产生数据行
Iterator<T> it = dataset.iterator();
int index = 1;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = (T) it.next();
// 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < fields.length; i++) {
HSSFCell cell = row.createCell(i);
// cell.setCellStyle(style2);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
try {
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName,
new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
// 判断值的类型后进行强制类型转换
String textValue = null;
if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
cell.setCellValue(textValue);
} else {
if (value != null && value != "") {
textValue = value.toString();
cell.setCellValue(textValue);
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 清理资源
}
}
}
return workbook;
}
}
3.调用示例
ExportExcel<OrderFormForExcel> ex = new ExportExcel<OrderFormForExcel>();
//标题栏数组
String[] headers = { "编号","订单id", …标题数组… };
HSSFWorkbook wb = ex.exportExcel("标题",headers,listForExcels);
// 文件名
String str = "导出文件标题";
String fileName = str + ".xls";
response.setContentType("application/octet-stream");
// response.setHeader("Content-Type","video/x-msvideo");
response.setHeader("Content-disposition", "attachment;filename="+ fileName);
response.flushBuffer();
// wb:HSSFWorkbook
OutputStream os = response.getOutputStream();
wb.write(os);
4.注意
与PrintWriter同时使用会出问题.
解决办法:
在末尾加上如下代码:
out.clear();
out = pageContext.pushBody();
以上内容整理自网络