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. 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 * from [sheet1 $]"; myconn.open (); file: // Open data link to get a dataset 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 In fact, there is no substantial difference between the data in reading 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.collections; using system.componentmodel; using system.windows.form; using system.data; using system.data.oledb;
Public class form1: form {private button button1; private system.data.dataset mydataset; private system.componentmodel.container components = null Download Adobe Reader Diavate;
Public Form1 () {file: // Initialize the various components in the form InitializeComponent (); 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 (component! = Null) {Components.dispose ();}}}}}}}}}});} 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 data link to get a datasete 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 (button); this.controls.add (datagrid1); This.name = "Form1"; this.text = "reads the data in the ExcLe table and displays 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 picture below is the program compile, run the result: 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 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.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] = "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 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: Form {private Button button1; private System. ComponentModel.Container Components = NULL; Public Form1 () {file: // Initialization in the various components in the form InitializeComponent (); File: // Clear the various 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 = "calls the Excel file! "; Button1.Click = new system.eventhandler (Button1_Click); AutoScaleBaseSize = new system.drawing.size (5, 13);
THISTEM.DRAWING.SIZE (292, 273); this.controls.add (button1); this.name = "form1"; this.text = "How to call 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] = "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 column"; Excel.cells [3, 2] = "Third line second column"; Excel.visible = true;}} (5). Compile source program and program run interface:
After compiled by 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", the interface is as follows
http://www.yesky.com/20020313/jt-2002-3-13-Image003.jpg Figure 02: Visual C # Call the Excel form, and store the data running 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 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.
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.
