ARTICLE

Play with the Excel Object Library using .NET

Posted by Sushmita Kumari in Articles | Office Development on March 02, 2006

Tags: .NET, C#, Excel chart, Excel Report, Web Browser Control

In this article we will see usage of several things like how to use WebBrowser control, manipulating Excel object libarary.

Tweet
62869
6

Reader Level: Beginner

Download Files:

excel.zip

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

This Feature is Sponsored By

DynamicPDF Merger is a developers dream for interacting with any existing PDF documents. Merge, append, split, form fill, flatten stamp and so much more.

post comment

how to open excel file using webbrowser controlin windows application?i tried it but it opens ups in a different window.can anybody tell me how it can be done.for eg my code...webbrowser.navigate(@"D:\text.xlsx",false); If i enter url instead of file path itworks as expected..ir the entered url opens up within webbrowser control

Posted by sudhin on Dec 01, 2011

a happy, thank you!!!

Posted by park junes on Aug 08, 2009

Where it says:

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

Change the ("B3", "B11") to something like ("A3:C11") where A-C are your columns of the chart

Of course, you also have to add data in the respective columns for them to display...

Posted by Robert Heynen on Feb 27, 2008

its fantastic bt i hav to show only graph on winform. so, hw cn do it? plz help me out. thanx.

Posted by Jayesh Sharma on Jan 29, 2008

But how to make two series in one graph? I have column A and colomn B. And want todisplay two diff. series in a one graph.... I can make it in two different graph. Any suggests. Thanks, Naish

Posted by naish shr on Dec 05, 2007

View All Comments

COMMENT USING

PREMIUM SPONSORS

DynamicPDF Developer Components

DynamicPDF™ product line allows you to dynamically generate PDF documents, merge PDF documents and add new content to existing PDF documents from within your applications.

TRENDING UP

Create Outlook Add-in by MAPI Functionality by Using Visual Studio 2010 Outlook Project Debugging jQuery in ASP.NET How to Enable Sign in as Different User Option in SharePoint 2013 Top 10 HTML5 Interview Questions Find and Delete Duplicate Records From SQL Table Panel Default Button is Not Working For Rad Combobox Custom Paging With GridView in ASP.NET 4.5 Working With Database in HTML5 Extending WinJS ListView With Custom Item Events Encrypt and Decrypt in SQL Server: Part 2

View All

MOST LIKED ARTICLE

WCF Introduction and Contracts - Day 1 Create SSRS Report Using Report Wizard CREATE READ UPDATE and DELETE - CRUD Operation Using LINQ to SQL WCF - Data Contract - Day 4 Factory Design Pattern

Follow @twitterapi

Options	Votes	%
Lenovo	19	13.19
HP	6	4.17
Dell	17	11.81
Toshiba	54	37.50
Acer	43	29.86
Sony	5	3.47
Total	144	100%

In Focus

Play with the Excel Object Library using .NET

Related Articles


Favorite Computer Manufacturer

Who is your favorite Computer manufacturer?

Lenovo HP Dell Toshiba Acer Sony