Need to export content of a datagrid to EXCEL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I need to export the content of a DataGrid (in Windows application in C#), into
an Excel spreadsheet.

Anyone knows how to do this? Any code snippets would help!

thanks a lot,

Maria
 
Maria,

Your options are expanded or reduced based on the version of Excel that
you are running. If you are runing Office XP (I believe), then I believe
that Excel has support for XML in it (I know that 2003 has it, and you can
definitely do it in this case). If there is XML support, you could save
your contents as an XML file and then import that into Excel.

If not, then you will have to cycle through the rows and columns and
insert the values one by one.

Either way, you will have to set a reference to the Microsoft Office
Object Library, as well as the Microsoft Excel Object library, and use the
classes in there to instatiate/get the Excel sheet and then write the values
to it.

Hope this helps.
 
thanks for your reply

Actually, I am running Office 2000, so I will need to do it row by row

What is the name of the libraries that I have to import
Do you have any code snippet that might give me an idea
I know the general idea, but I don't know where to start

Maria
 
Maria,

You will have to go to the COM tab when you select "Add Reference" to
your project. The "Microsoft Office Object Library" will be there, as well
as the "Microsoft Excel Object Library". When you set references to that,
it will create interop assemblies in your project which you will have to
ship with your product.

Also, you will have to install Excel separately, and make sure that it
is the same version. The interop assemblies only handle the calling of the
COM objects, they don't wrap the COM objects for distribution.
 
here is a code snippet from one of my apps..it works for me..

public void ExportToExcel(DataSet myDataSet) {

try {

// Export The Dataset passed to an Excel Spreadsheet.

Excel.Application oXL;

Excel.Workbook oWB;

Excel.Worksheet oSheet;

oXL = new Excel.ApplicationClass();

oXL.Visible = true;

oWB = (Excel.Workbook)(oXL.Workbooks.Add(Missing.Value));

oSheet = (Excel.Worksheet)oWB.ActiveSheet;

oSheet.Name = "QueryBuilder Export";

oXL.WindowState = Excel.XlWindowState.xlMinimized;

// Initialise the progress form.

frmProgress newProgress = new frmProgress();

newProgress.pbProgress.Minimum = 1;

newProgress.pbProgress.Value = 1;

newProgress.pbProgress.Step = 1;

newProgress.Show();


foreach(DataTable table in myDataSet.Tables) {

// Set Maximum to the total number of files to copy.

newProgress.pbProgress.Maximum = table.Rows.Count;

int row = 1, column = 1;

// Need to export the column headers here.

foreach (object oH in table.Columns) {

oSheet.Cells[row, column] = oH.ToString();

column++;

}

row++;

foreach(DataRow r in table.Rows) {

column = 1;

foreach (object o in r.ItemArray) {

oSheet.Cells[row, column] = o.ToString();

column++;

}

row++;

newProgress.pbProgress.PerformStep();

Application.DoEvents();

}

}

newProgress.Close();


MessageBox.Show("Export Process has completed");

oXL.WindowState = Excel.XlWindowState.xlMaximized;


}

catch (Exception e) {

// Exception Handler

MessageBox.Show("Export Process has Errored : " + e.Message);

}

}
 
Back
Top