converting a dataset to excel

  • Thread starter Thread starter Robert Smith
  • Start date Start date
R

Robert Smith

Hi,
I wish to convert a dataset to Excel and open the document in Excel from
my winforms c# application.

I have added a reference to microsoft excel object library 9, which is the
highest on my machine. I have the following code

I'm getting an error on the following line
Interop.Excel.Application Excel = new Interop.Excel.Application();

It is saying Inerop doesn't exist in the current context. What am I doing,
does anyone have an app that can copy a dataset to excel and open that excel
document.

Thanx in advance



using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using Interop.Excel;

namespace ExportToExcel
{


public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();

SqlConnection Conn = new SqlConnection("Data
Source=rsmith\\NetSdk;initial catalog=informs; integrated security=true");
SqlDataAdapter MyADP;

MyADP = new SqlDataAdapter("sp_SelectAllAgencies", Conn);
DataSet MyDataSet = new DataSet();
MyADP.Fill(MyDataSet, "Agencies");
this.gvwAgencies.AutoGenerateColumns = true;
this.gvwAgencies.DataSource = MyDataSet.Tables[0];
Interop.Excel.Application Excel = new Interop.Excel.Application();
exportToExcel(MyDataSet);


}

private void exportToExcel(DataSet MyDataSet)
{
Excel.ApplicationClass excel = new ApplicationClass();

excel.Application.Workbooks.Add(true);
DataTable table = MyDataSet.Tables[0];
int ColumnIndex=0;
foreach(Column col in table.Columns)
{
ColumnIndex++;
excel.Cells[1,ColumnIndex]=col.ColumnName;
}
int rowIndex=0;
foreach(DataRow row in table.Rows)
{
rowIndex++;
ColumnIndex=0;
foreach(DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex+1,ColumnIndex]=row[col.ColumnName].Text;

}
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
worksheet.Activate();
}


}
}
 
Hi,
   I wish to convert a dataset to Excel and open the document in Excel from
my winforms c# application.

 I have added a reference to microsoft excel object library 9, which isthe
highest on my machine. I have the following code

I'm getting an error on the following line
 Interop.Excel.Application Excel = new Interop.Excel.Application();

It is saying Inerop doesn't exist in the current context. What am I doing,
does anyone have an app that can copy a dataset to excel and open that excel
document.

Thanx in advance

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using Interop.Excel;

namespace ExportToExcel
{

    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            SqlConnection Conn = new SqlConnection("Data
Source=rsmith\\NetSdk;initial catalog=informs; integrated security=true");
            SqlDataAdapter MyADP;

            MyADP = new SqlDataAdapter("sp_SelectAllAgencies", Conn);
            DataSet MyDataSet = new DataSet();
            MyADP.Fill(MyDataSet, "Agencies");
            this.gvwAgencies.AutoGenerateColumns = true;
            this.gvwAgencies.DataSource = MyDataSet.Tables[0];
            Interop.Excel.Application Excel = new Interop.Excel.Application();
            exportToExcel(MyDataSet);

        }

        private void exportToExcel(DataSet MyDataSet)
        {
            Excel.ApplicationClass excel = new ApplicationClass();

        excel.Application.Workbooks.Add(true);
        DataTable table = MyDataSet.Tables[0];
        int ColumnIndex=0;
        foreach(Column col in table.Columns)
        {
            ColumnIndex++;
            excel.Cells[1,ColumnIndex]=col.ColumnName;
        }
    int rowIndex=0;
        foreach(DataRow row in table.Rows)
            {
        rowIndex++;
        ColumnIndex=0;
            foreach(DataColumn col in table.Columns)
            {
            ColumnIndex++;
            excel.Cells[rowIndex+1,ColumnIndex]=row[col.ColumnName].Text;

            }
        }
            excel.Visible = true;
            Worksheet worksheet = (Worksheet)excel.ActiveSheet;
            worksheet.Activate();
        }

    }



}- Hide quoted text -

- Show quoted text -
Click to expand...

Hi,

You probably have problem with references to Office interop
libraries.
References in VS should be (some old project I have):
- Microsoft.Office.Core (Office.dll)
- Microsoft.Office.Interop.Excel (Microsoft.Office.Interop.Excel.dll)
- VBIDE (Microsoft.Vbe.Interop.dll)

Beware of COM automation problems: http://www.gemboxsoftware.com/GBSpreadsheet.htm#Automation

If your workbooks are not large you can use our GemBox.Spreadsheet
Free (http://www.gemboxsoftware.com/GBSpreadsheetFree.htm)
Excel component for XLS/CSV/XLSX reading/writing/reporting.

--Zeljko
 
Back
Top