java实现Excel模板导出
模板样式：
核心代码：
@GetMapping("/export-excel")
@Operation(summary = "导出巡检计划 Excel")
//@PreAuthorize("@ss.hasPermission('govinspect:patrol-scheme:export')")
@OperateLog(type = EXPORT)
public void exportPatrolSchemeExcel(@Valid PatrolSchemePageReqVO pageVO,
HttpServletResponse response) throws IOException {
List<PatrolSchemeDO> list = patrolSchemeService.getExportPatrolSchemeExcelList(pageVO);
List<ExportExcelProjectReportVO> exportExcelProjectReportVO = new ArrayList<>();
List<ExportExcelEnterpriseReportVO> exportExcelEnterpriseReportVO = new ArrayList<>();
String dirName = System.getProperty("user.dir") + File.separator + "file";
//导出项目
if (("1").equals(pageVO.getExportInspectionType())) {
exportExcelProjectReportVO = patrolSchemeService.getExportExcelProjectReportData(list);
//需要合并的列
Integer[] mergeColumn = {0, 1, 2, 3, 4, 5, 6};
String templateFileName = dirName + File.separator + "全区房屋建筑和市政工程质量安全巡查检查情况汇总表.xlsx";
String fileName = "全区房屋建筑和市政工程质量安全巡查检查情况汇总表" + System.currentTimeMillis() + ".xlsx";
File outFile = new File(dirName + File.separator + fileName);
ExcelUtils.fill(response, dirName, fileName, templateFileName, null, exportExcelProjectReportVO, Arrays.asList(mergeColumn), null);
}
//导出企业
if (("2").equals(pageVO.getExportInspectionType())) {
exportExcelEnterpriseReportVO = patrolSchemeService.getExportExcelEnterpriseReportData(list);
//需要合并的列
Integer[] mergeColumn = {0, 1, 2, 3};
String templateFileName = dirName + File.separator + "全区企业安全巡查检查情况汇总表.xlsx";
String fileName = "全区企业安全巡查检查情况汇总表" + System.currentTimeMillis() + ".xlsx";
ExcelUtils.fill(response, dirName, fileName, templateFileName, null, exportExcelEnterpriseReportVO, Arrays.asList(mergeColumn), null);
}
}
ExcelUtils.class
import cn.com.shouzhu.framework.excel.core.mergestragy.CustomColumnMergeStrategy;
import cn.com.shouzhu.framework.excel.core.mergestragy.CustomRowMergeStrategy;
import cn.com.shouzhu.framework.excel.core.threadrowandcolumn.ExcelRowAndColumCal;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.FileUtils;
import com.alibaba.excel.util.IoUtils;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
/**
* Excel 工具类
*/
public class ExcelUtils {
/**
* @param response 输出流
* @param path 生成文件所在路径
* @param filename 文件名称
* @param templateFileName 模板名称 全路径,包含模板名称
* @param data 单个数据
* @param listData 列表数据
* @param mergeColumn 如果有相同数据，需要合并的列
* @param mergeRow 如果有相同数据，需要合并的行
* @throws IOException
*/
public static <T> void fill(HttpServletResponse response, String path, String filename, String templateFileName,
Map<String, Object> data, List<T> listData, List<Integer> mergeColumn, List<Integer> mergeRow) throws IOException {
try (ExcelWriter excelWriter = EasyExcel.write(path + File.separator + filename).withTemplate(templateFileName).build()) {
ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.writerSheet();
if (null != mergeColumn && mergeColumn.size() > 0) {
CustomRowMergeStrategy strategy = new CustomRowMergeStrategy(mergeColumn);
excelWriterSheetBuilder.registerWriteHandler(strategy);
}
if (null != mergeRow && mergeRow.size() > 0) {
CustomColumnMergeStrategy strategy = new CustomColumnMergeStrategy(mergeRow);
excelWriterSheetBuilder.registerWriteHandler(strategy);
}
WriteSheet writeSheet = excelWriterSheetBuilder.build();
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
excelWriter.fill(listData, fillConfig, writeSheet);
if (null != data) {
excelWriter.fill(data, writeSheet);
}
}
ExcelRowAndColumCal.clearThreads();
File outputFile = new File(path + File.separator + filename);
if (outputFile.exists()) {
byte[] fileBytes = FileUtils.readFileToByteArray(outputFile);
// 输出 Excel
ServletOutputStream sos = response.getOutputStream();
sos.write(fileBytes);
sos.flush();
sos.close();
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
outputFile.delete();
// 设置 header 和 contentType。写在最后的原因是，避免报错时，响应 contentType 已经被修改了
}
}
}
CustomRowMergeStrategy.class
import cn.com.shouzhu.framework.excel.core.enums.CellMergeEnum;
import cn.com.shouzhu.framework.excel.core.util.MergeUtils;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.ArrayList;
import java.util.List;
public class CustomRowMergeStrategy extends AbstractMergeStrategy {
private List<Integer> mergeCols;
public CustomRowMergeStrategy() {
}
public CustomRowMergeStrategy(List<Integer> mergeCols) {
this.mergeCols = mergeCols;
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
if (CollectionUtils.isNotEmpty(this.mergeCols) && !this.mergeCols.contains(cell.getColumnIndex())) {
return;
}
MergeUtils.mergeCell(cell.getSheet(), cell, CellMergeEnum.ROW,mergeCols.size());
}
}
MergeUtils.class
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
import java.util.Objects;
public class MergeUtils {
public static void mergeCell(Sheet sheet, Cell cell, CellMergeEnum cellMergeEnum, int mergeSize) {
ExcelRowAndColumCal.incrementCell();
int rowIndex = cell.getRowIndex();
int colIndex = cell.getColumnIndex();
//获取列的上一行单元格
Row preRow = null;
Cell preCell = null;
if (CellMergeEnum.ROW.equals(cellMergeEnum)) {
if (ExcelRowAndColumCal.getPreRow() == 0 || rowIndex == ExcelRowAndColumCal.getPreRow()) {
ExcelRowAndColumCal.setPreRow(rowIndex);
return;
}
preRow = sheet.getRow(ExcelRowAndColumCal.getPreRow());
if (null == preRow) {
return;
}
preCell = preRow.getCell(colIndex);
}
if (CellMergeEnum.COLUMN.equals(cellMergeEnum)) {
if (colIndex == 0) {
return;
}
preRow = cell.getRow();
if (null == preRow) {
return;
}
preCell = preRow.getCell(colIndex - 1);
}
Object preCellValue = getCellValue(preCell), curCellValue = getCellValue(cell);
if (!Objects.equals(preCellValue, curCellValue)) {
//如果当前结果出现过不一致的情况，当前行不再合并
ExcelRowAndColumCal.setCurrRow(rowIndex);
} else {
if (!ExcelRowAndColumCal.getCurrRow().equals(rowIndex)) {
mergeRows(sheet, preCell, cell);
return;
}
}
double result = (double) ExcelRowAndColumCal.getCellCal() / mergeSize;
if (result >= (rowIndex - ExcelRowAndColumCal.getPreRow() + 1)) {
ExcelRowAndColumCal.setPreRow(rowIndex);
}
}
/**
* 行单元格合并
*
* @param sheet
* @param preCell
* @param curCell
*/
private static void mergeRows(Sheet sheet, Cell preCell, Cell curCell) {
CellStyle preCellStyle = preCell.getCellStyle();
List<CellRangeAddress> list = sheet.getMergedRegions();
if (CollectionUtils.isEmpty(list)) {
sheet.addMergedRegion(new CellRangeAddress(preCell.getRowIndex(), curCell.getRowIndex(), preCell.getColumnIndex(), curCell.getColumnIndex()));
preCell.setCellStyle(preCellStyle);
return;
}
for (int i = 0; i < list.size(); i++) {
CellRangeAddress cellRangeAddress = list.get(i);
if (cellRangeAddress.containsRow(preCell.getRowIndex()) && cellRangeAddress.containsColumn(preCell.getColumnIndex())) {
int firstRowIndex = cellRangeAddress.getFirstRow(), firstColIndex = cellRangeAddress.getFirstColumn();
sheet.removeMergedRegion(i);
sheet.addMergedRegion(new CellRangeAddress(firstRowIndex, curCell.getRowIndex(), firstColIndex, curCell.getColumnIndex()));
preCell.setCellStyle(preCellStyle);
return;
}
}
sheet.addMergedRegion(new CellRangeAddress(preCell.getRowIndex(), curCell.getRowIndex(), preCell.getColumnIndex(), curCell.getColumnIndex()));
preCell.setCellStyle(preCellStyle);
}
/**
* 获取单元格的值
*
* @param cell
* @return
*/
protected static Object getCellValue(Cell cell) {
if (Objects.isNull(cell)) {
return null;
}
CellType cellTypeEnum = cell.getCellTypeEnum();
switch (cellTypeEnum) {
case STRING:
return cell.getStringCellValue();
case BOOLEAN:
return cell.getBooleanCellValue();
case NUMERIC:
return cell.getNumericCellValue();
default:
return null;
}
}
}
CellMergeEnum.class
public enum CellMergeEnum {
ROW, COLUMN;
}
ExcelRowAndColumCal.class
import java.util.HashMap;
import java.util.Map;
public class ExcelRowAndColumCal {
public static ThreadLocal<Map<String, Integer>> ROW_AND_COL = new ThreadLocal<Map<String, Integer>>();
private static final String PRE_ROW = "preRow";
private static final String CELL_CAL = "cellCal";
private static final String CURR_ROW = "currRow";
public static void setCurrRow(Integer currRow) {
Map<String, Integer> map = ROW_AND_COL.get();
if (null == map) {
map = new HashMap<String, Integer>();
}
map.put(CURR_ROW, currRow);
ROW_AND_COL.set(map);
}
public static Integer getCurrRow() {
Map<String, Integer> map = ROW_AND_COL.get();
if (null == map || map.get(CURR_ROW) == null) {
return 0;
} else {
return map.get(CURR_ROW);
}
}
public static void setPreRow(Integer preRow) {
Map<String, Integer> map = ROW_AND_COL.get();
if (null == map) {
map = new HashMap<String, Integer>();
}
map.put(PRE_ROW, preRow);
ROW_AND_COL.set(map);
}
public static Integer getPreRow() {
Map<String, Integer> map = ROW_AND_COL.get();
if (null == map || map.get(PRE_ROW) == null) {
return 0;
} else {
return map.get(PRE_ROW);
}
}
public static void setCellCal(Integer cellCal) {
Map<String, Integer> map = ROW_AND_COL.get();
if (null == map) {
map = new HashMap<String, Integer>();
}
map.put(CELL_CAL, cellCal);
ROW_AND_COL.set(map);
}
public static Integer getCellCal() {
Map<String, Integer> map = ROW_AND_COL.get();
if (null == map || map.get(CELL_CAL) == null) {
return 0;
} else {
return map.get(CELL_CAL);
}
}
public static void incrementCell() {
Map<String, Integer> map = ROW_AND_COL.get();
if (null == map) {
map = new HashMap<String, Integer>();
map.put(CELL_CAL, 1);
} else {
if (null == map.get(CELL_CAL)) {
map.put(CELL_CAL, 1);
} else {
map.put(CELL_CAL, map.get(CELL_CAL) + 1);
}
}
ROW_AND_COL.set(map);
}
public static void clearThreads() {
ROW_AND_COL.remove();
}
}
导出样式：
1949
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
