JXLS生成excel并自定义单元格样式
本人最近需要每天统计数据表,并每周一发送统计结果的邮件,所以写了个springboot老自动完成工作。项目地址为:https://github.com/707293891/springboot
其中某些统计数据需要特殊标记:红色显示。
如图:模版如下
但是需要在某一处特殊显示为红色:如图
现在写出实现过程:
利用jxls的区域监听器实现.
Transformer transformer = TransformerFactory.createTransformer(getFileInputStream(), new FileOutputStream( new File( Thread.currentThread().getContextClassLoader(). getResource("excelTemplates/result").getFile()+"/result.xls"))); XlsArea xlsArea=new XlsArea("Sheet1!A1:F3",transformer); XlsArea employeeArea = new XlsArea("Sheet1!A3:F3", transformer); employeeArea.addAreaListener(new SimpleAreaListener(employeeArea)); EachCommand eachCommand=new EachCommand("item","items",employeeArea); xlsArea.addCommand("A3:F3", eachCommand); Context context = new Context(); Map map=new HashMap(); // map.put("list",getData()); map.put("week",CalendarUtil.getWeekNum()); map.put("month",CalendarUtil.getMonth()); context.putVar("print", map); context.putVar("items",list); xlsArea.applyAt(new CellRef("Sheet1!A1"), context); transformer.write();
SimpleAreaListener实现如下:
package com.yinhai.yunwei.excel; import com.yinhai.yunwei.yunwei.mapper.YunweiInfo; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFFont; import org.jxls.area.Area; import org.jxls.area.XlsArea; import org.jxls.common.AreaListener; import org.jxls.common.CellRef; import org.jxls.common.Context; import org.jxls.transform.poi.PoiTransformer; import java.util.Map; /** * @author 范超 * @version V1.0 * @Title SimpleAreaListener * @Package jxls * @Descript :TODO() * @date : 2018/6/25 上午10:22 */ public class SimpleAreaListener implements AreaListener { private Area area; PoiTransformer transformer; public SimpleAreaListener(XlsArea xlsArea) { this.area=xlsArea; transformer= (PoiTransformer) xlsArea.getTransformer(); } @Override public void beforeApplyAtCell(CellRef cellRef, Context context) { } @Override public void afterApplyAtCell(CellRef cellRef, Context context) { } @Override public void beforeTransformCell(CellRef cellRef, CellRef cellRef1, Context context) { } @Override public void afterTransformCell(CellRef cellRef, CellRef cellRef1, Context context) { if (cellRef1.getCol()!=3&&cellRef1.getCol()!=5){ return; } Workbook workbook=transformer.getWorkbook(); Cell cell=workbook.getSheet(cellRef1.getSheetName()).getRow(cellRef1.getRow()).getCell(cellRef1.getCol()); CellStyle cellStyle=cell.getCellStyle(); Font font=workbook.createFont(); CellStyle resultCell=workbook.createCellStyle(); Object item=context.getVar("item"); //需要显示红色的条件 if(item!=null&&item instanceof YunweiInfo &&"****".equals(((YunweiInfo) item).getName())){ font.setColor(XSSFFont.COLOR_RED); resultCell.setFont(font); cell.setCellStyle(resultCell); } } }
这样当满足条件时就会实现特殊的显示格式了。
其中jxls版本为如下
<dependency> <groupId>org.jxls</groupId> <artifactId>jxls</artifactId> <version>[2.4.3,)</version> </dependency> <dependency> <groupId>org.jxls</groupId> <artifactId>jxls-poi</artifactId> <version>[1.0.14,)</version> </dependency> <dependency> <groupId>org.jxls</groupId> <artifactId>jxls-jexcel</artifactId> <version>[1.0.6,)</version> </dependency> <dependency> <groupId>org.jxls</groupId> <artifactId>jxls-reader</artifactId> <version>[2.0.3,)</version> </dependency>
https://snailsdream.github.io/