使用 springBoot+mysql+poi 进行数据查询，填充到excle表格中，最终实现表格下载（简单实现）v2-修改单元格样式

于 2021-06-24 18:53:06 发布
阅读量506 收藏 3
点赞数
分类专栏： java随笔 文章标签： java excel poi
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/JGYBZX_G/article/details/118193721
版权

springBoot 实现excle表格下载

描述

简单实现 springBoot 从数据库中获取数据，利用poi进行表格下载。
第二次更新内容：
1、解决某一个字段字数过多，导致列宽太长的问题。
2、解决excle无法识别 “\n”的问题

准备工作

1、效果展示

在这里插入图片描述

2、mysql数据库，建表语句+数据

CREATE TABLE `student` (
  `id` varchar(32) NOT NULL COMMENT '主键',
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL COMMENT '性别',
  `department` varchar(32) DEFAULT NULL COMMENT '部门',
  `address` varchar(255) DEFAULT NULL COMMENT '地址',
  `birthday` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `jgybzx`.`student`(`id`, `name`, `sex`, `department`, `address`, `birthday`) VALUES ('1', '张老大', '男', '财务部', '北京市海淀区', '1995-05-13');
INSERT INTO `jgybzx`.`student`(`id`, `name`, `sex`, `department`, `address`, `birthday`) VALUES ('2', '张老二', '男', '法律部', '北京市昌平区', '2020-07-27');
INSERT INTO `jgybzx`.`student`(`id`, `name`, `sex`, `department`, `address`, `birthday`) VALUES ('3', '张三', '女', '信息技术部', '湖南省永州市', '1995-05-12');
INSERT INTO `jgybzx`.`student`(`id`, `name`, `sex`, `department`, `address`, `birthday`) VALUES ('4', '李四', '男', '人力资源部', '辽宁省阜新市', '1995-05-19');
INSERT INTO `jgybzx`.`student`(`id`, `name`, `sex`, `department`, `address`, `birthday`) VALUES ('5', '王五', '女', '后勤部', '福建省厦门市', '1995-05-12');
INSERT INTO `jgybzx`.`student`(`id`, `name`, `sex`, `department`, `address`, `birthday`) VALUES ('6', '王六', '男', '信息技术部', '湖南省衡阳市', '2020-07-27');

3、poi maven 依赖，版本：3.8

        <!-- poi操作excel -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.8</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.8</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.8</version>
        </dependency>

过程

1、思路

  • 设置响应流，让浏览器进行下载。
  • 获取数据中的数据，封装到实体类中。
  • 数据写入excle。
  • 生成临时文件。
  • 读取临时文件，将文件流写给浏览器进行下载。
  • 删除临时文件。

2、具体实现

  1. 设置响应头类型以及响应头
    // 设置响应头类型
    response.setContentType(“application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”);
    // 让浏览器下载文件
    response.setHeader(“Content-Disposition”, “attachment;filename=”" + name + “”");

  2. excle数据写入
    1、首先创建工作簿，然后创建工作表。
    2、之后创建表头信息：此处利用的是反射，获取出实体类的所有属性名称，放到list数组中

    Field[] declaredFields = Student.class.getDeclaredFields();
    List attribute = Arrays.stream(declaredFields).map(Field::getName).collect(Collectors.toList());

    3、遍历属性名数组，在循环中，创建单元格，将表头数据写入。
    4、开始写入数据库中的数据，遍历从数据库中查出的数据集合，从第二行开始填充数据。
    此处进行两层循环，外层循环，是控制行。内层循环控制单元格。
    外层循环：获取每一个实体类对象，反射获取对象属性集合。
    内层循环：遍历属性集合，利用反射获取当前循环属性的值，填充到当前单元格中

  3. 在项目根目录下创建临时文件。
    将生成的临时文件写入到项目根目录下，同时返回文件路径。

  4. controller层，根据返回的临时文件路径，读取文件。
    通过FileInputStream读临时文件，ServletOutputStream将临时文件写给浏览器

  5. 关闭文件流，删除临时文件。

  6. 最后根据需要设置样式，包括字体、单元格宽度、背景颜色
    设置的样式，可以将表头的样式和数据的样式区分开，进行不同的设置
    在这里插入图片描述
    在这里插入图片描述

    其中单元格宽度自适应有可能版本的问题会不生效
    sheet.autoSizeColumn((short) num);

3、代码

  • controller层
    @GetMapping("export")
    public void export(HttpServletResponse response) {
        String name = "人员信息表.xlsx";
        //避免文件名中文乱码，将UTF8打散重组成ISO-8859-1编码方式
        name = new String(name.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
        // 设置响应头类型
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        // 让浏览器下载文件
        response.setHeader("Content-Disposition", "attachment;filename=\"" + name + "\"");
        InputStream inputStream = null;
        OutputStream outputStream = null;
        String downloadPath = studentService.exportStu();
        //根据临时文件的路径创建File对象，FileInputStream读取时需要使用
        File file = new File(downloadPath);
        try {
            //通过FileInputStream读临时文件，ServletOutputStream将临时文件写给浏览器
            inputStream = new FileInputStream(file);
            outputStream = response.getOutputStream();
            int len = -1;
            byte[] b = new byte[1024];
            while ((len = inputStream.read(b)) != -1) {
                outputStream.write(b);
            }
            //刷新
            outputStream.flush();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭输入输出流
            try {
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
        //最后才能，删除临时文件，如果流在使用临时文件，file.delete()是删除不了的
        file.delete();
    }
  • service层
    @Override
    public String exportStu() {
        //创建临时文件存放的路径
        String temp = System.getProperty("user.dir");
        List<Student> students = mapper.queryAll();

        //创建工作簿
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        CellStyle headCellStyle = setHeadCellStyle(xssfWorkbook);
        CellStyle cellStyle = setCellStyle(xssfWorkbook);
        //创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet();

        xssfWorkbook.setSheetName(0, "信息表");
        //创建表头
        XSSFRow head = sheet.createRow(0);
        // 获取对象所有属性
        Field[] declaredFields = Student.class.getDeclaredFields();
        List<String> attribute = Arrays.stream(declaredFields).map(Field::getName).collect(Collectors.toList());
        for (int i = 0; i < attribute.size(); i++) {
            XSSFCell cell = head.createCell(i);
            cell.setCellStyle(headCellStyle);
            cell.setCellValue(attribute.get(i));
        }

        // 填充数据（从第二行开始）
        for (int i = 1; i <= students.size(); i++) {
            Student student = students.get(i - 1);
            XSSFRow row = sheet.createRow(i);
            Class<? extends Student> aClass = student.getClass();
            Field[] declaredFields1 = aClass.getDeclaredFields();
            // 创建单元格 填充数据
            for (int num = 0; num < declaredFields1.length; num++) {
                XSSFCell cell = row.createCell(num);
                cell.setCellStyle(cellStyle);
                String attributeValue = "";
                try {
                    Field field = declaredFields1[num];
                    field.setAccessible(true);
                    attributeValue = field.get(student).toString();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                cell.setCellValue(attributeValue);
                // 设置单元格自适应
                sheet.autoSizeColumn((short) num);
            }

        }
        //创建临时文件的目录
        File file = new File(temp);
        if (!file.exists()) {
            file.mkdirs();
        }
        //临时文件路径/文件名
        String downloadPath = file + "\\" + System.currentTimeMillis() + UUID.randomUUID();
        OutputStream outputStream = null;
        try {
            //使用FileOutputStream将内存中的数据写到本地，生成临时文件
            outputStream = new FileOutputStream(downloadPath);
            xssfWorkbook.write(outputStream);
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return downloadPath;
    }
  • 单元格格式
    /**
     * 设置表头单元格格式
     *
     * @param xssfWorkbook
     * @return org.apache.poi.ss.usermodel.CellStyle
     * @author jgybzx
     * @date 2021/6/24 16:20
     */
    CellStyle setHeadCellStyle(XSSFWorkbook xssfWorkbook) {
        //创建styleHead
        CellStyle cellStyle = xssfWorkbook.createCellStyle();
        //背景色 天蓝色
        cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //水平居中
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        XSSFFont font = xssfWorkbook.createFont();
        //加粗
        font.setBold(true);
        //字体大小
        font.setFontHeight((short) 280);
        // 字体
        font.setFontName("宋体");
        cellStyle.setFont(font);
        // 设置边框
        setBorderStyle(cellStyle);
        return cellStyle;
    }

    /**
     * 设置非表头单元格格式
     *
     * @param xssfWorkbook
     * @return org.apache.poi.ss.usermodel.CellStyle
     * @author jgybzx
     * @date 2021/6/24 16:20
     */
    CellStyle setCellStyle(XSSFWorkbook xssfWorkbook) {
        //创建styleHead
        CellStyle cellStyle = xssfWorkbook.createCellStyle();
        //水平居中
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        XSSFFont font = xssfWorkbook.createFont();
        //加粗
        font.setBold(false);
        //字体大小
        font.setFontHeight((short) 240);
        // 字体
        font.setFontName("宋体");
        cellStyle.setFont(font);
        // 设置边框
        setBorderStyle(cellStyle);
        return cellStyle;
    }

    /**
     * 边框样式
     *
     * @param cellStyle
     * @return org.apache.poi.ss.usermodel.CellStyle
     * @author jgybzx
     * @date 2021/6/24 16:14
     */
    private CellStyle setBorderStyle(CellStyle cellStyle) {
        // 底部边框+颜色 BORDER_THIN:细线
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        // 左边边框+颜色
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        // 右边边框+颜色
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        // 上边边框+颜色
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        return cellStyle;
    }

注意

请求方式使用get请求。
注意文件流的关闭。
单元格自适应可能回不起作用。
如果单元格样式进行多次设置，最后一次的设置会覆盖掉上边的样式。
想要进行更加美观的样式设置，需要自行百度各个参数的用法，比如字体、边框、背景颜色。
本次是简单的数据填充，不涉及到单元格合并。

修改单元格样式（第二次更新）

问题

1、实际工作中遇到某一个字段字数过多，导致列宽太长的问题，需要针对这个字段进行单独设置样式。
2、遇到换行的数据，直接将 “\n”输出到表格了，没有换行显示。
在这里插入图片描述

解决

  • 取消列宽自适应，改为固定宽度 40个字符。
    在这里插入图片描述

  • 取消水平居中，改为左对齐。
    在这里插入图片描述

  • 设置自动换行。

 //自动换行
cellStyle.setWrapText(true);
  • 将“\n”替换文“< b r >”
replace("\\n", "<br>");

效果

在这里插入图片描述

更新后的代码（service层）

    @Override
    public String exportStu(List<Customer> customerList) {
        //创建临时文件存放的路径
        String temp = System.getProperty("user.dir");
        //创建工作簿
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        //创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet();
        xssfWorkbook.setSheetName(0, "学生信息表");
        //创建表头
        XSSFRow head = sheet.createRow(0);
        CellStyle headCellStyle = setHeadCellStyle(xssfWorkbook);
        // 获取对象所有属性
        // Field[] declaredFields = Customer.class.getDeclaredFields();
        // List<String> attribute = Arrays.stream(declaredFields).map(Field::getName).collect(Collectors.toList());
        String[] attribute = {"序号", "客户号", "客户姓名", "客户概述", "客户联系电话", "咨询人姓名", "咨询人联系电话", "客户级别", "销售人员", "未跟踪天数", "末次跟踪日期", "沟通记录", "进度"};
        for (int i = 0; i < attribute.length; i++) {
            XSSFCell cell = head.createCell(i);
            cell.setCellStyle(headCellStyle);
            cell.setCellValue(attribute[i]);
        }

        // 填充数据（从第二行开始）
        CellStyle cellStyle = setCellStyle(xssfWorkbook, true);
        for (int i = 1; i <= customerList.size(); i++) {
            Customer customer = customerList.get(i - 1);
            XSSFRow row = sheet.createRow(i);
            Class<? extends Customer> aClass = customer.getClass();
            Field[] declaredFields1 = aClass.getDeclaredFields();
            // 创建单元格 填充数据
            for (int num = 0; num < declaredFields1.length; num++) {
                sheet.autoSizeColumn((short) num);
                XSSFCell cell = row.createCell(num);
                String attributeValue;

                cell.setCellStyle(cellStyle);
                try {
                    Field field = declaredFields1[num];
                    field.setAccessible(true);
                    if ("communicationRecord".equals(field.getName())) {
                        // 单独设置可能数据量大的单元格，进行固定宽度，自动换行，取消水平居中
                        sheet.setColumnWidth(11, 40 * 256);
                        cell.setCellStyle(setCellStyle(xssfWorkbook, false));
                    }
                    attributeValue = field.get(customer).toString();
                    attributeValue.replace("\\n", "<br>");
                    cell.setCellValue(attributeValue);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
        }
        //创建临时文件的目录
        File file = new File(temp);
        if (!file.exists()) {
            file.mkdirs();
        }
        //临时文件路径/文件名
        String downloadPath = file + "\\" + System.currentTimeMillis() + UUID.randomUUID();
        OutputStream outputStream = null;
        try {
            //使用FileOutputStream将内存中的数据写到本地，生成临时文件
            outputStream = new FileOutputStream(downloadPath);
            xssfWorkbook.write(outputStream);
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return downloadPath;
    }

    /**
     * 设置表头单元格格式
     *
     * @param xssfWorkbook
     * @return org.apache.poi.ss.usermodel.CellStyle
     * @author jgybzx
     * @date 2021/6/24 16:20
     */
    CellStyle setHeadCellStyle(XSSFWorkbook xssfWorkbook) {
        //创建styleHead
        CellStyle cellStyle = xssfWorkbook.createCellStyle();
        //背景色 天蓝色
        cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //水平居中
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        XSSFFont font = xssfWorkbook.createFont();
        //加粗
        font.setBold(true);
        //字体大小
        font.setFontHeight((short) 280);
        // 字体
        font.setFontName("宋体");
        cellStyle.setFont(font);
        // 设置边框
        setBorderStyle(cellStyle);
        return cellStyle;
    }

    /**
     * 单元格样式
     *
     * @param xssfWorkbook
     * @param alignmentFlag 是否垂直居中标识
     * @return org.apache.poi.ss.usermodel.CellStyle
     * @author jgybzx
     * @date 2021/6/25 9:52
     */
    CellStyle setCellStyle(XSSFWorkbook xssfWorkbook, boolean alignmentFlag) {
        //创建styleHead
        CellStyle cellStyle = xssfWorkbook.createCellStyle();
        if (alignmentFlag) {
            //水平居中设置
            cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        } else {
            //水平居中设置
            cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
        }
        // 垂直居中设置
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        XSSFFont font = xssfWorkbook.createFont();
        //加粗
        font.setBold(false);
        //字体大小
        font.setFontHeight((short) 240);
        // 字体
        font.setFontName("宋体");
        cellStyle.setFont(font);
        // 设置边框
        setBorderStyle(cellStyle);
        //自动换行
        cellStyle.setWrapText(true);
        return cellStyle;
    }

    /**
     * 边框样式
     *
     * @param cellStyle
     * @return org.apache.poi.ss.usermodel.CellStyle
     * @author jgybzx
     * @date 2021/6/24 16:14
     */
    private CellStyle setBorderStyle(CellStyle cellStyle) {
        // 底部边框+颜色 BORDER_THIN:细线
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        // 左边边框+颜色
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        // 右边边框+颜色
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        // 上边边框+颜色
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        return cellStyle;
    }
JGYBZX_G
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值