Transfer data to an Excel workbook with Visual C# 2005 or Visual C# .NET - Office (2023)

For a Microsoft Visual Basic 6.0 version of this article, see247412.

This article describes several step-by-step 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 pros and cons of each method so that you can choose the solution that best suits your situation.

general description

The most widely used technique for transferring data to an Excel workbook is automation. Automation allows you to call specific methods and properties in Excel tasks. Automation gives you the most flexibility in specifying the location of your data in the workbook, formatting the workbook, and performing various configurations 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 using the CopyFromRecordset method.
• Create a QueryTable object in an Excel spreadsheet that contains the result of a query against an ODBC or OLEDB data source.
• Transfer the data to the clipboard and paste the clipboard contents into an Excel spreadsheet.
  You can also use various methods that don't necessarily require automation to transfer data to Excel. If you're running a server-side program, this can be a good approach to take most of the data processing off your clients.

To transfer your data without automation, you can use the following approaches:

• Transfer your data to a tab- or comma-delimited text file that Excel can later parse into spreadsheet cells.
• Transfer your data to a spreadsheet using ADO.NET.
• Transfer XML data to Excel (versions 2002 and 2003) to provide data that is formatted and organized in rows and columns.

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

techniques

Use automation to transfer data cell by cell

Automation allows you to transfer data cell by cell to a worksheet:

// Nuevo trabajo en Excel.m_objExcel = new Excel.Application();m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));// Adicionar Dados a Celulas na primeira planilha da nova pasta de trabalho.m_objSheets = (Excel.Sheets)m_objBook.Worksheets;m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));m_objRange = m_objSheet.get_Range("A1", m_objOpt ); m_objRange.Value = "Sobrenome";m_objRange = m_objSheet.get_Range("B1", m_objOpt);m_objRange.Value = "Name";m_objRange = m_objSheet.get_Range("A2", m_objOpt);m_objRange.Value = "Doe"; m_objRange = m_objSheet.get_Range("B2", m_objOpt);m_objRange.Value = "John";// Aplique negrito às cellulas A1:B1.m_objRange = m_objSheet.get_Range("A1", "B1");m_objFont = m_objRange. Font;m_objFont.Bold=true;// Salve una pasta de trabalho y saia do Excel.m_objBook.SaveAs(m_strSampleFolder + "Book1.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt , m_objOpt, m_objOpt);m_o bjBook.Close(false, m_objOpt, m_objOpt);m_objExcel.Quit();

Cell-to-cell data transfer is an acceptable approach when you have a small amount of data. You have the flexibility to place data anywhere in the workbook, and you can conditionally format cells at run time. However, it's not a good idea to use this approach when you need to transfer a large amount of data to an Excel workbook. Each Range object you acquire at runtime results in an interface request, which means slower data transfers. Additionally, Microsoft Windows 95, Microsoft Windows 98, and Microsoft Windows Millennium Edition (Me) have a 64 kilobyte (KB) limit for interface requests. If you have more than 64,000 interface requests, the Automation Server (Excel) might stop responding or you might get low memory errors.

Again, cell-by-cell data transfer is only acceptable for small amounts of data. If you need to transfer a large amount of data to Excel, you should use one of the other approaches described in this article to transfer data in bulk.

For more information and an example of how to automate Excel with Visual C# .NET, click the article number below to view the article in the Microsoft Knowledge Base:

302084HOWTO: Automate Microsoft Excel from Microsoft Visual C# .NET

Use automation to transfer a data matrix to a range in a worksheet

You can transfer an array of data to a range of multiple cells at once:

