java导出多sheet的excel

excel工具类，适合单sheet或多sheet的excel表格导出

package Util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*;

import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.util.LinkedHashMap; import java.util.List;

/** * Excel文件工具类 * * @author : Mr.S * @Version : 1.01 */ @SuppressWarnings("ALL") public class ExcelUtil { /** * 创建excel文档， * * @param list 数据 * @param keys list中map的key数组集合 * @param columnNames excel的列名 */ public static Workbook createWorkBook(List<LinkedHashMap<String, Object>> list, String sheetName, String[] keys, String columnNames[]) { // 创建excel工作簿 Workbook wb = new HSSFWorkbook(); // 创建第一个sheet（页），并命名 Sheet sheet = wb.createSheet(sheetName); // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。 for (int i = 0; i < keys.length; i++) { sheet.setColumnWidth((short) i, (short) (35.7 * 150)); }

1    // 创建两种单元格格式
2    CellStyle columnStyle = wb.createCellStyle();
3    CellStyle valueStyle = wb.createCellStyle();
4
5    // 创建两种字体
6    Font columnFont = wb.createFont();
7    Font valueFont = wb.createFont();
8
9    setFontParameters(columnFont,true);//加粗
10    setFontParameters(valueFont, false);
11
12    setCellStyle(columnStyle, columnFont);
13    setCellStyle(columnStyle, valueFont);
14
15    setColumnName(sheet,columnNames,columnStyle);
16    setColumnValue(sheet,list,keys,valueStyle);
17
18    return wb;
19}
20
21// 创建字体样式
22public static void setFontParameters(Font font,boolean isBold){
23    font.setFontHeightInPoints((short) 10);
24    font.setColor(IndexedColors.BLACK.getIndex());
25    if(isBold) {
26        font.setBoldweight(Font.BOLDWEIGHT\_BOLD);
27    }
28}
29
30// 设置元格的样式
31public static void setCellStyle(CellStyle cellStyle,Font font){
32    cellStyle.setFont(font);
33    cellStyle.setBorderLeft(CellStyle.BORDER\_THIN);
34    cellStyle.setBorderRight(CellStyle.BORDER\_THIN);
35    cellStyle.setBorderTop(CellStyle.BORDER\_THIN);
36    cellStyle.setBorderBottom(CellStyle.BORDER\_THIN);
37    cellStyle.setAlignment(CellStyle.ALIGN\_CENTER);
38}
39
40public static void setColumnName(Sheet sheet,String columnNames\[\],CellStyle cellStyle){
41    // 创建第一行
42    Row row = sheet.createRow((short) 0);
43    for (int i = 0; i < columnNames.length; i++) {
44        Cell cell = row.createCell(i);
45        cell.setCellValue(columnNames\[i\]);
46        cell.setCellStyle(cellStyle);
47    }
48}
49
50private static void setColumnValue(Sheet sheet, List<LinkedHashMap<String, Object>> list, String\[\] keys, CellStyle valueStyle) {
51    for (int i = 0; i < list.size(); i++) {
52        // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
53        // 创建一行，在页sheet上
54        Row row1 = sheet.createRow(i + 1);
55        // 在row行上创建一个方格
56        for (int j = 0; j < keys.length; j++) {
57            Cell cell = row1.createCell(j);
58            cell.setCellValue(list.get(i).get(keys\[j\]) == null ? " " : list.get(i).get(keys\[j\]).toString());
59            cell.setCellStyle(valueStyle);
60        }
61    }
62}
63
64/\*\*
65 \* 创建包含多个Sheet的excel表格
66 \*
67 \* @param list
68 \* @param fileName
69 \* @param sheetName
70 \* @param keys
71 \* @param columnNames
72 \* @return
73 \*/
74public static Workbook createWorkBookForMoreSheet(List<List<LinkedHashMap<String, Object>>> list, String fileName, String sheetName\[\], List<String\[\]> keys, List<String\[\]> columnNames) {
75    // 创建excel工作簿
76    Workbook wb = new HSSFWorkbook();
77    for (int n = 0; n < list.size(); n++) {
78        // 创建第一个sheet（页），并命名
79        Sheet sheet = wb.createSheet(sheetName\[n\]);
80        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
81        for (int i = 0; i < keys.get(n).length; i++) {
82            sheet.setColumnWidth((short) i, (short) (35.7 \* 150));
83        }
84
85        // 创建第一行
86        Row row = sheet.createRow((short) 0);
87
88        // 创建两种单元格格式
89        CellStyle columnStyle = wb.createCellStyle();
90        CellStyle valueStyle = wb.createCellStyle();
91
92        // 创建两种字体
93        Font columnFont = wb.createFont();
94        Font valueFont = wb.createFont();
95
96        setFontParameters(columnFont,true);
97        setFontParameters(valueFont, false);
98
99        setCellStyle(columnStyle, columnFont);
100        setCellStyle(columnStyle, valueFont);
101
102        setColumnNameForSheets(n, sheet, columnNames, columnStyle);
103        setColumnValueForSheets(n,sheet,list,keys,valueStyle);
104
105    }
106    return wb;
107}
108
109private static void setColumnValueForSheets(int n, Sheet sheet, List<List<LinkedHashMap<String, Object>>> list, List<String\[\]> keys, CellStyle valueStyle) {
110    for (int i = 0; i < list.get(n).size(); i++) {
111        // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
112        // 创建一行，在页sheet上
113        Row row1 = sheet.createRow(i + 1);
114        // 在row行上创建一个方格
115        for (int j = 0; j < keys.get(n).length; j++) {
116            Cell cell = row1.createCell(j);
117            cell.setCellValue(list.get(n).get(i).get(keys.get(n)\[j\]) == null ? " " : list.get(n).get(i).get(keys.get(n)\[j\]).toString());
118            cell.setCellStyle(valueStyle);
119        }
120    }
121}
122
123public static void setColumnNameForSheets(int n,Sheet sheet,  List<String\[\]> columnNames,CellStyle cellStyle){
124    // 创建第一行
125    Row row = sheet.createRow((short) 0);
126    for (int i = 0; i < columnNames.get(n).length; i++) {
127        Cell cell = row.createCell(i);
128        cell.setCellValue(columnNames.get(n)\[i\]);
129        cell.setCellStyle(cellStyle);
130    }
131}
132
133/\*\*
134 \* @param data        数据列表
135 \* @param fileName    文件名
136 \* @param keys        数据列表中map的key
137 \* @param columnNames 列名
138 \* @param request
139 \* @param response
140 \* @throws IOException
141 \*/
142public static void downExcel(List<LinkedHashMap<String, Object>> data, String fileName,
143                             String\[\] keys, String columnNames\[\],
144                             HttpServletRequest request, HttpServletResponse response) throws IOException {
145
146    ByteArrayOutputStream os = new ByteArrayOutputStream();
147    try {
148        createWorkBook(data, fileName, keys, columnNames).write(os);
149    } catch (IOException e) {
150        e.printStackTrace();
151    }
152    // 设置response参数，可以打开下载页面
153    setResponseParameters(response,fileName);
154
155    ServletOutputStream out = response.getOutputStream();
156    BufferedInputStream bis = null;
157    BufferedOutputStream bos = null;
158
159    writeWorkBook(os,bis,bos,out);
160}
161
162/\*\*
163 \* 导出包含多个sheet的excel表格
164 \*
165 \* @param data
166 \* @param fileName
167 \* @param sheetName
168 \* @param keys
169 \* @param columnNames
170 \* @param response
171 \* @throws IOException
172 \*/
173public static void downExcelMoreSheet(List data, String fileName, String sheetName\[\],
174                                      List keys, List columnNames, HttpServletResponse response) throws IOException {
175
176    ByteArrayOutputStream os = new ByteArrayOutputStream();
177    try {
178        ExcelUtil.createWorkBookForMoreSheet(data, fileName, sheetName, keys, columnNames).write(os);
179    } catch (IOException e) {
180        e.printStackTrace();
181    }
182
183    // 设置response参数，可以打开下载页面
184    setResponseParameters(response,fileName);
185
186    ServletOutputStream out = response.getOutputStream();
187    BufferedInputStream bis = null;
188    BufferedOutputStream bos = null;
189
190    writeWorkBook(os,bis,bos,out);
191
192}
193
194private static void writeWorkBook(ByteArrayOutputStream os,BufferedInputStream bis, BufferedOutputStream bos,ServletOutputStream out) throws IOException {
195    try {
196        byte\[\] content = os.toByteArray();
197        InputStream is = new ByteArrayInputStream(content);
198        bis = new BufferedInputStream(is);
199        bos = new BufferedOutputStream(out);
200        byte\[\] buff = new byte\[2048\];
201        int bytesRead;
202        // Simple read/write loop.
203        while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
204            bos.write(buff, 0, bytesRead);
205        }
206    } catch (final IOException e) {
207        throw e;
208    } finally {
209        if (bis != null)
210            bis.close();
211        if (bos != null)
212            bos.close();
213    }
214}
215
216private static void setResponseParameters(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
217    response.reset();
218    response.setContentType("application/vnd.ms-excel;charset=utf-8");
219    response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
220}

}

