easyexcel同时导出多个excel到web(zip压缩文件)

目录

环境设置

一、表头设计:

1、利用列表

2、注解形式(推荐)

二、导出zip(可实现任意个excel文件)

三、单元格策略

只要相同的列就合并

合并判断

四、动态标题实现

五、整合(动态标题+单元格合并)

实体类举例

六、感兴趣可看(封装函数说明)

参考:


环境设置

<!-- 阿里开源easyexcel-->
        <dependency>
            <groupId>***.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.0-beta2</version>
        </dependency>

一、表头设计:

1、利用列表

private static List<List<String>> head() {
        List<List<String>> list = new ArrayList<>();
        List<String> head0 = new ArrayList<>();
        head0.add("序号");
        list.add(head0);
        Map<String, List<String>> map = getHeader();
        map.forEach((k, v) -> {
            String deviceCategory = k;
            List<String> ls = v;
            ls.forEach(e -> {
                List<String> head = new ArrayList<>();
                head.add(deviceCategory);
                head.add(e);
                list.add(head);
            });
​
•    });
•    List<String> head1 = new ArrayList<>();
•    head1.add("备注");
•    list.add(head1);
•    List<String> head2 = new ArrayList<>();
•    head2.add("埋深");
•    list.add(head2);
•    return list;
}
private static Map<String, List<String>> getHeader() {
    Map<String, List<String>> map = new HashMap<>();
    List<String> aList = new ArrayList<>();
    List<String> sList = new ArrayList<>();
    List<String> subList = new ArrayList<>();
    String column1 = "X";
    aList.add(column1);
    String column2 = "Y";
    aList.add(column2);
    String column3 = "B";
    sList.add(column3);
    String column4 = "L";
    sList.add(column4);
    String subColumn = "其它";
    subList.add(subColumn);
    subList.add("小计3");
    map.put("坐标", aList);
    map.put("经纬度", sList);
    return map;
}

2、注解形式(推荐)

@Data
@ApiModel("角色管理")
public class TSRoleVo extends ExcelModel {
​
@ExcelIgnore
@ApiModelProperty("id")
private String id;
​
•    
•    @ExcelProperty(value = {"角色表列表","导出人:${title}","角色名称"} , index = 0)
•    @ApiModelProperty(value = "角色名称")
•    @ColumnWidth(25)
•    private String roleName;//角色名称
•    
​
@ExcelProperty(value = {"角色表列表","导出人:${title}","角色编码"} , index = 1)
@ApiModelProperty(value = "角色编码")
@ColumnWidth(25)
private String roleCode;//角色编码
​
@ExcelProperty(value = {"角色表列表","导出人:${title}","部门权限组ID"} , index = 2)
@ApiModelProperty(value = "部门权限组ID")
@ColumnWidth(25)
private String departAgId;//组织机构ID  部门权限组ID
​
@Override
public boolean validation(Map<String, List<String>> validationArgs) {
    return false;
}
​
}
ps:
​
宽:@ColumnWidth(25)
​
内容的高:@ContentRowHeight(10)
​
表头的高:@HeadRowHeight(20)

二、导出zip(可实现任意个excel文件)

对于每个excel拥有两个sheet且 每个sheet实体类数据不同

