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

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

话不多说直接上代码：

Report

java">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)) {//1Context context = new Context();for (Entry<String, Object> element : data.entrySet()) { // 2context.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

java">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

java">package com.example.jxls.model;import lombok.Data;public class UserTask {//用户idprivate String id;//用户名称private String userName;//任务Idprivate String taskId;//插件idprivate String  plugId;//班级idprivate 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

java">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

java">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();}}