Excel programming in Visual C #

By wyhw

It's strange. wyhw'sArticleI have a post on other websites, but I don't know if my website has the same name and surname?
-_-!

Excel is a software in Microsoft Office Automation suite. It is mainly used to process workbooks. Excel has been welcomed by many users for its powerful functions and friendly interfaces. At work, it is precisely because of the advantages of Excel that many important data are often stored in the form of Excel workbooks. In this wayProgramAlthough Excel is powerful, it is not a database after all. It is much easier to process the data in the database in the program than to process the data in the Excel table. So how can I use Visual C # to read data from an Excel table? In the past, when using Delphi programming, they had different requirements for printing for different users. If you want to make the printing function in the program suitable for every user, as you can imagine, program design is very complicated. When I think of Excel, because of its powerful functionality, and because almost every machine has installed it, if I put the results of program processing into an Excel table, in this way, each user can customize their own printing in EXCEL according to their own needs. This not only makes the program design simple, but also meets the requirements of many users and is more practical. So how can I use Visual C # To Call Excel and store data in an Excel table? This article will discuss the solutions to the above problems.

I. program design and running environment

(1). Microsoft Windows 2000 Server Edition

(2). Net Framework SDK beta 2

(3). Microsoft Data Access Component 2.6 or later (mdac2.6)

(4). Office 2000 suite
Ii. Visual C # read data from an Excel table:

This section describes how to use a program to read data from an Excel table in Visual C # and display the data in a DataGrid.

(1). How to read data:

In fact, reading data from an Excel table is very similar to reading data from a database, because an Excel table can be regarded as a data table to some extent. The main difference between the two is that the data engine used is different. In the program in this articleCodeTo read data from an Excel table, perform the following operations:

// 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: // open the data link to obtain a dataset.
Oledbdataadapter mycommand = new oledbdataadapter (strcom, myconn );
File: // create a DataSet object
Mydataset = new dataset ();
File: // get your DataSet object
Mycommand. Fill (mydataset, "[sheet1 $]");
File: // close this data link
Myconn. Close ();

There is no substantial difference between reading data from an Excel table and reading data from a database.

Note: The "sample.xls" file under the root directory of the C drive is read here.

(2). Use the DataGrid to display the dataset:

After obtaining the DataSet object, you can use the DataGrid to display the dataset through the following two lines of code:

Datagrid1.datamember = "[sheet1 $]";
Datagrid1.datasource = mydataset;

(3). Use Visual C # To read the Excel table and use the program code (read. CS) displayed in the DataGrid and the program running interface:

With the above two points in mind, 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 each component in the form
Initializecomponent ();
File: // open the data link to obtain the dataset.
Getconnect ();
}
File: // clear resources used in the program
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: // open the data link to obtain a dataset.
Oledbdataadapter mycommand = new oledbdataadapter (strcom, myconn );
File: // create a DataSet object
Mydataset = new dataset ();
File: // get your DataSet object
Mycommand. Fill (mydataset, "[sheet1 $]");
File: // close this data link
Myconn. Close ();
}
Private void initializecomponent ()
{
Maid = new DataGrid ();
Button1 = new button ();
Suspendlayout ();
Datagrid1.name = "datagrid1 ";
Datagrid1.size = new system. Drawing. Size (400,200 );

Button1.location = new system. Fig. Point (124,240 );
Button1.name = "button1 ";
Button1.tabindex = 1;
Button1.text = "reading data ";
Button1.size = new system. Drawing. Size (84, 24 );
Button1.click + = new system. eventhandler (this. button#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 data from the excle table and display it in the DataGrid! ";
This. resumelayout (false );

}
Private void button#click (Object sender, system. eventargs E)
{
Datagrid1.datamember = "[sheet1 $]";
Datagrid1.datasource = mydataset;

}
Static void main ()
{
Application. Run (New form1 ());
}
}

Is the running result after the program is compiled:
Http://www.yesky.com/20020313/jt-2002-3-13-image001.jpg

Figure 01: Use Visual C # To read the running interface of "C: \ sample.xls"
(4). Summary:

