导出excel多种类对应工具类整理（util）

1.导出无表头excel 文件单个工作表(sheet)，【fileName是标题名，titleList是列名，list就是列的内容了】

[java] view plain copy

1. /** 
2.      * 导出无表头excel 文件单个工作表(sheet) 
3.      *  
4.      * @param fileName 
5.      * @param titleList 
6.      * @param list 
7.      * @param response 
8.      */  
9.     public static void exportNoHeadExcel(String fileName, String[] titleList,  
10.             List list, HttpServletResponse response) {  
11.         SimpleDateFormat df = new java.text.SimpleDateFormat("yyyyMMdd");  
12.         String todayStr = df.format(new Date());  
13.         OutputStream os = null;  
14.         try {  
15.             os = response.getOutputStream();  
16.             String localFileName = fileName;  
17.             fileName = java.net.URLEncoder.encode(fileName, "UTF-8");// 处理中文文件名的问题  
18.             fileName = new String(fileName.getBytes("UTF-8"), "GBK");// 处理中文文件名的问题  
19.             response.setContentType("application/vnd.ms-excel;");  
20.             response.setHeader("Content-disposition", "attachment; filename=\""  
21.                     + fileName + "_" + todayStr + ".xls\"");  
22.             // 开始写入excel  
23.             // 字段字体  
24.             jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(  
25.                     WritableFont.COURIER, 10, WritableFont.NO_BOLD, true);  
26.             jxl.write.WritableCellFormat wcfFC1 = new jxl.write.WritableCellFormat(  
27.                     wfc1);  
28.             wcfFC1.setAlignment(jxl.format.Alignment.CENTRE);  
29.             wcfFC1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);  
30.             // 结果字体  
31.             jxl.write.WritableCellFormat wcfFC2 = new jxl.write.WritableCellFormat();  
32.             wcfFC2.setAlignment(jxl.format.Alignment.CENTRE);  
33.             wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);  
34.             WritableWorkbook wbook = Workbook.createWorkbook(os);  
35.             // 写sheet名称  
36.             WritableSheet wsheet = wbook.createSheet(localFileName, 0);  
37.             for (int m = 0; m < titleList.length; m++) {  
38.                 wsheet.setColumnView(m, 30);  
39.             }  
40.             // 加入字段名  
41.             for (int n = 0; n < titleList.length; n++) {  
42.                 wsheet.addCell(new jxl.write.Label(n, 0, titleList[n], wcfFC1));  
43.             }  
44.             // 写入流中  
45.             int row = 0;  
46.             for (int r = 0; r < list.size(); r++) {  
47.                 Object[] obj = (Object[]) list.get(r);  
48.                 for (int x = 0; x < titleList.length; x++) {  
49.                     wsheet.addCell(new jxl.write.Label(x, row + 1,  
50.                             obj[x] == null ? " " : obj[x].toString(), wcfFC1));  
51.                 }  
52.                 row++;  
53.                 if (row % 60000 == 0) {  
54.                     row = 0;  
55.                     // 写sheet名称  
56.                     wsheet = wbook.createSheet(localFileName, 0);  
57.                     for (int m = 0; m < titleList.length; m++) {  
58.                         wsheet.setColumnView(m, 30);  
59.                     }  
60.                     // 加入字段名  
61.                     for (int n = 0; n < titleList.length; n++) {  
62.                         wsheet.addCell(new jxl.write.Label(n, 0, titleList[n],  
63.                                 wcfFC1));  
64.                     }  
65.                 }  
66.             }  
67.             wbook.write();  
68.             wbook.close();  
69.             os.flush();  
70.         } catch (Exception e) {  
71.             e.printStackTrace();  
72.         } finally {  
73.             if (os == null) {  
74.                 Log.info("os is null");  
75.             } else {  
76.                 try {  
77.                     os.close();  
78.                     os = null;  
79.                 } catch (IOException e) {  
80.                     e.printStackTrace();  
81.                 }  
82.             }  
83.         }  
84.     }  
85.       

2.首先导出无表头excel 文件带多个工作表(sheet)，【sheetList存放的就是Map包含fileName是标题名，titleList是列名，list就是列的内容】

[java] view plain copy

