creating chart in an excel spreadsheet using NPOI

Question

I know that my question will be very similar to other ones already asked on SO but as I can't find a satisfying answer, I try my luck !

Does so know whether it's possible or not to generate charts in an Excel spreadsheet using the NPOI library.

I have read that blog but it gives an example where there is already an existing template. Isn't it possible "from scratch" ?

Eudemonism

Now it is possible using NPOI: github.com/tonyqus/npoi/blob/master/examples/xssf/LineChart/… – Reader

Answer 1

After more investigation I got my answer here: http://npoi.codeplex.com/releases/view/19351

Unsupported Features: Excel Chart

so as explained by Leniel in his blog, we need to use a primary spreadsheet as a template.

Thanks a lot Leniel ! :)

Eudemonism

Now it is possible using NPOI: github.com/tonyqus/npoi/blob/master/examples/xssf/LineChart/… – Reader

Answer 2

I have found a way around this as I also had the same problem. There are not many good examples using the C# port of NPOI to generate charts. Most people I think use a template and ad then alter the result.

Depending on the chart type you need, different methodologies must be used. My examples were stolen directly from my own code that worked with 2.5.2 so defining vars like sheet may be missing so you will need to fill those in yourself.

For Bar and Line charts you can use something simple the following:

    IDrawing drawing = sheet.CreateDrawingPatriarch ( );
/* Define anchor points in the worksheet to position the chart */
IClientAnchor anchor = drawing.CreateAnchor ( 0, 0, 0, 0, 0, 3, 10, 23 );
row = row + 23;
/* Create the chart object based on the anchor point */
IChart barChart = drawing.CreateChart ( anchor );
/* Define legends for the line chart and set the position of the legend */
IChartLegend legend = barChart.GetOrCreateLegend ( );
legend.Position = LegendPosition.Bottom;
/* Create data for the chart */
IBarChartData<double, double> data = barChart.ChartDataFactory.CreateBarChartData<double, double> ( );
/* Define chart AXIS */
IChartAxis bottomAxis = barChart.ChartAxisFactory.CreateCategoryAxis ( AxisPosition.Bottom );
IValueAxis leftAxis = barChart.ChartAxisFactory.CreateValueAxis ( AxisPosition.Left );
leftAxis.Crosses = AxisCrosses.AutoZero;

/* Define Data sources for the chart */
/* Set the right cell range that contain values for the chart */
/* Pass the worksheet and cell range address as inputs */
/* Cell Range Address is defined as First row, last row, first column, last column */
int iDataPoints = dataSet.Tables[ "_CHART" ].Rows.Count + 1;

//Defines the rows/columns used for X-Axis data
IChartDataSource<double> xs = DataSources.FromNumericCellRange ( sheetChart, new CellRangeAddress ( 2, iDataPoints, 0, 0 ) );
//Defines the rows/columns used for the line data
IChartDataSource<double> ys1 = DataSources.FromNumericCellRange ( sheetChart, new CellRangeAddress ( 2, iDataPoints, 1, 1 ) );
/* Add chart data sources as data to the chart */
data.AddSeries ( xs, ys1 );

/* Plot the chart with the inputs from data and chart axis */
barChart.Plot ( data, new IChartAxis[] { bottomAxis, leftAxis } );

For Pie Charts, this got a bit more tricky as I found that the needed properties/methods were not publicly exposed. However, I was able to make this work using something like this:

    /* At the end of this step, we have a worksheet with test data, that we want to write into a chart */
/* Create a drawing canvas on the worksheet */
IDrawing drawing = sheet.CreateDrawingPatriarch ( );
/* Define anchor points in the worksheet to position the chart */
IClientAnchor anchor = drawing.CreateAnchor ( 0, 0, 0, 0, 0, 3, 10, 23 );
row = row + 23;
/* Create the chart object based on the anchor point */
IChart pieChart = drawing.CreateChart ( anchor );

XSSFChart xssfChart = (XSSFChart) pieChart;

MethodInfo dynMethod = xssfChart.GetType ( ).GetMethod ( "GetCTChart", BindingFlags.NonPublic | BindingFlags.Instance );
object oCTChart = dynMethod.Invoke ( xssfChart, null );
CT_Chart ctChart = (CT_Chart) oCTChart;


//CT_PlotArea plotArea = xssfChart.GetCTChart ( ).plotArea == null ? xssfChart.GetCTChart ( ).AddNewPlotArea ( ) : xssfChart.GetCTChart ( ).plotArea;
CT_PlotArea plotArea = ctChart.plotArea == null ? ctChart.AddNewPlotArea ( ) : ctChart.plotArea;
//plotArea.
var ctpieChart = plotArea.AddNewPie3DChart ( );

//CT_Pie3DChart ctpieChart = plotArea.AddNewPie3DChart ( );


CT_Boolean bVaryColor = new CT_Boolean ( );
bVaryColor.val = 1;
ctpieChart.varyColors = bVaryColor; // .AddNewVaryColors ( ).val = 1;// addNewVaryColors ( ).setVal ( true );
                                    //xssfChart. ( this.title );


