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. I have two Questions here
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();
}
I have found a very good solution for this scenario. Table in Excel can be drawn from the following link http://www.nitrix-reloaded.com/2010/09/26/creating-excel-files-from-dataset-using-openxml-20-c-sharp/
and than by using http://spreadsheetlight.com/ Libraries any type of chart/graph can be drawn in Excel.
Changes can be made according to the scenario. Such as supplying starting and ending cell values for chart creation etc. In my scenario I have worked with run time values where I am not aware of what and how many columns are there in my Excel table. I can provide the code snippet of my scenario, if anyone needs it.
Regards!