使用JXLS+Excel模板制作灵活的excel导出

前期一直卡在模板的批注上，改了很多遍的模板批注最终才成功导入，记录下方便以后寻找。

话不多说直接上代码：

Report

package com.example.jxls.common;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Map;
import java.util.Map.Entry;

import org.jxls.common.Context;
import org.jxls.util.JxlsHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class Report {

	private static final Logger logger = LoggerFactory.getLogger(Report.class);

	public void createDocument(OutputStream outStream, String templateName, Map<String, Object> data) {
		logger.debug("Start creation of document");

		String pathTemplateName = ("/reports/").concat(templateName).concat(".xls");
		try(InputStream input = this.getClass().getResourceAsStream(pathTemplateName)) {//1
		
            Context context = new Context();
            
            for (Entry<String, Object> element : data.entrySet()) { // 2
            	context.putVar(element.getKey(), element.getValue());
			}
//			System.out.println("context = " + context);
            JxlsHelper.getInstance().processTemplate(input, outStream, context); // 3

		} catch (Exception exception) {
			logger.error("Fail to generate the document", exception);
		} finally {
			closeAndFlushOutput(outStream); // 4
		}
	}

	private void closeAndFlushOutput(OutputStream outStream) {
		try {
			outStream.flush();
			outStream.close();
		} catch (IOException exception) {
			logger.error("Fail to flush and close the output", exception);
		}
	}
}

 

CommonDao

 

package com.example.jxls.dao;

import com.example.jxls.model.Client;
import com.github.javafaker.Faker;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

public class CommonDao {
	
	public List<Client> getAllClients() {
		
		List<Client> clients = new ArrayList<>();
		Faker faker = new Faker();

		for (int i = 0; i < 20; i++) {
			clients.add(new Client(faker.name().firstName(), 
					faker.name().lastName(), 
					faker.bool().bool(),
					"SSN",
					faker.idNumber().ssnValid(),
					BigDecimal.valueOf(faker.number().numberBetween(1, 100000))));
		}
		
		return clients;
	}
}

UserTask

package com.example.jxls.model;

import lombok.Data;


public class UserTask {
    //用户id
    private String id;
    //用户名称
    private String userName;
    //任务Id
    private String taskId;
    //插件id
    private String  plugId;
    //班级id
    private String  classId;

    public UserTask(String id, String userName, String taskId, String plugId, String classId) {
        this.id = id;
        this.userName = userName;
        this.taskId = taskId;
        this.plugId = plugId;
        this.classId = classId;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getTaskId() {
        return taskId;
    }

    public void setTaskId(String taskId) {
        this.taskId = taskId;
    }

    public String getPlugId() {
        return plugId;
    }

    public void setPlugId(String plugId) {
        this.plugId = plugId;
    }

    public String getClassId() {
        return classId;
    }

    public void setClassId(String classId) {
        this.classId = classId;
    }
}

CommonService

package com.example.jxls.service;

import com.example.jxls.common.Report;
import com.example.jxls.dao.CommonDao;
import com.example.jxls.model.UserTask;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class CommonService {
	
	private CommonDao dao;
	
	public CommonService() {
		dao = new CommonDao();
	}
	
	private void createCommonClientReport(String templateName, String outputName) throws FileNotFoundException, ParseException {
		Report report = new Report();
		
		OutputStream outStream = new FileOutputStream(outputName);
		List<UserTask> employees = generateSampleEmployeeData();
		Map<String, Object> data = new HashMap<>();
//		data.put("createdAt", "2021-01-01");
//		data.put("clients", dao.getAllClients());
		data.put("employees", employees);
		data.put("nowdate", new Date());
		data.put("data2", "222222qwer");
		report.createDocument(outStream, templateName, data);
	}
	public static List<UserTask> generateSampleEmployeeData() throws ParseException {
		List<UserTask> employees = new ArrayList<UserTask>();
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MMM-dd", Locale.US);
		employees.add( new UserTask("2L", "1970-Jul-10", "1500L", "0.15", "1500L") );
		employees.add( new UserTask("3L", "1973-Apr-30", "2300L", "0.15","2300L") );
		employees.add( new UserTask("4L", "1975-Oct-05", "2500L", "0.15","2500L") );
		employees.add( new UserTask("5L", "1978-Jan-07", "1700L", "0.15","1700L") );
		employees.add( new UserTask("6L", "1969-May-30", "2800L", "0.15","2800L") );
		return employees;
	}
	public void createClientReport1() throws FileNotFoundException, ParseException {
		createCommonClientReport("clientsTemplate", "target/clients.xls");
	}
	public void createClientReport2() throws FileNotFoundException, ParseException {
		createCommonClientReport("2111", "target/clients3.xls");
	}
	
//	public void createClientReportWithConditions() throws FileNotFoundException, ParseException {
//		createCommonClientReport("clientsMarkInactiveTemplate", "target/clientsMarkInactive.xls");
//	}
}

Application

package com.example.jxls;

import com.example.jxls.service.CommonService;

import java.io.FileNotFoundException;
import java.text.ParseException;

public class Application {

	public static void main(String[] args) throws FileNotFoundException, ParseException {
		CommonService service = new CommonService();
		
		service.createClientReport2();
		
//		service.createClientReportWithConditions();
	}

}

demo1模板示例：

jx:area(lastCell=”D4“)

效果展示：