IChartDataSource<double> xs = DataSources.FromNumericCellRange ( sheetChart, new CellRangeAddress ( 2, iDataPoints, 0, 0 ) );
//Defines the rows/columns used for the line data
IChartDataSource<double> ys1 = DataSources.FromNumericCellRange ( sheetChart, new CellRangeAddress ( 2, iDataPoints, 1, 1 ) );

String axisDataRange = new CellRangeAddress ( 2, iDataPoints, 0, 0 ).FormatAsString ( sheetChart.SheetName, true );
String numDataRange = new CellRangeAddress ( 2, iDataPoints, 1, 1 ).FormatAsString ( sheetChart.SheetName, true );


CT_UnsignedInt uIval = new CT_UnsignedInt ( );
uIval.val = 0;

//Pie Chart Series
ctpieChart.ser = new List<CT_PieSer> ( );
CT_PieSer ser = new CT_PieSer ( ); //.AddNewSer ( );

ser.idx = uIval;
ser.order = uIval;

//Create category section
ser.cat = new CT_AxDataSource ( );
ser.cat.strRef = new CT_StrRef ( );
ser.cat.strRef.strCache = new CT_StrData ( );
ser.cat.strRef.f = axisDataRange;
CT_UnsignedInt uIRange = new CT_UnsignedInt ( );
uIRange.val = (uint) dataSet.Tables[ "_CHART" ].Rows.Count;
ser.cat.strRef.strCache.ptCount = uIRange;

//Create value section
ser.val = new CT_NumDataSource ( );
ser.val.numRef = new CT_NumRef ( );
ser.val.numRef.f = numDataRange;
ser.val.numRef.numCache = new CT_NumData ( );
ser.val.numRef.numCache.formatCode = "General";
ser.val.numRef.numCache.ptCount = uIRange;

//Create Pts array
ser.dPt = new List<CT_DPt> ( );

//Create Category Pts
ser.cat.strRef.strCache.pt = new List<CT_StrVal> ( );

//Create Value Pts
ser.val.numRef.numCache.pt = new List<CT_NumVal> ( );

//Create Chart Styles/Settings
ser.dLbls = new CT_DLbls ( );
ser.dLbls.spPr = new CT_ShapeProperties ( );
ser.dLbls.spPr.noFill = new NPOI.OpenXmlFormats.Dml.CT_NoFillProperties ( );
ser.dLbls.spPr.ln = new NPOI.OpenXmlFormats.Dml.CT_LineProperties ( );
ser.dLbls.spPr.ln.noFill = new NPOI.OpenXmlFormats.Dml.CT_NoFillProperties ( );
ser.dLbls.showSerName = new CT_Boolean ( ) { val = 0 };
ser.dLbls.showPercent = new CT_Boolean ( ) { val = 0 };

//Add the series
ctpieChart.ser.Add ( ser );

//Loop through points and add to arrays
for ( int iPt = 0; iPt < dataSet.Tables[ "_CHART" ].Rows.Count; iPt++ )
{
    CT_UnsignedInt uIPt = new CT_UnsignedInt ( );
    uIPt.val = (uint) iPt;

    //Create Pt
    CT_DPt oPt = new CT_DPt ( );
    oPt.idx = uIPt;
    ser.dPt.Add ( oPt );

    //Create Label Pt
    CT_StrVal cPt = new CT_StrVal ( );
    cPt.idx = (uint) iPt;
    cPt.v = dataSet.Tables[ "_CHART" ].Rows[ iPt ][ "Label" ].ToString ( );
    ser.cat.strRef.strCache.pt.Add ( cPt );

    //Create Value Pt
    CT_NumVal vPt = new CT_NumVal ( );
    vPt.idx = (uint) iPt;
    vPt.v = dataSet.Tables[ "_CHART" ].Rows[ iPt ][ "Value" ].ToString ( );
    ser.val.numRef.numCache.pt.Add ( vPt );
}

Pressman

Answer 3

Another solution to create chart without automation is to use third party components,for example this one SmartXLS for .Net,it can create chart/pivottable from scratch.

Detrude

Thanks liya, I'll have a look at that! – Eudemonism

Answer 4

For NPOI Example with Chart see: http://www.zachhunter.com/2010/05/npoi-excel-template/

Happily

This does not meet the requirements of the question. The person who asked the question even states that he doesn't want to do it from a template. – Ivers

Now it is possible using NPOI: github.com/tonyqus/npoi/blob/master/examples/xssf/LineChart/… – Reader

Answer 5

Romias has right! "Now it is possible using NPOI: github.com/tonyqus/npoi/blob/master/examples/xssf/LineChart/…"

Probably there was no possibility in 2010, but now it is definitely possible. This is my experience, I could create Line and XY chart.

Examples are available in “testcases\ooxml\XSSF\UserModel\Charts” folder of the source code. E.g. "TestXSSFScatterChartData.cs" is the XY chart, my favorite.

