POI EXCEL修改图表

我就是我 2023-10-06 22:15 46阅读 0赞

具体参考：[POI EXCEL 图表、折线图、条形图，柱状图、饼图、散点图\_小百菜的博客-CSDN博客\_poi 图表][POI EXCEL _-CSDN_poi]

### 生成柱状图示例 ： ###

package com.demo.test;
    
    import java.io.FileOutputStream;
    import java.io.IOException;
    
    import org.apache.poi.xddf.usermodel.XDDFColor;
    import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
    import org.apache.poi.xddf.usermodel.chart.AxisCrossBetween;
    import org.apache.poi.xddf.usermodel.chart.AxisCrosses;
    import org.apache.poi.xddf.usermodel.chart.AxisPosition;
    import org.apache.poi.xddf.usermodel.chart.BarDirection;
    import org.apache.poi.xddf.usermodel.chart.ChartTypes;
    import org.apache.poi.xddf.usermodel.chart.LegendPosition;
    import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData;
    import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
    import org.apache.poi.xddf.usermodel.chart.XDDFCategoryDataSource;
    import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
    import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
    import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
    import org.apache.poi.xddf.usermodel.chart.XDDFValueAxis;
    import org.apache.poi.xssf.usermodel.XSSFChart;
    import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
    import org.apache.poi.xssf.usermodel.XSSFDrawing;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
    
    public class ApachePoiBarChart {
    
    	public static void main(String[] args) throws IOException {
    		XSSFWorkbook wb = new XSSFWorkbook();
    		String sheetName = "Sheet1";
    		FileOutputStream fileOut = null;
    		try {
    			XSSFSheet sheet = wb.createSheet(sheetName);
    			// 创建一个画布
    			XSSFDrawing drawing = sheet.createDrawingPatriarch();
    			// 前四个默认0，[0,5]：从0列5行开始;[7,26]:到7列26行结束
    			// 默认宽度(14-8)*12
    			XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 7, 26);
    			// 创建一个chart对象
    			XSSFChart chart = drawing.createChart(anchor);
    			// 标题
    			chart.setTitleText("地区排名前七的国家");
    			// 标题覆盖
    			chart.setTitleOverlay(false);
    
    			// 图例位置
    			XDDFChartLegend legend = chart.getOrAddLegend();
    			legend.setPosition(LegendPosition.TOP);
    
    			// 分类轴标(X轴),标题位置
    			XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
    			bottomAxis.setTitle("国家");
    			// 值(Y轴)轴,标题位置
    			XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
    			leftAxis.setTitle("面积和人口");
    
    			// CellRangeAddress(起始行号，终止行号， 起始列号，终止列号）
    			// 分类轴标(X轴)数据，单元格范围位置[0, 0]到[0, 6]
    			// XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, 0, 0, 6));
    			XDDFCategoryDataSource countries = XDDFDataSourcesFactory.fromArray(new String[] { "俄罗斯", "加拿大", "美国", "中国", "巴西", "澳大利亚", "印度" });
    			// 数据1，单元格范围位置[1, 0]到[1, 6]
    			// XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, 6));
    			XDDFNumericalDataSource<Integer> area = XDDFDataSourcesFactory.fromArray(new Integer[] { 17098242, 9984670, 9826675, 9596961, 8514877, 7741220, 3287263 });
    
    			// 数据2，单元格范围位置[2, 0]到[2, 6]
    
    			// bar：条形图，
    			XDDFBarChartData bar = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
    
    			leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
    			leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
    			// 设置为可变颜色
    			bar.setVaryColors(true);
    			// 条形图方向，纵向/横向：纵向
    			bar.setBarDirection(BarDirection.COL);
    
    			// 图表加载数据，条形图1
    			XDDFBarChartData.Series series1 = (XDDFBarChartData.Series) bar.addSeries(countries, area);
    			// 条形图例标题
    			series1.setTitle("面积", null);
    			byte[] cbyte = new byte[] { (byte) 0, (byte) 255, (byte) 255 };
    			XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(cbyte));
    			// XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.RED));
    			// 条形图，填充颜色
    			series1.setFillProperties(fill);
    
    			// 图表加载数据，条形图2
    
    			CTPlotArea plotArea = chart.getCTChart().getPlotArea();
    			plotArea.getValAxArray(0).addNewMajorGridlines();// 网格线
    			// 绘制
    			chart.plot(bar);
    
    			// 堆积条形图，将2个条形图重叠起来
    			// bar.setBarGrouping(BarGrouping.STACKED);
    			// 修正重叠，使钢筋真正堆叠而不是并排
    			// plotArea.getBarChartArray(0).addNewOverlap().setVal((byte) 100);
    
    			// 柱状图1上显示数值
    			plotArea.getBarChartArray(0).getSerArray(0).addNewDLbls();
    			plotArea.getBarChartArray(0).getSerArray(0).getDLbls().addNewShowVal().setVal(true);
    			plotArea.getBarChartArray(0).getSerArray(0).getDLbls().addNewShowLegendKey().setVal(false);
    			plotArea.getBarChartArray(0).getSerArray(0).getDLbls().addNewShowCatName().setVal(false);
    			plotArea.getBarChartArray(0).getSerArray(0).getDLbls().addNewShowSerName().setVal(false);
    
    			// 将输出写入excel文件
    			String filename = "test.xlsx";
    			fileOut = new FileOutputStream(filename);
    			wb.write(fileOut);
    		} catch (Exception e) {
    			e.printStackTrace();
    		} finally {
    			wb.close();
    			if (fileOut != null) {
    				fileOut.close();
    			}
    		}
    
    	}
    
    }

