java根据xml配置文件导出excel

 1 <?xml version="1.0" encoding="utf-8"?>
 2 <excel>
 3     <element title="检测库信息表" class="com.model.CheckRecord">
 4         <property name="xmmc" display_name="项目名称"></property>
 5         <property name="sampleNo" display_name="样品编号"></property>
 6         <property name="detectTime" display_name="检测日期"></property>
 7         <property name="jcbh" display_name="检测板号"></property>
 8         <property name="ypmc" display_name="样品名称"></property>
 9         <property name="yplx" display_name="样品类型"></property>
10         <property name="ypcd" display_name="样品产地"></property>
11         <property name="resultType" display_name="检测类型"></property>
12         <property name="resultValue" display_name="检测结果"></property>
13         <property name="resultDetect" display_name="检测读数值"></property>
14         <property name="resultTip" display_name="检测提示"></property>
15         <property name="submitStaff" display_name="送检人员"></property>
16         <property name="submitTime" display_name="送检时间"></property>
17     </element>
18 </excel>

  1 package com.egf.modules.util;
  2 
  3 import java.io.IOException;
  4 import java.io.InputStream;
  5 import java.io.OutputStream;
  6 import java.lang.reflect.Field;
  7 import java.util.Date;
  8 import java.util.HashMap;
  9 import java.util.List;
 10 import java.util.Map;
 11 
 12 import javax.servlet.http.HttpServletRequest;
 13 import javax.servlet.http.HttpServletResponse;
 14 
 15 import org.apache.poi.hssf.usermodel.HSSFCell;
 16 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
 17 import org.apache.poi.hssf.usermodel.HSSFFont;
 18 import org.apache.poi.hssf.usermodel.HSSFRow;
 19 import org.apache.poi.hssf.usermodel.HSSFSheet;
 20 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 21 import org.apache.poi.ss.util.CellRangeAddress;
 22 import org.dom4j.Document;
 23 import org.dom4j.DocumentException;
 24 import org.dom4j.Element;
 25 import org.dom4j.io.SAXReader;
 26 
 27 import com.egf.common.util.DateTimeUtils;
 28 
 29 /**
 30  * 导出excel工具类
 31  * @author BaiFL
 32  */
 33 public class ExportExcelUtil {
 34     
 35     /**标题**/
 36     private String title;
 37     
 38     private InputStream inputStream;
 39     
 40     private OutputStream outputStream;
 41     
 42     private HSSFWorkbook workbook;
 43     
 44     private HSSFSheet sheet;
 45     
 46     /**表格行**/
 47     private HSSFRow row;
 48     
 49     /**单元格**/
 50     private HSSFCell cell; 
 51     
 52     /**字体**/
 53     private HSSFFont font;
 54     
 55     /**单元格样式**/
 56     private HSSFCellStyle cellStyle;
 57     
 58     /**
 59      * 字段及字段注释
 60      * key：字段名
 61      * value：字段注释
 62      */
 63     private Map<String, String> propertyMap = new HashMap<String, String>();
 64     
 65     /**
 66      * 导出excel
 67      * @param className 对象
 68      * @param list 导出结果集
 69      * @param request
 70      * @param response
 71      */
 72     public void export(String className, List<?> list, HttpServletRequest request, 
               HttpServletResponse response){
 73         
 74         //初始化
 75         this.init(className);
 76         
 77         /* 设置资源头信息 */
 78         response.reset();
 79         response.setCharacterEncoding("UTF-8");
 80         response.setContentType("application/vnd.ms-excel");
 81         response.setHeader("Content-Disposition", "filename=" + title 
                + DateTimeUtils.getTimeShortString(new Date()) + ".xls");
 82         
 83         try {
 84             //创建输出流
 85             outputStream = response.getOutputStream();
 86         } catch (IOException e1) {
 87             // TODO Auto-generated catch block
 88             e1.printStackTrace();
 89         }
 90         
 91         //设置字体
 92         font = workbook.createFont();
 93         font.setFontName("宋体");
 94         font.setFontHeightInPoints((short) 12);
 95         
 96         //设置单元格类型
 97         cellStyle = workbook.createCellStyle();
 98         cellStyle.setFont(font);
 99         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
100         
101         //创建第一行title
102         row = sheet.createRow(0);
103         this.setCellValue(0, title);
104         //合并单元格：0行~0行，0列~propertyMap.size() - 1列
105         sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, propertyMap.size() - 1));
106         
107         //创建第二行标题行
108         row = sheet.createRow(1);
109         int i = 0;
110         //遍历propertyMap
111         for(String key : propertyMap.keySet()){
112             //创建单元格
113             this.setCellValue(i, propertyMap.get(key));
114             i++;
115         }
116         
117         //遍历数据集合
118         for(int j = 0; j < list.size(); j++) {
119             Object object = list.get(j);
120             //创建数据行，从第三行开始
121             row = sheet.createRow(j + 2);
122             int k = 0;
123             for(String key : propertyMap.keySet()){
124                 Field f;
125                 String value;
126                 try {
127                     f = object.getClass().getDeclaredField(key);
128                     //设置私有字段的可访问性
129                     f.setAccessible(true);
130                     //获取字段get方法
131                     value = String.valueOf(f.get(object));
132                     //设置单元格
133                     this.setCellValue(k, value);
134                     k++;
135                 } catch (Exception e) {
136                     // TODO: handle exception
137                     e.printStackTrace();
138                 }
139             }
140         }
141         
142         try {
143             workbook.write(outputStream);
144             outputStream.flush();
145         } catch (IOException e) {
146             // TODO Auto-generated catch block
147             e.printStackTrace();
148         }finally {
149             try {
150                 if(outputStream != null){
151                     outputStream.close();
152                 }
153             } catch (Exception e) {
154                 e.printStackTrace();
155             }
156         }
157     }
158     
159     /**
160      * 初始化
161      * @param className
162      */
164     private void init(String className){
165         
166         inputStream = ExportExcelUtil.class
                .getResourceAsStream("/resources/exportExcel/excel.xml");
167         this.workbook = new HSSFWorkbook();
168         this.sheet = workbook.createSheet();
169         
170         SAXReader reader = new SAXReader();
171         
172         Document document = null;
173         try {
174             document = reader.read(inputStream);
175         } catch (DocumentException e) {
176             // TODO Auto-generated catch block
177             e.printStackTrace();
178         }
179         
180         //获取根节点
181         Element excel = document.getRootElement();
182 
183         //获取element集合
184         List<Element> elementList = excel.elements("element");
185         
186         for(Element element : elementList){
187             if(className.equals(element.attributeValue("class"))){
188                 title = element.attributeValue("title");
189                 //element下所有property集合
190                 List<Element> childList = element.elements();
191                 for(Element child : childList){
192                     propertyMap.put(child.attributeValue("name"), 
                            child.attributeValue("display_name"));
193                 }
194             }
195         }
196     }
197     
198     /**
199      * 设置单元格
200      * @param index
201      * @param value
202      */
203     private void setCellValue(int index, String value){
204         //创建单元格
205         cell = row.createCell(index, HSSFCell.CELL_TYPE_STRING);
206         cell.setCellStyle(cellStyle);
207         cell.setCellValue(value);
208         //设置第index列宽为自动
209         sheet.autoSizeColumn(index);
210     }
212 }