Download from: https://github.com/nissl-lab/npoi

Udale

Answer 6

This is what I did and it's working well.

    ```
XSSFDrawing drawing = (XSSFDrawing)Sheet.CreateDrawingPatriarch();

IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, 6, 3, 24, 24);

// Create a pie chart
XSSFChart pieChart = (XSSFChart)drawing.CreateChart(anchor);

IChartLegend legend = pieChart.GetOrCreateLegend();
legend.Position = LegendPosition.Right;
legend.IsOverlay = false;

// Calculate the total price "Forever" of all companies
totalForever = generalWIP.AsEnumerable()
    .Where(row => row.Field<string>("Customer") != "Total:")
    .Sum(row => row.Field<double>("TotalPrice"));

// Prepare the data for the pie chart
List<string> categories = new List<string>(); // Categories (company names)
List<double> values = new List<double>();     // Values (company total prices "Forever")
double othersValue = 0;                       // For "Others" category

// Populate companyValues with actual values
foreach (var row in generalWIP.Rows)
{
    if (row["Customer"].ToString() != "Total:")
    {
        double value = Convert.ToDouble(row["TotalPrice"]);
        companyValues[row["Customer"].ToString()] = value;
    }
}

// Filter out companies with less than 5% value
foreach (var company in companyValues.Keys.ToList())
{
    double percentage = (companyValues[company] / totalForever) * 100;
    if (percentage < 5)
    {
        otherValues.Add(company, companyValues[company]);
        companyValues.Remove(company);
    }
}

// Add "Others" category if necessary
if (otherValues.Count > 0)
{
    companyValues["Others"] = otherValues.Sum(kv => kv.Value);
}

IChartDataSource<string> xs = DataSources.FromStringCellRange(Sheet, new CellRangeAddress(3, 3 + generalWIP.Rows.Count, 1, 1));
IChartDataSource<double> ys1 = DataSources.FromNumericCellRange(Sheet, new CellRangeAddress(3, 3 + generalWIP.Rows.Count, 3, 3));

// Create the data for the pie chart
IPieChartData<string, double> pieChartData = pieChart.ChartDataFactory.CreatePieChartData<string, double>();
IPieChartSeries<string, double> series1 = pieChartData.AddSeries(xs, ys1);
series1.SetTitle("By Price: Forever");
pieChart.Plot(pieChartData);

// Add data labels
if (pieChart.GetCTChart().plotArea.pieChart[0].ser[0].dLbls == null)
{
    pieChart.GetCTChart().plotArea.pieChart[0].ser[0].dLbls = new NPOI.OpenXmlFormats.Dml.Chart.CT_DLbls();
}
pieChart.GetCTChart().plotArea.pieChart[0].ser[0].dLbls.showVal = new NPOI.OpenXmlFormats.Dml.Chart.CT_Boolean();
pieChart.GetCTChart().plotArea.pieChart[0].ser[0].dLbls.showVal.val = 1;
pieChart.GetCTChart().plotArea.pieChart[0].ser[0].dLbls.showSerName = new NPOI.OpenXmlFormats.Dml.Chart.CT_Boolean();
pieChart.GetCTChart().plotArea.pieChart[0].ser[0].dLbls.showSerName.val = 0;
pieChart.GetCTChart().plotArea.pieChart[0].ser[0].dLbls.showCatName = new NPOI.OpenXmlFormats.Dml.Chart.CT_Boolean();
pieChart.GetCTChart().plotArea.pieChart[0].ser[0].dLbls.showCatName.val = 1;
pieChart.GetCTChart().plotArea.pieChart[0].ser[0].dLbls.showPercent = new NPOI.OpenXmlFormats.Dml.Chart.CT_Boolean();
pieChart.GetCTChart().plotArea.pieChart[0].ser[0].dLbls.showPercent.val = 1;
pieChart.GetCTChart().plotArea.pieChart[0].ser[0].dLbls.showLegendKey = new NPOI.OpenXmlFormats.Dml.Chart.CT_Boolean();

pieChart.GetCTChart().plotArea.pieChart[0].ser[0].dLbls.showLegendKey.val = 0; pieChart.GetCTChart().plotArea.pieChart[0].ser[0].explosion = new CT_UnsignedInt(); pieChart.GetCTChart().plotArea.pieChart[0].ser[0].explosion.val = 5; //chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).getDLbls().addNewNumFmt().setFormatCode("0,00"); pieChart.GetCTChart().plotArea.pieChart[0].ser[0].dLbls.numFmt = new NPOI.OpenXmlFormats.Dml.Chart.CT_NumFmt(); pieChart.GetCTChart().plotArea.pieChart[0].ser[0].dLbls.numFmt.sourceLinked = false; pieChart.GetCTChart().plotArea.pieChart[0].ser[0].dLbls.numFmt.formatCode = "#,##0.00"; // Optional: Set the chart title pieChart.SetTitle("By Price: Forever");

Beachcomber