// Neue Übersetzungen ohne Excel.m_objExcel = new Excel.Application();m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));m_objSheets = (Excel. Sheets) m_objBook.Worksheets;m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));// Schreiben Sie ein Array für Tabellen und A1:C1.object[] objHeaders = {"ID do order", "Amount", " Tax"};m_objRange = m_objSheet.get_Range("A1", "C1");m_objRange.Value = objHeaders;m_objFont = m_objRange.Font;m_objFont.Bold=true;// Schrei ähm Array con 3 Spalten y 100 Zeilen und Addiction -o à// planilha começando na cella A2.object[,] objData = new Object[100,3];Random rdm = new Random((int)DateTime.Now.Ticks); double nOrderAmt, nTax ;for(int r=0;r<100;r++){objData[r,0] = "ORD" + r.ToString("0000");nOrderAmt = rdm.Next(1000);objData[ r,1] = nOrderAmt.ToString("c");nTax = nOrderAmt*0.07;objData[r,2] = nTax.ToString("c");}m_objRange = m_objSheet.get_Range("A2", m_objOpt); m_objRange = m_objRange. get_Resize( 100,3);m_objRange.Value = objData;// Speichern Sie eine Pasta aus Trabalho und Excel.m_objBook.SaveAs(m_strSampleFolder + "Book2.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode . xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt);m_objBook.Close(false, m_objOpt, m_objOpt);m_objExcel.Quit();

If you pass your data through an array instead of cell by cell, you can get a big performance boost when you have a large amount of data. Consider the following lines of code mentioned above, which transfer data to 300 cells in the worksheet:

objRange = objSheet.get_Range("A2", m_objOpt);objRange = objRange.get_Resize(100,3);objRange.Value = objData;

This code represents two interface requests: one for the Range object that is returned by the Range method, and one for the Range object that is returned by the Resize method. By contrast, transferring the data cell by cell requires requests from 300 interfaces to Range objects. Whenever possible, you can benefit from moving your data in bulk and reducing the number of interface requests you make.

For more information about using arrays to get and set range values ​​with Excel Automation, click the article number below to view the article in the Microsoft Knowledge Base:

302096HOWTO: Automate Excel with Visual C# .NET to fill or retrieve data in a range using arrays

Use automation to transfer an ADO recordset to a worksheet range

The Excel 2000, Excel 2002, and Excel 2003 object models provide the CopyFromRecordset method to copy an ADO recordset to a range on a worksheet. The following code demonstrates how to automate Excel to transfer the contents of the Orders table in the Northwind sample database using the CopyFromRecordset method:

// Create a recordset of all records in the table Orders.ADODB.Connection objConn = new ADODB.Connection();ADODB._Recordset objRS = null;objConn.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " +m_strNorthwind + ";", "", "", 0);objConn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;object objRecAff;objRS = (ADODB._Recordset)objConn.Execute("Orders", out objRecAff , ( int )ADODB.CommandTypeEnum.adCmdTable);// Start new workbook in Excel.m_objExcel = new Excel.Application();m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;m_objBook = (Excel._Workbook)(m_objBooks .Add ( m_objOpt ) );m_objSheets = (Excel.Sheets)m_objBook.Worksheets;m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); // Get the Fields collection from the recordset and determine // the number of fields (or columns) System.Collections.IEnumerator objFields = objRS.Fields.GetEnumerator();int nFields = objRS.Fields.Count; // Create an array for the headers and add it to the // worksheet starting at cell A1.object [] objHeaders = new object[nFields];ADODB.Field objField = null;for(int n=0;n <nFields;n++){objFields.MoveNext();objField = (ADODB.Field)objFields. Current;objHeaders[ n] = objField.Name;}m_objRange = m_objSheet.get_Range("A1", m_objOpt);m_objRange = m_objRange.get_Resize(1, nFields);m_objRange.Value = objHeaders;m_objFont = m_objRange.Font;m_objFont. bold = true; // Transfer the record to the table starting at cell A2.m_objRange = m_objSheet.get_Range("A2", m_objOpt);m_objRange.CopyFromRecordset(objRS, m_objOpt, m_objOpt); // Save the workbook and exit Excel .m_objBook.SaveAs( m_strSampleFolder + "Book3.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt);m_objOpt); .Close(objOpt). , m_objtobpt); ;m_tobj; // closes the recordset and the connection.objRS.Close();objConn.Close();

Use Automation to create a QueryTable object in a worksheet

A QueryTable object represents a table created from data returned from an external data source. When automating Excel, you can create a QueryTable by providing a connection string to an OLE DB or ODBC data source and an SQL string. Excel generates the recordset and inserts it into the worksheet at the specified location. QueryTable objects offer the following advantages over the CopyFromRecordset method:

