SpringBoot之poi Excel导出

需求：把申请流程表生成Excel导出。然后我就试了试HuTool里的Excel工具类，可能是我技术不行，导出的Excel有问题，打不开，显示格式不正确。然后我又查资料，看poi的导出，网上查了好多，都不能复制粘贴直接用，烦呐，既然没找到别人造好的轮子，那我只能苦哈哈的造轮子了，参考了好几篇文章，才造出来的，具体的文章忘了。废话不多说，直接上代码。

导入jar包

        <!-- poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>

注解接口

配置导出的实体类时用到，给要导出的属性添加@ExcelHeader(value=””)就可以

import java.lang.annotation.*;

/**
 * Excel表头
 * @author 刘银龙
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelHeader {

    /**
     * 表头
     */
    String value() default "";

    /**
     * 列索引
     *
     */
    int columnIndex() default 0;
}

生成Excel工具类

package com.wp.app.cdc.utils;

import com.wp.app.base.annotation.ExcelHeader;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.format.annotation.DateTimeFormat;

import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Author: 刘银龙
 * Date: 2021-09-28
 * Time: 13:35
 * Description: 生成Excel
 */
public class ExcelWriter {

    /**
     * 生成Excel并写入数据信息
     * @param titleStr 标题
     * @param dataList 数据集合
     * @param clz 数据类
     * @return 写入数据后的工作簿对象
     */
    public static <T> Workbook exportData(String titleStr , List<T> dataList, Class<T> clz){
        // 生成xlsx的Excel
        Workbook workbook = new SXSSFWorkbook();

        // 如需生成xls的Excel，请使用下面的工作簿对象，注意后续输出时文件后缀名也需更改为xls
        Field[] fields = clz.getDeclaredFields();
        List<String> headers = new LinkedList<>();
        List<String> variables = new LinkedList<>();

        Sheet sheet = workbook.createSheet();
        // 设置列头宽度
        for (int i=0; i<fields.length; i++) {
            sheet.setColumnWidth(i, 4000);
        }
        // 设置默认行高
        sheet.setDefaultRowHeight((short) 400);

        // 写入标头
        Row head = sheet.createRow(1);

        // 表头处理
        for (int h = 0; h < fields.length; h++) {
            Field field = fields[h];
            if (field.isAnnotationPresent(ExcelHeader.class)) {
                // 表头
                ExcelHeader annotation = field.getAnnotation(ExcelHeader.class);
                headers.add(annotation.value());
                CellStyle style = workbook.createCellStyle();
                Cell cell = head.createCell(h);
                //设置边框样式并且居中
                cell.setCellValue(annotation.value());
                setBorderStyle(style, cell);
                //设置标头字体加粗
                Font font = workbook.createFont();
                font.setBold(true);
                style.setFont(font);

                // 字段
                variables.add(field.getName());
            }
        }

        //写入标题
        Row title = sheet.createRow(0);
        //合并列  参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, (short) 0, 0, (short) headers.size()-1);

        // 合并单元格边框样式
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);

