ARTICLE

Play with the Excel Object Library using .NET

Posted by Sushmita Kumari Articles | Office Development March 02, 2006
In this article we will see usage of several things like how to use WebBrowser control, manipulating Excel object libarary.
Reader Level:
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:-

 

 

COMMENT USING

Trending up