excel导出(支持多工作表)

目录

• excel导出(支持多工作表)
  • maven依赖
  • 相关实例
    • 启动文件
    • 控制类
    • 实体类
    • 工具类

excel导出(支持多工作表)

maven依赖

<!--springboot-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <version>2.5.6</version>
</dependency>
<!--实体类-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.10</version>
</dependency>
<!-- excel操作-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>

相关实例

启动文件

package com.ntt.web;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.builder.SpringApplicationBuilder;
import org.springframework.boot.web.servlet.support.SpringBootServletInitializer;


@SpringBootApplication
public class CmsApplication extends SpringBootServletInitializer {

    public static void main(String[] args) {
        SpringApplication.run(CmsApplication.class, args);
    }

    @Override
    protected SpringApplicationBuilder configure(SpringApplicationBuilder application) {
        return application.sources(CmsApplication.class);
    }
}

server:
  port: 9999

控制类

package com.ntt.web.controller;


import com.ntt.web.pojo.Student;
import com.ntt.web.utils.ExcelUtils;
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletResponse;

import java.time.LocalDateTime;
import java.util.*;

@RestController
@RequestMapping("/api/v1")
public class StuController {

    /**
     * 导出
     * @param response
     * @throws Exception
     */
    @GetMapping(value = "/download")
    public void download(HttpServletResponse response) throws Exception{
        // 模拟查询匹配数据
        List<Student> list = new ArrayList<>();
        list.add(new Student().setName("小白").setAge(10).setCreateTime(LocalDateTime.now()));
        list.add(new Student().setName("小黑").setAge(11).setCreateTime(LocalDateTime.now()));
        // 导出
        LinkedHashMap<String, List<?>> resultMap = new LinkedHashMap<>();
        resultMap.put("结果", list);
        ExcelUtils.dataType = Arrays.asList(new Student());
        ExcelUtils.httpSaveByExcel(response, resultMap);
    }

}

实体类

package com.ntt.web.pojo;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonFormat;

import java.time.LocalDateTime;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

@Data
@Accessors(chain = true)
@EqualsAndHashCode(callSuper = false)
public class Student {
    private static final long serialVersionUID = 1L;

    /**
     * 姓名(原始)
     */
    @ExcelProperty(value = "姓名")
    private String name;

    /**
     * 年龄
     */
    @ExcelProperty(value = "年龄")
    private Integer age;

    /**
     * 创建时间
     */
    @ExcelIgnore
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime createTime;

}

工具类

package com.ntt.web.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtils {
    public static List<?> dataType = null;  // 导出的数据实体类型

    /**
     * Web导出结果(http-excel-多sheet)
     * @param response
     * @param resultMap
     * @throws Exception
     */
    public static void httpSaveByExcel(HttpServletResponse response, LinkedHashMap<String,List<?>> resultMap) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        try{
            // 获取文件结果
            SimpleDateFormat dft = new SimpleDateFormat("yyyyMMdd");
            String fileName = URLEncoder.encode(dft.format(new Date()), "UTF-8"); // 20220916
            // 响应信息构建
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            // 新建ExcelWriter
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            // 根据传递过来的map将结果导出到多个sheet
            Integer sheetNum = 0;  // sheet工作表序号
            if (resultMap != null && !resultMap.isEmpty()){
                for(Map.Entry<String, List<?>> entry : resultMap.entrySet()){
                    String rmKey = entry.getKey();  // sheet工作表名称
                    List<?> resultVal = entry.getValue(); // sheet工作表数据
                    if (rmKey != null){
                        resultVal = (resultVal != null && !resultVal.isEmpty()) ? resultVal : new ArrayList<>();
                        excelWriter.write(
                                resultVal, // 相关数据
                                EasyExcel.writerSheet(sheetNum, rmKey)
                                        .head(dataType.get(sheetNum).getClass())
                                        .registerWriteHandler(myHorizontalCellStyleStrategy())
                                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                                        .build()  // 工作表创建
                        );
                        sheetNum ++;
                    }
                }
            }
            // 关闭流
            excelWriter.finish();
        }catch (Exception e){
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            response.getWriter().println(e);
        }
    }

    /**
     * 本地导出结果(excel-单sheet|多sheet)
     * @param resultMap
     * @param resultPath
     */
    public static void localSaveByExcel(LinkedHashMap<String,List<?>> resultMap, String resultPath) throws IOException {
        // 指定输出的文件流
        File outFile = new File(resultPath);
        OutputStream outputStream = null;
        ExcelWriter excelWriter = null;
        try{
            outputStream = new FileOutputStream(resultPath);
            // 优先创建文件
            if (!outFile.exists()){
                File parentFile = outFile.getParentFile();
                if (!parentFile.exists()){
                    parentFile.mkdirs();
                }
                outFile.createNewFile();
            }
            // 新建ExcelWriter
            excelWriter = EasyExcel.write(outputStream).build();
            // 根据传递过来的map将结果导出到多个sheet
            Integer sheetNum = 0;  // sheet工作表序号
            if (resultMap != null && !resultMap.isEmpty()){
                for(Map.Entry<String, List<?>> entry : resultMap.entrySet()){
                    String rmKey = entry.getKey();  // sheet工作表名称
                    List<?> resultVal = entry.getValue(); // sheet工作表数据
                    if (rmKey != null){
                        resultVal = (resultVal != null && !resultVal.isEmpty()) ? resultVal : new ArrayList<>();
                        excelWriter.write(
                                resultVal, // 相关数据
                                EasyExcel.writerSheet(sheetNum, rmKey)
                                        .head(dataType.get(sheetNum).getClass())
                                        .registerWriteHandler(myHorizontalCellStyleStrategy())
                                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                                        .build()  // 工作表创建
                        );
                        sheetNum ++;
                    }
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
            if (outputStream != null){
                outputStream.close();
            }
        }
    }

    /**
     *  设置表头 和内容样式
     * @return
     */
    public static HorizontalCellStyleStrategy myHorizontalCellStyleStrategy(){
        //1 表头样式策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //表头前景设置浅绿色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("等线");
        headWriteFont.setFontHeightInPoints((short)11);
        headWriteCellStyle.setWriteFont(headWriteFont);

        //内容样式  多个样式则隔行换色
        List<WriteCellStyle>   listCntWritCellSty =  new ArrayList<>();
        //2 内容样式策略  样式一
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        //内容字体大小
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short)11);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //背景设置白色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        //设置自动换行
        contentWriteCellStyle.setWrapped(false);
        //设置垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        //设置背景黄色
        //        contentWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        //设置水平靠左
        //contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        //设置边框样式
        setBorderStyle(contentWriteCellStyle);
        //内容风格可以定义多个。
        listCntWritCellSty.add(contentWriteCellStyle);

        // 水平单元格风格综合策略(表头 + 内容));
        return  new HorizontalCellStyleStrategy(headWriteCellStyle, listCntWritCellSty);
    }

    /**
     * 设置边框样式
     * @param contentWriteCellStyle
     */
    private static void setBorderStyle(WriteCellStyle contentWriteCellStyle){
        //设置边框样式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
    }
}