说明
Java poi实现生成图表并附带表格数据展示
一、效果图与模板
1、模板
2、效果图
二、Word生成图标与报表工具类
1.工具类
代码如下:
package com.ml.module.file.util.wordchart;
import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.ml.module.file.domain.entity.ChartType;
import com.ml.support.dto.ChartData;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.chart.XDDFChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
import org.apache.poi.xwpf.usermodel.*;
import org.apache.xmlbeans.XmlCursor;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.*;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
/**
* word图表模板与table工具类
* 根据图表模板以及动态生成表格工具类
*/
public class WordChartTplAndTableUtils {
private static final BigDecimal bd2 = new BigDecimal("2");
/**
* 替换图表模板以及生产动态表格
*/
public static void replaceChartTplAndCreateTable(XWPFDocument doc, List<WordTplDataSourceInput> charItems) {
Map<String, TableDataSourceInput> inputMap = Maps.newHashMap();
Map<String, POIXMLDocumentPart> chartsMap = getChartsMap(doc);//获取模板的图表
AtomicInteger ao = new AtomicInteger(1);
charItems.forEach(data -> {
TableDataSourceInput table = new TableDataSourceInput();
int sortNum = ao.getAndIncrement();
table.setTextParam("table" + sortNum);
POIXMLDocumentPart documentPart = chartsMap.get("/word/charts/chart" + sortNum + ".xml");
switch (data.getChartType()) {
case REPORT:
table.setHeaders(data.getHeaders());
table.setDataSource(data.getDataSource());
break;
default:
setChartData((XWPFChart) documentPart, data.getTitle(), data.getCharts());
}
/**
* 1、标题值为paramColTitle+data里的group的值,作为标题
* 2、每一行的数据为,按data里的name进行分组,然后根据headers进行拼装
*/
if (data.getChartType() != ChartType.REPORT) {
List<String> titleArr = data.getCharts().stream().map(ChartData::getGroup).distinct().collect(Collectors.toList());
List<List<String>> dataSource = Lists.newArrayList();
Map<String, List<ChartData>> maps = data.getCharts().stream()
.collect(Collectors.groupingBy(ChartData::getName, LinkedHashMap::new, Collectors.toList()));
maps.forEach((k, v) -> {
List<String> dataItems = Lists.newArrayList();
dataItems.add(k);
//根据分组获取
Map<String, BigDecimal> groupMap = v.stream().collect(Collectors.toMap(ChartData::getGroup, ChartData::getXxxValue));
titleArr.forEach(group -> {
Object val = MapUtils.getObject(groupMap, group);
dataItems.add(Objects.isNull(val) ? "" : val.toString());
});
dataSource.add(dataItems);
});
titleArr.add(0, data.getParamColTitle());
table.setHeaders(titleArr);
table.setDataSource(dataSource);
}
inputMap.put(table.getTextParam(), table);
});
doParagraphs(doc, inputMap); // 处理段落文字数据,包括文字和表格、图片
}
/**
* 处理图表
* 获取word模板中的所有图表元素,用map存放
* 为什么不用list保存:查看doc.getRelations()的源码可知,源码中使用了hashMap读取文档图表元素,
* 对relations变量进行打印后发现,图表顺序和文档中的顺序不一致,也就是说relations的图表顺序不是文档中从上到下的顺序
*/
private static Map<String, POIXMLDocumentPart> getChartsMap(XWPFDocument doc) {
Map<String, POIXMLDocumentPart> chartsMap = new HashMap<>();
//动态刷新图表
List<POIXMLDocumentPart> relations = doc.getRelations();
for (POIXMLDocumentPart poixmlDocumentPart : relations) {
if (poixmlDocumentPart instanceof XWPFChart) { // 如果是图表元素
String str = poixmlDocumentPart.toString();
System.out.println("str:" + str);
String key = str.replaceAll("Name: ", "")
.replaceAll(" - Content Type: application/vnd\\.openxmlformats-officedocument\\.drawingml\\.chart\\+xml", "").trim();
System.out.println("key:" + key);
chartsMap.put(key, poixmlDocumentPart);
}
}
System.out.println("\n图表数量:" + chartsMap.size() + "\n");
return chartsMap;
}
/**
* 设置图标数据
*
* @param chart 模板图标
* @param chartTitle 图标标题
* @param chartDataList --》String[] series, String[] categories, Double[] values1, Double[] values2
*/
private static void setChartData(XWPFChart chart, String chartTitle, List<ChartData> chartDataList) {
if (CollectionUtils.isEmpty(chartDataList)) {//当传入的值为空时,暂时改为如下
chartDataList.add(ChartData.of("无", "无", BigDecimal.ZERO));
}
final List<XDDFChartData> data = chart.getChartSeries();
XDDFChartData bar = data.get(0);
/*************设计数据源(Excel格式)开始**************/
String[] series = chartDataList.stream().map(ChartData::getGroup).distinct().toArray(String[]::new);
String[] categories = chartDataList.stream().map(ChartData::getName).distinct().toArray(String[]::new);
final int numOfPoints = categories.length;
//获取数据区间--》如:Sheet1!$A$2:$A$9
final String categoryDataRange = chart.formatRange(new CellRangeAddress(1, numOfPoints, 0, 0));//第一列固定为分类
final XDDFDataSource<?> categoriesData = XDDFDataSourcesFactory.fromArray(categories, categoryDataRange, 0);
AtomicInteger ao = new AtomicInteger(1);//每一列代表一个序列
for (String ser : series) {// 假设不包含分类序列
int aoNum = ao.getAndIncrement();
String valuesDataRange = chart.formatRange(new CellRangeAddress(1, numOfPoints, aoNum, aoNum));
Map<String, BigDecimal> valueMap = chartDataList.stream()
.filter(i -> ser.equals(i.getGroup()))
.collect(Collectors.toMap(ChartData::getName, ChartData::getXxxValue));
Double[] values = getValues(categories, valueMap);
XDDFNumericalDataSource<? extends Number> valuesData = XDDFDataSourcesFactory.fromArray(values, valuesDataRange, aoNum);
if (aoNum == 1) {
XDDFChartData.Series series1 = bar.getSeries().get(0);
series1.replaceData(categoriesData, valuesData);// 替换
series1.setTitle(ser, chart.setSheetTitle(ser, 0));
} else {
XDDFChartData.Series series2 = bar.addSeries(categoriesData, valuesData);// 添加
series2.setTitle(ser, chart.setSheetTitle(ser, aoNum));
}
}
chart.plot(bar);// 绘制(图表)
chart.setTitleText(chartTitle); // https://stackoverflow.com/questions/30532612
chart.setTitleOverlay(false);
}
/**
* 1、值的顺序怎么保证呢???
* 2、为空的值,是否能忽略???
*/
private static Double[] getValues(String[] categories, Map<String, BigDecimal> valueMap) {
List<Double> result = Lists.newArrayList();
for (String cate : categories) {
result.add(valueMap.getOrDefault(cate, BigDecimal.ZERO).doubleValue());
}
return result.toArray(new Double[0]);
}
/********************动态表格开始****************************/
/**
* 处理段落文字
*/
private static void doParagraphs(XWPFDocument doc, Map<String, TableDataSourceInput> inputMap) {
List<XWPFParagraph> paragraphList = doc.getParagraphs();
if (paragraphList != null && paragraphList.size() > 0) {
for (XWPFParagraph paragraph : paragraphList) {
List<XWPFRun> runs = paragraph.getRuns();
for (XWPFRun run : runs) {
String text = run.getText(0);
if (!Strings.isNullOrEmpty(text)) {
String param = StringUtils.substringBetween(text, "{", "}");
if (inputMap.containsKey(param)) {
TableDataSourceInput input = (TableDataSourceInput) MapUtils.getObject(inputMap, param);
createTable(doc, paragraph, run, input.headers, input.dataSource);
}
}
}
}
}
}
// 动态表格
private static void createTable(XWPFDocument doc, XWPFParagraph paragraph, XWPFRun run, List<String> headers, List<List<String>> dataSource) {
run.setText("", 0);
XmlCursor cursor = paragraph.getCTP().newCursor();
XWPFTable tableOne = doc.insertNewTbl(cursor);// ---这个是关键
// 设置表格宽度,第一行宽度就可以了,这个值的单位,目前我也还不清楚,还没来得及研究
tableOne.setWidth(8500);
// 表格第一行,对于每个列,必须使用createCell(),而不是getCell(),因为第一行嘛,肯定是属于创建的,没有create哪里来的get呢
XWPFTableRow tableOneRowOne = tableOne.getRow(0);//行
AtomicInteger ao = new AtomicInteger(1);
int num = headers.size();
if (num == 0) return;
int width = 100 / num;
headers.forEach(i -> {
if (ao.getAndIncrement() == 1)
setWordCellSelfStyle(tableOneRowOne.getCell(0), "微软雅黑", "9", 0, "left", "top", "#000000", "#FFFFFF", "" + width + "%", i);
else
setWordCellSelfStyle(tableOneRowOne.createCell(), "微软雅黑", "9", 0, "left", "top", "#000000", "#FFFFFF", "" + width + "%", i);
});
// 动态添加数据
dataSource.forEach(row -> {
AtomicInteger pos = new AtomicInteger(0);
XWPFTableRow tableOneRowTwo = tableOne.createRow();//行
row.forEach(coll -> setWordCellSelfStyle(tableOneRowTwo.getCell(pos.getAndIncrement()), "微软雅黑", "9", 0, "left", "top", "#000000", "#FFFFFF", "" + width + "%", coll));
});
}
/**
* 设置表格样式
*/
private static void setWordCellSelfStyle(XWPFTableCell cell, String fontName, String fontSize, int fontBlod,
String alignment, String vertical, String fontColor,
String bgColor, String cellWidth, String content) {
if (null == cell) return;
//poi对字体大小设置特殊,不支持小数,但对原word字体大小做了乘2处理
BigInteger bFontSize = new BigInteger("24");
if (Strings.isNullOrEmpty(fontSize)) {
//poi对字体大小设置特殊,不支持小数,但对原word字体大小做了乘2处理
BigDecimal fontSizeBD = new BigDecimal(fontSize);
fontSizeBD = bd2.multiply(fontSizeBD);
fontSizeBD = fontSizeBD.setScale(0, BigDecimal.ROUND_HALF_UP);//这里取整
bFontSize = new BigInteger(fontSizeBD.toString());// 字体大小
}
// 设置单元格宽度
cell.setWidth(cellWidth);
//=====获取单元格
CTTc tc = cell.getCTTc();
//====tcPr开始====》》》》
CTTcPr tcPr = tc.getTcPr();//获取单元格里的<w:tcPr>
if (tcPr == null) {//没有<w:tcPr>,创建
tcPr = tc.addNewTcPr();
}
// --vjc开始-->>
CTVerticalJc vjc = tcPr.getVAlign();//获取<w:tcPr> 的<w:vAlign w:val="center"/>
if (vjc == null) {//没有<w:w:vAlign/>,创建
vjc = tcPr.addNewVAlign();
}
//设置单元格对齐方式
vjc.setVal(vertical.equals("top") ? STVerticalJc.TOP : vertical.equals("bottom") ? STVerticalJc.BOTTOM : STVerticalJc.CENTER); //垂直对齐
CTShd shd = tcPr.getShd();//获取<w:tcPr>里的<w:shd w:val="clear" w:color="auto" w:fill="C00000"/>
if (shd == null) {//没有<w:shd>,创建
shd = tcPr.addNewShd();
}
// 设置背景颜色
shd.setFill(bgColor.substring(1));
//《《《《====tcPr结束====
//====p开始====》》》》
CTP p = tc.getPList().get(0);//获取单元格里的<w:p w:rsidR="00C36068" w:rsidRPr="00B705A0" w:rsidRDefault="00C36068" w:rsidP="00C36068">
//---ppr开始--->>>
CTPPr ppr = p.getPPr();//获取<w:p>里的<w:pPr>
if (ppr == null) {//没有<w:pPr>,创建
ppr = p.addNewPPr();
}
// --jc开始-->>
CTJc jc = ppr.getJc();//获取<w:pPr>里的<w:jc w:val="left"/>
if (jc == null) {//没有<w:jc/>,创建
jc = ppr.addNewJc();
}
//设置单元格对齐方式
jc.setVal(alignment.equals("left") ? STJc.LEFT : alignment.equals("right") ? STJc.RIGHT : STJc.CENTER); //水平对齐
// <<--jc结束--
// --pRpr开始-->>
CTParaRPr pRpr = ppr.getRPr(); //获取<w:pPr>里的<w:rPr>
if (pRpr == null) {//没有<w:rPr>,创建
pRpr = ppr.addNewRPr();
}
CTFonts pfont = pRpr.getRFonts();//获取<w:rPr>里的<w:rFonts w:ascii="宋体" w:eastAsia="宋体" w:hAnsi="宋体"/>
if (pfont == null) {//没有<w:rPr>,创建
pfont = pRpr.addNewRFonts();
}
//设置字体
pfont.setAscii(fontName);
pfont.setEastAsia(fontName);
pfont.setHAnsi(fontName);
CTOnOff pb = pRpr.getB();//获取<w:rPr>里的<w:b/>
if (pb == null) {//没有<w:b/>,创建
pb = pRpr.addNewB();
}
//设置字体是否加粗
pb.setVal(fontBlod == 1 ? STOnOff.ON : STOnOff.OFF);
CTHpsMeasure psz = pRpr.getSz();//获取<w:rPr>里的<w:sz w:val="32"/>
if (psz == null) {//没有<w:sz w:val="32"/>,创建
psz = pRpr.addNewSz();
}
// 设置单元格字体大小
psz.setVal(bFontSize);
CTHpsMeasure pszCs = pRpr.getSzCs();//获取<w:rPr>里的<w:szCs w:val="32"/>
if (pszCs == null) {//没有<w:szCs w:val="32"/>,创建
pszCs = pRpr.addNewSzCs();
}
// 设置单元格字体大小
pszCs.setVal(bFontSize);
// <<--pRpr结束--
//<<<---ppr结束---
//---r开始--->>>
List<CTR> rlist = p.getRList(); //获取<w:p>里的<w:r w:rsidRPr="00B705A0">
CTR r;
if (rlist != null && rlist.size() > 0) {//获取第一个<w:r>
r = rlist.get(0);
} else {//没有<w:r>,创建
r = p.addNewR();
}
//--rpr开始-->>
CTRPr rpr = r.getRPr();//获取<w:r w:rsidRPr="00B705A0">里的<w:rPr>
if (rpr == null) {//没有<w:rPr>,创建
rpr = r.addNewRPr();
}
//->-
CTFonts font = rpr.getRFonts();//获取<w:rPr>里的<w:rFonts w:ascii="宋体" w:eastAsia="宋体" w:hAnsi="宋体" w:hint="eastAsia"/>
if (font == null) {//没有<w:rFonts>,创建
font = rpr.addNewRFonts();
}
//设置字体
font.setAscii(fontName);
font.setEastAsia(fontName);
font.setHAnsi(fontName);
CTOnOff b = rpr.getB();//获取<w:rPr>里的<w:b/>
if (b == null) {//没有<w:b/>,创建
b = rpr.addNewB();
}
//设置字体是否加粗
b.setVal(fontBlod == 1 ? STOnOff.ON : STOnOff.OFF);
CTColor color = rpr.getColor();//获取<w:rPr>里的<w:color w:val="FFFFFF" w:themeColor="background1"/>
if (color == null) {//没有<w:color>,创建
color = rpr.addNewColor();
}
// 设置字体颜色
if (!Strings.isNullOrEmpty(content) && content.contains("↓")) {
color.setVal("43CD80");
} else if (!Strings.isNullOrEmpty(content) && content.contains("↑")) {
color.setVal("943634");
} else {
color.setVal(fontColor.substring(1));
}
CTHpsMeasure sz = rpr.getSz();
if (sz == null) {
sz = rpr.addNewSz();
}
sz.setVal(bFontSize);
CTHpsMeasure szCs = rpr.getSzCs();
if (szCs == null) {
szCs = rpr.addNewSz();
}
szCs.setVal(bFontSize);
//-<-
//<<--rpr结束--
List<CTText> tlist = r.getTList();
CTText t;
if (tlist != null && tlist.size() > 0) {//获取第一个<w:r>
t = tlist.get(0);
} else {//没有<w:r>,创建
t = r.addNewT();
}
t.setStringValue(Strings.isNullOrEmpty(content) ? "" : content);
//<<<---r结束---
}
/********************动态表格结束****************************/
}
2.入参WordTplDataSourceInput
代码如下:
@Getter @Setter
public class WordTplDataSourceInput extends TableDataSourceInput {
ChartType chartType = ChartType.BAR;//类型
private String title;
private List<ChartData> charts = Lists.newArrayList();
private String paramColTitle;//参数列名称--》对应数据源(Excel)第一列的标题
String textParam = "table1";//模板参数定义表格:最终格式为:${table1},只保留里面
List<String> headers = Lists.newArrayList();// 标题/或表格头部
List<List<String>> dataSource = Lists.newArrayList();
}
3.测试用例
代码如下:
public class ChartExampleService {
@Test
public void chart_example_test() throws Exception {
final String fileInput = "C:\\file\\chart\\test\\line-chart-template.docx"; //
try (FileInputStream argIS = new FileInputStream(fileInput)) {
try (XWPFDocument doc = new XWPFDocument(argIS)) {// doc为模板文件
WordChartTplAndTableUtils.replaceChartTplAndCreateTable(doc, mockData());
// 保存返回
try (OutputStream out = new FileOutputStream("C:\\file\\chart\\test\\line_chart-demo-output.docx")) {
doc.write(out);
}
}
System.out.println("Done");
}
}
private List<WordTplDataSourceInput> mockData() {
List<WordTplDataSourceInput> result = Lists.newArrayList();
WordTplDataSourceInput input1 = new WordTplDataSourceInput();
input1.setChartType(ChartType.BAR);
input1.setCharts(chartData());
input1.setTitle("请假统计分析");
input1.setParamColTitle("年月");
result.add(input1);
return result;
}
/**
* 数据集
*/
private List<ChartData> chartData() {
List<ChartData> result = Lists.newArrayList();
result.add(ChartData.of("测试-请假类型", "202001", BigDecimal.valueOf(4)));
result.add(ChartData.of("测试-请假类型", "202002", BigDecimal.valueOf(4)));
result.add(ChartData.of("测试-请假类型", "202003", BigDecimal.valueOf(3)));
result.add(ChartData.of("请假测试类型", "202001", BigDecimal.valueOf(1.3)));
result.add(ChartData.of("请假测试类型", "202002", BigDecimal.valueOf(2)));
return result.stream()
.sorted(Comparator.comparing(ChartData::getGroup)
.thenComparing(ChartData::getName))
.collect(Collectors.toList());
}
}
参考官网例子
可以导POI官网查看相关资料,例子的网址为:
https://poi.apache.org/components/spreadsheet/examples.html#linked-dropdown