poi导出数据(需要合并单元格)

[java]  view plain  copy

1. import java.io.IOException;  
2. import java.io.OutputStream;  
3. import java.io.UnsupportedEncodingException;  
4. import java.lang.reflect.InvocationTargetException;  
5. import java.lang.reflect.Method;  
6. import java.math.RoundingMode;  
7. import java.text.DecimalFormat;  
8. import java.text.SimpleDateFormat;  
9. import java.util.Collection;  
10. import java.util.Date;  
11. import java.util.Iterator;  
12. import java.util.regex.Matcher;  
13. import java.util.regex.Pattern;  
14.   
15. import javax.servlet.http.HttpServletResponse;  
16.   
17. import org.apache.poi.xssf.usermodel.XSSFCell;  
18. import org.apache.poi.xssf.usermodel.XSSFCellStyle;  
19. import org.apache.poi.xssf.usermodel.XSSFDataFormat;  
20. import org.apache.poi.xssf.usermodel.XSSFRichTextString;  
21. import org.apache.poi.xssf.usermodel.XSSFRow;  
22. import org.apache.poi.xssf.usermodel.XSSFSheet;  
23. import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
24.   
25.   
26.   
27. public class CreateExcel<T> {  
28.       
29.     public void expExcel(String title, String[] headers,String[] context,Collection<T> dataset, OutputStream out){  
30.         exportExcel(title,headers,context,dataset,out,"yyyy/mm/dd");  
31.     }  
32.       
33.     public void expExcel(String title, String[] headers,String[] context,  
34.             Collection<T> dataset, OutputStream out, String pattern){  
35.         exportExcel(title,headers,context,dataset,out,pattern);  
36.     }  
37.       
38.     public void setExcelResponse(HttpServletResponse response,String name,String[] headers,String[] context,Collection<T> dataset){  
39.         OutputStream out = null;  
40.         try {  
41.             out = response.getOutputStream();  
42.             response.reset();// 清空输出流  
43.             response.setHeader("Content-disposition", "attachment; filename="  
44.                     + new String(name.getBytes("GBK"), "ISO-8859-1") + ".xlsx");// 设定输出文件头  
45.             response.setContentType("application/ms-excel;charset=UTF-8");// 定义输出类型  
46.             expExcel(name, headers, context, dataset, out);  
47.         } catch (IOException e) {  
48.             e.printStackTrace();  
49.         } finally {  
50.             try {  
51.                 out.close();  
52.             } catch (IOException e) {  
53.                 // TODO Auto-generated catch block  
54.                 e.printStackTrace();  
55.             }  
56.         }  
57.     }  
58.       
59.     public static void setExcelResponse(HttpServletResponse response,String name,XSSFWorkbook workbook){  
60.         OutputStream out = null;  
61.         try {  
62.             out = response.getOutputStream();  
63.             response.reset();// 清空输出流  
64.             response.setHeader("Content-disposition", "attachment; filename="  
65.                     + new String(name.getBytes("GBK"), "ISO-8859-1") + ".xlsx");// 设定输出文件头  
66.             response.setContentType("application/ms-excel;charset=UTF-8");// 定义输出类型  
67.             workbook.write(out);  
68.         } catch (IOException e) {  
69.             e.printStackTrace();  
70.         } finally {  
71.             try {  
72.                 out.close();  
73.             } catch (IOException e) {  
74.                 // TODO Auto-generated catch block  
75.                 e.printStackTrace();  
76.             }  
77.         }  
78.     }  
79.   
80.     private void exportExcel(String title, String[] headers, String[] context,   
81.             Collection<T> dataset, OutputStream out, String pattern)    
82.     {    
83.         // 声明一个工作薄    
84.         XSSFWorkbook workbook = new XSSFWorkbook();    
85.         // 生成一个表格    
86.         XSSFSheet sheet = workbook.createSheet(title);    
87.         // 生成一个样式    
88.         XSSFCellStyle style = workbook.createCellStyle();           
89.         XSSFCellStyle style2 = workbook.createCellStyle();   
90.         XSSFDataFormat format = workbook.createDataFormat();   
91.         style2.setDataFormat(format.getFormat("@"));   
92.     
93.         // 产生表格标题行    
94.         XSSFRow row = sheet.createRow(0);    
95.         for (int i = 0; i < headers.length; i++)    
96.         {    
97.             XSSFCell cell = row.createCell(i);    
98.             cell.setCellStyle(style);    
99.             XSSFRichTextString text = new XSSFRichTextString(headers[i]);    
100.             cell.setCellValue(text);    
101.         }    
102.     
103.         // 遍历集合数据，产生数据行    
104.         Iterator<T> it = dataset.iterator();    
105.         int index = 0;    
106.         while (it.hasNext())    
107.         {    
108.             index++;    
109.             row = sheet.createRow(index);    
110.             T t = (T) it.next();    
111.             for (int i = 0; i < context.length; i++)    
112.             {    
113.                 XSSFCell cell = row.createCell(i);      
114.                 String fieldName = context[i];    
115.                 String getMethodName = "get"    
116.                         + fieldName.substring(0, 1).toUpperCase()    
117.                         + fieldName.substring(1);    
118.                 try    
119.                 {    
120.                     Class tCls = t.getClass();    
121.                     Method getMethod = tCls.getMethod(getMethodName,    
122.                             new Class[]    
123.                             {});  
124.                     Object value = getMethod.invoke(t, new Object[]    
125.                     {});   
126.                     // 判断值的类型后进行强制类型转换    
127.                     if(value==null){  
128.                         continue;  
129.                     }else     
130.                     {    
131.                         getValue(cell,value,pattern,style2);  
132.                     }  
133.                 }    
134.                 catch (SecurityException e)    
135.                 {    
136.                     e.printStackTrace();    
137.                 }    
138.                 catch (NoSuchMethodException e)    
139.                 {    
140.                     e.printStackTrace();    
141.                 }    
142.                 catch (IllegalArgumentException e)    
143.                 {    
144.                     e.printStackTrace();    
145.                 }    
146.                 catch (IllegalAccessException e)    
147.                 {    
148.                     e.printStackTrace();    
149.                 }    
150.                 catch (InvocationTargetException e)    
151.                 {    
152.                     e.printStackTrace();    
153.                 }    
154.                 finally    
155.                 {    
156.                     // 清理资源    
157.                 }    
158.             }    
159.         }    
160.         try    
161.         {    
162.             workbook.write(out);    
163.         }    
164.         catch (IOException e)    
165.         {    
166.             e.printStackTrace();    
167.         }    
168.     }   
169.       
170.     /** 
171.      * 获取时间格式的值 
172.      * @param cell 
173.      * @param value 
174.      * @param style 
175.      */  
176.     public static void getValue(XSSFCell cell,Object value,XSSFCellStyle style){  
177.         getValue(cell,value,"yyyy/MM/dd",style);  
178.     }  
179.       
180.     /** 
181.      * 通过反射获取值 
182.      * @param fieldName 
183.      * @param obj 
184.      * @param className 
185.      * @return 
186.      */  
187.     @SuppressWarnings({ "rawtypes", "unchecked" })  
188.     public static Object getValueByReflect(String fieldName,Object obj,String className){  
189.         String getMethodName = "get"    
190.                 + fieldName.substring(0, 1).toUpperCase()    
191.                 + fieldName.substring(1);             
192.         try {  
193.             Class tcls=Class.forName(className);  
194.             Method  getMethod = tcls.getMethod(getMethodName,  new Class[]  {});  
195.             Object value = getMethod.invoke(obj, new Object[] {});  
196.             return value;  
197.         } catch (NoSuchMethodException e) {  
198.             e.printStackTrace();  
199.         } catch (IllegalAccessException e) {  
200.             // TODO Auto-generated catch block  
201.             e.printStackTrace();  
202.         } catch (IllegalArgumentException e) {  
203.             // TODO Auto-generated catch block  
204.             e.printStackTrace();  
205.         } catch (InvocationTargetException e) {  
206.             // TODO Auto-generated catch block  
207.             e.printStackTrace();  
208.         } catch (ClassNotFoundException e) {  
209.             // TODO Auto-generated catch block  
210.             e.printStackTrace();  
211.         }    
212.         return null;  
213.     }  
214.       
215.     /** 
216.      * 把值写入excel 
217.      * @param cell 
218.      * @param value 
219.      * @param pattern 
220.      * @param style 
221.      */  
222.     public static void getValue(XSSFCell cell,Object value,String pattern,XSSFCellStyle style){  
223.         String textValue=null;  
224.          if (value instanceof Date)    
225.          {    
226.              Date date = (Date) value;    
227.              SimpleDateFormat sdf = new SimpleDateFormat(pattern);    
228.              textValue = sdf.format(date);    
229.          }else if(value instanceof Double)  
230.          {  
231.              double dou=(Double)value;  
232.              DecimalFormat decimalFormat = new DecimalFormat("#0.0000");//格式化设置    
233.              decimalFormat.setRoundingMode(RoundingMode.HALF_UP);  
234.              textValue=decimalFormat.format(dou);  
235.          }else    
236.          {    
237.              if(value!=null){  
238.                  textValue = value.toString();    
239.              }  
240.          }    
241.          if(style!=null){  
242.              cell.setCellStyle(style);  
243.          }  
244.          if (textValue != null)    
245.          {    
246.              Pattern p = Pattern.compile("^\\d+(\\.\\d+)?$");    
247.              Matcher matcher = p.matcher(textValue);    
248.              if (matcher.matches())    
249.              {    
250.                  // 是数字当作double处理    
251.                  cell.setCellValue(Double.parseDouble(textValue));  
252.              }    
253.              else    
254.              {    
255.                  cell.setCellValue(textValue);    
256.              }    
257.          }    
258.          cell.setCellValue(textValue);  
259.     }  
260.       
261.     /** 
262.      * 根据实体字段转化对应的实际类型 
263.      * @param cell 
264.      * @param value 
265.      * @param pattern 
266.      * @param style 
267.      */  
268.     public static void getActualValue(XSSFCell cell,Object value,String pattern,XSSFCellStyle style){  
269.         String textValue=null;  
270.          if (value instanceof Date)    
271.          {    
272.              Date date = (Date) value;    
273.              SimpleDateFormat sdf = new SimpleDateFormat(pattern);    
274.              textValue = sdf.format(date);    
275.          }else if(value instanceof Double)  
276.          {  
277.              double dou=(Double)value;  
278.              DecimalFormat decimalFormat = new DecimalFormat("#0.0000");//格式化设置    
279.              decimalFormat.setRoundingMode(RoundingMode.HALF_UP);  
280.              textValue=decimalFormat.format(dou);  
281.          }else    
282.          {    
283.              if(value!=null){  
284.                  textValue = value.toString();    
285.              }  
286.          }    
287.          if(style!=null){  
288.              cell.setCellStyle(style);  
289.          }  
290.          cell.setCellValue(textValue);  
291.     }  
292.       
293.     /** 
294.      * 填充数据(利用反射循环填充一行数据) 
295.      * @param rowNum 行数 
296.      * @param sheet sheet页 
297.      * @param style  样式 
298.      * @param obj   数据对象 
299.      * @param keyBean  反射的熟悉名（按excel顺序） 
300.      * @param cla    反射的bean 
301.      * @return 
302.      */  
303.     public static int setValueForExcel(int rowNum, XSSFSheet sheet,XSSFCellStyle style,Object obj,String[] keyBean,@SuppressWarnings("rawtypes") Class cla){  
304.         if(obj==null){  
305.             return rowNum;  
306.         }  
307.         XSSFRow row = sheet.createRow(rowNum);  
308.         for(int i=0;i<keyBean.length;i++){  
309.             Object value=getValueByReflect(keyBean[i], obj, cla.getName());  
310.             getValue(row.createCell(i), value, style);  
311.         }  
312.         return rowNum;  
313.     }  
314.       
315.     /** 
316.      * 填充数据(利用反射循环填充一行数据) 
317.      * @param rowNum 行数 
318.      * @param sheet sheet页 
319.      * @param style  样式 
320.      * @param obj   数据对象 
321.      * @param keyBean  反射的熟悉名（按excel顺序） 
322.      * @param cla    反射的bean 
323.      * @return 
324.      */  
325.     public static void setValueForExcel(XSSFRow row,int firCol, XSSFSheet sheet,XSSFCellStyle style,Object obj,String[] keyBean,@SuppressWarnings("rawtypes") Class cla){  
326.         for(int i=0;i<keyBean.length;i++){  
327.             Object value=getValueByReflect(keyBean[i], obj, cla.getName());  
328.             getValue(row.createCell(i+firCol), value, style);  
329.         }  
330.     }  
331.       
332.       
333.       
334.     /** 
335.      * 当值遇到-1时，变为-(此方法有待修改，可以封装全面一些) 
336.      * @param value 
337.      * @return 
338.      */  
339.     public static Object changeValueByMark(Object value){  
340.         try {  
341.             int val = (int)Double.parseDouble(value.toString());  
342.             if (val==-1) {  
343.                 return "-";  
344.             } else {  
345.                 return value;  
346.             }  
347.         } catch (Exception e) {  
348.             return value;  
349.         }  
350.     }  
351. }  