1. /** 
2.      * 导出无表头excel文件 
3.      *  
4.      * @param fileName 
5.      * @param titleList 
6.      * @param list 
7.      * @param response 
8.      */  
9.     public static void exportNoHeadExcel(List<Map<String,Object>> sheetList, HttpServletResponse response) {  
10.         SimpleDateFormat df = new java.text.SimpleDateFormat("yyyyMMdd");  
11.         String todayStr = df.format(new Date());  
12.         OutputStream os = null;  
13.         try {  
14.             os = response.getOutputStream();  
15.             WritableWorkbook wbook = Workbook.createWorkbook(os);  
16.             for(int i=0;i<sheetList.size();i++){  
17.                 Map<String,Object> map=sheetList.get(i);  
18.                 String fileName=(String) map.get("fileName");  
19.                 String[] titleList=(String[]) map.get("titleList");  
20.                 List list=(List) map.get("list");  
21.                 String localFileName = fileName;  
22.                 fileName = java.net.URLEncoder.encode(fileName, "UTF-8");// 处理中文文件名的问题  
23.                 fileName = new String(fileName.getBytes("UTF-8"), "GBK");// 处理中文文件名的问题  
24.                 response.setContentType("application/vnd.ms-excel;");  
25.                 response.setHeader("Content-disposition", "attachment; filename=\""  
26.                         + fileName + "_" + todayStr + ".xls\"");  
27.                 // 开始写入excel  
28.                 // 字段字体  
29.                 jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(  
30.                         WritableFont.COURIER, 10, WritableFont.NO_BOLD, true);  
31.                 jxl.write.WritableCellFormat wcfFC1 = new jxl.write.WritableCellFormat(  
32.                         wfc1);  
33.                 wcfFC1.setAlignment(jxl.format.Alignment.CENTRE);  
34.                 wcfFC1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);  
35.                 // 结果字体  
36.                 jxl.write.WritableCellFormat wcfFC2 = new jxl.write.WritableCellFormat();  
37.                 wcfFC2.setAlignment(jxl.format.Alignment.CENTRE);  
38.                 wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);  
39.                 // 写sheet名称  
40.                 WritableSheet wsheet = wbook.createSheet(localFileName,i);  
41.                 for (int m = 0; m < titleList.length; m++) {  
42.                     wsheet.setColumnView(m, 30);  
43.                 }  
44.                 // 加入字段名  
45.                 for (int n = 0; n < titleList.length; n++) {  
46.                     wsheet.addCell(new jxl.write.Label(n, 0, titleList[n], wcfFC1));  
47.                 }  
48.                 // 写入流中  
49.                 int row = 0;  
50.                 for (int r = 0; r < list.size(); r++) {  
51.                     Object[] obj = (Object[]) list.get(r);  
52.                     for (int x = 0; x < titleList.length; x++) {  
53.                         wsheet.addCell(new jxl.write.Label(x, row + 1,  
54.                                 obj[x] == null ? " " : obj[x].toString(), wcfFC1));  
55.                     }  
56.                     row++;  
57.                     if (row % 60000 == 0) {  
58.                         row = 0;  
59.                         // 写sheet名称  
60.                         wsheet = wbook.createSheet(localFileName, 0);  
61.                         for (int m = 0; m < titleList.length; m++) {  
62.                             wsheet.setColumnView(m, 30);  
63.                         }  
64.                         // 加入字段名  
65.                         for (int n = 0; n < titleList.length; n++) {  
66.                             wsheet.addCell(new jxl.write.Label(n, 0, titleList[n],  
67.                                     wcfFC1));  
68.                         }  
69.                     }  
70.                 }  
71.             }     
72.             wbook.write();  
73.             wbook.close();  
74.             os.flush();  
75.         } catch (Exception e) {  
76.             e.printStackTrace();  
77.         } finally {  
78.             if (os == null) {  
79.                 Log.info("os is null");  
80.             } else {  
81.                 try {  
82.                     os.close();  
83.                     os = null;  
84.                 } catch (IOException e) {  
85.                     e.printStackTrace();  
86.                 }  
87.             }  
88.         }  
89.     }  

3.导出excel文件带标题【fileName是标题名，titleList是列名，list就是列的内容了】

[java] view plain copy

