easyexce复杂表头导出（二）
导出结果：
思路：
步骤：
1.引入依赖
<!-- easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.0-beta1</version>
</dependency>
2.所需类
public class ColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 140;
private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<Integer, Map<Integer, Integer>>(6);
@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) {
return;
}
Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap<Integer, Integer>(8);
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = dataLength(cellDataList, cell, isHead);
if (columnWidth < 0) {
return;
}
if (columnWidth > MAX_COLUMN_WIDTH) {
columnWidth = MAX_COLUMN_WIDTH;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 140);
}
}
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
}
CellData cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
}
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;
}
}
}
3.代码
public static void main(String[] args) {
//配置字体，表头背景等
HorizontalCellStyleStrategy horizontalCellStyleStrategy = setConfigure();
List<List<Object>> lists = new ArrayList<List<Object>>();
int count = 0;
Double sum = 0.0;
for(int i = 0 ; i<10 ; i++){
List<Object> list = new ArrayList<>();
for(int j = 0 ; j<7 ; j++){
list.add("测试"+i);
sum += 9999;
}
if(count == 0 ){
list.add("时间");
list.add("06-17");
list.add("07-17");
list.add("08-17");
list.add("09-17");
list.add("10-17");
list.add("9999");
}else {
list.add("工资");
list.add("9999");
list.add("9999");
list.add("9999");
list.add("9999");
list.add("9999");
list.add("9999");
}
lists.add(list);
count++;
if(count >1){
count = 0;
}
}
//合计(注意:设置的为2行为一个格子,需要给每行赋值,并且每一列都要赋值)
for(int i = 0 ;i < 2 ;i++){
List<Object> list = new ArrayList<>();
for(int j = 0 ; j<6 ; j++) {
list.add("");
}
list.add("合计");
for(int j = 0 ; j<6 ; j++) {
list.add("");
}
list.add(sum);
lists.add(list);
}
try {
//设置请求信息(获取HttpServletResponse，可通过HttpServletResponse给导出数据命名)
/* response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=sjbkzjap.xlsx");
ExcelWriter excelWriter = EasyExcelFactory.getWriter(response.getOutputStream());
*/
String outPath = "c:/demo.xlsx";
File file = new File(outPath);
if(file.exists()){
// 文件存在
file.delete();
}
ExcelWriter excelWriter = EasyExcelFactory.getWriter(new FileOutputStream(outPath));
//设置单元格合并策略
LoopMergeStrategy loopMergeStrategy1 = new LoopMergeStrategy(2, 0);
LoopMergeStrategy loopMergeStrategy2 = new LoopMergeStrategy(2, 1);
LoopMergeStrategy loopMergeStrategy3 = new LoopMergeStrategy(2, 2);
LoopMergeStrategy loopMergeStrategy4 = new LoopMergeStrategy(2, 3);
LoopMergeStrategy loopMergeStrategy5 = new LoopMergeStrategy(2, 4);
LoopMergeStrategy loopMergeStrategy6 = new LoopMergeStrategy(2, 5);
LoopMergeStrategy loopMergeStrategy7 = new LoopMergeStrategy(2, 6);
LoopMergeStrategy loopMergeStrategy8 = new LoopMergeStrategy(2, 13);
//获取集合最后2行数据并加上表头为所需的行数;0:为行合并开始的列,12为结束的列
OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy = new OnceAbsoluteMergeStrategy(lists.size()-1-1+5, lists.size()-1+5, 0, 12);
//需要合并的列,相当的数据就会合并,不合并则加(i % 2 == 0 ? "" : "\t")
CellRangeAddress cellRangeAddress = new CellRangeAddress(9,10,1,1);
WriteSheet writeSheet = EasyExcel.writerSheet(0, "sheet")
.registerWriteHandler(loopMergeStrategy1)
.registerWriteHandler(loopMergeStrategy2)
.registerWriteHandler(loopMergeStrategy3)
.registerWriteHandler(loopMergeStrategy4)
.registerWriteHandler(loopMergeStrategy5)
.registerWriteHandler(loopMergeStrategy6)
.registerWriteHandler(loopMergeStrategy7)
.registerWriteHandler(loopMergeStrategy8)
.registerWriteHandler(horizontalCellStyleStrategy)
.registerWriteHandler(new ColumnWidthStyleStrategy())
.registerWriteHandler(onceAbsoluteMergeStrategy)
.build();
// 创建一个表格
WriteTable table = new WriteTable();
// 动态添加 表头 headList --> 所有表头行集合
//表头数据
List<List<String>> headList = setHeadList();
table.setHead(headList);
excelWriter.write(lists,writeSheet,table);
excelWriter.finish();
System.out.println("导出成功!");
}catch (IOException e){
e.printStackTrace();
}
}
public static List<List<String>> setHeadList(){
List<String> headTitle0 = new ArrayList<String>();
List<String> headTitle1 = new ArrayList<String>();
List<String> headTitle2 = new ArrayList<String>();
List<String> headTitle3 = new ArrayList<String>();
List<String> headTitle4 = new ArrayList<String>();
List<String> headTitle5 = new ArrayList<String>();
List<String> headTitle6 = new ArrayList<String>();
List<String> headTitle7 = new ArrayList<String>();
List<String> headTitle8 = new ArrayList<String>();
List<String> headTitle9 = new ArrayList<String>();
List<String> headTitle10 = new ArrayList<String>();
List<String> headTitle11 = new ArrayList<String>();
List<String> headTitle12 = new ArrayList<String>();
List<String> headTitle13 = new ArrayList<String>();
//设置第一列为项目导出标题
headTitle0.add("测试导出");
headTitle1.add("测试导出");
headTitle2.add("测试导出");
headTitle3.add("测试导出");
headTitle4.add("测试导出");
headTitle5.add("测试导出");
headTitle6.add("测试导出");
headTitle7.add("测试导出");
headTitle8.add("测试导出");
headTitle9.add("测试导出");
headTitle10.add("测试导出");
headTitle11.add("测试导出");
headTitle12.add("测试导出");
headTitle13.add("测试导出");
headTitle0.add("");
headTitle1.add("");
headTitle2.add("");
headTitle3.add("");
headTitle4.add("");
headTitle5.add("");
headTitle6.add("");
headTitle7.add("");
headTitle8.add("");
headTitle9.add("");
headTitle10.add("");
headTitle11.add("");
headTitle12.add("");
headTitle13.add("单位：元");
headTitle0.add("编号");
headTitle1.add("姓名");
headTitle2.add("年龄");
headTitle3.add("居住地");
headTitle4.add("父亲");
headTitle5.add("母亲");
headTitle6.add("工作单位");
headTitle7.add("");
headTitle8.add("工资");
headTitle9.add("工资");
headTitle10.add("工资");
headTitle11.add("工资");
headTitle12.add("工资");
headTitle13.add("工资");
headTitle0.add("编号");
headTitle1.add("姓名");
headTitle2.add("年龄");
headTitle3.add("居住地");
headTitle4.add("父亲");
headTitle5.add("母亲");
headTitle6.add("工作单位");
headTitle7.add("");
headTitle8.add("第一个月");
headTitle9.add("第二个月");
headTitle10.add("第三个月");
headTitle11.add("第四个月");
headTitle12.add("第五个月");
headTitle13.add("平均工资");
headTitle0.add("编号");
headTitle1.add("姓名");
headTitle2.add("年龄");
headTitle3.add("居住地");
headTitle4.add("父亲");
headTitle5.add("母亲");
headTitle6.add("工作单位");
headTitle7.add("");
headTitle8.add("第一个月");
headTitle9.add("第二个月");
headTitle10.add("第三个月");
headTitle11.add("第四个月");
headTitle12.add("第五个月");
headTitle13.add("平均工资");
//列数据保存
List<List<String>> headList = new ArrayList<List<String>>();
headList.add(headTitle0);
headList.add(headTitle1);
headList.add(headTitle2);
headList.add(headTitle3);
headList.add(headTitle4);
headList.add(headTitle5);
headList.add(headTitle6);
headList.add(headTitle7);
headList.add(headTitle8);
headList.add(headTitle9);
headList.add(headTitle10);
headList.add(headTitle11);
headList.add(headTitle12);
headList.add(headTitle13);
return headList;
}
//配置字体，表头背景等
private static HorizontalCellStyleStrategy setConfigure() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 10);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 字体策略
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short) 10);
contentWriteCellStyle.setWriteFont(contentWriteFont);
//边框
//导出数据垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//导出数据水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
//设置 自动换行
contentWriteCellStyle.setWrapped(true);
//设置
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
注：暂时只找到指定的列合并
扫一扫
2252
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
