easyExcel根据模板生成多sheet数据

菜的不行的java

已于 2024-02-01 18:37:12 修改

阅读量517

点赞数 5

文章标签： excel spring boot

于 2024-02-01 18:36:34 首次发布

本文链接：https://blog.csdn.net/weixin_44534767/article/details/135980281

版权

该代码示例展示了如何利用EasyExcel库根据sheet1模版生成包含多个sheet的Excel文件，通过读取模板，替换特定sheet的数据。

摘要由CSDN通过智能技术生成

easyExcel根据sheet1模版生成多sheet表格
依赖只修要导入最新的easyExcel即可
在这里插入图片描述

package com.topsec.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

public class Excelll {


    public static void main(String[] args) throws Exception {
        //表格sheet名
        List<String> taskNoList = Arrays.asList("1","2","3");
        String s3Url = null;
        String path = "C:\\Users\\12415\\Desktop\\";
        //中间临时模版
        final String modelFileName = "temp.xlsx";
        //生成的数据
        final String outsFileName = "newExcel" + new SimpleDateFormat("yyyyMMddHH").format(new Date()) + ".xlsx";
        final File outModelFile = new File(path, modelFileName);
        final File outsFile = new File(path, outsFileName);
        //模版地址
        String templateFileName = path + "测试模板.xlsx";
        //复制模版到新的临时文件 生成多sheet
        createModel(taskNoList, outModelFile, templateFileName);
    //        ExcelWriter excelWriter = setStyle(outModelFile, outsFile);
        //调用阿里的接口
        ExcelWriter excelWriter = EasyExcel.write(outsFile).
                withTemplate(outModelFile)
                .build();

        for (String taskNo : taskNoList) {
            WriteSheet writeSheet = EasyExcel.writerSheet(taskNo).build();
                Map<String, Object> map = new HashMap<>();
                //替换的 各个sheet模版数据 可以自定义筛选替换响应的sheet 测试就采用sheet名
            map.put("test",taskNo);
                excelWriter.fill(map, writeSheet);
        }
        excelWriter.finish();
        outModelFile.delete();
    }


    /**
     * 生成多sheet模板
     * @param taskNoList
     * @param outModelFile
     * @param templateFileName
     */
    private static void createModel(List<String> taskNoList, File outModelFile, String templateFileName) throws Exception {
        try (FileOutputStream fileOutputStream = new FileOutputStream(outModelFile)) {
            //读取excel模板
            XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(templateFileName));
            workbook.cloneSheet(0);

            // 获取要复制的工作表索引
            int sourceSheetIndex = 0;
            Sheet sourceSheet = workbook.getSheetAt(sourceSheetIndex);

            Workbook targetWorkbook = new XSSFWorkbook();
            for (int i = 0; i < taskNoList.size(); i++) {
                String sheetName = taskNoList.get(i);
                Sheet targetSheet = targetWorkbook.createSheet(sheetName);
                // 复制原始工作表的内容到目标工作表
                copySheet(sourceSheet, targetSheet);
            }
            // 保存目标文件
            targetWorkbook.write(fileOutputStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
    /**
     * 获取模板内容和格式
     *
     * @param sourceSheet
     * @param targetSheet
     */
    private static void copySheet(Sheet sourceSheet, Sheet targetSheet) {
        Workbook targetWorkbook = targetSheet.getWorkbook();
        CellStyle targetCellStyle;

        // 复制合并的单元格区域
        for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sourceSheet.getMergedRegion(i);
            targetSheet.addMergedRegion(mergedRegion);
        }

        for (Row sourceRow : sourceSheet) {
            Row targetRow = targetSheet.createRow(sourceRow.getRowNum());

            for (Cell sourceCell : sourceRow) {
                Cell targetCell = targetRow.createCell(sourceCell.getColumnIndex());
                targetCell.setCellType(sourceCell.getCellType());

                // 复制单元格的值
                switch (sourceCell.getCellType()) {
                    case STRING:
                        targetCell.setCellValue(sourceCell.getStringCellValue());
                        break;
                    case NUMERIC:
                        targetCell.setCellValue(sourceCell.getNumericCellValue());
                        break;
                    case BOOLEAN:
                        targetCell.setCellValue(sourceCell.getBooleanCellValue());
                        break;
                }

                // 复制单元格的样式
                CellStyle sourceCellStyle = sourceCell.getCellStyle();
                targetCellStyle = targetWorkbook.createCellStyle();
                targetCellStyle.cloneStyleFrom(sourceCellStyle);
                targetCell.setCellStyle(targetCellStyle);
            }
        }
    }

}