POM 依赖
3.10-FINAL
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.1</version>
</dependency>
工具类
package com.picc.job.utils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import javax.servlet.http.HttpServletResponse;
/**
* Created by Cheung on 2017/12/19.
*
* Apache POI操作Excel对象 HSSF:操作Excel 2007之前版本(.xls)格式,生成的EXCEL不经过压缩直接导出
* XSSF:操作Excel 2007及之后版本(.xlsx)格式,内存占用高于HSSF SXSSF:从POI3.8
* beta3开始支持,基于XSSF,低内存占用,专门处理大数据量(建议)。
*
* 注意: 值得注意的是SXSSFWorkbook只能写(导出)不能读(导入)
*
* 说明: .xls格式的excel(最大行数65536行,最大列数256列) .xlsx格式的excel(最大行数1048576行,最大列数16384列)
*/
@Slf4j
public class ExcelUtil {
public static final String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日";// 默认日期格式
public static final int DEFAULT_COLUMN_WIDTH = 17;// 默认列宽
private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
/**
* 导出Excel(.xlsx)格式
*
* @param linkMap
* 表头信息
* @param dataArray
* 数据数组
* @param filePath
* 文件路径
* @param excelTitle
* 表格标题
* @throws IOException
*/
public static Map<String, Object> exportExcel(LinkedHashMap<String, String> linkMap, JSONArray dataArray, String excelTitle,
int type) throws IOException {
String datePattern = DEFAULT_DATE_PATTERN;
int minBytes = DEFAULT_COLUMN_WIDTH;
Map<String, Object> map = new HashMap<String, Object>();
/**
* 声明一个工作薄
*/
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);// 大于1000行时会把之前的行写入硬盘
workbook.setCompressTempFiles(true);
// 表头1样式
CellStyle title1Style = workbook.createCellStyle();
title1Style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
title1Style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
Font titleFont = workbook.createFont();// 字体
titleFont.setFontHeightInPoints((short) 20);
titleFont.setBoldweight((short) 700);
title1Style.setFont(titleFont);
// head样式
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headerStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置颜色
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 前景色纯色填充
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
Font headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 12);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
// 单元格样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
Font cellFont = workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(cellFont);
/**
* 生成一个(带名称)表格
*/
SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(excelTitle);
/*
* if(type == 1){ sheet.createFreezePane(0, 2, 0, 2);// (单独)冻结前两行 }else
* if(type == 2){ sheet.createFreezePane(0, 1, 0, 1);// (单独)冻结第一行 }
*/
/**
* 生成head相关信息+设置每列宽度
*/
int[] colWidthArr = new int[linkMap.size()];// 列宽数组
String[] headKeyArr = new String[linkMap.size()];// headKey数组
String[] headValArr = new String[linkMap.size()];// headVal数组
int i = 0;
for (Map.Entry<String, String> entry : linkMap.entrySet()) {
headKeyArr[i] = entry.getKey();
headValArr[i] = entry.getValue();
int bytes = headKeyArr[i].getBytes().length;
colWidthArr[i] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(i, colWidthArr[i] * 256);// 设置列宽
i++;
}
/**
* 遍历数据集合,产生Excel行数据
*/
int rowIndex = 0;
if (dataArray.size() == 0) {
if (type == 1) {
SXSSFRow title1Row = (SXSSFRow) sheet.createRow(0);// title1行
title1Row.createCell(0).setCellValue(excelTitle);
title1Row.getCell(0).setCellStyle(title1Style);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, linkMap.size() - 1));// 合并单元格
SXSSFRow headerRow = (SXSSFRow) sheet.createRow(1);// head行
for (int j = 0; j < headValArr.length; j++) {
headerRow.createCell(j).setCellValue(headValArr[j]);
headerRow.getCell(j).setCellStyle(headerStyle);
}
} else if (type == 2) {
SXSSFRow headerRow = (SXSSFRow) sheet.createRow(0);// head行
for (int j = 0; j < headValArr.length; j++) {
headerRow.createCell(j).setCellValue(headValArr[j]);
headerRow.getCell(j).setCellStyle(headerStyle);
}
}
} else {
for (Object obj : dataArray) {
// 生成title+head信息
if (rowIndex == 0) {
SXSSFRow title1Row = (SXSSFRow) sheet.createRow(0);// title1行
title1Row.createCell(0).setCellValue(excelTitle);
title1Row.getCell(0).setCellStyle(title1Style);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, linkMap.size() - 1));// 合并单元格
SXSSFRow headerRow = (SXSSFRow) sheet.createRow(1);// head行
for (int j = 0; j < headValArr.length; j++) {
headerRow.createCell(j).setCellValue(headValArr[j]);
headerRow.getCell(j).setCellStyle(headerStyle);
}
rowIndex = 2;
}
JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
// 生成数据
SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);// 创建行
for (int k = 0; k < headKeyArr.length; k++) {
SXSSFCell cell = (SXSSFCell) dataRow.createCell(k);// 创建单元格
Object o = jo.get(headKeyArr[k]);
String cellValue = "";
if (o == null) {
cellValue = "";
} else if (o instanceof Date) {
cellValue = new SimpleDateFormat(datePattern).format(o);
} else if (o instanceof Float || o instanceof Double) {
cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
} else {
cellValue = o.toString();
}
cell.setCellValue(cellValue);
cell.setCellStyle(cellStyle);
}
rowIndex++;
}
}
// ByteArrayOutputStream bos = new ByteArrayOutputStream();
// workbook.write(bos);
// return new ByteArrayInputStream(bos.toByteArray());
map.put("sheet",sheet);
map.put("workbook",workbook);
return map;
}
/**
* 合并单元格
*
* @param sheet
* 表格
* @param firstRow
* 开始行
* @param lastRow
* 结束行
* @param firstCol
* 开始列
* @param lastCol
* 结束列
*/
public static void mergeCell(SXSSFSheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));// 合并单元格
}
/**
* 把数据写入Excel
*
* @param workbook
* 工作簿
* @param filePath
* 表格路径
* @throws FileNotFoundException
*/
public static void writeExcel(SXSSFWorkbook workbook, HttpServletResponse response, String fileName)
throws FileNotFoundException {
try {
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
workbook.write(response.getOutputStream());
workbook.dispose();// 释放workbook所占用的所有windows资源
} catch (IOException e) {
logger.error("项目名:{},类名:{},方法名():{},中文描述:{},报错信息:{},",
"cronjob","ExcelUtil","writeExcel","错误原因",e);
}
}
/**
* @param map
* 表头信息
* @param dataArray
* 数据数组
* @param filePath
* 文件路径
* @param excelTitle
* 表格标题
* @Param type 1:有表头 2:无表头
* 多sheet页导出
**/
public static Map<String,Object> exportExcel2(List<LinkedHashMap<String, String>> linkMap, List<JSONArray> dataArray,
List<String> excelTitle, int type) throws IOException {
String datePattern = DEFAULT_DATE_PATTERN;
int minBytes = DEFAULT_COLUMN_WIDTH;
Map<String, Object> map = new HashMap<String, Object>();
/**
* 声明一个工作薄
*/
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);// 大于1000行时会把之前的行写入硬盘
workbook.setCompressTempFiles(true);
// 表头1样式
CellStyle title1Style = workbook.createCellStyle();
title1Style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
title1Style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
Font titleFont = workbook.createFont();// 字体
titleFont.setFontHeightInPoints((short) 20);
titleFont.setBoldweight((short) 700);
title1Style.setFont(titleFont);
// head样式
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headerStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置颜色
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 前景色纯色填充
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
Font headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 12);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
// 单元格样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
Font cellFont = workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(cellFont);
String excelTitleq = null;
LinkedHashMap<String, String> linkMapq = null;
JSONArray dataArrayq = null;
for (int q = 0; q < linkMap.size(); q++) {// 遍历生成附件中的sheet页
dataArrayq = dataArray.get(q);// 数据数组
excelTitleq = excelTitle.get(q);// 单元格标题
linkMapq = linkMap.get(q);// 表格标题信息
/**
* 生成一个(带名称)表格
*/
// int indexOf = excelTitleq.indexOf("(");
SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(excelTitleq);
/**
* 生成head相关信息+设置每列宽度
*/
int[] colWidthArr = new int[linkMapq.size()];// 列宽数组
String[] headKeyArr = new String[linkMapq.size()];// headKey数组
String[] headValArr = new String[linkMapq.size()];// headVal数组
int i = 0;
for (Map.Entry<String, String> entry : linkMapq.entrySet()) {
headKeyArr[i] = entry.getKey();
headValArr[i] = entry.getValue();
int bytes = headKeyArr[i].getBytes().length;
colWidthArr[i] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(i, colWidthArr[i] * 256);// 设置列宽
i++;
}
/**
* 遍历数据集合,产生Excel行数据
*/
int rowIndex = 0;
if (dataArrayq == null || dataArrayq.size() == 0) {
if (type == 1) {
SXSSFRow title1Row = (SXSSFRow) sheet.createRow(0);// title1行
title1Row.createCell(0).setCellValue(excelTitleq);
title1Row.getCell(0).setCellStyle(title1Style);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, linkMapq.size() - 1));// 合并单元格
SXSSFRow headerRow = (SXSSFRow) sheet.createRow(1);// head行
for (int j = 0; j < headValArr.length; j++) {
headerRow.createCell(j).setCellValue(headValArr[j]);
headerRow.getCell(j).setCellStyle(headerStyle);
}
} else if (type == 2) {
SXSSFRow headerRow = (SXSSFRow) sheet.createRow(0);// head行
for (int j = 0; j < headValArr.length; j++) {
headerRow.createCell(j).setCellValue(headValArr[j]);
headerRow.getCell(j).setCellStyle(headerStyle);
}
}
} else {
for (Object obj : dataArrayq) {
// 生成title+head信息
if (rowIndex == 0) {
SXSSFRow title1Row = (SXSSFRow) sheet.createRow(0);// title1行
title1Row.createCell(0).setCellValue(excelTitleq);
title1Row.getCell(0).setCellStyle(title1Style);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, linkMapq.size() - 1));// 合并单元格
SXSSFRow headerRow = (SXSSFRow) sheet.createRow(1);// head行
for (int j = 0; j < headValArr.length; j++) {
headerRow.createCell(j).setCellValue(headValArr[j]);
headerRow.getCell(j).setCellStyle(headerStyle);
}
rowIndex = 2;
}
JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
// 生成数据
SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);// 创建行
for (int k = 0; k < headKeyArr.length; k++) {
SXSSFCell cell = (SXSSFCell) dataRow.createCell(k);// 创建单元格
Object o = jo.get(headKeyArr[k]);
String cellValue = "";
if (o == null) {
cellValue = "";
} else if (o instanceof Date) {
cellValue = new SimpleDateFormat(datePattern).format(o);
} else if (o instanceof Float || o instanceof Double) {
cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP)
.toString();
} else {
cellValue = o.toString();
}
cell.setCellValue(cellValue);
cell.setCellStyle(cellStyle);
}
rowIndex++;
}
}
}
// ByteArrayOutputStream bos = new ByteArrayOutputStream();
// workbook.write(bos);
// return new ByteArrayInputStream(bos.toByteArray());
map.put("workbook",workbook);
return map;
}
}
使用
JSONArray dataArray = JSONArray.parseArray(JSON.toJSONString(List));
LinkedHashMap<String, String> headMap = new LinkedHashMap<>();
headMap.put("userCode","用户账号");
headMap.put("userName","用户名");
String excelTitle = "超时案件统计";
Map<String, Object> map = ExcelUtil.exportExcel(headMap,dataArray,excelTitle,type);
SXSSFWorkbook workbook = (SXSSFWorkbook)map.get("workbook");
ExcelUtil.writeExcel(workbook,getResponse(),"fileName");
private HttpServletResponse getResponse(){
return ((ServletRequestAttributes)RequestContextHolder.getRequestAttribute()).getResponse();
}