Excel报表导出，复杂Excel模板导出（带单元格合并），jxls2

package com.test; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.math.BigDecimal; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import org.junit.Test; import org.jxls.builder.xls.XlsCommentAreaBuilder; import org.jxls.common.Context; import org.jxls.expression.JexlExpressionEvaluator; import org.jxls.transform.Transformer; import org.jxls.util.JxlsHelper; import com.rtp.component.jxls.JxlsTool; import com.rtp.component.jxls.MergeCommand; public class JxlsTest { @Test public void t1() { long start = System.currentTimeMillis(); XlsCommentAreaBuilder.addCommandMapping("merge", MergeCommand.class); try { Map<String, Object> data = getData(); InputStream is = new FileInputStream("D:/0A/jxls/model.xlsx");//模板文件放在resource下 OutputStream os = new FileOutputStream("D:/result.xlsx"); Context context = new Context(); if (data != null) { for (String key : data.keySet()) { context.putVar(key, data.get(key)); } } JxlsHelper jxlsHelper = JxlsHelper.getInstance(); // JxlsSuperHelper jxlsHelper = new JxlsSuperHelper(); Transformer transformer = jxlsHelper.createTransformer(is, os); JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator(); Map<String, Object> funcs = new HashMap<String, Object>(); funcs.put("tool", new JxlsTool()); //添加自定义功能 evaluator.getJexlEngine().setFunctions(funcs); jxlsHelper.processTemplate(context, transformer); // jxlsHelper.processTemplate(templateStream, targetStream, context); System.out.println(System.currentTimeMillis() - start); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } private Map<String, Object> getData() { Map<String, Object> data = new HashMap<>(); data.put("deptment", "某部门"); data.put("man", "黄某"); data.put("phone", "13826547852"); data.put("isC", "1"); data.put("isDp", "0"); data.put("mainDp", "A，某评级机构"); data.put("fullName", "某发行人"); data.put("createTime", "2015-01-02"); data.put("amt", "50亿"); data.put("addr", "某地"); data.put("situation", "情况良好"); data.put("about", "概况良好"); data.put("soo", "共享单车"); data.put("result1", "良好"); data.put("result2", "良好"); data.put("result3", "良好"); data.put("sug1", "某意见1"); data.put("sug2", "某意见2"); data.put("sign1", "李某"); data.put("sign2", "梅某"); data.put("sign3", "万某"); data.put("sign4", "张某"); this.setBisData(data); this.setBisMakeMoneyData(data); this.setAssets(data); this.setGtList(data); this.setCreditList(data); this.setDebtList(data); this.setCooperation(data); return data; } //主营业务收入 private void setBisData(Map<String, Object> data) { List<Map<String, Object>> bisList = new ArrayList<>(); Map<String, Object> bis1 = new HashMap<>(); bis1.put("name", "第一业务板块"); bis1.put("y2015", 123654); bis1.put("y2016", 452365); bis1.put("y2017", 478569); bis1.put("y2018", 854123); bisList.add(bis1); Map<String, Object> bis2 = new HashMap<>(); bis2.put("name", "第二业务板块"); bis2.put("y2015", 4256); bis2.put("y2016", 4896); bis2.put("y2017", 7856); bis2.put("y2018", 66552); bisList.add(bis2); data.put("bisList", bisList); } //主营业务毛利 private void setBisMakeMoneyData(Map<String, Object> data) { List<Map<String, Object>> bisAmtList = new ArrayList<>(); Map<String, Object> bis1 = new HashMap<>(); bis1.put("name", "第一业务板块"); bis1.put("y2015", 10000); bis1.put("y2016", 20000); bis1.put("y2017", 50000); bis1.put("y2018", 100000); bisAmtList.add(bis1); Map<String, Object> bis2 = new HashMap<>(); bis2.put("name", "第二业务板块"); bis2.put("y2015", 500); bis2.put("y2016", 600); bis2.put("y2017", 700); bis2.put("y2018", 8000); bisAmtList.add(bis2); Map<String, Object> bis3 = new HashMap<>(); bis3.put("name", "第三业务板块"); bis3.put("y2015", 500); bis3.put("y2016", 600); bis3.put("y2017", 700); bis3.put("y2018", 8000); bisAmtList.add(bis3); data.put("bisAmtList", bisAmtList); } //资产数据 private void setAssets(Map<String, Object> data) { Map<String, Object> totalAssets = new HashMap<>(); totalAssets.put("y2015", 0.20); totalAssets.put("y2016", 0.30); totalAssets.put("y2017", 0.40); totalAssets.put("y2018", 0.50); data.put("totalAssets", totalAssets); Map<String, Object> netAssets = new HashMap<>(); netAssets.put("y2015", 0.10); netAssets.put("y2016", 0.11); netAssets.put("y2017", 0.12); netAssets.put("y2018", 0.13); data.put("netAssets", netAssets); Map<String, Object> netProfit = new HashMap<>(); netProfit.put("y2015", 0.22); netProfit.put("y2016", 0.33); netProfit.put("y2017", 0.44); netProfit.put("y2018", 0.55); data.put("netProfit", netProfit); Map<String, Object> liabilityRatio = new HashMap<>(); liabilityRatio.put("y2015", 0.22); liabilityRatio.put("y2016", 0.33); liabilityRatio.put("y2017", 0.44); liabilityRatio.put("y2018", 0.55); data.put("liabilityRatio", liabilityRatio); Map<String, Object> guaranteeMultiple = new HashMap<>(); guaranteeMultiple.put("y2015", 0.55); guaranteeMultiple.put("y2016", 0.66); guaranteeMultiple.put("y2017", 0.77); guaranteeMultiple.put("y2018", 0.88); data.put("guaranteeMultiple", guaranteeMultiple); } //担保List private void setGtList(Map<String, Object> data) { List<Guarantee> guarantees = new ArrayList<>(); guarantees.add(new Guarantee("车", "张三", new BigDecimal(30), 20, "良好")); guarantees.add(new Guarantee("房", "李四", new BigDecimal(20), 30, "良好")); data.put("guarantees", guarantees); } //授信List private void setCreditList(Map<String, Object> data) { List<Credit> credits = new ArrayList<>(); credits.add(new Credit("部门一", new BigDecimal(10), new BigDecimal(10), new BigDecimal(10))); credits.add(new Credit("部门二", new BigDecimal(20), new BigDecimal(20), new BigDecimal(20))); credits.add(new Credit("部门三", new BigDecimal(30), new BigDecimal(30), new BigDecimal(30))); List<ComplexList> cls = new ArrayList<>(); cls.add(new ComplexList("张三", credits)); cls.add(new ComplexList("李四", credits)); data.put("credits", credits); data.put("cls", cls); } //发行人历年直接债务融资工具发行情况 private void setDebtList(Map<String, Object> data) { List<Debt> debts = new ArrayList<>(); debts.add(new Debt("A债券", new BigDecimal(100), new Date(), new Date())); debts.add(new Debt("B债券", new BigDecimal(295), new Date(), new Date())); debts.add(new Debt("C债券", new BigDecimal(295), new Date(), new Date())); debts.add(new Debt("D债券", new BigDecimal(295), new Date(), new Date())); /*for (int i = 0; i <= 100000; i++) { debts.add(new Debt("E债券", new BigDecimal(295), new Date(), new Date())); }*/ data.put("debts", debts); } private void setCooperation(Map<String, Object> data) { List<Cooperation> cooperations = new ArrayList<>(); cooperations.add(new Cooperation("名称一", new BigDecimal(1), "品种一", new BigDecimal(5), "评级一")); cooperations.add(new Cooperation("名称一", new BigDecimal(33), "品种一", new BigDecimal(5), "评级一")); cooperations.add(new Cooperation("名称二", new BigDecimal(2), "品种二", new BigDecimal(6), "评级二")); co