本人最近需要每天统计数据表,并每周一发送统计结果的邮件,所以写了个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>