This Article Was Previously Published Under Q306023
For a Microsoft Visual Basic .NET VERSION OF This Article, See
306022.
For a Microsoft Visual Basic 6.0 Version of this Article, See
247412.
In this task
Summary
Overview Techniques
Use Automation to Transfer Data Cell by Cell Use Automation to Transfer an Array of Data to a Range on a Worksheet Use Automation to Transfer an ADO Recordset to a Worksheet Range Use Automation to Create a QueryTable Object on a Worksheet Use the Windows Clipboard Create a Delimited Text File That Excel Can Parse Into Rows and Column Transfer Data To a Worksheet by Using ADO.NET TRANSFER XML DATA (Excel 2002 and 2003) Create The Complete Sample Visual C # .NET PROJECT References
SUMMARYThis step-by-step article describes several methods for transferring data to Excel 2002 from a 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.
Back to the top
OverviewThe 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 transfert 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 users:
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 Column.This Article Provides a Discussion and a Code Sample for Each of these Techniques. THE
Create The Complete Sample Visual C # .NET Project Section, Later in this Article, Demonstrates How to create a Visual C # .NET Program That Executes Each Technique.
Back to the top
Techniques
Use Automation to Transfer Data Cell By Cellwith Automation, you can transfer data to a worksheet One Cell At A Time:
// 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 cells in the first worksheet in The New Workbook.
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 = "Last Name";
m_objrange = m_objsheet.get_range ("b1", m_objopt);
m_objrange.value = "first 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";
// Apply Bold To Cells A1: B1.
m_objrange = m_objsheet.get_range ("a1", "b1");
m_objfont = m_objrange.font;
m_objfont.bold = true;
// save the workbook and quit 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_objbook.close (false, m_objopt, m_objopt);
m_Objexcel.quit ();
Transferring data cell by cell is an acceptable approach if you have a small quantity of data. You have the flexibility to put data anywhere in the workbook and you can format the cells conditionally at run-time. However, it is not a good idea to Use this approach if you have limited to an an excel workbook. Each
Range object that you acquire at run-time results in an interface request that means data transfers more slowly. Additionally, Microsoft Windows 95, Microsoft Windows 98, and Microsoft Windows Millennium Edition (Me) have a 64 kilobyte (KB) limitation on interface requests . If you have more than 64 KB of interface requests, the Automation server (Excel) may stop responding, or you may receive error messages that indicate low memory. For additional information, click the article number below to view the article in the Microsoft Knowledge Base: 216400 PRB:. Cross-Process COM Automation Can Hang Client Application on Win95 / 98 Again, transferring data cell by cell is acceptable only for small quantities of data If you must transfer large data sets to Excel, consider using one of the other Approaches That Are Discussed in This Article To Transfer Data IN BULK.
For Additional Information, And for An Example of Automating Excel with Visual C # .NET, CLICK The Article Number Below To View The Article In The Microsoft Knowledge Base:
302084 HOWTO: Automate Microsoft Excel from Microsoft Visual C # .NET
Back to the top
Use Automation To Transfer an Array of Data TO A Range ON AN Array Of Data To a Range of Multiple Cells At One Time:
// 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.worksheets;
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.value = objheaders;
m_objfont = m_objrange.font;
m_objfont.bold = true;
// Create An Array With 3 Column and 100 Rows and Add It To
// The Worksheet 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 = 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;
// save the workbook and quit 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 transfer your data by using an array instead of cell by cell, you can realize an enormous performance gain with a large quantity of data Consider the following lines from the aforementioned code that 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 Repesents Two Interface Requests: One for the
Range Object That
Range Method Returns, And Another for Thae
Range Object That
Resize Method Returns. In Contrast, Transferring The Data Cell by Cell Requires Requests for 300 Interfaces To
.
For Additional Information About Using ARRAYS TO GET AND SET VALUES IN RANGES with EXCEL Automation, Click The article Number Below to View The Article In The Microsoft Knowledge Base:
302096 HOWTO: Automate Excel With Visual C # .NET TO FILL O OBTAIN DATA IN A Range Using Arrays
Back to the top
Use Automation to Transfer An Ado RecordSet To a Worksheet Rangethe Object Models for Excel 2000, Excel 2002 and Excel 2003 Provide THE
CopyFromRecordset method for transferring an ADO recordset to a range on a worksheet. The following code illustrates how to automate Excel to transfer the contents of the Orders table in the Northwind sample database by using the
CopyFromRecordset Method:
// CREATE A Recordset from all the records in the order of the order.
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 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.worksheets;
m_objsheet = (Excel._Worksheet) (m_objsheets.get_item (1));
// Get The Fields Collection from The RecordSet and Dermine
// The number of inf (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 { 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 Recordset 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 quit 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_objbook.close (false, m_objopt, m_objopt); m_Objexcel.quit (); // Close The RecordSet and Connection. Objrs.close (); Objconn.close (); Note: CopyFromRecordset Works ONLY with ADO Recordset Objects. You canNot USE THE DataSet That You create by using ado.net with the CopyFromRecordset Method. Several Examples in The Sections That Follow Demonstrate How To Transfer Data To Excel with add. Back to the top Use automation to create a querytable object on a Worksheeta QueryTable Object Represents a Table That Is Built from Data That Is Returned from an External Data Source. When You Automate Excel, You Can Create A QueryTable by providing a connection string to an OLE DB or an ODBC data source and a SQL string. Excel generates the recordset and inserts the recordset into the worksheet at the location that you specify.QueryTable objects offer the following advantages over the CopyFromRecordset Method: Excel handles the creation of the recordset and its placement on the worksheet. You can save the query with the QueryTable object and refresh it later to obtain an updated recordset. When a new QueryTable is added to your worksheet, you can specify that data that already exists in cells on the worksheet be shifted to handle the new data (for more information, see the RefreshStyle property) .The following code demonstrates how to automate Excel 2000, Excel 2002, or Excel 2003 to create a new QueryTable in an Excel Worksheet by Using Data from The Northwind Sample Database: // 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 QueryTable That Starts At Cell 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; data source =" M_StrnorthWind ";", m_objrange, "select * from orderers"); m_objqrytable.refreshStyle = Excel.xlcellInsertionMode.xlinsertireRower; m_objqrytable.refresh (false); // save the workbook and quit 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_objbook.close (false, m_objopt, m_objopt); m_Objexcel.quit (); Back to the top Use the Windows ClipboardYou can use the Windows Clipboard to transfer data to a worksheet. To paste data into multiple cells on a worksheet, you can copy a string in which columns are delimited by TAB characters, and rows are delimited by carriage returns. The following Code Illustrates How Visual C # .NET CAN Use The Windows Clipboard to Transfer Data To Excel: // Copy a string to the windows clipboard. String sdata = "firstname / tlastname / tbpterdate / r / n" "BILL / TBROWN / T2 / 5/85 / R / N" "Joe / TTHOMAS / T1 / 1/91"; System.windows.Forms.clipboard.SetDataObject (SDATA); // 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)); // Paste the data starting at cell 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); // save the workbook and quit 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 (); Back to the top Create a Delimited Text File that Excel Can Parse into Rows and ColumnsExcel can open tab- or comma-delimited files and correctly parse the data into cells. You can use this feature when you want to transfer a large quantity of data to a worksheet while using little, if any, Automation. This may be a good approach for a client-server program because the text file can be generated server-side. You can then open the text file at the client, using Automation where it is appropriate.The following Code Illustrates How To Generate A Tab-Delimited Text File From Data That Is 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 (); // Execute a command to retrieve all 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 Object to Write // The Recordset Contents 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" Objreader.getname (4) "/ t" Objreader.getName (5)); // Write the first Six Column in The RecordSet To a Text File AS // tab-delimited. While (ObjReader.Read ()) { For (int i = 0; i <= 5; i ) { IF (! ObjReader.Indbnull (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 (); // Write the Buffered Data to the filestream. // Close the filestream. fs.close (); // Close The Reader and the Connection. Objreader.close (); Objconn.close (); The AFOREMATION. HOWEVER, INWOWEVER, You CAN Use Automation To Open The Text File And Save The File in The Excel Workbook Format, Similar To this: // Open the text file 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_objbook = m_Objexcel.activeWorkbook; // Save the text file in The Typical Workbook Format and Quit Excel. m_objbook.saveas (m_strsamplefolder "book6.xls", Excel.xlfileFormat.xlworkbookbooknormal, 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 (); Back to the top TRANSFER DATA TO A WORKSHEET by Using Ado.Netyou CAN Use The Microsoft Jet Ole DB Provider To Add Records To a Table in An Existing Excel Workbook. A Table in Excel Is Merely A Range of Cells; The Range May Have A Defined Name. Typically, The First Row of The Range Contains The Headers (OR Field Names), And All Later 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: // 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 the connection. Objconn.close (); WHEN You Add Records with ado.net as shown in this Example, The Formatting in The Row Borrows The Format from The Row Before IT. For additional information about using ADO.NET, click the article numbers below to view the articles in the Microsoft Knowledge Base: 306636 HOW TO: Connect to a Database and Run a Command by Using ADO.NET and Visual C # .NET 314145 How to: populate a dataset object from a database by using visual c # .net 307587 HOW TO: Update a Database from a DataSet Object by Using Visual C # .NET For additional information about using the Jet OLEDB provider with Excel data sources, click the article numbers below to view the articles in the Microsoft Knowledge Base: 316934 How To: Use ado.net to retrieve and modify records in an Excel Workbook with Visual Basic .NET 278973 Sample: Excelado Demonstrates How To Use ado to read and write data in Excel Workbooks 257819 HOWTO: USE ADO with Excel Data from visual Basic or VBA Back to the top Transfer XML Data (Excel 2002 and Excel 2003) Excel 2002 and 2003 Can open Any Xml File That Is Well-formed. You can open XML Files Directly by Using T Open Command on The File menu, or programatically by using either Open OR OpenXML Methods of The Workbooks collection. If you create XML Files for Use in Excel, You Can Also Create Style Sheets To Format The Data. For Additional Information About Using XML with Excel 2002, Click The article Numbers Below to View The Articles in the Microsoft Knowledge Base: 307029 How To Microsoft Excel 2002 by USING Visual C # .NET 288215 INFO: Microsoft Excel 2002 and XML Back to the top Create the Complete Sample Visual C # .NET Project Create a New Folder Named C: / Exceldata. The Sample Program Will Store Excel Workbooks in this folder. Create a new workbook for the sample to write to: Start a new workbook in Excel On Sheet1 of the new workbook, type FirstName in cell A1 and LastName in cell B1 Select A1:.... B1 On the Insert menu, point to Name, and then click Define Type the name MyTable and then click OK Save the workbook as C:.... /Exceldata/Book7.xls Quit Excel Start Visual Studio .NET On the File menu, point to New, and then click Project Under Visual C # Projects, select Windows Application By default.. , Form1 is created Add a reference to the Excel object library and the ADODB primary interop assembly to do this, follow these steps:... On the Project menu, click Add Reference On the NET tab, locate ADODB, and then click Select. On the COM tab, locate Microsoft Excel 10.0 Object Library or Microsoft Excel 11.0 Object Library, and then click Select.NOTE: If you are using Microsoft Excel install the Microsoft 2002 and you have not already done so, Microsoft recommends that you download and then Office XP Primary Interop Assemblies (PIAS). For additional information about Office XP PIAs, click the article number below to view the article in the Microsoft Knowledge Base: 328912 INFO: Microsoft Office XP PIAs Are Available for Download In the Add References dialog box, click OK to accept your selections Add a Combo Box control. And A Button Control to Form1. Add Event Handlers for the Form Load Event and The Click Events of The Button Control: In design view for Form1.cs, double-click Form1. The handler for the Form's Load event is created and appears in Form1.cs. On the View menu, click Designer to switch to design view. Double-click Button1. The handler for The Button's Click Event IS CREATED and APPEARS IN FORM1.CS. in form1.cs, Replace The Following Code: Private Void Form1_Load (Object Sender, System.EventArgs E) { } Private void Button1_Click (Object Sender, System.Eventargs E) { } WITH: // Excel Object References. 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; // frequenty-useed variable for optional arguments. Private object m_objopt = system.reflection.Missing.Value; // paths used by The Sample Code for Accessing and Storing Data. Private object m_strsamplefolder = "c: // ExcelData //"; Private string m_strnorthwind = "c: // program files // microsoft office // office10 // samples // northwind.mdb"; Private Void Form1_Load (Object Sender, 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 on A Worksheet", "Use Automation to Transfer An Ado Recordset to a Worksheet Range", "Use Automation to Create a QueryTable on A Worksheet", "Use the clipboard", "CREATE A Delimited Text File That Excel Can Parse Into Rows and Column", "Transfer Data TO A Worksheet Using ADO.NET"}); ComboBox1.SelectedIndex = 0; Button1.text = "GO!"; } Private void Button1_Click (Object Sender, System.Eventargs E) { Switch (ComboBoX1.Selected ") { Case 0: Automation_cellbycell (); Break; Case 1: Automation_useArray (); Break; Case 2: Automation_AdorecordSet (); Break; Case 3: Automation_QueryTable (); BREAK; Case 4: USE_CLIPBOARD (); BREAK; Case 5: CREATE_TEXTFILE (); Case 6: USE_ADONET (); BREAK; } // Clean-Up m_objfont = NULL; m_objrange = NULL; m_objsheet = NULL; m_objsheets = NULL; m_objbooks = null; m_objbook = 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 cells of the first worksheet in the new workbook. 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.set_Value (m_objopt, "limited name"); m_objrange = m_objsheet.get_range ("b1", m_objopt); m_objrange.set_value (m_objopt, "first name"); m_objrange = m_objsheet.get_range ("a2", m_objopt); m_objrange.set_value (M_Objopt, "DOE"); 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_objfont.bold = true; // save the workbook and quit 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_userray () { // 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.worksheets; 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_objfont.bold = true; // Create An Array With 3 Column and 100 Rows and Add It To // The Worksheet 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 = 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.set_value (m_objopt, "objData"); // save the workbook and quit 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 from all the records in the order of the order. 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 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.worksheets; m_objsheet = (Excel._Worksheet) (m_objsheets.get_item (1)); // Get The Fields Collection from The RecordSet and Dermine // the number of fields. 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 { 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 Recordset 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 quit 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 The 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 QueryTable That Starts At Cell 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; data source =" M_StrnorthWind ";", m_objrange, "select * from orderers"); m_objqrytable.refreshStyle = Excel.xlcellInsertionMode.xlinsertireRower; m_objqrytable.refresh (false); // save the workbook and quit 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 us_clipboard () { // Copy A String to the clipboard. String sdata = "firstname / tlastname / tbpterdate / r / n" "BILL / TBROWN / T2 / 5/85 / R / N" "Joe / TTHOMAS / T1 / 1/91"; System.windows.Forms.clipboard.SetDataObject (SDATA); // 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)); // Paste the data starting at cell 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); // save the workbook and quit 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 (); // Execute a command to retrieve all 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 Object to Write // The Recordset Contents 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" objreader.getname (4) "/ t" Objreader.getname (5)); // Write The First Six Column in The RecordSet To a Text File AS // tab-delimited. While (ObjReader.Read ()) { For (int i = 0; i <= 5; i ) { IF (! ObjReader.Indbnull (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 (); // Write the Buffered Data to the filestream. // Close 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 the text file and save it in // Excel Workbook Format. // Open the text file 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 The Typical Workbook Format and Quit Excel. m_objbook.saveas (m_strsamplefolder "book6.xls", Excel.xlfileFormat.xlworkbookbooknormal, 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 us_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 the connection. Objconn.close (); } } // End Class } // end namespace NOTE: If you did not install Office to the default folder (C: / Program Files / Microsoft Office), modify the m_strNorthwind constant in the code sample to match your installation path for Northwind.mdb Add the following to the Using directives in Form1. .cs: using system.reflection; Using system.Runtime.InteropServices; Using Excel = Microsoft.Office.Interop.Excel; Press f5 to build and rain the sample. Back to the top Referencesfor More Information, Visit The Following Microsoft Web Site: Microsoft Office Developments with Visual Studio Back to the top The Information in this Article Applies TO: Microsoft Excel 2002 Microsoft Visual C # .NET (2002) Microsoft AD (Included with the .NET Framework) 1.0 Last Reviewed: 12/15/2003 (5.0) Keywords: Kbautomation KbhowTomaster KB306023 KBAUDDEVELOPER
