精华内容
下载资源
问答
  • <groupId>org.jxls <artifactId>jxls <version>2.6.0 <groupId>org.jxls <artifactId>jxls-poi <version>1.2.0 <groupId>org.apache.commons <artifactId>commons-jexl <version>2.1.1 ...

    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>org.apache.commons</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>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.8.1</version>
        </dependency>
    </dependencies>
    

    2、生成excel的工具类:

    
    ```java
    import org.jxls.area.Area;
    import org.jxls.command.AbstractCommand;
    import org.jxls.command.CellRefGenerator;
    import org.jxls.command.Command;
    import org.jxls.command.SheetNameGenerator;
    import org.jxls.common.CellRef;
    import org.jxls.common.Context;
    import org.jxls.common.JxlsException;
    import org.jxls.common.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 items     name 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;
        }
    
        @Override
        public 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;
        }
    
        @Override
        public 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
    
    ```java
    import jxl.write.WriteException;
    import lombok.Data;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.commons.lang3.math.NumberUtils;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.jxls.area.Area;
    import org.jxls.command.AbstractCommand;
    import org.jxls.command.Command;
    import org.jxls.common.CellRef;
    import org.jxls.common.Context;
    import org.jxls.common.Size;
    import org.jxls.transform.Transformer;
    import org.jxls.transform.jexcel.JexcelTransformer;
    import org.jxls.transform.poi.PoiTransformer;
    
    /**
     * @Author: David.Huang
     * @Date: 2019/12/20 0020 15:04
     * 合并单元格命令
     */
    @Data
    public class MergeCommand extends AbstractCommand {
        /**
         * 合并的列数
         */
        private String cols;
        /**
         * 合并的行数
         */
        private String rows;
        private Area area;
        /**
         * 单元格的样式
         */
        private CellStyle cellStyle;
    
        @Override
        public String getName() {
            return "merge";
        }
    
        @Override
        public 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);
        }
    
        @Override
        public 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);
        }
    
    }
    
    
    
    ```java
    import jxl.write.WriteException;
    import lombok.Data;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.commons.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 org.jxls.command.AbstractCommand;
    import org.jxls.command.Command;
    import org.jxls.common.CellRef;
    import org.jxls.common.Context;
    import org.jxls.common.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: 2019/11/18  15:06
     * <p>
     * 根据范围合并单元格,合并区间不能交叉
     * 例如:rang="1:3,5:7"即1到3合并,5到7合并
     */
    @Data
    public class MergeCommand2 extends AbstractCommand {
        /**
         * 行合并的范围
         */
        private String rowRange;
        /**
         * 列合并的范围
         */
        private String cols;
        private Area area;
        /**
         * 单元格的样式
         */
        private CellStyle cellStyle;
    
    
        @Override
        public String getName() {
            return "mg";
        }
    
        @Override
        public 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);
        }
    
        @Override
        public 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.hyf.demo.command.MergeCommand;
    import com.hyf.demo.command.MergeCommand2;
    import org.jxls.builder.xls.XlsCommentAreaBuilder;
    import org.jxls.command.EachCommand;
    import org.jxls.common.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
    
    ```java
    import lombok.Data;
    
    /**
     * @Author: David.Huang
     * @Date: 2019/12/20 0020 15:05
     */
    @Data
    public 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
    
    ```java
    import lombok.Data;
    
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * @Author: David.Huang
     * @Date: 2019/12/20 0020 16:25
     */
    @Data
    public class Grade {
    
        private String name;
        private List<Student> studentList = new ArrayList<>();
    }
    
    
    

    测试第一种合并方式:
    模板如下:
    在这里插入图片描述
    测试代码:

    
    ```java
    import 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: 2019/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: 2019/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();
            }
        }
    }
    

    导出效果是一样的:

    在这里插入图片描述

    模板地址:添加链接描述
    添加链接描述

    展开全文
  • 可以看到代码中指定合并的部分现在已经显示出了效果。 模板 [java] view plain copy List supplyAreaList = saBiz.getSupplyAreaById(supplyAreaId);   SupplyArea sa = ...

    原文地址:http://blog.csdn.net/hu_shengyang/article/details/6736816

    可以看到代码中指定合并的部分现在已经显示出了效果。

    模板

    1. List supplyAreaList = saBiz.getSupplyAreaById(supplyAreaId);  
    2.             SupplyArea sa = (SupplyArea) supplyAreaList.get(0);  
    3.             Long id = sa.getSupplyAreaId();  
    4.             List qcList = new ArrayList();  
    5.             QueryCondition idObj = new QueryCondition();  
    6.             idObj.setFieldName("supplyAreaId");  
    7.             idObj.setQueryOperator(QueryOperator.le);  
    8.             idObj.setValue(id);  
    9.             qcList.add(idObj);  
    10.             List recordsList = saBiz.getRecords(qcList, new SupplyArea());  
    11.             int i = recordsList.size();  
    12.             String templateDir = "D:/excel/template/SupplyAreaDynaMergeCells.xls";  
    13.             String targetDir="D:/excel/export/testDynaMergeCells.xls";        
    14.             InputStream is = new FileInputStream(templateDir);  
    15.             Map beans = new HashMap();  
    16.             beans.put("suplyArea", recordsList);  
    17.             //关联模板   
    18.             XLSTransformer transformer = new XLSTransformer();  
    19.             HSSFWorkbook workBook = transformer.transformXLS(is, beans);  
    20.             HSSFSheet sheet = workBook.getSheetAt(0);  
    21.             sheet.addMergedRegion(new Region(2,(short)0,2+i,(short)0));  
    22.             sheet.addMergedRegion(new Region(2,(short)2,2+i,(short)2));  
    23.             OutputStream os = new FileOutputStream(targetDir);  
    24.             workBook.write(os);  
    25.                 is.close();  
    26.                 os.flush();  
    27. os.close();  


    导出结果


    展开全文
  • 文档 :http://jxls.sourceforge.net/ ... var is a name of the variable in Jxls context to put each new collection item when iterating items is a name of a context variable containing the collection

    文档 :http://jxls.sourceforge.net/

    源码:https://github.com/jxlsteam/jxls

    基本使用方法

     

    <dependency>
        <groupId>org.jxls</groupId>
        <artifactId>jxls-poi</artifactId>
        <version>2.9.0</version>
    </dependency>
    
    public void test() throws ParseException, IOException {
        logger.info("Running Formula Copy demo");
        List<Org> orgs = Org.generate(3, 3);
        try(InputStream is = Demo.class.getResourceAsStream("formula_copy_template.xls")) {
            try (OutputStream os = new FileOutputStream("target/formula_copy_output.xls")) {
                Context context = new Context();
                context.putVar("orgs", orgs);
                JxlsHelper jxlsHelper = JxlsHelper.getInstance();
                jxlsHelper.setUseFastFormulaProcessor(false);
                jxlsHelper.processTemplate(is, os, context);
            }
        }
    }

    基本语法

     

    Each-Command

    • var is a name of the variable in Jxls context to put each new collection item when iterating

    • items is a name of a context variable containing the collection (Iterable<?>) or array to iterate

    • area is a reference to XLS Area used as each command body

    • direction is a value of Direction enumeration which may have values DOWN or RIGHT to indicate how to repeat the command body - by rows or by columns. The default value is DOWN.

    • select is an expression selector to filter out collection items during the iteration

    • groupBy is a property to do the grouping

    • groupOrder indicates ordering for groups (‘desc’ or ‘asc’)

    • orderBy contains the names separated with comma and each with an optional postfix “ ASC” (default) or “ DESC” for the sort order

    • cellRefGenerator is a custom strategy for target cell references creation

    • multisheet is a name of a context variable containing a list of sheet names to output the collection

    • lastCell is a common attribute for any command pointing to the last cell of the command area

       

    If-Command

    • condition is a conditional expression to test

    • ifArea is a reference to an area to output when this command condition evaluates to true

    • elseArea is a reference to an area to output when this command condition evaluates to false

    • lastCell is a common attribute for any command pointing to the last cell of the command area

    eg.

    jx:if(condition="employee.payment <= 2000", lastCell="F9", areas=["A9:F9","A18:F18"])

     

     

    Grid-Command

    可以自定义各列单元格格式

    Grid-Command has the following attributes

    • headers - name of a context variable containing a collection of headers (Collection<Object>)

    • data - name of a context variable containing a collection of data (Collection<Collection<Object>>)

    • props - comma separated list of object properties for each grid row (required only if each grid row is an Object)

    • formatCells - comma-separated list of type-format map cells e.g. formatCells=“Double:E1, Date:F1”

    • headerArea - source xls area for headers

    • bodyArea - source xls area for body

    • lastCell is a common attribute for any command pointing to the last cell of the command area

     

    ${header}
    ${cell}

     

    Image-Command

    eg.

    InputStream imageInputStream = ImageDemo.class.getResourceAsStream("business.png");
    byte[] imageBytes = Util.toByteArray(imageInputStream);
    context.putVar("image", imageBytes);
    jx:image(lastCell="D10" src="image" imageType="PNG")

     

    MergeCells-Command

    jx:mergeCells(
    lastCell="Merge cell ranges"
    [, cols="Number of columns combined"]
    [, rows="Number of rows combined"]
    [, minCols="Minimum number of columns to merge"]
    [, minRows="Minimum number of rows to merge"]
    )

    其他

     

    Multiple sheets

    jx:each 命令中添加属性 multisheet 

    eg.

    jx:each(items="departments", var="dep", multisheet="sheetnames", lastCell="D4")

    Excel Formulas

    • 公式默认值

    jx:params(defaultValue="1")
    • 拷贝公式

    jx:params(formulaStrategy="BY_COLUMN")
    • Joined cell (同一行对应多个模板行时)

    $[SUM(U_(D9,D18))]

     

    Custom Function

    JxlsHelper jxlsHelper = JxlsHelper.getInstance();
    Transformer transformer = jxlsHelper.createTransformer(is, os);
    JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();
    Map<String, Object> functionMap = new HashMap<>();
    functionMap.put("fn", new MyCustomFunctions());
    JexlEngine customJexlEngine = new JexlBuilder().namespaces(functionMap).create();
    evaluator.setJexlEngine(customJexlEngine);
    jxlsHelper.processTemplate(context, transformer);
    ​

     

    public static class MyCustomFunctions {
        public Object ifelse(boolean b, Object o1, Object o2) {
            return b ? o1 : o2;
        }
    ​
        public boolean contains(Collection o1, Object o2) {
            return o1.contains(o2);
        }
    ​
        public String concat(Object o1, Object o2) {
            return String.valueOf(o1).concat(String.valueOf(o2));
        }
    }
    ${fn:ifelse(fn:contains(data.ext,"prevQyoy"),"上季度增速","上年度增速")}

     

    UpdateCell-Command

    见官方文档

    Custom Commands

    见官方文档

    示例

     

    Report template

     

    Excel output

     

    展开全文
  • 概述 excel比较常用的工具类就是poi和jxl当然后者已经停止维护很久而且只支持excel...简单格式的数据导出jxl就够用了,但是复杂格式的数据则用excel模板较为方便 <dependency> <groupId>net.sf....

    概述

    • excel比较常用的工具类就是poi和jxl当然后者已经停止维护很久而且只支持excel2003,不支持excel2007. 对2003版,最大行数是65536行 ,对2007以上版本,最大行数是1048576行.简单格式的数据导出jxl就够用了,但是复杂格式的数据则用excel模板较为方便
      
      <dependency>
          <groupId>net.sf.jxls</groupId>
          <artifactId>jxls-core</artifactId>
          <version>1.0.6</version>
      </dependency>
      后者也已经停止维护,最新的包是2014年的,不过相对于来说我更喜欢用后者,更方便,
      相对于前者不用配置作用域,包括循环等.前者是用注释的方式实现,我尝试使用的时候
      发现行中间的合并其不能做到(也许是我用的不对,总之就那样了),但是直接用代码操作
      合并单元格又过于繁琐,这也是当前开发的背景.
      同事使用时候遇到合并单元格的问题,合并的单元格不能解析数据也不能正确的合并
      如我下图的模板,合并了3行则生成出来的excel也只合并了3行,并且根据循环的数据量
      往下错位若干格,而且少一个边框,究其原因,原作者根本没处理循环内的合并单元格问题
      ,故此对1.0.6做了开发,修复这个问题,并将版本命名为2.0.传递到maven私服,pom为
      <dependency>
          <groupId>net.sf.jxls</groupId>
          <artifactId>jxls-core</artifactId>
          <version>2.0</version>
      </dependency>
    • 正文

    测试类如下

    import com.fasterxml.jackson.databind.exc.InvalidFormatException;
    import net.sf.jxls.exception.ParsePropertyException;
    import net.sf.jxls.transformer.XLSTransformer;
    import org.junit.Test;
    
    import java.io.BufferedInputStream;
    import java.io.BufferedOutputStream;
    import java.io.File;
    import java.io.IOException;
    import java.net.URLEncoder;
    import java.text.SimpleDateFormat;
    import java.util.*;
    
    /**
     * Created by admin on 2019/2/15.
     */
    public class JxlsExcelTest {
    
        public static void excelTest() throws IOException,
     org.apache.poi.openxml4j.exceptions.InvalidFormatException {
            //获得模版
            String tempFileName = Thread.currentThread().getContextClassLoader()
    .getResource("model/excelTemplate.xls").getFile();
            //将结果放入这个list中
            Date date = new Date();
            SimpleDateFormat simpl = new SimpleDateFormat("yyyyMMddHHmmss");
            String currntTime = simpl.format(date);
    
            //导出列表名
            String fileName = currntTime+"列表.xls";
            Map dataMap = new HashMap();
    
            List persionTypeList = new ArrayList();
            Map map1 = new HashMap();
            map1.put("personType", "初级");
            persionTypeList.add(map1);
    
            Map map2 = new HashMap();
            map2.put("personType", "中级");
            persionTypeList.add(map2);
    
            Map map3 = new HashMap();
            map3.put("personType", "高级");
            persionTypeList.add(map3);
    
            Map map4 = new HashMap();
            map4.put("personType", "高1级");
            persionTypeList.add(map4);
    
            dataMap.put("listData1", persionTypeList);
    
            List<Map<String,Object>> persionTypeList2=new 
    ArrayList<Map<String,Object>>();
            persionTypeList2.addAll(persionTypeList);
            persionTypeList2.remove(2);
            dataMap.put("listData2",persionTypeList2);
    
            List<Map<String,Object>> persionTypeList3=new 
    ArrayList<Map<String,Object>>();
            persionTypeList3.addAll(persionTypeList2);
            persionTypeList3.addAll(persionTypeList2);
            dataMap.put("listData3",persionTypeList3);
    
            dataMap.put("sum", "2012");
            dataMap.put("sum1", "2013");
            dataMap.put("sum2", "2014");
            dataMap.put("sum3", "2015");
            dataMap.put("sum4", "2016");
            dataMap.put("sum5", "2017");
    
            //生成的导出文件
            File destFile =new File("yyglxt/src/main/resources/model/"+fileName);
    
            //transformer转到Excel
            XLSTransformer transformer = new XLSTransformer();
    
            BufferedInputStream bis = null;
            BufferedOutputStream bos = null;
            try {
                System.out.println(destFile.getAbsolutePath());
                //将数据添加到模版中生成新的文件
                transformer.transformXLS(tempFileName, dataMap,
     destFile.getAbsolutePath());
            } catch (ParsePropertyException e) {
                e.printStackTrace();
            } catch (InvalidFormatException e) {
                e.printStackTrace();
            }finally {
                //使用完成后关闭流
                try {
                    if (bis != null)
                        bis.close();
                    if (bos != null)
                        bos.close();
                } catch (IOException e) {
                }
    
            }
        }
        public static void main(String[] args) throws Exception {
            excelTest();
        }
    }
    • excel模板如下

    • 最终生成的结果

    • 结束语

    目前的模式支持左侧,中间,右侧任意合并单元格,合并的是整个循环,如果循环是复杂数据结构,中间合并了少量单元格的话目前是不支持的

    如果发现有什么bug或者有需求不满足也可以给我留言,有时间的话我会尽量处理

    未经授权禁止转载,如果转载需要注明出处

    • 下载地址:

        jar包:jar包里已经包含了pom.xml,可以自行解压方便上传到maven私服,这种方式会带上依赖,否则要一个个手动添加依赖.pom在jar包里jxls-core\2.0\jxls-core-2.0\META-INF\maven\pom.xml,不过我也单独上传了一份,这个pom文件不需要下载,当然分多的豪如果不吝赞助,那就道一声多谢!

    如果现在不能下载,那是因为附件尚未过审核,需要等明天审核完毕了.

    上传maven私服的时候,可以直接上传以下的pom文件和jar包,这样的话不用自行添加依赖,否则依照pom

    文件人工添加依赖

    jar下载地址:   https://download.csdn.net/download/a1091662876/10965456

    pom下载地址(无须下载,jar里已经存在):https://download.csdn.net/download/a1091662876/10965540

     

     

    展开全文
  • 一个Maven项目,下载直接使用,直接找到 JxlsTest运行demo即可,功能强大,基本能满足所有报表的导出需求,支持多个Sheet导出,支持复杂的导出组合,可以自己写模板试玩下。
  • java将数据导出,带有合并单元格excel--jxls技术

    万次阅读 热门讨论 2017-03-09 11:40:04
    说起用这个还是比较有意思的,当时项目有个导出表格的功能,但是没能合并单元格,客户不是很满意,当时项目中大家都说弄不了,我想着自己网上查查吧,就查到了这个,试了一下午完成了,很有成就感哪,哈哈。...
  • 一.JXLS简介 ,安装,以及标签使用说明 Each数据循环、公式使用、if-else逻辑判断、加载图片、动态表格、数据分组、单元格超链接、SQL模板实现、自定义表达式解析引擎、自定义函数、单元格合并
  • java中成熟的excel导出工具有pol、jxl,但他们都是使用java代码的方式来导出excel,编码效率很低且不方便维护。 还可以使用一些工具很轻松的实现模板导出。这些工具现在还在维护,而且做得比较好的国内的有easyPOI...
  • jxls 使用模板文件导出生成excel

    千次阅读 2013-08-07 16:56:15
    jxls采用基于excel模板的方式来导出excel,支持 支持写sql语句导数据 简单属性访问 强大的表达式 报表导出 报表标签使用 列隐藏 动态样式 jdbc resultset导出 在一个单元格上使用多个bean的属性 合并单元格 动态分组 ...
  • student2.xlsx

    2019-12-20 17:31:11
    本人博客,jxls导出excel合并单元格的两种方式,这是合并单元格第二种方式模板 本人博客,jxls导出excel合并单元格的两种方式,这是合并单元格第二种方式模板 本人博客,jxls导出excel合并单元格的两种方式...
  •  在项目中借助POI 和 JXLS 两个开源工具jar实现excel数据导出,原有使用POI-&gt;HSSF方式进行数据导出,随着导出数据量的增大远远超出单sheet 65535条上限,将导出方式由POI-&gt;HSSF升级为POI-&gt;...
  • student1.xlsx

    2019-12-20 17:29:58
    jxls导出excel合并单元格的两种方式,这是合并单元格第一种方式模板
  • java中成熟的excel导出工具有pol、jxl,但他们都是使用java代码的方式来导出excel,编码效率很低且不方便维护。 还可以使用一些工具很轻松的实现模板导出。这些工具现在还在维护, 而且做得比较好的国内的有...
  • 日常工作中可能经常会涉及到用java开发报表,需求比较多的就是表格类的报表导出单元格合并,图表的展现。具体怎么实现,分表格和图表两类来说。 1、表格类 通常采用的方式是“Java+POI+Excel模板”来制作简单的...

空空如也

空空如也

1 2
收藏数 24
精华内容 9
关键字:

jxls导出excel合并单元格