[转载]java根据xml配置文件导出excel - 白飞龙 - 博客园

[转载]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

不同列表只需在上述配置文件增加节点即可，确保class属性唯一

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);
    }
}