Java通用的Excel文件生成工具类，支持生成文件和浏览器直接下载

1. <span style="font-size:14px;">java通用的Excel文件创建方法，支持同文件多tab页创建。只需要调用静态方法，传递List<String>表头和List<Map>数据集合等，即可生成Excel文件。  
2.   
3. package com.matols.utils;  
4.    
5. import java.io.File;  
6. import java.io.FileInputStream;  
7. import java.io.FileOutputStream;  
8. import java.util.ArrayList;  
9. import java.util.HashMap;  
10. import java.util.List;  
11. import java.util.Map;  
12.    
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.HSSFRichTextString;  
19. import org.apache.poi.hssf.usermodel.HSSFRow;  
20. import org.apache.poi.hssf.usermodel.HSSFSheet;  
21. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
22. import org.apache.poi.hssf.util.HSSFColor;  
23.    
24. import com.google.common.collect.Lists;  
25.    
26. /** 
27.  * Excel工具类 
28.  */  
29. public class ExcelUtils {  
30.     public static void main(String[] args) throws Throwable {  
31.         String path = "D:/tj/统计报表.xls";  
32.         //表头  
33.         List<String> headers = Lists.newArrayList();  
34.         for (int i = 1; i < 10; i++) {  
35.             headers.add("表头"+i);  
36.         }  
37.         //数据行  
38.         List<Map> datas = new ArrayList<Map>();  
39.         Map m = null;  
40.         for(int i=1;i<10;i++){  
41.             m = new HashMap(); //一行数据集  
42.             for(int j=0;j<headers.size();j++){  
43.                 m.put(j, "第"+i+" 行数据："+j);  
44.             }  
45.             datas.add(m);  
46.         }  
47.         ExpExs(path,"","统计报表",headers,datas);  
48.     }  
49.        
50.     /* 
51.      * 通用的Excel文件创建方法 
52.      *   title:首行标题: 2015年度统计报表 
53.      *  sheets:sheet的tab标签页说明: 15年度报表 
54.      * headers:表头：List存放表头  编号、姓名、备注 
55.      *   datas:数据行：list存放实体数据，map存放具体每一行数据，和headers对应。 
56.      *      rs:HttpServletResponse响应作用域，如果不为null，会直接将文件流输出到客户端，下载文件 
57.      */  
58.     public static void ExpExs(String title,String sheets,List headers,List<Map> datas,HttpServletResponse rs){  
59.         try {   
60.             if(sheets== null || "".equals(sheets)){ sheets = "sheet"; }  
61.                 
62.             HSSFWorkbook workbook = new HSSFWorkbook();   
63.             HSSFSheet sheet = workbook.createSheet(sheets); //+workbook.getNumberOfSheets()  
64.                
65.             HSSFRow row;  
66.             HSSFCell cell;  
67.                 
68.             // 设置这些样式  
69.             HSSFFont font = workbook.createFont();  
70.             font.setFontName(HSSFFont.FONT_ARIAL);//字体  
71.             font.setFontHeightInPoints((short) 16);//字号   
72.             font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗  
73.             //font.setColor(HSSFColor.BLUE.index);//颜色  
74.                 
75.             HSSFCellStyle cellStyle= workbook.createCellStyle(); //设置单元格样式  
76.             cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);  
77.             cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
78.             cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER );  
79.             cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
80.             cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
81.             cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);  
82.             cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);  
83.             cellStyle.setFont(font);  
84.                 
85.             //产生表格标题行         
86.             row = sheet.createRow(0);  
87.             row.setHeightInPoints(20);  
88.             for (int i = 0; i < headers.size(); i++) {   
89.                 HSSFRichTextString text = new HSSFRichTextString(headers.get(i).toString());    
90.                 cell = row.createCell(i);  
91.                 cell.setCellValue(text);   
92.                 cell.setCellStyle(cellStyle);  
93.             }    
94.                 
95.                 
96.             cellStyle= workbook.createCellStyle();   
97.             cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);  
98.             cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
99.             cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
100.             cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);  
101.             cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);  
102.             cellStyle.setDataFormat((short)0x31);//设置显示格式，避免点击后变成科学计数法了  
103.             //cellStyle.setWrapText(true);//设置自动换行  
104.             Map map;  
105.             //遍历集合数据，产生数据行    
106.             for (int i=0; i <datas.size(); i++) {   
107.                 row=sheet.createRow((i+1));  
108.                 row.setHeightInPoints(20);  
109.                 map = datas.get(i);  
110.     
111.                 for(int j=0;j<map.size();j++) {  
112.                      cell = row.createCell(j);  
113.                      cell.setCellStyle(cellStyle);  
114.     
115.                      cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
116.                      if(map.get(j) != null) {  
117.                          cell.setCellValue(new HSSFRichTextString(map.get(j).toString()));   
118.                      }else{  
119.                          cell.setCellValue(new HSSFRichTextString(""));       
120.                     }  
121.                 }  
122.             }     
123.                 
124.             for (int i = 0; i < headers.size(); i++) {   
125.                 sheet.autoSizeColumn((short)i);  
126.             }  
127.                
128.             rs.reset();  
129.             rs.setContentType("multipart/form-data"); //自动识别  
130.             rs.setHeader("Content-Disposition","attachment;filename=data.xls");  
131.             //文件流输出到rs里  
132.             workbook.write(rs.getOutputStream());  
133.             rs.getOutputStream().flush();  
134.             rs.getOutputStream().close();  
135.         } catch (Exception e) {    
136.             System.out.println("#Error ["+e.getMessage()+"] ");  
137.         }   
138.         System.out.println("["+sheets+"] 创建成功...");  
139.         System.out.println("");  
140.     }     
141.        
142.        
143.     /* 
144.      * 通用的Excel文件创建方法 
145.      *    path:保存路径: C:/xls/统计报表.xls 
146.      *   title:首行标题: 2015年度统计报表 
147.      *  sheets:sheet的tab标签页说明: 15年度报表 
148.      * headers:表头：List存放表头  编号、姓名、备注 
149.      *   datas:数据行：list存放实体数据，map存放具体每一行数据，和headers对应。 
150.      */  
151.     public static void ExpExs(String path,String title,String sheets,List headers,List<Map> datas){  
152.         try {   
153.             if(sheets== null || "".equals(sheets)){ sheets = "sheet"; }  
154.                 
155.             boolean isExist = new File(path).exists();  
156.             if(!isExist){  
157.                 HSSFWorkbook workbook = new HSSFWorkbook();  
158.                 HSSFSheet sheet = workbook.createSheet(sheets);  
159.                     
160.                 FileOutputStream out = new FileOutputStream(new File(path));  
161.                 workbook.write(out);  
162.                 out.flush();  
163.                 out.close();  
164.             }  
165.             FileInputStream file = new FileInputStream(new File(path));  
166.             HSSFWorkbook workbook = new HSSFWorkbook(file);  
167.                 
168.             HSSFSheet sheet = null;  
169.             if(!isExist){  
170.                 sheet = workbook.getSheetAt(0);  
171.             }else{  
172.                 if(workbook.getSheet(sheets) == null){  
173.                     sheet = workbook.createSheet(sheets); //+workbook.getNumberOfSheets()  
174.                 }else{  
175.                     System.out.println("文件：["+path+"] ["+sheets+"] 已经存在...");  
176.                     System.out.println("");  
177.                     return;  
178.                 }  
179.             }  
180.             HSSFRow row;  
181.             HSSFCell cell;  
182.                 
183.             // 设置这些样式  
184.             HSSFFont font = workbook.createFont();  
185.             font.setFontName(HSSFFont.FONT_ARIAL);//字体  
186.             font.setFontHeightInPoints((short) 16);//字号   
187.             font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗  
188.             //font.setColor(HSSFColor.BLUE.index);//颜色  
189.                 
190.             HSSFCellStyle cellStyle= workbook.createCellStyle(); //设置单元格样式  
191.             cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);  
192.             cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
193.             cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER );  
194.             cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
195.             cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
196.             cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);  
197.             cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);  
198.             cellStyle.setFont(font);  
199.                 
200.             //产生表格标题行         
201.             row = sheet.createRow(0);  
202.             row.setHeightInPoints(20);  
203.             for (int i = 0; i < headers.size(); i++) {   
204.                 HSSFRichTextString text = new HSSFRichTextString(headers.get(i).toString());    
205.                 cell = row.createCell(i);  
206.                 cell.setCellValue(text);   
207.                 cell.setCellStyle(cellStyle);  
208.             }    
209.                 
210.                 
211.             cellStyle= workbook.createCellStyle();   
212.             cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);  
213.             cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
214.             cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
215.             cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);  
216.             cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);  
217.             cellStyle.setDataFormat((short)0x31);//设置显示格式，避免点击后变成科学计数法了  
218.             //cellStyle.setWrapText(true);//设置自动换行  
219.             Map map;  
220.             //遍历集合数据，产生数据行    
221.             for (int i=0; i <datas.size(); i++) {   
222.                 row=sheet.createRow((i+1));  
223.                 row.setHeightInPoints(20);  
224.                 map = datas.get(i);  
225.     
226.                 for(int j=0;j<map.size();j++) {  
227.                      cell = row.createCell(j);  
228.                      cell.setCellStyle(cellStyle);  
229.     
230.                      cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
231.                      if(map.get(j) != null) {  
232.                          cell.setCellValue(new HSSFRichTextString(map.get(j).toString()));   
233.                      }else{  
234.                          cell.setCellValue(new HSSFRichTextString(""));       
235.                     }  
236.                 }  
237.             }     
238.                 
239.             for (int i = 0; i < headers.size(); i++) {   
240.                 sheet.autoSizeColumn((short)i);  
241.             }  
242.                 
243.             FileOutputStream out = new FileOutputStream(new File(path));  
244.             workbook.write(out);  
245.             out.flush();  
246.             out.close();  
247.                 
248.             /* 
249.             HSSFRow row = sheet.createRow(sheets); 
250.             HSSFCell cell = null; 
251.             cell=row.createCell(sheets); 
252.             cell.setCellValue(new HSSFRichTextString("-["+sheets+"]-")); 
253.             sheets=sheets+2;//中间空一行 
254.             row=sheet.createRow(sheets); 
255.             */  
256.                 
257.         } catch (Exception e) {    
258.             System.out.println("#Error ["+e.getMessage()+"] ");  
259.         }   
260.         System.out.println("文件：["+path+"] ["+sheets+"] 创建成功...");  
261.         System.out.println("");  
262.     }  
263. }</span>