支持在pptx中动态刷新各种图表数据柱状图、饼状图、折线图、柱状折现组合图等
编写的工具类。
public class PoiPPTGrapthUtils {
private static Logger logger =Logger.getLogger(PoiPPTGrapthUtils.class);
@Resource
private ReadXmlUtil readXml;
@Resource
private IExportBatchWordService iExportBatchWordService;
public static void main(String[] args) {
//String templateFile = “D://ZHRISK_TEMPLETE.pptx”;
//String destFile = “D://dest.pptx”;
String templateFile = “D://weituimage.pptx”;
String destFile = “D://weituimage11.pptx”;
logger.info(“templateFile” + templateFile + " destFile:" + destFile);
XMLSlideShow pptx = null;
try {
//准备模拟数据
pptx = new XMLSlideShow(new FileInputStream(templateFile));
List<BaseFormMap> dataList=new ArrayList<BaseFormMap>();//数据
List<String> fldNameArr = new ArrayList<String>();// 字段名
List<String> titleArr = new ArrayList<String>();// 标题
BaseFormMap baseFormMap=new BaseFormMap();
List<String> showtailArr=new ArrayList<String>();
List<String> ispercentArr=new ArrayList<String>();
// 销售额
// 第一季度 8.2
// 第二季度 3.2
// 第三季度 1.4
// 第四季度 1.2
baseFormMap.put(“value1”, “2002/1/5”);
baseFormMap.put(“value2”, new BigDecimal(0.300));
baseFormMap.put(“value3”, new BigDecimal(0.500));
BaseFormMap baseFormMap1=new BaseFormMap();
baseFormMap1.put("value1", "2002/1/6");
baseFormMap1.put("value2", new BigDecimal(0.300));
baseFormMap1.put("value3", new BigDecimal(0.400));
BaseFormMap baseFormMap2=new BaseFormMap();
baseFormMap2.put("value1", "2002/1/7");
baseFormMap2.put("value2", new BigDecimal(0.200));
baseFormMap2.put("value3", new BigDecimal(0.300));
BaseFormMap baseFormMap3=new BaseFormMap();
baseFormMap3.put("value1", "2002/1/8");
baseFormMap3.put("value2", new BigDecimal(0.200));
baseFormMap3.put("value3", new BigDecimal(0.400));
fldNameArr.add("value1");
fldNameArr.add("value2");
fldNameArr.add("value3");
titleArr.add("类型");
titleArr.add("买入");
titleArr.add("卖出");
showtailArr.add("0");
showtailArr.add("3");
showtailArr.add("3");
ispercentArr.add("0");
ispercentArr.add("1");
ispercentArr.add("1");
dataList.add(baseFormMap);
dataList.add(baseFormMap1);
dataList.add(baseFormMap2);
dataList.add(baseFormMap3);
//PPTGrapthUtils.judgeGraphSheetType(pptx);
for (XSLFSlide slide : pptx.getSlides()) {
for (POIXMLDocumentPart part : slide.getRelations()) {
if (part instanceof XSLFChart) {
boolean result = refreshLineGraph(pptx, (XSLFChart)part, dataList, fldNameArr, titleArr,showtailArr,ispercentArr);
logger.info(result);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
// 保存文件
OutputStream out;
try {
out = new FileOutputStream("D://weituimage11.pptx");
pptx.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public String createNewPPT(String templateFile, String destFile) {
String clearId="";
String suiteId="";
int serial=0;
String unitcode="";
String type="";
String result = “success”;
XMLSlideShow pptx = null;
try {
// 打开模板ppt
pptx = new XMLSlideShow(new FileInputStream(templateFile));
//PPTGrapthUtils.judgeGraphSheetType(pptx);
for (XSLFSlide slide : pptx.getSlides()) {
for (POIXMLDocumentPart part : slide.getRelations()) {
if (part instanceof XSLFChart) {
updateGraph(pptx, (XSLFChart) part, clearId, suiteId, serial, unitcode, type);
}
}
}
// 保存文件
OutputStream out = new FileOutputStream(destFile);
pptx.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
result = e.toString();
} finally {
if (pptx != null) {
try {
pptx.close();
} catch (IOException e) {
result = e.toString();
}
}
}
return result;
}
public boolean updateGraph(XMLSlideShow pptx,XSLFChart chart,String clearId, String suiteId, int serial,
String unitcode, String type) {
boolean result = false;
try {
String content = readXml.readFormat(serial, true);
content = content.replaceFirst(“GB2312”, “UTF-8”);
List<String> list_seccategory = readXml.getSingleNode(content,"seccategory");
String subName = list_seccategory.get(0);
List<String> arr_fldname = new ArrayList<String>();// 字段名
List<String> arr_showtext = new ArrayList<String>();// 显示文本
List<String> arr_fldtype = new ArrayList<String>();// 字段类型，0文本，1数字，3日期
List<String> arr_datarow = new ArrayList<String>();// 数据行属性
List<String> arr_datatype = new ArrayList<String>();// 数据类型
List<String> arr_showtail = new ArrayList<String>();//显示百分比
List<String> arr_ispercent = new ArrayList<String>();//小数点
// 读取表格配置信息
List<String[]> list_format = readXml.readClob(content, type);
String[] s = new String[36];
// 解析表格配置信息
for (int loopi = 0; loopi < list_format.size(); loopi++) {//
// 开始
s = list_format.get(loopi);
arr_fldtype.add(s[0]);
arr_fldname.add(s[1]);
arr_showtext.add(s[2]);
arr_datarow.add((s[13] == null ? "0" : s[13]));// 数据行：否、是
arr_datatype.add((s[14] == null ? "0" : s[14]));// 数据类型：0文本、1数据、2日期
arr_showtail.add(s[5]);
arr_ispercent.add(s[6]);
} // 每个表格元素
int index = 0;
int maxRow = readXml.rowcount(content); // 总行数
int maxCol = readXml.colcount(content, type);// 总列数
// 显示标题行
index = 0;
// 显示标题行 循环
List<String> fldNameArr = new ArrayList<String>();// 字段名
List<String> titleArr = new ArrayList<String>();// 标题
List<String> showtailArr = new ArrayList<String>();//显示百分比
List<String> ispercentArr = new ArrayList<String>();//小数点
for (int i = 0; i < maxRow; i++) {
index = i * maxCol;
// 数据行：0否、1是
if (arr_datarow.get(index).trim().equals("1") ) {
for (int colIndex = 0; colIndex < maxCol; colIndex++) {
// 存储-类型：字段value、文本name、公式formula
if (arr_fldtype.get(index).equals("value")) {
String fldName = arr_fldname.get(index);
fldNameArr.add(fldName);
showtailArr.add(arr_showtail.get(index));
ispercentArr.add(arr_ispercent.get(index));
}
index++;// 循环单元格加1
}
}else {
// 循环列
for (int colIndex = 0; colIndex < maxCol; colIndex++) {
// 存储-类型：字段value、文本name、公式formula
if (!arr_fldtype.get(index).trim().equals("value")) {
String titleText = arr_showtext.get(index);
titleArr.add(titleText);
}
index++;// 循环单元格加1
}
}
}// 显示标题行 循环结束
// 节点6401、6406、6151、6420、6045、6801、7101、6350 查询这些节点数据
List<BaseFormMap> dataList = null;
if (unitcode.equals("6045")) {
dataList = iExportBatchWordService.getNodeTablesInfoBy6045(suiteId, subName);
} else if (unitcode.equals("6151")) {
dataList = iExportBatchWordService.getNodeTablesInfoBy6151(suiteId, subName);
} else if (unitcode.equals("6202")) {
dataList = iExportBatchWordService.getNodeTablesInfoBy6202(suiteId, subName);
} else if (unitcode.equals("6350")) {
dataList = iExportBatchWordService.getNodeTablesInfoBy6350(suiteId, subName);
} else if (unitcode.equals("6420")) {
dataList = iExportBatchWordService.getNodeTablesInfoBy6420(suiteId, "", "1", subName);
} else if (unitcode.equals("6503")) {
dataList = iExportBatchWordService.getNodeTablesInfoBy6503(suiteId, subName);
} else if (unitcode.equals("6801")) {
dataList = iExportBatchWordService.getNodeTablesInfoBy6801(suiteId, subName);
} else if (unitcode.equals("7101")) {
dataList = iExportBatchWordService.getNodeTablesInfoBy7101(suiteId, subName);
} else if (unitcode.equals("6172")) {
dataList = iExportBatchWordService.getNodeTables6172(clearId, subName);
} else {
dataList = iExportBatchWordService.getNodeTablesInfoOther(suiteId, subName);
}
String chartType = getGraphType(chart);
if ("pie".equalsIgnoreCase(chartType)) {
result = refreshPieGraph(pptx,chart,dataList,fldNameArr,titleArr,showtailArr,ispercentArr);
} else if ("bar".equalsIgnoreCase(chartType)) {
result = refreshBarGraph(pptx,chart, dataList,fldNameArr,titleArr,showtailArr,ispercentArr);
} else if ("line".equalsIgnoreCase(chartType)) {
result = refreshLineGraph(pptx,chart, dataList,fldNameArr,titleArr,showtailArr,ispercentArr);
} else if ("area".equalsIgnoreCase(chartType)) {
result = refreshAreaGraph(pptx,chart, dataList,fldNameArr,titleArr,showtailArr,ispercentArr);
}
logger.info("updateGraph type:" + type);
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
/**
* 通过图表类型
*
* @param chart
* @return
*/
private String getGraphType(XSLFChart chart) {
String graphTye = “noSupport”;
CTPlotArea plot = chart.getCTChart().getPlotArea();
if (null != plot && plot.getBarChartList().size() > 0) {
graphTye = “bar”;
} else if (null != plot && plot.getPieChartList().size() > 0) {
graphTye = “pie”;
} else if (null != plot && plot.getLineChartList().size() > 0) {
graphTye = “line”;
} else if (null != plot && plot.getAreaChartList().size() > 0) {
graphTye = “area”;
}
return graphTye;
}
//
/**
* @Description: 刷新圆饼图
* @param pptx
* @param chart
* @param dataList
* @param fldNameArr
* @param titleArr
* @param showtailArr
* @param ispercentArr
* @return
* @author os-nieds
*/
private static boolean refreshPieGraph(XMLSlideShow pptx,XSLFChart chart,List dataList,List fldNameArr,List titleArr,List showtailArr,List ispercentArr) {
boolean result = false;
//根据属性第一列名称切换数据类型
CTChart ctChart = chart.getCTChart();
//获取标题中隐藏的类型
String type=getType(ctChart);
//刷新内置excel数据
result=refreshExcel(chart,dataList,fldNameArr,titleArr,showtailArr,ispercentArr);
CTPlotArea plotArea = ctChart.getPlotArea();
if(type.contains("pie")){//简单饼状
CTPieChart pieChart = plotArea.getPieChartArray(0);
List<CTPieSer> pieSerList = pieChart.getSerList();
result=refreshStrGraphContent(pieChart,pieSerList,dataList,fldNameArr,titleArr,showtailArr,ispercentArr,1);
}else if(type.contains("3D")){//3D
CTPie3DChart pie3DChart = plotArea.getPie3DChartArray(0);
List<CTPieSer> pie3DSerList = pie3DChart.getSerList();
result=refreshStrGraphContent(pie3DChart,pie3DSerList,dataList,fldNameArr,titleArr,showtailArr,ispercentArr,1);
}else{//c:doughnutChart环状
CTDoughnutChart doughnutChart = plotArea.getDoughnutChartArray(0);
List<CTPieSer> pieDoughnutSerList = doughnutChart.getSerList();
result=refreshStrGraphContent(doughnutChart,pieDoughnutSerList,dataList,fldNameArr,titleArr,showtailArr,ispercentArr,1);
}
return result;
}
/**
* @Description: 刷新Excel数据
* @param chart
* @param dataList
* @param fldNameArr
* @param titleArr
* @param showtailArr
* @param ispercentArr
* @return boolean
* @author os-nieds
*/
private static boolean refreshExcel(XSLFChart chart,
List dataList,List fldNameArr,List titleArr,
List showtailArr,List ispercentArr) {
boolean result = true;
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet();
//根据数据创建excel第一行标题行
for (int i = 0; i < titleArr.size(); i++) {
if(sheet.getRow(0)==null){
sheet.createRow(0).createCell(i).setCellValue(titleArr.get(i)==null?"":titleArr.get(i));
}else{
sheet.getRow(0).createCell(i).setCellValue(titleArr.get(i)==null?"":titleArr.get(i));
}
}
//遍历数据行
for (int i = 0; i < dataList.size(); i++) {
BaseFormMap baseFormMap = dataList.get(i);//数据行
//fldNameArr字段属性
for (int j = 0; j < fldNameArr.size(); j++) {
if(sheet.getRow(i+1)==null){
if(j==0){
try {
sheet.createRow(i+1).createCell(j).setCellValue(baseFormMap.getStr(fldNameArr.get(j))==null?"":baseFormMap.getStr(fldNameArr.get(j)));
} catch (Exception e) {
if(baseFormMap.getStr(fldNameArr.get(j))==null){
sheet.createRow(i+1).createCell(j).setCellValue("");
}else{
sheet.createRow(i+1).createCell(j).setCellValue(baseFormMap.getDate(fldNameArr.get(j)));
}
}
}
}else{
BigDecimal b=baseFormMap.getBigDecimal(fldNameArr.get(j));
double value=0d;
if(b!=null){
value=b.doubleValue();
}
if(value==0){
sheet.getRow(i+1).createCell(j);
}else{
sheet.getRow(i+1).createCell(j).setCellValue(baseFormMap.getBigDecimal(fldNameArr.get(j)).doubleValue());
}
if("1".equals(ispercentArr.get(j))){//是否设置百分比
// 设置Y轴的数字为百分比样式显示
StringBuilder sb=new StringBuilder();
if("0".equals(showtailArr.get(j))){//保留几位小数
sb.append("0");
if("1".equals(ispercentArr.get(j))){//是否百分比
sb.append("%");
}
}else{
sb.append("0.");
for(int k=0;k<Integer.parseInt(showtailArr.get(j));k++){
sb.append("0");
}
if("1".equals(ispercentArr.get(j))){//是否百分比
sb.append("%");
}
}
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(wb.createDataFormat().getFormat(sb.toString()));
sheet.getRow(i+1).getCell(j).setCellStyle(cellStyle);
}else{
//是否设置百分比
// 设置Y轴的数字为百分比样式显示
StringBuilder sb=new StringBuilder();
if("0".equals(showtailArr.get(j))){//保留几位小数
sb.append("0");
}else{
sb.append("0.");
for(int k=0;k<Integer.parseInt(showtailArr.get(j));k++){
sb.append("0");
}
}
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(wb.createDataFormat().getFormat(sb.toString()));
sheet.getRow(i+1).getCell(j).setCellStyle(cellStyle);
}
}
}
}
// 更新嵌入的workbook
POIXMLDocumentPart xlsPart = chart.getRelations().get(2);
OutputStream xlsOut = xlsPart.getPackagePart().getOutputStream();
try {
wb.write(xlsOut);
xlsOut.close();
} catch (IOException e) {
e.printStackTrace();
result = false;
} finally {
if (wb != null) {
try {
wb.close();
} catch (IOException e) {
e.printStackTrace();
result = false;
}
}
}
return result;
}
/**
* @Description: 刷新柱状图
* @param pptx
* @param chart
* @param dataList
* @param fldNameArr
* @param titleArr
* @param showtailArr 小数位
* @param ispercentArr 显示百分比 0否，1是
* @return boolean
* @author os-nieds
*/
private static boolean refreshBarGraph(XMLSlideShow pptx,XSLFChart chart,List dataList,List fldNameArr,
List titleArr,List showtailArr,List ispercentArr) {
boolean result = false;
//根据属性第一列名称切换数据类型
CTChart ctChart = chart.getCTChart();
CTPlotArea plotArea = ctChart.getPlotArea();
CTBarChart barChart = plotArea.getBarChartArray(0);
List<CTBarSer> barSerList = barChart.getSerList();
//获取标题中隐藏的类型
String type=getType(ctChart);
//刷新内置excel数据
result=refreshExcel(chart,dataList,fldNameArr,titleArr,showtailArr,ispercentArr);
if(type.contains("bar1")||type.contains("bar2")){//交易情况-2,3,行业配置1,2
result=refreshStrGraphContent(barChart,barSerList,dataList,fldNameArr,titleArr,showtailArr,ispercentArr,1);
}else if(type.contains("bar-line")){//交易情况-1
//更新数据区域bar
result=refreshDateGraphContent(barChart,barSerList,dataList,fldNameArr,titleArr,showtailArr,ispercentArr,1);
//更新数据区域line
CTLineChart lineChart = plotArea.getLineChartArray(0);
List<CTLineSer> lineSerList = lineChart.getSerList();
result=refreshDateGraphContent(lineChart,lineSerList,dataList,fldNameArr,titleArr,showtailArr,ispercentArr,3);
}
return result;
}
//获取隐藏类型
private static String getType(CTChart ctChart) {
//获取图标题隐藏的类型,获取完之后获取标题之后将数据清空
String type="";
String title="";
CTRegularTextRun[] ctArr = ctChart.getTitle().getTx().getRich().getPArray(0).getRArray();
for (int i = 0; i < ctArr.length; i++) {
if (i0) {
type=ctArr[0].getT();
logger.info(type);
ctArr[i].setT("");
}else if(i1){
title=ctArr[1].getT();
logger.info(title);
}else{
ctArr[i].setT("");
}
}
return type;
}
/**
* @Description:刷新数据方法(str进行分类)
* @param typeChart:传递的图形类型
* @param serList:传递的serList(数据)
* @param dataList:显示的数据
* @param fldNameArr:属性列
* @param position:取数的列
* @return boolean
*/
private static boolean refreshStrGraphContent(Object typeChart,
List<?> serList, List dataList, List fldNameArr,List titleArr,ListshowtailArr,List ispercentArr, int position) {
boolean result=true;
//更新数据区域
for (int i = 0; i < serList.size(); i++) {
CTSerTx tx=null;
CTAxDataSource cat=null;
CTNumDataSource val=null;
//获取序列
if(typeChart instanceof CTPieChart){//普通饼状
CTPieSer ser = ((CTPieChart)typeChart).getSerArray(i);
tx= ser.getTx();
// Category Axis Data
cat= ser.getCat();
// 获取图表的值
val = ser.getVal();
}else if(typeChart instanceof CTPie3DChart){//3D饼状
CTPieSer ser = ((CTPie3DChart)typeChart).getSerArray(i);
tx= ser.getTx();
// Category Axis Data
cat= ser.getCat();
// 获取图表的值
val = ser.getVal();
}else if(typeChart instanceof CTBarChart){//柱状图简单
CTBarSer ser = ((CTBarChart)typeChart).getSerArray(i);
tx= ser.getTx();
// Category Axis Data
cat= ser.getCat();
// 获取图表的值
val = ser.getVal();
}else if(typeChart instanceof CTLineChart){//简单折线图
CTLineSer ser = ((CTLineChart)typeChart).getSerArray(i);
tx= ser.getTx();
// Category Axis Data
cat= ser.getCat();
// 获取图表的值
val = ser.getVal();
}else if(typeChart instanceof CTAreaChart){
CTAreaSer ser = ((CTAreaChart)typeChart).getSerArray(i);
tx= ser.getTx();
// Category Axis Data
cat= ser.getCat();
// 获取图表的值
val = ser.getVal();
}else if(typeChart instanceof CTDoughnutChart){
CTPieSer ser = ((CTDoughnutChart)typeChart).getSerArray(i);
tx= ser.getTx();
// Category Axis Data
cat= ser.getCat();
// 获取图表的值
val = ser.getVal();
}else{
logger.info("图形类型不存在");
}
// strData.set
CTStrData strData = cat.getStrRef().getStrCache();
CTNumData numData = val.getNumRef().getNumCache();
strData.setPtArray((CTStrVal[]) null); // unset old axis text
numData.setPtArray((CTNumVal[]) null); // unset old values
// set model
long idx = 0;
for (int j=0;j<dataList.size();j++) {
//判断获取的值是否为空
String value = "0";
if(dataList.get(j).getBigDecimal(fldNameArr.get(i+position))!=null){
value=dataList.get(j).getBigDecimal(fldNameArr.get(i+position)).toString();
}
if(!"0".equals(value)){
CTNumVal numVal = numData.addNewPt();//序列值
numVal.setIdx(idx);
numVal.setV(value);
}
CTStrVal sVal = strData.addNewPt();//序列名称
sVal.setIdx(idx);
sVal.setV(dataList.get(j).getStr(fldNameArr.get(0)));
idx++;
}
numData.getPtCount().setVal(idx);
strData.getPtCount().setVal(idx);
//赋值横坐标数据区域
String axisDataRange = new CellRangeAddress(1, dataList.size(), 0, 0)
.formatAsString("Sheet0", true);
cat.getStrRef().setF(axisDataRange);
tx.getStrRef().getStrCache().getPtArray(0).setV(titleArr.get(i+position));
//图例区域
String legendDataRange = new CellRangeAddress(0, 0, i+position, i+position)
.formatAsString("Sheet0", true);
tx.getStrRef().setF(legendDataRange);
//数据区域
String numDataRange = new CellRangeAddress(1, dataList.size(), i+position, i+position)
.formatAsString("Sheet0", true);
val.getNumRef().setF(numDataRange);
if("1".equals(ispercentArr.get(i+position))){//是否设置百分比
// 设置Y轴的数字为百分比样式显示
StringBuilder sb=new StringBuilder();
if("0".equals(showtailArr.get(i+position))){//保留几位小数
sb.append("0");
if("1".equals(ispercentArr.get(i+position))){//是否百分比
sb.append("%");
}
}else{
sb.append("0.");
for(int k=0;k<Integer.parseInt(showtailArr.get(i+position));k++){
sb.append("0");
}
if("1".equals(ispercentArr.get(i+position))){//是否百分比
sb.append("%");
}
}
val.getNumRef().getNumCache().setFormatCode(sb.toString());
}else{
//是否设置百分比
// 设置Y轴的数字为百分比样式显示
StringBuilder sb=new StringBuilder();
if("0".equals(showtailArr.get(i+position))){//保留几位小数
sb.append("0");
}else{
sb.append("0.");
for(int k=0;k<Integer.parseInt(showtailArr.get(i+position));k++){
sb.append("0");
}
}
val.getNumRef().getNumCache().setFormatCode(sb.toString());
}
}
return result;
}
/**
* @Description:刷新数据方法(日期进行分类)
* @param typeChart:传递的图形类型
* @param serList:传递的serList(数据)
* @param dataList:显示的数据
* @param fldNameArr:属性列
* @param position:取数的列
* @return boolean
* @author os-nieds
*/
private static boolean refreshDateGraphContent(Object typeChart,
List<?> serList, List dataList, List fldNameArr, List titleArr,ListshowtailArr,List ispercentArr,int position) {
boolean result=true;
//更新数据区域
for (int i = 0; i < serList.size(); i++) {
CTSerTx tx=null;
CTAxDataSource cat=null;
CTNumDataSource val=null;
//获取序列
if(typeChart instanceof CTPieChart){//普通饼状
CTPieSer ser = ((CTPieChart)typeChart).getSerArray(i);
tx= ser.getTx();
// Category Axis Data
cat= ser.getCat();
// 获取图表的值
val = ser.getVal();
}else if(typeChart instanceof CTPie3DChart){//3D饼状
CTPieSer ser = ((CTPie3DChart)typeChart).getSerArray(i);
tx= ser.getTx();
// Category Axis Data
cat= ser.getCat();
// 获取图表的值
val = ser.getVal();
}else if(typeChart instanceof CTBarChart){//柱状图简单
CTBarSer ser = ((CTBarChart)typeChart).getSerArray(i);
tx= ser.getTx();
// Category Axis Data
cat= ser.getCat();
// 获取图表的值
val = ser.getVal();
}else if(typeChart instanceof CTLineChart){//简单折线图
CTLineSer ser = ((CTLineChart)typeChart).getSerArray(i);
tx= ser.getTx();
// Category Axis Data
cat= ser.getCat();
// 获取图表的值
val = ser.getVal();
}else if(typeChart instanceof CTAreaChart){
CTAreaSer ser = ((CTAreaChart)typeChart).getSerArray(i);
tx= ser.getTx();
// Category Axis Data
cat= ser.getCat();
// 获取图表的值
val = ser.getVal();
}else if(typeChart instanceof CTDoughnutChart){
CTPieSer ser = ((CTDoughnutChart)typeChart).getSerArray(i);
tx= ser.getTx();
// Category Axis Data
cat= ser.getCat();
// 获取图表的值
val = ser.getVal();
}else{
logger.info(“图形类型不存在”);
}
// strData.set
CTNumData strData = cat.getNumRef().getNumCache();
CTNumData numData = val.getNumRef().getNumCache();
strData.setPtArray((CTNumVal[]) null); // unset old axis text
numData.setPtArray((CTNumVal[]) null); // unset old values
// set model
long idx = 0;
for (int j=0;j<dataList.size();j++) {
//判断获取的值是否为空
String value = "0";
if(dataList.get(j).getBigDecimal(fldNameArr.get(i+position))!=null){
value=dataList.get(j).getBigDecimal(fldNameArr.get(i+position)).toString();
}
if(!"0".equals(value)){
CTNumVal numVal = numData.addNewPt();//序列值
numVal.setIdx(idx);
numVal.setV(value);
}
CTNumVal sVal = strData.addNewPt();//序列名称
sVal.setIdx(idx);
String days=getDaysSum(dataList.get(j).getStr(fldNameArr.get(0)));
sVal.setV(days);
idx++;
}
numData.getPtCount().setVal(idx);
strData.getPtCount().setVal(idx);
//赋值横坐标数据区域
String axisDataRange = new CellRangeAddress(1, dataList.size(), 0, 0)
.formatAsString("Sheet0", true);
cat.getNumRef().setF(axisDataRange);
//图例区域
String legendDataRange = new CellRangeAddress(0, 0, i+position, i+position)
.formatAsString("Sheet0", true);
tx.getStrRef().setF(legendDataRange);
tx.getStrRef().getStrCache().getPtArray(0).setV(titleArr.get(i+position));
//数据区域
String numDataRange = new CellRangeAddress(1, dataList.size(), i+position, i+position)
.formatAsString("Sheet0", true);
val.getNumRef().setF(numDataRange);
if("1".equals(ispercentArr.get(i+position))){//是否设置百分比
// 设置Y轴的数字为百分比样式显示
StringBuilder sb=new StringBuilder();
if("0".equals(showtailArr.get(i+position))){//保留几位小数
sb.append("0");
if("1".equals(ispercentArr.get(i+position))){//是否百分比
sb.append("%");
}
}else{
sb.append("0.");
for(int k=0;k<Integer.parseInt(showtailArr.get(i+position));k++){
sb.append("0");
}
if("1".equals(ispercentArr.get(i+position))){//是否百分比
sb.append("%");
}
}
val.getNumRef().getNumCache().setFormatCode(sb.toString());
}else{
//是否设置百分比
// 设置Y轴的数字为百分比样式显示
StringBuilder sb=new StringBuilder();
if("0".equals(showtailArr.get(i+position))){//保留几位小数
sb.append("0");
}else{
sb.append("0.");
for(int k=0;k<Integer.parseInt(showtailArr.get(i+position));k++){
sb.append("0");
}
}
val.getNumRef().getNumCache().setFormatCode(sb.toString());
}
}
return result;
}
//获取天数从1900年1月1日到endYear
private static String getDaysSum(String endYear) {
String endYearArr[]=endYear.split("/");
int year=Integer.parseInt(endYearArr[0]);
int month=Integer.parseInt(endYearArr[1]);
int day=Integer.parseInt(endYearArr[2]);
LocalDate start=new LocalDate(1900, 1,1);
LocalDate end=new LocalDate(year,month,day);
int days = Days.daysBetween(start, end).getDays()+2;
return days+"";
}
/**
* @Description:刷新线性图
* @param pptx
* @param chart
* @param dataList
* @param fldNameArr
* @param titleArr
* @param showtailArr
* @param ispercentArr
* @return
* @author os-nieds
*/
private static boolean refreshLineGraph(XMLSlideShow pptx,XSLFChart chart,List dataList,List fldNameArr,
List titleArr,List showtailArr,List ispercentArr) {
boolean result = false;
//根据属性第一列名称切换数据类型
CTChart ctChart = chart.getCTChart();
CTPlotArea plotArea = ctChart.getPlotArea();
//更新数据区域line
CTLineChart lineChart = plotArea.getLineChartArray(0);
List lineSerList = lineChart.getSerList();
//获取标题中隐藏的类型
String type=getType(ctChart);
logger.info(type);
//刷新内置excel数据
result=refreshExcel(chart,dataList,fldNameArr,titleArr,showtailArr,ispercentArr);
//刷新图表数据
result=refreshDateGraphContent(lineChart,lineSerList,dataList,fldNameArr,titleArr,showtailArr,ispercentArr,1);
return result;
}
/**
* @Description:刷新面积图(只支持pptx中的area前两种)
* @param pptx
* @param chart
* @param dataList
* @param fldNameArr
* @param titleArr
* @param showtailArr
* @param ispercentArr
* @return
* @author os-nieds
*/
private static boolean refreshAreaGraph(XMLSlideShow pptx,XSLFChart chart,List dataList,List fldNameArr,List titleArr,ListshowtailArr,List ispercentArr) {
boolean result = false;
//根据属性第一列名称切换数据类型
CTChart ctChart = chart.getCTChart();
CTPlotArea plotArea = ctChart.getPlotArea();
CTAreaChart areaChart = plotArea.getAreaChartArray(0);
List areaSerList = areaChart.getSerList();
//获取标题中隐藏的类型
String type=getType(ctChart);
logger.info(type);
//刷新内置excel数据
result=refreshExcel(chart,dataList,fldNameArr,titleArr,showtailArr,ispercentArr);
//刷新图表数据
result=refreshDateGraphContent(areaChart,areaSerList,dataList,fldNameArr,titleArr,showtailArr,ispercentArr,1);
return result;
}
9435
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
