1、引入maven依赖：
<dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.8</version><scope>provided</scope></dependency><dependency><groupId>org.jxls</groupId><artifactId>jxls</artifactId><version>2.6.0</version></dependency><dependency><groupId>org.jxls</groupId><artifactId>jxls-poi</artifactId><version>1.2.0</version></dependency><dependency><groupId>mons</groupId><artifactId>commons-jexl</artifactId><version>2.1.1</version></dependency><dependency><groupId>org.jxls</groupId><artifactId>jxls-jexcel</artifactId><version>1.0.9</version><scope>compile</scope></dependency><dependency><groupId>mons</groupId><artifactId>commons-lang3</artifactId><version>3.8.1</version></dependency></dependencies>
2、生成excel的工具类：
```javaimport org.jxls.area.Area;import mand.AbstractCommand;import mand.CellRefGenerator;import mand;import mand.SheetNameGenerator;import mon.CellRef;import mon.Context;import mon.JxlsException;import mon.Size;import org.jxls.expression.JexlExpressionEvaluator;import org.jxls.util.Util;import java.util.Collection;import java.util.List;/*** 扩展jxls each命令* 增加retainEmpty属性，当items为null或size为0时，也保留当前一行数据的格式* 循环增加下标变量“var_index”。如var="item"，获取下标方法：${item_index}*/public class EachCommand extends AbstractCommand {public enum Direction {RIGHT, DOWN}private String var;private String items;private String select;private Area area;private Direction direction = Direction.DOWN;private CellRefGenerator cellRefGenerator;private String multisheet;private String retainEmpty; //当集合大小为0时，是否最少保留一行空行数据public EachCommand() {}/*** @param var name of the key in the context to contain each collection items during iteration* @param itemsname of the collection bean in the context* @param direction defines processing by rows (DOWN - default) or columns (RIGHT)*/public EachCommand(String var, String items, Direction direction) {this.var = var;this.items = items;this.direction = direction == null ? Direction.DOWN : direction;}public EachCommand(String var, String items, Area area) {this(var, items, area, Direction.DOWN);}public EachCommand(String var, String items, Area area, Direction direction) {this(var, items, direction);if (area != null) {this.area = area;addArea(this.area);}}/*** @param var name of the key in the context to contain each collection items during iteration* @param items name of the collection bean in the context* @param area body area for this command* @param cellRefGenerator generates target cell ref for each collection item during iteration*/public EachCommand(String var, String items, Area area, CellRefGenerator cellRefGenerator) {this(var, items, area, (Direction) null);this.cellRefGenerator = cellRefGenerator;}/*** Gets iteration directino** @return current direction for iteration*/public Direction getDirection() {return direction;}/*** Sets iteration direction** @param direction*/public void setDirection(Direction direction) {this.direction = direction;}public void setDirection(String direction) {this.direction = Direction.valueOf(direction);}/*** Gets defined cell ref generator** @return current {@link CellRefGenerator} instance or null*/public CellRefGenerator getCellRefGenerator() {return cellRefGenerator;}public void setCellRefGenerator(CellRefGenerator cellRefGenerator) {this.cellRefGenerator = cellRefGenerator;}@Overridepublic String getName() {return "each";}/*** Gets current variable name for collection item in the context during iteration** @return collection item key name in the context*/public String getVar() {return var;}/*** Sets current variable name for collection item in the context during iteration** @param var*/public void setVar(String var) {this.var = var;}/*** Gets collection bean name** @return collection bean name in the context*/public String getItems() {return items;}/*** Sets collection bean name** @param items collection bean name in the context*/public void setItems(String items) {this.items = items;}/*** Gets current 'select' expression for filtering out collection items** @return current 'select' expression or null if undefined*/public String getSelect() {return select;}/*** Sets current 'select' expression for filtering collection** @param select filtering expression*/public void setSelect(String select) {this.select = select;}/*** @return Context variable name holding a list of Excel sheet names to output the collection to*/public String getMultisheet() {return multisheet;}/*** Sets name of context variable holding a list of Excel sheet names to output the collection to* @param multisheet*/public void setMultisheet(String multisheet) {this.multisheet = multisheet;}@Overridepublic Command addArea(Area area) {if (area == null) {return this;}if (super.getAreaList().size() >= 1) {throw new IllegalArgumentException("You can add only a single area to 'each' command");}this.area = area;return super.addArea(area);}@Override@SuppressWarnings("rawtypes")public Size applyAt(CellRef cellRef, Context context) {Collection itemsCollection = Util.transformToCollectionObject(getTransformationConfig().getExpressionEvaluator(), items, context);int width = 0;int height = 0;int index = 0;CellRefGenerator cellRefGenerator = this.cellRefGenerator;if (cellRefGenerator == null && multisheet != null) {List<String> sheetNameList = extractSheetNameList(context);cellRefGenerator = new SheetNameGenerator(sheetNameList, cellRef);}CellRef currentCell = cellRefGenerator != null ? cellRefGenerator.generateCellRef(index, context) : cellRef;JexlExpressionEvaluator selectEvaluator = null;if (select != null) {selectEvaluator = new JexlExpressionEvaluator(select);}for (Object obj : itemsCollection) {context.putVar(var, obj);context.putVar(var+"_index", index);if (selectEvaluator != null && !Util.isConditionTrue(selectEvaluator, context)) {context.removeVar(var);context.removeVar(var+"_index");continue;}Size size = area.applyAt(currentCell, context);index++;if (cellRefGenerator != null) {width = Math.max(width, size.getWidth());height = Math.max(height, size.getHeight());if(index < itemsCollection.size()) {currentCell = cellRefGenerator.generateCellRef(index, context);}} else if (direction == Direction.DOWN) {currentCell = new CellRef(currentCell.getSheetName(), currentCell.getRow() + size.getHeight(), currentCell.getCol());width = Math.max(width, size.getWidth());height += size.getHeight();} else {currentCell = new CellRef(currentCell.getSheetName(), currentCell.getRow(), currentCell.getCol() + size.getWidth());width += size.getWidth();height = Math.max(height, size.getHeight());}context.removeVar(var);context.removeVar(var+"_index");}if("true".equalsIgnoreCase(retainEmpty) && width == 0 && height == 0){return area.applyAt(currentCell, context);}return new Size(width, height);}@SuppressWarnings("unchecked")private List<String> extractSheetNameList(Context context) {try {return (List<String>) context.getVar(multisheet);} catch (Exception e) {throw new JxlsException("Failed to get sheet names from " + multisheet, e);}}public String getRetainEmpty() {return retainEmpty;}public void setRetainEmpty(String retainEmpty) {this.retainEmpty = retainEmpty;}}
```java```javaimport jxl.write.WriteException;import lombok.Data;import mons.lang3.StringUtils;import mons.lang3.math.NumberUtils;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.jxls.area.Area;import mand.AbstractCommand;import mand;import mon.CellRef;import mon.Context;import mon.Size;import org.jxls.transform.Transformer;import org.jxls.transform.jexcel.JexcelTransformer;import org.jxls.transform.poi.PoiTransformer;/*** @Author: David.Huang* @Date: /12/20 0020 15:04* 合并单元格命令*/@Datapublic class MergeCommand extends AbstractCommand {/*** 合并的列数*/private String cols;/*** 合并的行数*/private String rows;private Area area;/*** 单元格的样式*/private CellStyle cellStyle;@Overridepublic String getName() {return "merge";}@Overridepublic Command addArea(Area area) {if (super.getAreaList().size() >= 1) {throw new IllegalArgumentException("You can add only a single area to 'merge' command");}this.area = area;return super.addArea(area);}@Overridepublic Size applyAt(CellRef cellRef, Context context) {int rows = 1, cols = 1;if (StringUtils.isNotBlank(this.rows)) {Object rowsObj = getTransformationConfig().getExpressionEvaluator().evaluate(this.rows, context.toMap());if (rowsObj != null && NumberUtils.isDigits(rowsObj.toString())) {rows = NumberUtils.toInt(rowsObj.toString());}}if (StringUtils.isNotBlank(this.cols)) {Object colsObj = getTransformationConfig().getExpressionEvaluator().evaluate(this.cols, context.toMap());if (colsObj != null && NumberUtils.isDigits(colsObj.toString())) {cols = NumberUtils.toInt(colsObj.toString());}}if (rows > 1 || cols > 1) {Transformer transformer = this.getTransformer();if (transformer instanceof PoiTransformer) {return poiMerge(cellRef, context, (PoiTransformer) transformer, rows, cols);} else if (transformer instanceof JexcelTransformer) {return jexcelMerge(cellRef, context, (JexcelTransformer) transformer, rows, cols);}}area.applyAt(cellRef, context);return new Size(1, 1);}protected Size poiMerge(CellRef cellRef, Context context, PoiTransformer transformer, int rows, int cols) {Sheet sheet = transformer.getWorkbook().getSheet(cellRef.getSheetName());CellRangeAddress region = new CellRangeAddress(cellRef.getRow(),cellRef.getRow() + rows - 1,cellRef.getCol(),cellRef.getCol() + cols - 1);sheet.addMergedRegion(region);area.applyAt(cellRef, context);if (cellStyle == null) {setPoiCellStyle(transformer);}setRegionStyle(cellStyle, region, sheet);return new Size(cols, rows);}protected Size jexcelMerge(CellRef cellRef, Context context, JexcelTransformer transformer, int rows, int cols) {try {transformer.getWritableWorkbook().getSheet(cellRef.getSheetName()).mergeCells(cellRef.getRow(),cellRef.getCol(),cellRef.getRow() + rows - 1,cellRef.getCol() + cols - 1);area.applyAt(cellRef, context);} catch (WriteException e) {throw new IllegalArgumentException("合并单元格失败");}return new Size(cols, rows);}public static void setRegionStyle(CellStyle cs, CellRangeAddress region, Sheet sheet) {for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {Row row = sheet.getRow(i);if (row == null) {row = sheet.createRow(i);}for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {Cell cell = row.getCell(j);if (cell == null) {cell = row.createCell(j);}cell.setCellStyle(cs);}}}/*** 设置单元格格式* 根据自己需求修改* @param transformer* @return*/private void setPoiCellStyle(PoiTransformer transformer) {cellStyle = transformer.getWorkbook().createCellStyle();cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);}}
```javaimport jxl.write.WriteException;import lombok.Data;import mons.lang3.StringUtils;import mons.lang3.math.NumberUtils;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import org.jxls.area.Area;import mand.AbstractCommand;import mand;import mon.CellRef;import mon.Context;import mon.Size;import org.jxls.transform.Transformer;import org.jxls.transform.jexcel.JexcelTransformer;import org.jxls.transform.poi.PoiCellData;import org.jxls.transform.poi.PoiTransformer;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;/*** @Author: David.Huang* @Date: /11/18 15:06* <p>* 根据范围合并单元格，合并区间不能交叉* 例如：rang="1:3,5:7"即1到3合并，5到7合并*/@Datapublic class MergeCommand2 extends AbstractCommand {/*** 行合并的范围*/private String rowRange;/*** 列合并的范围*/private String cols;private Area area;/*** 单元格的样式*/private CellStyle cellStyle;@Overridepublic String getName() {return "mg";}@Overridepublic Command addArea(Area area) {if (super.getAreaList().size() >= 1) {throw new IllegalArgumentException("You can add only a single area to 'merge' command");}this.area = area;return super.addArea(area);}@Overridepublic Size applyAt(CellRef cellRef, Context context) {String[] rangeArray = rowRange.split(",");Map<Integer, Integer> map = new HashMap<>();List<Integer> startRowList = new ArrayList<>();for (String r : rangeArray) {map.put(Integer.valueOf(r.split(":")[0]), Integer.valueOf(r.split(":")[1]));startRowList.add(Integer.valueOf(r.split(":")[0]));}int cols = 1;if (StringUtils.isNotBlank(this.cols)) {Object colsObj = getTransformationConfig().getExpressionEvaluator().evaluate(this.cols, context.toMap());if (colsObj != null && NumberUtils.isDigits(colsObj.toString())) {cols = NumberUtils.toInt(colsObj.toString());}}Transformer transformer = getTransformer();if (transformer instanceof PoiTransformer) {if (cellStyle == null) {PoiCellData cellData = (PoiCellData) transformer.getCellData(cellRef);cellStyle = cellData.getCellStyle();}if (startRowList.contains(cellRef.getRow())) {return poiMerge(cellRef, context, (PoiTransformer) transformer, cellRef.getRow(), map.get(cellRef.getRow()), cols);}} else if (transformer instanceof JexcelTransformer) {return jexcelMerge(cellRef, context, (JexcelTransformer) transformer, cellRef.getRow(), map.get(cellRef.getRow()), cols);}area.applyAt(cellRef, context);return new Size(1, 1);}protected Size poiMerge(CellRef cellRef, Context context, PoiTransformer transformer, int firstRow, int lastRow, int cols) {if (cellRef.getRow() == firstRow) {Sheet sheet = transformer.getWorkbook().getSheet(cellRef.getSheetName());CellRangeAddress region = new CellRangeAddress(firstRow - 1,lastRow - 1,cellRef.getCol(),cellRef.getCol() + cols - 1);sheet.addMergedRegion(region);area.applyAt(cellRef, context);MergeCommand.setRegionStyle(cellStyle, region, sheet);}return new Size(1, 1);}protected Size jexcelMerge(CellRef cellRef, Context context, JexcelTransformer transformer, int firstRow, int lastRow, int cols) {try {transformer.getWritableWorkbook().getSheet(cellRef.getSheetName()).mergeCells(firstRow - 1,cellRef.getCol(),lastRow - 1,cellRef.getCol() + cols - 1);area.applyAt(cellRef, context);} catch (WriteException e) {throw new IllegalArgumentException("合并单元格失败");}return new Size(1, 1);}}
import com.mand.MergeCommand;import com.mand.MergeCommand2;import org.jxls.builder.xls.XlsCommentAreaBuilder;import mand.EachCommand;import mon.Context;import org.jxls.expression.JexlExpressionEvaluator;import org.jxls.transform.Transformer;import org.jxls.transform.poi.PoiTransformer;import org.jxls.util.JxlsHelper;import java.io.*;import java.util.HashMap;import java.util.Map;public class JxlsUtils {static {XlsCommentAreaBuilder.addCommandMapping("each", EachCommand.class);XlsCommentAreaBuilder.addCommandMapping("merge", MergeCommand.class);XlsCommentAreaBuilder.addCommandMapping("mg", MergeCommand2.class);}public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model)throws IOException {Context context = PoiTransformer.createInitialContext();if (model != null) {for (String key : model.keySet()) {context.putVar(key, model.get(key));}}JxlsHelper jxlsHelper = JxlsHelper.getInstance();Transformer transformer = jxlsHelper.createTransformer(is, os);//获得配置JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();//设置静默模式，不报警告//evaluator.getJexlEngine().setSilent(true);//函数强制，自定义功能Map<String, Object> funcs = new HashMap<String, Object>();funcs.put("utils", new JxlsUtils()); //添加自定义功能evaluator.getJexlEngine();//.setFunctions(funcs);//必须要这个，否者表格函数统计会错乱jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);}public static void exportExcel(File xls, File out, Map<String, Object> model)throws FileNotFoundException, IOException {exportExcel(new FileInputStream(xls), new FileOutputStream(out), model);}public static void exportExcel(String templatePath, OutputStream os, Map<String, Object> model)throws Exception {File template = getTemplate(templatePath);if (template != null) {exportExcel(new FileInputStream(template), os, model);} else {throw new Exception("Excel 模板未找到。");}}/*** 获取jxls模版文件** @param path* @return*/public static File getTemplate(String path) {File template = new File(path);if (template.exists()) {return template;}return null;}}
3、测试
```java```javaimport lombok.Data;/*** @Author: David.Huang* @Date: /12/20 0020 15:05*/@Datapublic class Student {private String name;private String sex;private String age;private String grade;public Student(String name, String sex, String age, String grade) {this.name = name;this.sex = sex;this.age = age;this.grade = grade;}}
```java```javaimport lombok.Data;import java.util.ArrayList;import java.util.List;/*** @Author: David.Huang* @Date: /12/20 0020 16:25*/@Datapublic class Grade {private String name;private List<Student> studentList = new ArrayList<>();}
测试第一种合并方式：
模板如下：
测试代码：
```javaimport com.hyf.demo.entity.Grade;import com.hyf.demo.entity.Student;import com.hyf.demo.until.JxlsUtils;import java.io.FileOutputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;/*** @Author: David.Huang* @Date: /12/20 0020 15:04*/public class MergeTest1 {public static void main(String[] args) {//模拟10条学生信息Grade grade1 = new Grade();grade1.setName("1年级");grade1.getStudentList().add(new Student("小明", "男", "9", "1年级"));grade1.getStudentList().add(new Student("小天", "男", "9", "1年级"));grade1.getStudentList().add(new Student("小妮", "女", "10", "1年级"));grade1.getStudentList().add(new Student("小友", "女", "10", "1年级"));grade1.getStudentList().add(new Student("小空", "男", "10", "1年级"));Grade grade2 = new Grade();grade2.setName("2年级");grade2.getStudentList().add(new Student("小马", "男", "11", "2年级"));grade2.getStudentList().add(new Student("小易", "女", "11", "2年级"));grade2.getStudentList().add(new Student("小启", "女", "11", "2年级"));grade2.getStudentList().add(new Student("小曲", "女", "12", "2年级"));grade2.getStudentList().add(new Student("小浪", "男", "12", "2年级"));List<Grade> gradeList = new ArrayList<>();gradeList.add(grade1);gradeList.add(grade2);Map<String, Object> model = new HashMap<>(10);model.put("gradeList", gradeList);String template_path = "D:\\excel_test/student1.xlsx";String target_path = "D:\\test/student1.xlsx";try {JxlsUtils.exportExcel(template_path, new FileOutputStream(target_path), model);} catch (Exception e) {e.printStackTrace();}}}
导出效果：
测试第二种合并方式：
模板：
测试代码：
import com.hyf.demo.entity.Student;import com.hyf.demo.until.JxlsUtils;import java.io.FileOutputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;/*** @Author: David.Huang* @Date: /12/20 0020 15:04*/public class MergeTest2 {public static void main(String[] args) {//模拟10条学生信息List<Student> list = new ArrayList<>();list.add(new Student("小明", "男", "9", "四年级"));list.add(new Student("小天", "男", "9", "四年级"));list.add(new Student("小妮", "女", "10", "四年级"));list.add(new Student("小友", "女", "10", "四年级"));list.add(new Student("小空", "男", "10", "四年级"));list.add(new Student("小马", "男", "11", "五年级"));list.add(new Student("小易", "女", "11", "五年级"));list.add(new Student("小启", "女", "11", "五年级"));list.add(new Student("小曲", "女", "12", "五年级"));list.add(new Student("小浪", "男", "12", "五年级"));Map<String, Object> model = new HashMap<>(10);model.put("list", list);String template_path = "D:\\excel_test/student2.xlsx";String target_path = "D:\\test/student2.xlsx";try {JxlsUtils.exportExcel(template_path, new FileOutputStream(target_path), model);} catch (Exception e) {e.printStackTrace();}}}
导出效果是一样的：
模板地址：添加链接描述
添加链接描述
