JXLS写入一份excel多个sheet实例

官方文档如下：

https://jxls.sourceforge.net/samples/multi_sheet_markup_demo.html

遇到一麻烦需求，客户需要同时打印多分excel，有的甚至高达100多份，本来excel支持多个excel同时打印，但是很遗憾，客户的打印电脑太久远了，打印失败崩溃。。。。。。

为了让打印小哥，不需要一张张频繁打开打印，因此，需要开发将100多人的成绩单写入一份excel，每人一个sheet。由于基础导出技术栈使用的jxls，因此，就直接用到了多个sheet写入了。

开始：

加入依赖，可以参考官网，我这是与easy-excel兼容后的处理版本

        <!--导出excel组件 start-->
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls</artifactId>
            <version>2.10.0</version>
            <exclusions>
                <exclusion>
                    <groupId>ch.qos.logback</groupId>
                    <artifactId>logback-core</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls-poi</artifactId>
            <version>2.10.0</version>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!--导出excel组件 end-->

import lombok.Data;

import java.util.Date;

@Data
public class Employee {

    private String name;
    private int age;
    private Double payment;
    private Double bonus;
    private Date birthDate;
    private Employee superior;

}

import lombok.Data;

import java.util.ArrayList;
import java.util.List;

@Data
public class Department {

    private String name;
    private Employee chief;
    private List<Employee> staff = new ArrayList<Employee>();
    private String link;

}

import org.junit.Test;
import org.jxls.common.Context;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.util.JxlsHelper;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class DuoSheetTest {


    String templateFileName = "D:\\workspace\\ty-fstme-pa\\src\\main\\test\\com\\inspur\\duoSheet\\duoSheetTemplate.xlsx";
    String fileName = "D:\\workspace\\ty-fstme-pa\\src\\main\\test\\com\\inspur\\duoSheet\\duoSheet001.xlsx";

    @Test
    public void test(){

        List<Department> departments = createDepartments();

        try(InputStream is = new FileInputStream(templateFileName)) {
            try (OutputStream os = new FileOutputStream(fileName)) {
                Context context = PoiTransformer.createInitialContext();
                context.putVar("departments", departments);
                context.putVar("sheetNames", Arrays.asList(
                        departments.get(0).getName(),
                        departments.get(1).getName(),
                        departments.get(2).getName()));
                JxlsHelper.getInstance().processTemplate(is, os, context);
            }catch (Exception e){
                e.printStackTrace();
            }
        }catch (Exception e){
            e.printStackTrace();
        }

    }

    private List<Department> createDepartments() {

        List<Department> departmentList = new ArrayList<>();
        for(int i=1; i < 4 ; i++ ){

            Department department = new Department();
            department.setName("班级" + i);

            List<Employee> staff = new ArrayList();
            for(int j = 1 ; j < 11; j ++){
                Employee employee = new Employee();
                employee.setName("学生"+j);
                employee.setAge(11-j);
                staff.add(employee);
            }
            department.setStaff(staff);

            departmentList.add(department);
        }

        return departmentList;

    }
}

Excel模板 duoSheetTemplate.xlsx

由上向下，3个批注

jx:area(lastCell="B5")

jx:each(items="departments", var="department", lastCell="B5" multisheet="sheetNames")

其中最关键的便是 multisheet="sheetNames" 此处决定是否是分sheet

jx:each(items="department.staff", var="employee", lastCell="B5")

最后结果展示：