Visual C # Excel programming
Author: wyhw Time: 2003-5-11 Recommended Level: ★ View author information and author anthology
Excel is a software in Microsoft's Office Automation Suite, which is mainly used to process spreadsheets. 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 strCon = "provider = microsoft.jet.Oledb.4.0; data source = c: //sample.xls; extended Properties = Excel 8.0"; OLEDBCON nection myConn = new OleDbConnection (strCon); string strCom = "SELECT * FROM [Sheet1 $]"; myConn.Open (); file: // open link data to obtain 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 = "Read ExcLe table the data and display it 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 following image is after the program compile, the results: http://www.yesky.com/20020313/jt-2002-3-13-Image001. JPG Figure 01: Read the "C: /Sample.xls" running interface (4) with Visual C # (4). Summary: The above is just read the data in "Sheet1" in the Excel table, for the content in other "sheet" , You can refer to the program in "sheet1", you can only make a point of modification, for example, to read the content in "Sheet2", just need to change "Sheet1 $" in the "Read.cs" program to "SHE ET2 $ "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 void initialization; (); button1.location = new system.drawing.point (32, 72); Button1.name = "Button1"; button1.size = new system.drawing.size (100, 30); button1.tabindex = 0; button1 .Text = "Call 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 # to call the Excel form! "; ..ResumeLayout (false);
