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 operating environment
(1). Microsoft Window 2000 Server Edition
(2) .. Net Framework SDK Beta 2
(3). Microsoft Data Access Component 2.6 or above (MDAC2.6)
(4). Infice 2000 kit
two. Visual C # reads the data in the Excel table:
This section will introduce Visual C # to read the data in the Excel table through a program, and display the data in the form of a DataGrid.
(1). How to read data:
In fact, reading the data in the Excel form and reading the data in the database is very similar, because in some extent, the Excel form can be seen as a piece of data sheet. 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, 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 *" ";
Myconn.open ();
File: // Open the 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: // Turn 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, you only need to pass the following two lines of code, you can display DataGrid:
DataGrid1.datamember = "[Sheet1 $]";
DataGrid1.datasource = MyDataSet;
(3). Read the Excel form with Visual C # and use the program code displayed by DataGrid and the interface running:
Master the above two points, water to the channel 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: 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 in the form
InitializationComponent ();
File: / / Open the data link to get the data set
GetConnect ();
}
File: / / Clear the 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 *" ";
Myconn.open ();
File: // Open the 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: // Turn this data link
MyConn.close ();
}
Private vidinitiRizeComponent () {
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 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 figure is after the program is compiled, the result:
http://www.yesky.com/20020313/jt-2002-3-13-3001.jpg
Figure 01: Using Visual C # reads "c: /sample.xls" run interface
(4). Summary:
The above is just the data in "Sheet1" in the Excel form. For the content in the other "Sheet", you can refer to the program in "Sheet1", just make a point of modification, for example, to read "Sheet2" The content, just change "Sheet1 $" in the "Read.cs" program to "Sheet2 $".
three. Visual C # Call the Excel form and store data in the Excel table:
The Excel form is called in Visual C #, and it is not easy to read the data in the Excel form, because the Excel form is used to use the Excel table to use the Excel table in Visual C #. 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
This creates three DLL files below the root directory of the C drive: "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:
In "Excel.dll", a namespace "Excel" is defined, and a class "Application" is encapsulated in the difference namespace. This class and the startup Excel form have a 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 you will introduce how to enter data into the Excel form.
(3) Enter data into the Excel form:
In the namespace "Excel", a class "cell" is also defined. The representative of this class is 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] = "The first line of the first line";
Excel.cells [1, 2] = "The second line of the first line";
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 stores the program code of the data in the Excel form:
Understand the above knowledge, get the program code that completed the above functions, it is more likely, the specific:
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: Form1: Form
{
Private button button1;
Private system.componentmodel.Container Components = NULL;
Public Form1 ()
{
File: // Initialize the various components in the form
InitializationComponent ();
}
File: / / Clear the resources used in the program
Protected Override Void Dispose (Bool Disposing)
{
IF (Disposing)
{
IF (Components! = NULL)
{
Components.dispose ();
}
}
Base.dispose (Disposing);
}
Private vidinitiRizeComponent ()
{
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 (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 call the Excel form 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] = "The first line of the first column"; Excel.cells [1, 2] = "The second column of the first line";
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;
}
}
(5). Compile the source program and program run interface:
After compiled after the following commands:
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", the interface is as follows
http://www.yesky.com/20020313/jt-2002-3-13-3003.jpg
Figure 02: Visual C # Call the Excel form and store the data run interface
four. Visual C # processes other member programs in the Office suite:
Although this article only introduces the solution to some problems that Visual C # in handling the Excel form, it is actually a strong reference to other members of the Office suite, such as Visual C # to handle Word documents, calling Word. When the document, the COM component must first complete the conversion of the non-accomplished code to the managed code. Word's COM component bit "msword9.olb", and three DLL files will be generated after the conversion, but is "word.dll) "," Office.dll "," vbide.dll ". In fact, calling Word in Visual C # is also very easy. Just need to change 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, see if you meet your requirements. 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.
Fives. to sum up:
This article introduces Visual C # to handle several of the most frequently encountered cases of Excel form, although targeting is just an Excel form, but members of other Office suits have also known significance.
----------------------------- Yesky.com
