POI Word生成图表：POI Word 图表、柱状图、条形图、折线图、饼图_u014644574的博客-CSDN博客_poi word 图表
1、pom.xml
<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>2、POI EXCEL 图表-折线图
package test;
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;
/**
* POI EXCEL 图表-折线图
*/
public class ApachePoiLineChart4 {
public static void main(String[] args) throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
String sheetName = "Sheet1";
FileOutputStream fileOut = null;
try {
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(8514877);
cell = row.createCell(5);
cell.setCellValue(7741220);
cell = row.createCell(6);
cell.setCellValue(3287263);
// 第三行，农村人口
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);
// 创建一个画布
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});
// 数据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);
// 图表加载数据，平均线3
// 数据1，单元格范围位置[2, 0]到[2, 6]
XDDFNumericalDataSource<Double> population3 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(3, 3, 0, 6));
XDDFLineChartData.Series series3 = (XDDFLineChartData.Series) data.addSeries(countries, population3);
// 折线图例标题
series3.setTitle("面积平均", null);
// 直线
series3.setSmooth(false);
// 设置标记大小
// series3.setMarkerSize((short) 3);
// 设置标记样式，正方形
series3.setMarkerStyle(MarkerStyle.NONE);
// 折线图LineChart
// XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.CHARTREUSE));
XDDFLineProperties line = new XDDFLineProperties();
// line.setFillProperties(fill);
// line.setLineCap(LineCap.ROUND);
line.setPresetDash(new XDDFPresetLineDash(PresetLineDash.DOT));// 虚线
// XDDFShapeProperties shapeProperties = new XDDFShapeProperties();
// shapeProperties.setLineProperties(line);
// series3.setShapeProperties(shapeProperties);
series3.setLineProperties(line);
// 图表加载数据，平均线3
// 数据1，单元格范围位置[2, 0]到[2, 6]
XDDFNumericalDataSource<Double> population4 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(4, 4, 0, 6));
XDDFLineChartData.Series series4 = (XDDFLineChartData.Series) data.addSeries(countries, population4);
// 折线图例标题
series4.setTitle("人口平均", null);
// 直线
series4.setSmooth(false);
// 设置标记大小
// series4.setMarkerSize((short) 3);
// 设置标记样式，正方形
series4.setMarkerStyle(MarkerStyle.NONE);
XDDFLineProperties line4 = new XDDFLineProperties();
line4.setPresetDash(new XDDFPresetLineDash(PresetLineDash.DOT));// 虚线
series4.setLineProperties(line);
// 绘制
chart.plot(data);
// 打印图表的xml
// System.out.println(chart.getCTChart());
// 将输出写入excel文件
String filename = "排行榜前七的国家.xlsx";
fileOut = new FileOutputStream(filename);
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
} finally {
wb.close();
if (fileOut != null) {
fileOut.close();
}
}
}
}
单个柱状图
package com.poi.test;
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.PresetColor;
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.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.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
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;
/**
* POI EXCEL 图表-柱状图
*/
public class ApachePoiBarChart5 {
public static void main(String[] args) throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
String sheetName = "Sheet1";
FileOutputStream fileOut = null;
try {
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(8514877);
cell = row.createCell(5);
cell.setCellValue(7741220);
cell = row.createCell(6);
cell.setCellValue(3287263);
// 第三行，农村人口
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);
// 创建一个画布
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});
// bar：条形图，
XDDFBarChartData bar = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
// 设置为可变颜色
bar.setVaryColors(false);// 如果需要设置成自己想要的颜色，这里可变颜色要设置成false
// 条形图方向，纵向/横向：纵向
bar.setBarDirection(BarDirection.COL);
// 图表加载数据，条形图1
XDDFBarChartData.Series series1 = (XDDFBarChartData.Series) bar.addSeries(countries, area);
// 条形图例标题
series1.setTitle("面积图例", null);
XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.RED));
// 条形图，填充颜色
series1.setFillProperties(fill);
// 绘制
chart.plot(bar);
// CTBarSer ser = chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(0);
// CTLegend legend2 = chart.getCTChartSpace().getChart().getLegend();//更详细的图例设置
// 打印图表的xml
System.out.println(chart.getCTChart());
// 将输出写入excel文件
String filename = "排行榜前七的国家.xlsx";
fileOut = new FileOutputStream(filename);
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
} finally {
wb.close();
if (fileOut != null) {
fileOut.close();
}
}
}
}
3、POI EXCEL 双Y轴
package test;
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.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.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;
/**
* POI EXCEL 双Y轴
*/
public class Test5 {
public static void main(String[] args) throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
String sheetName = "Sheet1";
FileOutputStream fileOut = null;
try {
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(8514877);
cell = row.createCell(5);
cell.setCellValue(7741220);
cell = row.createCell(6);
cell.setCellValue(3287263);
// 第三行，农村人口
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);
// 创建一个画布
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);
/*双Y轴*/
// X轴
XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
bottomAxis.setTitle("X轴标题");
// bottomAxis.setVisible(false);// 隐藏X轴
// 左Y轴
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
// 左Y轴和X轴交叉点在X轴0点位置
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
// 构建坐标轴
leftAxis.crossAxis(bottomAxis);
bottomAxis.crossAxis(leftAxis);
// 设置左Y轴最大值
leftAxis.setMaximum(20000000);
// leftAxis.setVisible(false);// 隐藏Y轴
leftAxis.setTitle("左Y轴标题");
// 右Y轴
XDDFValueAxis rightAxis = chart.createValueAxis(AxisPosition.RIGHT);
// 右Y轴和X轴交叉点在X轴最大值位置
rightAxis.setCrosses(AxisCrosses.MAX);
rightAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
// 构建坐标轴
rightAxis.crossAxis(bottomAxis);
bottomAxis.crossAxis(rightAxis);
// 设置左Y轴最大值
rightAxis.setMaximum(40000000);
// rightAxis.setVisible(false);// 隐藏Y轴
rightAxis.setTitle("右Y轴标题");
// 图例位置
XDDFChartLegend legend = chart.getOrAddLegend();
legend.setPosition(LegendPosition.TOP);
// CellRangeAddress(起始行号，终止行号， 起始列号，终止列号）
// X轴数据，单元格范围位置[0, 0]到[0, 6]
XDDFDataSource<String> xdatas = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, 0, 0, 6));
// XDDFCategoryDataSource xdatas = XDDFDataSourcesFactory.fromArray(new String[] {"俄罗斯","加拿大","美国","中国","巴西","澳大利亚","印度"});
// 左Y轴数据，单元格范围位置[1, 0]到[1, 6]
XDDFNumericalDataSource<Double> leftYdatas = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, 6));
// XDDFNumericalDataSource<Integer> leftYdatas = XDDFDataSourcesFactory.fromArray(new Integer[] {17098242,9984670,9826675,9596961,8514877,7741220,3287263});
// 右Y轴数据，单元格范围位置[2, 0]到[2, 6]
XDDFNumericalDataSource<Double> rightYdatas = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, 6));
// XDDFNumericalDataSource<Integer> rightYdatas = XDDFDataSourcesFactory.fromArray(new Integer[] {17098242,9984670,9826675,9596961,8514877,7741220,3287263});
// LINE：折线图，
XDDFLineChartData line1 = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, rightAxis);
// 图表加载数据，折线1
XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) line1.addSeries(xdatas, rightYdatas);
// 折线图例标题
series1.setTitle("右Y轴数据的图例标题", null);
// 直线
series1.setSmooth(false);
// 设置标记大小
series1.setMarkerSize((short) 6);
// 设置标记样式，星星
series1.setMarkerStyle(MarkerStyle.STAR);
// 绘制
chart.plot(line1);
// LINE：折线图，
XDDFLineChartData line2 = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
// 图表加载数据，折线1
XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) line2.addSeries(xdatas, leftYdatas);
// 折线图例标题
series2.setTitle("左Y轴数据的图例标题", null);
// 直线
series2.setSmooth(false);
// 设置标记大小
series2.setMarkerSize((short) 6);
// 设置标记样式，星星
series2.setMarkerStyle(MarkerStyle.STAR);
// 绘制
chart.plot(line2);
// 填充与线条-标记-填充-依数据点着色（取消勾选）
chart.getCTChart().getPlotArea().getLineChartArray(0).addNewVaryColors().setVal(false);
// 打印图表的xml
// System.out.println(chart.getCTChart());
// 将输出写入excel文件
String filename = "双Y轴.xlsx";
fileOut = new FileOutputStream(filename);
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
} finally {
wb.close();
if (fileOut != null) {
fileOut.close();
}
}
}
}
4、POI EXCEL 图表-柱状图、条形图
package test;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Chart;
import org.apache.poi.ss.usermodel.Row;
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.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.CTLegend;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLineChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLineSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
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.STCrosses;
import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;
/**
* POI EXCEL 图表-柱状图、条形图
*/
public class BarAndLineChart2 {
public static void main(String[] args) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("Sheet1");
Row row;
Cell cell;
row = sheet.createRow(0);
row.createCell(0);
row.createCell(1).setCellValue("Bars");
row.createCell(2).setCellValue("Lines");
for (int r = 1; r < 7; r++) {
row = sheet.createRow(r);
cell = row.createCell(0);
cell.setCellValue("C" + r);
cell = row.createCell(1);
cell.setCellValue(new java.util.Random().nextDouble());
cell = row.createCell(2);
cell.setCellValue(new java.util.Random().nextDouble() * 10d);
}
XSSFDrawing drawing = sheet.createDrawingPatriarch();
// ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 4, 0, 11, 15);
XSSFClientAnchor anchor = (XSSFClientAnchor) drawing.createAnchor(0, 0, 0, 0, 4, 0, 11, 15);
Chart chart = drawing.createChart(anchor);
CTChart ctChart = ((XSSFChart) chart).getCTChart();
CTPlotArea ctPlotArea = ctChart.getPlotArea();
// the bar chart
CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
ctBoolean.setVal(true);
ctBarChart.addNewBarDir().setVal(STBarDir.COL);
// the bar series
CTBarSer ctBarSer = ctBarChart.addNewSer();
CTSerTx ctSerTx = ctBarSer.addNewTx();
CTStrRef ctStrRef = ctSerTx.addNewStrRef();
ctStrRef.setF("Sheet1!$B$1");
ctBarSer.addNewIdx().setVal(0);
CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();
ctStrRef = cttAxDataSource.addNewStrRef();
ctStrRef.setF("Sheet1!$A$2:$A$7");
CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
ctNumRef.setF("Sheet1!$B$2:$B$7");
// at least the border lines in Libreoffice Calc ;-)
ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] { 0, 0, 0 });
// telling the BarChart that it has axes and giving them Ids
ctBarChart.addNewAxId().setVal(123456); // cat axis 1 (bars)
ctBarChart.addNewAxId().setVal(123457); // val axis 1 (left)
// the line chart
CTLineChart ctLineChart = ctPlotArea.addNewLineChart();
ctBoolean = ctLineChart.addNewVaryColors();
ctBoolean.setVal(true);
// the line series
CTLineSer ctLineSer = ctLineChart.addNewSer();
ctSerTx = ctLineSer.addNewTx();
ctStrRef = ctSerTx.addNewStrRef();
ctStrRef.setF("Sheet1!$C$1");
ctLineSer.addNewIdx().setVal(1);
cttAxDataSource = ctLineSer.addNewCat();
ctStrRef = cttAxDataSource.addNewStrRef();
ctStrRef.setF("Sheet1!$A$2:$A$7");
ctNumDataSource = ctLineSer.addNewVal();
ctNumRef = ctNumDataSource.addNewNumRef();
ctNumRef.setF("Sheet1!$C$2:$C$7");
// at least the border lines in Libreoffice Calc ;-)
ctLineSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] { 0, 0, 0 });
// telling the LineChart that it has axes and giving them Ids
ctLineChart.addNewAxId().setVal(123458); // cat axis 2 (lines)
ctLineChart.addNewAxId().setVal(123459); // val axis 2 (right)
// cat axis 1 (bars)
CTCatAx ctCatAx = ctPlotArea.addNewCatAx();
ctCatAx.addNewAxId().setVal(123456); // id of the cat axis
CTScaling ctScaling = ctCatAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctCatAx.addNewDelete().setVal(false);
ctCatAx.addNewAxPos().setVal(STAxPos.B);
ctCatAx.addNewCrossAx().setVal(123457); // id of the val axis
ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
// val axis 1 (left)
CTValAx ctValAx = ctPlotArea.addNewValAx();
ctValAx.addNewAxId().setVal(123457); // id of the val axis
ctScaling = ctValAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctValAx.addNewDelete().setVal(false);
ctValAx.addNewAxPos().setVal(STAxPos.L);
ctValAx.addNewCrossAx().setVal(123456); // id of the cat axis
ctValAx.addNewCrosses().setVal(STCrosses.AUTO_ZERO); // this val axis crosses the cat axis at zero
ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
// cat axis 2 (lines)
ctCatAx = ctPlotArea.addNewCatAx();
ctCatAx.addNewAxId().setVal(123458); // id of the cat axis
ctScaling = ctCatAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctCatAx.addNewDelete().setVal(true); // this cat axis is deleted
ctCatAx.addNewAxPos().setVal(STAxPos.B);
ctCatAx.addNewCrossAx().setVal(123459); // id of the val axis
ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
// val axis 2 (right)
ctValAx = ctPlotArea.addNewValAx();
ctValAx.addNewAxId().setVal(123459); // id of the val axis
ctScaling = ctValAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctValAx.addNewDelete().setVal(false);
ctValAx.addNewAxPos().setVal(STAxPos.R);
ctValAx.addNewCrossAx().setVal(123458); // id of the cat axis
ctValAx.addNewCrosses().setVal(STCrosses.MAX); // this val axis crosses the cat axis at max value
ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
// legend
CTLegend ctLegend = ctChart.addNewLegend();
ctLegend.addNewLegendPos().setVal(STLegendPos.B);
ctLegend.addNewOverlay().setVal(false);
// 打印图表的xml
// System.out.println(ctChart);
FileOutputStream fileOut = new FileOutputStream("BarAndLineChart2.xlsx");
wb.write(fileOut);
fileOut.close();
wb.close();
}
}
package test;
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.PresetColor;
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.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.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
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;
/**
* POI EXCEL 图表-柱状图
*/
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);
// 第一行，国家名称
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(8514877);
cell = row.createCell(5);
cell.setCellValue(7741220);
cell = row.createCell(6);
cell.setCellValue(3287263);
// 第三行，农村人口
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);
// 创建一个画布
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]
XDDFNumericalDataSource<Double> population = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, 6));
// bar：条形图，
XDDFBarChartData bar = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
// 设置为可变颜色
bar.setVaryColors(true);
// 条形图方向，纵向/横向：纵向
bar.setBarDirection(BarDirection.COL);
// 图表加载数据，条形图1
XDDFBarChartData.Series series1 = (XDDFBarChartData.Series) bar.addSeries(countries, area);
// 条形图例标题
series1.setTitle("面积", null);
XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.RED));
// 条形图，填充颜色
series1.setFillProperties(fill);
// 图表加载数据，条形图2
XDDFBarChartData.Series series2 = (XDDFBarChartData.Series) bar.addSeries(countries, population);
// 条形图例标题
series2.setTitle("人口", null);
XDDFSolidFillProperties fill2 = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.BLUE));
// 条形图，填充颜色
series2.setFillProperties(fill2);
// 绘制
chart.plot(bar);
// 打印图表的xml
// System.out.println(chart.getCTChart());
// 将输出写入excel文件
String filename = "排行榜前七的国家.xlsx";
fileOut = new FileOutputStream(filename);
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
} finally {
wb.close();
if (fileOut != null) {
fileOut.close();
}
}
}
}
5、POI EXCEL 饼图
package test;
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.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.XDDFChartData;
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.XDDFNumericalDataSource;
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;
/**
* POI EXCEL 饼图
*/
public class ApachePoiPieChart {
public static void main(String[] args) throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
FileOutputStream fileOut = null;
try {
XSSFSheet sheet = wb.createSheet("Sheet1");
// 第一行，国家名称
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(8514877);
cell = row.createCell(5);
cell.setCellValue(7741220);
cell = row.createCell(6);
cell.setCellValue(3287263);
// 第三行，农村人口
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);
// 创建一个画布
XSSFDrawing drawing = sheet.createDrawingPatriarch();
// 前四个默认0，[0,4]：从0列4行开始;[7,20]:到7列20行结束
// 默认宽度(14-8)*12
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 4, 7, 20);
// 创建一个chart对象
XSSFChart chart = drawing.createChart(anchor);
// 标题
chart.setTitleText("地区排名前七的国家");
// 标题是否覆盖图表
chart.setTitleOverlay(false);
// 图例位置
XDDFChartLegend legend = chart.getOrAddLegend();
legend.setPosition(LegendPosition.TOP_RIGHT);
// CellRangeAddress(起始行号，终止行号， 起始列号，终止列号）
// 分类轴标数据，
XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, 0, 0, 6));
// 数据1，
XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, 6));
// XDDFChartData data = chart.createData(ChartTypes.PIE3D, null, null);
XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);
// 设置为可变颜色
data.setVaryColors(true);
// 图表加载数据
data.addSeries(countries, values);
// 绘制
chart.plot(data);
CTDLbls dLbls = chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).addNewDLbls();
dLbls.addNewShowVal().setVal(false);
dLbls.addNewShowLegendKey().setVal(false);
dLbls.addNewShowCatName().setVal(true);// 类别名称
dLbls.addNewShowSerName().setVal(false);
dLbls.addNewShowPercent().setVal(true);// 百分比
dLbls.addNewShowLeaderLines().setVal(true);// 引导线
dLbls.setSeparator("\n");// 分隔符为分行符
dLbls.addNewDLblPos().setVal(org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos.Enum.forString("inEnd"));// 数据标签内
// 打印图表的xml
// System.out.println(chart.getCTChart());
// 将输出写入excel文件
fileOut = new FileOutputStream("pie-chart-top-seven-countries.xlsx");
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
} finally {
wb.close();
fileOut.close();
}
}
}
6、POI EXCEL 散点图
package test;
import java.awt.Color;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
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.XDDFNoFillProperties;
import org.apache.poi.xddf.usermodel.chart.AxisCrosses;
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.ScatterStyle;
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.XDDFScatterChartData;
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.CTCatAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTChartSpace;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTDPt;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTScatterChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTScatterSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
import org.openxmlformats.schemas.drawingml.x2006.main.CTLineProperties;
import org.openxmlformats.schemas.drawingml.x2006.main.CTShapeProperties;
import org.openxmlformats.schemas.drawingml.x2006.main.CTSolidColorFillProperties;
import org.openxmlformats.schemas.drawingml.x2006.main.STSchemeColorVal.Enum;
/**
* 散点图
*/
public class ApachePoiScatterChart {
public static void main(String[] args) throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
String sheetName = "Sheet1";
FileOutputStream fileOut = null;
try {
XSSFSheet sheet = wb.createSheet(sheetName);
// 第一行，表头名称
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("a");
cell = row.createCell(1);
cell.setCellValue("b");
cell = row.createCell(2);
cell.setCellValue("c");
// 数据1
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellValue(1);
cell = row.createCell(1);
cell.setCellValue(1);
cell = row.createCell(2);
cell.setCellValue(2);
// 数据2
row = sheet.createRow(2);
cell = row.createCell(0);
cell.setCellValue(2);
cell = row.createCell(1);
// cell.setCellValue(2);
cell = row.createCell(2);
cell.setCellValue(3);
// 数据3
row = sheet.createRow(3);
cell = row.createCell(0);
cell.setCellValue(3);
cell = row.createCell(1);
cell.setCellValue(3);
cell = row.createCell(2);
// cell.setCellValue(4);
// 数据4
row = sheet.createRow(4);
cell = row.createCell(0);
cell.setCellValue(4);
cell = row.createCell(1);
cell.setCellValue(4);
cell = row.createCell(2);
cell.setCellValue(5);
// 数据5
row = sheet.createRow(5);
cell = row.createCell(0);
cell.setCellValue(5);
cell = row.createCell(1);
cell.setCellValue(5);
cell = row.createCell(2);
cell.setCellValue(5);
// 数据6
row = sheet.createRow(6);
cell = row.createCell(0);
cell.setCellValue(6);
cell = row.createCell(1);
cell.setCellValue(6);
cell = row.createCell(2);
cell.setCellValue(5);
// 数据7
row = sheet.createRow(7);
cell = row.createCell(0);
cell.setCellValue(7);
cell = row.createCell(1);
cell.setCellValue(7);
cell = row.createCell(2);
cell.setCellValue(5);
// 创建一个画布
XSSFDrawing drawing = sheet.createDrawingPatriarch();
// 前四个默认0，[8,7]：从8列7行开始;[15,22]:到15列22行结束
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 8, 7, 15, 22);
// 创建一个chart对象
XSSFChart chart = drawing.createChart(anchor);
// 设置画布边框样式
CTChartSpace space = chart.getCTChartSpace();
space.addNewRoundedCorners().setVal(false);// 去掉圆角
CTLineProperties ln = space.addNewSpPr().addNewLn();
CTSolidColorFillProperties solidFill = ln.addNewSolidFill();
Color color = new Color(217, 217, 217);// 灰色边框
solidFill.addNewSrgbClr().setVal(new byte[] { (byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue() });
// 标题
chart.setTitleText("图表标题");
// 标题覆盖
chart.setTitleOverlay(false);
// 图例位置
XDDFChartLegend legend = chart.getOrAddLegend();
legend.setPosition(LegendPosition.TOP);
// 分类轴标(X轴),标题位置,XDDFCategoryAxis会乱码
XDDFValueAxis bottomAxis = chart.createValueAxis(AxisPosition.BOTTOM);
// bottomAxis.setMinimum(0);
// 设置左X轴最大值
// bottomAxis.setMaximum(8);
// bottomAxis.setTitle("X轴标题");
// 值(Y轴)轴,标题位置
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
// leftAxis.setTitle("Y轴标题");
// 左Y轴和X轴交叉点在X轴0点位置
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
// leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
// 构建坐标轴
leftAxis.crossAxis(bottomAxis);
bottomAxis.crossAxis(leftAxis);
// 设置左Y轴最大值
// leftAxis.setMaximum(8);
// leftAxis.setVisible(false);// 隐藏Y轴
// CellRangeAddress(起始行号，终止行号， 起始列号，终止列号）
// 分类轴标(X轴)数据，单元格范围位置[0, 1]到[0, 7]
// XDDFDataSource<Double> category = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 7, 0, 0));
XDDFNumericalDataSource<Integer> category = XDDFDataSourcesFactory.fromArray(new Integer[] { 1, 2, 3, 4, 5, 6, 7 });
// 数据1，单元格范围位置[1, 1]到[1, 7]
XDDFNumericalDataSource<Double> data1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 7, 1, 1));
// XDDFNumericalDataSource data1 = XDDFDataSourcesFactory.fromArray(new Integer[] {1,2,3,4,5,6,7});
// 数据2，单元格范围位置[2, 1]到[2, 7]
XDDFNumericalDataSource<Double> data2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 7, 2, 2));
// XDDFNumericalDataSource<Integer> data2 = XDDFDataSourcesFactory.fromArray(new Integer[] {2,3,4,5,5,5,5});
// Scatter：散点图
XDDFScatterChartData scatter = (XDDFScatterChartData) chart.createData(ChartTypes.SCATTER, bottomAxis, leftAxis);
// 设置为可变颜色
// scatter.setVaryColors(true);
scatter.setStyle(ScatterStyle.LINE_MARKER);
// 图表加载数据，数据1
XDDFScatterChartData.Series series1 = (XDDFScatterChartData.Series) scatter.addSeries(category, data1);
// 散点图例标题
series1.setTitle("图例标题b", null);
// XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.YELLOW));
XDDFNoFillProperties fill = new XDDFNoFillProperties();
// 散点图
series1.setFillProperties(fill);
series1.setSmooth(false);
series1.setMarkerStyle(MarkerStyle.CIRCLE);
series1.setMarkerSize((short) 5);
// 绘制
series1.plot();
// 设置颜色
color = new Color(255, 0, 0);// 红色
byte[] colorByte = new byte[] { (byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue() };
CTScatterChart scatterChart = chart.getCTChart().getPlotArea().getScatterChartArray(0);
CTScatterSer ser = scatterChart.getSerArray(0);
CTShapeProperties spPr = ser.getMarker().addNewSpPr();
spPr.addNewSolidFill().addNewSrgbClr().setVal(colorByte);
// 无轮廓
spPr.addNewLn().addNewNoFill();
// 隐藏值为空或者0的点
for (int i = 0; i < data1.getPointCount(); i++) {
Double pointAt = data1.getPointAt(i);
if (pointAt == null || pointAt == 0) {
CTDPt dDPt = ser.addNewDPt();
dDPt.addNewIdx().setVal(i);
CTShapeProperties addNewSpPr = dDPt.addNewMarker().addNewSpPr();
addNewSpPr.addNewNoFill();
addNewSpPr.addNewLn().addNewNoFill();
}
}
// 图表加载数据，数据2
XDDFScatterChartData.Series series2 = (XDDFScatterChartData.Series) scatter.addSeries(category, data2);
// 散点图例标题
series2.setTitle("图例标题c", null);
// XDDFSolidFillProperties fill2 = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.RED));
XDDFNoFillProperties fill2 = new XDDFNoFillProperties();
// 散点图
series2.setFillProperties(fill2);
series2.setSmooth(false);
series2.setMarkerStyle(MarkerStyle.CIRCLE);
series2.setMarkerSize((short) 5);
// 绘制
series2.plot();
// 设置颜色
color = new Color(0, 0, 139);// 深蓝色
colorByte = new byte[] { (byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue() };
scatterChart = chart.getCTChart().getPlotArea().getScatterChartArray(0);
ser = scatterChart.getSerArray(1);
spPr = ser.getMarker().addNewSpPr();
spPr.addNewSolidFill().addNewSrgbClr().setVal(colorByte);
// 无轮廓
spPr.addNewLn().addNewNoFill();
// 隐藏值为空或者0的点
for (int i = 0; i < data2.getPointCount(); i++) {
Double pointAt = data2.getPointAt(i);
if (pointAt == null || pointAt <= 0) {
CTDPt dDPt = ser.addNewDPt();
dDPt.addNewIdx().setVal(i);
CTShapeProperties addNewSpPr = dDPt.addNewMarker().addNewSpPr();
addNewSpPr.addNewNoFill();
addNewSpPr.addNewLn().addNewNoFill();
}
}
// 绘制
// chart.plot(scatter);
// 去掉连接线
List<CTScatterSer> serList = scatterChart.getSerList();
for (CTScatterSer sser : serList) {
sser.getSpPr().addNewLn().addNewNoFill();
// 删掉x轴系列值
sser.unsetXVal();
}
Enum bg2 = org.openxmlformats.schemas.drawingml.x2006.main.STSchemeColorVal.Enum.forString("bg2");
// 设置网格线水平方向
List<CTValAx> valAxList = chart.getCTChart().getPlotArea().getValAxList();
for (CTValAx valAx : valAxList) {
valAx.addNewMajorGridlines().addNewSpPr().addNewLn().addNewSolidFill().addNewSchemeClr().setVal(bg2);
}
// 设置网格线垂直方向
List<CTCatAx> catAxList = chart.getCTChart().getPlotArea().getCatAxList();
for (CTCatAx catAx : catAxList) {
catAx.addNewMajorGridlines().addNewSpPr().addNewLn().addNewSolidFill().addNewSchemeClr().setVal(bg2);
}
// 打印图表的xml
System.out.println(chart.getCTChart());
System.out.println();
System.out.println("***********************************************************");
System.out.println();
System.out.println("上面打印的xml节点就是 chart.getCTChart() 方法得到的api一一对应");
System.out.println();
System.out.println("***********************************************************");
// 将输出写入excel文件
String filename = "散点图.xlsx";
fileOut = new FileOutputStream(filename);
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
} finally {
wb.close();
if (fileOut != null) {
fileOut.close();
}
}
}
}
7、POI api
PS：
怎么设置各种复杂的样式，查各种api？
1、新建一个excel，将需要的样式，在一个Excel中实现想要的功能，然后重命名为zip解压，打开：xl\charts\chart1.xml。
不想解压直接打印新建excel的xml：System.out.println(chart.getCTChart())
2、CTChart ctChart = chart.getCTChart();//该类中的属性就是上面xml解压文件的节点属性一一对应。
打印poi
package com.demo.test;
import java.io.FileInputStream;
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.CTChart;
public class Test {
public static void main(String[] args) throws Exception {
String path = "D:/已经实现了功能的excel/test.xlsx";
FileInputStream fis = new FileInputStream(path);
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet sheet = wb.getSheetAt(0);
XSSFDrawing drawing = sheet.getDrawingPatriarch();
List<XSSFChart> charts = drawing.getCharts();
for (int i = 0; i < charts.size(); i++) {
XSSFChart chart = charts.get(i);
CTChart ctChart = chart.getCTChart();
System.out.println(ctChart);
}
fis.close();
wb.close();
}
}
1070
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
