springboot +easyexcel 下载工具类

g3230863

已于 2024-09-02 13:51:34 修改

阅读量479

点赞数 1

分类专栏： java 文章标签： excel java easyexcel

于 2024-08-31 11:16:33 首次发布

本文链接：https://blog.csdn.net/g3230863/article/details/141748881

版权

java 专栏收录该内容

65 篇文章 0 订阅

订阅专栏

使用springboot +easyexcel通过web来下载Excel文件

pom文件

    <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.4</version>
        </dependency>

Controller 类



@RestController
@Slf4j
public class TestController {

   @PostMapping(value = "/down")
    public void down(HttpServletResponse response) {
        log.info("hello java");
        TestDownloadData testDownloadData = new TestDownloadData("张三" ,"男",18 );
        TestDownloadData testDownloadData2 = new TestDownloadData("李四" ,"女",20 );
        TestDownloadData testDownloadData3 = new TestDownloadData("王五" ,"男",19 );
        List<TestDownloadData> list = Arrays.asList(testDownloadData, testDownloadData2, testDownloadData3);
        String fileName =String.format("测试_%s",getCurrentDateStr("yyyyMMddHHmmss"));
        ExcelUtil.download(response, fileName, list, TestDownloadData.class,"测试sheet");
    }

    public static String getCurrentDateStr( String pattern) {
        LocalDateTime localDateTime = LocalDateTime.now();
        DateTimeFormatter df = DateTimeFormatter.ofPattern(pattern);
        return df.format(localDateTime);
    }

}

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class TestDownloadData {
    @ExcelProperty(value = "姓名", index = 0)
    private String name;
    @ExcelProperty(value = "性别", index = 1)
    private String sex;
    @ExcelProperty(value = "年龄", index = 2)
    private Integer age;

}

工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.extern.slf4j.Slf4j;

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

/**
 * Excel工具类
 */
@Slf4j
public class ExcelUtil {


    public static  <T> void download(HttpServletResponse response, String fileName, List<T> list,Class<T> clazz,String sheetName){
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(StyleUtils.getHeadStyle(), StyleUtils.getContentStyle());
            EasyExcel.write(response.getOutputStream(), clazz)
                    .sheet(sheetName)
                    .registerWriteHandler(new ExcelCellWidthStyleStrategy())
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .doWrite(list);
        } catch (Exception e) {
            log.info("下载异常,文件:"+fileName,e);
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = MapUtils.newHashMap();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            try {
                response.getWriter().println(JSON.toJSONString(map));
            } catch (IOException ex) {
                log.error("response写入失败,文件:"+fileName,e);
            }
        }
    }
}

配置类

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

public class StyleUtils {
 
 
    private static final String[] _formats = new String[]{
            "General",
            "0",
            "0.00",
            "#,##0",
            "#,##0.00",
            "\"$\"#,##0_);(\"$\"#,##0)",
            "\"$\"#,##0_);[Red](\"$\"#,##0)",
            "\"$\"#,##0.00_);(\"$\"#,##0.00)",
            "\"$\"#,##0.00_);[Red](\"$\"#,##0.00)",
            "0%", "0.00%", "0.00E+00",
            "# ?/?", "# ??/??",
            "m/d/yy", "d-mmm-yy",
            "d-mmm", "mmm-yy",
            "h:mm AM/PM",
            "h:mm:ss AM/PM",
            "h:mm", "h:mm:ss",
            "m/d/yy h:mm",
            "reserved-0x17",
            "reserved-0x18",
            "reserved-0x19",
            "reserved-0x1A",
            "reserved-0x1B",
            "reserved-0x1C",
            "reserved-0x1D",
            "reserved-0x1E",
            "reserved-0x1F",
            "reserved-0x20",
            "reserved-0x21",
            "reserved-0x22",
            "reserved-0x23",
            "reserved-0x24",
            "#,##0_);(#,##0)",
            "#,##0_);[Red](#,##0)",
            "#,##0.00_);(#,##0.00)",
            "#,##0.00_);[Red](#,##0.00)",
            "_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)",
            "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)",
            "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)",
            "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)",
            "mm:ss",
            "[h]:mm:ss",
            "mm:ss.0",
            "##0.0E+0",
            "@" // 文本格式
    };
 
 
    /**
     * 标题样式
     * @return
     */
    public static WriteCellStyle getHeadStyle(){
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景颜色
//        headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE1.getIndex());
//        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
 
        // 字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("宋体");//设置字体名字
        headWriteFont.setFontHeightInPoints((short)10);//设置字体大小
        headWriteFont.setBold(true);//字体加粗
        headWriteCellStyle.setWriteFont(headWriteFont); //在样式用应用设置的字体;
 
        // 样式
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
        headWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;
        headWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
        headWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
        headWriteCellStyle.setTopBorderColor((short) 0); //设置顶边框颜色;
 
        headWriteCellStyle.setWrapped(true);  //设置自动换行;
 
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐;
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  //设置垂直对齐的样式为居中对齐;
        headWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适
 
        return headWriteCellStyle;
    }
 
 
    /**
     * 内容样式
     * @return
     */
    public static WriteCellStyle getContentStyle(){
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
 
        // 背景绿色
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
 
        // 设置字体
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 9);//设置字体大小
        contentWriteFont.setFontName("宋体"); //设置字体名字
        contentWriteCellStyle.setWriteFont(contentWriteFont);//在样式用应用设置的字体;
 
