Fuxing Published on 2004-8-25 10:05:00
Author: wyhw Excel is a Microsoft office automation software suite, he was mainly used to process electronic form. Excel is welcomed by many users with powerful, friendly interface. In the office, it is because of so many advantages of Excel, many important data are often stored in the form of an Excel spreadsheet. This brings a problem to the programmer, although the Excel function is relatively powerful, but not a database, the data in the program is handled more than the data in the Excel table in the program. So how do you read the data in the Excel table with Visual C #? When programming with Delphi before, for different users, they are different for printing, if they want to make print functions in the program to each user, imagine that programming is very complicated. At this time, Excel thought that due to the power of the Excel form, it was installed in almost every machine. If the result of the program processing is placed in the Excel form, each user can customize according to their own needs. Your own print. This not only makes the program design, but also meets the requirements of many users, more practical. So how to call Excel with Visual C #, how do you store the data into an Excel form? This article explores the solution of the above problems. One. Program Design and Operation Environment (1). Microsoft Window 2000 Server Edition (2) .. Net Framework SDK Beta 2 (3) .microsoft Data Access Component 2.6 or later version (MDAC2.6) (4) .Office 2000 Kit 2. Visual C # reads the data in the Excel table: This section will introduce the data in the Excel table through a program, and display the data as a DataGrid. (1). How to read data: Actually read the data in the Excel form and read the data in the database is very similar, because in some extent, an Excel form can be seen as a piece of data. The main difference between the two is different in the data engines used.
In the program of this article, the read Excel table data is implemented by the following code: // Create a data link strCon = "provike = microsoft.jet.Oledb.4.0; data source = c: //sample.xls; Extended Properties = Excel 8.0 "; OleDbConnection myConn = new OleDbConnection (strCon); string strCom =" SELECT * FROM [Sheet1 $] "; myConn.Open (); file: // open data link, get a set of data OleDbDataAdapter myCommand = New OLEDBDataAdapter (STRCOM, MyConn); File: // Create a DataSet Object MyDataSet = New DataSet (); file: // Get your own DataSet object myCommand.Fill (MyDataSet, "[Sheet1 $]); file: // Close this data link MyConn.close (); how to read the data in the Excel table is actually in real distinctive differences in the data in the database. Note: The "Sample.xls" file under the root directory of the C drive here is read.
(2). Use DataGrid to display the obtained data set: After getting a DataSet object, only the following two lines of code can be displayed by the data set: DataGrid1.Datamember = "[Sheet1 $]"; DataGrid1 .Datasource = mydataset; (3). Read the Excel form with Visual C # and displayed with DataGrid and run the interface to run the program: Master the above two points, water to the canal achievements You can get the following code: using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.OleDb; public class Form1: Form {private Button button1; private System.Data .DataSet myDataSet; private DataGrid DataGrid1; private System.ComponentModel.Container components = null; public Form1 () {file: // initialize the various components InitializeComponent form (); file: // open the data link, the data sets obtained GetConnect ();} File: // Remove the resources used in the program protected {if (disponents! = null) {components.dispose ();}} } private void getConnect () {file: // Create a data link string strcon = "provider = microsoft.jet.Oledb.4.0; data source = C: // Sam PLE.XLS; Extended Properties = Excel 8.0 "; OLEDBConnection myconn = new oledbconnection (strcon); string strcom =" select * from [sheet1 $] "; myconn.open (); file: // Open data link to get a data Set OLEDBDataAdApter mycommand = new oledbdataadapter (strcom, myconn); file: // Create a DataSet object myDataSet = new dataset (); file: // get your own DataSet object myCommand.Fill (MyDataSet, "[Sheet1 $]); File: // Close this data link myconn.close ();} private void initializecomponent () {dataGrid1 = new datagrid (); button1 = new button (); suspendlayout ();
DataGrid1.name = "DataGrid1"; DataGrid1.size = new system.drawing.size (400, 200); button1.location = new system.drawing.point (124, 240); button1.name = "button1"; button1. TabINDEX = 1; button1.text = "Read data"; button1.size = new system.drawing.size (84, 24); button1.click = new system.eventhandler (this.button1_click); this.autoscalebasesize = new System.drawing.size (6, 14); this.clientsize = new system.drawing.size (400, 280); this.controls.add (button1); this.controls.add (datagrid1); this.name = " Form1 "; this.text =" reads the data in the ExcLe table and displayed with DataGrid! "; This.ResumeLayout (false);} private void button1_click (object sender, system.eventargs e) {dataGrid1.datamember =" [ Sheet1 $] "; DataGrid1.datasource = MyDataSet;} static void main () {Application.run (new form1 ());}}} The picture below is after the program compile, the results: http://www.yesky.com/ 20020313 / JT-2002-3-13-Image001.jpg Figure 01: Read "C: /Sample.xls" running interface (4) with Visual C # (4). Summary: The above is just read "Sheet1" in the Excel form Data in other "sheet" In the middle, you can refer to the program in "Sheet1", just make a change, for example, to read the content in "Sheet2", just change "Sheet1 $" in the "Read.cs" program. "Sheet2 $" is OK. three. Visual C # Calls the Excel form and stores data in the Excel form: call the Excel form in Visual C #, not as easy as reading the data in the Excel table, because in Visual C #, call the Excel form to use Excel's COM component.
If you install the Office suite in the "C" disk, then in "C: / Program Files / Microsoft Office / Office" can find this COM component "Excel9.olb", in "How Visual C # use Active X Components", These COM components are non-managed code. To use these non-managed COM components in Visual C #, they must convert them into the class library of managed code. So before the Excel form is called with Visual C #, you must complete the conversion of non-managed code from the COM component to the class library of the category library. (1). Non-managed code COM components are converted into managed code class libraries: First copy the COM component "Excel9.OLB" to the root directory of the C disk, then enter the following command: TLBIMP Excel9.olb in C The root directory produces three DLL files: "Excel.dll", "Office.dll", "vbide.dll". After generating three files, this conversion is completed successfully. In the following program, you can use this three-class libraries to write and the various operations related to the Excel form. (2) .visual C # Open Excel form: Define a namespace "Excel" in "Excel.dll", encapsulate a class "Application" in the difference namespace, this class and the Excel form are very important Relationship, in Visual C #, only the following three lines of code can complete the work of opening the Excel form, as follows: Excel.Application Excel = new Excel.Application (); Excel.Application.Workbooks.add (true); Excel .Visible = true; But at this time the Excel form is an empty form, there is no content, here is how to enter data into the Excel form. (3) Enter the data in the Excel form: In the "Excel" in the namespace, a class "cell" is also defined, and the class represents one of the EXCEL forms. By giving the difference "Cell" assignment, the corresponding data is entered into the Excel form, the following code function is to open the Excel table and enter some data to the table.
Excel.Application Excel = New Excel.Application (); Excel.Application.Workbooks.add (true); Excel.cells [1, 1] = "First Line 1"; Excel.cells [1, 2] = "First line second column"; Excel.cells [2, 1] = "second line first column"; Excel.cells [2, 2] = "second line second column"; Excel.cells [3 1] = "Third line first column"; Excel.cells [3, 2] = "Third line second column"; Excel.visible = true; (4). Visual C # calls the Excel form, and Excel.cs stored in the Excel form: Understand the above knowledge to get the program code to complete the above functions, as follows: USING System; use system.drawing; using system.collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; public class Form1: Form {private Button button1; private System.ComponentModel.Container components = null; public Form1 () {file : // Initialize the various components in the form initializeComponent (),} file: // Clear the individual resources used in the program Protected {if (disposing) {if (component! = Null) {components. Dispose ();}} Base.dispose (Disposing);} private vidinitis; ) {Button1 = new button (); suspendlayout (); button1.Location = new system.drawing.point (32, 72); button1.name = "button1"; button1.size = new system.drawing.size (100, 30); Button1.TabINDEX = 0; button1.text = "calls the Excel file! "; Button1.Click = new System.EventHandler (button1_Click); AutoScaleBaseSize = new System.Drawing.Size (5, 13); this.ClientSize = new System.Drawing.Size (292, 273); this.Controls.Add (Button1); this.name = "form1"; this.text = "How to use Visual C #
Call the Excel form! ";} static void main ()} static void main () {Application.run (new form1 ());} private void button1_click (object sender, system.eventargs e) {excel.application excel = new Excel.Application Excel.Application.Workbooks.add (true); Excel.cells [1, 1] = "First line first column"; Excel.cells [1, 2] = "First line second column"; Excel .Cells [2, 1] = "second line first column"; Excel.cells [2, 2] = "second line second column"; Excel.cells [3, 1] = "third line first "Excel.cells [3, 2] =" Third line second column "; Excel.visible = true;}} (5). Compile source program and program run interface: After compiled after the following command: CSC .exe /R :system.windows.forms.dll /r:System.drawing.dll /r :Excel.dll /R:Office.dll /R:vbide.dll Excel.cs can get "Excel.exe", running interface is http://www.yesky.com/20020313/jt-2002-3-13-Image003.jpg Figure 02: Visual C # calls the Excel form, and store the data running interface Four. Visual C # Handling Other Member Programs in the Office Kit: This article only introduces the solution to some problems that have been encountered in the Excel form, but in fact, other members of the Office suite are also very strong. Significance, such as Visual C # to process the Word document, when calling the Word document, you must first complete the COM component from the non-accomplished code to the managed code, Word's COM component bit "mword9.olb", after conversion I will generate three DLL files, but it is "Wor" D. DLL "," Office.dll "," vbide.dll ". In fact, calling Word in Visual C # is also very easy. Just switch the code in the Excel table to call the code code, the specific: word.application word = new word.application (); word.application.visible = true; Don't believe you try, see if Reach your request. For other operations for Word, it is generally similar to the operation of the Excel table. Since only a document is just a document, the program is relatively small, so it will not be introduced.
