For Microsoft Visual Basic .NET versions of this article, see
306022.
For the Microsoft Visual Basic 6.0 version of this article, see
247412.
This task content
summary
Overview
Using "Automation" one-by-cell transmission data Use "Automation" to transfer data arrays to the worksheet using Automation to transfer the ADO recordset to the workpiece area Use "Automation" to create a QueryTable object on a worksheet. WINDOWS The clipboard creates a text file that can be analyzed by Excel analysis as a row and columns using ADO.NET to transfer data to worksheet transfer XML data (Excel 2002 and 2003) Create a complete example Visual C # .NET project reference
SUMMARY This article has gradually introduced a variety of ways to transmit data from the Visual C # .NET program to Excel 2002. 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 transmit data to an Excel workbook is
automation. 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:
Transferring data by unit-by-cell transmission to a region 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, which contains the results of the query on the ODBC or OLEDB data source. Transfer the 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 tab-divided or comma-separated text files, 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 full sample Visual C # .NET project section (later in this article), demonstrate how to create a Visual C # .NET program that performs each technology.
Back to top
method
Use "Automation" to transfer data by "Automation" by "Automation", and 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. Every one you get at runtime
The Range object generates 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 Need to emphasize that transmitting 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 in Microsoft Visual C # .NET
Back to top
Use "Automation" to transfer data arrays to the worksheet. The data array can be transmitted at once in order to the area consisting of multiple cells:
// 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 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 code represents two interface requests: a request is for
Range method returned
Range object, another request is
Resize method returned
Range object. In contrast, transmitting data by cell is required
The RANGE object issues 300 interface requests. 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 the value of the value in an array acquisition and setting area, please click on the article number in the 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 Excel 2000, Excel 2002 and Excel 2003 object models.
CopyFromRecordset method, used to transfer ADO records to the area on the worksheet. The following code shows how to use
The CopyFromRecordset method allows Excel to run 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 with ADO The Recordset object is used together. Created using ADO.NET DataSet can't be CopyFromRecordset method is used together. 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 a connection string and SQL string that point to OLE DB or ODBC data sources. QueryTable. Excel will generate record sets and insert the recordset into the works you specify. QueryTable object provides the following best The advantages of 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 existing in the unit on the worksheet has been displaced to process new data (for more information, see the RefreshStyle property). The following code demonstrates how to automatically run Excel 2000, Excel 2002, or Excel 2003 so that data in the Northwind sample database is created in the Excel worksheet. 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_objbook.close (false, m_objopt, m_objopt); m_Objexcel.quit (); Back to top Using the Windows clipboard 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.worksheet; 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 a text file Excel with a tab and column of Excel analysis as a row and column can open a file from a tab or comma and analyze the data to 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.xlWorkbookNormal, 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 ADO.NET to transfer data to a worksheet. You can add a record to the table in the existing Excel workbook using the Microsoft Jet OLE DB provider. Excel Table is just a region consisting of cells; 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 the command 314145 HOW TO: Visual C # .NET Pack DataSet object from the database 307587 How to: Using Visual C # .NET Update Database About Database About How to Use Additional information to use the Jet OLEDB provider with Excel data sources, click the article number below to view the article in the Microsoft Knowledge Base. 316934 How to: Use ADO.NET in Visual Basic .NET to retrieve and modify records in the Excel workbook 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 use it File menu Open the command directly to the XML file or use Workbooks collection Open or The OpenXML method opens an XML file programmatically. 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: Use Visual C # .NET to transmit XML data to Microsoft Excel 2002 288215 INFO: Microsoft Excel 2002 and XML Back to top Create a complete example Visual C # .NET project Create a new folder called C: / Exceldata. The sample program will store the Excel workbook in this folder. Create a new workbook to write data to the present: Start a new workbook in Excel. On the new workbook's Sheet1, type firstname is typed in cell A1, and type LastName in cell B1. Select A1: B1. On the Insert menu, point to the name, then click Definition. Type Name MyTable, and then click OK. Save the workbook as C: /exceldata/book7.xls. Exit Excel. 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 is created by default. Add a reference to the Excel object library and AdoDB master interface. To do this, follow these steps: On the project menu, click Add Reference. On the Net tab, find Adodb, then click Select. 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 view the article in the Microsoft Knowledge Base: 328912 Info: Microsoft Office XP PIA is available in Add Reference dialog box, click OK to accept you select. Add a COMBO BOX control to Form1 and a Button control. Add an event handler for the Click event of the LOAD event and the Button control: Double-click Form1 in the design view of Form1.cs. The event handler of the LOAD event of the form is created, which appears in Form1.cs. On the View menu, click the Designer to switch to the design view. Double click on Button1. The handler of the click Click event will be created, which appears in Form1.cs. 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 Determine // 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 // 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. Add the following code to the USING instruction in Form1.cs: use system.reflection; Using system.Runtime.InteropServices; Using Excel = Microsoft.Office.Interop.Excel; Press F5 to generate and run this example. Back to top Refer to 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 Microsoft Visual C # .NET (2002) Microsoft AD (Included with the .NET Framework) 1.0 Recent Updated: 2004-3-29 (5.0) Keywords: kbautomation kbhowtomaster KB306023 KBAUDDEVELOPER
