背景:需要导出大量数据到Excel中,但是又因为普通的导出数据工具不太好用,并且很容易OOM,那么可以使用这个方法来解决:下面给出2个方法,一个是简洁数据文件,不带样式和标题的导出工具类,一个是导出的数据文件带有标题和一些简易样式
并且到百万级别数据速度非常快,如果字段不多,30秒左右即可
需要加入的poi依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
1. 简洁易用的导出数据工具类:(不带样式)
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.common.usermodel.Hyperlink;
import org.apache.poi.hpsf.SummaryInformation;
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.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
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.apache.poi.xssf.usermodel.*;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* 不带样式的Excel输出基本数据的工具类
*/
public class ExcelUtil {
public static final String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日";// 默认日期格式,如果传递的是Date类型,则会格式化为这个
public static final int DEFAULT_COLUMN_WIDTH = 17;// 默认列宽
/**
* 导出Excel(.xlsx)格式
* @param titleList 表格头信息集合
* @param dataArray 数据数组
* @param os 文件输出流
*/
public static void exportExcel(ArrayList<LinkedHashMap> titleList, JSONArray dataArray, OutputStream os) {
String datePattern = DEFAULT_DATE_PATTERN;
int minBytes = DEFAULT_COLUMN_WIDTH;
/**
* 声明一个工作薄
*/
SXSSFWorkbook workbook = new SXSSFWorkbook(100000);// 大于1000行时会把之前的行写入硬盘
workbook.setCompressTempFiles(true);
String title1 = (String) titleList.get(0).get("title1");
String title2 = (String) titleList.get(0).get("title2");
LinkedHashMap<String, String> headMap = titleList.get(1);
/**
* 生成一个(带名称)表格
*/
if(StringUtils.isNotBlank(title1)) {
SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(title1);
sheet.createFreezePane(0, 3, 0, 3);// (单独)冻结前三行
}
SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet();
/**
* 生成head相关信息+设置每列宽度
*/
int[] colWidthArr = new int[headMap.size()];// 列宽数组
String[] headKeyArr = new String[headMap.size()];// headKey数组
String[] headValArr = new String[headMap.size()];// headVal数组
int i = 0;
for (Map.Entry<String, String> entry : headMap.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;
for (Object obj : dataArray) {
// 生成title+head信息
if (rowIndex == 0) {
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);
}
rowIndex++;
}
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);
}
rowIndex++;
}
try {
workbook.write(os);
os.flush();// 刷新此输出流并强制将所有缓冲的输出字节写出
IOUtils.closeQuietly(os);
workbook.dispose();// 释放workbook所占用的所有windows资源
} catch (IOException e) {
e.printStackTrace();
}
}
}
调用示例:
public void exportData(String dir) {
// 从数据库中查询list数据
List<Object[]> list = tempRepository.queryData();
String fileName = "EXAMPLE_FILE" + ".xlsx";
// 将list集合数据转换为JsonArray类型
JSONArray dataArray = new JSONArray();
for (Object[] obj : list) {
if (obj != null) {
ExportModel fc = new ExportModel();
fc.setId((String) obj[0]);
fc.setName((String) obj[1]);
fc.setMobile((String) obj[2]);
Date date = (Date)obj[3];
fc.setCreateDate(date != null? df.format(date):"");
dataArray.add(fc);
}
}
ArrayList<LinkedHashMap> titleList = new ArrayList<LinkedHashMap>();
// 1.titleMap存放了该excel的头信息
LinkedHashMap<String, String> titleMap = new LinkedHashMap<String, String>();
// 2.headMap存放了该excel的列项
LinkedHashMap<String, String> headMap = new LinkedHashMap<String, String>();
headMap.put("id", "ID");
headMap.put("name", "name");
headMap.put("mobile", "MOBILE");
headMap.put("create_date", "create_date");
titleList.add(titleMap);
titleList.add(headMap);
File file = new File(dir);
if (!file.exists()) file.mkdirs();// 创建该文件夹目录
OutputStream os = null;
try {
log.info("正在导出xlsx...");
long start = System.currentTimeMillis();
// .xlsx格式
os = new FileOutputStream(file.getAbsolutePath() + File.separator + fileName);
ExcelUtil.exportExcel(titleList, dataArray, os);
log.info("导出完成...共" + list.size() + "条数据,用时" + (System.currentTimeMillis() - start) + "毫秒");
log.info("文件路径:" + file.getAbsolutePath() + File.separator + start + ".xlsx");
} catch (Exception e) {
log.error("创建文件异常:", e);
} finally {
IOUtils.closeQuietly(os);
}
}
2. 简洁易用的导出数据工具类:(带样式)
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.common.usermodel.Hyperlink;
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.CreationHelper;
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.apache.poi.xssf.usermodel.*;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* 带有样式的Excel导出工具类
* @time 2019/8/8 10:05 AM
*/
public class ExcelUtil {
public static final String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日";// 默认日期格式,如果传递的是Date类型,则会格式化为这个
public static final int DEFAULT_COLUMN_WIDTH = 17;// 默认列宽
/**
* 导出Excel(.xlsx)格式
* @param titleList 表格头信息集合
* @param dataArray 数据数组
* @param os 文件输出流
*/
public static void exportExcel(ArrayList<LinkedHashMap> titleList, JSONArray dataArray, OutputStream os) {
String datePattern = DEFAULT_DATE_PATTERN;
int minBytes = DEFAULT_COLUMN_WIDTH;
/**
* 声明一个工作薄
*/
SXSSFWorkbook workbook = new SXSSFWorkbook(100000);// 大于100000行时会把之前的行写入硬盘
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);
// 表头2样式
CellStyle title2Style = workbook.createCellStyle();
title2Style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
title2Style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
title2Style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
title2Style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右
title2Style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下
title2Style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左
Font title2Font = workbook.createFont();
title2Font.setUnderline((byte) 1);
title2Font.setColor(HSSFColor.BLUE.index);
title2Style.setFont(title2Font);
// head样式
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headerStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.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 title1 = (String) titleList.get(0).get("title1");
String title2 = (String) titleList.get(0).get("title2");
LinkedHashMap<String, String> headMap = titleList.get(1);
/**
* 生成一个(带名称)表格
*/
SXSSFSheet sheet = null;
if(StringUtils.isNotBlank(title1)) {
sheet = (SXSSFSheet) workbook.createSheet(title1);
sheet.createFreezePane(0, 3, 0, 3);// (单独)冻结前三行
} else {
sheet = (SXSSFSheet) workbook.createSheet();
}
/**
* 生成head相关信息+设置每列宽度
*/
int[] colWidthArr = new int[headMap.size()];// 列宽数组
String[] headKeyArr = new String[headMap.size()];// headKey数组
String[] headValArr = new String[headMap.size()];// headVal数组
int i = 0;
for (Map.Entry<String, String> entry : headMap.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;
for (Object obj : dataArray) {
// 生成title+head信息
if (rowIndex == 0) {
SXSSFRow title1Row = (SXSSFRow) sheet.createRow(0);// title1行
title1Row.createCell(0).setCellValue(title1);
title1Row.getCell(0).setCellStyle(title1Style);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));// 合并单元格
if(StringUtils.isNotBlank(title2)) {
SXSSFRow title2Row = (SXSSFRow) sheet.createRow(1);// title2行
title2Row.createCell(0).setCellValue(title2);
CreationHelper createHelper = workbook.getCreationHelper();
XSSFHyperlink hyperLink = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
hyperLink.setAddress(title2);
title2Row.getCell(0).setHyperlink(hyperLink);// 添加超链接
title2Row.getCell(0).setCellStyle(title2Style);
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headMap.size() - 1));// 合并单元格
}
SXSSFRow headerRow = (SXSSFRow) sheet.createRow(2);// head行
for (int j = 0; j < headValArr.length; j++) {
headerRow.createCell(j).setCellValue(headValArr[j]);
headerRow.getCell(j).setCellStyle(headerStyle);
}
rowIndex = 3;
}
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++;
}
try {
workbook.write(os);
os.flush();// 刷新此输出流并强制将所有缓冲的输出字节写出
IOUtils.closeQuietly(os);
workbook.dispose();// 释放workbook所占用的所有windows资源
} catch (IOException e) {
e.printStackTrace();
}
}
}
调用示例:
public void exportData(String dir) {
// 查询数据
List<Object[]> list = tempRepository.queryData();
String fileName = "EXAMPLE_FILE" + ".xlsx";
// 将list集合转换为JsonnArray
JSONArray dataArray = new JSONArray();
for (Object[] obj : list) {
if (obj != null) {
ExportModel fc = new ExportModel();
fc.setId((String) obj[0]);
fc.setName((String) obj[1]);
fc.setMobile((String) obj[2]);
Date date = (Date)obj[3];
fc.setCreateDate(date != null? df.format(date):"");
dataArray.add(fc);
}
}
ArrayList<LinkedHashMap> titleList = new ArrayList<LinkedHashMap>();
// 1.titleMap存放了该excel的头信息
LinkedHashMap<String, String> titleMap = new LinkedHashMap<String, String>();
titleMap.put("title1", "大标题");
titleMap.put("title2", "小标题");
// 2.headMap存放了该excel的列项
LinkedHashMap<String, String> headMap = new LinkedHashMap<String, String>();
headMap.put("id", "id");
headMap.put("name", "name");
headMap.put("mobile", "mobile");
headMap.put("create_date", "create_date");
titleList.add(titleMap);
titleList.add(headMap);
File file = new File(dir);
if (!file.exists()) file.mkdirs();// 创建该文件夹目录
OutputStream os = null;
try {
log.info("正在导出xlsx...");
long start = System.currentTimeMillis();
// .xlsx格式
os = new FileOutputStream(file.getAbsolutePath() + File.separator + fileName);
ExcelUtil.exportExcel(titleList, dataArray, os);
log.info("导出完成...共" + list.size() + "条数据,用时" + (System.currentTimeMillis() - start) + "毫秒");
log.info("文件路径:" + file.getAbsolutePath() + File.separator + start + ".xlsx");
} catch (Exception e) {
log.error("创建文件异常:", e);
} finally {
IOUtils.closeQuietly(os);
}
}