### 修改柱状图数据，以上面生成的为例子 ###

package com.demo.test;
    
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.util.List;
    
    import org.apache.poi.xssf.usermodel.XSSFChart;
    import org.apache.poi.xssf.usermodel.XSSFDrawing;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumVal;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
    
    public class Test {
    
    	public static void main(String[] args) throws Exception {
    		String fin = "D:\\Works\\eclipse\\znfx\\workspace\\poi\\test.xlsx";
    		String fout = "D:\\Works\\eclipse\\znfx\\workspace\\poi\\test2.xlsx";
    		FileInputStream fis = new FileInputStream(fin);
    		FileOutputStream fos = new FileOutputStream(fout);
    		XSSFWorkbook wb = new XSSFWorkbook(fis);
    		XSSFSheet sheet = wb.getSheetAt(0);
    		XSSFDrawing drawing = sheet.getDrawingPatriarch();
    		List<XSSFChart> charts = drawing.getCharts();
    		XSSFChart chart = charts.get(0);
    		CTPlotArea plotArea = chart.getCTChart().getPlotArea();
    		CTBarSer serArray = plotArea.getBarChartArray(0).getSerArray(0);
    		System.out.println(serArray);
    		List<CTNumVal> ptList = serArray.getVal().getNumLit().getPtList();
    		for (int i = 0; i < ptList.size(); i++) {
    			ptList.get(i).setV((i + 1) * 100 + "");// 改变值
    		}
    		wb.write(fos);
    		fis.close();
    		fos.close();
    		wb.close();
    	}
    
    }

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCP55m-6I-c_size_20_color_FFFFFF_t_70_g_se_x_16][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCP55m-6I-c_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

**具体api查考：**

[POI EXCEL 图表、折线图、条形图，柱状图、饼图、散点图\_小百菜的博客-CSDN博客\_poi 图表][POI EXCEL _-CSDN_poi]

第7、POI api

[POI EXCEL _-CSDN_poi]: https://blog.csdn.net/u014644574/article/details/105695787
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCP55m-6I-c_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/5647c1475f9d46c1baf69cd73790e8b4.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5bCP55m-6I-c,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCP55m-6I-c_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/761b313fc5c24ef3b422e45c2d2672ab.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5bCP55m-6I-c,size_20,color_FFFFFF,t_70,g_se,x_16