Jxls 实现带模板的excle的excel导出

1.创建Excel模板

• 1) mployees 是一个 List<Employee> employees
• 2) mployee 是 employees 的一个对象
• 3) $employee.name} 对象employee的属性 name
• 4) 注释 jx:area(lastCell="D2") 表示从当前的 A1 到 D2 也就是A1:D2的 2 * 4 范围内是模板内容
• 5) 注释 jx:each(item="employees" var="employee" lastCell="D2") 表示从当前 A2 到 D2 即 A2:D2 部分是 Jxls 中 Context对象(也就是需要填充到excle模板中的数据)中的集合中的对象 Employee 中字段与 ${employee.字段} 匹配的区域

2.Maven (后面会有完整的项目地址)

<!-- jxls -->
<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls</artifactId>
    <version>2.8.1</version>
</dependency>
<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-poi</artifactId>
    <version>2.8.1</version>
</dependency>

3.项目目录结构

我太难了,图片又双叒叕上传失败了~

4. application.properties

server.port=8080

5.Employee

package com.zhiyue.study.pojo;

import com.fasterxml.jackson.annotation.JsonFormat;
import java.math.BigDecimal;
import java.util.Date;

/**
 * @Description 员工表实体
 * @Project spirng-boot-jxls
 * @Author ZhiYue
 * @Date 2020/5/29 15:43
 */
public class Employee {

    private String name;
    private Date birthDate;
    private BigDecimal payment;
    private BigDecimal bonus;
    
    // setter & getter 省略
}

6.SpringBoot控制器

package com.zhiyue.study;

import com.zhiyue.study.pojo.Employee;
import jdk.nashorn.internal.runtime.logging.Logger;
import org.jxls.common.Context;
import org.jxls.util.JxlsHelper;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Description jxls excel 下载
 * @Project spirng-boot-jxls
 * @Author ZhiYue
 * @Date 2020/5/29 17:39
 */
@RestController
public class JxlsController {

    @GetMapping("/export")
    public void jxlsExport(HttpServletRequest request, HttpServletResponse response) throws IOException {
        List<Employee> employees = getEmployees();
        String path = new File("").getAbsolutePath() + "/src/main/resources/aaa.xls";

        // InputStream is = JxlsController.class.getResourceAsStream("aaa.xls"); // TODO 1
        InputStream is = new FileInputStream(path);
        String fileName = URLEncoder.encode("export.xls", "UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        OutputStream os = response.getOutputStream();
        Context context = new Context();
        context.putVar("employees", employees);
        JxlsHelper.getInstance().processTemplate(is, os, context);
        os.flush();
        os.close();
        
    }

    // 数据模拟
    private List<Employee> getEmployees() {
        List<Employee> data = new ArrayList<Employee>(2);
        Employee e1 = new Employee();
        e1.setBirthDate(new Date());
        e1.setBonus(new BigDecimal(200));
        e1.setName("zhiyue");
        e1.setPayment(new BigDecimal(5000));
        Employee e2 = new Employee();
        e2.setBirthDate(new Date());
        e2.setBonus(new BigDecimal(200));
        e2.setName("gengniao");
        e2.setPayment(new BigDecimal(5000));
        data.add(e1);
        data.add(e2);
        return data;
    }
}

注意: TODO 1 处使用 JxlsController.class.getResourceAsStream("aaa.xls") 会出现异常 org.jxls.util.CannotOpenWorkbookException: java.lang.NullPointerException 目前还不知道原因,希望大佬能指点我

7.SpringBoot启动类

package com.zhiyue.study;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * @Description
 * @Project spirng-boot-jxls
 * @Author ZhiYue
 * @Date 2020/5/29 15:31
 */
@SpringBootApplication
public class SampleApplication {

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

8.页面访问 : localhost:8080/export

9.最后一个坑

• birthDate 是时间格式,这里我们需要 设置他的单元格格式

10.项目地址

https://github.com/gengniao/s...