Reader Level:
Articles

Play with the Excel Object Library using .NET

By Sushmita Kumari on March 02, 2006
In this article we will see usage of several things like how to use WebBrowser control, manipulating Excel object libarary.
  • 0
  • 0
  • 68693
Download Files:
 

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:-

 

 

Sushmita Kumari

Sushmita kumari holds  under graduate in Computer Science degree and MBA in Healthcare Management.She is Brain Bench and Microsoft certified professional. Presently she is working  as a software engineer. ... Read more

Personal Blog: http://www.addiox.com
COMMENT USING

Trending up