EXCEL导出带图表

参考POI EXCEL 图表、折线图、条形图,柱状图、饼图、散点图_poi excel图表-CSDN博客

 pom

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-collections4</artifactId>
            <version>4.4</version>
        </dependency>
        <dependency>
            <groupId>commons-codec</groupId>
            <artifactId>commons-codec</artifactId>
            <version>1.13</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-compress</artifactId>
            <version>1.19</version>
        </dependency>
        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>ooxml-schemas</artifactId>
            <version>1.4</version>
        </dependency>
package com.charsming;
 
import java.io.FileOutputStream;
import java.io.IOException;
 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.PresetLineDash;
import org.apache.poi.xddf.usermodel.XDDFLineProperties;
import org.apache.poi.xddf.usermodel.XDDFPresetLineDash;
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.MarkerStyle;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
import org.apache.poi.xddf.usermodel.chart.XDDFLineChartData;
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.CTDLbls;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
import org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos;

/**
 * POI EXCEL 图表-折线图
 */
public class ApachePoiLineChart4 {
 
	public static void main(String[] args) throws IOException {
		XSSFWorkbook wb = new XSSFWorkbook();
		String sheetName = "Sheet1";
		FileOutputStream fileOut = null;

		XSSFSheet sheet = wb.createSheet(sheetName);
		// 第一行,国家名称
		Row row = sheet.createRow(0);
		Cell cell = row.createCell(0);
		cell.setCellValue("俄罗斯");
		cell = row.createCell(1);
		cell.setCellValue("加拿大");
		cell = row.createCell(2);
		cell.setCellValue("美国");
		cell = row.createCell(3);
		cell.setCellValue("中国");
		cell = row.createCell(4);
		cell.setCellValue("巴西");
		cell = row.createCell(5);
		cell.setCellValue("澳大利亚");
		cell = row.createCell(6);
		cell.setCellValue("印度");
		// 第二行,乡村地区
		row = sheet.createRow(1);
		cell = row.createCell(0);
		cell.setCellValue(17098242);
		cell = row.createCell(1);
		cell.setCellValue(9984670);
		cell = row.createCell(2);
		cell.setCellValue(9826675);
		cell = row.createCell(3);
		cell.setCellValue(9596961);
		cell = row.createCell(4);
		cell.setCellValue(8000000);
		cell = row.createCell(5);
		cell.setCellValue(7000000);
		// 设置第6列的值为第4列和第5列值的和,通过公式实现
		cell = row.createCell(6);
		cell.setCellFormula("SUM(E2:F2)");

		// 第三行,农村人口
		row = sheet.createRow(2);
		cell = row.createCell(0);
		cell.setCellValue(14590041);
		cell = row.createCell(1);
		cell.setCellValue(35151728);
		cell = row.createCell(2);
		cell.setCellValue(32993302);
		cell = row.createCell(3);
		cell.setCellValue(14362887);
		cell = row.createCell(4);
		cell.setCellValue(21172141);
		cell = row.createCell(5);
		cell.setCellValue(25335727);
		cell = row.createCell(6);
		cell.setCellValue(13724923);
		// 第四行,面积平局
		row = sheet.createRow(3);
		cell = row.createCell(0);
		cell.setCellValue(9435701.143);
		cell = row.createCell(1);
		cell.setCellValue(9435701.143);
		cell = row.createCell(2);
		cell.setCellValue(9435701.143);
		cell = row.createCell(3);
		cell.setCellValue(9435701.143);
		cell = row.createCell(4);
		cell.setCellValue(9435701.143);
		cell = row.createCell(5);
		cell.setCellValue(9435701.143);
		cell = row.createCell(6);
		cell.setCellValue(9435701.143);
		// 第四行,人口平局
		row = sheet.createRow(4);
		cell = row.createCell(0);
		cell.setCellValue(22475821.29);
		cell = row.createCell(1);
		cell.setCellValue(22475821.29);
		cell = row.createCell(2);
		cell.setCellValue(22475821.29);
		cell = row.createCell(3);
		cell.setCellValue(22475821.29);
		cell = row.createCell(4);
		cell.setCellValue(22475821.29);
		cell = row.createCell(5);
		cell.setCellValue(22475821.29);
		cell = row.createCell(6);
		cell.setCellValue(22475821.29);



		// 第一行,国家名称
		Row row2 = sheet.createRow(29);
		Cell cell2 = row2.createCell(0);
		cell2.setCellValue("俄罗");
		cell2 = row2.createCell(1);
		cell2.setCellValue("加拿");
		cell2 = row2.createCell(2);
		cell2.setCellValue("美");
		cell2 = row2.createCell(3);
		cell2.setCellValue("中");
		cell2 = row2.createCell(4);
		cell2.setCellValue("巴");
		cell2 = row2.createCell(5);
		cell2.setCellValue("澳");
		cell2 = row2.createCell(6);
		cell2.setCellValue("印");
		// 第二行,乡村地区
		row2 = sheet.createRow(30);
		cell2 = row2.createCell(0);
		cell2.setCellValue(17098242);
		cell2 = row2.createCell(1);
		cell2.setCellValue(9984670);
		cell2 = row2.createCell(2);
		cell2.setCellValue(9826675);
		cell2 = row2.createCell(3);
		cell2.setCellValue(9596961);
		cell2 = row2.createCell(4);
		cell2.setCellValue(8514877);
		cell2 = row2.createCell(5);
		cell2.setCellValue(7741220);
		cell2 = row2.createCell(6);
		cell2.setCellValue(3287263);

		// 第三行,农村人口
		row2 = sheet.createRow(31);
		cell2 = row2.createCell(0);
		cell2.setCellValue(14590041);
		cell2 = row2.createCell(1);
		cell2.setCellValue(35151728);
		cell2 = row2.createCell(2);
		cell2.setCellValue(32993302);
		cell2 = row2.createCell(3);
		cell2.setCellValue(14362887);
		cell2 = row2.createCell(4);
		cell2.setCellValue(21172141);
		cell2 = row2.createCell(5);
		cell2.setCellValue(25335727);
		cell2 = row2.createCell(6);
		cell2.setCellValue(13724923);

		try {
			createLineChart( sheet);
			createLineChart2( sheet);

			// 将输出写入excel文件
			String filename = "排行榜前七的国家.xlsx";
			fileOut = new FileOutputStream(filename);
			wb.write(fileOut);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			wb.close();
			if (fileOut != null) {
				fileOut.close();
			}

		}
	}

