Easyexcel进行多个Sheet导出
Maven
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
Controller
@GetMapping("/exportWeb2")
@ApiOperation("多个Sheet导出")
public void exportToWeb(HttpServletResponse response) {
try {
ExcelsUtils.allExportToWeb(response, "产品-项目表", //excel表格名称
"项目表", "厂家表", "产品表", "模块表", //excel表格的sheet名称
itemService.getExcelItems(),
factoryService.getExcelFactoryList(),
productService.getExcelProduct(),
subsystemService.getSystemicAndSubstemicAndModule()); //导出sheet的四份数据
} catch (Exception e) {
log.error("报表导出异常:", e);
}
}
Service & ServiceImpl
//第一个Sheet表格获取的数据
List<ItemsExcel> getExcelItems();
public List<ItemsExcel> getExcelItems() {
return itemMapper.getExcelItems();
}
//第一个Sheet表格获取的数据
List<FactorysExcel> getExcelFactory();
public List<FactorysExcel> getExcelFactory() {
return factoryMapper.getExcelFactory();
}
//第三个Sheet表格获取的数据
List<ProductsExcel> getExcelProducts();
public List<ProductsExcel> getExcelProducts() {
return productMapper.getExcelProducts();
}
//第四个Sheet表格获取的数据
List<Subsystems> getSystemic();
public List<Subsystems> getSystemic() {
return subsystemMapper.getSystemic();
}
ExcelsUtils 工具类
/**
* 导出Excel(07版.xlsx)到web
* 多sheet
*/
public static void allExportToWeb(HttpServletResponse response, String excelName,
String sheetName1, String sheetName2, String sheetName3,
String sheetName4,
List data1, List data2, List data3,List data4) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
excelName = URLEncoder.encode(excelName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
ExcelWriter excelWriter = EasyExcel
.write(response.getOutputStream())
.registerWriteHandler(new Custemhandler())
.registerConverter(new LocalDateTimeConverter())
.registerWriteHandler(ExcelStyleUtil.getStyleStrategy())// Excel样式引用方式
.build();
WriteSheet writeSheet1 = EasyExcel.writerSheet(0, sheetName1).head(ItemsExcel.class).build();
WriteSheet writeSheet2 = EasyExcel.writerSheet(1, sheetName2).head(FactorysExcel.class).build();
WriteSheet writeSheet3 = EasyExcel.writerSheet(2, sheetName3).head(ProductsExcel.class).build();
WriteSheet writeSheet4 = EasyExcel.writerSheet(3, sheetName4).head(SystemicExcel.class).build();
excelWriter.write(data1, writeSheet1);
excelWriter.write(data2, writeSheet2);
excelWriter.write(data3, writeSheet3);
excelWriter.write(data4, writeSheet4);
excelWriter.finish();
}
下面是进行样式更改的一些工具包
Custemhandler
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* easyexcel的自动列宽有些小问题,将官方代码略微修改一下使用
* 此处尽量使用高一些的版本,不然无法修改CellData为List<CellData>
*/
public class Custemhandler extends AbstractColumnWidthStyleStrategy{
private static final int MAX_COLUMN_WIDTH = 255;
//因为在自动列宽的过程中,有些设置地方让列宽显得紧凑,所以做出了个判断
private static final int COLUMN_WIDTH = 20;
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);
public Custemhandler() {
}
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = (Map)CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap(16);
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > MAX_COLUMN_WIDTH) {
columnWidth = MAX_COLUMN_WIDTH;
}else {
columnWidth = columnWidth+3;
}
Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
((Map)maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth* 256);
}
}
}
}
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData cellData = (CellData)cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch(type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
LocalDateTimeConverter 日期转换器
LocalDateTimeConverter充当了一个数据转换器,用于在Java和Excel之间转换LocalDateTime类型的数据,确保数据能够正确地在两者之间传递和显示。它将LocalDateTime对象转换为Excel字符串和将Excel字符串转换为LocalDateTime对象的逻辑,为了防止在进行导出的时候数据库上的数据日期格式与excel导出是发生的异常,当然了,你还可以进行如下的操作,可以不使用这个类,在你进行导出的属性上添加@JsonFormat(pattern = “yyyy-MM-dd HH:mm:ss”)
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime expireTime;
下面的类可以进行设置导出excel样式,可以进行背景颜色、字体大小等,看个人情况进行选择
ExcelStyleUtil 样式工具类
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.*;
/**
*
*/
public class ExcelStyleUtil implements IExcelExportStyler {
private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
private static final short FONT_SIZE_TEN = 12;
private static final short FONT_SIZE_ELEVEN = 14;
private static final short FONT_SIZE_TWELVE = 20;
/**
* 大标题样式
*/
private CellStyle headerStyle;
/**
* 每列标题样式
*/
private CellStyle titleStyle;
/**
* 数据行样式
*/
private CellStyle styles;
public ExcelStyleUtil(Workbook workbook) {
this.init(workbook);
}
/**
* 初始化样式
*
* @param workbook
*/
private void init(Workbook workbook) {
this.headerStyle = initHeaderStyle(workbook);
this.titleStyle = initTitleStyle(workbook);
this.styles = initStyles(workbook);
}
/**
* 大标题样式
*
* @param color
* @return
*/
@Override
public CellStyle getHeaderStyle(short color) {
return headerStyle;
}
/**
* 每列标题样式
*
* @param color
* @return
*/
@Override
public CellStyle getTitleStyle(short color) {
return titleStyle;
}
/**
* 数据行样式
*
* @param parity 可以用来表示奇偶行
* @param entity 数据内容
* @return 样式
*/
@Override
public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
return styles;
}
/**
* 获取样式方法
*
* @param dataRow 数据行
* @param obj 对象
* @param data 数据
*/
@Override
public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
return getStyles(true, entity);
}
/**
* 模板使用的样式设置
*/
@Override
public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
return null;
}
/**
* 初始化--大标题样式
*
* @param workbook
* @return
*/
private CellStyle initHeaderStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
//背景色
style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
//style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFont(getFont(workbook, FONT_SIZE_TWELVE, (short) 9, false));
return style;
}
/**
* 初始化--每列标题样式
*
* @param workbook
* @return
*/
private CellStyle initTitleStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, (short) 0,true));
//背景色
style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return style;
}
/**
* 初始化--数据行样式
*
* @param workbook
* @return
*/
private CellStyle initStyles(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_TEN, (short) 48,false));
style.setDataFormat(STRING_FORMAT);
return style;
}
/**
* 基础样式
*
* @return
*/
private CellStyle getBaseCellStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
//水平居中
style.setAlignment(HorizontalAlignment.CENTER);
//上下居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置自动换行
style.setWrapText(true);
return style;
}
/**
* 字体样式
*
* @param size 字体大小
* @param isBold 是否加粗
* @return
*/
private Font getFont(Workbook workbook, short size, short color, boolean isBold) {
Font font = workbook.createFont();
//字体样式
font.setFontName("宋体");
//是否加粗
//font.setBold(isBold);
//字体大小
font.setFontHeightInPoints(size);
//字体颜色
font.setColor(color);
return font;
}
public static HorizontalCellStyleStrategy getStyleStrategy() {
// 头的策略 样式调整
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 头背景 浅绿
headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
WriteFont headWriteFont = new WriteFont();
// 头字号
headWriteFont.setFontHeightInPoints((short) 14);
// 字体样式
headWriteFont.setFontName("宋体");
headWriteCellStyle.setWriteFont(headWriteFont);
// 自动换行
headWriteCellStyle.setWrapped(false);
// 设置细边框
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
// 设置边框颜色 25灰度
headWriteCellStyle.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
headWriteCellStyle.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
headWriteCellStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
headWriteCellStyle.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
// 水平对齐方式
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 垂直对齐方式
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 内容的策略 宋体
WriteCellStyle contentStyle = new WriteCellStyle();
// 设置垂直居中
//contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置 水平居中
contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
WriteFont contentWriteFont = new WriteFont();
// 内容字号
contentWriteFont.setFontHeightInPoints((short) 12);
// 字体样式
contentWriteFont.setFontName("宋体");
contentWriteFont.setColor(IndexedColors.BLUE.getIndex());
contentStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentStyle);
}
}
以上代码即可完成将多个数据列表分别写入一个Excel文件的不同工作表中,然后将生成的Excel文件通过HTTP响应发送给客户端以供下载。那么我们下次再见,谢谢您观看。