POI工具操作excel（六）基于模板打印进行导出

1、概述

       上一篇 我们已经进行了导出excel的工具类的封装，但是还有一种方式可以实现通用的功能，而且样式可以更加随客户的意思去制定。并且它有一个好处，我们可以不用去管excel表格的表头的合并及其样式。

       这种实现方式的思路是：首先手动创建一个excel，里面创建好标题和表头，真正的数据行只需要一行就够了，然后在这一行中，为每列都设计好样式（字体大小，字体型号，字体颜色，背景颜色等）。通过程序去读取该文件，得到文件流，然后通过文件流创建对应的工作簿。这个得到的工作簿就拥有了所有的内容，包括样式。我们的任务就是得到内容行，获取每一格的样式并存储起来。然后在循环进行样式的设置。把这个过程成为模板打印。

模板打印的步骤：

1. 制作模版文件（模版文件的路径）
2. 导入（加载）模版文件，从而得到一个工作簿
3. 读取工作表
4. 读取行
5. 读取单元格
6. 读取单元格样式
7. 设置单元格内容
8. 其他单元格就可以使用读到的样式了

 

2 创建模板文件

我们先用excel创建一个文件，里面根据自己的爱好设置好格式，我这里设置的样式如下（这个是自己随便设置的，呵呵）：

3、代码实现

3.1 基于上传的模板文件然后进行excel文件的导出

3.1.1 逻辑代码

我们通过postman可以进行文件的上传，然后再下载文件，这是我们的第一种方式。下面的代码：

package com.poi.controller;

import com.poi.pojo.User;
import com.poi.util.DownloadUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 模板打印的控制器
 */
@Controller
@RequestMapping("/model")
public class ModelController {

