easyexcel工具+表格标题自定义+表格合并

easyexcel

快速、简洁、解决大文件内存溢出的java处理Excel工具

项目地址：https://gitcode.com/gh_mirrors/ea/easyexcel

免费下载资源

W-同学

4729人浏览 · 2023-03-02 23:11:47

W-同学 · 2023-03-02 23:11:47 发布

excel导出工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

/**
 * Excel相关处理
 * @author dmw
 */
public class ExcelUtil {
	/**
	 *  单个sheet页导出
	 */
	public static void writeExcel(HttpServletResponse response, List<?> data, String fileName, String sheetName, Class<?> clazz) throws Exception {
		 EasyExcel.write(getOutputStream(fileName, response), clazz)
				 .excelType(ExcelTypeEnum.XLSX).sheet(sheetName)
				 .relativeHeadRowIndex(1)
				 .registerWriteHandler(new CustomTitleWriteHandler(clazz,fileName))
				 .registerWriteHandler(getCellStyle())
				 .doWrite(data);
	}

	/**
	 *  合并导出
	 */
	public static void writeExcelCustomMerge(HttpServletResponse response, List<?> data, String fileName, String sheetName, Class<?> clazz) throws Exception {
		EasyExcel.write(getOutputStream(fileName, response), clazz)
				.excelType(ExcelTypeEnum.XLSX)
				.sheet(sheetName)
				//表格标题占位
				.relativeHeadRowIndex(1)
				.registerWriteHandler(getCellStyle())
				.head(clazz)
				//自定义表格标题处理
				.registerWriteHandler(new CustomTitleWriteHandler(clazz,fileName))
				//自定义表格合并处理
				.registerWriteHandler(new CustomMergeStrategy(clazz))
				.doWrite(data);
	}

	/**
	 *   多个sheet页导出
	 */
	public static void writeExcels(HttpServletResponse response,List<List<?>> data, String fileName, Class<?> clazz) throws Exception{
		ExcelWriter excelWriter = null;
		try{
			excelWriter = EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(getCellStyle()).build();
			for(int i=0;i<data.size();i++){
				WriteSheet writeSheet = EasyExcel.writerSheet(i, "sheet"+i).build();
				excelWriter.write(data.get(i), writeSheet);
			}
		} finally {
			if(excelWriter != null){
				excelWriter.finish();
			}
		}
	}

	/**
	 * 设置请求
	 */
	private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
		fileName = URLEncoder.encode(fileName +"."+ ExcelTypeEnum.XLSX, "UTF-8");
		// 告诉浏览器用什么软件可以打开此文件
		response.setHeader("content-Type", "application/vnd.ms-excel");
		response.setCharacterEncoding("utf8");
		response.setHeader("content-disposition",  "attachment;filename="+fileName+";"+"filename*=utf-8''"+fileName);
		return response.getOutputStream();
	}
	/**
	 * 设置样式
	 */
	private static HorizontalCellStyleStrategy getCellStyle(){
		//表头样式
		WriteCellStyle headWriteCellStyle = new WriteCellStyle();
		//设置表头居中对齐
		headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
		//内容样式
		WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
		//设置内容垂直居中对齐
		contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		//设置内容水平居中对齐
		contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
		return new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle);
	}
}

自定义表格标题处理

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;

import java.lang.reflect.Field;

/**
 * @description: 表格标题处理
 * @author: dmw
 * @date: 2023/3/2 17:00
 **/
public class CustomTitleWriteHandler implements SheetWriteHandler {

    /**
     * 标题
     */
    private final String fileName;

    /**
     * DTO数据类型
     */
    private final Class<?> elementType;

    public CustomTitleWriteHandler(Class<?> elementType,String fileName) {
        this.fileName = fileName;
        this.elementType = elementType;
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 获取clazz所有的属性
        Field[] fields = this.elementType.getDeclaredFields();
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        Row row1 = sheet.createRow(0);
        row1.setHeight((short) 800);
        Cell cell = row1.createCell(0);
        //设置标题
        cell.setCellValue(fileName);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeight((short) 400);
        font.setFontName("宋体");
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, fields.length-1));
    }
}

3 . 自定义表格合并注解

import java.lang.annotation.*;

/**
 * 自定义注解，用于判断是否需要合并以及合并的主键
 * @author dmw
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface CustomMerge {

    /**
     * 是否是主键,即该字段相同的行合并
     */
    boolean isPk() default false;
    
    /**
     * 是否需要合并单元格
     */
    boolean needMerge() default false;
}

4 .1.统计行注解

import java.lang.annotation.*;

/**
 * 自定义注解，用于判断是否需要合计
 * @author dmw
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface SummaryRow {

    /**
     * 是否需要合计
     */
    boolean needSummary() default false;

    /**
     * 展示为合计字段
     */
    String summaryText() default "合计：";
}

