参考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();
}
}
}


4909

被折叠的 条评论
为什么被折叠?



