Play with the Excel Object Library using .NET

Here you can find how to pass value to the cells as well as generating Charts based on the Excel data. In this example I have used " Excel Object Library 11.0".

using System; 

using System.Collections.Generic; 

using System.ComponentModel; 

using System.Data; 

using System.Drawing; 

using System.Text; 

using System.Windows.Forms; 

using Microsoft.Office.Core; 

using interop = System.Runtime.InteropServices; 

namespace excel 

{ 

          public partial class Form1 : Form

                       {

                                public Form1()

                                {

                                         InitializeComponent();

                                }

                                // Excel.Application excel;

                                private Excel.Application m_oExcelApp;

                                private Excel.Workbooks m_oBooks;

                                private Excel._Workbook m_oBook;

                                private Excel._Worksheet m_oSheet;

                                private Excel.Range excelRange;

                                private void Form1_Load(object sender, EventArgs e)

                                {

                                }

                                private void button1_Click(object sender, EventArgs e)

                                {

                                         //office.Application excel = null;

                                         m_oExcelApp = new Excel.Application();

                                         m_oExcelApp.Visible = false;

                                         //m_oBooks = m_oExcelApp.Workbooks;

                                         m_oSheet = null;

                                         m_oBooks = null;

                                         excelRange = null;

                                         object missing = Type.Missing;

                                         try

                                         {

                                                   m_oBooks = m_oExcelApp.Workbooks;

                                                   m_oBook = m_oExcelApp.Workbooks.Add(missing);

 

                                                   m_oSheet = (Excel._Worksheet)m_oBook.ActiveSheet;

                                                   excelRange = m_oSheet.get_Range("B1", missing);

                                                   excelRange.Value2 = "Sales Detail";

                                                   m_oSheet.get_Range("B1", missing).Font.Bold = false;

                                                   excelRange = m_oSheet.get_Range("A3", missing);

                                                   m_oSheet.get_Range("A3", missing).Font.Bold = false;

                                                   excelRange.Value2 = "Quarter";

                                                   excelRange = m_oSheet.get_Range("B3", missing);

                                                   m_oSheet.get_Range("B3", missing).Font.Bold = false;

                                                   excelRange.Value2 = "Sales";

                                                   excelRange = m_oSheet.get_Range("A5", missing);

                                                   excelRange.Value2 = "First";

                                                   excelRange = m_oSheet.get_Range("B5", missing);

                                                   excelRange.Value2 = 1000.0;

                                                   excelRange = m_oSheet.get_Range("A6", missing);

                                                   excelRange.Value2 = "Second";

                                                   excelRange = m_oSheet.get_Range("B6", missing);

                                                   excelRange.Value2 = 2500.0;

                                                   excelRange = m_oSheet.get_Range("A7", missing);

                                                   excelRange.Value2 = "Third";

                                                   excelRange = m_oSheet.get_Range("B7", missing);

                                                   excelRange.Value2 = 4500.0;

                                                   excelRange = m_oSheet.get_Range("A8", missing);

                                                   excelRange.Value2 = "Fourth";

                                                   excelRange = m_oSheet.get_Range("B8", missing);

                                                   excelRange.Value2 = 4500.0;

                                                   excelRange = m_oSheet.get_Range("A9", missing);

                                                   excelRange.Value2 = "Fifth";

                                                   excelRange = m_oSheet.get_Range("B9", missing);

                                                   excelRange.Value2 = 4300.0;

                                                   excelRange = m_oSheet.get_Range("A10", missing);

                                                   excelRange.Value2 = "Sixth";

                                                   excelRange = m_oSheet.get_Range("B10", missing);

                                                   excelRange.Value2 = 4100.0;

                                                   excelRange = m_oSheet.get_Range("A11", missing);

                                                   excelRange.Value2 = "Fourth";

                                                   excelRange = m_oSheet.get_Range("B11", missing);

                                                   excelRange.Value2 = 4000.0;

                                                   excelRange = m_oSheet.get_Range("A13", missing);

                                                   excelRange.Value2 = "Total";

                                                   excelRange = m_oSheet.get_Range("B13", missing);

                                                   m_oSheet.get_Range("A13", missing).Font.Bold = false;

                                                   excelRange.Formula = "=@Sum(B5..B11)";

                                                   excelRange.Font.Bold = false;

                                                   excelRange.Calculate();

                                                   Excel.ChartObjects charts = (Excel.ChartObjects)m_oSheet.ChartObjects

                                                   (missing);

                                                   Excel.ChartObject objChart = charts.Add(150, 20, 500, 300);

                                                   Excel.Range chartRange = m_oSheet.get_Range("B3", "B11");

                                                   objChart.Chart.SetSourceData(chartRange, missing);

                                                   objChart.Chart.ChartType = Excel.XlChartType.xlLineMarkers;      
                                                   //If you want to generate the diffrent kind of chart/Graph you need to

                                                   make a change in (objChart.Chart.ChartType =

                                                   Excel.XlChartType.xlLineMarkers; ) line.Inplace of

                                                   XlChartType.xlLineMarkers you need to select the another option. 

                                                   Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)

                                                   objChart.Chart.SeriesCollection(missing);

                                                   Excel.Series series = seriesCollection.Item(seriesCollection.Count);

                                                   string template = Application.StartupPath;

                                                   string strRunReport=template + "\\" + "SalesReport.xls";

                                                   m_oBook.SaveAs(strRunReport, Excel.XlFileFormat.xlWorkbookNormal, null,

                                                   null, null, null, Excel.XlSaveAsAccessMode.xlShared, null, null, null, null,

                                                   null);

                                                   this.webBrowser1.Navigate(strRunReport);

                                         }

                                         catch (interop.COMException ex)

                                         {

                                                   MessageBox.Show("Error accessing Excel: " + ex.ToString());

                                         }

                                         catch (Exception ex)

                                         {

                                                   MessageBox.Show("Error: " + ex.ToString());

                                         }

                                         finally

                                          {

                                                   if (m_oExcelApp == null)

                                                   {

                                                             m_oExcelApp.Quit();

                                                             m_oExcelApp = null;

                                                             GC.Collect();

                                                             GC.WaitForPendingFinalizers();

                                                             GC.Collect();

                                                             GC.WaitForPendingFinalizers();

                                                   }

                                         }

                                }

                       }

}

 

Output:-