• Excel takes care of creating the recordset and placing it in the worksheet.
• You can save the query with the QueryTable object and update it later to get an updated recordset.
• When a new QueryTable is added to your spreadsheet, you can specify that data already present in spreadsheet cells be moved to process the new data (see the RefreshStyle property for more information).

The following code shows how to automate Excel 2000, Excel 2002, or Excel 2003 to create a new lookup table in an Excel worksheet using data from the Northwind sample database:

// Neue Übersetzungen ohne Excel.m_objExcel = new Excel.Application();m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));// Cria uma QueryTable wird aus A1.m_objSheets = (Excel.Sheets)m_objBook.Worksheets;m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));m_objRange = m_objSheet.get_Range("A1", m_objOpt);m_objQryTables = m_objSheet. QueryTables;m_objQryTable = (Excel._QueryTable)m_objQryTables.Add("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Fonte de dados=" +m_strNorthwind + ";", m_objRange, "Seleccionar * de pedidos");m_objQryTable. RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;m_objQryTable.Refresh(false) XlSaveAsChangeMode.xl , m_objOpt, m_objOpt,m_objOpt, m_objOpt);m_objBook.Close(false, m_objOpt, m_objOpt);m_objExcel.Quit();

Use the Windows clipboard

You can use the Windows clipboard to transfer data to a spreadsheet. To paste data into multiple cells on a worksheet, you can copy a string in which columns are separated by TAB characters and rows are separated by line breaks. The following code illustrates how Visual C# .NET can use the Windows clipboard to transfer data to Excel:

// Kopiere einen String für einen Übertragungsbereich von Windows.string sData = "FirstName\tLastName\tBirthdate\r\n" +"Bill\tBrown\t2/5/85\r\n" +"Joe\tThomas\t1 /1 / 91";System.Windows.Forms.Clipboard.SetDataObject(sData);// Neue Aufgaben ohne Excel erstellen.m_objExcel = new Excel.Application();m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel. _Workbook)(m_objBooks.Add(m_objOpt));// Cole os dados iniciando na clula A1.m_objSheets = (Excel.Sheets)m_objBook.Worksheets;m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1 ));m_objRange = m_objSheet.get_Range("A1", m_objOpt);m_objSheet.Paste(m_objRange, false);// Speichern Sie Trabalho-Nudeln und Excel.m_objBook.SaveAs(m_strSampleFolder + "Book5.xls", m_objOpt , m_objOpt , m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt,m_objOpt, m_objOpt);m_objBook.Close(false, m_objOpt, m_objOpt);m_objExcel.Quit();

Create a delimited text file that Excel can break down into rows and columns

Excel can open tab- or comma-delimited files and correctly parse the data in the cells. You can use this feature when you want to transfer a large amount of data to a spreadsheet using little or no automation. This may be a good approach for a client-server program since the text file can be generated on the server side. You can then open the text file on the client and use automation if necessary.

The following code shows how to generate a tab-delimited text file from data read with ADO.NET:

// Connect to the data source System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strNorthwind + ";") ;objConn .Open();// Run a command to get all records from the Employees.System.Data.OleDb.OleDbCommand table objCmd = new System.Data.OleDb.OleDbCommand( "Select * From Employees", objConn); System .Data . OleDb.OleDbDataReader objReader;objReader = objCmd.ExecuteReader(); // create the FileStream object and StreamWriter to // write the contents of the record to file.System.IO.FileStream fs = new System.IO.FileStream(m_strSampleFolder + "Book6 .txt", System.IO.FileMode. Create); System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.Unicode); // Write the field names (headers) as the first line in the text file.sw.WriteLine(objReader.GetName(0) + "\t" + objReader.GetName(1) +"\t" + objReader. GetName(2) ) + "\t " + objReader.GetName( 3) +"\t" + ob jReader.GetName(4) + "\t" + objReader.GetName(5)); // Write the first six columns of the recordset to a tab-delimited // text file.while(objReader.Read( )) {for(int i=0;i<=5;i++) {if(!objReader .IsDBNull( i)){string s;s = objReader.GetDataTypeName(i);if(objReader.GetDataTypeName(i)= =" DBTYPE_I4"){sw.Write(objReader.GetInt32(i).ToString()); } else if (objReader.GetDataTypeName(i)=="DBTYPE_DATE"){sw.Write(objReader.GetDateTime(i).ToString("d"));} else if (objReader.GetDataTypeName(i )=="DBTYPE_WVARCHAR " ){ sw.Write(objReader.GetString(i));}}if(i<5) sw.Write("\t");}sw.WriteLine(); }sw.Flush();// Writes the data from the buffer to the file stream.// Closes FileStream.fs.Close();// Closes the reader and the connection.objReader.Close();objConn.Close ( );

