Click here to Skip to main content
12,690,575 members (33,777 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# .NET Office Chart
Hi folks,
I have generated an Excel using Open XML and input some data. Now I want to create a Bar Chart based on these data; however I have no idea how to continue. Can anyone share a code sample for that? Thanks very much in advance.
Below is the codes I used(this part without any error):
using System;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Drawing.Charts;

namespace openxmlExcel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {


        }

        private void button1_Click(object sender, EventArgs e)
        {
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create("D:\\test.xls", SpreadsheetDocumentType.Workbook))
            {

                // Add a WorkbookPart to the document.
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                ////workbookpart.Workbook = new Workbook();

                // Add a WorksheetPart to the WorkbookPart.
                WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                ////worksheetPart.Worksheet = new Worksheet(new SheetData());
                string relld = workbookpart.GetIdOfPart(worksheetPart);
                Workbook workbook = new Workbook();
                FileVersion fileVersion = new FileVersion { ApplicationName = "MS Excel" };
                Worksheet worksheet = new Worksheet();
                SheetData sheetData = new SheetData();
            
                Row contentRow1 = CreateTextRow(4,"Market1","Market2","Market3");
                sheetData.AppendChild(contentRow1);
                Row contentRow2 = CreateContentRow(5,"2009",120,90,110);
                sheetData.AppendChild(contentRow2);
                Row contentRow3 = CreateContentRow(6,"2010",152,69,100);
                sheetData.AppendChild(contentRow3);
                Row contentRow4 = CreateContentRow(7,"2011",163,65,54);
                sheetData.AppendChild(contentRow4);
                Row contentRow5 = CreateContentRow(8,"2012",120,35,56);
                sheetData.AppendChild(contentRow5);                
                worksheet.Append(sheetData);                                           
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();
                Sheets sheets=new Sheets();
                Sheet sheet = new Sheet {Name="Sheet-chart",SheetId=1,Id=relld};
                sheets.Append(sheet);              
                workbook.Append(fileVersion);
                workbook.Append(sheets);               
                spreadsheetDocument.WorkbookPart.Workbook = workbook;
                spreadsheetDocument.WorkbookPart.Workbook.Save();
                spreadsheetDocument.Close();              
                System.Diagnostics.Process.Start("D:\\test.xls");

            }
        }

        private OpenXmlElement[] Formula(SheetData sheetData)
        {
            throw new NotImplementedException();
        }
            
             
        private static Row CreateTextRow(UInt32 index,string header1, string header2,string header3)
        {
            Row r = new Row { RowIndex =index };
            Cell cell1 = CreateTextCell("D", index, header1);
            Cell cell2 = CreateTextCell("E", index, header2);
            Cell cell3 = CreateTextCell("F", index, header3);
            r.Append(cell1);
            r.Append(cell2);
            r.Append(cell3);
            return r;
        }
        private static Row CreateContentRow(UInt32 index,string years,int sale1,int sale2,int sale3)
        {

            Row r=new Row{RowIndex=index};
            Cell cell1 = CreateTextCell( "C", index,years);
            Cell cell2 = CreateNumberCell("D", index, sale1);
            Cell cell3 = CreateNumberCell("E",index,sale2);
            Cell cell4 = CreateNumberCell("F",index,sale3);           
            r.Append(cell1);
            r.Append(cell2);
            r.Append(cell3);
            r.Append(cell4);     

            return r;

        }

        private static Cell CreateTextCell(string  header, UInt32 index, string text)
        {

            Cell c = new Cell { DataType = CellValues.InlineString,CellReference=header+index };

            InlineString istring = new InlineString();

            Text t = new Text { Text = text };

            istring.Append(t);

            c.Append(istring);

            return c;

        }

        private static Cell CreateNumberCell(string  header, UInt32 index, int number)
        {

            Cell c = new Cell {CellReference=header+index};

            CellValue v = new CellValue { Text = number.ToString() };

            c.Append(v);

            return c;

        }     
 
    }
}


Please Help...

Best Regards
Gerxy
Posted 25-Jul-12 23:35pm
devmr749
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

ChartSpace object is the main object in chart creation. Chart object and many properties to it are added later point of time.

Whenever you are dealing with Office Open Xml please have Open XML SDK 2.0 Productivity Tool installed in your system. Create a chart in excel manually. Open up the Productivity Tool and open the excel file which contains the chart. Right click and see the Reflected code for your chart. Try to understand it and try to create your generic code following similar approach followed there.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

I found that the Open XML SDK Productivity Tool is useful for this kind of thing. You can create a document containing a bar chart or whatever then open it using the tool and reflect the code, then modify it to suit. For an Excel file containing a barchart it should have something like this:

            C.ChartSpace chartSpace1 = new C.ChartSpace();
            chartSpace1.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");
            chartSpace1.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
            chartSpace1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            C.Date1904 date19041 = new C.Date1904(){ Val = false };
            C.EditingLanguage editingLanguage1 = new C.EditingLanguage(){ Val = "en-AU" };
            C.RoundedCorners roundedCorners1 = new C.RoundedCorners(){ Val = false };

            C.BarChart barChart1 = new C.BarChart();
            C.BarDirection barDirection1 = new C.BarDirection(){ Val = C.BarDirectionValues.Bar };
            C.BarGrouping barGrouping1 = new C.BarGrouping(){ Val = C.BarGroupingValues.Clustered };
            C.VaryColors varyColors1 = new C.VaryColors(){ Val = false };
            C.BarChartSeries barChartSeries1 = new C.BarChartSeries();
            C.Index index1 = new C.Index(){ Val = (UInt32Value)0U };
            C.Order order1 = new C.Order(){ Val = (UInt32Value)0U };
            C.InvertIfNegative invertIfNegative1 = new C.InvertIfNegative(){ Val = false };
            C.Values values1 = new C.Values();
            C.NumberReference numberReference1 = new C.NumberReference();
            C.Formula formula1 = new C.Formula();
            formula1.Text = "Sheet1!$A$1:$A$3";



(I've omitted a lot more since it gets quite cluttered).
  Permalink  
Comments
Gerxy 31-Jul-12 22:30pm
   
sorry,
following your code and modifying it, i still can`t solve it . could you give me more with comments ? thanks in advance.


Best Regards
shakti patnayak 27-Aug-12 15:41pm
   
Perhaps you can visit open xml developer forum where you can get the exact code.
http://openxmldeveloper.org
ranjith.murthy 23-May-16 15:00pm
   
Guy please provide any document which helps me understand charts in Open xml Excel

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170117.1 | Last Updated 24 Aug 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100