![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/f53b5d468bcd4170bc8dc9631a39a185.png#pic_center)
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
import java.awt.Color;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
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.CTDLbls;
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.CTMarker;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTMarkerStyle;
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.STBarGrouping;
import org.openxmlformats.schemas.drawingml.x2006.chart.STCrosses;
import org.openxmlformats.schemas.drawingml.x2006.chart.STDispBlanksAs;
import org.openxmlformats.schemas.drawingml.x2006.chart.STGrouping;
import org.openxmlformats.schemas.drawingml.x2006.chart.STLblAlgn;
import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STMarkerStyle;
import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickMark;
public class XSSFUtils {
public static void testForLineChart(XSSFSheet sheet) {
Map<String, Object> arams = new HashMap<String, Object>();
int[] chartPositon = new int[] {getColumnIndexByAddress("B"), 35, getColumnIndexByAddress("AA"), 52, 0, 0, 0, 0};
int[] xAxisRange = new int[] {59, 89, XSSFUtils.getColumnIndexByAddress("B"), XSSFUtils.getColumnIndexByAddress("B")};
List<int[]> seriesRangeList = new ArrayList<int[]>();
seriesRangeList.add(new int[] {
58, 58, XSSFUtils.getColumnIndexByAddress("D"), XSSFUtils.getColumnIndexByAddress("D"),
59, 89, XSSFUtils.getColumnIndexByAddress("D"), XSSFUtils.getColumnIndexByAddress("D")});
seriesRangeList.add(new int[] {
58, 58, XSSFUtils.getColumnIndexByAddress("J"), XSSFUtils.getColumnIndexByAddress("J"),
59, 89, XSSFUtils.getColumnIndexByAddress("J"), XSSFUtils.getColumnIndexByAddress("J")});
seriesRangeList.add(new int[] {
58, 58, XSSFUtils.getColumnIndexByAddress("P"), XSSFUtils.getColumnIndexByAddress("P"),
59, 89, XSSFUtils.getColumnIndexByAddress("P"), XSSFUtils.getColumnIndexByAddress("P")});
arams.put("chartPosition", chartPositon);
arams.put("chartTitle", "");
arams.put("dispBlanksAs", "zero");
arams.put("legendPosition", "t");
arams.put("xAxisDataCellRange", xAxisRange);
arams.put("seriesDataCellRangeList", seriesRangeList);
arams.put("lineColor", null);
arams.put("isXAxisDelete", false);
arams.put("yAxisPosition", "l");
createLineChart(sheet, arams);
}
public static void testForBarChart(XSSFSheet sheet, Integer startRow, Integer endRow) {
Map<String, Object> params = new HashMap<String, Object>();
int[] chartPositon = new int[] {XSSFUtils.getColumnIndexByAddress("L"), 15,
XSSFUtils.getColumnIndexByAddress("R"), 28, 10, 0, 30, 0};
String chartTitle = "项目1";
int[] xAxisRange = new int[] {startRow, endRow, XSSFUtils.getColumnIndexByAddress("A"),
XSSFUtils.getColumnIndexByAddress("A")};
List<int[]> seriesRangeList = new ArrayList<int[]>();
seriesRangeList.add(
new int[] {
startRow - 1, startRow - 1, XSSFUtils.getColumnIndexByAddress("B"), XSSFUtils.getColumnIndexByAddress("B"),
startRow, endRow, XSSFUtils.getColumnIndexByAddress("B"), XSSFUtils.getColumnIndexByAddress("B")});
params.put("chartPosition", chartPositon);
params.put("chartTitle", chartTitle);
params.put("dispBlanksAs", "gap");
params.put("legendPosition", "r");
params.put("xAxisDataCellRange", xAxisRange);
params.put("seriesDataCellRangeList", seriesRangeList);
params.put("barColor", new XSSFColor(new Color(247, 150, 70)));
createBarChart(sheet, params);
}
public static void testForComboChart(XSSFSheet sheet, Integer startRow, Integer endRow) {
Map<String, Object> params = new HashMap<String, Object>();
int[] chartPosition =
new int[] {XSSFUtils.getColumnIndexByAddress("L"), 0, XSSFUtils.getColumnIndexByAddress("R"), 14, 10
, 0, 30, 0};
String chartTitle = "项目2";
int[] xAxisRange = new int[] {startRow, endRow, XSSFUtils.getColumnIndexByAddress("A"),
XSSFUtils.getColumnIndexByAddress("A")};
List<int[]> barSeriesRangeList = new ArrayList<int[]>();
barSeriesRangeList.add(
new int[] {
startRow - 1, startRow - 1, XSSFUtils.getColumnIndexByAddress("B"), XSSFUtils.getColumnIndexByAddress("B"),
startRow, endRow, XSSFUtils.getColumnIndexByAddress("B"), XSSFUtils.getColumnIndexByAddress("B")});
List<int[]> lineSeriesRangeList = new ArrayList<int[]>();
lineSeriesRangeList.add(
new int[] {
startRow - 1, startRow - 1, XSSFUtils.getColumnIndexByAddress("C"), XSSFUtils.getColumnIndexByAddress("C"),
startRow, endRow, XSSFUtils.getColumnIndexByAddress("C"), XSSFUtils.getColumnIndexByAddress("C")});
params.put("chartPosition", chartPosition);
params.put("chartTitle", chartTitle);
params.put("dispBlanksAs", "gap");
params.put("legendPosition", "r");
params.put("barXAxisDataCellRange", xAxisRange);
params.put("barSeriesDataCellRangeList", barSeriesRangeList);
params.put("barColor", new XSSFColor(new Color(79, 129, 189)));
params.put("lineXAxisDataCellRange", xAxisRange);
params.put("lineSeriesDataCellRangeList", lineSeriesRangeList);
params.put("lineColor", new XSSFColor(new Color(190, 75, 72)));
params.put("lineIsXAxisDelete", true);
params.put("lineYAxisPosition", "r");
XSSFUtils.createComboChart(sheet, params);
}
@SuppressWarnings("unchecked")
public static void createLineChart(XSSFSheet sheet, Map<String, Object> params) {
CTPlotArea ctPlotArea = createCTPlotArea(sheet, params);
createCTLineChart(sheet, ctPlotArea, params);
}
@SuppressWarnings("unchecked")
public static void createBarChart(XSSFSheet sheet, Map<String, Object> params) {
CTPlotArea ctPlotArea = createCTPlotArea(sheet, params);
createCTBarChart(sheet, ctPlotArea, params);
}
@SuppressWarnings("unchecked")
public static void createComboChart(XSSFSheet sheet, Map<String, Object> params) {
Map<String, Object> barParams = new HashMap<String, Object>();
Map<String, Object> lineParams = new HashMap<String, Object>();
barParams.put("xAxisDataCellRange", params.get("barXAxisDataCellRange"));
barParams.put("seriesDataCellRangeList", params.get("barSeriesDataCellRangeList"));
barParams.put("barColor", params.get("barColor"));
lineParams.put("xAxisDataCellRange", params.get("lineXAxisDataCellRange"));
lineParams.put("seriesDataCellRangeList", params.get("lineSeriesDataCellRangeList"));
lineParams.put("lineColor", params.get("lineColor"));
lineParams.put("isXAxisDelete", params.get("lineIsXAxisDelete"));
lineParams.put("yAxisPosition", params.get("lineYAxisPosition"));
CTPlotArea ctPlotArea = createCTPlotArea(sheet, params);
createCTBarChart(sheet, ctPlotArea, barParams);
createCTLineChart(sheet, ctPlotArea, lineParams);
}
private static void createCTLineChart(XSSFSheet sheet, CTPlotArea ctPlotArea, Map<String, Object> params) {
int[] xAxisDataCellRange = (int[]) params.get("xAxisDataCellRange");
List<int[]> seriesDataCellRangeList = (List<int[]>) params.get("seriesDataCellRangeList");
XSSFColor lineColor = (XSSFColor) params.get("lineColor");
Boolean isXAxisDelete = (Boolean) params.get("isXAxisDelete");
String yAxisPosition = (String) params.get("yAxisPosition");
CTLineChart ctLineChart = ctPlotArea.addNewLineChart();
ctLineChart.addNewGrouping().setVal(STGrouping.STANDARD);
ctLineChart.addNewVaryColors().setVal(true);
for (int i = 0; i <= seriesDataCellRangeList.size() - 1; i++) {
int[] seriesDataCellRange = seriesDataCellRangeList.get(i);
CTLineSer ctLineSer = ctLineChart.addNewSer();
if (lineColor != null) {
ctLineSer.addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(lineColor.getRGB());
}
CTSerTx ctSerTx = ctLineSer.addNewTx();
CTStrRef ctStrRef = ctSerTx.addNewStrRef();
ctStrRef.setF(new CellRangeAddress(
seriesDataCellRange[0], seriesDataCellRange[1], seriesDataCellRange[2], seriesDataCellRange[3])
.formatAsString(sheet.getSheetName(), true));
ctLineSer.addNewIdx().setVal(i + 1);
CTAxDataSource cttAxDataSource = ctLineSer.addNewCat();
ctStrRef = cttAxDataSource.addNewStrRef();
ctStrRef.setF(new CellRangeAddress(
xAxisDataCellRange[0], xAxisDataCellRange[1], xAxisDataCellRange[2], xAxisDataCellRange[3])
.formatAsString(sheet.getSheetName(), true));
CTNumDataSource ctNumDataSource = ctLineSer.addNewVal();
CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
ctNumRef.setF(new CellRangeAddress(
seriesDataCellRange[4], seriesDataCellRange[5], seriesDataCellRange[6], seriesDataCellRange[7])
.formatAsString(sheet.getSheetName(), true));
CTBoolean addNewSmooth = ctLineSer.addNewSmooth();
addNewSmooth.setVal(false);
CTMarker addNewMarker = ctLineSer.addNewMarker();
CTMarkerStyle addNewSymbol = addNewMarker.addNewSymbol();
addNewSymbol.setVal(STMarkerStyle.NONE);
}
int xAxisId = seriesDataCellRangeList.size() + 1 + 10000;
int yAxisId = seriesDataCellRangeList.size() + 2 + 10000;
ctLineChart.addNewAxId().setVal(xAxisId);
ctLineChart.addNewAxId().setVal(yAxisId);
CTCatAx ctCatAx = ctPlotArea.addNewCatAx();
ctCatAx.addNewAxId().setVal(xAxisId);
CTScaling ctScaling = ctCatAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctCatAx.addNewDelete().setVal(isXAxisDelete);
ctCatAx.addNewAxPos().setVal(STAxPos.B);
ctCatAx.addNewMajorTickMark().setVal(STTickMark.OUT);
ctCatAx.addNewMinorTickMark().setVal(STTickMark.NONE);
ctCatAx.addNewAuto().setVal(true);
ctCatAx.addNewLblAlgn().setVal(STLblAlgn.CTR);
ctCatAx.addNewCrossAx().setVal(yAxisId);
ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
ctCatAx.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(
new XSSFColor(new Color(134, 134, 134)).getRGB());
CTValAx ctValAx = ctPlotArea.addNewValAx();
ctValAx.addNewAxId().setVal(yAxisId);
ctScaling = ctValAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctValAx.addNewDelete().setVal(false);
switch (yAxisPosition) {
case "l" :
ctValAx.addNewAxPos().setVal(STAxPos.L);
ctValAx.addNewCrosses().setVal(STCrosses.MIN);
break;
case "r" :
ctValAx.addNewAxPos().setVal(STAxPos.R);
ctValAx.addNewCrosses().setVal(STCrosses.MAX);
break;
case "t" :
ctValAx.addNewAxPos().setVal(STAxPos.T);
break;
case "b" :
ctValAx.addNewAxPos().setVal(STAxPos.B);
break;
default :
ctValAx.addNewAxPos().setVal(STAxPos.L);
ctValAx.addNewCrosses().setVal(STCrosses.MIN);
break;
}
ctValAx.addNewMajorTickMark().setVal(STTickMark.OUT);
ctValAx.addNewMinorTickMark().setVal(STTickMark.NONE);
ctValAx.addNewCrossAx().setVal(xAxisId);
ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
ctValAx.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(
new XSSFColor(new Color(134, 134, 134)).getRGB());
ctValAx.addNewMajorGridlines().addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(
new XSSFColor(new Color(134, 134, 134)).getRGB());
}
private static void createCTBarChart(XSSFSheet sheet, CTPlotArea ctPlotArea, Map<String, Object> params) {
int[] xAxisDataCellRange = (int[]) params.get("xAxisDataCellRange");
List<int[]> seriesDataCellRangeList = (List<int[]>) params.get("seriesDataCellRangeList");
XSSFColor barColor = (XSSFColor) params.get("barColor");
CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
ctBarChart.getVaryColors().setVal(true);
ctBarChart.addNewGrouping().setVal(STBarGrouping.CLUSTERED);
ctBarChart.addNewBarDir().setVal(STBarDir.COL);
for (int i = 0; i <= seriesDataCellRangeList.size() - 1; i++) {
int[] seriesDataCellRange = seriesDataCellRangeList.get(i);
CTBarSer ctBarSer = ctBarChart.addNewSer();
if (barColor != null) {
ctBarSer.addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(barColor.getRGB());
}
CTSerTx ctSerTx = ctBarSer.addNewTx();
CTStrRef ctStrRef = ctSerTx.addNewStrRef();
ctStrRef.setF(new CellRangeAddress(
seriesDataCellRange[0], seriesDataCellRange[1], seriesDataCellRange[2], seriesDataCellRange[3])
.formatAsString(sheet.getSheetName(), true));
ctBarSer.addNewIdx().setVal(i + 1);
CTAxDataSource ctAxDataSource = ctBarSer.addNewCat();
ctStrRef = ctAxDataSource.addNewStrRef();
ctStrRef.setF(new CellRangeAddress(
xAxisDataCellRange[0], xAxisDataCellRange[1], xAxisDataCellRange[2], xAxisDataCellRange[3])
.formatAsString(sheet.getSheetName(), true));
CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
ctNumRef.setF(new CellRangeAddress(
seriesDataCellRange[4], seriesDataCellRange[5], seriesDataCellRange[6], seriesDataCellRange[7])
.formatAsString(sheet.getSheetName(), true));
ctBarSer.addNewInvertIfNegative().setVal(false);
ctBoolean.setVal(false);
CTDLbls newDLbls = ctBarSer.addNewDLbls();
newDLbls.setShowLegendKey(ctBoolean);
newDLbls.setShowVal(ctBoolean);
newDLbls.setShowCatName(ctBoolean);
newDLbls.setShowSerName(ctBoolean);
newDLbls.setShowPercent(ctBoolean);
newDLbls.setShowBubbleSize(ctBoolean);
newDLbls.setShowLeaderLines(ctBoolean);
}
int xAxisId = seriesDataCellRangeList.size() + 1;
int yAxisId = seriesDataCellRangeList.size() + 2;
ctBarChart.addNewAxId().setVal(xAxisId);
ctBarChart.addNewAxId().setVal(yAxisId);
CTCatAx ctCatAx = ctPlotArea.addNewCatAx();
ctCatAx.addNewAxId().setVal(xAxisId);
CTScaling ctScaling = ctCatAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctCatAx.addNewDelete().setVal(false);
ctCatAx.addNewAxPos().setVal(STAxPos.B);
ctCatAx.addNewMajorTickMark().setVal(STTickMark.OUT);
ctCatAx.addNewMinorTickMark().setVal(STTickMark.NONE);
ctCatAx.addNewCrossAx().setVal(yAxisId);
ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
ctCatAx.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(
new XSSFColor(new Color(134, 134, 134)).getRGB());
CTValAx ctValAx = ctPlotArea.addNewValAx();
ctValAx.addNewAxId().setVal(yAxisId);
ctScaling = ctValAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctValAx.addNewDelete().setVal(false);
ctValAx.addNewAxPos().setVal(STAxPos.L);
ctValAx.addNewMajorTickMark().setVal(STTickMark.OUT);
ctValAx.addNewMinorTickMark().setVal(STTickMark.NONE);
ctValAx.addNewCrossAx().setVal(xAxisId);
ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
ctValAx.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(
new XSSFColor(new Color(134, 134, 134)).getRGB());
ctValAx.addNewMajorGridlines().addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(
new XSSFColor(new Color(134, 134, 134)).getRGB());
}
private static CTPlotArea createCTPlotArea(XSSFSheet sheet, Map<String, Object> params) {
int[] chartPosition = (int[]) params.get("chartPosition");
String chartTitle = (String) params.get("chartTitle");
String dispBlanksAs = (String) params.get("dispBlanksAs");
String legendPosition = (String) params.get("legendPosition");
XSSFDrawing drawing = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor =
drawing.createAnchor(
chartPosition[4] * Units.EMU_PER_POINT, chartPosition[5] * Units.EMU_PER_POINT,
chartPosition[6] * Units.EMU_PER_POINT, chartPosition[7] * Units.EMU_PER_POINT,
chartPosition[0], chartPosition[1], chartPosition[2], chartPosition[3]);
XSSFChart chart = drawing.createChart(anchor);
CTChart ctChart = chart.getCTChart();
CTPlotArea ctPlotArea = ctChart.getPlotArea();
chart.setTitleText(chartTitle);
ctChart.getTitle().addNewOverlay().setVal(false);
ctChart.addNewShowDLblsOverMax().setVal(true);
switch (dispBlanksAs) {
case "span" :
ctChart.addNewDispBlanksAs().setVal(STDispBlanksAs.SPAN);
break;
case "gap" :
ctChart.addNewDispBlanksAs().setVal(STDispBlanksAs.GAP);
break;
case "zero" :
ctChart.addNewDispBlanksAs().setVal(STDispBlanksAs.ZERO);
break;
default:
ctChart.addNewDispBlanksAs().setVal(STDispBlanksAs.ZERO);
break;
}
CTLegend ctLegend = ctChart.addNewLegend();
ctLegend.addNewOverlay().setVal(false);
switch (legendPosition) {
case "b" :
ctLegend.addNewLegendPos().setVal(STLegendPos.B);
break;
case "tr" :
ctLegend.addNewLegendPos().setVal(STLegendPos.TR);
break;
case "l" :
ctLegend.addNewLegendPos().setVal(STLegendPos.L);
break;
case "r" :
ctLegend.addNewLegendPos().setVal(STLegendPos.R);
break;
case "t" :
ctLegend.addNewLegendPos().setVal(STLegendPos.T);
break;
default:
ctLegend.addNewLegendPos().setVal(STLegendPos.T);
break;
}
return ctPlotArea;
}
public static int getColumnIndexByAddress(String columnAddress) {
int colNum = 0;
for (int i = 0; i < columnAddress.length(); i++) {
char ch = columnAddress.charAt(columnAddress.length() - 1 - i);
colNum += (ch - 'A' + 1) * Math.pow(26, i);
}
return colNum - 1;
}
}