Java poi导出excel 将模板覆盖了

发布于 2021-12-08 21:30:49 字数 16389 浏览 918 评论 11

这是模板excel：

，

但是导出数据之后就变成这样了：

求高手解决。

导出excel的方法如下：

public static Sheet export(Workbook workbook, ExportConfiguration exportConfiguration, ExportDataVo exportDataVo, int sheetIndex, int startIndex, int lIndex, String sheetName) throws Exception {
   Sheet sheet = workbook.cloneSheet(0);
   workbook.setSheetOrder(sheet.getSheetName(), sheetIndex);
   int list_data_row = exportConfiguration.getList_data_row();
   Boolean summry_bold = exportConfiguration.getSummry_bold();
   int add_row_mode = exportConfiguration.getAdd_row_mode();
   List dataList = exportDataVo.getDataList();
   Integer multiple = exportConfiguration.getMultiple();
   Integer space = exportConfiguration.getSpace();

   workbook.setSheetName(sheetIndex, sheetName);
   writeSheetHead(exportDataVo, sheet);
Object summryBean = null;
boolean unadd = true;
Font summryFont = null;

int count=0;
if(dataList !=null){
   for (int i=0, rowIndex=list_data_row; i<dataList.size(); i++, rowIndex++) {
   Object object = dataList.get(i);

   if (rowIndex > list_data_row && add_row_mode == 2) {
       sheet.shiftRows(rowIndex, rowIndex+2, 1, true, false);
   }
   Row row = sheet.getRow(rowIndex);
   if (row == null) {
       sheet.shiftRows(rowIndex, dataList.size(), 1);
       row = sheet.createRow(rowIndex);
       if (rowIndex > list_data_row) {
           row.setHeightInPoints(sheet.getRow(list_data_row).getHeightInPoints());
       }
   }
Field[] fieldArr = object.getClass().getDeclaredFields();
for (Field field : fieldArr) {
ExportColumn exportIndex = field.getAnnotation(ExportColumn.class);
if (exportIndex == null) {
continue;
}
String fieldName = field.getName();
int columnIndex = exportIndex.index();
int lastIndex = exportIndex.lastIndex();
boolean lastRow = exportIndex.lastRow();
String summry = exportIndex.summry();
boolean hasEnter = exportIndex.hasEnter();
CellStyle cellStyle = sheet.getRow(list_data_row).getCell(columnIndex).getCellStyle();
if (summryFont == null) {
   summryFont = workbook.createFont();
   Font oldFont = workbook.getFontAt(cellStyle.getFontIndex());
   summryFont.setFontName(oldFont.getFontName());
   summryFont.setFontHeightInPoints(oldFont.getFontHeightInPoints());
   summryFont.setBold(true);
}
AbstractExportModel abstractExportModel = (AbstractExportModel) object;
boolean isSummryRow = abstractExportModel.isSummryRow();
if(StringUtils.isNotBlank(summry) && rowIndex == list_data_row) {
   summryBean = summryBean == null ? object.getClass().newInstance() : summryBean;
   BeanUtils.setProperty(summryBean, fieldName, summry);
   BeanUtils.setProperty(summryBean, "summryRow", true);
}
String value = BeanUtils.getProperty(object, fieldName);

if (isSummryRow && StringUtils.isNotBlank(value) && value.contains("%s")) {
   value = String.format(value, TextUtil.toChineseNum(sheetIndex));
}
boolean isTotal = exportIndex.isTotal();
if (isTotal && !isSummryRow) {
   if (rowIndex == list_data_row && summryBean == null) {
       summryBean = object.getClass().newInstance();
       BeanUtils.setProperty(summryBean, "summryRow", true);
   }
   if (StringUtils.isNotBlank(value)) {
       addationTotalValue(summryBean, fieldName, value);
   }
}
Cell cell = row.getCell(columnIndex);
if(cell == null) {
   cell = row.createCell(columnIndex);
}

if (lastIndex > -1 && !ExcelUtil.isMerge(sheet, rowIndex, columnIndex)) {
   CellRangeAddress cra = new CellRangeAddress(rowIndex, rowIndex, columnIndex, lastIndex);
   sheet.addMergedRegion(cra);
   setRegionStyle(cellStyle, cra, sheet);
} else {
   cell.setCellStyle(cellStyle);
}
if (lastRow&&count-i==0) {
   count=0;
   for (int j=0; j<dataList.size(); j++) {
               Object obj = dataList.get(j);
               String fieldName1 = fieldName;
               String value2 = BeanUtils.getProperty(obj, fieldName1);
               if(value.equals(value2)){
                   count++;
               }
           }
   if(count>1){
       CellRangeAddress cra =new CellRangeAddress(rowIndex, rowIndex+count-1, 0, 0);
       sheet.addMergedRegion(cra);
       setRegionStyle(cellStyle, cra, sheet);
       count=count+i;
   }else{
       count=i+1;
   }
} else {
   cell.setCellStyle(cellStyle);
}
   addSummryStyle(isSummryRow, workbook, summry_bold, cell, summryFont);

   if (hasEnter && StringUtils.isNotBlank(value)) {
       int length = TextUtil.count(value, "[|]");
       if (value.endsWith("|")) {
           length --;
       }
   value = value.replaceAll("[|]", "rn");
   if (length > 1) {
   cell.getCellStyle().setWrapText(true);
   }
}
   setCellValue(object, fieldName, value, cell);
}

if (i == dataList.size() -1 && summryBean != null && unadd) {
       dataList.add(summryBean);
       unadd = false;
}
//
}
}

if(multiple != null && multiple ==2){
   int lastIndex = sheet.getLastRowNum();
int rowCount = lastIndex + 1;
float hp = sheet.getRow(lastIndex).getHeightInPoints();
for(int i=1; i<space; i++){
   sheet.createRow(lastIndex+i).setHeightInPoints(hp);
}
int offset = lastIndex + space + 1;
for(int i=0; i<rowCount; i++){
   sheet.createRow(offset + i);
}

//合并区域创建
mergerRegion(sheet, offset);

//复制内容
for(int i=0; i<rowCount; i++){
   copyRow(workbook, sheet, sheet.getRow(i), sheet.getRow(offset+i), true);
}
}

return sheet;
}