    /**
     * 这种方式：我们通过程序上传一个模板文件，然后进行导出
     * @param file
     */
    @RequestMapping("/userList")
    public void exportExcelByModel(@RequestParam("file") MultipartFile file, HttpServletRequest request, HttpServletResponse response){
        try {

            //获取数据，正式环境是从数据库获取的，这里我们就手动构造
            List<User> userList = this.getUserList();

            //获取模板文件的输入流
            InputStream inputStream = file.getInputStream();
            String fileName = file.getName();//这个获取的二进制文件对应的name属性的名称，这里是file
            String originalFilename = file.getOriginalFilename();//这个获取的原始的文件名称
            String ext = originalFilename.substring(originalFilename.lastIndexOf(".")+1);//截取得到后缀名
            //通过流创建工作簿，根据文件的后缀名判断创建不同的工作簿
            Workbook workbook = null;
            if("xls".equals(ext)){
                workbook = new HSSFWorkbook(inputStream);
            }else if("xlsx".equals(ext)){
                workbook = new XSSFWorkbook(inputStream);
            }
            //得到第一个sheet
            Sheet sheet = workbook.getSheetAt(0);
            //得到内容的第一行的每一单元格的样式，存到数组中
            Row row = sheet.getRow(2);//我们的内容是从第3行开始的，所以索引是2
            CellStyle[] cellStyles = new CellStyle[row.getLastCellNum()];//根据该行的有内容的列数创建存储样式的数组
            for(int i = 0; i<row.getLastCellNum(); i++){
                Cell cell = row.getCell(i);//得到单元格对象
                CellStyle cellStyle = cell.getCellStyle();//获取该单元格的格式
                cellStyles[i] = cellStyle;//把样式存储到样式数组中，索引跟列索引对应的
            }

            //遍历数据集合，循环设置值和样式
            int startRowIndex = 2;//设置数据内容的开始行的行号，第三行的索引是2
            Cell cell = null;
            for(User user : userList){
                //创建行对象
                row = sheet.createRow(startRowIndex++);

                //用户ID
                cell = row.createCell(0);
                cell.setCellValue(user.getId());
                cell.setCellStyle(cellStyles[0]);

                //姓名
                cell = row.createCell(1);
                cell.setCellValue(user.getName());
                cell.setCellStyle(cellStyles[1]);

                //年龄
                cell = row.createCell(2);
                cell.setCellValue(user.getAge());
                cell.setCellStyle(cellStyles[2]);

                //姓名
                cell = row.createCell(3);
                cell.setCellValue(user.getHigh());
                cell.setCellStyle(cellStyles[3]);

                //姓名
                cell = row.createCell(4);
                cell.setCellValue(user.getAddress());
                cell.setCellStyle(cellStyles[4]);
            }

            //下载文件
            DownloadUtil.downloadFile(workbook,request,response,"测试模板导出excel",".xlsx");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private List<User> getUserList() {
        List<User> userList = new ArrayList<>();
        User user1 = new User("1001","老庞",38,new Date(System.currentTimeMillis()-1000*60*60*24*365),"湖南省长沙市岳麓区岳麓书院1",180.5);
        User user2 = new User("1002","老王",38,new Date(System.currentTimeMillis()-1000*60*60*24),"湖南省长沙市岳麓区岳麓书院2",170.5);
        User user3 = new User("1003","老李",38,new Date(System.currentTimeMillis()-1000*60*60*24*365*2),"湖南省长沙市岳麓区岳麓书院3",160.5);
        User user4 = new User("1004","老周",38,new Date(System.currentTimeMillis()-1000*60*60*24*365*10),"湖南省长沙市岳麓区岳麓书院4",190.5);
        User user5 = new User("1005","老赵",38,new Date(System.currentTimeMillis()-1000*60*60*24*365*20),"湖南省长沙市岳麓区岳麓书院5",150.5);
        userList.add(user1);
        userList.add(user2);
        userList.add(user3);
        userList.add(user4);
        userList.add(user5);

        return userList;
    }
}

3.1.2 文件下载的工具类

excel导出，文件下载的工具类：

package com.poi.util;

import org.apache.poi.ss.usermodel.Workbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.Base64;

/**
 * excel导出下载文件的工具类
 */
public class DownloadUtil {

    /**
     * 文件下载的方法
     * @param workbook  工作簿
     * @param request   请求
     * @param response  响应
     * @param filename  文件名称
     * @param ext       文件扩展名
     */
    public static void downloadFile(Workbook workbook, HttpServletRequest request, HttpServletResponse response, String filename, String ext){
        filename = filename+ext;//文件名+扩展名
        //调用其他下载方法
        downloadFile(workbook,request,response,filename);
    }

    private static void downloadFile(Workbook workbook,HttpServletRequest request, HttpServletResponse response,String filename){
        try {
            //从请求头中获取User-Agent判断当前使用的是否是火狐浏览器
            String agent = request.getHeader("User-Agent");
            //根据不同浏览器进行不同的编码
            String realFilename = "";
            if (agent.contains("MSIE")) {
                // IE浏览器
                realFilename = URLEncoder.encode(filename, "utf-8");
                realFilename = realFilename.replace("+", " ");
            } else if (agent.contains("Firefox")) {
                // 火狐浏览器，此处使用java8
                realFilename = "=?utf-8?B?" + Base64.getEncoder().encodeToString(filename.getBytes("utf-8")) + "?=";
            } else {
                // 其它浏览器
                realFilename = URLEncoder.encode(filename, "utf-8");
            }
            //设置要被下载的文件名
            response.setHeader("Content-Disposition","attachment;filename="+realFilename);
            response.setContentType("application/octet-stream");
            response.setHeader("filename", filename);
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

3.1.3 postman测试

这里涉及到文件的上传和下载，为了测试方便，不在进行页面的书写，而是利用一个工具postman进行测试：

请求路径和header的设置如下：

Body中的参数设置如下：

最后要注意的一点，因为我们是要做文件的下载，所以发送的方式不是简单的Send，而是要选择Send And Download：

当我们点击Send and Download，请求成功之后，会弹出一个框来，让我们保存下载的文件，下载完成，打开文件查看效果：

 

3.2 基于模板文件存放到工程中，然后进行excel文件的导出

       这种方式是提前制作好模板文件，然后存放到工程的类路径下，程序里面就从类路径下获取文件，得到文件流。其他的操作是和3.1 的方式是一样的，不过相比他没有这么灵活，如果模板有修改，就得重新部署项目才行。不过这里还是稍微讲解下如何实现。

import com.poi.pojo.User;
import com.poi.util.DownloadUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 模板打印的控制器
 */
@Controller
@RequestMapping("/model")
public class ModelController {

    /**
     * 这种方式：我们通过程序上传一个模板文件，然后进行导出
     * @param file
     */
    @RequestMapping("/userList")
    public void exportExcelByModel(/*@RequestParam("file") MultipartFile file, */HttpServletRequest request, HttpServletResponse response){
        try {

            //获取数据，正式环境是从数据库获取的，这里我们就手动构造
            List<User> userList = this.getUserList();

            //获取模板文件的输入流
            /*InputStream inputStream = file.getInputStream();
            String fileName = file.getName();//这个获取的二进制文件对应的name属性的名称，这里是file
            String originalFilename = file.getOriginalFilename();//这个获取的原始的文件名称
            String ext = originalFilename.substring(originalFilename.lastIndexOf(".")+1);*///截取得到后缀名

            //Spring提供了一个Resource类，可以用来加载相关的配置文件,ClassPathResource是从类路径下加载
            Resource resource = new ClassPathResource("excel-template/测试模板.xlsx");
            InputStream inputStream = new FileInputStream(resource.getFile());
            String ext = resource.getFilename().substring(resource.getFilename().lastIndexOf(".")+1);
            //下面的代码完全是和之前的一模一样的

    }
}

注意：方法声明的形参中，把接收文件的参数去掉：/*@RequestParam("file") MultipartFile file, */，这里修改的只是获取文件的流的方式改变了，达到的效果是一样的。

 

 

4、工具类封装

上面的有些代码是比较繁杂的，每个列都要进行数据和样式的设置。我们也可以基于反射和注解写一个工具类，这样就只要把数据传给工具类就可以进行导出了。

4.1 定义注解

本次定义注解@ModelColumn，注解当中只要一个属性就可以了，那就是sort

package com.poi.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ModelColumn {
    int sort();//单元格对应的列号，从1开始
}

4.2 定义Java类，并添加注解

package com.poi.pojo.exp;

import com.poi.annotation.ModelColumn;

import java.util.Date;

public class User {
    //如果这个属性需要导出，那么就在上面添加注解信息
    @ModelColumn(sort = 2)
    private String name;
    @ModelColumn(sort = 1)
    private String id;

    @ModelColumn(sort = 3)
    private int age;
    private Date birthday;
    @ModelColumn(sort = 5)
    private String address;
    @ModelColumn(sort = 4)
    private double high;

    public User() {
    }

    public User(String id, String name, int age, Date birthday, String address, double high) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.birthday = birthday;
        this.address = address;
        this.high = high;
    }

   //get/set方法
}

4.3 导出工具类

package com.poi.util;

import com.poi.annotation.ModelColumn;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.List;

public class ModelExcelUtil<T> {

    private Class<T> clazz;//导出对象的Class对象
    private Field[] fields;//所有属性的数组，包含私有的
    private int startRowIndex;

    public ModelExcelUtil(Class<T> clazz,String titleName, int startRowIndex){
        this.clazz = clazz;
        this.fields = clazz.getDeclaredFields();
        this.startRowIndex = startRowIndex-1;
    }

    public void modelExcelExport(InputStream inputStream, List<T> dataList, HttpServletRequest request, HttpServletResponse response, String fileName, String ext){
        try {
            //通过流创建工作簿，根据文件的后缀名判断创建不同的工作簿
            Workbook workbook = null;
            if("xls".equals(ext)){
                workbook = new HSSFWorkbook(inputStream);
            }else if("xlsx".equals(ext)){
                workbook = new XSSFWorkbook(inputStream);
            }
            //得到第一个sheet
            Sheet sheet = workbook.getSheetAt(0);
            //得到内容的第一行的每一单元格的样式，存到数组中
            Row row = sheet.getRow(startRowIndex);//得到起始行
            CellStyle[] cellStyles = new CellStyle[row.getLastCellNum()];//根据该行的有内容的列数创建存储样式的数组
            for(int i = 0; i<row.getLastCellNum(); i++){
                Cell cell = row.getCell(i);//得到单元格对象
                CellStyle cellStyle = cell.getCellStyle();//获取该单元格的格式
                cellStyles[i] = cellStyle;//把样式存储到样式数组中，索引跟列索引对应的
            }

            //遍历数据集合，循环设置值和样式
            Cell cell = null;
            for(T t : dataList) {
                //创建行对象
                row = sheet.createRow(startRowIndex++);
                for(int i=0; i<fields.length; i++){
                    Field field = fields[i];//得到属性
                    field.setAccessible(true);//这个必须要设计
                    boolean annotationPresent = field.isAnnotationPresent(ModelColumn.class);
                    if(annotationPresent){//说明该属性存在注解
                        ModelColumn annotation = field.getAnnotation(ModelColumn.class);
                        int sort = annotation.sort();//得到属性上面的排序
                        cell = row.createCell(sort-1);//得到列
                        cell.setCellStyle(cellStyles[sort-1]);//设置样式
                        cell.setCellValue(field.get(t).toString());//设置值
                    }
                }
            }
            //下载文件
            DownloadUtil.downloadFile(workbook,request,response,fileName,ext);
        }catch (Exception e){
            e.printStackTrace();
        }

    }
}

4.4 编写测试Controller

注意我们的模板还是使用上面的模板，我们的模板中，表头是用户ID，姓名，年龄，身高和地址。所以我们的User类中，属性上注解的sort值要与这个顺序对应。

package com.poi.controller;

import com.poi.pojo.exp.User;
import com.poi.util.ModelExcelUtil;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 模板打印  导出excel 的控股器
 */
@Controller
@RequestMapping("/excel")
public class ModelExportExcelController {

    @RequestMapping("/modelUserList")
    public void model(@RequestParam("file")MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws IOException {
        //获取数据，正式环境是从数据库获取的，这里我们就手动构造
        List<User> userList = this.getUserList();
        InputStream inputStream = file.getInputStream();
        new ModelExcelUtil<User>(User.class,"用户列表",3)
                .modelExcelExport(inputStream,userList,request,response,"模板测试","xlsx");
    }

    private List<User> getUserList() {
        List<User> userList = new ArrayList<>();
        User user1 = new User("1001","老庞",38,new Date(System.currentTimeMillis()-1000*60*60*24*365),"湖南省长沙市岳麓区岳麓书院1",180.5);
        User user2 = new User("1002","老王",38,new Date(System.currentTimeMillis()-1000*60*60*24),"湖南省长沙市岳麓区岳麓书院2",170.5);
        User user3 = new User("1003","老李",38,new Date(System.currentTimeMillis()-1000*60*60*24*365*2),"湖南省长沙市岳麓区岳麓书院3",160.5);
        User user4 = new User("1004","老周",38,new Date(System.currentTimeMillis()-1000*60*60*24*365*10),"湖南省长沙市岳麓区岳麓书院4",190.5);
        User user5 = new User("1005","老赵",38,new Date(System.currentTimeMillis()-1000*60*60*24*365*20),"湖南省长沙市岳麓区岳麓书院5",150.5);
        userList.add(user1);
        userList.add(user2);
        userList.add(user3);
        userList.add(user4);
        userList.add(user5);

        return userList;
    }
}