Re: Populating data from dataset to SpreadSheet and than drawing chart C#

Development Tools

Discussions about working with Open XML using a wide range of development tools

Details

4 Replies
3 Subscribers
Posted11 months ago

Options

Open XML SDK 2.0

Populating data from dataset to SpreadSheet and than drawing chart C#

rated by 0 users
This post has 4 Replies |
3 Followers

Waseem

Posted by Waseem

on Thu, Jul 5 2012 3:48 AM

rated by 0 users
Post Points: 20
Populating data from dataset to SpreadSheet and than drawing chart C#

I have created a spread sheet (Example for MSDN) and now I want to populate data from a dataset to this sheet. Once the data is populated than I want to draw chart on the basis of available data. I dont want to use Pivot Table, I just want to draw chart whatever data is comming from dataset.I have never worked with spreadsheets before and can't find a right example to get some help from. Anyone's help will really be appriciated. Following is my code snippet

Public void CreateSpreadSheet()

{
DataSet dataSet = GetDatasetForSpreadSheetChart();
int noOfRows = dataSet.Tables["SpreadSheetTestTable"].Rows.Count;
int noOfColumns = dataSet.Tables["SpreadSheetTestTable"].Columns.Count;

// Create a spreadsheet document by supplying the filepath.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filePath, 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());

// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());

// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),SheetId = 1,Name = "mySheet"
};

sheets.Append(sheet);

// Get data from dataset and insert it in spreadsheet
SheetData sheetData = sheet.GetFirstChild<SheetData>();
for (int c = 0; c < noOfColumns; c++)
{
string headerName = dataSet.Tables["SpreadSheetTestTable"].Columns[c].ToString();
}

workbookpart.Workbook.Save();

// Close the document.
spreadsheetDocument.Close();
}

SpreadsheetML, C#, Excel Table C# Insert Rows, open xml 2.0, Excel

bobm

2650

Posted by bobm

on Thu, Jul 5 2012 8:00 AM

rated by 0 users
Post Points: 20
Re: Populating data from dataset to SpreadSheet and than drawing chart C#

There is a cmdlet in the PowerTools for OpenXML that creates a chart. You might find that useful. The file ChartsheetAccessor.cs contains the code for creating charts.

Waseem

Posted by Waseem

on Fri, Jul 6 2012 6:45 AM

rated by 0 users
Post Points: 20
Re: Populating data from dataset to SpreadSheet and than drawing chart C#

Thanks BobM,

I will look into the PowerTools for OpenXML.

SoftArtisans

Posted by SoftArtisans

on Wed, Jul 18 2012 10:29 AM

rated by 0 users
Post Points: 20
Re: Populating data from dataset to SpreadSheet and than drawing chart C#

Hi Waseem – You might try OfficeWriter, which has a full blown charting API. By using the ExcelApplication object in the OfficeWriter library, you’d have access to an entire object model representing various parts of an XLSX workbook, including creating/modification of charts/series etc. You can take a look at the code sample here. Additionally you can also take a look at the docs.

Disclaimer: I am one of the developers on the OfficeWriter team. Let me know if you have any questions! -Ozgur

Waseem

Posted by Waseem

on Mon, Jul 23 2012 2:56 AM

rated by 0 users
Post Points: 5
Re: Populating data from dataset to SpreadSheet and than drawing chart C#

Hi Ozgur, Thank you very much for your reply. I found a very good code sample to create table (from dataset) in Excel and than by using SpreadSheetLight I am drawing charts. I will give a try to your OfficeWriter in near future. Thanks once again!