Re: Creating Chart in Excel 2007 using Open XML at Server side

Hi,

Can someone please help me out in creating chart in Excel 2007 using OpenXML. We are done with creating an excel 2007 with data from database but could not proceed further to create the excel chart out of it.

ASP.NET 3.5

VS2008 and Windows 2003 Server (Without Office Installed on Server).

Please treat this as urgent.

Thanks,

Yashwanth

Hi Yashwanth,

Have a read of section 5.13 of the OpenXML Primer for the basic details of creating charts in DrawingML (and the example XML to give you an idea of how this should be structured). Also, section 5.7 of the OpenXML Markup Reference gives further details of the XML elements and how they fit together.

When creating the chart in the SpreadsheetML package, you'll need to define a relationship linking the DrawingML opject to the XML definition of the chart (more information about relationships in section 8.3 of the Open Packaging Convention document).

For a practical example, create (and save) a sample spreadsheet with chart in Excel, then use the DocumentReflector tool that comes with the OpenXML SDK to examine how the spreadsheet and its chart are defined and trace how the relationship is defined.

Hope this helps.

-Daniel

Thanks Daniel for your reply.

Actually, I am looking a related sample code as below, which is working good for me (i.e, creation of Excel Sheet on Server, where no MS-Office is installed). But, I am totally stuck after this, I could not find the code related to creation of Chart on Excel.

Our customer requirement is that, it should be created in two different sheet (i.e Sheet1 will have all the data from database with some columns and relevant chart of the data in Sheet1 to be displayed in another sheet "Sheet2")

**********Working sample for excel sheet generation**********

private static void BuildWorkbook(string fileName)

{

try

{

using (SpreadsheetDocument s = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))

{

WorkbookPart workbookPart = s.AddWorkbookPart();

WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

string relId = workbookPart.GetIdOfPart(worksheetPart);

Workbook workbook = new Workbook();

FileVersion fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };

Worksheet worksheet = new Worksheet();

SheetData sheetData = new SheetData();

DateTime date = new DateTime(2009, 1, 1);

int salesLastYear = 25185;

int salesThisYear = 25348;

//int index1 = 2;

UInt32 i = 0;

for (i = 1; i <= 52; i++)

{

Row contentRow = CreateContentRow(i, date, salesLastYear, salesThisYear);

//index1++;

sheetData.AppendChild(contentRow);

date = date.AddDays(7);

salesLastYear += (int)(salesLastYear * 0.031);

salesThisYear += (int)(salesThisYear * 0.027);

}

worksheet.Append(sheetData);

worksheetPart.Worksheet = worksheet;

worksheetPart.Worksheet.Save();

Sheets sheets = new Sheets();

Sheet sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relId };

sheets.Append(sheet);

workbook.Append(fileVersion);

workbook.Append(sheets);

s.WorkbookPart.Workbook = workbook;

s.WorkbookPart.Workbook.Save();

s.Close();

FixChartData(workbookPart, i - 1);

}

}

catch (Exception)

{

throw;

}

}

private static Row CreateContentRow(UInt32 index, DateTime date, int salesLastYear, int salesThisYear)

{

Row r = new Row { RowIndex = index };

Cell cell1 = CreateTextCell("A", index, date.ToString());

Cell cell2 = CreateNumberCell("B", index, salesLastYear);

Cell cell3 = CreateNumberCell("C", index, salesThisYear);

r.Append(cell1);

r.Append(cell2);

r.Append(cell3);

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;

}

*********************************

Any help will be much appreciated.

One quick note, I am using OpenXMLSDKv2.msi on my desktop and nothing relevant on the Webserver i.e (SDK or MS-Office)

Added reference to "DocumentFormat.OpenXml" in the project solution.

Thanks,

Yashwanth

Hi
Where is this method
FixChartData(workbookPart, i - 1);