4.2 表格合并策略

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.*;

import static cn.afterturn.easypoi.util.PoiCellUtil.getCellValue;

/**
 * 自定义单元格合并策略
 * @author dmw
 */
@Slf4j
public class CustomMergeStrategy implements RowWriteHandler {
    /**
     * 主键下标
     */
    private Integer pkIndex;

    /**
     * 需要合并的列的下标集合
     */
    private final List<Integer> needMergeColumnIndex = new ArrayList<>();

    /**
     * 需要合计列的下标集合
     */
    private final Map<Integer,BigDecimal> needSummaryColumnMap = new HashMap<>(10);

    /**
     * 合计字段列的下标集合
     */
    private final Map<Integer,String> summaryTextColumnMap = new HashMap<>(10);

    /**
     * 需要合计列的下标集合
     */
    private List<?> dataList =  new ArrayList<>(10);

    /**
     * DTO数据类型
     */
    private final Class<?> elementType;

    /**
     * 数据长度
     */
    private final int rowLength;

    public CustomMergeStrategy(Class<?> elementType,int rowLength,List<?> dataList) {
        this.elementType = elementType;
        this.rowLength = rowLength;
        this.dataList = dataList;
    }

    @Override
    public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        // 如果是标题,则直接返回
        if (isHead) {
            return;
        }
        // 获取当前sheet
        Sheet sheet = writeSheetHolder.getSheet();
        if (null == pkIndex) {
            this.lazyInit(writeSheetHolder);
        }
        if (needSummaryColumnMap.isEmpty()){
            try {
                this.initSummaryRow();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        // 不能和标题合并，只能数据行之间合并
        int rowNum = row.getRowNum();
        if (rowNum <= 1) {
            return;
        }

        // 获取上一行数据
        Row lastRow = sheet.getRow(rowNum - 1);
        // 将本行和上一行是同一类型的数据(通过主键字段进行判断)，则需要合并
        if (!CollectionUtils.isEmpty(needMergeColumnIndex)){
            if (lastRow.getCell(pkIndex).getStringCellValue().equalsIgnoreCase(row.getCell(pkIndex).getStringCellValue())) {
                boolean isMerged = false;
                for (Integer needMerIndex : needMergeColumnIndex) {
                    List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
                    for (int i = 0; i < mergeRegions.size(); i++) {
                        CellRangeAddress cellRangeAddress = mergeRegions.get(i);
                        // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                        if (cellRangeAddress.isInRange(rowNum - 1, needMerIndex)) {
                            sheet.removeMergedRegion(i);
                            cellRangeAddress.setLastRow(rowNum);
                            sheet.addMergedRegionUnsafe(cellRangeAddress);
                            isMerged = true;
                        }
                    }
                    // 若上一个单元格未被合并，则新增合并单元
                    if (!isMerged) {
                        CellRangeAddress cellRangeAddress = new CellRangeAddress(rowNum - 1, rowNum,    needMerIndex, needMerIndex);
                        sheet.addMergedRegionUnsafe(cellRangeAddress);
                    }
                }
            }
        }
        if (rowNum >= rowLength+1){
            //最后一行
            Row summaryRow = sheet.createRow(rowLength + 2);
            for (Integer index : summaryTextColumnMap.keySet()) {
                Cell cell = summaryRow.createCell(index);
                String summaryText = summaryTextColumnMap.get(index);
                cell.setCellValue(summaryText);
                Workbook wb = writeSheetHolder.getSheet().getWorkbook();
                this.setCellStyle(cell,wb,HorizontalAlignment.RIGHT);
            }
            //设置合计行
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            for (Integer index : needSummaryColumnMap.keySet()) {
                Cell cell = summaryRow.createCell(index);
                BigDecimal total = needSummaryColumnMap.get(index);
                BigDecimal fixVal = BigDecimal.ZERO;
                for (CellRangeAddress mergeRegion : mergeRegions) {
                    if (needMergeColumnIndex.contains(index) &&  mergeRegion.containsColumn(index)){
                        //即需要合并也需要统计的单独处理，总计-多余跨度*行值
                        String cellVal = getCellVal(sheet, mergeRegion);
                        int span = mergeRegion.getLastRow()-mergeRegion.getFirstRow();//保留一个值
                        if (StrUtil.isNotEmpty(cellVal)){
                            BigDecimal cellDecimal = new BigDecimal(cellVal);
                            fixVal = fixVal.add(cellDecimal.multiply(new BigDecimal(String.valueOf(span))));
                        }
                    }
                }
                cell.setCellValue( total.subtract(fixVal).toString());
                Workbook wb = writeSheetHolder.getSheet().getWorkbook();
                this.setCellStyle(cell,wb,HorizontalAlignment.CENTER);
            }
        }
    }