        //设置样式;
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
        contentWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;
        contentWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
        contentWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
        contentWriteCellStyle.setTopBorderColor((short) 0); ///设置顶边框颜色;
 
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 水平居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        contentWriteCellStyle.setWrapped(true); //设置自动换行;
         contentWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适
 
        return contentWriteCellStyle;
    }
 
}

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelCellWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
    // 可以根据这里的最大宽度，按自己需要进行调整,搭配单元格样式实现类中的，自动换行，效果更好
    private static final int MAX_COLUMN_WIDTH = 50;
    private  Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);
 
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = (Map)CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap(16);
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }
 
            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > MAX_COLUMN_WIDTH) {
                    columnWidth = MAX_COLUMN_WIDTH;
                }
 
                Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    ((Map)maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }
 
            }
        }
    }
 
    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = (CellData)cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch(type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
 
}

合并单元格

为了下面的效果
在这里插入图片描述
增加一个registerWriteHandler

在这里插入图片描述

     List<HighwayData> highwayData = Arrays.asList(
                new HighwayData("鲁衡管理处", "都香高速", 320000, 330000, 315000, 305000, 300000, 280000, 1850000),
                new HighwayData("鲁衡管理处", "大永高速", 280000, 315000, 305000, 330000, 300000, 300000, 1860000),
                new HighwayData("鲁衡管理处", "格巧高速", 320000, 315000, 305000, 300000, 330000, 280000, 1850000),
                new HighwayData("鲁衡管理处", "清水高速", 320000, 330000, 315000, 300000, 300000, 280000, 1850000),
                new HighwayData("鲁衡管理处", "串新高速", 280000, 330000, 315000, 300000, 330000, 280000, 1860000),
                new HighwayData("鲁衡管理处", "邵阳环西高速", 280000, 315000, 305000, 330000, 300000, 300000, 1860000),
                new HighwayData("镇维管理处", "昭泸高速", 320000, 315000, 305000, 300000, 330000, 280000, 1850000),
                new HighwayData("镇维管理处", "镇纳高速", 320000, 330000, 315000, 300000, 300000, 280000, 1850000)
        );

自定义合并的逻辑，如果是固定行数的合并，可以参考官方demo类LoopMergeStrategy

import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFRow;

public class CellMergeStrategy implements RowWriteHandler {

    private final int mergeColumnIndex;

    public CellMergeStrategy(int mergeColumnIndex) {
        this.mergeColumnIndex = mergeColumnIndex;
    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                Integer relativeRowIndex, Boolean isHead) {
        if (isHead) {
            return;
        }
        Sheet sheet = writeSheetHolder.getSheet();
        //怎么把row转换成SXSSFRow，获取SXSSFRow中的rowIndex
        SXSSFRow sr = (SXSSFRow) row;

        //行号
        int rowIndex = sr.getRowNum();

        String currentData = sr.getCell(mergeColumnIndex).getStringCellValue();
        String previousData = sheet.getRow(rowIndex - 1).getCell(mergeColumnIndex).getStringCellValue();

        // 如果当前单元格数据与上一个单元格数据相同，则合并
        if (currentData.equals(previousData)) {
            CellRangeAddress cellRangeAddress;
            cellRangeAddress = new CellRangeAddress(
                    rowIndex - 1, rowIndex ,
                    mergeColumnIndex, mergeColumnIndex
            );
            sheet.addMergedRegionUnsafe(cellRangeAddress);
        }

    }
}