Java 使用 Apache POI 在 Excel 中创建条形图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38913412/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-11 20:44:40 来源:igfitidea点击:
Create Bar Chart in Excel with Apache POI
提问by LuTHieR
I'm trying to create a Bar Chart in an XLSX Spreadsheet using Apache POI, but Excel keeps saying that there's a problem with the content and deleting the chart when I try to open the file. Here's the full code of what I'm trying to do:
我正在尝试使用 Apache POI 在 XLSX 电子表格中创建条形图，但 Excel 一直说内容有问题，并在我尝试打开文件时删除了图表。这是我正在尝试做的完整代码：
public static void createBarChart() {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet2");
Row row;
Cell cell;
row = sheet.createRow(0);
row.createCell(0);
row.createCell(1).setCellValue("HEADER 1");
row.createCell(2).setCellValue("HEADER 2");
row.createCell(3).setCellValue("HEADER 3");
for (int r = 1; r < 5; r++) {
row = sheet.createRow(r);
cell = row.createCell(0);
cell.setCellValue("Serie " + r);
cell = row.createCell(1);
cell.setCellValue(123);
cell = row.createCell(2);
cell.setCellValue(456);
cell = row.createCell(3);
cell.setCellValue(789);
}
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 5, 20);
Chart chart = drawing.createChart(anchor);
CTChart ctChart = ((XSSFChart) chart).getCTChart();
ctChart.addNewTitle();
CTPlotArea ctPlotArea = ctChart.getPlotArea();
CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
ctBarChart.addNewBarDir().setVal(STBarDir.COL);
ctBarChart.addNewGrouping().setVal(STBarGrouping.CLUSTERED);
ctBarChart.addNewVaryColors().setVal(false);
// First series
CTBarSer ctBarSer = ctBarChart.addNewSer();
ctBarSer.addNewIdx().setVal(0);
ctBarSer.addNewOrder().setVal(0);
// [01] Title
CTSerTx ctSerTx = ctBarSer.addNewTx();
CTStrRef ctSerTxStrRef = ctSerTx.addNewStrRef();
ctSerTxStrRef.setF("Sheet2!$A");
CTStrData strCache = ctSerTxStrRef.addNewStrCache();
strCache.addNewPtCount().setVal(1);
CTStrVal newPt = strCache.addNewPt();
newPt.setV("Serie 1");
newPt.setIdx(0);
// [01] SpPr
CTShapeProperties spPr = ctBarSer.addNewSpPr();
CTSolidColorFillProperties solidFill = spPr.addNewSolidFill();
solidFill.addNewSchemeClr().setVal(STSchemeColorValImpl.ACCENT_1);
spPr.addNewLn().addNewNoFill();
spPr.addNewEffectLst();
// [01] Invert if negative
ctBarSer.addNewInvertIfNegative().setVal(false);
// [01] Series titles
CTAxDataSource newCat = ctBarSer.addNewCat();
CTStrRef addNewStrRef = newCat.addNewStrRef();
addNewStrRef.setF("Sheet2!$B:$D");
CTStrData addNewStrCache = addNewStrRef.addNewStrCache();
addNewStrCache.addNewPtCount().setVal(3);
CTStrVal pt1 = addNewStrCache.addNewPt();
pt1.setIdx(0);
pt1.setV(sheet.getRow(0).getCell(1).getStringCellValue());
CTStrVal pt2 = addNewStrCache.addNewPt();
pt2.setIdx(1);
pt2.setV(sheet.getRow(0).getCell(2).getStringCellValue());
CTStrVal pt3 = addNewStrCache.addNewPt();
pt3.setIdx(2);
pt3.setV(sheet.getRow(0).getCell(3).getStringCellValue());
// [01] Series values
CTNumDataSource newVal = ctBarSer.addNewVal();
CTNumRef numRef = newVal.addNewNumRef();
numRef.setF("Sheet2!$B:$D");
CTNumData numCache = numRef.addNewNumCache();
numCache.addNewPtCount().setVal(3);
CTNumVal numpt1 = numCache.addNewPt();
numpt1.setIdx(0);
numpt1.setV(String.valueOf(sheet.getRow(1).getCell(1).getNumericCellValue()));
CTNumVal numpt2 = numCache.addNewPt();
numpt2.setIdx(1);
numpt2.setV(String.valueOf(sheet.getRow(1).getCell(2).getNumericCellValue()));
CTNumVal numpt3 = numCache.addNewPt();
numpt3.setIdx(2);
numpt3.setV(String.valueOf(sheet.getRow(1).getCell(3).getNumericCellValue()));
// dLbls
CTDLbls dLbls = ctBarChart.addNewDLbls();
dLbls.addNewShowBubbleSize().setVal(false);
dLbls.addNewShowLegendKey().setVal(false);
dLbls.addNewShowCatName().setVal(false);
dLbls.addNewShowSerName().setVal(false);
dLbls.addNewShowPercent().setVal(false);
dLbls.addNewShowVal().setVal(false);
FileOutputStream fileOut = new FileOutputStream("barchart.xlsx");
wb.write(fileOut);
wb.close();
fileOut.close();
}
Can anyone help me find (and, well, solve) the issue? Thanks in advance!
任何人都可以帮我找到（并解决）问题吗？提前致谢！
采纳答案by Axel Richter
Phew, difficult birth ;-). For users without knowledge of the background Apache POI supports only ScatterCharts and LineCharts why?. There is described how to proceed in principle.
呼，难产;-)。对于不了解后台Apache POI 的用户，为什么只支持 ScatterCharts 和 LineCharts？. 描述了原则上如何进行。
As I said. Take the simplestpossible bar chart for testing first. That is one series with two values. Then you would have seen that you have completely forgotten the axes in your code. A pie chart has no axes. Thats why there they are not necessary. But a bar chart will not work without axes.
就像我说的。首先采用最简单的条形图进行测试。这是一个具有两个值的系列。然后你会看到你完全忘记了代码中的轴。饼图没有轴。这就是为什么它们不是必需的。但是没有轴，条形图将无法工作。
Instead you can unscrupulously forget all things what is named "cache".
相反，您可以肆无忌惮地忘记所有名为“缓存”的东西。
Whether other things are needed is determined by try and error. Also one could read the Office OpenXML recommendation. Probably the missing of all elements having minOccurs<> "0"` leads to deleting the Drawing while opening the Workbook.
是否需要其他东西由尝试和错误决定。也可以阅读 Office OpenXML 推荐。可能缺少具有minOccurs<> "0"`的所有元素会导致在打开工作簿时删除绘图。
Simple Example for a bar chart:
条形图的简单示例：
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.usermodel.charts.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTScaling;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend;
import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir;
import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;
public class BarChart {
public static void main(String[] args) throws Exception {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet1");
Row row;
Cell cell;
row = sheet.createRow(0);
row.createCell(0);
row.createCell(1).setCellValue("HEADER 1");
row.createCell(2).setCellValue("HEADER 2");
row.createCell(3).setCellValue("HEADER 3");
for (int r = 1; r < 5; r++) {
row = sheet.createRow(r);
cell = row.createCell(0);
cell.setCellValue("Serie " + r);
cell = row.createCell(1);
cell.setCellValue(new java.util.Random().nextDouble());
cell = row.createCell(2);
cell.setCellValue(new java.util.Random().nextDouble());
cell = row.createCell(3);
cell.setCellValue(new java.util.Random().nextDouble());
}
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 8, 20);
Chart chart = drawing.createChart(anchor);
CTChart ctChart = ((XSSFChart)chart).getCTChart();
CTPlotArea ctPlotArea = ctChart.getPlotArea();
CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
ctBoolean.setVal(true);
ctBarChart.addNewBarDir().setVal(STBarDir.COL);
for (int r = 2; r < 6; r++) {
CTBarSer ctBarSer = ctBarChart.addNewSer();
CTSerTx ctSerTx = ctBarSer.addNewTx();
CTStrRef ctStrRef = ctSerTx.addNewStrRef();
ctStrRef.setF("Sheet1!$A$" + r);
ctBarSer.addNewIdx().setVal(r-2);
CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();
ctStrRef = cttAxDataSource.addNewStrRef();
ctStrRef.setF("Sheet1!$B:$D");
CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
ctNumRef.setF("Sheet1!$B$" + r + ":$D$" + r);
//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);
ctBarChart.addNewAxId().setVal(123457);
//cat axis
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
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.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
//legend
CTLegend ctLegend = ctChart.addNewLegend();
ctLegend.addNewLegendPos().setVal(STLegendPos.B);
ctLegend.addNewOverlay().setVal(false);
System.out.println(ctChart);
FileOutputStream fileOut = new FileOutputStream("BarChart.xlsx");
wb.write(fileOut);
fileOut.close();
}
}
This example needs the full jar of all of the schemas ooxml-schemas-1.3.jaras mentioned in the FAQ-N10025.
此示例需要FAQ-N10025 中ooxml-schemas-1.3.jar提到的所有模式的完整 jar 。
Above code has worked until apache poi 3.17.
上面的代码一直工作到apache poi 3.17.
Following code works using apache poi 4.1.0. It needs the full jar of all of the schemas ooxml-schemas-1.4.jar.
以下代码使用apache poi 4.1.0. 它需要所有模式的完整 jar ooxml-schemas-1.4.jar。
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTScaling;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend;
import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir;
import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;
public class BarChart {
public static void main(String[] args) throws Exception {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet1");
Row row;
Cell cell;
row = sheet.createRow(0);
row.createCell(0);
row.createCell(1).setCellValue("HEADER 1");
row.createCell(2).setCellValue("HEADER 2");
row.createCell(3).setCellValue("HEADER 3");
for (int r = 1; r < 5; r++) {
row = sheet.createRow(r);
cell = row.createCell(0);
cell.setCellValue("Serie " + r);
cell = row.createCell(1);
cell.setCellValue(new java.util.Random().nextDouble());
cell = row.createCell(2);
cell.setCellValue(new java.util.Random().nextDouble());
cell = row.createCell(3);
cell.setCellValue(new java.util.Random().nextDouble());
}
XSSFDrawing drawing = (XSSFDrawing)sheet.createDrawingPatriarch();
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 8, 20);
XSSFChart chart = drawing.createChart(anchor);
CTChart ctChart = ((XSSFChart)chart).getCTChart();
CTPlotArea ctPlotArea = ctChart.getPlotArea();
CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
ctBoolean.setVal(true);
ctBarChart.addNewBarDir().setVal(STBarDir.COL);
for (int r = 2; r < 6; r++) {
CTBarSer ctBarSer = ctBarChart.addNewSer();
CTSerTx ctSerTx = ctBarSer.addNewTx();
CTStrRef ctStrRef = ctSerTx.addNewStrRef();
ctStrRef.setF("Sheet1!$A$" + r);
ctBarSer.addNewIdx().setVal(r-2);
CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();
ctStrRef = cttAxDataSource.addNewStrRef();
ctStrRef.setF("Sheet1!$B:$D");
CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
ctNumRef.setF("Sheet1!$B$" + r + ":$D$" + r);
//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);
ctBarChart.addNewAxId().setVal(123457);
//cat axis
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
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.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
//legend
CTLegend ctLegend = ctChart.addNewLegend();
ctLegend.addNewLegendPos().setVal(STLegendPos.B);
ctLegend.addNewOverlay().setVal(false);
System.out.println(ctChart);
FileOutputStream fileOut = new FileOutputStream("BarChart.xlsx");
wb.write(fileOut);
fileOut.close();
}
}
回答by sandeep tiwari
with Apache-Poi 4.0 we can achive it with minimal code. Below example is available with apache poi example. Now createChart Method is available with all format(Excel/DOCX/PPTX).
使用 Apache-Poi 4.0，我们可以用最少的代码实现它。下面的示例可用于 apache poi 示例。现在 createChart 方法适用于所有格式（Excel/DOCX/PPTX）。
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.XDDFShapeProperties;
import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
import org.apache.poi.xddf.usermodel.chart.AxisCrosses;
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.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.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;
public class BarChart {
public static void main(String[] args) throws IOException {
try (XSSFWorkbook wb = new XSSFWorkbook()) {
XSSFSheet sheet = wb.createSheet("barchart");
final int NUM_OF_ROWS = 3;
final int NUM_OF_COLUMNS = 10;
// Create a row and put some cells in it. Rows are 0 based.
Row row;
Cell cell;
for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {
row = sheet.createRow((short) rowIndex);
for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
cell = row.createCell((short) colIndex);
cell.setCellValue(colIndex * (rowIndex + 1.0));
}
}
XSSFDrawing drawing = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);
XSSFChart chart = drawing.createChart(anchor);
chart.setTitleText("BarChart");
chart.setTitleOverlay(false);
XDDFChartLegend legend = chart.getOrAddLegend();
legend.setPosition(LegendPosition.TOP_RIGHT);
// Use a category axis for the bottom axis.
XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
bottomAxis.setTitle("x");
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
leftAxis.setTitle("f(x)");
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
XDDFDataSource<Double> xs = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
XDDFNumericalDataSource<Double> ys1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
XDDFNumericalDataSource<Double> ys2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));
XDDFChartData data = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
XDDFChartData.Series series1 = data.addSeries(xs, ys1);
series1.setTitle("2x", null);
XDDFChartData.Series series2 = data.addSeries(xs, ys2);
series2.setTitle("3x", null);
chart.plot(data);
// in order to transform a bar chart into a column chart, you just need to change the bar direction
XDDFBarChartData bar = (XDDFBarChartData) data;
bar.setBarDirection(BarDirection.COL);
// looking for "Stacked Bar Chart"? uncomment the following line
// bar.setBarGrouping(BarGrouping.STACKED);
solidFillSeries(data, 0, PresetColor.CHARTREUSE);
solidFillSeries(data, 1, PresetColor.TURQUOISE);
// Write the output to a file
try (FileOutputStream fileOut = new FileOutputStream("ooxml-bar-chart.xlsx")) {
wb.write(fileOut);
}
}
}
private static void solidFillSeries(XDDFChartData data, int index, PresetColor color) {
XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
XDDFChartData.Series series = data.getSeries().get(index);
XDDFShapeProperties properties = series.getShapeProperties();
if (properties == null) {
properties = new XDDFShapeProperties();
}
properties.setFillProperties(fill);
series.setShapeProperties(properties);
}
}
回答by Pratixa
This is the code for creating the bar chart (actually creating the column chart) in Excel using XSSF for Excel 2007 and above using the NPOI 2.4.1 nuget package.
这是在 Excel 中使用 XSSF for Excel 2007 及更高版本使用 NPOI 2.4.1 nuget 包创建条形图（实际上是创建柱形图）的代码。
private XSSFWorkbook createBarchart()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("WorkForceAnalytics");
XSSFCellStyle styleHeader = (XSSFCellStyle)workbook.CreateCellStyle();
styleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
styleHeader.SetFont(getNewXSSFFont(workbook, styleHeader));
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
XSSFRow row1 = (XSSFRow)sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue("");
List<string> lstdatase = new List<string>(4);
lstdatase.Add("A1");
lstdatase.Add("B1");
lstdatase.Add("C1");
lstdatase.Add("D1");
lstdatase.Add("E1");
for (int i = 1; i < 5; i++)
{
row1.CreateCell(i).SetCellValue(lstdatase[i - 1].ToString());
row1.GetCell(i).CellStyle = styleHeader;
}
int rowvalue = 1;
List<string> lstdata = new List<string>(8);
lstdata.Add("A");
lstdata.Add("B");
lstdata.Add("C");
lstdata.Add("D");
lstdata.Add("E");
lstdata.Add("F");
lstdata.Add("G");
lstdata.Add("H");
int d = 10;
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = (XSSFClientAnchor)drawing.CreateAnchor(0, 0, 0, 0, 6, 1, 15, 18);
IChart chart = drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = (LegendPosition.TopRight);
IBarChartData<string, double> data = chart.ChartDataFactory.CreateBarChartData<string, double>();
IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
leftAxis.Crosses = AxisCrosses.AutoZero;
leftAxis.SetCrossBetween(AxisCrossBetween.Between);
IChartDataSource<string> xs = DataSources.FromStringCellRange(sheet, new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 5-1));
for (int ii = 0; ii < 8; ii++)
{
XSSFRow rownew = (XSSFRow)sheet.CreateRow(rowvalue);
rownew.CreateCell(0).SetCellValue(lstdata[ii].ToString());
for (int i = 1; i < 5; i++)
{
rownew.CreateCell(i).SetCellValue(d);
d++;
rownew.GetCell(i).CellStyle = style;
}
rowvalue++;
IChartDataSource<double> ys = DataSources.FromNumericCellRange(sheet, new NPOI.SS.Util.CellRangeAddress(ii + 1, ii + 1, 1, 5 - 1));
data.AddSeries(xs, ys).SetTitle(lstdata[ii].ToString());
}
chart.Plot(data, bottomAxis, leftAxis);
sheet.ForceFormulaRecalculation = true;
string fileName = "WorkforceAnalyticsReport.xlsx";
using (var exportData = new MemoryStream())
{
Response.Clear();
workbook.Write(exportData);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
Response.BinaryWrite(exportData.ToArray());
Response.End();
}
return workbook;
}