    private String getCellVal( Sheet sheet ,CellRangeAddress ca ){
        int lastColumn = ca.getLastColumn();
        int lastRow = ca.getLastRow();
        Row fRow = sheet.getRow(lastRow);
        if (Objects.isNull(fRow)){
            return null;
        }
        Cell fCell = fRow.getCell(lastColumn);
        return getCellValue(fCell);
    }

    private void setCellStyle( Cell cell,Workbook wb,HorizontalAlignment horizontalAlignment){
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setAlignment(horizontalAlignment);// 水平居中
        // 生成12号字体
        Font font = wb.createFont();
        font.setColor(IndexedColors.RED.getIndex());
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    }
    /**
     * 初始化主键下标和需要合并字段的下标
     */
    private void lazyInit(WriteSheetHolder writeSheetHolder) {

        // 获取当前sheet
        Sheet sheet = writeSheetHolder.getSheet();

        // 获取标题行
        Row titleRow = sheet.getRow(1);

        // 获取DTO所有的属性
        Field[] fields = this.elementType.getDeclaredFields();

        // 遍历所有的字段，因为是基于DTO的字段来构建excel，所以字段数 >= excel的列数
        for (Field theField : fields) {
            // 获取@ExcelProperty注解，用于获取该字段对应在excel中的列的下标
            ExcelProperty easyExcelAnn = theField.getAnnotation(ExcelProperty.class);
            // 为空,则表示该字段不需要导入到excel,直接处理下一个字段
            if (null == easyExcelAnn) {
                continue;
            }
            // 获取自定义的注解，用于合并单元格
            CustomMerge customMerge = theField.getAnnotation(CustomMerge.class);

            // 没有@CustomMerge注解的默认不合并
            if (null == customMerge) {
                continue;
            }
            for (int index = 0; index < fields.length; index++) {
                Cell theCell = titleRow.getCell(index);
                // 当配置为不需要导出时，返回的为null，这里作一下判断，防止NPE
                if (null == theCell) {
                    continue;
                }
                // 将字段和excel的表头匹配上
                if (easyExcelAnn.value()[0].equalsIgnoreCase(theCell.getStringCellValue())) {
                    if (customMerge.isPk()) {
                        pkIndex = index;
                    }
                    if (customMerge.needMerge()) {
                        needMergeColumnIndex.add(index);
                    }
                }
            }
        }
    }

