hutool导出excle设置多sheet设置表头样式,设置字体样式等示例
@Test
public void test2(){
ExcelWriter writer = ExcelUtil.getWriter(true);
writer.renameSheet("库龄");
writer.merge(0, 1, 0, 0, "序号", true);
writer.merge(0, 1, 1, 1,"市场", true);
writer.merge(0, 0, 2, 3,"库龄>720天", true);
writer.writeCellValue(2,1,"金额（万元）");
writer.writeCellValue(3,1,"体积（方）");
writer.merge(0, 0,4,5,"库龄360-720天", true);
writer.writeCellValue(4,1,"金额（万元）");
writer.writeCellValue(5,1,"体积（方）");
writer.merge(0, 0,6,7,"库龄90-180天", true);
writer.writeCellValue(6,1,"金额（万元）");
writer.writeCellValue(7,1,"体积（方）");
writer.merge(0, 0,8,9,"库龄<90天", true);
writer.writeCellValue(8,1,"金额（万元）");
writer.writeCellValue(9,1,"体积（方）");
writer.merge(0, 0,10,11,"合计", true);
writer.writeCellValue(10,1,"金额（万元）");
writer.writeCellValue(11,1,"体积（方）");
writer.merge(2,2,0,1,"海外商品事业三部合计",true);
writer.writeCellValue(2,2,"580");
writer.writeCellValue(3,2,"171");
writer.writeCellValue(4,2,"171");
writer.writeCellValue(5,2,"171");
writer.writeCellValue(6,2,"171");
writer.writeCellValue(7,2,"171");
writer.writeCellValue(8,2,"171");
writer.writeCellValue(9,2,"171");
writer.writeCellValue(10,2,"171");
writer.writeCellValue(11,2,"171");
AtomicInteger integer = new AtomicInteger(1);
writer.writeCellValue(0,3,integer.getAndIncrement());
writer.writeCellValue(1,3,"中东及非洲区");
writer.writeCellValue(2,3,"580");
writer.writeCellValue(3,3,"171");
writer.writeCellValue(4,3,"171");
writer.writeCellValue(5,3,"171");
writer.writeCellValue(6,3,"171");
writer.writeCellValue(7,3,"171");
writer.writeCellValue(8,3,"171");
writer.writeCellValue(9,3,"171");
writer.writeCellValue(10,3,"171");
writer.writeCellValue(11,3,"171");
CellStyle cellStyle = writer.createCellStyle();
Font font = writer.createFont();
//红色字体
font.setColor(HSSFColor.HSSFColorPredefined.DARK_RED.getIndex());
//字体加粗
font.setBold(true);
cellStyle.setFont(font);
//垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//设置4个边框
cellStyle.setBorderTop(writer.getCellStyle().getBorderTop());
cellStyle.setBorderRight(writer.getCellStyle().getBorderRight());
cellStyle.setBorderBottom(writer.getCellStyle().getBorderBottom());
cellStyle.setBorderLeft(writer.getCellStyle().getBorderLeft());
//设置背景颜色
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
;
//设置头部背景颜色
writer.getHeadCellStyle().setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
writer.getHeadCellStyle().setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font headerFont = writer.createFont();
headerFont.setBold(true);
writer.getHeadCellStyle().setFont(headerFont);
writer.setDefaultRowHeight(20);
//设置第一行区域灰色
CellStyle cellStyle1 = writer.createCellStyle();
//设置灰色
cellStyle1.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
cellStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置垂直居中和水平居中
cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle1.setAlignment(HorizontalAlignment.CENTER);
//设置左右边框
cellStyle1.setBorderLeft(writer.getCellStyle().getBorderLeft());
cellStyle1.setBorderRight(writer.getCellStyle().getBorderRight());
//设置字体加粗
Font font1 = writer.createFont();
font1.setBold(true);
cellStyle1.setFont(font1);
for(int i=0;i<=11;i++){
if( 2<= i && i<=7){
//第2列到第7列 设置表头部分样式 红色字体
writer.setStyle(cellStyle,i,0);
//第2列到第7列 设置表头部分样式 红色字体
writer.setStyle(cellStyle,i,1);
}else{
//设置表头部分样式
writer.setStyle(writer.getHeadCellStyle(),i,0);
//设置表头部分样式
writer.setStyle(writer.getHeadCellStyle(),i,1);
}
//设置第一行汇总数据样式
writer.setStyle(cellStyle1,i,2);
//设置列宽
writer.setColumnWidth(i,16);
}
writeIP(writer);
writeExists(writer);
//ExportUtil.headers(response, true, filename);
//writer.flush(response.getOutputStream());
writer.flush(new File("E:\\a.xlsx"));
}
//写 ip节庆版权
public void writeIP(ExcelWriter writer){
writer.setSheet("临期版权节庆");
writer.merge(0, 1, 0, 0, "序号", true);
writer.merge(0, 1, 1, 1,"市场", true);
writer.merge(0, 0, 2, 3,"临期（有效期<540天）", true);
writer.writeCellValue(2,1,"金额（万元）");
writer.writeCellValue(3,1,"体积（方）");
writer.merge(0, 0,4,5,"版权（距版权终止期<=270天）", true);
writer.writeCellValue(4,1,"金额（万元）");
writer.writeCellValue(5,1,"体积（方）");
writer.merge(0, 0,6,7,"版权（强圣诞&强万圣）", true);
writer.writeCellValue(6,1,"金额（万元）");
writer.writeCellValue(7,1,"体积（方）");
writer.merge(0, 0,8,9,"合计（去重汇总）", true);
writer.writeCellValue(8,1,"金额（万元）");
writer.writeCellValue(9,1,"体积（方）");
writer.merge(2,2,0,1,"海外商品事业三部合计",true);
writer.writeCellValue(2,2,"580");
writer.writeCellValue(3,2,"171");
writer.writeCellValue(4,2,"171");
writer.writeCellValue(5,2,"171");
writer.writeCellValue(6,2,"171");
writer.writeCellValue(7,2,"171");
writer.writeCellValue(8,2,"171");
writer.writeCellValue(9,2,"171");
AtomicInteger integer = new AtomicInteger(1);
writer.writeCellValue(0,3,integer.getAndIncrement());
writer.writeCellValue(1,3,"中东及非洲区");
writer.writeCellValue(2,3,"580");
writer.writeCellValue(3,3,"171");
writer.writeCellValue(4,3,"171");
writer.writeCellValue(5,3,"171");
writer.writeCellValue(6,3,"171");
writer.writeCellValue(7,3,"171");
writer.writeCellValue(8,3,"171");
writer.writeCellValue(9,3,"171");
Font font = writer.createFont();
//字体加粗
font.setBold(true);
//设置头部背景颜色 字体 默认行高
writer.getHeadCellStyle().setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
writer.getHeadCellStyle().setFillPattern(FillPatternType.SOLID_FOREGROUND);
writer.getHeadCellStyle().setFont(font);
writer.setDefaultRowHeight(20);
//设置第一行区域灰色
CellStyle cellStyle1 = writer.createCellStyle();
//设置灰色
cellStyle1.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
cellStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置垂直居中和水平居中
cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle1.setAlignment(HorizontalAlignment.CENTER);
//设置左右边框
cellStyle1.setBorderLeft(writer.getCellStyle().getBorderLeft());
cellStyle1.setBorderRight(writer.getCellStyle().getBorderRight());
Font font1 = writer.createFont();
font1.setBold(true);
font1.setFontHeightInPoints((short)12);
cellStyle1.setFont(font1);
for(int i=0;i<=9;i++){
//设置表头部分样式
writer.setStyle(writer.getHeadCellStyle(),i,0);
//设置表头部分样式
writer.setStyle(writer.getHeadCellStyle(),i,1);
//设置第一行汇总数据样式
writer.setStyle(cellStyle1,i,2);
//设置列宽
writer.setColumnWidth(i,16);
}
}
//在库未提
public void writeExists(ExcelWriter writer){
writer.setSheet("在库未提");
writer.merge(0, 1, 0, 0, "序号", true);
writer.merge(0, 1, 1, 1,"市场", true);
writer.merge(0, 0, 2, 3,"A（超交期60天以上）", true);
writer.writeCellValue(2,1,"金额（万元）");
writer.writeCellValue(3,1,"体积（方）");
writer.merge(0, 0,4,5,"B（超交期30-60天）", true);
writer.writeCellValue(4,1,"金额（万元）");
writer.writeCellValue(5,1,"体积（方）");
writer.merge(0, 0,6,7,"C（超交期15-30天）", true);
writer.writeCellValue(6,1,"金额（万元）");
writer.writeCellValue(7,1,"体积（方）");
writer.merge(0, 0,8,9,"D（交期前后15天内）", true);
writer.writeCellValue(8,1,"金额（万元）");
writer.writeCellValue(9,1,"体积（方）");
writer.merge(0, 0,10,11,"E（交期前置15天以上）", true);
writer.writeCellValue(10,1,"金额（万元）");
writer.writeCellValue(11,1,"体积（方）");
writer.merge(0, 0,12,13,"合计", true);
writer.writeCellValue(12,1,"金额（万元）");
writer.writeCellValue(13,1,"体积（方）");
writer.merge(2,2,0,1,"海外商品事业三部合计",true);
writer.writeCellValue(2,2,"580");
writer.writeCellValue(3,2,"171");
writer.writeCellValue(4,2,"171");
writer.writeCellValue(5,2,"171");
writer.writeCellValue(6,2,"171");
writer.writeCellValue(7,2,"171");
writer.writeCellValue(8,2,"171");
writer.writeCellValue(9,2,"171");
writer.writeCellValue(10,2,"171");
writer.writeCellValue(11,2,"171");
writer.writeCellValue(12,2,"171");
writer.writeCellValue(13,2,"171");
AtomicInteger integer = new AtomicInteger(1);
writer.writeCellValue(0,3,integer.getAndIncrement());
writer.writeCellValue(1,3,"中东及非洲区");
writer.writeCellValue(2,3,"580");
writer.writeCellValue(3,3,"171");
writer.writeCellValue(4,3,"171");
writer.writeCellValue(5,3,"171");
writer.writeCellValue(6,3,"171");
writer.writeCellValue(7,3,"171");
writer.writeCellValue(8,3,"171");
writer.writeCellValue(9,3,"171");
writer.writeCellValue(10,3,"171");
writer.writeCellValue(11,3,"171");
writer.writeCellValue(12,3,"171");
writer.writeCellValue(13,3,"171");
CellStyle cellStyle = writer.createCellStyle();
Font font = writer.createFont();
//红色字体
font.setColor(HSSFColor.HSSFColorPredefined.DARK_RED.getIndex());
//字体加粗
font.setBold(true);
cellStyle.setFont(font);
//垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//设置4个边框
cellStyle.setBorderTop(writer.getCellStyle().getBorderTop());
cellStyle.setBorderRight(writer.getCellStyle().getBorderRight());
cellStyle.setBorderBottom(writer.getCellStyle().getBorderBottom());
cellStyle.setBorderLeft(writer.getCellStyle().getBorderLeft());
//设置背景颜色
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置头部背景颜色
writer.getHeadCellStyle().setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
writer.getHeadCellStyle().setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font headerFont = writer.createFont();
headerFont.setBold(true);
writer.getHeadCellStyle().setFont(headerFont);
writer.setDefaultRowHeight(20);
//设置第一行区域灰色
CellStyle cellStyle1 = writer.createCellStyle();
//设置灰色
cellStyle1.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
cellStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置垂直居中和水平居中
cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle1.setAlignment(HorizontalAlignment.CENTER);
//设置左右边框
cellStyle1.setBorderLeft(writer.getCellStyle().getBorderLeft());
cellStyle1.setBorderRight(writer.getCellStyle().getBorderRight());
//设置字体加粗
Font font1 = writer.createFont();
font1.setBold(true);
cellStyle1.setFont(font1);
for(int i=0;i<=13;i++){
if( 2<= i && i<=5){
//第2列到第7列 设置表头部分样式 红色字体
writer.setStyle(cellStyle,i,0);
//第2列到第7列 设置表头部分样式 红色字体
writer.setStyle(cellStyle,i,1);
}else{
//设置表头部分样式
writer.setStyle(writer.getHeadCellStyle(),i,0);
//设置表头部分样式
writer.setStyle(writer.getHeadCellStyle(),i,1);
}
//设置第一行汇总数据样式
writer.setStyle(cellStyle1,i,2);
//设置列宽
writer.setColumnWidth(i,14);
}
}效果图
设置样式关键代码
CellStyle headerStyle = writer.getHeadCellStyle();
Font font = writer.createFont();
//字体加粗
font.setBold(true);
headerStyle.setFont(font);
//垂直居中
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//水平居中
headerStyle.setAlignment(HorizontalAlignment.CENTER);
//设置背景颜色
headerStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.TAN.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置边框
headerStyle.setBorderTop(writer.getCellStyle().getBorderTop());
headerStyle.setBorderRight(writer.getCellStyle().getBorderRight());
headerStyle.setBorderBottom(writer.getCellStyle().getBorderBottom());
headerStyle.setBorderLeft(writer.getCellStyle().getBorderLeft());示例二
@ApiOperation("请求日志查询接口")
@PostMapping("/exportExcel")
public void exportExcel(@RequestBody SysRequestLogReq logReq, HttpServletResponse response){
List<SysRequestLog> list = sysRequestLogService.lambdaQuery()
.eq(StrUtil.isNotBlank(logReq.getUsername()), SysRequestLog::getUserCode, logReq.getUsername())
.ge(StrUtil.isNotBlank(logReq.getBeginDateTime()),SysRequestLog::getBeginTime,logReq.getBeginDateTime())
.le(StrUtil.isNotBlank(logReq.getBeginDateTime()),SysRequestLog::getEndTime,logReq.getBeginDateTime())
.orderByDesc(SysRequestLog::getRequestTime)
.list();
ExcelWriter writer = ExcelUtil.getWriter(true);
ExcelWriter excelWriter = writer.renameSheet("人员统计信息");
// 自动换行
CellStyle cellStyle = excelWriter.getCellStyle();
cellStyle.setWrapText(true);
// 设置单元格宽高
// excelWriter.setDefaultRowHeight(22);
excelWriter.setColumnWidth(0, 14);
excelWriter.setColumnWidth(1, 14);
excelWriter.setColumnWidth(2, 24);
excelWriter.setColumnWidth(3, 12);
excelWriter.setColumnWidth(4, 40);
excelWriter.setColumnWidth(5, 40);
//设置表头
excelWriter.writeHeadRow(Arrays.asList("用户编码","IP地址","操作路径","状态","请求参数","返回结果"));
CellStyle cellStyle2 = excelWriter.getHeadCellStyle();
//设置单元格颜色
cellStyle2.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
//全部填充
cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
AtomicInteger currRow = new AtomicInteger(1);
for (SysRequestLog rsp : list) {
excelWriter.writeCellValue(0, currRow.get(), rsp.getUserCode());
excelWriter.writeCellValue(1, currRow.get(), rsp.getClientIp());
excelWriter.writeCellValue(2, currRow.get(), rsp.getUri());
excelWriter.writeCellValue(3, currRow.get(), rsp.getResultCode() == 0 ?"正常":"异常");
excelWriter.writeCellValue(4, currRow.get(), rsp.getRequestParam());
excelWriter.writeCellValue(5, currRow.get(), rsp.getResultDesc());
currRow.getAndIncrement();
}
try {
//设置content—type
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset:utf-8");
//设置标题
String exportFileName = URLEncoder.encode("系统日志.xlsx", "UTF-8");
//Content-disposition是MIME协议的扩展，MIME协议指示MIME用户代理如何显示附加的文件。
response.setHeader("Content-Disposition", "attachment;filename=" + exportFileName);
ServletOutputStream outputStream = response.getOutputStream();
//导出到浏览器
excelWriter.flush(outputStream,true);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
