使用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 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 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 getAllClients() {
		
		List 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 employees = generateSampleEmployeeData();
		Map 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 generateSampleEmployeeData() throws ParseException {
		List employees = new ArrayList();
		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“)

效果展示：