excel合并列导出文件

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
    </dependency>

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>3.17</version>
    </dependency>

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>

@Log(title = "字典类型", businessType = BusinessType.EXPORT)
@PreAuthorize("@ss.hasPermi('system:dict:export')")
@PostMapping("/export")
public void export(HttpServletResponse response, SysDictType dictType) throws IOException
{
List list = dictTypeService.selectDictTypeList(dictType);
//List pilebodysList = pilebodycheckService.pilebodystatisticsmonth(sysDepartDto, month);
//设置序号
for (int i = 1;i <= list.size();i++){
list.get(i-1).setDictId(Long.valueOf(i));
}
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// response.setContentType("application/vnd.ms-excel");
// response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("存量建筑垃圾堆体治理进度月报表", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
//内容样式策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//垂直居中,水平居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
//设置 自动换行
contentWriteCellStyle.setWrapped(true);
// 字体策略
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short) 12);
contentWriteCellStyle.setWriteFont(contentWriteFont);
//头策略使用默认
WriteCellStyle headWriteCellStyle = new WriteCellStyle();

    //excel如需下载到本地,只需要将response.getOutputStream()换成File即可(注释掉以上response代码)
    EasyExcel.write(response.getOutputStream(), SysDictType.class)
            //设置输出excel版本,不设置默认为xlsx
           // .excelType(ExcelTypeEnum.XLS).head(SysDictType.class)
            //设置拦截器或自定义样式
            .registerWriteHandler(new MonthSheetWriteHandler())
            .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle))
            .sheet("存量建筑垃圾堆体治理进度月报表")
            //设置默认样式及写入头信息开始的行数
            .useDefaultStyle(true).relativeHeadRowIndex(1)
            //这里的addsumColomn方法是个添加合计的方法,可删除
            .doWrite(list/*pilebodycheckService.addSumColomn(pilebodysList)*/);
   // return new WebApiResponse(200, "生成excel文件成功", null);
}

package com.AAA.web.ExcelUtil;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

public class MonthSheetWriteHandler implements SheetWriteHandler {

@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

}

@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    Workbook workbook = writeWorkbookHolder.getWorkbook();
    Sheet sheet = workbook.getSheetAt(0);
/*    Row row1 = sheet.createRow(0);
    row1.setHeight((short) 500);
    Cell cell = row1.createCell(0);
    //设置单元格内容
    cell.setCellValue("附件2");*/
    //设置标题
    Row row2 = sheet.createRow(0);
    row2.setHeight((short) 800);
    Cell cell1 = row2.createCell(0);
    cell1.setCellValue("存量建筑垃圾堆体治理进度月报表");
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    Font font = workbook.createFont();
    font.setBold(true);
    font.setFontHeight((short) 400);
    cellStyle.setFont(font);
    cell1.setCellStyle(cellStyle);
    sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, 17));
    //设置填表日期,填报人,联系方式
/*    Row row3 = sheet.createRow(2);
    row3.setHeight((short) 500);*/

/ row3.createCell(1).setCellValue("填表日期");
row3.createCell(11).setCellValue("填表人");
row3.createCell(15).setCellValue("联系方式");/

}
}

package com.AAA.common.core.domain.entity;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Pattern;
import javax.validation.constraints.Size;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import lombok.experimental.Accessors;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
import com.AAA.common.annotation.Excel;
import com.AAA.common.annotation.Excel.ColumnType;
import com.AAA.common.core.domain.BaseEntity;

import java.util.Date;

