Shulou(Shulou.com)06/02 Report--

Editor to share with you what are the Excel programming skills of C#, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

I. Excel programming and running environment of C #

(1)。 Microsoft Windows 2000 Server Edition

(2).. Net Framework SDK Beta 2

(3). Microsoft Data Access Component version 2.6 or above (MDAC2.6)

(4). Office 2000 kit

II. Data in the Excel table of Visual C#:

This section will introduce Visual C # to read the data in the Excel table and display the data in the form of DataGrid through a program.

(1)。 How to read data:

In fact, reading the data in the Excel table is very similar to reading the data in the database, because to some extent, the Excel table can be regarded as a data table. The main difference between the two is that the data engines used are different. In the program of this article, read Excel table data through the following code, as follows:

/ / create a data link string strCon = "Provider = 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:// opens the data link and gets a dataset OleDbDataAdapter myCommand = new OleDbDataAdapter (strCom, myConn) File:// creates a DataSet object myDataSet = new DataSet (); file:// gets its own DataSet object myCommand.Fill (myDataSet, "[Sheet1 $]"); file:// closes this data link myConn.Close ()

How to read the data in the Excel table is actually no different from reading the data in the database.

Note: what is read here is the "Sample.xls" file in the root directory of the C disk.

(2)。 Use DataGrid to display the resulting dataset:

After you get the DataSet object, you can display the dataset in DataGrid with the following two lines of code:

DataGrid1.DataMember= "[Sheet1 $]"; DataGrid1.DataSource = myDataSet

(3)。 Read the Excel table with Visual C # and use the program code (Read.cs) displayed by DataGrid and the interface in which the program runs:

Once you have mastered the above two points, you will 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:// initializes various components in the form InitializeComponent () File:// opens the data link to get the dataset GetConnect ();} protected override void Dispose (bool disposing) {if (disposing) {if (components! = null) {components.Dispose ();}} base.Dispose (disposing) } private void GetConnect () {file:// create a data link string strCon = "Provider = 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:// opens the data link to get a dataset OleDbDataAdapter myCommand = new OleDbDataAdapter (strCom, myConn) File:// creates a DataSet object myDataSet = new DataSet (); file:// gets its own DataSet object myCommand.Fill (myDataSet, "[Sheet1 $]"); file:// closes 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 the data in the Excle table and display it in 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 figure shows the result of running the program after compilation:

Figure 01: run interface for reading "c:\ sample.xls" with Visual C #

(4)。 Summary:

The above only read the data in "Sheet1" in the Excel table. For other "Sheet" contents, you can refer to the program in "Sheet1" and make only a little modification. For example, to read the contents in "Sheet2", you only need to change the "Sheet1 $" in the "Read.cs" program to "Sheet2 $".

III. Excel form of Visual C# and store data in Excel table:

Calling Excel tables in Visual C # is not as easy as reading data in Excel tables, because calling Excel tables in Visual C # uses Excel's COM component. If you install the Office suite on the "C" disk, you can find the COM component "EXCEL9.OLB" in "C:\ Program Files\ Microsoft Office\ Office". In the article "how to use Active X components in Visual C #", these COM components are unmanaged code. In order to use these unmanaged code COM components in Visual C #, you must convert them into managed code class libraries. So before calling the Excel table with Visual C #, you must complete the conversion from the unmanaged code of the COM component to the class library of the managed code.

(1)。 A class library that converts unmanaged code COM components into managed code:

First copy the COM component "EXCEL9.OLB" to the root directory of disk C, and then enter the following command:

Tlbimp excel9.olb

In this way, three DLL files are generated under the root directory of disk C: "Excel.dll", "Office.dll" and "VBIDE.dll". After the above three files are generated, the conversion is completed successfully. In the following procedure, you can use the converted three class libraries to write various operations related to the Excel table.

(2). Visual C# opens the Excel form:

A namespace "Excel" is defined in "Excel.dll", and a class "Application" is encapsulated in the differential namespace. This class has a very important relationship with starting the Excel table. In Visual C #, you only need the following three lines of code to open the Excel table, as follows:

Excel.Application excel = new Excel.Application (); excel.Application.Workbooks.Add (true); excel.Visible = true

But at this time, the Excel table is an empty table with no content, so here's how to enter data into the Excel table.

(3)。 Enter data into the Excel table:

In the namespace "Excel", a class "Cell" is also defined, which represents a lower unit in the Excel table. Enter the corresponding data into the Excel table by assigning a value to the differential "Cell". The following code function is to open the Excel table and enter some data into the table.

Excel.Application excel = new Excel.Application (); excel.Application.Workbooks.Add (true); excel.Cells [1,1] = "* Row * column"; excel.Cells [1,2] = "* Row second column"; excel.Cells [2,1] = "second Row * * column"; excel.Cells [2,2] = "second Row second column"; excel.Cells [3,1] = "third Row * * column" Excel.Cells [3,2] = "third row second column"; excel.Visible = true

(4)。 Visual C # calls the Excel table and stores the data in the Excel table (Excel.cs):

Knowing the above knowledge, it is relatively easy to get the program code to complete the above functions, as follows:

Using System; using 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:// initializes various components in the form InitializeComponent () } Resources used in the file:// cleanup program protected override void Dispose (bool disposing) {if (disposing) {if (components! = null) {components.Dispose ();}} base.Dispose (disposing);} private void InitializeComponent () {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 = "invoke 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 invoke Excel tables with Visual C #!" ; this.ResumeLayout (false);} 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] = "* Row * column"; excel.Cells [1,2] = "* Row second column" Excel.Cells [2,1] = "second row * column"; excel.Cells [2,2] = "second row second column"; excel.Cells [3,1] = "third row * column"; excel.Cells [3,2] = "third row second column"; excel.Visible = true;}}

(5)。 Compile the source program and the program running interface:

After compiling with the following command:

Csc.exe / r:system.dll / r:system.windows.forms.dll / r:system.drawing.dll / r:excel.dll / r:office.dll / r:vbide.dll excel.cs

You can get "Excel.exe". After running, the interface is as follows:

Figure 02:Visual C # calls the Excel table and stores the data in the program running interface

IV. Visual C# handles other member programs in the Office suite:

Although this article only introduces the solutions to some problems often encountered by Visual C # in dealing with Excel tables, it is also a strong reference for other members of the Office suite, such as Visual C # to deal with Word documents. When calling Word documents, you must first complete the conversion of COM components from unmanaged code to managed code. The COM component bit "MSWORD9.OLB" of Word will also produce three DLL files after conversion. But they are "Word.dll", "Office.dll" and "VBIDE.dll" respectively. In fact, calling Word in Visual C # is also very easy. Just replace the code in the Excel table with the code that calls Word, as shown below:

Word.Application word = new Word.Application (); word.Application.Visible = true

Try it if you don't believe it, and see if it meets your requirements. For other operations on Word, it is generally similar to the operation on Excel tables. Because the Word is only a document, the program has less operations on Word, so it will not be introduced one by one.

The above is all the content of the article "what are the Excel programming skills of C#". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.