easeExcel和poi得使用(复杂表头)
POI
下载地址:https://archive.apache.org/dist/poi/release/bin/ (当然我个人更推荐直接maven导入)
<!--2.0.5 可以和poi3.8兼容--> 但是 poi得很多方法会不能 严重不推荐
什么是poi?
Apache POI [1] 是用Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写,意为“简洁版的模糊实现”。
主要结构:
HSSF - 提供读写Microsoft Excel格式档案的功能。
XSSF - 提供读写Microsoft Excel OOXML格式档案的功能。
HWPF - 提供读写Microsoft Word格式档案的功能。
HSLF - 提供读写Microsoft PowerPoint格式档案的功能。
HDGF - 提供读写Microsoft Visio格式档案的功能。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FSQsUvo5-1621159020130)(C:\Users\lenovo\Desktop\新建文件夹\a.jpg)]
EaseExcel
什么是EaseExcel?
EaseExcel是一个基于java的简单,省内存的读写excel得到项目,在尽可能节约内存的情况下读写百M的excel
两款框架有冲突,所以单个项目模块推荐只使用一种框架
POI的使用
废话不多说 直接干代码。
- pom 依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
<exclusions>
<exclusion>
<artifactId>commons-codec</artifactId>
<groupId>commons-codec</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
- pojo层
package com.example.demo.test.entity.POI;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class ExportExcel implements Serializable {
public static final int ROW_ACCESS_WINDOW_SIZE = 10000;
private static final long serialVersionUID = -1715237191325535025L;
private static final Logger logger = LoggerFactory.getLogger(ExportExcel.class);
private String curTitle;
private String fileName;
private String[] curRowName;
private String[] curRowNameNext;
//合并单元格区域
private List<CellRangeAddressLocal> mergeRanges;
private WorkBookLocal localWb;
private List<Object[]> curDataList = new ArrayList<Object[]>();
private SXSSFWorkbook sfwb = null;
public ExportExcel(WorkBookLocal localWb) {
this.localWb = localWb;
}
public void export(HttpServletResponse response) {
if (!generateWorkBook()) {
return;
}
writeWorkBook(response);
}
public byte[] getWorkBookStream() {
if (!generateWorkBook()) {
return null;
}
if (sfwb == null) {
return null;
}
ByteArrayOutputStream bos = null;
try {
bos = new ByteArrayOutputStream();
sfwb.write(bos);
return bos.toByteArray();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (bos != null) {
try {
bos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return null;
}
public String getFileName(){
return this.fileName;
}
/**
* @desc 直接输出文件 到指定目录
*/
public File export(String fileDirPath) throws UnsupportedEncodingException {
if (!generateWorkBook()) {
return null;
}
if (sfwb == null) {
return null;
}
if (StringUtils.isBlank(fileDirPath)) {
logger.error("Excel template path is empty!");
throw new BizException("临时文件路径为空");
}
fileDirPath = checkTemplatePath(fileDirPath);
File dirPath = new File(URLDecoder.decode(fileDirPath, "UTF-8"));
// 文件夹是否存在,不存在创建
if (!dirPath.exists()) {
dirPath.mkdirs();
}
//删除超过7天的数据
FileUtil.deleteFiles(fileDirPath, 7);
File file = new File(dirPath, URLDecoder.decode(this.fileName+ ".xlsx", "UTF-8"));
if (file.exists()) {
FileUtils.deleteQuietly(file);
}
FileOutputStream fos = null;
ByteArrayOutputStream bos = null;
BufferedInputStream bis = null;
try {
fos = new FileOutputStream(file);
bos=new ByteArrayOutputStream();
sfwb.write(bos);
bis = new BufferedInputStream(new ByteArrayInputStream(bos.toByteArray()));
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
fos.write(buff, 0, bytesRead);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
closeOutputStrem(fos);
closeOutputStrem(bos);
closeInputStream(bis);
}
return file;
}
public void closeInputStream(InputStream is) {
if (is != null) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public void closeOutputStrem(OutputStream os) {
if (os != null) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
private String checkTemplatePath(String filePath) {
return filePath.replace("+", "%2B");
}
private void writeWorkBook(HttpServletResponse response) throws BizException {
ServletOutputStream out = null;
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
ByteArrayOutputStream os = null;
ByteArrayInputStream is = null;
try {
if (sfwb != null) {
response.reset();
response.setHeader(
"Content-Disposition",
"attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
response.setContentType("application/vnd.ms-excel;charset=utf-8");
out = response.getOutputStream();
os = new ByteArrayOutputStream();
sfwb.write(os);
is = new ByteArrayInputStream(os.toByteArray());
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
}
} catch (BizException e) {
logger.error("BizException:{}", e);
throw new BizException(e.getErrorCode(), e.getErrorMsg());
} catch (Exception e) {
logger.error("BizException:{}", e);
throw new BizException(BizException.ERROR_CODE, "报表生成失败!");
} finally {
closeOutputStrem(bos);
closeInputStream(bis);
closeOutputStrem(os);
}
}
private boolean generateWorkBook() {
if (localWb == null) {
logger.warn("无内容输出");
return false;
}
if (StringUtils.isBlank(localWb.getFileName())
&& CollectionUtils.isNotEmpty(localWb.getSheets())) {
logger.warn("文件名不能为空");
return false;
}
if (StringUtils.isBlank(localWb.getFileName())
&& CollectionUtils.isNotEmpty(localWb.getSheets())) {
logger.warn("文件名不能为空");
// return;
// 没有名字,设置默认文件名
localWb.setFileName(
DateFormatUtils.format(new Date(), "yyyy年MM月dd日")
.concat("报表-")
.concat(String.valueOf(System.currentTimeMillis())));
}
// SXSSFWorkbook sfwb = null;
if (CollectionUtils.isNotEmpty(localWb.getSheets())) {
sfwb = new SXSSFWorkbook(ROW_ACCESS_WINDOW_SIZE);
for (int i = 0; i < localWb.getSheets().size(); i++) {
SheetLocal localSheet = localWb.getSheets().get(i);
// 设置本地sheet相关信息
this.curTitle = localSheet.getTitle();
this.curRowName = localSheet.getRowName();
this.curRowNameNext = localSheet.getRowNameNext();
this.curDataList = localSheet.getDataList();
this.mergeRanges=localSheet.getMergeRanges();
String sheetname =
StringUtils.isNotBlank(localSheet.getSheetName())
? localSheet.getSheetName()
: localSheet.getTitle();
if (StringUtils.isBlank(sheetname)) {
throw new BizException(BizException.ERROR_CODE, String.format("报表第%d个sheet名不能为空", i));
}
Sheet currentSheet = sfwb.createSheet(sheetname);
// 分sheet打印
write_sheet(sfwb, currentSheet);
}
}
// 输出
// 设置工作簿的名称
this.fileName = localWb.getFileName();
return true;
}
/**
* @desc 分sheet输出
* @date wxs 2018-09-10 15:26
* @param sfwb
* @param sheet
* @return void
*/
private void write_sheet(SXSSFWorkbook sfwb, Sheet sheet) {
Row rowm = sheet.createRow(0);
Cell cellTiltle = rowm.createCell(0);
// 设置样式
CellStyle columnTopStyle = this.getColumnTopStyle(sfwb);
CellStyle style = this.getStyle(sfwb);
// 第一行的标题信息
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (curRowName.length - 1)));
cellTiltle.setCellStyle(columnTopStyle);
cellTiltle.setCellValue(curTitle);
// 标题列行
int rowNumber = 2;
int columnNum = curRowName.length;
Row rowRowName = sheet.createRow(rowNumber);
rowNumber++;
for (int n = 0; n < columnNum; n++) {
Cell cellRowName = rowRowName.createCell(n);
cellRowName.setCellType(Cell.CELL_TYPE_STRING);
RichTextString text = new XSSFRichTextString(curRowName[n]);
cellRowName.setCellValue(text);
cellRowName.setCellStyle(columnTopStyle);
// resetColumnWidth(sheet, cellRowName);
}
// 附加标题单元格
if (curRowNameNext != null && curRowNameNext.length > 0) {
int columnNextNum = curRowNameNext.length;
Row rowRowNameNext = sheet.createRow(rowNumber);
rowNumber++;
for (int n = 0; n < columnNextNum; n++) {
Cell cellRowName = rowRowNameNext.createCell(n);
cellRowName.setCellType(Cell.CELL_TYPE_STRING);
if (curRowNameNext[n] == null) {
CellStyle columnCenterStyle = this.getColumnTopStyle(sfwb);
columnCenterStyle.setAlignment(CellStyle.ALIGN_CENTER);
columnCenterStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
sheet.addMergedRegion(new CellRangeAddress(rowNumber - 2, rowNumber - 1, n, n));
cellRowName.setCellStyle(columnCenterStyle);
continue;
}
RichTextString text = new XSSFRichTextString(curRowNameNext[n]);
cellRowName.setCellValue(text);
cellRowName.setCellStyle(columnTopStyle);
}
}
// 合合并单元格
if (CollectionUtils.isNotEmpty(mergeRanges)) {
for (CellRangeAddressLocal mergeRange : mergeRanges) {
CellStyle columnCenterStyle = this.getColumnTopStyle(sfwb);
columnCenterStyle.setAlignment(CellStyle.ALIGN_CENTER);
columnCenterStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
sheet.addMergedRegion(
new CellRangeAddress(
mergeRange.getFirstRow(),
mergeRange.getLastRow(),
mergeRange.getFirstCol(),
mergeRange.getLastCol()));
sheet
.getRow(mergeRange.getFirstRow())
.getCell(mergeRange.getFirstCol())
.setCellStyle(columnCenterStyle);
}
}
// 内容起始行
// int contentRowNumStart=rowNumber;
// 输出值
for (int i = 0; i < curDataList.size(); i++) {
Object[] obj = curDataList.get(i);
Row row = sheet.createRow(i + rowNumber);
for (int j = 0; j < obj.length; j++) {
Cell cell;
if (!"".equals(obj[j]) && obj[j] != null) {
cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(obj[j].toString());
}else{
cell = row.createCell(j);
}
cell.setCellStyle(style);
}
}
// 调整宽度
for (int colNum = 0; colNum < columnNum; colNum++) {
sheet.autoSizeColumn(colNum, true);
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 1; rowNum < sheet.getLastRowNum(); rowNum++) {
Row currentRow;
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
Cell currentCell = currentRow.getCell(colNum);
if (currentCell.getCellType() == Cell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().trim().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
// if (colNum == 1) {
// sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
// } else {
// 最大限制256
sheet.setColumnWidth(
colNum, (((columnWidth + 5) * 256) > 255 * 256) ? 255 * 256 : ((columnWidth + 5) * 256));
// }
}
}
/**
* 样式设置
*
**/
public CellStyle getColumnTopStyle(Workbook workbook) {
// 设置字体
Font font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 11);
// 字体加粗
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式;
CellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(CellStyle.BORDER_THIN);
// 设置底边框颜色;
style.setBottomBorderColor(IndexedColors.BLACK.index);
// 设置左边框;
style.setBorderLeft(CellStyle.BORDER_THIN);
// 设置左边框颜色;
style.setLeftBorderColor(IndexedColors.BLACK.index);
// 设置右边框;
style.setBorderRight(CellStyle.BORDER_THIN);
// 设置右边框颜色;
style.setRightBorderColor(IndexedColors.BLACK.index);
// 设置顶边框;
style.setBorderTop(CellStyle.BORDER_THIN);
// 设置顶边框颜色;
style.setTopBorderColor(IndexedColors.BLACK.index);
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(CellStyle.ALIGN_CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
return style;
}
public void resetColumnWidth(Sheet sheet, Cell currentCell) {
if (currentCell == null) {
return;
}
int columnNum = currentCell.getColumnIndex();
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
sheet.setColumnWidth(columnNum, columnWidth * 256);
}
}
public CellStyle getStyle(Workbook workbook) {
// 设置字体
Font font = workbook.createFont();
// 设置字体大小
// font.setFontHeightInPoints((short)10);
// 字体加粗
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式;
CellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(CellStyle.BORDER_THIN);
// 设置底边框颜色;
style.setBottomBorderColor(IndexedColors.BLACK.index);
// 设置左边框;
style.setBorderLeft(CellStyle.BORDER_THIN);
// 设置左边框颜色;
style.setLeftBorderColor(IndexedColors.BLACK.index);
// 设置右边框;
style.setBorderRight(CellStyle.BORDER_THIN);
// 设置右边框颜色;
style.setRightBorderColor(IndexedColors.BLACK.index);
// 设置顶边框;
style.setBorderTop(CellStyle.BORDER_THIN);
// 设置顶边框颜色;
style.setTopBorderColor(IndexedColors.BLACK.index);
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(CellStyle.ALIGN_CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
return style;
}
}
那么dao层的代码我就省略了
那么直接干service层
service层我会写两种,因为我们导出得excel有两种情况,你可能会出现复杂表头和简单表头。
- service层
简单表头
//excel测试
public static final String[] PROCESS_MANAGEMENT = new String[]{"日期", "姓名", "家住地址"};
@Override
public ExportExcel downloadExcelForBloodProcess(String queryData) {
StringBuilder fileName = new StringBuilder();
List<Object[]> result = Lists.newArrayList();
fileName.append("excel测试");
WorkBookLocal localWb = new WorkBookLocal(fileName.toString());
//此处为你dao层数据查询结果
List<DownloadBloodProcessExcel> downloadBloodProcessExcels = processDao.downloadBloodProcessExcel(queryData);
for (DownloadBloodProcessExcel listData : downloadBloodProcessExcels) {
result.add(
new Object[]{
//这是你excel中需要显示得字段数据
listData.getDate(),
listData.getUser(),
listData.getHome()
}
);
}
localWb = new WorkBookLocal(fileName.toString());
localWb
.getSheets()
.addAll(
Lists.<SheetLocal>newArrayList(
new SheetLocal(fileName.toString(), BloodRelationshipAnalysisProcessImpl.PROCESS_MANAGEMENT, null, result)));
return new ExportExcel(localWb);
}
复杂表头
//获取前一个月
if (screenParameter.getTypeName().equals("0")) {
fileName = new StringBuilder();
fileName.append("用户数据表单");
result = Lists.newArrayList();
String[] rowName1 = new String[]{"月份", "新增订购用户数", "", "T+1", "", "", "T+2", "", "", "T+3"};
String[] rowName = new String[12];
rowName[1] = "新增订购用户数";
rowName[2] = "环比";
rowName[3] = "新增续订用户数";
rowName[4] = "续订率";
rowName[5] = "续订率环比";
rowName[6] = "新增续订用户数";
rowName[7] = "续订率";
rowName[8] = "续订率环比";
rowName[9] = "新增续订用户数";
rowName[10] = "续订率";
rowName[11] = "续订率环比";
List<Object[]> dataResult = Lists.newArrayList();
String startDate = screenParameter.getStartDate();
String startMonth = ScreenUtils.strToDateForMonth(ScreenUtils.addMonth(startDate,-1));
screenParameter.setStartDate(startMonth);
String endMonth = ScreenUtils.strToDateForMonth(screenParameter.getEndDate());
screenParameter.setEndDate(endMonth);
List<BigScreenPojoForMonthExcel> bigScreenPojoForMonthExcelsLastMonth = bigScreenDaos.downloadExcelForMonthRenew(screenParameter);
WorkBookLocal localWb = new WorkBookLocal(fileName.toString());
//设置第二行合并
List<CellRangeAddressLocal> mergeRanges = Lists.newArrayList();
mergeRanges.add(new CellRangeAddressLocal(0, 1, 0, 11));
mergeRanges.add(new CellRangeAddressLocal(2, 2, 1, 2));
mergeRanges.add(new CellRangeAddressLocal(2, 2, 3, 5));
mergeRanges.add(new CellRangeAddressLocal(2, 2, 6, 8));
mergeRanges.add(new CellRangeAddressLocal(2, 2, 9, 11));
List<SheetLocal> sheetLocals = Lists.newArrayList();
SheetLocal sheetLocal1 = new SheetLocal("新增留存", rowName1, rowName, dataResult);
sheetLocal1.setMergeRanges(mergeRanges);
sheetLocals.add(sheetLocal1);
localWb.getSheets().addAll(sheetLocals);
return new ExportExcel(localWb);
- controller层
//Excel下载
@RequestMapping(value = "/test", method = RequestMethod.GET)
public ResponseMsg<?> downloadExcelData(HttpServletRequest request, HttpServletResponse response, String queryData) {
try {
ExportExcel exportExcel = processService.downloadExcelForBloodProcess(queryData);
return getSuccessMsg(null, "导出成功", null);
} catch (Exception e) {
logger.error(e.getMessage(), e);
return getFailMsg(null, "导出失败", e.getMessage());
}
}
EaseExcel使用
Excel他将表头得样式设置,放在了实体类上。所以我个人推荐,使用EaseExcel比较方便,容易上手
dao层忽略,懒得写了~~~~
- maven
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
- pojo类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelEntity implements Serializable {
//简单表头得情况就是这样。
@ExcelProperty("id")
private String id;
@ExcelProperty("用户名")
private String username;
@ExcelProperty("密码")
private String password;
/**
* 忽略这个字段
*/
@ExcelIgnore //注意这个注解是高版本的easyexcel依赖才有
private String ignore;
}
/**
* 复杂头写入
* @ColumnWidth 为列宽
* @ExcelProperty({"参数a","参数b", "参数c"})
* 参数a为第一行,参数b为第二行,参数c为第三行
如果想合并单元格,只需要将需要合并得写在一起
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ComplexHeadData implements Serializable {
@ExcelProperty({"新增订购用户数", "月份"})
@ColumnWidth(23)
private String date;
@ExcelProperty({"新增订购用户数","新增订购用户数", "新增订购用户数"})
@ColumnWidth(30)
private String id;
@ExcelProperty({"新增订购用户数","新增订购用户数", "环比"})
@ColumnWidth(10)
private String username;
@ExcelProperty({"新增订购用户数","T+1", "T+1新增订购用户数"})
@ColumnWidth(30)
private String password;
@ExcelProperty({"新增订购用户数","T+1", "续订率"})
private String uv;
@ExcelProperty({"新增订购用户数","T+1", "新增续订用户数"})
@ColumnWidth(30)
private String vvuv;
@ExcelProperty({"新增订购用户数","T+2", "T+2新增订购用户数"})
@ColumnWidth(30)
private String money;
@ExcelProperty({"新增订购用户数","T+2", "续订率"})
private String lastVvuv;
@ExcelProperty({"新增订购用户数","T+2", "新增续订用户数"})
@ColumnWidth(30)
private String groupId;
}
- service层
List<ExcelEntity> selById(@Param("start") Integer start, @Param("end") Integer end);
- serviceImpl
@Override
public List<ExcelEntity> selById(Integer start, Integer end) {
return excelTestDao.selById(start, end);
}
- controller层
excelTestService.selById(start, end)就是你调用service层查询出得数据
@GetMapping("/downloadFailedUsingJson2")
public void downloadFailedUsingJson2(HttpServletResponse response, Integer start, Integer end) throws IOException {
// 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("新增订购用户数", "utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream(), ExcelEntity.class).autoCloseStream(Boolean.FALSE).sheet("数据表")
.doWrite(excelTestService.selById(start, end));
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = new HashMap<String, String>();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}