Jxls2.x版本，添加自定义功能、函数 – 小飞侠

Jxls2.x版本，添加自定义功能、函数

Post author:xfxia
Post published:2023年8月27日
Post category:其他

1.引入maven依赖：

<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>

2.生成excel辅助类代码：

public class JxlsxView extends AbstractUrlBasedView {
  private static final SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat("_yyyy年MM月dd日-HHmmss");
  private static final String CONTENT_TYPE = "application/vnd.ms-excel";

  public JxlsxView() {
    super();
    setContentType(CONTENT_TYPE);
  }

  /**
   * 输出  "指定文件名 + 当前时间" 的 .xls 或 .xlsx 文件
   */
  @Override
  protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request,
      HttpServletResponse response) throws IOException {
    Context context = new Context(model);
    String exportFileName = (String) model.get(Constants.EXPORT_FILE_NAME) + SIMPLE_DATE_FORMAT.format(new Date())
        + getUrl().substring(getUrl().lastIndexOf("."));
    response.setContentType(getContentType());
    response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(exportFileName, "UTF-8"));
    try (InputStream is = new FileInputStream(new File(this.getServletContext().getRealPath(this.getUrl())));
        ServletOutputStream os = response.getOutputStream()) {
      //JxlsHelper.getInstance().processTemplate(is, os, context);
      JxlsUtils.exportExcel(is, os, context);
    }
  }
  
  public static void main(String[] args) {
      
      OutputStream os = new FileOutputStream("target/object_collection_output.xls");
      //ServletOutputStream os = response.getOutputStream()
      Map<String , Object> model=new HashMap<String , Object>();
      model.put("nowdate", new Date());
      JxlsUtils.exportExcel("object_collection_template.xls", os, model);
      os.close();
  }
}

package com.dome.common.web.view;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.jexl3.JexlBuilder;
import org.apache.commons.jexl3.JexlEngine;
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;

import com.dome.common.web.view.command.MergeCommand;

/**
 * <p>生成excel辅助类</p>
 * @author 86152
 *
 */
public class JxlsUtils {

	static {
		//注册 jx 命令
		XlsCommentAreaBuilder.addCommandMapping("merge", MergeCommand.class);
	}

	/**
	 * jxls2.6以上版本，添加自定义功能
	 */
	public static void exportExcel(InputStream is, OutputStream os, Context context) throws IOException {
		JxlsHelper jxlsHelper = JxlsHelper.getInstance();
		Transformer transformer = jxlsHelper.createTransformer(is, os);
		//获得配置
		JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig()
				.getExpressionEvaluator();
		//函数强制，自定义功能
		Map<String, Object> funcs = new HashMap<String, Object>();
		funcs.put("utils", new JxlsUtils()); //添加自定义功能
		JexlBuilder jb = new JexlBuilder();
		jb.namespaces(funcs);
		//jb.silent(true); //设置静默模式，不报警告
		JexlEngine je = jb.create();
		evaluator.setJexlEngine(je);

		//必须要这个，否者表格函数统计会错乱
		jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);
	}

	/**
	 * jxls2.6以下版本，添加自定义功能
	 */
	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);
	}

    /**
     * if判断
     * ${utils:ifelse(b, o1, o2)} 单元格内
     * utils:ifelse(b, o1, o2) 批注内
     * @param b
     * @param o1
     * @param o2
     * @return
     */
    public Object ifelse(boolean b, Object o1, Object o2) {
        return b ? o1 : o2;
    }

}

MergeCommand

package com.dome.common.web.view.command;

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.poi.PoiCellData;
import org.jxls.transform.poi.PoiTransformer;

/**
 * jxls <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 = getVal(this.rows, context);
		int cols = getVal(this.cols, context);
		rows = Math.max(getVal(this.minRows, context), rows);
		cols = Math.max(getVal(this.minCols, context), cols);
		rows = rows > 0 ? rows : area.getSize().getHeight();
		cols = cols > 0 ? cols : area.getSize().getWidth();
		if (rows > 1 || cols > 1) {
			Transformer transformer = this.getTransformer();
			if (transformer instanceof PoiTransformer) {
				poiMerge(cellRef, context, (PoiTransformer) transformer, rows, cols);
			} else {
				transformer.mergeCells(cellRef, 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);
	}

	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(HorizontalAlignment.CENTER);
					cell.getCellStyle().setVerticalAlignment(VerticalAlignment.CENTER);
				} else {
					cell.setCellStyle(cs);
				}
			}
		}
	}

	private int getVal(String expression, Context context) {
		if ((expression != null) && (expression.trim().length() > 0)) {
			Object obj = getTransformationConfig().getExpressionEvaluator().evaluate(expression, context.toMap());
			try {
				return Integer.parseInt(obj.toString());
			} catch (NumberFormatException e) {
				throw new IllegalArgumentException("Expression: " + expression + " failed to resolve");
			}
		}
		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;
	}

}

示例

excel模板文件：

版权声明：本文为qq_42277412原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/qq_42277412/article/details/107533623