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