The above code does not use automation. However, if you prefer, you can use Automation to open the text file and save the file in Excel workbook format, similar to this:

// Abra o arquivo de texto en Excel.m_objExcel = new Excel.Application();m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;m_objBooks.OpenText(m_strSampleFolder + "Book6.txt", Excel.XlPlatform.xlWindows, 1, Excel .XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierDoubleQuote,false, true, false, false, false, false, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);m_objBook = m_objExcel.ActiveWorkbook;// Salve o arquivo de texto na pasta El tipo de formato de trabajo es Excel.m_objBook.SaveAs(m_strSampleFolder + "Book6.xls", Excel.XlFileFormat.xlWorkbookNormal, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt); , m_objOpt, m_objOpt);m_objExcel.Quit();

Transfer data to a worksheet using ADO.NET

You can use the Microsoft Jet OLE DB provider to add records to a table in an existing Excel workbook. A table in Excel is just a series of cells; the range can have a defined name. Typically, the first row in the range contains the headers (or field names), and all subsequent rows in the range contain the records.

The following code adds two new records to a table in Book7.xls. The table in this case is Sheet1:

// Establishes a connection to the data source.System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strSampleFolder +"Book7. xls ; Extended Properties=Excel 8.0;");objConn.Open();// Add two records to the table named "MyTable".System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand() ; objCmd .Connection = objConn;objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" + "Values ​​('Bill', 'Brown')";objCmd.ExecuteNonQuery();objCmd.CommandText = "Insert into MyTable(Name , LastName)" +" values('Joe', 'Thomas')";objCmd.ExecuteNonQuery();// Close Connection.objConn.Close();

When you add records using ADO.NET, as in this example, the formatting is preserved in the workbook. Each record added to a row inherits the format of the previous row.

For more information about using ADO.NET, click the following article numbers to view the articles in the Microsoft Knowledge Base:

306636HOWTO: Connect to a Database and Execute a Command Using ADO.NET and Visual C# .NET

314145HOWTO: Populate a DataSet object from a database using Visual C# .NET

307587HOWTO: Update a database from a DataSet object using Visual C# .NET

For more information about using the Jet OLEDB provider with Excel data sources, click the following article numbers to view the articles in the Microsoft Knowledge Base:

278973EXAMPLE: ExcelADO demonstrates how to use ADO to read and write data in Excel workbooks

257819HOW TO: Use ADO with Excel data from Visual Basic or VBA

XML Data Transfer (Excel 2002 and Excel 2003)

Excel 2002 and 2003 can open any well-formed XML file. You can open XML files directly using the Open command on the File menu, or programmatically using the Open or OpenXML methods of the Workbooks collection. When creating XML files for use in Excel, you can also create style sheets to format the data.

Create a complete sample Visual C# .NET project

1. Create a new folder called C:\ExcelData. The sample program saves Excel workbooks in this folder.

2. Create a new workbook to record the sample:

   1. Start a new workbook in Excel.
   2. On Sheet1 of the new workbook, type FirstName in cell A1 and LastName in cell B1.
   3. Elijah A1:B1.
   4. On the Insert menu, point to Name and click Define. Enter the name MyTable and click OK.
   5. Save the workbook as C:\Exceldata\Book7.xls.
   6. Quit Excel.

3. Start Microsoft Visual Studio 2005 or Microsoft Visual Studio .NET. On the File menu, point to New and click Project. In Visual C# projects orVisual C#, select the Windows app. By default, Form1 is created.

