工作需求:
最近需求是要根据检索的数据信息给excel模板表中插入对应的数据,数据可能有多条,如果超过两条的话那么需要新增行,对本人而言难点在于,很多单元格有函数公式在里面,以及下拉列表要附带到新增行中,例图如下:
经过很多次思考与痛苦,懂的都懂,excel的操作真的是头痛,初始使用的是easyexcel达到了以上需求,样例代码如下:当然要填写的位置需要加上模板{item.id}等等
/**
* 给ATS车站数据验证单模板写数据 --- > F:\workspace\卡斯柯项目\casco_project\文档\审核文档
* 附带实现下拉框功能
*/
@Test
public void Test010() {
List<Map<String, Object>> paramValueMapListNew = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Map<String, Object> cmap = new HashMap<>();
cmap.put("id", i+1);
cmap.put("stationName", "药品" + i);
cmap.put("areaSelect","");
cmap.put("vpiView","");
paramValueMapListNew.add(cmap);
}
long begin = System.currentTimeMillis();
//设置输出流和模板信息
ExcelWriter excelWriter = EasyExcel.write("F:\\workspace\\卡斯柯项目\\casco_project\\文档\\审核文档\\ATS车站数据验证单模板.xlsx")
.registerWriteHandler(new SheetWriteHandler() {
@SneakyThrows
@Override
public void afterSheetCreate(SheetWriteHandlerContext context) {
int lastRowNum = context.getWriteSheetHolder().getSheet().getLastRowNum()-1;
// 区间设置 第一列第一行和第二行的数据。由于第一行是头,所以第一、二行的数据实际上是第二三行
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(3, paramValueMapListNew.size() + 3, 2, lastRowNum);
DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] {"√", "×","N/T","N/A","UTT"});
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
//增加条件格式
SheetConditionalFormatting sheetCF = context.getWriteSheetHolder().getSheet().getSheetConditionalFormatting();
//当内容为×的时候,变成浅红显示
ConditionalFormattingRule ruleX = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL,"\"×\"",null);
PatternFormatting fill = ruleX.createPatternFormatting();
String rgbS = "FFC7CE";
byte[] rgbB = Hex.decodeHex(rgbS); // get byte array from hex string
XSSFColor color = new XSSFColor(rgbB, null); //IndexedColorMap has no usage until now. So it can be set null.
fill.setFillBackgroundColor(color);
fill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
//当内容为N/T的时候,变成浅绿显示
ConditionalFormattingRule ruleNT = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL,"\"N/T\"",null);
fill = ruleNT.createPatternFormatting();
rgbS = "C6EFCE";
rgbB = Hex.decodeHex(rgbS); // get byte array from hex string
color = new XSSFColor(rgbB, null); //IndexedColorMap has no usage until now. So it can be set null.
fill.setFillBackgroundColor(color);
fill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
//当内容为N/T的时候,变成浅绿显示
ConditionalFormattingRule ruleNA = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL,"\"N/A\"",null);
fill = ruleNA.createPatternFormatting();
rgbS = "DDDDDD";
rgbB = Hex.decodeHex(rgbS); // get byte array from hex string
color = new XSSFColor(rgbB, null); //IndexedColorMap has no usage until now. So it can be set null.
fill.setFillBackgroundColor(color);
fill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
//当内容为N/T的时候,变成浅绿显示
ConditionalFormattingRule ruleUTT = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL,"\"UTT\"",null);
fill = ruleUTT.createPatternFormatting();
rgbS = "FFEB9C";
rgbB = Hex.decodeHex(rgbS); // get byte array from hex string
color = new XSSFColor(rgbB, null); //IndexedColorMap has no usage until now. So it can be set null.
fill.setFillBackgroundColor(color);
fill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
ConditionalFormattingRule[] cfRules1 = new ConditionalFormattingRule[]{ruleX,ruleNT};
ConditionalFormattingRule[] cfRules2 = new ConditionalFormattingRule[]{ruleNA,ruleUTT};
CellRangeAddress[] regions = {new CellRangeAddress(3, paramValueMapListNew.size() + 3, 2, 3)};
sheetCF.addConditionalFormatting(regions, cfRules1);
sheetCF.addConditionalFormatting(regions,cfRules2);
}
})
// .registerWriteHandler(new CellWriteHandler() {
// @Override
// public void afterCellDispose(CellWriteHandlerContext context) {
// Cell cell = context.getCell();
// log.info("col:{},row:{}",cell.getColumnIndex()+1,cell.getRowIndex()+1);
// if(cell.getRowIndex() == 4 && cell.getColumnIndex() == 3){
// CellStyle cellStyle = context.getWriteWorkbookHolder().getWorkbook().createCellStyle();
// cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
// // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND
// cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// // cellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
// cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
// cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
// cellStyle.setBorderTop(BorderStyle.MEDIUM);
// cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
// cellStyle.setBorderBottom(BorderStyle.MEDIUM);
// cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
// cellStyle.setBorderLeft(BorderStyle.MEDIUM);
// cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// cellStyle.setBorderRight(BorderStyle.MEDIUM);
// cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
// cell.setCellStyle(cellStyle);
// }
// }
// })
.withTemplate("F:\\workspace\\卡斯柯项目\\casco_project\\文档\\审核文档\\UEVOL-CASCO-7206-ATS车站数据验证单模板.xlsx").build();
WriteSheet writeSheet0 = EasyExcel.writerSheet("1-2 控制区域").build();
//开启自动换行,自动换行表示每次写入一条list数据是都会重新生成一行空行,此选项默认是关闭的,需要提前设置为true
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
FillWrapper item = new FillWrapper("item", paramValueMapListNew);
excelWriter.fill(item, fillConfig, writeSheet0);
excelWriter.finish();
System.out.println(System.currentTimeMillis() - begin);
}
总算以为事情可以告一段落,但是客户总是不让你如意,又说模板文件的列可能会变化,那么意味着下拉列表的复制需要支持列的动态变化,以上代码就不支持这个需求,于是经过痛苦的探索通过poi写出了如下模板,也是结合了网上的插入行模板做了升级修改,如作者看到!!本人只是借鉴以及分享,没有其他用意,
那么代码如下
package cn.com.casco.ats.utils;
import cn.hutool.poi.excel.ExcelWriter;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import java.util.List;
@Slf4j
public class ExcelCopyUtils {
// public static void main(String[] args) {
// ExcelWriter writer = ExcelUtil.getWriter(new File("F:\\workspace\\卡斯柯项目\\casco_project\\文档\\审核文档\\UEVOL-CASCO-7206-ATS车站数据验证单模板.xlsx"),"1-3 进路显示及控制");
// // startRow=5 行标为5的行后插入2行,样式拷贝第5行的样式
// int startRow= 5;
// int rows = 2;
// XSSFSheet sheet = (XSSFSheet)writer.getSheet();
//
// InsertRow(writer, startRow, rows, sheet,false,1);
// }
/**
*
* @param writer ExcelWriter
* @param startRow 插入行的行标,即在哪一行下插入
* @param rows 插入多少行
* @param sheet XSSFSheet
* @param copyvalue 新行复制(startRow-1)行的样式,而且在拷贝行的时候可以指定是否需要拷贝值
*/
public static void insertRow(ExcelWriter writer, int startRow, int rows, XSSFSheet sheet, Boolean copyvalue, int wirteIndex) {
if(sheet.getRow(startRow+1)==null){
// 如果复制最后一行,首先需要创建最后一行的下一行,否则无法插入,Bug 2023/03/20修复
sheet.createRow(startRow+1);
}
XSSFRow startRows = sheet.getRow(startRow);
XSSFCell cell = startRows.getCell(startRows.getLastCellNum() - 2);
System.out.println("cell---->" + cell);
XSSFCellStyle cellStyle = cell.getCellStyle();
//先获取原始的合并单元格address集合
List<CellRangeAddress> originMerged = sheet.getMergedRegions();
Row sourceRow = sheet.getRow(startRow - 1);
for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
log.info("复制样式下拉表--->第{}行", i);
//如果原有单元格的数据有效性为下拉框,则将新单元格也设置为下拉框,并进行动态更新
for (DataValidation sourceDataValidation : sheet.getDataValidations()) {
if (sourceDataValidation != null) {
if (i>wirteIndex) {
log.info("i等于----------->{}", i);
DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
CellRangeAddressList addressList = new CellRangeAddressList(startRow, startRow + rows -1, i, i);
DataValidation dataValidation = dataValidationHelper.createValidation(sourceDataValidation.getValidationConstraint(), addressList);
sheet.addValidationData(dataValidation);
}
}
}
}
for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
CellRangeAddress region = sheet.getMergedRegion(i);
//判断移动的行数后重新拆分
if(region.getFirstRow()>startRow){
sheet.removeMergedRegion(i);
}
}
sheet.shiftRows(startRow,sheet.getLastRowNum(),rows,true,false);
XSSFRow row = sheet.createRow(startRow);
for(CellRangeAddress cellRangeAddress : originMerged) {
//这里的8是插入行的index,表示这行之后才重新合并
if(cellRangeAddress.getFirstRow() > startRow) {
//你插入了几行就加几,我这里插入了一行,加1
int firstRow = cellRangeAddress.getFirstRow() + rows;
CellRangeAddress newCellRangeAddress = new CellRangeAddress(firstRow, (firstRow + (cellRangeAddress
.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(),
cellRangeAddress.getLastColumn());
sheet.addMergedRegion(newCellRangeAddress);
}
}
writer.flush();
CellCopyPolicy cellCopyPolicy = new CellCopyPolicy();
cellCopyPolicy.setCopyCellValue(copyvalue);
cellCopyPolicy.isCopyCellValue();
for (int i = 0; i < rows; i++) {
sheet.copyRows(startRow-1,startRow-1,startRow+i,cellCopyPolicy);
}
writer.close();
}
/**
* @param startRow 插入行的行标,即在哪一行下插入
* @param rows 插入多少行
* @param sheet XSSFSheet
* @param copyvalue 新行复制(startRow-1)行的样式,而且在拷贝行的时候可以指定是否需要拷贝值
* @param wirteIndex 要填写的最后一个index
*/
public static void insertRow(int startRow, int rows, XSSFSheet sheet,Boolean copyvalue,int wirteIndex) {
if(sheet.getRow(startRow+1)==null){
// 如果复制最后一行,首先需要创建最后一行的下一行,否则无法插入,Bug 2023/03/20修复
sheet.createRow(startRow+1);
}
XSSFRow startRows = sheet.getRow(startRow);
XSSFCell cell = startRows.getCell(startRows.getLastCellNum() - 2);
XSSFCellStyle cellStyle = cell.getCellStyle();
//先获取原始的合并单元格address集合
List<CellRangeAddress> originMerged = sheet.getMergedRegions();
for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
CellRangeAddress region = sheet.getMergedRegion(i);
//判断移动的行数后重新拆分
if(region.getFirstRow()>startRow){
sheet.removeMergedRegion(i);
}
}
sheet.shiftRows(startRow,sheet.getLastRowNum(),rows,true,false);
XSSFRow row = sheet.createRow(startRow);
Row sourceRow = sheet.getRow(startRow - 1);
for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
log.info("复制样式下拉表--->第{}行", i);
//如果原有单元格的数据有效性为下拉框,则将新单元格也设置为下拉框,并进行动态更新
for (DataValidation sourceDataValidation : sheet.getDataValidations()) {
if (sourceDataValidation != null) {
if (i > wirteIndex) {
log.info("i等于----------->{}", i);
DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
CellRangeAddressList addressList = new CellRangeAddressList(startRow, startRow + rows, i, i);
DataValidation dataValidation = dataValidationHelper.createValidation(sourceDataValidation.getValidationConstraint(), addressList);
sheet.addValidationData(dataValidation);
break;
}
}
}
}
for(CellRangeAddress cellRangeAddress : originMerged) {
//这里的8是插入行的index,表示这行之后才重新合并
if(cellRangeAddress.getFirstRow() > startRow) {
//你插入了几行就加几,我这里插入了一行,加1
int firstRow = cellRangeAddress.getFirstRow() + rows;
CellRangeAddress newCellRangeAddress = new CellRangeAddress(firstRow, (firstRow + (cellRangeAddress
.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(),
cellRangeAddress.getLastColumn());
sheet.addMergedRegion(newCellRangeAddress);
}
}
CellCopyPolicy cellCopyPolicy = new CellCopyPolicy();
cellCopyPolicy.setCopyCellValue(copyvalue);
cellCopyPolicy.isCopyCellValue();
for (int i = 0; i < rows; i++) {
sheet.copyRows(startRow-1,startRow-1,startRow+i,cellCopyPolicy);
}
}
}
开箱即用,如有问题欢迎来探讨,因为这个问题百度了很久没有发现有相应文章,才想发布一份给遇到困难的人提供一份帮助,
如果对你有帮助!请为我点个赞谢谢啦