This task content
•summary
• Overview • Method
• Transport data by "Automation" • Use "Automation" to transfer data arrays to the worksheet • Use "Automation" to transfer the ADO recordset to a worksheet area • Create it on a worksheet using "Automation" QueryTable object • Use a Windows clipboard • Create a text file that can be analyzed by Excel analysis as rows and columns • Transfer data to a worksheet using ADO.NET • Transfer XML Data (Excel 2002 and 2003) • Create a complete example Visual C # .NET project • Reference
summary
This article gradually introduces a variety of methods from the Visual C # .NET program to Excel 2002 to transmit data. This article also provides the advantages and disadvantages of each method so you can choose the solution that best suits your situation. Back to top
Overview
The technology that is most common to transfer data to the Excel workbook is automated. With "automation", you can call methods and properties specific to Excel task. "Automation" provides you with the greatest flexibility of specifying data in the workbook, formatting the workbook, and performing the biggest flexibility of various settings at runtime. With "automation", you can use multiple techniques to transfer data:
• Transport data by cell • Transfer data in the array to a region consisting of a cell. • Use the CopyFromRecordset method to transfer data in the ADO recordset to the cell area. • Create a queryTable object on the Excel worksheet that contains the results of the query on the ODBC or OLEDB data source. • Transfer data to the clipboard and paste the clipboard content into the Excel worksheet.
It is also possible to use a variety of methods that are not necessarily required to use "automation" to transmit data to Excel. If you are running a server-side program, this can be a good way to remove bulk data from the client. To transfer data without using Automation, you can use the following methods:
• Transfer data to a tab-divided or comma-separated text file, and Excel can analyze the text file into a unit on a worksheet. • Transfer data to a worksheet using ADO.NET. • Transfer XML data to Excel (only 2002 and 2003) to provide data that can be formatted and arranged as rows and columns.
This article provides discussion and code examples for each of these technologies. This article
Create a complete example Visual C # .NET project
Part (later part of this article), demonstrate how to create a Visual C # .NET program that performs each technology. Back to top
method
Transfer data by using "Automation" one by one
With "automation", you can transfer data to worksheets one by one unit:
// 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 ();
If you have a small amount of data, transferring data one by one unit is a method of acceptable. You can flexibly put the data anywhere in the workbook and can format the cells in accordance with the conditions based on the conditions. However, if you have a lot of data that need to be transferred to the Excel workbook, use this method is not a good idea. Each Range object you get at runtime will generate an interface request, which means that the data transfer speed becomes slower. In addition, both Microsoft Windows 95, Microsoft Windows Millennium Edition (ME) requests 64 KB restrictions on the interface. If you have 64 KB of interface requests, the Automation Server (Excel) may stop responding, or you may receive an error message indicating that insufficient memory. For additional information, click the article number below to see the article in the Microsoft Knowledge Base:
216400
PRB: Cross-Process CoM Automation Can Hang Client Application On Win95 / 98
What needs to be emphasized again is that the transmitted data by cell is only acceptable for a small amount of data. If you have to transfer big data sets to the Excel, you should consider transferring data to one of the other methods discussed herein. For example, how to automatically run Excel using Visual C # .NET, click the article number below to see the article in the Microsoft Knowledge Base:
302084
HOWTO: Make Microsoft Excel automatically in Microsoft Visual C # .NET
Back to top
Use "Automation" to transfer data arrays to the area on the worksheet
You can transfer data arrays to an area composed of multiple cells at once:
// Start a new workbook in excel.
M_Objexcel = new excel.application ();
m_objbooks = (Excel.Workbooks) m_objexcel.workbooks;
m_objbook = (M_ObJBooks.Add (m_objopt)); m_objsheets = (Excel.Sheets) m_objbook.worksheet;
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 use an array instead of a cell, the transmission performance is greatly enhanced when transmitting a large amount of data. Consider the following lines in the front code, these banks are transferred to 300 cells in the worksheet:
ObjRange = objsheet.get_range ("a2", m_objopt);
ObjRange = Objrange.get_resize (100, 3);
Objrange.value = Objdata;
These codes represent two interface requests: a request is a RANGE object returned for the Range method, and the other request is a RANGE object returned for the Resize method. In contrast, transmitted data by cells, but you need to send 300 interface requests for the RANGE object. As long as it is possible, you can benefit from bulk to data and reduce the number of interface requests issued. For additional information about EXCEL automation and using an array acquisition and setting area, click the article number below to view the article in Microsoft Knowledge Base: 302096
HOWTO: In Visual C # .NET, make Excel automatically run to use array to populate or get data in a region.
Back to top
Use "Automation" to transfer the ADO recordset to the worksheet area
The object model of Excel 2000, Excel 2002 and Excel 2003 provides a CopyFromRecordset method for transmitting ADO records to the area on the worksheet. The following code illustrates how to use the CopyFromRecordset method to automatically run Excel to transfer the contents of the "Order" table in the Northwind sample database:
// 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.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 can only be used with the ADO Recordset object. DataSet created using ADO.NET cannot be used with your CopyFromRecordset method. Multiple examples in the following sections demonstrate how to use ADO.NET to transfer data to Excel. Back to top Use "Automation" to create a QueryTable object on the worksheet The QueryTable object represents a table that is generated with data returned from the external data source. When you automatically run Excel, you can create queryTable by providing a connection string and SQL string that points to OLE DB or ODBC data sources. Excel will generate record sets and insert the recordset into the works you specify. QueryTable object provides the following advantages over the CopyFromRecordset method: • The Excel handles the creation of the record set and placed it into the worksheet. • You can use the queryTable object to save the query and refresh it later to get the updated recordset. • When adding new queryTable to a worksheet, you can specify that the data displacement already existing in the unit on the worksheet is to process new data (for more information, see the RefreshStyle attribute). The following code demonstrates how to automatically run Excel 2000, Excel 2002, or Excel 2003 so that data in the Northwind sample database creates new queryTable in the Excel worksheet: // Start a new workbook in excel. M_Objexcel = new excel.application (); m_objBooks = (Excel.Workbooks) m_objexcel.workbooks; m_objbook = (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 top Use a Windows clipboard You can use the Windows clipboard to transfer data to the worksheet. To paste the data into a plurality of cells on the worksheet, you can copy a string with the following format: In this string, the list is separated by the cargo manner. The following code illustrates how Visual C # .NET uses 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 top Create text files that can be analyzed by Excel as rows and columns Excel can open the files separated by a tab or comma and analyze the data correctly into a cell. This feature can be used when you want to transfer a lot of data to the worksheet. This may be a good way for client-server programs because text files can be generated on server-side. Then, you can open the text file as you need to use "Automation" as needed. The following code illustrates how to generate a table-separated text file from the 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 (); // 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 above code is not automated. However, if you like, you can use Automation as follows to open the text file and save this file in an 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_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 top Transfer data to worksheets using ADO.NET You can use the Microsoft Jet OLE DB provider to add records to the tables in the existing Excel workbook. The table in Excel is only a region consisting of a cell; the region may have a predetermined name. Typically, the first row of the region contains a title (or field name), and all future rows in this area contain records. The following code adds two new records to the tables in Book7.xls. In this case, the table 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 use the ADO.NET to add a record as shown in this example, the format in the workbook will be held. Each record added to the row will inherit the format of the row it in front. For additional information about using ADO.NET, click the article number below to see the article in the Microsoft Knowledge Base: 306636 How to: Connect to the database using ADO.NET and Visual C # .NET and run commands 314145 How to: Use Visual C # .NET from Database Package DataSet Objects 307587 HOW TO: Update the database from the dataset object using Visual C # .NET For additional information about how to use the Jet OLEDB provider with the Excel data source, click the article number below to view the article in the Microsoft Knowledge Base. 316934 How to: Use ADO.NET to retrieve and modify records in the Excel workbook in Visual Basic .NET 278973 Sample: Excelado Demonstrates How To Use ado to read and write data in Excel Workbooks 257819 HOWTO: Using ADO in Visual Basic or VBA to handle Excel data Back to top Transfer XML data (Excel 2002 and Excel 2003) Excel 2002 and 2003 can open any XML files in the format. You can open the XML file directly on the open command on the File menu, or you can use the Open or OpenXML method of the Workbooks collection to open an XML file. If you create an XML file for use in Excel, you can also create a style sheet to set the data. For additional information about how to use XML with Excel 2002, click the article number below to see the article in the Microsoft Knowledge Base: 307029 How to: Transport XML data to Microsoft Excel 2002 using Visual C # .NET 288215 INFO: Microsoft Excel 2002 and XML Back to top Create a complete example Visual C # .NET project 1. Create a new folder called C: / Exceldata. The sample program will store the Excel workbook in this folder. 2. Create a new workbook to give the data to the data: a. Start a new workbook in Excel. b. On the SHEET1 of the new workbook, type firstname in cell A1, type LastName in cell B1. c. Select A1: B1. d. On the Insert menu, point to the name, and then click Definition. Type Name MyTable, and then click OK. e. Save the workbook as C: /exceldata/book7.xls. f. Exit Excel. 3. Start Visual Studio .NET. On the File menu, point to New, and then click Project. Under the Visual C # item, select the Windows application. Form1.4 will be created by default. Add a reference to the Excel object library and AdoDB master interface. To do this, follow these steps: a. On the project menu, click Add Reference. b. On the NET tab, find AdoDB, and then click Select. c. On the COM tab, find the Microsoft Excel 10.0 object library or Microsoft Excel 11.0 object library, and then click Select. Note: If you are using Microsoft Excel 2002, and have not downloaded and install Microsoft Office XP master Interop assembly, Microsoft recommends you to download and install the Microsoft Office XP Main InteroP assembly (PIA). For additional information about Office XP PIA, click the article number below to see the article in the Microsoft Knowledge Base: 328912 INFO: Microsoft Office XP PIA is available for download d. In the Add Quotation dialog box, click OK to accept your choice. 5. Add a COMBO BOX control to Form1 and a Button control. 6. Add event handler to the Click event of the LOAD event and the Button control: a. In the design view of Form1.cs, double-click Form1. The event handler of the LOAD event of the form is created, which appears in Form1.cs. b. On the View menu, click the Designer to switch to the design view. C. Double click on Button1. The handler of the click Click event will be created, which appears in Form1.cs. 7. In Form1.cs, the following code: Private Void Form1_Load (Object Sender, System.EventArgs E) { } Private void Button1_Click (Object Sender, System.Eventargs E) { } Replace 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 do not install Office to the Default Folder (C: / Program Files / Microsoft Office, modify the m_strnorthwind constant in the code example to match the installation path of Northwind.mdb. 8. Add the following code to the USING instruction in Form1.cs: use system.reflection; Using system.Runtime.InteropServices; Using Excel = Microsoft.Office.Interop.Excel; 9. Press F5 to generate and run the example. Back to top reference For more information, please visit the Microsoft Web site below: Microsoft Office Development with Visual Studio (Microsoft Office development using Visual Studio) Back to top The information in this article applies to: • Microsoft Excel 2002 Standard Edition • Microsoft Visual C # .NET 2002 Standard Edition • Microsoft ADO.NET 1.0 Keywords: kbautomation kbhowtomaster kb306023
