static void CreateChart(IDrawing drawing, ISheet sheet, IClientAnchor anchor, string serie1, string serie2, bool enableMajorGridline = false)
{
XSSFChart chart = (XSSFChart)drawing.CreateChart(anchor);
chart.SetTitle("Test 1");
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.TopRight;
ILineChartData <double, double> data = chart.ChartDataFactory.CreateLineChartData <double, double>();
// Use a category axis for the bottom axis.
IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
leftAxis.Crosses = AxisCrosses.AutoZero;
IChartDataSource <double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
IChartDataSource <double> ys1 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
IChartDataSource <double> ys2 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));
var s1 = data.AddSeries(xs, ys1);
s1.SetTitle(serie1);
var s2 = data.AddSeries(xs, ys2);
s2.SetTitle(serie2);
chart.Plot(data, bottomAxis, leftAxis);
//add major gridline, available since NPOI 2.5.5
var plotArea = chart.GetCTChart().plotArea;
plotArea.catAx[0].AddNewMajorGridlines();
plotArea.valAx[0].AddNewMajorGridlines();
}
/**
* Create a new SpreadsheetML chart legend
*/
public XSSFChartLegend(XSSFChart chart)
{
CT_Chart ctChart = chart.GetCTChart();
this.legend = (ctChart.IsSetLegend()) ?
ctChart.legend :
ctChart.AddNewLegend();
}
public void FillChart(SS.UserModel.IChart chart, params IChartAxis[] axis)
{
if (!(chart is XSSFChart))
{
throw new ArgumentException("Chart must be instance of XSSFChart");
}
XSSFChart xssfChart = (XSSFChart)chart;
CT_PlotArea plotArea = xssfChart.GetCTChart().plotArea;
int allSeriesCount = plotArea.GetAllSeriesCount();
CT_BarChart barChart = plotArea.AddNewBarChart();
barChart.AddNewVaryColors().val = 0;
for (int i = 0; i < series.Count; ++i)
{
Series s = (Series)series[i];
s.SetId(allSeriesCount + i);
s.SetOrder(allSeriesCount + i);
s.AddToChart(barChart);
}
foreach (IChartAxis ax in axis)
{
barChart.AddNewAxId().val = (uint)ax.Id;
}
}
public void FillChart(IChart chart, IChartAxis[] axis)
{
if (!(chart is XSSFChart))
{
throw new ArgumentException("Chart must be instance of XSSFChart");
}
XSSFChart xssfChart = (XSSFChart)chart;
CT_PlotArea plotArea = xssfChart.GetCTChart().plotArea;
int allSeriesCount = plotArea.GetAllSeriesCount();
CT_ScatterChart scatterChart = plotArea.AddNewScatterChart();
AddStyle(scatterChart);
for (int i = 0; i < series.Count; ++i)
{
Series s = (Series)series[i];
s.SetId(allSeriesCount + i);
s.SetOrder(allSeriesCount + i);
s.AddToChart(scatterChart);
}
foreach (IChartAxis ax in axis)
{
scatterChart.AddNewAxId().val = (uint)ax.Id;
}
}
private static void CreateChart(ISheet sheet, IDrawing drawing, IClientAnchor anchor, string serieTitle, int startDataRow, int endDataRow, int columnIndex)
{
XSSFChart chart = (XSSFChart)drawing.CreateChart(anchor);
IBarChartData <string, double> barChartData = chart.ChartDataFactory.CreateBarChartData <string, double>();
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.Bottom;
IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
bottomAxis.MajorTickMark = AxisTickMark.None;
IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
leftAxis.Crosses = AxisCrosses.AutoZero;
leftAxis.SetCrossBetween(AxisCrossBetween.Between);
IChartDataSource <string> categoryAxis = DataSources.FromStringCellRange(sheet, new CellRangeAddress(startDataRow, endDataRow, 0, 0));
IChartDataSource <double> valueAxis = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(startDataRow, endDataRow, columnIndex, columnIndex));
var serie = barChartData.AddSeries(categoryAxis, valueAxis);
serie.SetTitle(serieTitle);
chart.Plot(barChartData, bottomAxis, leftAxis);
}
public void TestGetCharts()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithThreeCharts.xlsx");
XSSFSheet s1 = (XSSFSheet)wb.GetSheetAt(0);
XSSFSheet s2 = (XSSFSheet)wb.GetSheetAt(1);
XSSFSheet s3 = (XSSFSheet)wb.GetSheetAt(2);
Assert.AreEqual(0, (s1.CreateDrawingPatriarch() as XSSFDrawing).GetCharts().Count);
Assert.AreEqual(2, (s2.CreateDrawingPatriarch() as XSSFDrawing).GetCharts().Count);
Assert.AreEqual(1, (s3.CreateDrawingPatriarch() as XSSFDrawing).GetCharts().Count);
// Check the titles
XSSFChart chart = (s2.CreateDrawingPatriarch() as XSSFDrawing).GetCharts()[(0)];
Assert.AreEqual(null, chart.Title);
chart = (s2.CreateDrawingPatriarch() as XSSFDrawing).GetCharts()[(1)];
Assert.AreEqual("Pie Chart Title Thingy", chart.Title.String);
chart = (s3.CreateDrawingPatriarch() as XSSFDrawing).GetCharts()[(0)];
Assert.AreEqual("Sheet 3 Chart with Title", chart.Title.String);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
/**
* Create a new SpreadsheetML chart legend
*/
public XSSFChartLegend(XSSFChart chart)
{
CT_Chart ctChart = chart.GetCTChart();
this.legend = (ctChart.IsSetLegend()) ?
ctChart.legend :
ctChart.AddNewLegend();
}
/**
* Create a new SpreadsheetML manual layout for chart.
* @param chart a chart to create layout for.
*/
public XSSFManualLayout(XSSFChart chart)
{
CT_PlotArea ctPlotArea = chart.GetCTChart().plotArea;
CT_Layout ctLayout = ctPlotArea.IsSetLayout() ?
ctPlotArea.layout : ctPlotArea.AddNewLayout();
InitLayout(ctLayout);
}
/**
* Create a new SpreadsheetML manual layout for chart.
* @param chart a chart to create layout for.
*/
public XSSFManualLayout(XSSFChart chart)
{
CT_PlotArea ctPlotArea = chart.GetCTChart().plotArea;
CT_Layout ctLayout = ctPlotArea.IsSetLayout() ?
ctPlotArea.layout : ctPlotArea.AddNewLayout();
InitLayout(ctLayout);
}
/// <summary>
/// 创建一个图表实例
/// </summary>
/// <param name="excelChart"></param>
/// <param name="sheet"></param>
private void CreateChart(NPOIExcelChart excelChart, ISheet sheet, int startRow, int endRow)
{
if (_excelType != NPOIExcelType.XLS)
{
throw new NotImplementedException("只支持.xls文件作图");
}
IDrawing drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, 0, startRow, excelChart.Axis.Count, endRow);
XSSFChart chart = drawing.CreateChart(anchor) as XSSFChart;
chart.Title.String = excelChart.Title;
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.TopRight;
//坐标轴
var axis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
axis.IsVisible = true;
//值轴
var data = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
data.IsVisible = true;
data.Crosses = (AxisCrosses.AutoZero);
IChartDataSource <string> xs = DataSources.FromArray <string>(excelChart.Axis.ToArray());
switch (excelChart.ExcelChartType)
{
case NPOIExcelChartType.Bar:
var chartBarData = chart.ChartDataFactory.CreateBarChartData <string, double>();
foreach (var item in excelChart.Data)
{
var curData = DataSources.FromArray <double>(item.Value.ToArray());
var curSeries = chartBarData.AddSeries(xs, curData);
curSeries.SetTitle(item.Key);
}
chart.Plot(chartBarData, axis, data);
return;
case NPOIExcelChartType.Line:
var chartLineData = chart.ChartDataFactory.CreateLineChartData <string, double>();
foreach (var item in excelChart.Data)
{
var curData = DataSources.FromArray <double>(item.Value.ToArray());
var curSeries = chartLineData.AddSeries(xs, curData);
curSeries.SetTitle(item.Key);
}
chart.Plot(chartLineData, axis, data);
break;
default:
break;
}
}
public void TestGetCharts()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("chart_sheet.xlsx");
XSSFSheet ns = (XSSFSheet)wb.GetSheetAt(0);
XSSFChartSheet cs = (XSSFChartSheet)wb.GetSheetAt(2);
Assert.AreEqual(0, (ns.CreateDrawingPatriarch() as XSSFDrawing).GetCharts().Count);
Assert.AreEqual(1, (cs.CreateDrawingPatriarch() as XSSFDrawing).GetCharts().Count);
XSSFChart chart = (cs.CreateDrawingPatriarch() as XSSFDrawing).GetCharts()[0];
Assert.IsNull(chart.Title);
}
public void TestAccessMethods()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30) as XSSFClientAnchor;
XSSFChart chart = Drawing.CreateChart(anchor) as XSSFChart;
XSSFCategoryAxis axis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom) as XSSFCategoryAxis;
axis.Crosses = (AxisCrosses.AutoZero);
Assert.AreEqual(axis.Crosses, AxisCrosses.AutoZero);
Assert.AreEqual(chart.GetAxis().Count, 1);
}
public void TestNewChart()
{
IWorkbook wb = CreateWorkbookWithChart();
XSSFChart chart = GetChartFromWorkbook(wb, "linechart");
Assert.IsNotNull(chart);
Assert.IsNull(chart.Title);
String myTitle = "My chart title";
chart.SetTitle(myTitle);
XSSFRichTextString queryTitle = chart.Title;
Assert.IsNotNull(queryTitle);
Assert.AreEqual(myTitle, queryTitle.ToString());
}
public void TestExistingChartNoTitle()
{
IWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("chartTitle_noTitle.xlsx");
XSSFChart chart = GetChartFromWorkbook(wb, "Sheet1");
Assert.IsNotNull(chart);
Assert.IsNull(chart.Title);
String myTitle = "My chart title";
chart.SetTitle(myTitle);
XSSFRichTextString queryTitle = chart.Title;
Assert.IsNotNull(queryTitle);
Assert.AreEqual(myTitle, queryTitle.ToString());
}
public void TestGetChartAxisBug57362()
{
//Load existing excel with some chart on it having primary and secondary axis.
IWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("57362.xlsx");
ISheet sh = workbook.GetSheetAt(0);
XSSFSheet xsh = (XSSFSheet)sh;
XSSFDrawing Drawing = xsh.CreateDrawingPatriarch() as XSSFDrawing;
XSSFChart chart = Drawing.GetCharts()[(0)];
List <IChartAxis> axisList = chart.GetAxis();
Assert.AreEqual(4, axisList.Count);
Assert.IsNotNull(axisList[(0)]);
Assert.IsNotNull(axisList[(1)]);
Assert.IsNotNull(axisList[(2)]);
Assert.IsNotNull(axisList[(3)]);
}
public void TestAddChartsToNewWorkbook()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet s1 = (XSSFSheet)wb.CreateSheet();
XSSFDrawing d1 = (XSSFDrawing)s1.CreateDrawingPatriarch();
XSSFClientAnchor a1 = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
XSSFChart c1 = (XSSFChart)d1.CreateChart(a1);
Assert.AreEqual(1, d1.GetCharts().Count);
Assert.IsNotNull(c1.GetGraphicFrame());
Assert.IsNotNull(c1.GetOrCreateLegend());
XSSFClientAnchor a2 = new XSSFClientAnchor(0, 0, 0, 0, 1, 11, 10, 60);
XSSFChart c2 = (XSSFChart)d1.CreateChart(a2);
Assert.IsNotNull(c2);
Assert.AreEqual(2, d1.GetCharts().Count);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public void FillChart(SS.UserModel.IChart chart, params IChartAxis[] axis)
{
if (!(chart is XSSFChart))
{
throw new ArgumentException("Chart must be instance of XSSFChart");
}
XSSFChart xssfChart = (XSSFChart)chart;
CT_PlotArea plotArea = xssfChart.GetCTChart().plotArea;
CT_LineChart lineChart = plotArea.AddNewLineChart();
lineChart.AddNewVaryColors().val = 0;
foreach (Serie s in series)
{
s.AddToChart(lineChart);
}
foreach (IChartAxis ax in axis)
{
lineChart.AddNewAxId().val = (uint)ax.GetId();
}
}
public void FillChart(IChart chart, IChartAxis[] axis)
{
if (!(chart is XSSFChart))
{
throw new ArgumentException("Chart must be instance of XSSFChart");
}
XSSFChart xssfChart = (XSSFChart)chart;
CT_PlotArea plotArea = xssfChart.GetCTChart().plotArea;
CT_ScatterChart scatterChart = plotArea.AddNewScatterChart();
AddStyle(scatterChart);
foreach (Series s in series)
{
s.AddToChart(scatterChart);
}
foreach (IChartAxis ax in axis)
{
scatterChart.AddNewAxId().val = (uint)ax.Id;
}
}
public XSSFCategoryAxis(XSSFChart chart, CT_CatAx ctCatAx)
: base(chart)
{
this.ctCatAx = ctCatAx;
}
public XSSFValueAxis(XSSFChart chart, long id, AxisPosition pos)
: base(chart)
{
this.CreateAxis(id, pos);
}
public XSSFValueAxis(XSSFChart chart, CT_ValAx ctValAx)
: base(chart)
{
this.ctValAx = ctValAx;
}
public XSSFValueAxis(XSSFChart chart, long id, AxisPosition pos)
: base(chart)
{
CreateAxis(id, pos);
}
protected XSSFChartAxis(XSSFChart chart)
{
this.chart = chart;
}
public XSSFCategoryAxis(XSSFChart chart, CT_CatAx ctCatAx)
: base(chart)
{
this.ctCatAx = ctCatAx;
}
public XSSFValueAxis(XSSFChart chart, CT_ValAx ctValAx)
: base(chart)
{
this.ctValAx = ctValAx;
}
public XSSFDateAxis(XSSFChart chart, long id, AxisPosition pos)
: base(chart)
{
createAxis(id, pos);
}
public XSSFCategoryAxis(XSSFChart chart, long id, AxisPosition pos)
: base(chart)
{
createAxis(id, pos);
}
protected XSSFChartAxis(XSSFChart chart)
{
this.chart = chart;
}
public XSSFDateAxis(XSSFChart chart, CT_DateAx ctDateAx)
: base(chart)
{
this.ctDateAx = ctDateAx;
}
public XSSFManualLayout(XSSFChart chart)
{
CT_PlotArea plotArea = chart.GetCTChart().plotArea;
this.InitLayout(plotArea.IsSetLayout() ? plotArea.layout : plotArea.AddNewLayout());
}