	public static void createLineChart(XSSFSheet sheet) throws IOException {

		try {

			// 创建一个画布
			XSSFDrawing drawing = sheet.createDrawingPatriarch();
			// 前四个默认0,[0,5]:从0列5行开始;[7,26]:到7列26行结束
			// 前四个默认0,从第5行到第25行,(第五个参数)从第0列到第10列的区域
			// 默认宽度(14-8)*12
			XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 25);
			// 创建一个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});

			// 数据1,单元格范围位置[2, 0]到[2, 6]
			XDDFNumericalDataSource<Double> population = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, 6));

			// LINE:折线图,
			XDDFLineChartData data = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);

			// 图表加载数据,折线1
			XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) data.addSeries(countries, area);
			// 折线图例标题
			series1.setTitle("面积", null);
			// 直线
			series1.setSmooth(false);
			// 设置标记大小
			series1.setMarkerSize((short) 6);
			// 设置标记样式,星星
			series1.setMarkerStyle(MarkerStyle.STAR);

			// 图表加载数据,折线2
			XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) data.addSeries(countries, population);
			// 折线图例标题
			series2.setTitle("人口", null);
			// 曲线
			series2.setSmooth(true);
			// 设置标记大小
			series2.setMarkerSize((short) 6);
			// 设置标记样式,正方形
			series2.setMarkerStyle(MarkerStyle.SQUARE);
			//series2.setShowLeaderLines(true);
			// 获取图表元素
			CTPlotArea plotArea = chart.getCTChart().getPlotArea();
			// 绘制
			chart.plot(data);
			// 设置数据标签
			plotArea.getLineChartArray(0).getSerArray(0).addNewDLbls();
			plotArea.getLineChartArray(0).getSerArray(0).getDLbls().addNewShowVal().setVal(true);
			plotArea.getLineChartArray(0).getSerArray(0).getDLbls().addNewShowLegendKey().setVal(false);
			plotArea.getLineChartArray(0).getSerArray(0).getDLbls().addNewShowCatName().setVal(false);
			plotArea.getLineChartArray(0).getSerArray(0).getDLbls().addNewShowSerName().setVal(false);
			// 这里是设置数据标签的位置,目前inEnd比较符合,但还是不完美,数据标签未展示在 顶部, 后续需看下怎么优化
			plotArea.getLineChartList().get(0).getSerArray(0).getDLbls().addNewDLblPos().setVal(STDLblPos.Enum.forString("t"));// 数据标签
			plotArea.getValAxArray(0).addNewMajorGridlines();//水平网格线
			plotArea.getCatAxArray(0).addNewMajorGridlines();//垂直网格线
			// 打印图表的xml
			// System.out.println(chart.getCTChart());


		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static void createLineChart2(XSSFSheet sheet) throws IOException {

		try {
			// 画一个图区域

			XSSFDrawing drawing = sheet.createDrawingPatriarch();
			// 前四个默认0,从第5行到第26行,从第0列到第16列的区域
			// 默认宽度(14-8)*12
			//XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 36, 47, 26);
			XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 10, 32, 20, 52);
			// 创建一个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(29, 29, 0, 6));
			// XDDFCategoryDataSource countries = XDDFDataSourcesFactory.fromArray(new String[] {"俄罗斯","加拿大","美国","中国","巴西","澳大利亚","印度"});
			// 数据1,单元格范围位置[1, 0]到[1, 6]
			XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(30, 30, 0, 6));
			// XDDFNumericalDataSource<Integer> area = XDDFDataSourcesFactory.fromArray(new Integer[] {17098242,9984670,9826675,9596961,8514877,7741220,3287263});

			// 数据1,单元格范围位置[2, 0]到[2, 6]
			XDDFNumericalDataSource<Double> population = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(31, 31, 0, 6));

			// LINE:折线图,
			XDDFLineChartData data = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);

			// 图表加载数据,折线1
			XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) data.addSeries(countries, area);
			// 折线图例标题
			series1.setTitle("面积", null);
			// 直线
			series1.setSmooth(false);
			// 设置标记大小
			series1.setMarkerSize((short) 6);
			// 设置标记样式,星星
			series1.setMarkerStyle(MarkerStyle.STAR);

			// 图表加载数据,折线2
			XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) data.addSeries(countries, population);
			// 折线图例标题
			series2.setTitle("人口", null);
			// 曲线
			series2.setSmooth(true);
			// 设置标记大小
			series2.setMarkerSize((short) 6);
			// 设置标记样式,正方形
			series2.setMarkerStyle(MarkerStyle.SQUARE);
			//series2.setShowLeaderLines(true);
			// 获取图表元素
			CTPlotArea plotArea = chart.getCTChart().getPlotArea();
			// 绘制
			chart.plot(data);
			// 设置数据标签
			plotArea.getLineChartArray(0).getSerArray(0).addNewDLbls();
			plotArea.getLineChartArray(0).getSerArray(0).getDLbls().addNewShowVal().setVal(true);
			plotArea.getLineChartArray(0).getSerArray(0).getDLbls().addNewShowLegendKey().setVal(false);
			plotArea.getLineChartArray(0).getSerArray(0).getDLbls().addNewShowCatName().setVal(false);
			plotArea.getLineChartArray(0).getSerArray(0).getDLbls().addNewShowSerName().setVal(false);
			// 这里是设置数据标签的位置,目前inEnd比较符合,但还是不完美,数据标签未展示在 顶部, 后续需看下怎么优化
			plotArea.getLineChartList().get(0).getSerArray(0).getDLbls().addNewDLblPos().setVal(STDLblPos.Enum.forString("t"));// 数据标签
			plotArea.getValAxArray(0).addNewMajorGridlines();//水平网格线
			plotArea.getCatAxArray(0).addNewMajorGridlines();//垂直网格线
			// 打印图表的xml
			// System.out.println(chart.getCTChart());


		} catch (Exception e) {
			e.printStackTrace();
		}
	}
 
}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

yzhSWJ

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值