/**

• 字典类型表 sys_dict_type
  *

• @author ruoyi
  /
  @Data
  @ToString
  @NoArgsConstructor
  @Accessors(chain = true)
  @ContentRowHeight(45)
  @HeadRowHeight(50)
  public class SysDictType /extends BaseEntity*/
  {
  private static final long serialVersionUID = 1L;

  /* 字典主键 /
  @ExcelProperty(value = {"字典主键","字典主键"},index = 0)
  @ColumnWidth(30)

  private Long dictId;

  /* 字典名称 /
  @ExcelProperty(value = {"字典名称","字典名称"},index = 1)
  @ColumnWidth(30)

  private String dictName;

  /* 字典类型 /
  // @Excel(name = "字典类型")
  @ExcelProperty(value = {"字典类型","字典类型"},index = 2)
  @ColumnWidth(30)

  private String dictType;

  /* 状态（0正常 1停用） /
  @ExcelProperty(value = {"合并列","状态"},index = 3)
  private String status;

  /* 创建时间 /
  @ExcelProperty(value = {"合并列","创建时间"},index = 4)
  @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
  @ColumnWidth(30)

  private Date createTime;

  /* 备注 /
  @ExcelProperty(value = {"合并列","备注"},index = 5)
  @ColumnWidth(30)

  private String remark;

  public Long getDictId()
  {

   return dictId;
  

  }

  public void setDictId(Long dictId)
  {

   this.dictId = dictId;
  

  }
  // @ExcelProperty(value = {"字典类型","字典类型"},index = 0)

  @NotBlank(message = "字典名称不能为空")
  @Size(min = 0, max = 100, message = "字典类型名称长度不能超过100个字符")
  public String getDictName()
  {

   return dictName;
  

  }

  public void setDictName(String dictName)
  {

   this.dictName = dictName;
  

  }

  @NotBlank(message = "字典类型不能为空")
  @Size(min = 0, max = 100, message = "字典类型类型长度不能超过100个字符")
  @Pattern(regexp = "^[a-z][a-z0-9_]*$", message = "字典类型必须以字母开头，且只能为（小写字母，数字，下滑线）")
  public String getDictType()
  {

   return dictType;
  

  }

}

package com.AAA.common.core.domain.entity;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Pattern;
import javax.validation.constraints.Size;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import lombok.experimental.Accessors;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
import com.AAA.common.annotation.Excel;
import com.AAA.common.annotation.Excel.ColumnType;
import com.AAA.common.core.domain.BaseEntity;

import java.util.Date;

/**

• 字典类型表 sys_dict_type
  *

• @author ruoyi
  /
  @Data
  @ToString
  @NoArgsConstructor
  @Accessors(chain = true)
  @ContentRowHeight(45)
  @HeadRowHeight(50)
  public class SysDictType /extends BaseEntity*/
  {
  private static final long serialVersionUID = 1L;

  /* 字典主键 /
  @ExcelProperty(value = {"字典主键","字典主键"},index = 0)
  @ColumnWidth(30)

  private Long dictId;

  /* 字典名称 /
  @ExcelProperty(value = {"字典名称","字典名称"},index = 1)
  @ColumnWidth(30)

  private String dictName;

  /* 字典类型 /
  // @Excel(name = "字典类型")
  @ExcelProperty(value = {"字典类型","字典类型"},index = 2)
  @ColumnWidth(30)

  private String dictType;

  /* 状态（0正常 1停用） /
  @ExcelProperty(value = {"合并列","状态"},index = 3)
  private String status;

  /* 创建时间 /
  @ExcelProperty(value = {"合并列","创建时间"},index = 4)
  @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
  @ColumnWidth(30)

  private Date createTime;

  /* 备注 /
  @ExcelProperty(value = {"合并列","备注"},index = 5)
  @ColumnWidth(30)

  private String remark;

  public Long getDictId()
  {

   return dictId;
  

  }

  public void setDictId(Long dictId)
  {

   this.dictId = dictId;
  

  }
  // @ExcelProperty(value = {"字典类型","字典类型"},index = 0)

  @NotBlank(message = "字典名称不能为空")
  @Size(min = 0, max = 100, message = "字典类型名称长度不能超过100个字符")
  public String getDictName()
  {

   return dictName;
  

  }

  public void setDictName(String dictName)
  {

   this.dictName = dictName;
  

  }

  @NotBlank(message = "字典类型不能为空")
  @Size(min = 0, max = 100, message = "字典类型类型长度不能超过100个字符")
  @Pattern(regexp = "^[a-z][a-z0-9_]*$", message = "字典类型必须以字母开头，且只能为（小写字母，数字，下滑线）")
  public String getDictType()
  {

   return dictType;
  

  }

}

/**

• 开始时间
  excel中不显示 /
  @ExcelIgnore