import com.cdtye.manage.utils.jxlsCommand.*;
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.util.JxlsHelper;
import org.springframework.util.CollectionUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author zwx
* @description JXLS导出EXCEL工具类
* @date 2023/10/13 14:18
*/
public class JxlsUtil {
static {
// 添加自定义指令（可覆盖jxls原指令）
XlsCommentAreaBuilder.addCommandMapping("image", ImageCommand.class);
XlsCommentAreaBuilder.addCommandMapping("each", EachCommand.class);
XlsCommentAreaBuilder.addCommandMapping("merge", MergeCommand.class);
XlsCommentAreaBuilder.addCommandMapping("link", LinkCommand.class);
XlsCommentAreaBuilder.addCommandMapping("font", FontCommand.class);
}
/**
* jxls模版文件目录
*/
private final static String TEMPLATE_PATH = "/";
public static HttpServletResponse export(String templateFileName, String outFileName, Map<String, Object> dataMap,
List<String> sheetNameList, HttpServletResponse response) throws Exception {
response.resetBuffer();
response.reset();
response.setContentType("application/x-xls");
// String dateTime = DateFormatUtils.formatDateString(new Date(), DateFormatUtils.DATE_FORMAT);
String fileNames = java.net.URLEncoder.encode(outFileName, "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileNames);
response.addHeader("Content-Transfer-Encoding", "binary");
// 初始化JXLS解析器
JxlsHelper jxlsHelper = JxlsHelper.getInstance();
InputStream inputStream = null;
try {
String templateFilePath = JxlsUtil.class.getClassLoader().getResource("template").getPath();
templateFilePath = templateFilePath.replace("%20", " ");
File f = new File(templateFilePath, templateFileName);
// 首先查看目录中是否已经有该文件
if (f.exists()) {
inputStream = new FileInputStream(f);
} else {
// 从jar包中取 获取jar中的静态资源文件 （发布到通号只能通过从jar中才能获取到模板文件）
inputStream = JxlsUtil.class.getClassLoader().getResourceAsStream("template/" + templateFileName);
}
Transformer transformer = jxlsHelper.createTransformer(inputStream, response.getOutputStream());
JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();
Map<String, Object> funcs = new HashMap<>();
// 添加自定义功能
funcs.put("jx", new JxlsUtil());
JexlBuilder jb = new JexlBuilder();
jb.namespaces(funcs);
// 设置静默模式，不报警告
jb.silent(true);
JexlEngine je = jb.create();
evaluator.setJexlEngine(je);
// 删除指定sheet
if (!CollectionUtils.isEmpty(sheetNameList)) {
sheetNameList.forEach(transformer::deleteSheet);
}
// 开始解析excel模板文件
Context context = new Context();
if (dataMap != null) {
for (String key : dataMap.keySet()) {
context.putVar(key, dataMap.get(key));
}
}
JxlsHelper.getInstance().processTemplate(context, transformer);
} catch (Exception e) {
e.printStackTrace();
}
return response;
}
/**
* 静态资源转流下载
*/
public static void exportTemp(String templateFileName, String outFileName,HttpServletResponse response)throws Exception{
InputStream inputStream = null;
try {
String templateFilePath = JxlsUtil.class.getClassLoader().getResource("template").getPath();
templateFilePath = templateFilePath.replace("%20", " ");
File f = new File(templateFilePath, templateFileName);
// 首先查看目录中是否已经有该文件
if (f.exists()) {
inputStream = new FileInputStream(f);
} else {
// 从jar包中取 获取jar中的静态资源文件 （发布到通号只能通过从jar中才能获取到模板文件）
inputStream = JxlsUtil.class.getClassLoader().getResourceAsStream("template/" + templateFileName);
}
// 获取响应的输出流
OutputStream outputStream = response.getOutputStream();
// 将文件转成字节数组，再将数组写入响应的输出流
byte[] buffer = new byte[1024];
int bytesRead = -1;
while ((bytesRead = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, bytesRead);
}
// 刷新输出流
outputStream.flush();
// 关闭流
inputStream.close();
outputStream.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
}
其他样式文件：
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.jxls.area.Area;
import org.jxls.command.AbstractCommand;
import org.jxls.command.Command;
import org.jxls.common.*;
import org.jxls.transform.Transformer;
import org.jxls.transform.poi.PoiTransformer;
/**
* @Author: DQF @Date 2018/4/24 9:27
* @Description:解决图片最少占4个单元格才显示的问题并保留原来单元格样式
*/
public class ImageCommand extends AbstractCommand {
private byte[] imageBytes;
private ImageType imageType = ImageType.PNG;
private Area area;
/**
* Expression that can be evaluated to image byte array byte[]
*/
private String src;
private String text; //无法读取图片时的提示
public ImageCommand() {
}
public ImageCommand(String image, ImageType imageType) {
this.src = image;
this.imageType = imageType;
}
public ImageCommand(byte[] imageBytes, ImageType imageType) {
this.imageBytes = imageBytes;
this.imageType = imageType;
}
/**
* @return src expression producing image byte array
*/
public String getSrc() {
return src;
}
/**
* @param src expression resulting in image byte array
*/
public void setSrc(String src) {
this.src = src;
}
public void setImageType(String strType) {
imageType = ImageType.valueOf(strType);
}
@Override
public Command addArea(Area area) {
if (super.getAreaList().size() >= 1) {
throw new IllegalArgumentException("You can add only a single area to 'image' command");
}
this.area = area;
return super.addArea(area);
}
@Override
public String getName() {
return "image";
}
@Override
public Size applyAt(CellRef cellRef, Context context) {
if (area == null) {
throw new IllegalArgumentException("No area is defined for image command");
}
Transformer transformer = getTransformer();
Size size = area.getSize();
//获取图片显示区域是时候，多加一行和一列，获取完之后再恢复原来大小
size.setWidth(size.getWidth() + 1);
size.setHeight(size.getHeight() + 1);
AreaRef areaRef = new AreaRef(cellRef, size);
size.setWidth(size.getWidth() - 1);
size.setHeight(size.getHeight() - 1);
byte[] imgBytes = imageBytes;
if (src != null) {
Object imgObj = getTransformationConfig().getExpressionEvaluator().evaluate(src, context.toMap());
if (imgObj != null && !("".equals(imgObj))) {
if (!(imgObj instanceof byte[])) {
throw new IllegalArgumentException("src value must contain image bytes (byte[])");
}
imgBytes = (byte[]) imgObj;
}
}
if (imgBytes != null) {
transformer.addImage(areaRef, imgBytes, imageType);
}
area.applyAt(cellRef, context); //恢复原有的样式
if (imgBytes == null && StringUtils.isNotBlank(text)) {
PoiTransformer poi = (PoiTransformer) transformer;
Sheet sheet = poi.getWorkbook().getSheet(cellRef.getSheetName());
Row row = sheet.getRow(cellRef.getRow());
if (row != null && row.getCell(cellRef.getCol()) != null) {
row.getCell(cellRef.getCol()).setCellValue(text);
}
}
return size;
}
public String getText() {
return text;
}
public void setText(String text) {
this.text = text;
}
}
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;
/**
* @Author: DQF @Date 2018/4/24 9:16
* @Description: 扩展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时，是否最少保留一行空行数据
private String autoFill;
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 = null;
try {
itemsCollection = Util.transformToCollectionObject(getTransformationConfig().getExpressionEvaluator(), items, context);
} catch (Exception e) {
return new Size(1, 1);
}
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;
}
public String getAutoFill() {
return autoFill;
}
public void setAutoFill(String autoFill) {
this.autoFill = autoFill;
}
}
import jxl.write.WriteException;
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.PoiCellData;
import org.jxls.transform.poi.PoiTransformer;
/**
* @Author: DQF @Date 2018/4/24 10:10
* @Description: 合并单元格命令
*/
public class MergeCommand extends AbstractCommand {
private String cols; //合并的列数
private String rows; //合并的行数
private String nextRows;//扩展的行数 默认为0，且必须大于0，向下多合并nextRows行
private Area area;
private CellStyle cellStyle; //第一个单元格的样式
private String autoStyle;
private BorderStyle border = BorderStyle.THIN; // 边框
private HorizontalAlignment hAlign = HorizontalAlignment.CENTER; // 水平对齐方式
private VerticalAlignment vAlign = VerticalAlignment.CENTER; // 垂直对齐方式
private boolean wrap = true; // 是否自动换行
private short fontSize = 11; // 字号
private String fontFam = "宋体"; // 字体
@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, nextRows = 0;
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 (StringUtils.isNotBlank(this.nextRows)) {
nextRows = NumberUtils.toInt(this.nextRows);
nextRows = nextRows < 0 ? 0 : nextRows;
}
if (rows > 1 || cols > 1 || nextRows > 0) {
Transformer transformer = this.getTransformer();
if (transformer instanceof PoiTransformer) {
return poiMerge(cellRef, context, (PoiTransformer) transformer, rows, cols, nextRows);
} else if (transformer instanceof JexcelTransformer) {
return jexcelMerge(cellRef, context, (JexcelTransformer) transformer, rows, cols, nextRows);
}
}
area.applyAt(cellRef, context);
return new Size(1, 1);
}
protected Size poiMerge(CellRef cellRef, Context context, PoiTransformer transformer, int rows, int cols, int nextRows) {
Workbook workbook = transformer.getWorkbook();
Sheet sheet = workbook.getSheet(cellRef.getSheetName());
CellRangeAddress region = new CellRangeAddress(
cellRef.getRow(),
cellRef.getRow() + rows - 1 + nextRows,
cellRef.getCol(),
cellRef.getCol() + cols - 1);
// 如果合并的单元格只有一个cell，则不做合并处理
if (rows - 1 + nextRows > 0 || cols - 1 > 0) {
sheet.addMergedRegion(region);
}
//合并之后单元格样式会丢失，以下操作将合并后的单元格恢复成合并前第一个单元格的样式
area.applyAt(cellRef, context);
if (cellStyle == null) {
PoiCellData cellData = (PoiCellData) transformer.getCellData(cellRef);
if (cellData == null) {
// if ("true".equalsIgnoreCase(this.autoStyle)) {
cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(this.border);
cellStyle.setBorderLeft(this.border);
cellStyle.setBorderTop(this.border);
cellStyle.setBorderRight(this.border);
cellStyle.setWrapText(this.wrap);
cellStyle.setAlignment(this.hAlign);
cellStyle.setVerticalAlignment(this.vAlign);
Font font = workbook.createFont();
font.setFontHeightInPoints(fontSize);
font.setFontName(fontFam);
cellStyle.setFont(font);
// }
} else {
cellStyle = cellData.getCellStyle();
}
}
if (cellStyle != null) {
setRegionStyle(cellStyle, region, sheet);
}
return new Size(cols, rows);
}
protected Size jexcelMerge(CellRef cellRef, Context context, JexcelTransformer transformer, int rows, int cols, int nextRows) {
try {
transformer.getWritableWorkbook().getSheet(cellRef.getSheetName())
.mergeCells(
cellRef.getRow(),
cellRef.getCol(),
cellRef.getRow() + rows - 1 + nextRows,
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);
}
cell.setCellStyle(cs);
}
}
}
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 getNextRows() {
return nextRows;
}
public void setNextRows(String nextRows) {
this.nextRows = nextRows;
}
public String getAutoStyle() {
return autoStyle;
}
public void setAutoStyle(String autoStyle) {
this.autoStyle = autoStyle;
}
public Area getArea() {
return area;
}
public void setArea(Area area) {
this.area = area;
}
public CellStyle getCellStyle() {
return cellStyle;
}
public void setCellStyle(CellStyle cellStyle) {
this.cellStyle = cellStyle;
}
public BorderStyle getBorder() {
return border;
}
public void setBorder(BorderStyle border) {
this.border = border;
}
public boolean isWrap() {
return wrap;
}
public void setWrap(boolean wrap) {
this.wrap = wrap;
}
public short getFontSize() {
return fontSize;
}
public void setFontSize(short fontSize) {
this.fontSize = fontSize;
}
public String getFontFam() {
return fontFam;
}
public void setFontFam(String fontFam) {
this.fontFam = fontFam;
}
}
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
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 zwx
* @description url超链接
* @date 2023/10/13 14:32
*/
public class LinkCommand extends AbstractCommand {
private String url; //url地址（必选）
private String title; //url显示标题（可选）
private Area area;
@Override
public String getName() {
return "link";
}
@Override
public Command addArea(Area area) {
if (super.getAreaList().size() >= 1) {
throw new IllegalArgumentException("You can add only a single area to 'link' command");
}
this.area = area;
return super.addArea(area);
}
@Override
public Size applyAt(CellRef cellRef, Context context) {
if(StringUtils.isBlank(url)){
throw new NullPointerException("url不能为空");
}
area.applyAt(cellRef, context);
Transformer transformer = this.getTransformer();
if(transformer instanceof PoiTransformer){
poiLink(cellRef, context, (PoiTransformer)transformer);
}else if(transformer instanceof JexcelTransformer){
jxcelLink(cellRef, context, (JexcelTransformer)transformer);
}
return new Size(1, 1);
}
protected void poiLink(CellRef cellRef, Context context, PoiTransformer transformer){
Object urlObj = getTransformationConfig().getExpressionEvaluator().evaluate(url, context.toMap());
if(urlObj == null) {
{
return;
}
}
String url = urlObj.toString();
String title = url;
if(StringUtils.isNotBlank(this.title)){
Object titleObj = getTransformationConfig().getExpressionEvaluator().evaluate(this.title, context.toMap());
if(titleObj != null) {
title = titleObj.toString();
}
}
Sheet sheet = transformer.getWorkbook().getSheet(cellRef.getSheetName());
Row row = sheet.getRow(cellRef.getRow());
if(row == null){
row = sheet.createRow(cellRef.getRow());
}
Cell cell = row.getCell(cellRef.getCol());
if(cell == null){
cell = row.createCell(cellRef.getCol());
}
// cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("HYPERLINK(\"" + url+ "\",\"" + title + "\")");
if(!url.equals(title)){
cell.setCellValue(title);
}
CellStyle linkStyle = cell.getCellStyle();
Font cellFont= transformer.getWorkbook().createFont();
cellFont.setUnderline((byte) 1);
// cellFont.setColor(HSSFColor.BLUE.index);
linkStyle.setFont(cellFont);
}
protected void jxcelLink(CellRef cellRef, Context context, JexcelTransformer transformer){
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
}
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
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.poi.PoiTransformer;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
/**
* create by ahoi on 2018-5-9 09:24:24
* excel指令-> fx:font(lastCell="xx")
* 可对指定区域判断内容超出后自动缩小字体
*/
public class FontCommand extends AbstractCommand {
private Area area;
private String textLength;
private Map<String, HSSFCellStyle> cellStyleMap = new ConcurrentHashMap<>();
@Override
public String getName() {
return "font";
}
@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
public Size applyAt(CellRef cellRef, Context context) {
Size size = area.applyAt(cellRef, context);
PoiTransformer transformer = (PoiTransformer) this.getTransformer();
HSSFWorkbook workbook = (HSSFWorkbook) transformer.getWorkbook();
Sheet sheet = workbook.getSheet(cellRef.getSheetName());
Row row = sheet.getRow(cellRef.getRow());
Cell cell = row.getCell(cellRef.getCol());
// sheet.setRepeatingRows(new CellRangeAddress(0, 2, 0, 15));
// 字号默认从10号字开始计算是否超出
short fontSize = 0;
//循环利用cellStyle,防止样式太多，工具不支持
if (!cellStyleMap.containsKey(sheet.getSheetName())){
cellStyleMap.put(sheet.getSheetName(), workbook.createCellStyle());
}
for (short f = 10; f > 5; f--) {
fontSize = f;
// 如果未超出，则得到当前fontSize字号
if (checkCellIsOk(cell, f)) {
break;
}
}
// 如果字号有更新，则从新设置单元格字号
if (fontSize != 0) {
HSSFCellStyle cellStyle = cellStyleMap.get(sheet.getSheetName());
cellStyle.cloneStyleFrom(cell.getCellStyle());
//默认取第一cell的字体样式，不创建font
short fontIndex = 1;
HSSFFont font = workbook.getFontAt(fontIndex);
font.setFontHeightInPoints(fontSize);
font.setFontName("宋体");
cellStyle.setFont(font);
cellStyle.setWrapText(true);
cell.setCellStyle(cellStyle);
}
return size;
}
/**
* 校验单元格中的字体大小是否合理
*/
private boolean checkCellIsOk(Cell cell, short fontSize) {
String cellValue = cell.getStringCellValue();
// 每一行多少文字
double wordSize = getTotalWidth(cell) / 256 / 2;
// 计算如果有换行符，则多加一行文字数
int totalWordSize = cellValue.length();
int wrapSize = cellValue.split("\n").length;
if (wrapSize > 0) {
totalWordSize += wrapSize * wordSize;
}
// 共有多少行
double rowSize = totalWordSize / wordSize;
// 最多显示多少行
double cellHeight = cell.getRow().getHeightInPoints();
double rowSizeMax = cellHeight / fontSize;
return rowSizeMax >= rowSize;
}
/**
* 获取单元格的总宽度（单位：单位不是像素，是1/256个字符宽度）
*/
private int getTotalWidth(Cell cell) {
ExcelPositionRange tmpRange = getMergedRegionPositionRange(cell);
int totalWidth = 0;
for (int i = tmpRange.getFirstCol(); i <= tmpRange.getLastCol(); i++) {
totalWidth += cell.getSheet().getColumnWidth(i);
}
return totalWidth;
}
/**
* 获取合并的单元格
*/
private ExcelPositionRange getMergedRegionPositionRange(Cell cell) {
Sheet sheet = cell.getSheet();
int row = cell.getRowIndex();
int column = cell.getColumnIndex();
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; ++i) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (row >= firstRow && row <= lastRow && column >= firstColumn && column <= lastColumn) {
sheet.getRow(firstRow);
return new ExcelPositionRange(firstRow, firstColumn, lastRow, lastColumn);
}
}
return new ExcelPositionRange(row, column, row, column);
}
public class ExcelPositionRange {
private int firstRow;
private int firstCol;
private int lastRow;
private int lastCol;
public ExcelPositionRange(int firstRow, int firstCol, int lastRow, int lastCol) {
this.firstRow = firstRow;
this.firstCol = firstCol;
this.lastRow = lastRow;
this.lastCol = lastCol;
}
public ExcelPositionRange() {
}
public int getFirstRow() {
return firstRow;
}
public void setFirstRow(int firstRow) {
this.firstRow = firstRow;
}
public int getFirstCol() {
return firstCol;
}
public void setFirstCol(int firstCol) {
this.firstCol = firstCol;
}
public int getLastRow() {
return lastRow;
}
public void setLastRow(int lastRow) {
this.lastRow = lastRow;
}
public int getLastCol() {
return lastCol;
}
public void setLastCol(int lastCol) {
this.lastCol = lastCol;
}
}
public String getTextLength() {
return textLength;
}
public void setTextLength(String textLength) {
this.textLength = textLength;
}
}
767
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
