如需客戶端指定excel版本,只需要判斷后綴名然后在controller中的.excelType(ExcelTypeEnum.XLS)做指定輸出內容格式即可
***(注意表格行高列寬統一設置是在實體類的類名注解上,如果需要對表格進行精細的寬高設置需要刪除掉這兩個注解,可以在攔截器使用row的方法進行設置)
1. ## 引入依賴
com.alibaba
easyexcel
2.1.4
2.實體類(注解法)
package com.jpxx.admin.pilebody.service.api.dto;
import com.alibaba.excel.annotation.ExcelIgnore;
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.alibaba.excel.util.StringUtils;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
@Data
@NoArgsConstructor
@Accessors(chain = true)
@ContentRowHeight(45)
@HeadRowHeight(50)
public class PilebodycheckMonthDto {
@ExcelIgnore
private String id;
@ExcelIgnore
private String cityid;
@ExcelIgnore
private String districtid;
@ExcelProperty(value = {"序號","序號"},index = 0)
@ColumnWidth(10)
private String orderNum;
@ExcelProperty(value = {"堆體名稱","堆體名稱"},index = 1)
@ColumnWidth(15)
private String name;
@ExcelProperty(value = {"具體位置","具體位置"},index = 3)
@ColumnWidth(30)
private String address;
@ExcelProperty(value = {"占地面積(平方)","占地面積(平方)"},index = 4)
@ColumnWidth(15)
private String areastr;
@ExcelProperty(value = {"堆體高度(米)","堆體高度(米)"},index = 5)
@ColumnWidth(10)
private String heightstr;
@ExcelProperty(value = {"建筑垃圾堆存量(萬方)","建筑垃圾堆存量(萬方)"},index = 6)
@ColumnWidth(15)
private String stocknum;
@ExcelIgnore
@Dict(dicCode = "governway")
private String governway;
@ExcelProperty(value = {"治理方式","治理方式"},index = 7)
@ColumnWidth(20)
private String governwayname;
@ExcelProperty(value = {"如需外運，計劃外運時間","如需外運，計劃外運時間"},index = 8)
@ColumnWidth(15)
private String outwardtransporttime;
@ExcelProperty(value = {"截止目前累計治理量(萬方)","截止目前累計治理量(萬方)"},index = 13)
@ColumnWidth(15)
private String governnum;
@ExcelProperty(value = {"治理主體","治理主體"},index = 14)
@ColumnWidth(15)
private String governbody;
@ExcelIgnore
@Dict(dicCode = "typestr")
private String typestr;
@ExcelProperty(value = {"堆體類型","堆體類型"},index = 2)
@ColumnWidth(15)
private String typestrname;
@ExcelIgnore
@Dict(dicCode = "statestr")
private String statestr;
@ExcelIgnore
private String districtname;
@ExcelProperty(value = {"監管單位","監管單位"},index = 15)
@ColumnWidth(15)
private String supervisedepartname;
@ExcelProperty(value = {"監管責任人","監管責任人"},index = 16)
@ColumnWidth(10)
private String supervisepeoname;
@ExcelProperty(value = {"職務","職務"},index = 17)
@ColumnWidth(10)
private String supervisepeoposition;
@ExcelProperty(value = {"聯系方式","聯系方式"},index = 18)
@ColumnWidth(20)
private String supervisepeophone;
@ExcelIgnore
private String residuenum;
@ExcelIgnore
private String governendtime;
@ExcelIgnore
private String governendyearmonth;
@ExcelProperty(value = {"本月治理量(萬方)","外運量"},index = 9)
@ColumnWidth(15)
private String outwardtransportnum;
@ExcelProperty(value = {"本月治理量(萬方)","整理地形綠化量"},index = 10)
@ColumnWidth(15)
private String afforestnum;
@ExcelProperty(value = {"本月治理量(萬方)","臨時覆蓋或綠化量"},index = 11)
@ColumnWidth(15)
private String temporarilynum ;
@ExcelProperty(value = {"本月治理量(萬方)","合計"},index = 12)
private String goverytotal;
@ExcelIgnore
private String qynum;
@ExcelIgnore
@Dict(dicCode = "sourcestr")
private String sourcestr;
@ExcelIgnore
private String createbyname;
}
controller
@postMapping(“pilebodystatisticsmonthexport”)
public WebApiResponse pilebodystatisticsmonthexport (HttpServletResponse response,String month) throws IOException {
List pilebodysList = pilebodycheckService.pilebodystatisticsmonth(sysDepartDto, month);
//設置序號
for (int i = 1;i <= pilebodysList.size();i++){
pilebodysList.get(i-1).setOrderNum(i+"");
}
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 + “.xls”);
//內容樣式策略
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(), PilebodycheckMonthDto.class)
//設置輸出excel版本,不設置默認為xlsx
.excelType(ExcelTypeEnum.XLS).head(PilebodycheckMonthDto.class)
//設置攔截器或自定義樣式
.registerWriteHandler(new MonthSheetWriteHandler())
.registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle))
.sheet("存量建筑垃圾堆體治理進度月報表")
//設置默認樣式及寫入頭信息開始的行數
.useDefaultStyle(true).relativeHeadRowIndex(3)
//這里的addsumColomn方法是個添加合計的方法,可刪除
.doWrite(pilebodycheckService.addSumColomn(pilebodysList));
return new WebApiResponse(200, "生成excel文件成功", null);
}
4. 攔截器
package com.jpxx.admin.pilebody.web.api;
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(1);
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(1, 1, 0, 17));
//設置填表日期,填報人,聯系方式
Row row3 = sheet.createRow(2);
row3.setHeight((short) 500);
row3.createCell(1).setCellValue("填表日期");
row3.createCell(11).setCellValue("填表人");
row3.createCell(15).setCellValue("聯系方式");
}
}
生成表格如下:
