Play with the Excel Object Library using .NET

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

}

}

}

}

}