    /**
     * 初始化合计行
     */
    private void initSummaryRow() throws Exception {
        List<?> dataList = this.dataList;
        for (int i = 0; i < dataList.size(); i++) {
            Class<?> clazz = dataList.get(i).getClass();
            Field[] fields = clazz.getDeclaredFields();
            for (int index = 0; index < fields.length; index++) {
                // 获取@ExcelProperty注解，用于获取该字段对应在excel中的列的下标
                ExcelProperty easyExcelAnn = fields[index].getAnnotation(ExcelProperty.class);
                // 为空,则表示该字段不需要导入到excel,直接处理下一个字段
                if (null == easyExcelAnn) {
                    continue;
                }

                // 获取自定义的注解，用于合计数据
                SummaryRow summaryRow = fields[index].getAnnotation(SummaryRow.class);

                if (Objects.nonNull(summaryRow)) {
                    fields[index].setAccessible(true);
                    String name = fields[index].getName();
                    name = name.substring(0, 1).toUpperCase() + name.substring(1);
                    String type = fields[index].getGenericType().toString();    //获取属性的类型

                    if ("class java.lang.String".equals(type)) {   //如果type是类类型，则前面包含"class "，后面跟类名
                        try {
                            Method mGet = clazz.getMethod("get" + name);
                            String value = (String) mGet.invoke(dataList.get(i));    //调用getter方法获取属性值
                            if (summaryRow.needSummary()) {
                                this.setStringMap(index,value);
                            }
                            if (StrUtil.isNotBlank(summaryRow.summaryText())) {
                                summaryTextColumnMap.put(index,summaryRow.summaryText());
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }else if ("class java.math.BigDecimal".equals(type)) {   //如果type是类类型，则前面包含"class "，后面跟类名
                        try {
                            Method mGet = clazz.getMethod("get" + name);
                            Object value = mGet.invoke(dataList.get(i));    //调用getter方法获取属性值
                            if (summaryRow.needSummary()) {
                                this.setDecimalMap(index,(BigDecimal) value);
                            }
                            if (StrUtil.isNotBlank(summaryRow.summaryText())) {
                                summaryTextColumnMap.put(index,summaryRow.summaryText());
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }else if ("class java.lang.Integer".equals(type)) {   //如果type是类类型，则前面包含"class "，后面跟类名
                        try {
                            Method mGet = clazz.getMethod("get" + name);
                            Integer value = (Integer)mGet.invoke(dataList.get(i));    //调用getter方法获取属性值
                            if (summaryRow.needSummary()) {
                                this.setNumberMap(index,value);
                            }
                            if (StrUtil.isNotBlank(summaryRow.summaryText())) {
                                summaryTextColumnMap.put(index,summaryRow.summaryText());
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        }
    }
    private void setStringMap(Integer index, String value){
        BigDecimal cell = new BigDecimal(value);
        BigDecimal val = needSummaryColumnMap.get(index);
        if (Objects.isNull(val)){
            needSummaryColumnMap.put(index, cell);
        }else {
            needSummaryColumnMap.put(index,val.add(cell));
        }
    }

    private void setDecimalMap( Integer index, BigDecimal value){
        BigDecimal val = needSummaryColumnMap.get(index);
        if (Objects.isNull(val)){
            needSummaryColumnMap.put(index, value);
        }else {
            needSummaryColumnMap.put(index,val.add(value));
        }
    }

    private void setNumberMap( Integer index, Integer value){
        BigDecimal cell = new BigDecimal(value.toString());
        BigDecimal val = needSummaryColumnMap.get(index);
        if (Objects.isNull(val)){
            needSummaryColumnMap.put(index, cell);
        }else {
            needSummaryColumnMap.put(index,val.add(cell));

        }
    }
}

表格实体vo

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.springcloud.aj.mall.common.util.RebateStatusConverter;
import com.springcloud.aj.mall.common.util.easyexcel.CustomMerge;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;

import java.math.BigDecimal;

@Data
public class FinanceRebateExportVo {

	@SummaryRow//展示合计字段
	@ColumnWidth(20)
	@ExcelProperty("订单编号")
	@CustomMerge(needMerge = true,isPk = true)
	private String orderCode;
	
	//......其他字段
	
	@ColumnWidth(15)
	@CustomMerge(needMerge = true)
	@SummaryRow(needSummary = true)//添加合计行
	@ExcelProperty(value = "实际返利金额" )
	private BigDecimal realRebateAmount;

实际使用

	@Override
	public void financeRebateExport(HttpServletResponse response,FinanceRebateForm form) {
		String sheetName = "返利报表";
		if (Objects.nonNull(form.getBeginTime()) && Objects.nonNull(form.getEndTime())){
			String begin = form.getBeginTime().substring(0, 10);
			String end = form.getEndTime().substring(0, 10);
			sheetName=sheetName+"("+begin+"至"+end+")";
		}
		List<FinanceRebateVo> rebateList = ajOrderMapper.getFinanceRebateList(form);
		// 转换成execl 对象输出
		List<FinanceRebateExportVo> list = rebateList.stream().map(record -> {
			FinanceRebateExportVo excelVO = new FinanceRebateExportVo();
			BeanUtils.copyProperties(record, excelVO);
			return excelVO;
		}).collect(Collectors.toList());
		try {
			ExcelUtil.writeExcelCustomMerge(response,list, sheetName, sheetName, FinanceRebateExportVo.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

导出效果如下

在这里插入图片描述

end…
1.已参考并修复导出合并问题 easyExcel导入导出
2.添加自定义表格标题
3.添加统计行数
4.既需要统计行的又有合并单元格的单独处理
感谢

GitHub 加速计划 / ea / easyexcel

31.64 K

7.47 K

下载

快速、简洁、解决大文件内存溢出的java处理Excel工具

最近提交(Master分支：2 个月前 )

c42183df Bugfix 2 个月前

efa7dff6 * 重新加回 `commons-io` 2 个月前

GitCode 开源社区

旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。

更多推荐

[转载]在Windows环境下安装GNU Radio

转自：在Windows环境下安装GNURadio_恐弱智_新浪博客GNU Radio是用Python开发的，大部分开源的工程能够在Linux环境下运行良好，而Windows下却运行的很勉强，而且安装配置都很复杂。GNU Radio算是个例外了，不光提供了Windows的二进制安装，还有比较详细的说明。我是Python小白，所以折腾了好久才弄好，特意记录下来，免得以后再装还折腾。GNU Radio的

GitCode 开源社区

centOS 8 使用dnf安装Docker

DNF是什么？CentOS 8使用YUM软件包管理器版本v4.0.4。现在，该版本使用DNF(已删除YUM)。DNF是软件包管理器。它会在Linux发行版上安装，执行更新并删除软件包。使用DNF安装Docker跳过具有损坏依赖性的程序包一个有效的解决方案是使您的CentOS 8系统使用以下--nobest命令安装最符合条件的版本：sudo dnf install docker...

GitCode 开源社区

定时同步数据库表(mysql+linux+crontab)

sync.sh里面的参数需要改变，ip/username/password/database/tablesync.sh#!/bin/sh# Please change the IP and password of the data source db.# Then change the table name.filename=/home/nington/db/$(date +%Y-%m