需求:
要根据数据来导出这个excel
左侧菜单有一百多个固定菜单,这些肯定不能放到数据库里建字段,所以就根据上面的抽检总点数,合格总点数,合格率三个字段建立数据库字段。
技术实现:
我们选择的是easyExcel 这个操作excel 框架,以下链接是其官网。
EasyExcel · 语雀EasyExcel是一个基于Java的简单、省内存的读...https://www.yuque.com/easyexcel/doc/easyexcel官网上也有其git 代码地址:GitHub - alibaba/easyexcel: 快速、简洁、解决大文件内存溢出的java处理Excel工具GitHub - alibaba/easyexcel: 快速、简洁、解决大文件内存溢出的java处理Excel工具快速、简洁、解决大文件内存溢出的java处理Excel工具. Contribute to alibaba/easyexcel development by creating an account on GitHub.
https://github.com/alibaba/easyexcel这个git 地址很重要,把代码拉下来,可以看到里面有很多作者写的测试类。对每个api 都做了对应的demo。我们想要实现某些功能的话,可以直接参考官方demo,也可以在官网查询demo
GitHub - alibaba/easyexcel: 快速、简洁、解决大文件内存溢出的java处理Excel工具
上面了解到了有对应的哪些技术支撑可以实现我们的功能,下面我们就做甄别,目前具体哪项技术能实现我们的功能。
实现思路:
一,根据easyExcel 填充的方式
easyExcel 填充的方式:通过创建excel 模板,然后将查询出来的数据填充到模板中,可以横向填充,也可以竖向填充,但是这块我的需求是需要动态表头,填充方法我不知道如何设置动态表头。很尴尬,这条路走不通。
二,通过写数据的方式
就是将所有的数据都自己提供,不是填充模板的形式,写数据有两种方式,一种是根据对象来写数据,一种是不创建对象写数据。
创建对象的方式
将对象的注解设置好,对应表头,然后查出对象的数据。然后再设置 输出地址加文件名称
不创建对象的写入
不创建对象根据easyExcel api设置表头数据,设置内容数据。因为该方法即可以设置左侧目录,又可以设置动态表头。所以就采用了这个方法。
代码实现
controller
@PostMapping("/exportExcel")
public void exportExcel(@RequestBody SummaryRoadConditions summaryRoadConditions) throws IOException {
String fileName = "E:\\公路工程质量监督数据汇总表" + System.currentTimeMillis() + ".xlsx";
EasyExcel.write(fileName).head(head(summaryRoadConditions)).registerWriteHandler(new CustomCellWriteHandler()).sheet("公路工程质量监督数据汇总表").doWrite(data());
}
package easttrans.traffic.domain.core;
import com.baomidou.mybatisplus.annotation.*;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.ToString;
import org.springframework.format.annotation.DateTimeFormat;
import java.io.Serializable;
import java.util.Date;
/**
* @author: zjk
* @Description: 路数据汇总表
* @date: 2022/4/6 16:45
*/
@Data
@ToString
@TableName(value = "summary_road_conditions")
public class SummaryRoadConditions implements Serializable {
@TableId(type = IdType.AUTO)
private Integer id;
@TableField(fill = FieldFill.INSERT)
@JsonFormat(pattern="yyyy-MM-dd HH:mm:SS",timezone = "GMT+8")
private Date createTime;
@ApiModelProperty(value = "创建人标识")
private Integer createUser;
@ApiModelProperty(value = "创建者名称")
private String createUsername;
@TableField(fill = FieldFill.INSERT)
@JsonFormat(pattern="yyyy-MM-dd HH:mm:SS",timezone = "GMT+8")
private Date updateTime;
@ApiModelProperty(value = "更新人标识")
private Integer updateUser;
@ApiModelProperty(value = "更新者名称")
private String updateUsername;
@ApiModelProperty(value = "类别")
private String category;
@ApiModelProperty(value = "统计时间")
@JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM")
@DateTimeFormat(pattern="yyyy-MM")
private Date statisticsTime;
@ApiModelProperty(value = "抽检总点数")
private String totalNumberSamplingPoints;
@ApiModelProperty(value = "合格总点数")
private String totalQualifyingPoints;
@ApiModelProperty(value = "合格率(%)")
private String passRate;
@TableField(exist = false)
@ApiModelProperty(value = "用户Id")
private Integer userId;
@TableField(exist = false)
@ApiModelProperty(value = "开始时间")
private String startTime;
@TableField(exist = false)
@ApiModelProperty(value = "结束时间")
private String endTime;
@TableField(exist = false)
@ApiModelProperty(value = "季度")
private String quarter;
//逻辑删除
@TableLogic
private String disabled;
}
public List<List<String>> head (SummaryRoadConditions summaryRoadConditions){
//初始化表头
String bigTitle = "泰州市干线公路工程质量监督检查情况汇总";
List<List<String>> head = new ArrayList<>();
List<String> title = new ArrayList<>(Arrays.asList(bigTitle,"统计时间:", "单位工程","单位工程","路基工程","路基工程","路基工程","路基工程","路基工程","路基工程","路基工程",
"路基工程","路基工程","路基工程","路基工程","路基工程","路面工程","路面工程","路面工程","路面工程","路面工程","路面工程",
"路面工程","路面工程","路面工程","路面工程","路面工程","路面工程","路面工程","路面工程","桥梁工程","桥梁工程","桥梁工程",
"桥梁工程","桥梁工程","桥梁工程","桥梁工程","桥梁工程","桥梁工程","桥梁工程","桥梁工程","桥梁工程","桥梁工程","桥梁工程",
"桥梁工程","桥梁工程","桥梁工程","桥梁工程","隧道工程","隧道工程","隧道工程","隧道工程","隧道工程","隧道工程","隧道工程",
"隧道工程","隧道工程","隧道工程","隧道工程","隧道工程","隧道工程","隧道工程","隧道工程","隧道工程","隧道工程","隧道工程",
"交通安全设施","交通安全设施","交通安全设施","交通安全设施","交通安全设施","交通安全设施","交通安全设施","交通安全设施",
"交通安全设施","交通安全设施","交通安全设施","交通安全设施","交通安全设施","钢结构检测","钢结构检测","钢结构检测","钢结构检测",
"钢结构检测","钢结构检测","钢结构检测","钢结构检测","原材料及产品","原材料及产品","原材料及产品","原材料及产品","原材料及产品",
"原材料及产品","原材料及产品","原材料及产品","原材料及产品","原材料及产品","原材料及产品","原材料及产品","原材料及产品","原材料及产品","原材料及产品","原材料及产品"));
List<String> stringList = new ArrayList<>(Arrays.asList(bigTitle, "统计时间:", "检测指标","检测指标", "软土地基", "软土地基","软土地基","路基弯沉",
"路基压实度", "小桥涵混凝土强度","支挡工程砂浆及混凝土强度","支挡工程断面尺寸","排水工程铺砌厚度及断面尺寸",
"小桥涵主要结构尺寸","合计","备注:小桥涵混凝土强度统计按构件数统计,支挡工程砂浆及混凝土强度统计构件数统计",
"沥青路面弯沉","混凝土路面强度","沥青路面压实度","路面平整度","路面厚度","混凝土路面相邻板高差","路面抗滑",
"路面基层强度及厚度","路面基层横坡","路面基层平整度","路面底基层强度及厚度","路面底基层横坡","路面底基层平整度","合计",
"下部钢筋保护层厚度","上部钢筋保护层厚度","上部混凝土强度","墩台(下部)混凝土强度","桥面宽度","桥面横坡","桥面厚度","墩台垂直度",
"下部主要结构尺寸","上部主要结构尺寸","桩身完整性","受力钢筋间距","支座垫石高程","孔道压浆密实度","锚下有效预应力","弯起钢筋尺寸","合计","备注:上、下部混凝土强度按照构件数量统计",
"矿山法","矿山法","矿山法","矿山法","矿山法","矿山法","矿山法","盾构法","盾构法","盾构法","盾构法",
"明挖及堰筑法","明挖及堰筑法","明挖及堰筑法","明挖及堰筑法","明挖及堰筑法","合计","备注:混凝土强度按构件数量统计",
"护栏","护栏","护栏","护栏","护栏","护栏","标线","标线","标志","标志","标志","标志","合计","涂装质量检测(油漆、拉拔力等)",
"钢材物理化学性能","干膜总厚度","高强螺栓扭矩","主要结构尺寸","焊缝探伤检测","焊缝探伤检测","焊缝探伤检测","沥青","沥青混合料","水泥","钢筋",
"粗集料","细集料","细集料氯离子含量","橡胶支座","锚具","夹片","钢绞线","高强螺栓","外加剂","土工格栅","土工布","合计"));
List<String> stringList1 = new ArrayList<>(Arrays.asList(bigTitle, "统计时间:","检测指标","检测指标","桩距","桩径","桩基完整性(管桩)", "路基弯沉",
"路基压实度", "小桥涵混凝土强度","支挡工程砂浆及混凝土强度","支挡工程断面尺寸","排水工程铺砌厚度及断面尺寸",
"小桥涵主要结构尺寸","合计","备注:小桥涵混凝土强度统计按构件数统计,支挡工程砂浆及混凝土强度统计构件数统计",
"沥青路面弯沉","混凝土路面强度","沥青路面压实度","路面平整度","路面厚度","混凝土路面相邻板高差","路面抗滑",
"路面基层强度及厚度","路面基层横坡","路面基层平整度","路面底基层强度及厚度","路面底基层横坡","路面底基层平整度","合计",
"下部钢筋保护层厚度","上部钢筋保护层厚度","上部混凝土强度","墩台(下部)混凝土强度","桥面宽度","桥面横坡","桥面厚度","墩台垂直度",
"下部主要结构尺寸","上部主要结构尺寸","桩身完整性","受力钢筋间距","支座垫石高程","孔道压浆密实度","锚下有效预应力","弯起钢筋尺寸","合计","备注:上、下部混凝土强度按照构件数量统计",
"衬砌强度","衬砌厚度","净空、净宽","墙面平整度","锚杆间距及长度","锚杆拉拔力","锚杆注浆密实度","混凝土强度","钢筋保护层厚度","净空、净宽","几何尺寸",
"混凝土强度","钢筋保护层厚度","净空、净宽","侧墙厚度","顶板厚度","合计","备注:混凝土强度按构件数量统计","护栏横梁中心高度","立柱壁厚度","立柱埋深","波形板厚度","砼护栏断面尺寸",
"混凝土护栏强度","标线厚度","标线逆反射系数","立柱竖直度","标志板净空","标志逆反射系数","标志几何尺寸","合计","涂装质量检测(油漆、拉拔力等)",
"钢材物理化学性能","干膜总厚度","高强螺栓扭矩","主要结构尺寸","超声波","磁粉","射线","沥青","沥青混合料","水泥","钢筋",
"粗集料","细集料","细集料氯离子含量","橡胶支座","锚具","夹片","钢绞线","高强螺栓","外加剂","土工格栅","土工布","合计"));
head.add(title);
head.add(stringList);
head.add(stringList1);
//查找数据
List<SummaryRoadConditions> summaryRoadConditionList = roadStatisticsService.dataSummary(summaryRoadConditions);
System.out.println("data:"+summaryRoadConditionList);
//循环填充数据
for (SummaryRoadConditions sum: summaryRoadConditionList) {
//季度
String quarter = sum.getQuarter();
//月份
Date statisticsTime = sum.getStatisticsTime();
int month = statisticsTime.getMonth()+1;
//抽检总点数
String totalNumberSamplingPoints =sum.getTotalNumberSamplingPoints();
//合格总点数
String totalQualifyingPoints = sum.getTotalQualifyingPoints();
//合格率(%)
String passRate = sum.getPassRate();
List<String> stringList2 = new ArrayList<>(Arrays.asList((bigTitle+",第"+quarter+"季度,"+month+"月,抽检总点数,"+totalNumberSamplingPoints).split(",")));
List<String> stringList3 = new ArrayList<>(Arrays.asList((bigTitle+",第"+quarter+"季度,"+month+"月,合格总点数,"+totalQualifyingPoints).split(",")));
List<String> stringList4 = new ArrayList<>(Arrays.asList((bigTitle+",第"+quarter+"季度,"+month+"月,合格率(%),"+passRate).split(",")));
head.add(stringList2);
head.add(stringList3);
head.add(stringList4);
}
return head;
}
package easttrans.traffic.utils;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.util.BooleanUtils;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
/**
* @author: zjk
* @Description: 表头样式拦截器
* @date: 2022/4/8 10:40
*/
@Slf4j
public class CustomCellWriteHandler implements CellWriteHandler {
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
WriteCellData<?> cellData = context.getFirstCellData();
int rowNum = context.getRow().getRowNum();
Integer columnIndex = context.getColumnIndex();
Sheet sheet = context.getWriteSheetHolder().getSheet();
Cell cell = context.getCell();
if (BooleanUtils.isTrue(context.getHead())) {
WriteCellStyle writeCellStyle = cellData.getWriteCellStyle();
//字体
WriteFont writeFont = writeCellStyle.getWriteFont();
//背景颜色
writeCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
//字体大小
writeFont.setFontHeightInPoints((short)16);
//字体加粗
writeFont.setBold(true);
writeCellStyle.setWriteFont(writeFont);
//列宽,行高
sheet.setColumnWidth(cell.getColumnIndex(),4000);
sheet.setDefaultRowHeight((short)400);
//行号 从第四行到最后一行,列 从第一列到第三列,为左侧浅蓝色目录
if ( rowNum >=3 && columnIndex<=2) {
// 这里需要去cellData 获取样式
// 很重要的一个原因是 WriteCellStyle 和 dataFormatData绑定的 简单的说 比如你加了 DateTimeFormat
// ,已经将writeCellStyle里面的dataFormatData 改了 如果你自己new了一个WriteCellStyle,可能注解的样式就失效了
// 然后 getOrCreateStyle 用于返回一个样式,如果为空,则创建一个后返回
//背景颜色
writeCellStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 这样样式就设置好了 后面有个FillStyleCellWriteHandler 默认会将 WriteCellStyle 设置到 cell里面去 所以可以不用管了
// 字体大小
writeFont.setFontHeightInPoints((short)9);
//字体加粗
writeFont.setBold(true);
writeCellStyle.setWriteFont(writeFont);
}
if (rowNum == 3 ){
//背景色
writeCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());//SKY_BLUE
writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
}
//单位工程,检测指标
if (rowNum <=3 && rowNum >= 2 && columnIndex <= 2){
//背景色
writeCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());//SKY_BLUE
writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
}
}
}
}
private List<SummaryRoadConditions> data() {
return null;
}