CODEWITHSUNDEEP
c#excel-2007openxmlopenxml-sdk
Learning Curve
Learning Curve

Reputation: 1569

How to populate data in Excell SpreadSheet and than how to draw Chart from this data using 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. I have two Questions here

  1. How to populate data in SpreadSheet from dataset
  2. How to draw chart (Any chart example) on the basis of available data

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();
 }

Upvotes: 2

Views: 2246

Answers (2)

Learning Curve
Learning Curve

Reputation: 1569

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!

Upvotes: 0

Urik
Urik

Reputation: 1728

public void CreateExcelDocFromDatatable(DataTable dataTable)
{
    object misValue = System.Reflection.Missing.Value;
    Excel.Application xlApp = new Excel.ApplicationClass();
    Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
    Excel.Worksheet xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

    int iCol = 0;
    foreach (DataColumn c in dataTable.Columns)
    {
        iCol++;
        xlWorkSheet1.Cells[1, iCol] = c.ColumnName;
    }

    int iRow = 0;
    foreach (DataRow r in dataTable.Rows)
    {
        iRow++;
        iCol = 0;
        foreach (sd.DataColumn c in mdtOutput.Columns)
        {
            string cellData = r[c.ColumnName].ToString();
            iCol++;
            xlWorkSheet1.Cells[iRow + 1, iCol] = r[c.ColumnName];
        }
    }

    xlWorkSheet1.Activate();
    var range = xlWorkSheet1.get_Range("2:2",misValue);
    range.Select();
    xlApp.ActiveWindow.FreezePanes = true;
    range = xlWorkSheet1.get_Range("1:1", misValue);
    range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);

    mXlWorkBook.SaveAs(outputFilePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlApp.Visible = true;
    xlWorkSheet1.Activate();
}




private void CreateExcelCharts()
{
    object misValue = System.Reflection.Missing.Value;
    Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
    Excel.Worksheet chartsSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
    chartsSheet.DisplayRightToLeft = false;
    chartsSheet.Name = "Charts";

    Excel.ChartObjects chartObjs = (Excel.ChartObjects)chartsSheet.ChartObjects(Type.Missing);
    Excel.ChartObject chartObj = chartObjs.Add(200, 40, 300, 300);
    Excel.Chart xlChart = chartObj.Chart;
    Excel.Range range = chartsSheet.get_Range("B2", "C7");
    xlChart.SetSourceData(range, misValue);
    xlChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DPie;
    mXlWorkBook.Save();
}

Then you can play with the chart properties, where you'd be able to control practically everything as from Excel UI (chart title, axes, gridlines, colors, etc.)

Upvotes: 1

Related Questions