使用示例：

单个sheet data = (List<LinkedHashMap<String, Object>>) xxxxxxService.getDataByLast5Years(SITE_ID); String fileName = "地域分布分析"; String columnNames[] = {"站点名称", "日期", "地区", "独立访客(UV)", "浏览次数(PV)", "IP数", "平均访问时长", "平均访问深度","跳出率%"};//列名 String keys[] = {"name", "date", "region", "uniqueVisitor", "pv", "ip", "averageVisitTime", "averageVisitDepth","bounceRate"};//map中的key ExcelUtil.downExcel(data, fileName, keys, columnNames, request, response); // 执行excel下载

多个sheet String fileName = "网站概况"; String sheetName[] = {"网站概况", "搜索引擎", "来路域名", "地域分布分析"};

siteData = (List<LinkedHashMap<String, Object>>) countSiteAnalyzeService.getDataByLastYear(String.valueOf(SITE_ID)); searchEngineData = (List<LinkedHashMap<String, Object>>) countSearchEngineService.getDataByLastYear(SITE_ID); llDomainData = (List<LinkedHashMap<String, Object>>) countSourceDomainService.getDataByLastYear(SITE_ID); regionData = (List<LinkedHashMap<String, Object>>) countRegionAnalyzeService.getDataByLastYear(SITE_ID);