对应的service:

[java]  view plain  copy

1. @Override  
2.     public void expExcelAll(String siteNo, String productType, String startDate, String endDate, HttpServletResponse response) {  
3.      // 声明一个工作薄    
4.         XSSFWorkbook workbook = new XSSFWorkbook();    
5.         // 生成一个表格    
6.         XSSFSheet sheet = workbook.createSheet("详细数据");  
7.         sheet.setDefaultColumnWidth(20);  
8.         XSSFCellStyle style = workbook.createCellStyle();  
9.         style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);  
10.         style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  
11.         int num=0;  
12.   
13.         //单元格,createCell(i),这里的i代表单元格是第几列，CellRangeAddress(firstRow,lastRow,firstCol,lastCol)里的参数分别表示需要合并的单元格起始行，起始列  
14.         XSSFRow firstRow = sheet.createRow(num);  
15.         CreateExcel.getValue(firstRow.createCell(0),"放款日期",style);  
16.         sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 0));  
17.         CreateExcel.getValue(firstRow.createCell(1),"合同信息",style);  
18.         sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 15));  
19.         CreateExcel.getValue(firstRow.createCell(16),"本次付款信息",style);  
20.         sheet.addMergedRegion(new CellRangeAddress(0, 0, 16, 31));  
21.           
22.         XSSFRow secondRow = sheet.createRow(++num);  
23.         CreateExcel.getValue(secondRow.createCell(16),"其他收费",style);  
24.         sheet.addMergedRegion(new CellRangeAddress(1, 1, 16, 30));  
25.         CreateExcel.getValue(secondRow.createCell(31),"费用合计",style);  
26.         sheet.addMergedRegion(new CellRangeAddress(1, 2, 31, 31));  
27.           
28.         XSSFRow thirdRow = sheet.createRow(++num);  
29.         CreateExcel.getValue(thirdRow.createCell(1),"借款人",style);  
30.         CreateExcel.getValue(thirdRow.createCell(2),"合同号",style);  
31.         CreateExcel.getValue(thirdRow.createCell(3),"分公司",style);  
32.         CreateExcel.getValue(thirdRow.createCell(4),"是否直销",style);  
33.         CreateExcel.getValue(thirdRow.createCell(5),"合作机构",style);  
34.         CreateExcel.getValue(thirdRow.createCell(6),"贷款类型",style);   
35.         CreateExcel.getValue(thirdRow.createCell(7),"产品类型",style);  
36.         CreateExcel.getValue(thirdRow.createCell(8),"还款方式",style);  
37.         CreateExcel.getValue(thirdRow.createCell(9),"贷款期数",style);  
38.         CreateExcel.getValue(thirdRow.createCell(10),"客户主任",style);  
39.         CreateExcel.getValue(thirdRow.createCell(11),"客户经理",style);  
40.         CreateExcel.getValue(thirdRow.createCell(12),"计息本金",style);  
41.         CreateExcel.getValue(thirdRow.createCell(13),"付款类型",style);  
42.         CreateExcel.getValue(thirdRow.createCell(14),"付款金额",style);  
43.         CreateExcel.getValue(thirdRow.createCell(15),"履约保证金",style);  
44.         CreateExcel.getValue(thirdRow.createCell(16),"考察费",style);  
45.         CreateExcel.getValue(thirdRow.createCell(17),"GPS费",style);  
46.         CreateExcel.getValue(thirdRow.createCell(18),"抵押登记费",style);  
47.         CreateExcel.getValue(thirdRow.createCell(19),"停车费",style);  
48.         CreateExcel.getValue(thirdRow.createCell(20),"盗抢险",style);  
49.         CreateExcel.getValue(thirdRow.createCell(21),"刑侦费",style);  
50.         CreateExcel.getValue(thirdRow.createCell(22),"评估费",style);  
51.         CreateExcel.getValue(thirdRow.createCell(23),"律师签证费",style);  
52.         CreateExcel.getValue(thirdRow.createCell(24),"加急费",style);  
53.         CreateExcel.getValue(thirdRow.createCell(25),"风险金",style);  
54.         CreateExcel.getValue(thirdRow.createCell(26),"抵押登记",style);  
55.         CreateExcel.getValue(thirdRow.createCell(27),"手续费",style);  
56.         CreateExcel.getValue(thirdRow.createCell(28),"征信费",style);  
57.         CreateExcel.getValue(thirdRow.createCell(29),"快递费",style);  
58.         CreateExcel.getValue(thirdRow.createCell(30),"其他",style);  
59.         //单元格里面的值对应的实体bean字段  
60.         String[] keyBean = {"confirmDate","custName","contractNo","siteName","isDirect","cooperation","loanType","productTypeName","paymentTypeName",  
61.                 "totalPhases","customerDirector","customerManager","auditamt","payType","payAmt","lybzjFees","kcFeeS",  
62.                 "gpsFees","dydjFees","tcFees","dqxFees","xzFees","pgFees","lsjzFees","jjFees","kdFees","gzFees","sxFees","zxsxFees","shouldglf","qtdsFees","total"};  
63.         List<LoanCountVo>  loanCountVos = this.getAllLoanCountsForExport(siteNo, productType, startDate, endDate);  
64.         for(int i=0;i<loanCountVos.size();i++){  
65.             XSSFRow row = sheet.createRow(++num);  
66.             for(int j=0;j<keyBean.length;j++){  
67.                 Object value = CreateExcel.getValueByReflect(keyBean[j], loanCountVos.get(i), LoanCountVo.class.getName()); //设置单元格的值  
68.                 CreateExcel.getActualValue(row.createCell(j), value,"yyyy/MM/dd", style);  
69.             }             
70.         }          
71.         CreateExcel.setExcelResponse(response,"费用类科目自动对账全部数据",workbook);  
72.     }