java(jxl)根据excle模板填值生成新excle返回

这个工具类是基于 Jxl 写的 maven jar

        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>

 

首先我的模板 A4标准

生成之后的效果

直接上工具类吧 JxlUtils.class

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.CellFormat;
import jxl.format.PaperSize;
import jxl.write.*;

import java.io.*;
import java.util.Date;
import java.util.Map;
import java.util.UUID;

/**
 * Jxl工具类
 */
public class JxlUtils {

    /**
     * 生成新文件临时路径
     *
     * @return
     */
    private static String getNewFilePath() {
        return new Date().getTime() + UUID.randomUUID().toString().replaceAll("-", "");
    }

    /**
     * 过程管理
     */
    static class JxlModel {

        static Workbook wb;

        static WritableWorkbook wwb;

        static File f;

        /**
         * 初始化打印
         *
         * @param filePath
         * @return
         * @throws Exception
         */
        static WritableSheet openSheet(String filePath) throws Exception {
            //为了降低CPU性能影响 禁用回收机制
            WorkbookSettings wbs = new WorkbookSettings();
            wbs.setGCDisabled(true);
　　　　　　　//不设置会报错access超出长度
            wbs.setWriteAccess(null);
            //获取模板内容
            wb = getModelWorkbook(filePath);

            //利用已经创建的Excel工作薄,创建新的可写入的Excel工作薄
            String newFilePath = "/tmp/" + getNewFilePath() + ".xls";
            f = new File(newFilePath);
            FileOutputStream output = new FileOutputStream(f);
            wwb = Workbook.createWorkbook(output, wb, wbs);
            //读取第一张工作表
            WritableSheet ws = wwb.getSheet(0);
            //设置打印属性
            ws.getSettings().setPaperSize(PaperSize.A4);
            ws.getSettings().setTopMargin(0.1);
            ws.getSettings().setBottomMargin(0.1);
            ws.getSettings().setLeftMargin(0.2);
            ws.getSettings().setRightMargin(0.2);

            return ws;
        }

        /**
         * 写入文件，关闭打印
         *
         * @return
         * @throws IOException
         * @throws WriteException
         */
        static FileInputStream end() throws Exception {
            wwb.write();
            wwb.close();
            wb.close();
            return new FileInputStream(f);
        }

    }

    /**
     * 获取模板
     *
     * @param filePath
     * @return
     * @throws Exception
     */
    private static Workbook getModelWorkbook(String filePath) throws Exception {
        //构建Workbook对象, 只读Workbook对象
        //直接从本地文件创建Workbook
        InputStream instream = new FileInputStream(filePath);
        Workbook wb = Workbook.getWorkbook(instream);
        return wb;
    }

    /**
     * 表格列格式化
     *
     * @param writableSheet
     * @param format
     * @param column        列数
     * @param columns       表格列
     * @param start         开始行
     * @param size          列表一共多少行
     */
    private static void format(WritableSheet writableSheet, Map<Integer, CellFormat> format, int column, int[] columns, int start, int size) throws Exception {
        //算出列表结尾行
        size += start;
        //表格开始行
        final int rowStart = start;
        while (start < size) {
            for (int j = 0; j < column; j++) {
                WritableCell writableCell = writableSheet.getWritableCell(j, start);
                //全部用blank格式化
                Blank blank = new Blank(j, start);
                if (format.get(j) == null) {
                    format.put(j, writableCell.getCellFormat());
                } else {
                    blank.setCellFormat(format.get(j));
                    writableSheet.addCell(blank);
                }
            }
            start++;
            if (start > rowStart && start < size) {
                writableSheet.insertRow(start);
                for (int c = 0; c < columns.length; c++) {
                    int cStart = columns[c];
                    int cEnd = cStart;
                    //最后一个
                    if (c == columns.length - 1) {
                        cEnd = column - 1;
                    } else {
                        cEnd = columns[c + 1] - 1;
                    }
                    if (cStart != cEnd) {
                        writableSheet.mergeCells(cStart, start, cEnd, start);
                    }
                }
            }
        }

    }

    /**
     * 添加lable单元格
     *
     * @param writableSheet
     * @param content
     * @param cloum
     * @param row
     * @throws Exception
     */
    private static void insertCell(WritableSheet writableSheet, String content, int cloum, int row) throws Exception {
        //获取单元格
        WritableCell writableCell = writableSheet.getWritableCell(cloum, row);
        //统一label处理
        Label lable = new Label(cloum, row, content);
        //继承格式
        lable.setCellFormat(writableCell.getCellFormat());
        //写入单元格
        writableSheet.addCell(lable);
    }

    /**
     * 带格式添加lable单元格
     *
     * @param writableSheet
     * @param content
     * @param cloum
     * @param row
     * @param wrap
     * @throws Exception
     */
    private static void insertCell(WritableSheet writableSheet, String content, int cloum, int row, boolean wrap) throws Exception {
        //获取单元格
        WritableCell writableCell = writableSheet.getWritableCell(cloum, row);
        //格式化
        WritableCellFormat cellFormat = new WritableCellFormat(writableCell.getCellFormat());
        //换行
        cellFormat.setWrap(wrap);
        //统一label处理
        Label lable = new Label(cloum, row, content);
        //继承格式
        lable.setCellFormat(cellFormat);
        //写入单元格
        writableSheet.addCell(lable);
    }

}

调用这个工具类的方法还是拿部分业务代码做demo吧

注意：excle的行和列下标位都是从0开始的

    /**
     * 打印PO
     *
     * @param 你需要打印的对象
     * @throws Exception
     */
    public static FileInputStream printPO() throws Exception {//打开 PO模板
        WritableSheet ws = JxlModel.openSheet("E:/model/PO_Doc.xls");
        //列数
        int rsColumns = ws.getColumns();
        //行数
        int rsRows = ws.getRows();
        for (int i = 0; i < rsRows; i++) {
            for (int j = 0; j < rsColumns; j++) {
                //获取单元格
                WritableCell wc = ws.getWritableCell(j, i);
                //po no
                if (j == 7 && i == 0) {
                    //获得单元格对象 拼接原来的内容
                    insertCell(ws, wc.getContents() + ":" + "11", j, i);
                }
                //state
                if (j == 9 && i == 5) {
                    insertCell(ws, "state", j, i);
                }
                //Supplier Address
                if (j == 0 && i == 9) {
                    insertCell(ws, "address", j, i, true);
                }
            }
        }
        //list数据开始行
        int start = 23;
        int size = list.size();//列
        int[] column = {0, 1, 3, 5, 7, 8, 9};
        //列格式化
        Map<Integer, CellFormat> format = new HashMap<>();
        format(ws, format, ws.getColumns(), column, start, size);
        //需要遍历表格的集合
        for (Xxx xxx: list) {
            for (int c : column) {// S/N
                if (c == 0) {
                    insertCell(ws, xxx.getId(), c, start);
                }
                // SKU
                if (c == 1) {
                    insertCell(ws, xxx.getSku(), c, start);
                }
            }
            start++;
        }//结束打印
        return JxlModel.end();
    }

大致使用就是这样了，业务中使用的模型我就用 xxx 代替了

 如果大家有什么不解，或意见，欢迎在下方留言，楼主看到就会回复的，谢谢。