public class ReceiveTaskUtils {
    public static void addFileToZip(File file, ZipOutputStream zos) throws IOException {
        InputStream fileInputStream = new FileInputStream(file);
        //zip中要放文件称为zipEntry
        ZipEntry zipEntry = new ZipEntry(file.getName());
        zos.putNextEntry(zipEntry);
        byte[] bytes = new byte[1024];
        int len;
        while ((len = fileInputStream.read(bytes)) != -1) {
            //读的内容会自动放到zip条目中,因此zipentry再输出流读完需要关闭
            zos.write(bytes, 0, len);
        }
        //要关闭这个 zos.closeEntry();
        zos.closeEntry();
        fileInputStream.close();
    }
​
    static void deleteFile(String path) {
        File file = new File(path);
        if (file.exists()) {
            file.delete();
        }
    }
​
    /**
     * 获取Date的月份
     */
    static int getMonth(Date Time) {
​
        LocalDate localDate = Time.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
        int month = localDate.getMonthValue();
        return month;
​
    }
    private static String formatToDate(Date receiveCliDate) {
​
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yy");
        String formattedDate = dateFormat.format(receiveCliDate);
        return formattedDate;
    }
    //写到本地{
    public static void export(ReceiveExportVo result, HttpServletResponse response, String type) throws Exception {
//设置输出流格式
        String name = (type.contains("已完成") ? "采购完成" : "领受任务");
        Date cliTime = (type.contains("已完成") ? ***pleteCliDate : ReceiveCliDate);
        String month=String.valueOf(getMonth(cliTime));
        response.setContentType("application/zip");
        response.setCharacterEncoding("UTF-8");
        String fileName = name + System.currentTimeMillis() + ".zip";
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
//        创建excel文件
        String fileName1 = ".\\单一来源.xlsx";
        //合并策略:
        int[] mergeColumeIndex ={1,2,3,4,5,6,7,8,9,10,11,12};
        //从第二行后开始合并
        // 调用合并单元格工具类,此工具类是没有根据合并,数据相同就合并了
        String titleDate=formatToDate(ReceiveCliDate);
        ExcelMergeUtil excelFillCellMergeStrategy = new ExcelMergeUtil(2,mergeColumeIndex,titleDate,month);
        //设置样式 标题和内容
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置头居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        log.info("创建excel之前");
        try (ExcelWriter excelWriter = EasyExcel.write(fileName1).build()) {
            WriteSheet writeSheet1 = EasyExcel.writerSheet(month + "月单一来源进行中项目统计")
                    .registerWriteHandler(excelFillCellMergeStrategy)
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .head(SingReceiveExport1Vo.class).build();
            List<SingReceiveExport1Vo> singReceiveExport1VoList = result.getSingReceiveExport1VoList();
            excelWriter.write(singReceiveExport1VoList, writeSheet1);
            WriteSheet writeSheet2 = EasyExcel.writerSheet(month + "月已安排审价项目统计")
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(excelFillCellMergeStrategy)
                    .head(SingReceiveExport2Vo.class).build();
            List<SingReceiveExport2Vo> singReceiveExport2VoList = result.getSingReceiveExport2VoList();
            excelWriter.write(singReceiveExport2VoList, writeSheet2);
        }
        String fileName2 = ".\\竞争型.xlsx";
        try (ExcelWriter excelWriter = EasyExcel.write(fileName2).build()) {
            WriteSheet writeSheet3 = EasyExcel.writerSheet(month + "月正在进行中竞争性采购项目")
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(excelFillCellMergeStrategy)
                    .head(***peteReceiveExport1Vo.class).build();
            List<***peteReceiveExport1Vo> ***peteReceiveExport1VoList = result.get***peteReceiveExport1VoList();
            excelWriter.write(***peteReceiveExport1VoList, writeSheet3);
            WriteSheet writeSheet4 = EasyExcel.writerSheet(month + "月已开标项目")
                    .registerWriteHandler(excelFillCellMergeStrategy)
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .head(***peteReceiveExport2Vo.class).build();
            List<***peteReceiveExport2Vo> ***peteReceiveExport2VoList = result.get***peteReceiveExport2VoList();
            excelWriter.write(***peteReceiveExport2VoList, writeSheet4);
        }
        //压缩路径
//        String zipFilePath =".\\"+".zip";
        // 创建输出流
        FileOutputStream fos = new FileOutputStream(fileName);
        ZipOutputStream zos = new ZipOutputStream(fos);
        // 压缩文件
        File file1 = new File(fileName1);
        File file2 = new File(fileName2);
        addFileToZip(file1, zos);
        addFileToZip(file2, zos);
        zos.close();
        fos.close();
        //将压缩文件输入流传给response输出流
        InputStream fileInputStream = new FileInputStream(fileName);
        OutputStream outputStream = response.getOutputStream();
        byte[] bytes = new byte[1024 * 8];
        int len;
        while((len=fileInputStream.read(bytes))!=-1) {
            outputStream.write(bytes,0,len);
        }
        fileInputStream.close();
        outputStream.close();
//        删除文件
        deleteFile(fileName1);
        deleteFile(fileName2);
        deleteFile(fileName);
    }
}
​
​

三、单元格策略

只要相同的列就合并(图片仅供参考)

import ***.alibaba.excel.metadata.CellData;
import ***.alibaba.excel.metadata.Head;
import ***.alibaba.excel.write.handler.CellWriteHandler;
import ***.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import ***.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.Data;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;
​
@Data
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
    private int[] mergeColumnIndex;
    private int mergeRowIndex;
​
public ExcelFillCellMergeStrategy() {
}
​
public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
    this.mergeRowIndex = mergeRowIndex;
    this.mergeColumnIndex = mergeColumnIndex;
}
​
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
​
}
​
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
​
}
​
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
​
}
​
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
   //当前行
    int curRowIndex = cell.getRowIndex();
    //当前列
    int curColIndex = cell.getColumnIndex();