List data = new ArrayList(); data.add(siteData); data.add(searchEngineData); data.add(llDomainData); data.add(regionData);

List columnName = new ArrayList(); String siteColumnNames[] = {"站点", "日期", "IP", "独立访客(UV)", "浏览次数(PV)", "新访客数", "访问次数", "平均访问时长", "平均访问深度", "平均浏览页数"};//列名 String searchEngColumnNames[] = {"站点", "搜索引擎名称", "网址", "日期", "IP", "独立访客(UV)", "浏览次数(PV)", "新独立访客", "访问次数"};//列名 String domainColumnNames[] = {"站点", "类型", "日期", "IP", "独立访客(UV)", "浏览次数(PV)", "新访客数", "访问次数"};//列名 String regionColumnNames[] = {"站点", "日期", "地域", "IP", "独立访客(UV)", "浏览次数(PV)", "访问次数", "平均访问时长", "平均访问深度","跳出率%"};//列名 columnName.add(siteColumnNames); columnName.add(searchEngColumnNames); columnName.add(domainColumnNames); columnName.add(regionColumnNames);

List keys = new ArrayList(); String siteKeys[] = {"name", "date", "ip", "uniqueVisitor", "pv", "newVisitor", "visitCount", "averageVisitTime", "averageVisitDepth", "averageVisitPages"};//map中的key String searchEngKeys[] = {"name", "searchEngineName", "url", "date", "ip", "uniqueVisitor", "pv", "newVisitor", "visitCount"};//map中的key String domainKeys[] = {"name", "sourceType", "date", "ip", "uniqueVisitor", "pv", "newVisitor", "visitCount"};//map中的key String regionKeys[] = {"name", "date", "region", "ip", "uniqueVisitor", "pv", "visitCount", "averageVisitTime", "averageVisitDepth","bounceRate"};//map中的key keys.add(siteKeys); keys.add(searchEngKeys); keys.add(domainKeys); keys.add(regionKeys);

ExcelUtil.downExcelMoreSheet(data, fileName, sheetName, keys, columnName, response);