1. /** 
2.      * 导出excel 文件  带标题 
3.      *  
4.      * @param fileName  
5.      * @param titleList 
6.      * @param list 
7.      * @param response 
8.      */  
9.     public static void exportWithHeadExcel(String fileName, String[] titleList,  
10.             List list, HttpServletResponse response) {  
11.         Date now = new Date();  
12.         SimpleDateFormat dateformat = new java.text.SimpleDateFormat(  
13.                 "yyyy年MM月dd日HH时mm分");  
14.         SimpleDateFormat df = new java.text.SimpleDateFormat("yyyyMMdd");  
15.         String todayStr = df.format(new Date());  
16.         String today = dateformat.format(now);  
17.         OutputStream os = null;  
18.         try {  
19.             os = response.getOutputStream();  
20.             String localFileName = fileName;  
21.             fileName = java.net.URLEncoder.encode(fileName, "UTF-8");// 处理中文文件名的问题  
22.             fileName = new String(fileName.getBytes("UTF-8"), "GBK");// 处理中文文件名的问题  
23.             response.setContentType("application/vnd.ms-excel;");  
24.             response.setHeader("Content-disposition", "attachment; filename=\""  
25.                     + fileName + "_" + todayStr + ".xls\"");  
26.             // 开始写入excel  
27.             // 加标题  
28.             // 标题字体  
29.             jxl.write.WritableFont wfc = new jxl.write.WritableFont(  
30.                     WritableFont.COURIER, 18, WritableFont.NO_BOLD, false);  
31.             jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(  
32.                     wfc);  
33.             wcfFC.setAlignment(jxl.format.Alignment.CENTRE);  
34.             wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);  
35.             // 字段字体  
36.             jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(  
37.                     WritableFont.COURIER, 10, WritableFont.NO_BOLD, false);  
38.             jxl.write.WritableCellFormat wcfFC1 = new jxl.write.WritableCellFormat(  
39.                     wfc1);  
40.             wcfFC1.setAlignment(jxl.format.Alignment.CENTRE);  
41.             wcfFC1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);  
42.             // 结果字体  
43.             jxl.write.WritableCellFormat wcfFC2 = new jxl.write.WritableCellFormat();  
44.             wcfFC2.setAlignment(jxl.format.Alignment.CENTRE);  
45.             wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);  
46.             WritableWorkbook wbook = Workbook.createWorkbook(os);  
47.             // 写sheet名称  
48.             WritableSheet wsheet = wbook.createSheet(localFileName, 0);  
49.             int i = 3;  
50.             for (int m = 0; m < titleList.length; m++) {  
51.                 wsheet.setColumnView(m, 30);  
52.             }  
53.             // 加入字段名  
54.             for (int n = 0; n < titleList.length; n++) {  
55.                 wsheet.addCell(new jxl.write.Label(n, 3, titleList[n], wcfFC1));  
56.             }  
57.             // 加入标题  
58.             wsheet.mergeCells(0, 0, i - 1, 0);  
59.             wsheet.addCell(new Label(0, 0, localFileName, wcfFC));  
60.             // 加入打印时间  
61.             wsheet.addCell(new Label(i - 2, 1, "打印日期:"));  
62.             wsheet.addCell(new Label(i - 1, 1, today));  
63.             // 写入流中  
64.             int row = 0;  
65.             for (int r = 0; r < list.size(); r++) {  
66.                 Object[] obj = (Object[]) list.get(r);  
67.                 for (int x = 0; x < titleList.length; x++) {  
68.                     wsheet.addCell(new jxl.write.Label(x, row + 4,  
69.                             obj[x] == null ? " " : obj[x].toString(), wcfFC1));  
70.                 }  
71.                 row++;  
72.                 if (row % 60000 == 0) {  
73.                     row = 0;  
74.                     // 写sheet名称  
75.                     wsheet = wbook.createSheet(localFileName, 0);  
76.                     i = 3;  
77.                     for (int m = 0; m < titleList.length; m++) {  
78.                         wsheet.setColumnView(m, 30);  
79.                     }  
80.                     // 加入字段名  
81.                     for (int n = 0; n < titleList.length; n++) {  
82.                         wsheet.addCell(new jxl.write.Label(n, 3, titleList[n],  
83.                                 wcfFC1));  
84.                     }  
85.                     // 加入标题  
86.                     wsheet.mergeCells(0, 0, i - 1, 0);  
87.                     wsheet.addCell(new Label(0, 0, localFileName, wcfFC));  
88.                     // 加入打印时间  
89.                     wsheet.addCell(new Label(i - 2, 1, "打印日期:"));  
90.                     wsheet.addCell(new Label(i - 1, 1, today));  
91.                 }  
92.             }  
93.             wbook.write();  
94.             wbook.close();  
95.             os.flush();  
96.         } catch (Exception e) {  
97.             e.printStackTrace();  
98.         } finally {  
99.             if (os == null) {  
100.                 Log.info("os is null");  
101.             } else {  
102.                 try {  
103.                     os.close();  
104.                     os = null;  
105.                 } catch (IOException e) {  
106.                     e.printStackTrace();  
107.                 }  
108.             }  
109.         }  
110.     }  

目前大概整理了这3个，以后用到还会整理更新，今天就先到在这里吧..待续...