​
•    if (curRowIndex > mergeRowIndex) {
•        for (int i = 0; i < mergeColumnIndex.length; i++) {
•            if (curColIndex == mergeColumnIndex[i]) {
•                mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
•                break;
•            }
•        }
•    }
}
​
/**
​
- 当前单元格向上合并
  *
- @param writeSheetHolder
- @param cell             当前单元格
- @param curRowIndex      当前行
- @param curColIndex      当前列
  */
  private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
  //获取当前行的第一列的数据和上一行的第一列数据,通过第一行数据是否相同进行合并
​
//        Cell preCell_now = cell.getSheet().getRow(curRowIndex ).getCell(curColIndex);
//        Object curData = preCell_now.getCellTypeEnum() == CellType.STRING ? preCell_now.getStringCellValue() : preCell_now.getNumeri***ellValue();
//        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex - 1);
  //      Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumeri***ellValue();
        //获取当前行的当前列的数据和上一行的当前列列数据,通过上一行数据是否相同进行合并
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumeri***ellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumeri***ellValue();
​
•    // 比较当前行的第一列的单元格与上一行是否相同,相同合并当前单元格与上一行
•    //
•    if (curData.equals(preData)) {
•        Sheet sheet = writeSheetHolder.getSheet();
•        List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
•        boolean isMerged = false;
•        for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
•            CellRangeAddress cellRangeAddr = mergeRegions.get(i);
•            // 若上一个单元格已经被合并,则先移出原有的合并单元,再重新添加合并单元
•            if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
•                sheet.removeMergedRegion(i);
•                cellRangeAddr.setLastRow(curRowIndex);
•                sheet.addMergedRegion(cellRangeAddr);
•                isMerged = true;
•            }
•        }
•        // 若上一个单元格未被合并,则新增合并单元
•        if (!isMerged) {
•            CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
•            sheet.addMergedRegion(cellRangeAddress);
•        }
•    }
}
​
}

合并判断(图片仅供参考)

package ***.ph.rfwg.util;
​
import ***.alibaba.excel.metadata.CellData;
import ***.alibaba.excel.metadata.Head;
import ***.alibaba.excel.write.handler.CellWriteHandler;
import ***.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import ***.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
​
public class ExcelMergeUtil implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;
​
public ExcelMergeUtil() {
}
​
public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex) {
    this.mergeRowIndex = mergeRowIndex;
    this.mergeColumnIndex = mergeColumnIndex;
}
​
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
​
}
​
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
​
}
​
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
​
}
​
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
​
    //当前行
    int curRowIndex = cell.getRowIndex();
    //当前列
    int curColIndex = cell.getColumnIndex();
​
    if (curRowIndex > mergeRowIndex) {
        for (int i = 0; i < mergeColumnIndex.length; i++) {
            if (curColIndex == mergeColumnIndex[i]) {
                mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                break;
            }
        }
    }
}
​
​
/**
 * 当前单元格向上合并
 *
 * @param writeSheetHolder
 * @param cell             当前单元格
 * @param curRowIndex      当前行
 * @param curColIndex      当前列
 */
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
    Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumeri***ellValue();
    Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
    Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumeri***ellValue();
    // 将当前单元格数据与上一个单元格数据比较
    Boolean dataBool = preData.equals(curData);
    //此处需要注意:因为我是按照工程名称确定是否需要合并的,所以获取每一行第二列数据和上一行第一列数据进行比较,如果相等合并,getCell里面的值,是工程名称所在列的下标
    Boolean bool = cell.getRow().getCell(1).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(1).getStringCellValue());
    if (dataBool && bool) {
        Sheet sheet = writeSheetHolder.getSheet();
        List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
        boolean isMerged = false;
        for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
            CellRangeAddress cellRangeAddr = mergeRegions.get(i);
            // 若上一个单元格已经被合并,则先移出原有的合并单元,再重新添加合并单元
            if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                sheet.removeMergedRegion(i);
                cellRangeAddr.setLastRow(curRowIndex);
                sheet.addMergedRegion(cellRangeAddr);
                isMerged = true;
            }
        }
        // 若上一个单元格未被合并,则新增合并单元
        if (!isMerged) {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
            sheet.addMergedRegion(cellRangeAddress);
        }
    }
}
}
 

四、动态标题实现

public class CustomerTitleHandler implements CellWriteHandler {
    private String title;
    PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");
    public CustomerTitleHandler(String title) {
        this.title = title;
    }
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
        if (head!=null) {
        //获取一个标题列表
            List<String> headNameList = head.getHeadNameList();
            if (CollectionUtils.isNotEmpty(headNameList)) {
                Properties properties = new Properties();
                //需要多个动态变量直接在后面加,properties相当于map会自动映射
                properties.setProperty("title", title);
                headNameList.set(0, placeholderHelper.replacePlaceholders(headNameList.get(0), properties));
            }
        }
    }
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                Cell cell, Head head, Integer integer, Boolean aBoolean) {

}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                   CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                             List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {

}

}