The above only reads the data in "sheet1" in the Excel table. For other "sheet" content, refer to the program in "sheet1" and make only one modification, for example, to read the content in "sheet2", you only need to read "read. change "sheet1 $" in CS "program to" sheet2 $.

3. Visual C # Call an Excel table and store data in the Excel table:

Calling an Excel table in Visual C # is not as easy as reading data from an Excel table, because calling an Excel table in Visual C # uses the COM component of Excel. If you install the office suite on the "c" disk, find the COM component "excel9.olb" in "C: \ Program Files \ Microsoft Office \ office ", in the article "How to Use the Active X component in Visual C #", these COM components are not managed code and must be used in Visual C, they must be converted into a class library of managed code. Therefore, before using Visual C # to call an Excel table, you must complete the conversion from the unmanaged code of the COM component to the class library of the managed code.

(1). Convert the COM component of the unmanaged code to the class library of the managed code:

First, copy the COM component "excel9.olb" to the root directory of drive C, and then enter the following command:

Tlbimp excel9.olb

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

(2). Visual C # Open the Excel table:

In "Excel. DLL defines a namespace "Excel" and encapsulates a class "application" in the poor namespace. This class is very important to start an Excel table, in Visual C #, you only need the following three lines of code to open an Excel table:

Excel. Application Excel = new excel. Application ();
Excel. application. workbooks. Add (true );

Excel. Visible = true;

However, the Excel table is an empty table with no content. The following describes how to input data into the Excel table.

(3). input data to the Excel table:

In the namespace "Excel", a class "cell" is also defined, which represents a unit in an Excel table. By assigning values to cells, you can enter the corresponding data in an Excel table. The following code is used to open an Excel table and input some data into the table.

Excel. Application Excel = new excel. Application ();
Excel. application. workbooks. Add (true );
Excel. cells [1, 1] = "first column of the First row ";
Excel. cells [1, 2] = "the second column of the First row ";
Excel. cells [2, 1] = "first column of the Second row ";
Excel. cells [2, 2] = "second row and second column ";
Excel. cells [3, 1] = "first column of the third row ";
Excel. cells [3, 2] = "second column of the third row ";
Excel. Visible = true;

(4). Visual C # the program code (Excel. CS) that calls the Excel table and stores data in the Excel table ):

After learning the above knowledge, it is easier to get the program code to complete the above functions, as shown below:

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: // initialize each component in the form
Initializecomponent ();
}
File: // clear all resources used in the 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 = "Call the Excel file! ";
Button1.click + = new system. eventhandler (button#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 an Excel sheet! ";
This. resumelayout (false);

}< br> static void main ()
{< br> application. run (New form1 ();
}< br> private void button#click (Object sender, system. eventargs e)
{< br> excel. application Excel = new excel. application ();
excel. application. workbooks. add (true);
excel. cells [1, 1] = "first column of the First row";
excel. cells [1, 2] = "column 2 in the first row";
excel. cells [2, 1] = "first column of the Second row";
excel. cells [2, 2] = "second row and second column";
excel. cells [3, 1] = "first column of the third row";
excel. cells [3, 2] = "column 2 in the third row";
excel. visible = true;
}< BR >}

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

After the following command is compiled:

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:

Http://www.yesky.com/20020313/jt-2002-3-13-image003.jpg
Figure 02: Visual C # the program running interface that calls an Excel table and stores data

4. Visual C # process other member programs in the office suite:

Although this article only describes how to solve some of the problems that Visual C # often encounters in processing Excel tables, it also has a strong reference for other members of the office suite, for example, when using Visual C # To process Word documents, you must first convert the COM component from unmanaged code to managed code. The COM component bit of word "msword9.olb ", after conversion, three DLL files are generated, which are "word. DLL "," office. DLL "," vbide. DLL ". In fact, it is very easy to call word in Visual C. You only need to replace the code in the Excel file with the code that calls word. The details are as follows:

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

Don't believe it. Try it and see if it meets your requirements. Other operations on word are generally similar to those on Excel tables. Since word is only a document, the program does less operations on word, so we will not discuss it one by one.

V. Summary:

This article describes the most common situations in which Visual C # is used to process Excel tables. Although it is only for Excel tables, it is also of great reference for members of other office suites.