1、依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/jfree/jcommon -->
<dependency>
<groupId>jfree</groupId>
<artifactId>jcommon</artifactId>
<version>1.0.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/jfree/jfreechart -->
<dependency>
<groupId>jfree</groupId>
<artifactId>jfreechart</artifactId>
<version>1.0.13</version>
</dependency>
2、代码块1
package com.wwzh.vplus.util;
import com.wwzh.vplus.common.utils.CommonUtil;
import com.wwzh.vplus.web.pc.chart.bean.SpcChartDetailImportVo;
import com.wwzh.vplus.web.pc.chart.bean.SpcLineChartVo;
import java.io.File;
import java.io.IOException;
import java.util.List;
import org.apache.poi.xddf.usermodel.PresetColor;
import org.apache.poi.xddf.usermodel.PresetLineDash;
import org.apache.poi.xddf.usermodel.XDDFColor;
import org.apache.poi.xddf.usermodel.XDDFLineProperties;
import org.apache.poi.xddf.usermodel.XDDFPresetLineDash;
import org.apache.poi.xddf.usermodel.XDDFShapeProperties;
import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Component;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
/**
* @author 28219
*/
@Component
public class SpcExcelChartUtil {
public static String funcation(List<SpcChartDetailImportVo> detailImportList, File file) {
SpcExcelChartUtil ecu = new SpcExcelChartUtil();
FileOutputStream fileOut = null;
String filename = null;
try {
InputStream inputStream = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
XSSFSheet sheet = wb.getSheet("SPC模板");
int before = 0;
for (int i = 0; i < detailImportList.size(); i++) {
int end = before + 26 * (i);
int start = end + 1;
if (i == 0) {
if (detailImportList.size() + 10 <= 35) {
ecu.createTimeXYChar(sheet, detailImportList.get(i).getLineChart(), 35 + 1, 35 + 24);
before = 35;
} else {
ecu.createTimeXYChar(sheet, detailImportList.get(i).getLineChart(), 61 + 1, 61 + 24);
before = 61;
}
} else {
ecu.createTimeXYChar(sheet, detailImportList.get(i).getLineChart(), start, start + 26 - 3);
}
}
// 将输出写入excel文件
filename = System.getProperty("java.io.tmpdir") + "spc.xlsx";
fileOut = new FileOutputStream(filename);
wb.write(fileOut);
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
return filename;
}
public void createTimeXYChar(XSSFSheet sheet, SpcLineChartVo lineChart, int start, int end) {
// 创建一个画布
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, start, 9, end);
// 创建一个chart对象
XSSFChart chart = drawing.createChart(anchor);
// 标题
chart.setTitleText(lineChart.getInternalNum() + "-" + lineChart.getImpurityName());
// 标题覆盖
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(lineChart.getImpurityUnitName());
if (CommonUtil.isEmpty(lineChart.getXAxis())) {
return;
}
// CellRangeAddress(起始行号,终止行号, 起始列号,终止列号)
// 分类轴标(X轴)数据,单元格范围位置[0, 0]到[0, 6]
// XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, 0, 0, 6));
XDDFCategoryDataSource countries = XDDFDataSourcesFactory
.fromArray((String[]) (lineChart.getXAxis().toArray(new String[lineChart.getXAxis().size()])));
// 数据1,单元格范围位置[1, 0]到[1, 6]
// XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, 6));
XDDFNumericalDataSource<Double> area = null;
// 数据1,单元格范围位置[2, 0]到[2, 6]
// XDDFNumericalDataSource<Double> population = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, 6));
XDDFNumericalDataSource<Double> population = null;
// 图表加载数据,平均线3
// 数据1,单元格范围位置[2, 0]到[2, 6]
// XDDFNumericalDataSource<Double> population3 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(3, 3, 0, 6));
XDDFNumericalDataSource<Double> population3 = null;
// 图表加载数据,平均线3
// 数据1,单元格范围位置[2, 0]到[2, 6]
// XDDFNumericalDataSource<Double> population4 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(4, 4, 0, 6));
XDDFNumericalDataSource<Double> population4 = null;
XDDFNumericalDataSource<Double> population5 = null;
XDDFNumericalDataSource<Double> population6 = null;
for (int j = 0; j < lineChart.getSeriesVoList().size(); j++) {
if (lineChart.getSeriesVoList().get(j).getSpcSeriesType().equals(Integer.valueOf(10))) {
if (CommonUtil.isNotEmpty(lineChart.getSeriesVoList().get(j).getValueList())) {
area = XDDFDataSourcesFactory
.fromArray(ChartsUtils.convertDoubleImpList(lineChart.getSeriesVoList().get(j).getValueList()));
}
}
if (lineChart.getSeriesVoList().get(j).getSpcSeriesType().equals(Integer.valueOf(1))) {
if (CommonUtil.isNotEmpty(lineChart.getSeriesVoList().get(j).getValueList())) {
population = XDDFDataSourcesFactory
.fromArray(ChartsUtils.convertDoubleImpList(lineChart.getSeriesVoList().get(j).getValueList()));
}
}
if (lineChart.getSeriesVoList().get(j).getSpcSeriesType().equals(Integer.valueOf(2))) {
if (CommonUtil.isNotEmpty(lineChart.getSeriesVoList().get(j).getValueList())) {
population3 = XDDFDataSourcesFactory
.fromArray(ChartsUtils.convertDoubleImpList(lineChart.getSeriesVoList().get(j).getValueList()));
}
}
if (lineChart.getSeriesVoList().get(j).getSpcSeriesType().equals(Integer.valueOf(3))) {
if (CommonUtil.isNotEmpty(lineChart.getSeriesVoList().get(j).getValueList())) {
population4 = XDDFDataSourcesFactory
.fromArray(ChartsUtils.convertDoubleImpList(lineChart.getSeriesVoList().get(j).getValueList()));
}
}
if (lineChart.getSeriesVoList().get(j).getSpcSeriesType().equals(Integer.valueOf(4))) {
if (CommonUtil.isNotEmpty(lineChart.getSeriesVoList().get(j).getValueList())) {
population5 = XDDFDataSourcesFactory
.fromArray(ChartsUtils.convertDoubleImpList(lineChart.getSeriesVoList().get(j).getValueList()));
}
}
if (lineChart.getSeriesVoList().get(j).getSpcSeriesType().equals(Integer.valueOf(5))) {
if (CommonUtil.isNotEmpty(lineChart.getSeriesVoList().get(j).getValueList())) {
population6 = XDDFDataSourcesFactory
.fromArray(ChartsUtils.convertDoubleImpList(lineChart.getSeriesVoList().get(j).getValueList()));
}
}
}
// LINE:折线图,
XDDFLineChartData data = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
if (CommonUtil.isNotEmpty(area)) {
// 图表加载数据,折线1
XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) data.addSeries(countries, area);
// 折线图例标题
series1.setTitle("data", null);
// 直线
series1.setSmooth(false);
// 设置标记大小
series1.setMarkerSize((short) 8);
// 设置标记样式,星星
series1.setMarkerStyle(MarkerStyle.CIRCLE);
solidLineSeries(series1, PresetColor.DARK_BLUE);
}
if (CommonUtil.isNotEmpty(population)) {
// 图表加载数据,折线2
XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) data.addSeries(countries, population);
// 折线图例标题
series2.setTitle("USL", null);
// 曲线
// series2.setSmooth(true);
series2.setSmooth(false);
// 设置标记大小
series2.setMarkerSize((short) 6);
// 设置标记样式,正方形
series2.setMarkerStyle(MarkerStyle.NONE);
solidLineSeries(series2, PresetColor.LIGHT_GREEN);
}
XDDFLineProperties line = new XDDFLineProperties();
if (CommonUtil.isNotEmpty(population3)) {
XDDFLineChartData.Series series3 = (XDDFLineChartData.Series) data.addSeries(countries, population3);
// 折线图例标题
series3.setTitle("LSL", null);
// 直线
series3.setSmooth(false);
// 设置标记大小
series3.setMarkerSize((short) 6);
// 设置标记样式,圆形
series3.setMarkerStyle(MarkerStyle.NONE);
// 折线图LineChart
// XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.CHARTREUSE));
// line.setFillProperties(fill);
// line.setLineCap(LineCap.ROUND);
// line.setPresetDash(new XDDFPresetLineDash(PresetLineDash.DOT));// 虚线
// XDDFShapeProperties shapeProperties = new XDDFShapeProperties();
// shapeProperties.setLineProperties(line);
// series3.setShapeProperties(shapeProperties);
solidLineSeries(series3, PresetColor.YELLOW);
// series3.setLineProperties(line);
}
if (CommonUtil.isNotEmpty(population4)) {
XDDFLineChartData.Series series4 = (XDDFLineChartData.Series) data.addSeries(countries, population4);
// 折线图例标题
series4.setTitle("UCL", null);
// 直线
series4.setSmooth(false);
// 设置标记大小
series4.setMarkerSize((short) 6);
series4.setMarkerStyle(MarkerStyle.NONE);
XDDFLineProperties line4 = new XDDFLineProperties();
// line4.setPresetDash(new XDDFPresetLineDash(PresetLineDash.DOT));// 虚线
// series4.setLineProperties(line);
solidLineSeries(series4, PresetColor.RED);
}
if (CommonUtil.isNotEmpty(population5)) {
XDDFLineChartData.Series series5 = (XDDFLineChartData.Series) data.addSeries(countries, population5);
// 折线图例标题
series5.setTitle("LCL", null);
// 直线
series5.setSmooth(false);
// 设置标记大小
series5.setMarkerSize((short) 6);
series5.setMarkerStyle(MarkerStyle.NONE);
XDDFLineProperties line5 = new XDDFLineProperties();
// line5.setPresetDash(new XDDFPresetLineDash(PresetLineDash.DOT));// 虚线
// series5.setLineProperties(line);
solidLineSeries(series5, PresetColor.LIGHT_BLUE);
}
if (CommonUtil.isNotEmpty(population6)) {
XDDFLineChartData.Series series6 = (XDDFLineChartData.Series) data.addSeries(countries, population6);
// 折线图例标题
series6.setTitle("CL", null);
// 直线
series6.setSmooth(false);
// 设置标记大小
series6.setMarkerSize((short) 6);
series6.setMarkerStyle(MarkerStyle.NONE);
XDDFLineProperties line6 = new XDDFLineProperties();
// line6.setPresetDash(new XDDFPresetLineDash(PresetLineDash.DOT));// 虚线
// series6.setLineProperties(line);
solidLineSeries(series6, PresetColor.DARK_GREEN);
}
// 绘制
chart.plot(data);
}
private static void solidLineSeries(XDDFLineChartData.Series series,PresetColor color) {
XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
XDDFLineProperties line = new XDDFLineProperties();
line.setFillProperties(fill);
XDDFShapeProperties properties = series.getShapeProperties();
if (properties == null) {
properties = new XDDFShapeProperties();
}
properties.setLineProperties(line);
series.setShapeProperties(properties);
}
}
3、
4、添加链接描述
5、代码块2
package com.wwzh.vplus.util;
import com.wwzh.vplus.common.utils.CommonUtil;
import com.wwzh.vplus.web.pc.chart.bean.SpcChartDetailImportVo;
import com.wwzh.vplus.web.pc.chart.bean.SpcLineChartVo;
import java.io.File;
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.PresetColor;
import org.apache.poi.xddf.usermodel.PresetLineDash;
import org.apache.poi.xddf.usermodel.XDDFColor;
import org.apache.poi.xddf.usermodel.XDDFLineProperties;
import org.apache.poi.xddf.usermodel.XDDFPresetLineDash;
import org.apache.poi.xddf.usermodel.XDDFShapeProperties;
import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Component;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
/**
* @author 28219
*/
@Component
public class SpcExcelChartUtil {
public static void main(String[] args) throws Exception {
InputStream inputStream = new FileInputStream("D:\\3333.xlsx");
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
XSSFSheet sheet = wb.getSheet("333");
// String sheetName = "Sheet1";
FileOutputStream fileOut = null;
try {
// 第一行,国家名称
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);
for (int i = 0; i < 5; i++) {
// 创建一个画布
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, (i * 20) + 0, 7, (i * 20) + 20);
// 创建一个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));
// 数据1,单元格范围位置[1, 0]到[1, 6]
XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, 6));
// 数据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 = System.currentTimeMillis() + "排行榜前七的国家.xlsx";
fileOut = new FileOutputStream(filename);
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
} finally {
wb.close();
if (fileOut != null) {
fileOut.close();
}
}
}
}