五、整合(动态标题+单元格合并)

实体类举例

/**
     *需求单位联系人/电话
     */
    @ColumnWidth(25)
    @ExcelProperty(value={"${titleDate}","需求单位联系人/电话"})
//    @ExcelProperty(value={"","需求单位联系人/电话"})
    private String demandUnitPeople;
    /**
     *单一来源承制单位
     * plan
     */
    @ColumnWidth(40)
    @ExcelProperty(value={"单一来源正在进行中审价项目统计","单一来源承制单位"})
    private String singleSourceProvider;

    /**
     *承制单位联系人及电话
     */
    @ColumnWidth(40)
    @ExcelProperty(value={"单一来源正在进行中审价项目统计","承制单位联系人及电话"})
    private String manufacturerPhone;

package ***.example.procurementmanagement.util;
import ***.alibaba.excel.metadata.Head;
import ***.alibaba.excel.metadata.data.CellData;
import ***.alibaba.excel.metadata.data.WriteCellData;
import ***.alibaba.excel.write.handler.CellWriteHandler;
import ***.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import ***.alibaba.excel.write.metadata.holder.WriteTableHolder;
import ***.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.PropertyPlaceholderHelper;

import java.util.List;
import java.util.Properties;

/**
 * author:lanjie
 * 单元格合并策略
 * describe:合并相同字段的列,但需要判断首列是否合并(条件合并)
 * ps:可复用
 */
@Slf4j
public class ExcelMergeUtil implements CellWriteHandler {
    //需要合并的列数组
    private int[] mergeColumnIndex;
    //合并起始行
    private int mergeRowIndex;

    private String titleDate;
    private String titleMonth;
    PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");

    public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex,String titleDate,String titleMonth) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
        this.titleDate=titleDate;
        this.titleMonth=titleMonth;
    }

    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
            List<String> headNameList = head.getHeadNameList();
            if (CollectionUtils.isNotEmpty(headNameList)){
                Properties properties = new Properties();
                properties.setProperty("titleDate", titleDate);
                properties.setProperty("titleMonth", titleMonth);
                headNameList.set(0, placeholderHelper.replacePlaceholders(headNameList.get(0), properties));
                log.info("headNameList:{}",headNameList);
            }
        }




    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
            log.info("定义单元格以后!");
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }


    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumeri***ellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumeri***ellValue();
        // 将当前单元格数据与上一个单元格数据比较
        Boolean dataBool = preData.equals(curData);
        //此处需要注意:因为我是按照工程名称确定是否需要合并的,所以获取每一行第二列数据和上一行第一列数据进行比较,如果相等合并,getCell里面的值,是工程名称所在列的下标
        Boolean bool = cell.getRow().getCell(1).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(1).getStringCellValue());
        if (dataBool && bool) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并,则先移出原有的合并单元,再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并,则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }
}

六、感兴趣可看(封装函数说明)

1、 PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");

PropertyPlaceholderHelper 的构造函数接受两个参数,分别是前缀和后缀。在你的例子中,PropertyPlaceholderHelper("${", "}") 表示创建了一个 PropertyPlaceholderHelper 实例,用于识别以 "${" 开头、以 "}" 结尾的占位符。

2、properties.setProperty("title", title);

上述代码将在属性文件中设置一个名为 "title" 的键,并将变量 title 的值作为其对应的值。也就是说,title 变量的值将被保存在属性文件中,以后可以通过该键来获取它。

题中 headNameList.set(0, placeholderHelper.replacePlaceholders(headNameList.get(0), properties));

是将properties(相当于map)中的键和你设置的动态标题进行匹配映射,因此可以直接往里面加任意个

对于为什么这里是0,因为标题分多级标题,headNameList获取的是该单元格所在列的所有标题放在一个字符串数组中,因此一级标题的索引是0,当然如果是改其他标题,你只需要更换索引进行映射即可。

3、replacePlaceholders() 方法接受两个参数:

  1. template 是包含占位符的原始字符串,即待替换的字符串。可以是 "点个赞吧${man}"其中man是动态标题

  2. name 是要替换占位符 ${name} 的实际值。

String resolvedString = placeholderHelper.replacePlaceholders(template, name);

结果

本地

导出结果:

上传到web

excel结果:

参考:

表头设计参考

单元合并参考链接

有问题可留言!

转载请说明出处内容投诉
CSS教程_站长资源网 » easyexcel同时导出多个excel到web(zip压缩文件)

发表评论

欢迎 访客 发表评论

一个令你着迷的主题!

查看演示 官网购买