EXCEL模板导出--jxls

最新推荐文章于 2023-12-24 13:01:19 发布

转载最新推荐文章于 2023-12-24 13:01:19 发布 · 2.4k 阅读

文章标签：

#EXCEL导出 #jxls

EXCEL 专栏收录该内容

1 篇文章

订阅专栏

博客介绍了基于Jxls进行Excel导出的相关内容，包括导入依赖包，提供合并单元格操作工具类、导出所需工具类，给出调用伪代码，展示了如何设置响应头、获取输出流，将数据放入Map并结合模板路径调用封装工具类进行Excel导出，还提及了Excel模板。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、导入依赖包

<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls</artifactId>
    <version>2.5.1</version>
</dependency>
<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-poi</artifactId>
    <version>1.0.12</version>
</dependency>
<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-jexcel</artifactId>
    <version>1.0.6</version>
</dependency>

合并单元个操作工具类：

import jxl.write.WriteException;
import org.apache.commons.lang3.StringUtils;
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.PoiCellData;
import org.jxls.transform.poi.PoiTransformer;

/**
 * <p>合并单元格</p>
 * jx:merge(
 *  lastCell="单元格"
 *  [, cols="合并的列数"]
 *  [, rows="合并的行数"]
 *  [, minCols="最小合并的列数"]
 *  [, minRows="最小合并的行数"]
 * )
 *  
 */
public class MergeCommand extends AbstractCommand{
    private String cols;        //合并的列数
    private String rows;        //合并的行数
    private String minCols;     //最小合并的列数
    private String minRows;     //最小合并的行数
    private CellStyle cellStyle;//第一个单元格的样式

    private Area area;

    @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 = area.getSize().getHeight();
        int cols = area.getSize().getWidth();
        rows = Math.max(getVal(this.rows, context), rows);
        cols = Math.max(getVal(this.cols, context), cols);
        rows = Math.max(getVal(this.minRows, context), rows);
        cols = Math.max(getVal(this.minCols, context), cols);
        if(rows > 1 || cols > 1){
            Transformer transformer = this.getTransformer();
            if(transformer instanceof PoiTransformer){
                poiMerge(cellRef, context, (PoiTransformer)transformer, rows, cols);
            }else if(transformer instanceof JexcelTransformer){
                jexcelMerge(cellRef, context, (JexcelTransformer)transformer, rows, cols);
            }
        }
        area.applyAt(cellRef, context);
        return new Size(cols, rows);
    }

    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){
            PoiCellData cellData = (PoiCellData)transformer.getCellData(area.getStartCellRef());
            if(cellData != null){
                cellStyle = cellData.getCellStyle();
            }
        }
        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);
    }

    private 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);
                }
                if (cs == null){
                    cell.getCellStyle().setAlignment(CellStyle.ALIGN_CENTER);
                    cell.getCellStyle().setVerticalAlignment(CellStyle.VERTICAL_CENTER);
                }else {
                    cell.setCellStyle(cs);
                }
            }
        }
    }

    private int getVal(String expression, Context context){
        if(StringUtils.isNotBlank(expression)){
            Object obj = getTransformationConfig().getExpressionEvaluator().evaluate(expression, context.toMap());
            try {
                return Integer.parseInt(obj.toString());
            } catch (NumberFormatException e) {
                throw new IllegalArgumentException("表达式：" + expression + " 解析失败");
            }
        }
        return 0;
    }

    public String getCols() {
        return cols;
    }

    public void setCols(String cols) {
        this.cols = cols;
    }

    public String getRows() {
        return rows;
    }

    public void setRows(String rows) {
        this.rows = rows;
    }

    public String getMinCols() {
        return minCols;
    }

    public void setMinCols(String minCols) {
        this.minCols = minCols;
    }

    public String getMinRows() {
        return minRows;
    }

    public void setMinRows(String minRows) {
        this.minRows = minRows;
    }
}

导出需要的工具类

import java.io.File;
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.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.jxls.builder.xls.XlsCommentAreaBuilder;
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;

public class JxlsUtils{

    static{
        //添加自定义指令（可覆盖jxls原指令）
        //合并单元格(模板已经做过合并单元格操作的单元格无法再次合并)
        XlsCommentAreaBuilder.addCommandMapping("merge", MergeCommand.class);
    }

/**
 * excel 导出公用方法
 * @param templatePath  模板路径
 * @param os                输出流
 * @param model         数据模型
 * @throws Exception
 */
    public static void exportExcel(InputStream is, OutputStream os, Map<String, Object>
            model) throws IOException{
        Context context = PoiTransformer.createInitialContext();
        if (model != null) {
            for (Map.Entry<String, Object> entry : model.entrySet()){
                context.putVar(entry.getKey(), entry.getValue());
            }
        }
        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("jx", 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模版文件
    public static File getTemplate(String path){
        File template = new File(path);
        if(template.exists()){
            return template;
        }
        return null;
    }

    // 日期格式化
    public String dateFmt(Date date, String fmt) {
        if (date == null) {
            return "";
        }
        try {
            SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
            return dateFmt.format(date);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }

    // if判断
    public Object ifelse(boolean b, Object o1, Object o2) {
        return b ? o1 : o2;
    }
}

调用伪代码：

String fileName = URLEncoder.encode("样例.xlsx", "utf-8");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"; filename*=utf-8''" + fileName);

try ( OutputStream os = response.getOutputStream()){
//模板里展示的数据

Map<String, Object> datas = new HashMap<>();
data.put("datas", datas);
URL resource = CurrentController.class.getResource(tempPath);
String templatePath = resource.getPath();
//调用封装的工具类，传入模板路径，输出流，和装有数据的Map,按照模板导出

JxlsUtils.exportExcel(templatePath, os, data);

} catch (Exception e) { ;
e.printStackTrace();
}

EXCEL模板