POI导出,首先加入依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<scope>compile</scope>
</dependency>
ExcelUtil工具类:
package com.example.testword.utils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
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 javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
/**
* @ProjectName: testWord
* @Package: com.example.testword.utils
* @ClassName: ExcelUtil
* @Description: TODO
* @Author: A.Hacker
* @CreateDate: 2019/11/27 16:55
* @Version: 1.0
*/
public class ExcelUtil {
public static String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日";//默认日期格式
public static int DEFAULT_COLOUMN_WIDTH = 10;
/**
* 导出Excel 97(.xls)格式 ,少量数据
*
* @param title 标题行
* @param headMap 属性-列名
* @param jsonArray 数据集
* @param datePattern 日期格式,null则用默认日期格式
* @param colWidth 列宽 默认 至少17个字节
* @param out 输出流
*/
public static void exportExcel(String title, Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
if (datePattern == null) datePattern = DEFAULT_DATE_PATTERN;
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
workbook.createInformationProperties();
workbook.getDocumentSummaryInformation().setCompany("*****公司");
SummaryInformation si = workbook.getSummaryInformation();
si.setAuthor("JACK"); //填加xls文件作者信息
si.setApplicationName("导出程序"); //填加xls文件创建程序信息
si.setLastAuthor("最后保存者信息"); //填加xls文件最后保存者信息
si.setComments("JACK is a programmer!"); //填加xls文件作者信息
si.setTitle("POI导出Excel"); //填加xls文件标题信息
si.setSubject("POI导出Excel");//填加文件主题信息
si.setCreateDateTime(new Date());
//表头样式
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 20);
titleFont.setBoldweight((short) 700);
titleStyle.setFont(titleFont);
// 列头样式
HSSFCellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 12);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
// 单元格样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont cellFont = workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(cellFont);
// 生成一个(带标题)表格
HSSFSheet sheet = workbook.createSheet();
// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// 定义注释的大小和位置,详见文档
HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
0, 0, 0, (short) 4, 2, (short) 6, 5));
// 设置注释内容
comment.setString(new HSSFRichTextString("可以在POI中添加注释!"));
// 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.setAuthor("JACK");
//设置列宽
int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
int[] arrColWidth = new int[headMap.size()];
// 产生表格标题行,以及设置列宽
String[] properties = new String[headMap.size()];
String[] headers = new String[headMap.size()];
int ii = 0;
for (Iterator<String> iter = headMap.keySet().iterator(); iter
.hasNext(); ) {
String fieldName = iter.next();
properties[ii] = fieldName;
headers[ii] = fieldName;
int bytes = fieldName.getBytes().length;
arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
ii++;
}
// 遍历集合数据,产生数据行
int rowIndex = 0;
for (Object obj : jsonArray) {
if (rowIndex == 65535 || rowIndex == 0) {
if (rowIndex != 0) sheet = workbook.createSheet();//如果数据超过了,则在第二页显示
HSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
titleRow.createCell(0).setCellValue(title);
titleRow.getCell(0).setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
HSSFRow headerRow = sheet.createRow(1); //列头 rowIndex =1
for (int i = 0; i < headers.length; i++) {
headerRow.createCell(i).setCellValue(headers[i]);
headerRow.getCell(i).setCellStyle(headerStyle);
}
rowIndex = 2;//数据内容从 rowIndex=2开始
}
JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
HSSFRow dataRow = sheet.createRow(rowIndex);
for (int i = 0; i < properties.length; i++) {
HSSFCell newCell = dataRow.createCell(i);
Object o = jo.get(properties[i]);
String cellValue = "";
if (o == null) cellValue = "";
else if (o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
else cellValue = o.toString();
newCell.setCellValue(cellValue);
newCell.setCellStyle(cellStyle);
}
rowIndex++;
}
// 自动调整宽度
/*for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i);
}*/
try {
workbook.write(out);
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
//压缩文件
public static void ZipFiles(java.io.File[] srcfile, java.io.File zipfile) {
byte[] buf = new byte[1024];
try {
ZipOutputStream out2 = new ZipOutputStream(new FileOutputStream(zipfile));
for (int i = 0; i < srcfile.length; i++) {
FileInputStream in = new FileInputStream(srcfile[i]);
out2.putNextEntry(new ZipEntry(srcfile[i].getName()));
int len;
while ((len = in.read(buf)) > 0) {
out2.write(buf, 0, len);
}
out2.closeEntry();
in.close();
}
out2.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 导出Excel 2007 OOXML (.xlsx)格式
*
* @param title 标题行
* @param headMap 属性-列头
* @param jsonArray 数据集
* @param datePattern 日期格式,传null值则默认 年月日
* @param colWidth 列宽 默认 至少17个字节
* @param out 输出流
*/
public static void exportExcelX(String title, Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
if (datePattern == null) datePattern = DEFAULT_DATE_PATTERN;
// 声明一个工作薄
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
workbook.setCompressTempFiles(true);
//表头样式
CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Font titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 20);
titleFont.setBoldweight((short) 700);
titleStyle.setFont(titleFont);
// 列头样式
CellStyle headerStyle = workbook.createCellStyle();
// headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
// headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
// headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Font headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 1);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
// 单元格样式
/*CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
Font cellFont = workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(cellFont);*/
// 生成一个(带标题)表格
SXSSFSheet sheet = workbook.createSheet();
//设置列宽
int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
int[] arrColWidth = new int[headMap.size()];
// 产生表格标题行,以及设置列宽
String[] properties = new String[headMap.size()];
String[] headers = new String[headMap.size()];
int ii = 0;
for (Iterator<String> iter = headMap.keySet().iterator(); iter
.hasNext(); ) {
String fieldName = iter.next();
properties[ii] = fieldName;
headers[ii] = headMap.get(fieldName);
int bytes = fieldName.getBytes().length;
arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
ii++;
}
// 遍历集合数据,产生数据行
int rowIndex = 0;
for (Object obj : jsonArray) {
if (rowIndex == 65535 || rowIndex == 0) {
if (rowIndex != 0) sheet = workbook.createSheet();//如果数据超过了,则在第二页显示
SXSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
titleRow.createCell(0).setCellValue(title);
titleRow.getCell(0).setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
SXSSFRow headerRow = sheet.createRow(1); //列头 rowIndex =1
for (int i = 0; i < headers.length; i++) {
headerRow.createCell(i).setCellValue(headers[i]);
}
rowIndex = 2;//数据内容从 rowIndex=2开始
}
JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
SXSSFRow dataRow = sheet.createRow(rowIndex);
for (int i = 0; i < properties.length; i++) {
SXSSFCell newCell = dataRow.createCell(i);
Object o = jo.get(properties[i]);
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();
newCell.setCellValue(cellValue);
// newCell.setCellStyle(cellStyle);
}
rowIndex++;
}
// 自动调整宽度
/*for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i);
}*/
try {
workbook.write(out);
workbook.close();
workbook.dispose();
} catch (IOException e) {
e.printStackTrace();
}
}
//Web 导出excel
public static void downloadExcelFile(String title, Map<String, String> headMap, JSONArray ja, HttpServletResponse response) {
try {
ByteArrayOutputStream os = new ByteArrayOutputStream();
// ExcelUtil.exportExcelX(title,headMap,ja,null,0,os);
ExcelUtil.exportExcelXHSSFWorkbook(title, headMap, ja, null, 0, os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数,可以打开下载页面
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((title + ".xls").getBytes(), "iso-8859-1"));
response.setContentLength(content.length);
response.setCharacterEncoding("UTF-8");
ServletOutputStream outputStream = response.getOutputStream();
BufferedInputStream bis = new BufferedInputStream(is);
BufferedOutputStream bos = new BufferedOutputStream(outputStream);
byte[] buff = new byte[8192];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
bis.close();
bos.close();
outputStream.flush();
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//正在用的导出的----------------------------------
public static void exportExcelXHSSFWorkbook(String title, Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
if (datePattern == null) datePattern = DEFAULT_DATE_PATTERN;
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// workbook.setCompressTempFiles(true);
//表头样式
CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Font titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 18);
titleFont.setBoldweight((short) 700);
titleStyle.setFont(titleFont);
// 列头样式
CellStyle headerStyle = workbook.createCellStyle();
// headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
// headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
// headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Font headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 12);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
// 单元格样式
CellStyle cellStyle = workbook.createCellStyle();
// cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
// cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
// cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
Font cellFont = workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// cellFont.setFontName("宋体");
cellFont.setFontHeightInPoints((short) 12);// 设置字体大小
cellStyle.setFont(cellFont);
// 生成一个(带标题)表格
HSSFSheet sheet = workbook.createSheet();
//设置列宽
int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
int[] arrColWidth = new int[headMap.size()];
// 产生表格标题行,以及设置列宽
String[] properties = new String[headMap.size()];
String[] headers = new String[headMap.size()];
int ii = 0;
for (Iterator<String> iter = headMap.keySet().iterator(); iter
.hasNext(); ) {
String fieldName = iter.next();
properties[ii] = fieldName;
headers[ii] = headMap.get(fieldName);
int bytes = fieldName.getBytes().length;
arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
ii++;
}
// 遍历集合数据,产生数据行
int rowIndex = 0;
for (Object obj : jsonArray) {
if (rowIndex == 65535 || rowIndex == 0) {
if (rowIndex != 0) sheet = workbook.createSheet();//如果数据超过了,则在第二页显示
HSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
titleRow.createCell(0).setCellValue(title);
titleRow.getCell(0).setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
HSSFRow headerRow = sheet.createRow(1); //列头 rowIndex =1
for (int i = 0; i < headers.length; i++) {
headerRow.createCell(i).setCellValue(headers[i]);
headerRow.getCell(i).setCellStyle(headerStyle);
}
rowIndex = 2;//数据内容从 rowIndex=2开始
}
JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
HSSFRow dataRow = sheet.createRow(rowIndex);
for (int i = 0; i < properties.length; i++) {
HSSFCell newCell = dataRow.createCell(i);
Object o = jo.get(properties[i]);
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();
newCell.setCellValue(cellValue);
newCell.setCellStyle(cellStyle);
}
rowIndex++;
}
// 自动调整宽度
/*for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i);
}*/
try {
workbook.write(out);
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 导入方法
*/
private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
//成功
public static final Integer STATUS_OK = Integer.valueOf(1);
//失败
public static final Integer STATUS_NO = Integer.valueOf(0);
//私有化构造器
private ExcelUtil() {
}
/**
* 获取excel文件中的数据对象
*
* @param is excel
* @param excelColumnNames excel中每个字段的英文名(应该与pojo对象的字段名一致,顺序与excel一致)
* @return excel每行是list一条记录,map是对应的"字段名-->值"
* @throws Exception
*/
/* public static List<Map<String, String>> getImportData(InputStream is, List<String> excelColumnNames) throws Exception {
logger.debug("InputStream:{}", is);
if (is == null) {
return Collections.emptyList();
}
Workbook workbook = null;
try {
//拿到excel
workbook = Workbook.getWorkbook(is);
} catch (BiffException e) {
logger.error(e.getMessage(), e);
return Collections.EMPTY_LIST;
} catch (IOException e) {
logger.error(e.getMessage(), e);
return Collections.EMPTY_LIST;
}
logger.debug("workbook:{}", workbook);
if (workbook == null) {
return Collections.emptyList();
}
//第一个sheet
Sheet sheet = workbook.getSheet(0);
//行数
int rowCounts = sheet.getRows() - 1;
logger.debug("rowCounts:{}", rowCounts);
List<Map<String, String>> list = new ArrayList<Map<String, String>>(rowCounts - 1);
//双重for循环取出数据
for(int i = 1; i < rowCounts; i++){
Map<String, String> params = new HashMap<String, String>();
//i,j i:行 j:列
for(int j = 0; j < excelColumnNames.size(); j++){
Cell cell = sheet.getCell(j, i);
params.put(excelColumnNames.get(j), cell.getContents());
}
list.add(params);
}
return list;
}*/
public static void main(String[] args) throws IOException {
int count = 5000;
JSONArray ja = new JSONArray();
for (int i = 0; i < 5000; i++) {
Student s = new Student();
s.setName("POI" + i);
s.setAge(i);
s.setBirthday(new Date());
s.setSex(i / 2 == 0 ? false : true);
ja.add(s);
}
Map<String, String> headMap = new LinkedHashMap<String, String>();
headMap.put("name", "姓名");
headMap.put("age", "年龄");
headMap.put("birthday", "生日");
headMap.put("sex", "性别");
String title = "测试";
OutputStream outXlsx = new FileOutputStream("F://b.xlsx");
System.out.println("正在导出xlsx....");
Date d2 = new Date();
ExcelUtil.exportExcelX(title, headMap, ja, null, 0, outXlsx);
System.out.println("共" + count + "条数据,执行" + (new Date().getTime() - d2.getTime()) + "ms");
outXlsx.close();
}
}
class Student {
private String name;
private int age;
private Date birthday;
private boolean sex;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public boolean isSex() {
return sex;
}
public void setSex(boolean sex) {
this.sex = sex;
}
public void setAge(Integer age) {
this.age = age;
}
}