使用POI在Excel中动态生成图表工具类(支持柱状、组合、环状图、折线图、等)
使用POI在Excel中动态生成图表工具类
转载：https://blog.csdn.net/wangxiaoyingWXY/article/details/83218341#commentBox
由于公司是一个生成报表的机构，之前一直使用pageOffice，但是公司领导就是不买，你说公司那样有钱磨磨唧唧干啥，所以只好换成了开源免费的poi，经过两个星期的研究终于完成了一个工具类。
废话不多说直接上代码：
-
pom引入需要的jar包
import java.util.*;public class BaseFormMapimport java.io.FileOutputStream; import java.math.BigDecimal; import java.util.ArrayList; import java.util.Date; import java.util.List;import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFChart; import org.openxmlformats.schemas.drawingml.x2006.chart.CTAreaChart; import org.openxmlformats.schemas.drawingml.x2006.chart.CTAreaSer; import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource; import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarChart; import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer; import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean; import org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx; import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart; import org.openxmlformats.schemas.drawingml.x2006.chart.CTDLbls; import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend; import org.openxmlformats.schemas.drawingml.x2006.chart.CTLineChart; import org.openxmlformats.schemas.drawingml.x2006.chart.CTLineSer; import org.openxmlformats.schemas.drawingml.x2006.chart.CTMarker; import org.openxmlformats.schemas.drawingml.x2006.chart.CTMarkerStyle; import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource; import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef; import org.openxmlformats.schemas.drawingml.x2006.chart.CTPie3DChart; import org.openxmlformats.schemas.drawingml.x2006.chart.CTPieChart; import org.openxmlformats.schemas.drawingml.x2006.chart.CTPieSer; import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea; import org.openxmlformats.schemas.drawingml.x2006.chart.CTScaling; import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx; import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef; import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx; import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos; import org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir; import org.openxmlformats.schemas.drawingml.x2006.chart.STBarGrouping; import org.openxmlformats.schemas.drawingml.x2006.chart.STCrossBetween; import org.openxmlformats.schemas.drawingml.x2006.chart.STCrosses; import org.openxmlformats.schemas.drawingml.x2006.chart.STLblAlgn; import org.openxmlformats.schemas.drawingml.x2006.chart.STTickMark; import org.openxmlformats.schemas.drawingml.x2006.chart.STBarGrouping.Enum; import org.openxmlformats.schemas.drawingml.x2006.chart.STDispBlanksAs; import org.openxmlformats.schemas.drawingml.x2006.chart.STGrouping; import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos; import org.openxmlformats.schemas.drawingml.x2006.chart.STMarkerStyle; import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation; import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;public class ExcelChartUtil {public static void main(String[] args) throws Exception {boolean result=false;ListdataList=new ArrayList ();//数据List fldNameArr = new ArrayList ();// 字段名List titleArr = new ArrayList ();// 标题BaseFormMap baseFormMap=new BaseFormMap();List showtailArr=new ArrayList ();List ispercentArr=new ArrayList ();baseFormMap.put("value1", "股票");baseFormMap.put("value2", new BigDecimal(new java.util.Random().nextDouble()));baseFormMap.put("value3", new BigDecimal(new java.util.Random().nextDouble()));baseFormMap.put("value4", new BigDecimal(new java.util.Random().nextDouble()));BaseFormMap baseFormMap1=new BaseFormMap();baseFormMap1.put("value1", "货币型基金");baseFormMap1.put("value2", new BigDecimal(new java.util.Random().nextDouble()));baseFormMap1.put("value3", new BigDecimal(new java.util.Random().nextDouble()));baseFormMap1.put("value4", new BigDecimal(new java.util.Random().nextDouble()));BaseFormMap baseFormMap2=new BaseFormMap();baseFormMap2.put("value1", "可转债");baseFormMap2.put("value2", new BigDecimal(new java.util.Random().nextDouble()));baseFormMap2.put("value3", new BigDecimal(new java.util.Random().nextDouble()));baseFormMap2.put("value4", new BigDecimal(new java.util.Random().nextDouble()));BaseFormMap baseFormMap3=new BaseFormMap();baseFormMap3.put("value1", "买入返售");baseFormMap3.put("value2", new BigDecimal(new java.util.Random().nextDouble()));baseFormMap3.put("value3", new BigDecimal(new java.util.Random().nextDouble()));baseFormMap3.put("value4", new BigDecimal(new java.util.Random().nextDouble()));BaseFormMap baseFormMap4=new BaseFormMap();baseFormMap4.put("value1", "通知存款");baseFormMap4.put("value2", new BigDecimal(new java.util.Random().nextDouble()));baseFormMap4.put("value3", new BigDecimal(new java.util.Random().nextDouble()));baseFormMap4.put("value4", new BigDecimal(new java.util.Random().nextDouble()));BaseFormMap baseFormMap5=new BaseFormMap();baseFormMap5.put("value1", "当月累计");baseFormMap5.put("value2", new BigDecimal(new java.util.Random().nextDouble()));baseFormMap5.put("value3", new BigDecimal(new java.util.Random().nextDouble()));baseFormMap5.put("value4", new BigDecimal(new java.util.Random().nextDouble()));fldNameArr.add("value1");fldNameArr.add("value2");fldNameArr.add("value3");fldNameArr.add("value4");titleArr.add("类型");titleArr.add("买入");titleArr.add("卖出");titleArr.add("分红");showtailArr.add("0");showtailArr.add("2");showtailArr.add("2");showtailArr.add("2");ispercentArr.add("0");ispercentArr.add("1");ispercentArr.add("1");ispercentArr.add("1");dataList.add(baseFormMap);dataList.add(baseFormMap1);dataList.add(baseFormMap2);dataList.add(baseFormMap3);dataList.add(baseFormMap4);dataList.add(baseFormMap5);SXSSFWorkbook wb = new SXSSFWorkbook();SXSSFSheet sheet = wb.createSheet("Sheet1");result=createChart(wb,sheet,10,"bar",STBarGrouping.STACKED,false,false,dataList, fldNameArr, titleArr,showtailArr,ispercentArr);result=createChart(wb,sheet,10+dataList.size()+12,"bar",STBarGrouping.CLUSTERED,true,true,dataList, fldNameArr, titleArr,showtailArr,ispercentArr);//System.out.println(ctChart);System.out.println(result);FileOutputStream fileOut = new FileOutputStream("D://BarChart.xlsx");wb.write(fileOut);fileOut.close();}/*** @Description: 创建Excel数据* @param wb:工作薄* @param sheet：wb.createSheet();* @param dataList* @param fldNameArr* @param titleArr* @param showtailArr* @param ispercentArr* @param position:从第几行开始(0：就是第一行)* @return boolean*/public static boolean refreshChartExcel(SXSSFWorkbook wb,SXSSFSheet sheet,List dataList,List fldNameArr,List titleArr,List showtailArr,List ispercentArr,int position) {boolean result = true;//样式准备CellStyle style = wb.createCellStyle();style.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setBorderBottom(BorderStyle.THIN); //下边框style.setBorderLeft(BorderStyle.THIN);//左边框style.setBorderTop(BorderStyle.THIN);//上边框style.setBorderRight(BorderStyle.THIN);//右边框style.setAlignment(HorizontalAlignment.CENTER);CellStyle style1 = wb.createCellStyle();style1.setBorderBottom(BorderStyle.THIN); //下边框style1.setBorderLeft(BorderStyle.THIN);//左边框style1.setBorderTop(BorderStyle.THIN);//上边框style1.setBorderRight(BorderStyle.THIN);//右边框style1.setAlignment(HorizontalAlignment.CENTER);CellStyle cellStyle = wb.createCellStyle();cellStyle.setBorderTop(BorderStyle.THIN);//上边框cellStyle.setBorderBottom(BorderStyle.THIN); //下边框cellStyle.setBorderLeft(BorderStyle.THIN);//左边框cellStyle.setBorderRight(BorderStyle.THIN);//右边框cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平对齐方式//cellStyle.setVerticalAlignment(VerticalAlignment.TOP);//垂直对齐方式//根据数据创建excel第一行标题行for (int i = 0; i < titleArr.size(); i++) {if(sheet.getRow(position)==null){sheet.createRow(position).createCell(i).setCellValue(titleArr.get(i)==null?"":titleArr.get(i));}else{sheet.getRow(position).createCell(i).setCellValue(titleArr.get(i)==null?"":titleArr.get(i));}//标题行创建背景颜色sheet.getRow(position).getCell(i).setCellStyle(style);}//遍历数据行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(position+i+1)==null){if(j==0){try {sheet.createRow(position+i+1).createCell(j).setCellValue(((String) baseFormMap.get(fldNameArr.get(j)))==null?"":((String)baseFormMap.get(fldNameArr.get(j))));} catch (Exception e) {// TODOif((String)baseFormMap.get(fldNameArr.get(j))==null){sheet.createRow(position+i+1).createCell(j).setCellValue("");}else{sheet.createRow(position+i+1).createCell(j).setCellValue((Date)baseFormMap.get(fldNameArr.get(j)));}}}//标题行创建背景颜色sheet.getRow(position+i+1).getCell(j).setCellStyle(style1);}else{BigDecimal b=(BigDecimal)baseFormMap.get(fldNameArr.get(j));double value=0d;if(b!=null){value=b.doubleValue();}if(value==0){sheet.getRow(position+i+1).createCell(j);}else{sheet.getRow(position+i+1).createCell(j).setCellValue(((BigDecimal)baseFormMap.get(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 fldNameArr, List titleArr,List showtailArr,List ispercentArr) throws Exception{boolean result=false;String sheetName=sheet.getSheetName();//动态表sheet刷新result=refreshChartExcel(wb, sheet,dataList, fldNameArr, titleArr, showtailArr, ispercentArr, curRow);//创建一个画布Drawing drawing = sheet.createDrawingPatriarch();//前四个默认0，[0,5]：从0列5行开始;[6,20]:宽度6，20向下扩展到20行//默认宽度(14-8)*12ClientAnchor anchor =null;if(dataList.size()<10){anchor=drawing.createAnchor(0, 0, 0, 0, 0, curRow+dataList.size()+1, 6, curRow+dataList.size()+12);}else{anchor=drawing.createAnchor(0, 0, 0, 0, 0, curRow+dataList.size()+1, (int)Math.round(dataList.size()*0.5), curRow+dataList.size()+12);}//创建一个chart对象Chart chart = drawing.createChart(anchor);CTChart ctChart = ((XSSFChart)chart).getCTChart();CTPlotArea ctPlotArea = ctChart.getPlotArea();if("bar".equals(type)){CTBarChart ctBarChart = ctPlotArea.addNewBarChart();CTBoolean ctBoolean=ctBarChart.addNewVaryColors();ctBarChart.getVaryColors().setVal(true);//设置类型ctBarChart.addNewGrouping().setVal(group);ctBoolean.setVal(true);ctBarChart.addNewBarDir().setVal(STBarDir.COL);//是否添加左侧坐标轴ctChart.addNewDispBlanksAs().setVal(STDispBlanksAs.ZERO);ctChart.addNewShowDLblsOverMax().setVal(true);//设置这两个参数是为了在STACKED模式下生成堆积模式；(standard)标准模式时需要将这两行去掉if("stacked".equals(group.toString())||"percentStacked".equals(group.toString())){ctBarChart.addNewGapWidth().setVal(150);ctBarChart.addNewOverlap().setVal((byte)100);}//创建序列,并且设置选中区域for (int i = 0; i < fldNameArr.size()-1; i++) {CTBarSer ctBarSer = ctBarChart.addNewSer();CTSerTx ctSerTx = ctBarSer.addNewTx();//图例区CTStrRef ctStrRef = ctSerTx.addNewStrRef();String legendDataRange = new CellRangeAddress(curRow,curRow, i+1, i+1).formatAsString(sheetName, true);ctStrRef.setF(legendDataRange);ctBarSer.addNewIdx().setVal(i);//横坐标区CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();ctStrRef = cttAxDataSource.addNewStrRef();String axisDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), 0, 0).formatAsString(sheetName, true);ctStrRef.setF(axisDataRange);//数据区域CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();String numDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), i+1, i+1).formatAsString(sheetName, true);ctNumRef.setF(numDataRange);ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});//设置负轴颜色不是白色ctBarSer.addNewInvertIfNegative().setVal(false);//设置标签格式ctBoolean.setVal(false);CTDLbls newDLbls = ctBarSer.addNewDLbls();newDLbls.setShowLegendKey(ctBoolean);ctBoolean.setVal(true);newDLbls.setShowVal(ctBoolean);ctBoolean.setVal(false);newDLbls.setShowCatName(ctBoolean);newDLbls.setShowSerName(ctBoolean);newDLbls.setShowPercent(ctBoolean);newDLbls.setShowBubbleSize(ctBoolean);newDLbls.setShowLeaderLines(ctBoolean);}//telling the BarChart that it has axes and giving them IdsctBarChart.addNewAxId().setVal(123456);ctBarChart.addNewAxId().setVal(123457);//cat axisCTCatAx ctCatAx = ctPlotArea.addNewCatAx();ctCatAx.addNewAxId().setVal(123456); //id of the cat axisCTScaling ctScaling = ctCatAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctCatAx.addNewAxPos().setVal(STAxPos.B);ctCatAx.addNewCrossAx().setVal(123457); //id of the val axisctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//val axisCTValAx ctValAx = ctPlotArea.addNewValAx();ctValAx.addNewAxId().setVal(123457); //id of the val axisctScaling = ctValAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);//设置位置ctValAx.addNewAxPos().setVal(STAxPos.L);ctValAx.addNewCrossAx().setVal(123456); //id of the cat axisctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//是否删除主左边轴if(isvalAxis){ctValAx.addNewDelete().setVal(false);}else{ctValAx.addNewDelete().setVal(true);}//是否删除横坐标ctCatAx.addNewDelete().setVal(false);//legend图注if(isLegend){CTLegend ctLegend = ctChart.addNewLegend();ctLegend.addNewLegendPos().setVal(STLegendPos.B);ctLegend.addNewOverlay().setVal(false);}}else if("line".equals(type)){CTLineChart ctLineChart = ctPlotArea.addNewLineChart();CTBoolean ctBoolean = ctLineChart.addNewVaryColors();ctLineChart.addNewGrouping().setVal(STGrouping.STANDARD);//创建序列,并且设置选中区域for (int i = 0; i < fldNameArr.size()-1; i++) {CTLineSer ctLineSer = ctLineChart.addNewSer();CTSerTx ctSerTx = ctLineSer.addNewTx();//图例区CTStrRef ctStrRef = ctSerTx.addNewStrRef();String legendDataRange = new CellRangeAddress(curRow,curRow, i+1, i+1).formatAsString(sheetName, true);ctStrRef.setF(legendDataRange);ctStrRef.setF(legendDataRange);ctLineSer.addNewIdx().setVal(i);//横坐标区CTAxDataSource cttAxDataSource = ctLineSer.addNewCat();ctStrRef = cttAxDataSource.addNewStrRef();String axisDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), 0, 0).formatAsString(sheetName, true);ctStrRef.setF(axisDataRange);//数据区域CTNumDataSource ctNumDataSource = ctLineSer.addNewVal();CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();String numDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), i+1, i+1).formatAsString(sheetName, true);ctNumRef.setF(numDataRange);//设置标签格式ctBoolean.setVal(false);CTDLbls newDLbls = ctLineSer.addNewDLbls();newDLbls.setShowLegendKey(ctBoolean);ctBoolean.setVal(true);newDLbls.setShowVal(ctBoolean);ctBoolean.setVal(false);newDLbls.setShowCatName(ctBoolean);newDLbls.setShowSerName(ctBoolean);newDLbls.setShowPercent(ctBoolean);newDLbls.setShowBubbleSize(ctBoolean);newDLbls.setShowLeaderLines(ctBoolean);//是否是平滑曲线CTBoolean addNewSmooth = ctLineSer.addNewSmooth();addNewSmooth.setVal(false);//是否是堆积曲线CTMarker addNewMarker = ctLineSer.addNewMarker();CTMarkerStyle addNewSymbol = addNewMarker.addNewSymbol();addNewSymbol.setVal(STMarkerStyle.NONE);}//telling the BarChart that it has axes and giving them IdsctLineChart.addNewAxId().setVal(123456);ctLineChart.addNewAxId().setVal(123457);//cat axisCTCatAx ctCatAx = ctPlotArea.addNewCatAx();ctCatAx.addNewAxId().setVal(123456); //id of the cat axisCTScaling ctScaling = ctCatAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctCatAx.addNewAxPos().setVal(STAxPos.B);ctCatAx.addNewCrossAx().setVal(123457); //id of the val axisctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//val axisCTValAx ctValAx = ctPlotArea.addNewValAx();ctValAx.addNewAxId().setVal(123457); //id of the val axisctScaling = ctValAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctValAx.addNewAxPos().setVal(STAxPos.L);ctValAx.addNewCrossAx().setVal(123456); //id of the cat axisctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//是否删除主左边轴if(isvalAxis){ctValAx.addNewDelete().setVal(false);}else{ctValAx.addNewDelete().setVal(true);}//是否删除横坐标ctCatAx.addNewDelete().setVal(false);//legend图注if(isLegend){CTLegend ctLegend = ctChart.addNewLegend();ctLegend.addNewLegendPos().setVal(STLegendPos.B);ctLegend.addNewOverlay().setVal(false);}}else if("bar-line-2".equals(type)){CTBarChart ctBarChart = ctPlotArea.addNewBarChart();CTBoolean ctBoolean=ctBarChart.addNewVaryColors();ctBarChart.getVaryColors().setVal(true);//设置类型ctBarChart.addNewGrouping().setVal(STBarGrouping.CLUSTERED);ctBoolean.setVal(true);ctBarChart.addNewBarDir().setVal(STBarDir.COL);//是否添加左侧坐标轴ctChart.addNewDispBlanksAs().setVal(STDispBlanksAs.ZERO);ctChart.addNewShowDLblsOverMax().setVal(true);//创建序列,并且设置选中区域for (int i = 0; i < fldNameArr.size()-2; i++) {CTBarSer ctBarSer = ctBarChart.addNewSer();CTSerTx ctSerTx = ctBarSer.addNewTx();//图例区CTStrRef ctStrRef = ctSerTx.addNewStrRef();String legendDataRange = new CellRangeAddress(curRow,curRow, i+1, i+1).formatAsString(sheetName, true);ctStrRef.setF(legendDataRange);ctBarSer.addNewIdx().setVal(i);//横坐标区CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();ctStrRef = cttAxDataSource.addNewStrRef();String axisDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), 0, 0).formatAsString(sheetName, true);ctStrRef.setF(axisDataRange);//数据区域CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();String numDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), i+1, i+1).formatAsString(sheetName, true);ctNumRef.setF(numDataRange);ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});//设置负轴颜色不是白色ctBarSer.addNewInvertIfNegative().setVal(false);//设置标签格式ctBoolean.setVal(false);CTDLbls newDLbls = ctBarSer.addNewDLbls();newDLbls.setShowLegendKey(ctBoolean);ctBoolean.setVal(true);newDLbls.setShowVal(ctBoolean);ctBoolean.setVal(false);newDLbls.setShowCatName(ctBoolean);newDLbls.setShowSerName(ctBoolean);newDLbls.setShowPercent(ctBoolean);newDLbls.setShowBubbleSize(ctBoolean);newDLbls.setShowLeaderLines(ctBoolean);}//telling the BarChart that it has axes and giving them IdsctBarChart.addNewAxId().setVal(123456);ctBarChart.addNewAxId().setVal(123457);//telling the BarChart that it has axes and giving them IdsctBarChart.addNewAxId().setVal(123456);ctBarChart.addNewAxId().setVal(123457);//cat axisCTCatAx ctCatAx = ctPlotArea.addNewCatAx();ctCatAx.addNewAxId().setVal(123456); //id of the cat axisCTScaling ctScaling = ctCatAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctCatAx.addNewAxPos().setVal(STAxPos.B);ctCatAx.addNewCrossAx().setVal(123457); //id of the val axisctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//val axisCTValAx ctValAx = ctPlotArea.addNewValAx();ctValAx.addNewAxId().setVal(123457); //id of the val axisctScaling = ctValAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctValAx.addNewAxPos().setVal(STAxPos.L);ctValAx.addNewCrossAx().setVal(123456); //id of the cat axisctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//是否删除主左边轴if(isvalAxis){ctValAx.addNewDelete().setVal(false);}else{ctValAx.addNewDelete().setVal(true);}//是否删除横坐标ctCatAx.addNewDelete().setVal(false);//legend图注if(isLegend){CTLegend ctLegend = ctChart.addNewLegend();ctLegend.addNewLegendPos().setVal(STLegendPos.B);ctLegend.addNewOverlay().setVal(false);}CTLineChart ctLineChart = ctPlotArea.addNewLineChart();ctLineChart.addNewGrouping().setVal(STGrouping.STANDARD);//创建序列,并且设置选中区域for (int i =1; i < fldNameArr.size()-1; i++) {CTLineSer ctLineSer = ctLineChart.addNewSer();CTSerTx ctSerTx = ctLineSer.addNewTx();//图例区CTStrRef ctStrRef = ctSerTx.addNewStrRef();String legendDataRange = new CellRangeAddress(curRow,curRow, i+1, i+1).formatAsString(sheetName, true);ctStrRef.setF(legendDataRange);ctStrRef.setF(legendDataRange);ctLineSer.addNewIdx().setVal(i);//横坐标区CTAxDataSource cttAxDataSource = ctLineSer.addNewCat();ctStrRef = cttAxDataSource.addNewStrRef();String axisDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), 0, 0).formatAsString(sheetName, true);ctStrRef.setF(axisDataRange);//数据区域CTNumDataSource ctNumDataSource = ctLineSer.addNewVal();CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();String numDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), i+1, i+1).formatAsString(sheetName, true);ctNumRef.setF(numDataRange);//是否是平滑曲线CTBoolean addNewSmooth = ctLineSer.addNewSmooth();addNewSmooth.setVal(false);//是否是堆积曲线CTMarker addNewMarker = ctLineSer.addNewMarker();CTMarkerStyle addNewSymbol = addNewMarker.addNewSymbol();addNewSymbol.setVal(STMarkerStyle.NONE);}//telling the BarChart that it has axes and giving them Ids//TODO:写死是否有影响？ctLineChart.addNewAxId().setVal(1234567);ctLineChart.addNewAxId().setVal(1234578);//cat axisCTCatAx ctCatAxline = ctPlotArea.addNewCatAx();ctCatAxline.addNewAxId().setVal(1234567); //id of the cat axisCTScaling ctScalingline = ctCatAxline.addNewScaling();ctScalingline.addNewOrientation().setVal(STOrientation.MIN_MAX);ctCatAxline.addNewDelete().setVal(true);ctCatAxline.addNewAxPos().setVal(STAxPos.B);ctCatAxline.addNewMajorTickMark().setVal(STTickMark.OUT);ctCatAxline.addNewMinorTickMark().setVal(STTickMark.NONE);ctCatAxline.addNewAuto().setVal(true);ctCatAxline.addNewLblAlgn().setVal(STLblAlgn.CTR);ctCatAxline.addNewLblOffset().setVal(100);ctCatAxline.addNewNoMultiLvlLbl().setVal(false);ctCatAxline.addNewCrossAx().setVal(1234578); //id of the val axisctCatAxline.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//val axisCTValAx ctValAxline = ctPlotArea.addNewValAx();ctValAxline.addNewAxId().setVal(1234578); //id of the val axisctScalingline = ctValAxline.addNewScaling();ctScalingline.addNewOrientation().setVal(STOrientation.MIN_MAX);//Y轴右侧坐标true删除，false保留ctValAxline.addNewDelete().setVal(false);ctValAxline.addNewAxPos().setVal(STAxPos.R);ctValAxline.addNewMajorTickMark().setVal(STTickMark.OUT);ctValAxline.addNewMinorTickMark().setVal(STTickMark.NONE);ctValAxline.addNewCrosses().setVal(STCrosses.MAX);ctValAxline.addNewCrossBetween().setVal(STCrossBetween.BETWEEN);ctValAxline.addNewCrossAx().setVal(1234567); //id of the cat axisctValAxline.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);}else if("bar-line-4".equals(type)){CTBarChart ctBarChart = ctPlotArea.addNewBarChart();CTBoolean ctBoolean=ctBarChart.addNewVaryColors();ctBarChart.getVaryColors().setVal(true);//设置类型ctBarChart.addNewGrouping().setVal(STBarGrouping.CLUSTERED);ctBoolean.setVal(true);ctBarChart.addNewBarDir().setVal(STBarDir.COL);//是否添加左侧坐标轴ctChart.addNewDispBlanksAs().setVal(STDispBlanksAs.ZERO);ctChart.addNewShowDLblsOverMax().setVal(true);//创建序列,并且设置选中区域for (int i = 0; i < fldNameArr.size()-1-2; i++) {CTBarSer ctBarSer = ctBarChart.addNewSer();CTSerTx ctSerTx = ctBarSer.addNewTx();//图例区CTStrRef ctStrRef = ctSerTx.addNewStrRef();String legendDataRange = new CellRangeAddress(curRow,curRow, i+1, i+1).formatAsString(sheetName, true);ctStrRef.setF(legendDataRange);ctBarSer.addNewIdx().setVal(i);//横坐标区CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();ctStrRef = cttAxDataSource.addNewStrRef();String axisDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), 0, 0).formatAsString(sheetName, true);ctStrRef.setF(axisDataRange);//数据区域CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();String numDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), i+1, i+1).formatAsString(sheetName, true);ctNumRef.setF(numDataRange);ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});//设置负轴颜色不是白色ctBarSer.addNewInvertIfNegative().setVal(false);//设置标签格式ctBoolean.setVal(false);CTDLbls newDLbls = ctBarSer.addNewDLbls();newDLbls.setShowLegendKey(ctBoolean);ctBoolean.setVal(true);newDLbls.setShowVal(ctBoolean);ctBoolean.setVal(false);newDLbls.setShowCatName(ctBoolean);newDLbls.setShowSerName(ctBoolean);newDLbls.setShowPercent(ctBoolean);newDLbls.setShowBubbleSize(ctBoolean);newDLbls.setShowLeaderLines(ctBoolean);}//telling the BarChart that it has axes and giving them IdsctBarChart.addNewAxId().setVal(123456);ctBarChart.addNewAxId().setVal(123457);//telling the BarChart that it has axes and giving them IdsctBarChart.addNewAxId().setVal(123456);ctBarChart.addNewAxId().setVal(123457);//cat axisCTCatAx ctCatAx = ctPlotArea.addNewCatAx();ctCatAx.addNewAxId().setVal(123456); //id of the cat axisCTScaling ctScaling = ctCatAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctCatAx.addNewAxPos().setVal(STAxPos.B);ctCatAx.addNewCrossAx().setVal(123457); //id of the val axisctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//val axisCTValAx ctValAx = ctPlotArea.addNewValAx();ctValAx.addNewAxId().setVal(123457); //id of the val axisctScaling = ctValAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctValAx.addNewAxPos().setVal(STAxPos.L);ctValAx.addNewCrossAx().setVal(123456); //id of the cat axisctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//是否删除主左边轴if(isvalAxis){ctValAx.addNewDelete().setVal(false);}else{ctValAx.addNewDelete().setVal(true);}//是否删除横坐标ctCatAx.addNewDelete().setVal(false);//legend图注if(isLegend){CTLegend ctLegend = ctChart.addNewLegend();ctLegend.addNewLegendPos().setVal(STLegendPos.B);ctLegend.addNewOverlay().setVal(false);}CTLineChart ctLineChart = ctPlotArea.addNewLineChart();ctLineChart.addNewGrouping().setVal(STGrouping.STANDARD);//创建序列,并且设置选中区域for (int i = 2; i < fldNameArr.size()-1; i++) {CTLineSer ctLineSer = ctLineChart.addNewSer();CTSerTx ctSerTx = ctLineSer.addNewTx();//图例区CTStrRef ctStrRef = ctSerTx.addNewStrRef();String legendDataRange = new CellRangeAddress(curRow,curRow, i+1, i+1).formatAsString(sheetName, true);ctStrRef.setF(legendDataRange);ctStrRef.setF(legendDataRange);ctLineSer.addNewIdx().setVal(i);//横坐标区CTAxDataSource cttAxDataSource = ctLineSer.addNewCat();ctStrRef = cttAxDataSource.addNewStrRef();String axisDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), 0, 0).formatAsString(sheetName, true);ctStrRef.setF(axisDataRange);//数据区域CTNumDataSource ctNumDataSource = ctLineSer.addNewVal();CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();String numDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), i+1, i+1).formatAsString(sheetName, true);ctNumRef.setF(numDataRange);//是否是平滑曲线CTBoolean addNewSmooth = ctLineSer.addNewSmooth();addNewSmooth.setVal(false);//是否是堆积曲线CTMarker addNewMarker = ctLineSer.addNewMarker();CTMarkerStyle addNewSymbol = addNewMarker.addNewSymbol();addNewSymbol.setVal(STMarkerStyle.NONE);}//telling the BarChart that it has axes and giving them Ids//TODO:写死是否有影响？ctLineChart.addNewAxId().setVal(1234567);ctLineChart.addNewAxId().setVal(1234578);//cat axisCTCatAx ctCatAxline = ctPlotArea.addNewCatAx();ctCatAxline.addNewAxId().setVal(1234567); //id of the cat axisCTScaling ctScalingline = ctCatAxline.addNewScaling();ctScalingline.addNewOrientation().setVal(STOrientation.MIN_MAX);ctCatAxline.addNewDelete().setVal(true);ctCatAxline.addNewAxPos().setVal(STAxPos.B);ctCatAxline.addNewMajorTickMark().setVal(STTickMark.OUT);ctCatAxline.addNewMinorTickMark().setVal(STTickMark.NONE);ctCatAxline.addNewAuto().setVal(true);ctCatAxline.addNewLblAlgn().setVal(STLblAlgn.CTR);ctCatAxline.addNewLblOffset().setVal(100);ctCatAxline.addNewNoMultiLvlLbl().setVal(false);ctCatAxline.addNewCrossAx().setVal(1234578); //id of the val axisctCatAxline.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//val axisCTValAx ctValAxline = ctPlotArea.addNewValAx();ctValAxline.addNewAxId().setVal(1234578); //id of the val axisctScalingline = ctValAxline.addNewScaling();ctScalingline.addNewOrientation().setVal(STOrientation.MIN_MAX);ctValAxline.addNewDelete().setVal(false);ctValAxline.addNewAxPos().setVal(STAxPos.R);ctValAxline.addNewMajorTickMark().setVal(STTickMark.OUT);ctValAxline.addNewMinorTickMark().setVal(STTickMark.NONE);ctValAxline.addNewCrosses().setVal(STCrosses.MAX);ctValAxline.addNewCrossBetween().setVal(STCrossBetween.BETWEEN);ctValAxline.addNewCrossAx().setVal(1234567); //id of the cat axisctValAxline.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);}else if("pie".equals(type)){//pieCTPieChart ctPieChart = ctPlotArea.addNewPieChart();CTBoolean ctBoolean = ctPieChart.addNewVaryColors();//创建序列,并且设置选中区域for (int i = 0; i < fldNameArr.size()-1; i++) {CTPieSer ctPieSer = ctPieChart.addNewSer();CTSerTx ctSerTx = ctPieSer.addNewTx();//图例区CTStrRef ctStrRef = ctSerTx.addNewStrRef();String legendDataRange = new CellRangeAddress(curRow,curRow, i+1, i+1).formatAsString(sheetName, true);ctStrRef.setF(legendDataRange);ctPieSer.addNewIdx().setVal(i);//横坐标区CTAxDataSource cttAxDataSource = ctPieSer.addNewCat();ctStrRef = cttAxDataSource.addNewStrRef();String axisDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), 0, 0).formatAsString(sheetName, true);ctStrRef.setF(axisDataRange);//数据区域CTNumDataSource ctNumDataSource = ctPieSer.addNewVal();CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();String numDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), i+1, i+1).formatAsString(sheetName, true);ctNumRef.setF(numDataRange);ctPieSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});//设置标签格式ctBoolean.setVal(true);}//legend图注CTLegend ctLegend = ctChart.addNewLegend();ctLegend.addNewLegendPos().setVal(STLegendPos.B);ctLegend.addNewOverlay().setVal(true);}else if("pie3D".equals(type)){//pie3DCTPie3DChart ctPie3DChart = ctPlotArea.addNewPie3DChart();CTBoolean ctBoolean = ctPie3DChart.addNewVaryColors();//创建序列,并且设置选中区域for (int i = 0; i < fldNameArr.size()-1; i++) {CTPieSer ctPieSer = ctPie3DChart.addNewSer();CTSerTx ctSerTx = ctPieSer.addNewTx();//图例区CTStrRef ctStrRef = ctSerTx.addNewStrRef();String legendDataRange = new CellRangeAddress(curRow,curRow, i+1, i+1).formatAsString(sheetName, true);ctStrRef.setF(legendDataRange);ctPieSer.addNewIdx().setVal(i);//横坐标区CTAxDataSource cttAxDataSource = ctPieSer.addNewCat();ctStrRef = cttAxDataSource.addNewStrRef();String axisDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), 0, 0).formatAsString(sheetName, true);ctStrRef.setF(axisDataRange);//数据区域CTNumDataSource ctNumDataSource = ctPieSer.addNewVal();CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();String numDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), i+1, i+1).formatAsString(sheetName, true);ctNumRef.setF(numDataRange);ctPieSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});//设置标签格式ctBoolean.setVal(true);}//legend图注CTLegend ctLegend = ctChart.addNewLegend();ctLegend.addNewLegendPos().setVal(STLegendPos.B);ctLegend.addNewOverlay().setVal(true);}else{//areaCTAreaChart ctAreaChart = ctPlotArea.addNewAreaChart();CTBoolean ctBoolean = ctAreaChart.addNewVaryColors();ctAreaChart.addNewGrouping().setVal(STGrouping.STANDARD);//创建序列,并且设置选中区域for (int i = 0; i < fldNameArr.size()-1; i++) {CTAreaSer ctAreaSer = ctAreaChart.addNewSer();CTSerTx ctSerTx = ctAreaSer.addNewTx();//图例区CTStrRef ctStrRef = ctSerTx.addNewStrRef();String legendDataRange = new CellRangeAddress(curRow,curRow, i+1, i+1).formatAsString(sheetName, true);ctStrRef.setF(legendDataRange);ctStrRef.setF(legendDataRange);ctAreaSer.addNewIdx().setVal(i);//横坐标区CTAxDataSource cttAxDataSource = ctAreaSer.addNewCat();ctStrRef = cttAxDataSource.addNewStrRef();String axisDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), 0, 0).formatAsString(sheetName, true);ctStrRef.setF(axisDataRange);//数据区域CTNumDataSource ctNumDataSource = ctAreaSer.addNewVal();CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();String numDataRange = new CellRangeAddress(curRow+1, curRow+dataList.size(), i+1, i+1).formatAsString(sheetName, true);ctNumRef.setF(numDataRange);//设置标签格式ctBoolean.setVal(false);CTDLbls newDLbls = ctAreaSer.addNewDLbls();newDLbls.setShowLegendKey(ctBoolean);ctBoolean.setVal(true);newDLbls.setShowVal(ctBoolean);ctBoolean.setVal(false);newDLbls.setShowCatName(ctBoolean);newDLbls.setShowSerName(ctBoolean);newDLbls.setShowPercent(ctBoolean);newDLbls.setShowBubbleSize(ctBoolean);newDLbls.setShowLeaderLines(ctBoolean);/* //是否是平滑曲线CTBoolean addNewSmooth = ctAreaSer.addNewSmooth();addNewSmooth.setVal(false);//是否是堆积曲线CTMarker addNewMarker = ctAreaSer.addNewMarker();CTMarkerStyle addNewSymbol = addNewMarker.addNewSymbol();addNewSymbol.setVal(STMarkerStyle.NONE);*/}//telling the BarChart that it has axes and giving them IdsctAreaChart.addNewAxId().setVal(123456);ctAreaChart.addNewAxId().setVal(123457);//cat axisCTCatAx ctCatAx = ctPlotArea.addNewCatAx();ctCatAx.addNewAxId().setVal(123456); //id of the cat axisCTScaling ctScaling = ctCatAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctCatAx.addNewAxPos().setVal(STAxPos.B);ctCatAx.addNewCrossAx().setVal(123457); //id of the val axisctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//val axisCTValAx ctValAx = ctPlotArea.addNewValAx();ctValAx.addNewAxId().setVal(123457); //id of the val axisctScaling = ctValAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctValAx.addNewAxPos().setVal(STAxPos.L);ctValAx.addNewCrossAx().setVal(123456); //id of the cat axisctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//是否删除主左边轴if(isvalAxis){ctValAx.addNewDelete().setVal(false);}else{ctValAx.addNewDelete().setVal(true);}//是否删除横坐标ctCatAx.addNewDelete().setVal(false);//legend图注if(isLegend){CTLegend ctLegend = ctChart.addNewLegend();ctLegend.addNewLegendPos().setVal(STLegendPos.B);ctLegend.addNewOverlay().setVal(false);}}return result;}} -
执行结果
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！