Mikel
[转载]java根据xml配置文件导出excel – 白飞龙 – 博客园.
1、xml配置文件：
<?xml version="1.0" encoding="utf-8"?>
<excel>
<element title="检测库信息表" class="com.model.CheckRecord">
<property name="xmmc" display_name="项目名称"></property>
<property name="sampleNo" display_name="样品编号"></property>
<property name="detectTime" display_name="检测日期"></property>
<property name="jcbh" display_name="检测板号"></property>
<property name="ypmc" display_name="样品名称"></property>
<property name="yplx" display_name="样品类型"></property>
<property name="ypcd" display_name="样品产地"></property>
<property name="resultType" display_name="检测类型"></property>
<property name="resultValue" display_name="检测结果"></property>
<property name="resultDetect" display_name="检测读数值"></property>
<property name="resultTip" display_name="检测提示"></property>
<property name="submitStaff" display_name="送检人员"></property>
<property name="submitTime" display_name="送检时间"></property>
</element>
</excel>
title：excel标题栏
class：javaBean
display_name：列标题
name：实体property
不同列表只需在上述配置文件增加
2、ExportExcelUtil工具类实现代码：
package com.egf.modules.util;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import com.egf.common.util.DateTimeUtils;
/**
* 导出excel工具类
* @author BaiFL
*/
public class ExportExcelUtil {
/**标题**/
private String title;
private InputStream inputStream;
private OutputStream outputStream;
private HSSFWorkbook workbook;
private HSSFSheet sheet;
/**表格行**/
private HSSFRow row;
/**单元格**/
private HSSFCell cell;
/**字体**/
private HSSFFont font;
/**单元格样式**/
private HSSFCellStyle cellStyle;
/**
* 字段及字段注释
* key：字段名
* value：字段注释
*/
private Map<String, String> propertyMap = new HashMap<String, String>();
/**
* 导出excel
* @param className 对象
* @param list 导出结果集
* @param request
* @param response
*/
public void export(String className, List<?> list, HttpServletRequest request,
HttpServletResponse response){
//初始化
this.init(className);
/* 设置资源头信息 */
response.reset();
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "filename=" + title
+ DateTimeUtils.getTimeShortString(new Date()) + ".xls");
try {
//创建输出流
outputStream = response.getOutputStream();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
//设置字体
font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12);
//设置单元格类型
cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//创建第一行title
row = sheet.createRow(0);
this.setCellValue(0, title);
//合并单元格：0行~0行，0列~propertyMap.size() - 1列
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, propertyMap.size() - 1));
//创建第二行标题行
row = sheet.createRow(1);
int i = 0;
//遍历propertyMap
for(String key : propertyMap.keySet()){
//创建单元格
this.setCellValue(i, propertyMap.get(key));
i++;
}
//遍历数据集合
for(int j = 0; j < list.size(); j++) {
Object object = list.get(j);
//创建数据行，从第三行开始
row = sheet.createRow(j + 2);
int k = 0;
for(String key : propertyMap.keySet()){
Field f;
String value;
try {
f = object.getClass().getDeclaredField(key);
//设置私有字段的可访问性
f.setAccessible(true);
//获取字段get方法
value = String.valueOf(f.get(object));
//设置单元格
this.setCellValue(k, value);
k++;
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
}
try {
workbook.write(outputStream);
outputStream.flush();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(outputStream != null){
outputStream.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 初始化
* @param className
*/
private void init(String className){
inputStream = ExportExcelUtil.class
.getResourceAsStream("/resources/exportExcel/excel.xml");
this.workbook = new HSSFWorkbook();
this.sheet = workbook.createSheet();
SAXReader reader = new SAXReader();
Document document = null;
try {
document = reader.read(inputStream);
} catch (DocumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//获取根节点
Element excel = document.getRootElement();
//获取element集合
List<Element> elementList = excel.elements("element");
for(Element element : elementList){
if(className.equals(element.attributeValue("class"))){
title = element.attributeValue("title");
//element下所有property集合
List<Element> childList = element.elements();
for(Element child : childList){
propertyMap.put(child.attributeValue("name"),
child.attributeValue("display_name"));
}
}
}
}
/**
* 设置单元格
* @param index
* @param value
*/
private void setCellValue(int index, String value){
//创建单元格
cell = row.createCell(index, HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
cell.setCellValue(value);
//设置第index列宽为自动
sheet.autoSizeColumn(index);
}
}
