跨境派

跨境派

跨境派,专注跨境行业新闻资讯、跨境电商知识分享!

当前位置:首页 > 工具系统 > ERP系统 > java技术:easyexcel同时导出多个excel到web(zip压缩文件)

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

时间:2024-04-17 10:25:35 来源:网络cs 作者:晨起 栏目:ERP系统 阅读:

标签: 文件  技术  同时 

目录

环境设置

一、表头设计:

1、利用列表

2、注解形式(推荐)

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

三、单元格策略

只要相同的列就合并

合并判断

四、动态标题实现

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

实体类举例

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

参考:


环境设置

<!-- 阿里开源easyexcel-->        <dependency>            <groupId>com.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​@Overridepublic 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("已完成") ? CompleteCliDate : 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(CompeteReceiveExport1Vo.class).build();            List<CompeteReceiveExport1Vo> competeReceiveExport1VoList = result.getCompeteReceiveExport1VoList();            excelWriter.write(competeReceiveExport1VoList, writeSheet3);            WriteSheet writeSheet4 = EasyExcel.writerSheet(month + "月已开标项目")                    .registerWriteHandler(excelFillCellMergeStrategy)                    .registerWriteHandler(horizontalCellStyleStrategy)                    .head(CompeteReceiveExport2Vo.class).build();            List<CompeteReceiveExport2Vo> competeReceiveExport2VoList = result.getCompeteReceiveExport2VoList();            excelWriter.write(competeReceiveExport2VoList, 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 com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.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;​@Datapublic class ExcelFillCellMergeStrategy implements CellWriteHandler {    private int[] mergeColumnIndex;    private int mergeRowIndex;​public ExcelFillCellMergeStrategy() {}​public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {    this.mergeRowIndex = mergeRowIndex;    this.mergeColumnIndex = mergeColumnIndex;}​@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {​}​@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {​}​@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {​}​@Overridepublic 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.getNumericCellValue();//        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex - 1);  //      Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();        //获取当前行的当前列的数据和上一行的当前列列数据,通过上一行数据是否相同进行合并        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();​•    // 比较当前行的第一列的单元格与上一行是否相同,相同合并当前单元格与上一行•    //•    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 com.ph.rfwg.util;​import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.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;}​@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {​}​@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {​}​@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {​}​@Overridepublic 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.getNumericCellValue();    Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);    Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();    // 将当前单元格数据与上一个单元格数据比较    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) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,                                   CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic 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 com.example.procurementmanagement.util;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.metadata.data.CellData;import com.alibaba.excel.metadata.data.WriteCellData;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import com.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:可复用 */@Slf4jpublic 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.getNumericCellValue();        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();        // 将当前单元格数据与上一个单元格数据比较        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() 方法接受两个参数:

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

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

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

结果

本地

导出结果:

上传到web

excel结果:

参考:

表头设计参考

单元合并参考链接

有问题可留言!

本文链接:https://www.kjpai.cn/news/2024-04-17/159711.html,文章来源:网络cs,作者:晨起,版权归作者所有,如需转载请注明来源和作者,否则将追究法律责任!

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。

文章评论