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);
}
}
}
}