How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET

 

How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET

Article ID: 306023

Last Review: December 11, 2006

Revision: 7.4

This article was previously published under Q306023

SUMMARY

This step-by-step article describes several methods for transferring data to Microsoft Excel 2002 from a Microsoft Visual C# 2005 or Microsoft Visual C# .NET program. This article also presents the advantages and disadvantages of each method so that you can select the solution that works best for your situation.

Overview

The technique that is most frequently used to transfer data to an Excel workbook is Automation. With Automation, you can call methods and properties that are specific to Excel tasks. Automation gives you the greatest flexibility for specifying the location of your data in the workbook, formatting the workbook, and making various settings at run-time.
With Automation, you can use several techniques to transfer your data:

• Transfer data cell by cell.
• Transfer data in an array to a range of cells.
• Transfer data in an ADO recordset to a range of cells by using the CopyFromRecordset method.
• Create a QueryTable object on an Excel worksheet that contains the result of a query on an ODBC or OLEDB data source.
• Transfer data to the clipboard, and then paste the clipboard contents into an Excel worksheet.

You can also use several methods that do not necessarily require Automation to transfer data to Excel. If you are running a server-side program, this can be a good approach for taking the bulk of data processing away from your clients.
To transfer your data without Automation, you can use the following approaches:

• Transfer your data to a tab-delimited or comma-delimited text file that Excel can later parse into cells on a worksheet.
• Transfer your data to a worksheet by using ADO.NET.
• Transfer XML data to Excel (version 2002 and 2003) to provide data that is formatted and arranged into rows and columns.

This article provides a discussion and a code sample for each of these techniques. The Create the Complete Sample Visual C# 2005 or Visual C# .NET Project section, later in this article, demonstrates how to create a Visual C# .NET program that executes each technique.

How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET