Play with the Excel Object Library using .NET

Sushmita Kumari

C#.NETWeb Browser ControlExcel ReportExcel chart

6 Comments

Join the conversation! Your thoughts help the community grow.

sudhin15y ago
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
park junes17y ago
a happy, thank you!!!
Jayesh Sharma19y ago
its fantastic bt i hav to show only graph on winform. so, hw cn do it? plz help me out. thanx.
naish shr19y ago
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
- Robert Heynen18y agoedited
  Where it says: Excel.Range chartRange = m_oSheet.get_Range("B3", "B11");<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p> 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...
Dinesh19y ago
I've gone through generation of excel using C#.. but how can we do the same(automate excel) in web application using asp .net/C#

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