        sheet.addMergedRegion(cellRangeAddress);
        Cell cell = title.createCell(0);
        cell.setCellValue(titleStr);
        CellStyle style = workbook.createCellStyle();
        //设置标题居中
        style.setAlignment(HorizontalAlignment.CENTER);
        cell.setCellStyle(style);
        //设置字体加粗
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);

        //构建每行的数据内容
        int rowNum = 2;

        for (Iterator<T> it = dataList.iterator(); it.hasNext(); ) {
            T data = it.next();
            if (data == null) {
                continue;
            }
            //输出行数据
            Row row = sheet.createRow(rowNum++);

            convertDataToRow(data, row,variables,workbook,sheet);
        }
        return workbook;
    }

    /**
     * 将数据转换成行
     * @param data 源数据
     * @param row 行对象
     * @return
     */
    private static <T> void convertDataToRow(T data, Row row,List<String> variables,Workbook workbook,Sheet sheet){

        Class<?> aClass = data.getClass();
        for (int j = 0; j < variables.size(); j++) {

            Field declaredField = null;
            try {
                declaredField = aClass.getDeclaredField(variables.get(j));
            } catch (NoSuchFieldException e) {
                e.printStackTrace();
            }
            declaredField.setAccessible(true);
            String key = declaredField.getName();
            Object value = null;
            try {
                //时间类型 特殊处理
                if(declaredField.getType().equals(Date.class)){
                    SimpleDateFormat sdf = null;
                    //根据DateTimeFormat注解设置 时间格式  如果没有该注解   默认格式为 yyyy-MM-dd HH:mm:ss
                    if(declaredField.isAnnotationPresent(DateTimeFormat.class)){
                        DateTimeFormat annotation = declaredField.getAnnotation(DateTimeFormat.class);
                        sdf = new SimpleDateFormat(annotation.pattern());
                    }else{
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    if(declaredField.get(data) != null){
                        value = sdf.format(declaredField.get(data));
                    }
                }else{
                    value = declaredField.get(data);
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }

            Cell cell = row.createCell(j);
            CellStyle style = workbook.createCellStyle();

            byte[] imgByte = null;
            int addPicture;

            //设置边框样式   并且居中
            if(value != null){
                //判断值是否是https:的图片路径  填充图片
                if (value.toString().startsWith("https:")) {
                    imgByte = getFileStream(value.toString());
                    addPicture = workbook.addPicture(imgByte, workbook.PICTURE_TYPE_JPEG);
                    Drawing drawing = sheet.createDrawingPatriarch();
                    ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, cell.getColumnIndex(), row.getRowNum(), cell.getColumnIndex() + 1, row.getRowNum() + 1);
                    Picture picture = drawing.createPicture(anchor, addPicture);
                } else {
                    cell.setCellValue(value.toString());
                    setBorderStyle(style, cell);
                }
            }else{
                cell.setCellValue(" ");
                setBorderStyle(style,cell);
            }
        }
    }

    /**
     * 设置单元格 细 边框 并且内容居中
     * @param style
     * @param cell
     */
    private static void setBorderStyle(CellStyle style, Cell cell) {
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        cell.setCellStyle(style);
    }

     /**
     * 得到文件流 歌王
     * @param url
     * @return
     */
    public static byte[] getFileStream(String url){
        try {
            URL httpUrl = new URL(url);
            HttpURLConnection conn = (HttpURLConnection)httpUrl.openConnection();
            conn.setRequestMethod("GET");
            conn.setConnectTimeout(5 * 1000);
            InputStream inStream = conn.getInputStream();//通过输入流获取图片数据
            byte[] btImg = readInputStream(inStream);//得到图片的二进制数据
            return btImg;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 从输入流中获取数据 歌王
     * @param inStream 输入流
     * @return
     * @throws Exception
     */
    public static byte[] readInputStream(InputStream inStream) throws Exception{
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        byte[] buffer = new byte[1024];
        int len = 0;
        while( (len=inStream.read(buffer)) != -1 ){
            outStream.write(buffer, 0, len);
        }
        inStream.close();
        return outStream.toByteArray();
    }
    
}

时间类型单独特殊处理了一下，具体格式由下面这个注解决定：

@DateTimeFormat(pattern = "yyyy-MM-dd")

注释写的也很清楚了，哈哈，害怕下次看的时候忘了。

BorderStyle边框样式是个枚举，直接点就行，我翻译了一下，以实际为准哈，大概意思如下：

package org.apache.poi.ss.usermodel;

public enum BorderStyle {
    NONE(0),//无边框
    THIN(1),//细边框
    MEDIUM(2),//中等边框
    DASHED(3),//横杠虚线
    DOTTED(4),//点虚线
    THICK(5),//粗边框
    DOUBLE(6),//双线边框
    HAIR(7),//虚线
    MEDIUM_DASHED(8),//中虚线
    DASH_DOT(9),//点虚线
    MEDIUM_DASH_DOT(10),//中虚线点
    DASH_DOT_DOT(11),//点划线
    MEDIUM_DASH_DOT_DOT(12),//中划线圆点
    SLANTED_DASH_DOT(13);//斜点划线

    private final short code;
    private static final BorderStyle[] _table = new BorderStyle[14];

    private BorderStyle(int code) {
        this.code = (short)code;
    }

    public short getCode() {
        return this.code;
    }

    public static BorderStyle valueOf(short code) {
        return _table[code];
    }

    static {
        BorderStyle[] arr$ = values();
        int len$ = arr$.length;

        for(int i$ = 0; i$ < len$; ++i$) {
            BorderStyle c = arr$[i$];
            _table[c.getCode()] = c;
        }

    }
}

实体类

package com.wp.app.cdc.model.bis.export;

import com.wp.app.base.annotation.ExcelHeader;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

import java.util.Date;

/**
 * 归档申请单
 *
 * @author 刘银龙    @version V1.0     @date 2020-10-27 16:37:20
 */
@Data
public class CdcArchiveExport{

    /**
     * 名称
     */
    @ExcelHeader(value = "资料名称")
    private String cwName;

    /**
     * 标识
     */
    @ExcelHeader(value = "资料标识")
    private String cwCode;

    /**
     * 存储柜名称
     */
    @ExcelHeader(value = "存储柜名称")
    private String cccName;

    /**
     * 存储位置 行号-列号
     */
    @ExcelHeader(value = "存储位置")
    private String cccIndex;

    /**
     * 存储类别
     */
    @ExcelHeader(value = "存储类别")
    private String ctName;

    /**
     * 检测周期(天)
     */
    @ExcelHeader(value = "检测周期(天)")
    private Integer cwQcCycle;

    /**
     * 有效期
     */
    @ExcelHeader(value = "有效期")
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date cwValidPeriod;

    /**
     * 申请人
     */
    @ExcelHeader(value = "申请人")
    private String createName;

    /**
     * 申请部门
     */
    @ExcelHeader(value = "申请部门")
    private String createOrg;

    /**
     * 申请时间
     */
    @ExcelHeader(value = "申请时间")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createDate;

    /**
     * 状态
     */
    @ExcelHeader(value = "状态")
    private String archiveState;
    
    public void setArchiveState(Short state){
        switch (state) {
            case 0:
                archiveState = "草稿";
                break;
            case 1:
                archiveState = "提交";
                break;
            case 2:
                archiveState = "审核中";
                break;
            case 3:
                archiveState = "确认中";
                break;
            case 4:
                archiveState = "办理中";
                break;
            case 5:
                archiveState = "等待放入";
                break;
            case 6:
                archiveState = "等待拿出";
                break;
            case 8:
                archiveState = "结束";
                break;
            case 9:
                archiveState = "作废";
                break;
            default:
                archiveState = "未知";
                break;
        }
    }
}

Controller控制层导出

    /**
     * 生成Excel文件
     */
    @RequestMapping(value = "/bis/exportArchiveExcel")
    public void exportArchiveExcel(@RequestParam Map<String, Object> params, HttpServletRequest request, HttpServletResponse response) {

        Workbook workbook = null;
        OutputStream out = null;

        try {
            List<CdcArchive> cdcArchives = cdcArchiveService.queryCdcArchiveByItems(params);

            //获取用户名和机构名
            List<FormBaseInfo> parents = new ArrayList<FormBaseInfo>();
            parents.addAll(cdcArchives);
            GlobalUtil.getOrgUserNames(parents);
            List<CdcArchiveExport> cdcArchiveExports = new ArrayList<>();
            for (CdcArchive cdcArchive : cdcArchives) {
                CdcArchiveExport cdcArchiveExport = new CdcArchiveExport();
                BeanUtil.copyProperties(cdcArchive, cdcArchiveExport, false);
                cdcArchiveExport.setCccIndex(cdcArchive.getCwRow() + "-" + cdcArchive.getCwCol());
                cdcArchiveExport.setCreateName(cdcArchive.getCreateUserName());
                cdcArchiveExport.setCreateOrg(cdcArchive.getCreateSoName());
                cdcArchiveExport.setArchiveState(cdcArchive.getState());
                cdcArchiveExports.add(cdcArchiveExport);
            }

            // 生成Excel工作簿对象并写入数据
            workbook = ExcelWriter.exportData("归档申请流程",cdcArchiveExports,CdcArchiveExport.class);
            // 写入Excel文件到前端
            if(null != workbook){
                String fileName = "归档申请流程.xlsx";
                fileName = new String(fileName.getBytes("UTF-8"),"iso8859-1");
                response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
                response.setContentType("application/x-download");
                response.setCharacterEncoding("UTF-8");
                response.addHeader("Pargam", "no-cache");
                response.addHeader("Cache-Control", "no-cache");
                response.flushBuffer();
                out = response.getOutputStream();
                workbook.write(out);
                out.flush();
                out.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

生成后的Excel如下图：

歌王修改之后支持图片导出，如下图：

网易热评

导入jar包

注解接口

生成Excel工具类

实体类

Controller控制层导出

发送评论编辑评论

导入jar包

注解接口

生成Excel工具类

实体类

Controller控制层导出

发送评论 编辑评论

推荐文章

发送评论编辑评论