4. Add a reference to the Excel Object Library and ADODB Core Interop Assembly. To do this, follow these steps:

   1. On the Project menu, click Add Reference.
   2. On the NET tab, find ADODB and click Select.

   Note In Visual Studio 2005, you do not have to clickChoose.
   3. On the COM tab, locate Microsoft Excel 10.0 Object Library or Microsoft Excel 11.0 Object Library and click Select.

   Note In Visual Studio 2005, you do not have to clickChoose.

   Note If you are using Microsoft Excel 2002 and have not already done so, Microsoft recommends that you download and install the Microsoft Office XP Primary Interop Assemblies (PIAs).

5. In the Add References dialog, click OK to accept your choice.

6. Add a ComboBox control and a Button control to Form1.

   (Video) C# Tutorial - Import data from Excel to SQL Server | FoxLearn

7. Add event handlers for the Form Load and Button control Click events:

   1. In Design view of Form1.cs, double-click Form1.

   The handler for the Form Load event is created and displayed in Form1.cs.
   2. On the View menu, click Designer to switch to Design view.
   3. Double-click Button1.

   The handler for the button click event is created and displayed in Form1.cs.

8. In Form1.cs, replace the following code:

   private void Form1_Load(sending object, System.EventArgs e){}private void button1_Click(sending object, System.EventArgs e){}

   of:

   // References to Excel objects. private excel.Application m_objExcel = null; private Excel.Workbooks m_objBooks = null; private Excel._Workbook m_objBook = null; private Excel.Sheets m_objSheets = null; private Excel._Worksheet m_objSheet = null; private Excel.Range m_objRange = null; private Excel.Font m_objFont = null; Private Excel.QueryTables m_objQryTables = null; private Excel._QueryTable m_objQryTable = null; // Frequently used variable for optional arguments. private object m_objOpt = System.Reflection.Missing.Value; // Paths used by the sample code to access and save data. private object m_strSampleFolder = "C:\\ExcelData\\"; private string m_strNorthwind = "C:\\Program Files\\Microsoft Office\\Office10\\Samples\\Northwind.mdb";private void Form1_Load(sender object, System.EventArgs e) { comboBox1.DropDownStyle = ComboBoxStyle.DropDownList ;comboBox1 .Items.AddRange(new object[]{ "Use automation to transfer data cell by cell", "Use automation to transfer an array of data to a range in a worksheet", "Use automation to transfer an array ADO Records to a Worksheet Range", "Use automation to create a lookup table in a spreadsheet", "Use the clipboard", "Create a delimited text file that Excel can break down into rows and columns" , "ADO.NET data transfer to a table"} ); comboBox1.SelectedIndex = 0; button1.Text = "Go!"; }private void button1_Click(object sender, System.EventArgs e) { switch (comboBox1.SelectedIndex) { case 0: Automation_CellByCell(); break; F all 1: Automation_UseArray(); break; Case 2: Automation_ADORecordset(); break; Case 3: Automation_QueryTable(); break; Case 4: Use_Clipboard(); break; Case 5: Create_TextFile(); break; Case 6: Use_ADONET(); break; }//Clear m_objFont = null; m_objRange = null; m_objLeaf = null; m_objLeaves = null; m_objLivros = null; m_objLibro = null; m_objExcel = null; GC.Collect();}private void Automation_CellByCell() { // Start a new workbook in Excel. m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); // Add data to the cells of the first worksheet in the new workbook. m_objSheets = (Excel.Sheets)m_objBook.Sheets; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objRange.set_Value(m_objOpt,"LastName"); m_objRange = m_objSheet.get_Range("B1", m_objOpt); m_objRange.set_Value(m_objOpt,"Name"); m_objRange = m_objSheet.get_Range("A2", m_objOpt); m_objRange.set_Value(m_objOpt,"Donate"); m_objRange = m_objSheet.get_Range("B2", m_objOpt); m_objRange.set_Value(m_objOpt,"John"); // Apply bold to cells A1:B1. m_objRange = m_objSheet.get_Range("A1", "B1"); m_objFont = m_objRange.Font; m_objFuente.Bold=true; // Save the workbook and exit Excel. m_objBook.SaveAs(m_strSampleFolder + "Book1.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit();}private void Automation_UseArray() { // Start a new workbook in Excel. m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); m_objSheets = (Excel.Sheets)m_objBook.Sheets; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); // Create an array for the headers and add it to cells A1:C1. object[] objHeaders = {"Order ID", "Amount", "Tax"}; m_objRange = m_objSheet.get_Range("A1", "C1"); m_objRange.set_Value(m_objOpt,objHeaders); m_objFont = m_objRange.Font; m_objFuente.Bold=true; // Create a matrix with 3 columns and 100 rows and add it to the table // starting at cell A2. object[,] objData = new object[100,3]; Random rdm = new Random((int)DateTime.Now.Ticks); double nOrderAmt, nTax; for(int r=0;r<100;r++) { objData[r,0] = "ORD" + r.ToString("0000"); nOrderAmt = rdm.Next(1000); objData[r,1] = nOrderAmt.ToString("c"); nTax = nOrderAmount*0.07; objData[r,2] = nTax.ToString("c"); } m_objRange = m_objSheet.get_Range("A2", m_objOpt); m_objRange = m_objRange.get_Resize(100,3); m_objRange.set_Value(m_objOpt,"objData"); // Save the workbook and exit Excel. m_objBook.SaveAs(m_strSampleFolder + "Book2.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit();}private void Automation_ADORecordset() { // Create a recordset of all records in the orders table. ADODB.Connection objConn = new ADODB.Connection(); ADODB._Recordset objRS = null; objConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" + m_strNorthwind + ";", "", "", 0); objConn.CursorLocation = ADODB.CursorLocationEnum.adUseClient; object objRecAff; objRS = (ADODB._Recordset)objConn.Execute("Orders", out objRecAff, (int)ADODB.CommandTypeEnum.adCmdTable); // Start new workbook in Excel. m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); m_objSheets = (Excel.Sheets)m_objBook.Sheets; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); // Get the Fields collection from the Recordset and determine // the number of fields (or columns). System.Collections.IEnumerator objFields = objRS.Fields.GetEnumerator(); int nFields = objRS.Fields.Account; // Create an array for the headers and add it to the table // starting at cell A1. object[] objHeaders = new object[nFields]; ADODB.Field objField = null; for(int n=0;n<nFields;n++) { objFields.MoveNext(); objField = (ADODB.Field)objFields.Current; objHeaders[n] = objField.Name; } m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objRange = m_objRange.get_Resize(1, nFields); m_objRange.set_Value(m_objOpt,objHeaders); m_objFont = m_objRange.Font; m_objFont.Bold=true;// Transfer the records to the worksheet starting at cell A2. m_objRange = m_objSheet.get_Range("A2", m_objOpt); m_objRange.CopyFromRecordset(objRS, m_objOpt, m_objOpt); // Save the workbook and exit Excel. m_objBook.SaveAs(m_strSampleFolder + "Book3.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit();//Close recordset and connection objRS.Close(); objConn.Close();}private void Automation_QueryTable() { // Start a new workbook in Excel. m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); // Create a lookup table starting at cell A1. m_objSheets = (Excel.Sheets)m_objBook.Sheets; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objQryTables = m_objSheet.QueryTables; m_objQryTable = (Excel._QueryTable)m_objQryTables.Add( "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" + m_strNorthwind + ";", m_objRange, "Select * from Orders"); m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows; m_objQryTable.Refresh(false); // Save the workbook and exit Excel. m_objBook.SaveAs(m_strSampleFolder + "Book4.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit();}private void Use_Clipboard() { // Copies a string to the clipboard. string sData = "FirstName\tLastName\tBirthdate\r\n" + "Bill\tBrown\t2/5/85\r\n" + "Joe\tThomas\t1/1/91"; System.Windows.Forms.Clipboard.SetDataObject(sData); // Start new workbook in Excel. m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); // Insert data starting at cell A1. m_objSheets = (Excel.Sheets)m_objBook.Sheets; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objSheet.Paste(m_objRange, false); // Save the workbook and exit Excel. m_objBook.SaveAs(m_strSampleFolder + "Book5.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit();}private void Create_TextFile() { // Connect to the data source. System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strNorthwind + ";"); objConn.Open(); // Run a command to get all the records from the Employees table. System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand( "Select * from employees", objConn); System.Data.OleDb.OleDbDataReader objReader; objReader = objCmd.ExecuteReader(); // Create the FileStream and StreamWriter objects to // write the contents of the recordset to the file. System.IO.FileStream fs = new System.IO.FileStream( m_strSampleFolder + "Book6.txt", System.IO.FileMode.Create); System.IO.StreamWriter sw = new System.IO.StreamWriter( fs, System.Text.Encoding.Unicode); // Write the field names (headers) as the first line of the text file. sw.WriteLine(objReader.GetName(0) + "\t" + objReader.GetName(1) + "\t" + objReader.GetName(2) + "\t" + objReader.GetName(3) + "\t " + objReader.GetName(4) + "\t" + objReader.GetName(5)); // Writes the first six columns of the recordset as tab-delimited // to a text file. while(objReader.Read()) { for(int i=0;i<=5;i++) { if(!objReader.IsDBNull(i)) { string s; s = objReader.GetDataTypeName(i); if(objReader.GetDataTypeName(i)=="DBTYPE_I4") {sw.Write(objReader.GetInt32(i).ToString()); } Else if(objReader.GetDataTypeName(i)=="DBTYPE_DATE") { sw.Write(objReader.GetDateTime(i).ToString("d")); } Else if (objReader.GetDataTypeName(i)=="DBTYPE_WVARCHAR") { sw.Write(objReader.GetString(i)); } } if(i<5) sw.Write("\t"); } sw.WriteLine(); } sw.Flush();// Writes the buffered data to the FileStream.// Closes the FileStream. fs.Close();// Close the reader and the connection. objReader.Close(); objConn.Close(); // ================================================ = ===== ================= // Optionally automate Excel to open the text file and save it in // Excel workbook format // Open the file of text in Excel. m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBooks.OpenText(m_strSampleFolder + "Book6.txt", Excel.XlPlatform.xlWindows, 1, Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierDoubleQuote, false, true, false, false, false, false, m_objOpt, m_objOpt, m_objOpt m_objOpt, m_objOpt, m_objOpt, m_objOpt);m_objBook = m_objExcel.ActiveWorkbook; // Save the text file in typical workbook format and exit Excel. m_objBook.SaveAs(m_strSampleFolder + "Book6.xls", Excel.XlFileFormat.xlWorkbookNormal, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_obj ); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit();}private void Use_ADONET() { // Establish a connection to the data source. System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strSampleFolder + "Book7.xls;Extended Properties=Excel 8.0;"); objConn.Open(); // Add two records to the table named "MyTable". System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand(); objCmd.Connection = objConn; objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" + "Values('Bill', 'Brown')"; objCmd.ExecuteNonQuery(); objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" + "Values ​​('Joe', 'Thomas')"; objCmd.ExecuteNonQuery(); // Close connection. objConn.Close(); } } // end of class } // end of namespace

   Note You must change the code in Visual Studio 2005. By default, Visual C# adds a form to your project when you create a Windows Forms project. The form is called Form1. The two files that represent the form are named Form1.cs and Form1.designer.cs. Write code in Form1.cs. In the Form1.designer.cs file, the Windows Forms designer writes the code that implements all the actions you perform by dragging and dropping controls from the toolbox.

   For more information about the Windows Forms Designer in Visual C# 2005, visit the following Microsoft Developer Network (MSDN) website:

   Create a project (Visual C#)Note: If you did not install Office to the default folder (C:\Program Files\Microsoft Office), change the m_strNorthwind constant in the code sample to match the installation path of Northwind.mdb.

9. Add the following to the using directives in Form1.cs:

   usando System.Reflection;usando System.Runtime.InteropServices;usando Excel = Microsoft.Office.Interop.Excel;

10. Press F5 to compile and run the sample.

references

For more information, visit the following Microsoft website:

Microsoft Office development with Visual Studio