Visual Basic. NET processing excle table Full contact

Visual Visual Basic. NET processing Excel table mechanisms and processing Word documents are also implemented by interoperating, introducing COM components, so the prerequisite is that the computer running the program described in this article must have Excel software installed in Office 2000. If the computer is installed with Office 2000, the COM component is the Microsoft Excel 9.0 Object Library. Once this COM component is introduced, Visual Basic. NET, you can work with it by manually editing the Excel table. Here's how to introduce visual Basic. NET handles the typical operations of Excel tables, such as creating tables, editing tables, and how to import data from a database into an Excel table.

　　 one. The environment of programming, debugging and running in this paper：

(1). Microsoft Windows 2000 Server Edition.

(2). Visual Studio. NET2003 Enterprise Build,. Net FrameWork SDK version number 4322.

(3). Office 2000 Suite.

　　 two. Visual Basic. NET work with Excel tables and how to implement them:

In Visual Basic. NET to work with Excel tables, you first need to introduce COM components, which you can see step eighth in the third section of this article. After the introduction of the Microsoft Excel 9.0 Object Library COM component. The following are described in Visual Basic. NET, how to handle the typical operations of Excel tables:

1. Create Excel.exe process, display Excel interface, create Excel table:

Once a COM component is successfully introduced, the Excel.exe process can be created by using the following statements, Visual Basic. NET is to manipulate Excel tables by processing this process:

Private Appexcel as Excel.Application = New Excel.Application

When you finish this statement, the Task Manager to view the process to find out the Excel.exe process, when the Excel program's running interface does not appear, after this statement add the following code, Excel's running interface is displayed:

Appexcel.visible = True

Although the Excel run interface is displayed, but there are no tables, the following code creates a new table in an Excel program:

APPEXCEL.APPLICATION.WORKBOOKS.ADD (True)

2. Visual Basic. NET processing Excel tables:

As stated above, Visual Basic. NET processing Excel tables is accomplished by creating a Excel.exe process. See the Excel.exe process corresponding to the Appexcel in the above code through Task Manager. The following code sets the value of the (2,3) cell of the Excel table to "Temp":

Appexcel.cells (2, 3) = "Temp"

3. Close Excel table and destroy Excel.exe process:

When you finish editing an Excel table, you can close the Excel table and destroy the Excel.exe process by using the following statement:

AppExcel.Application.Workbooks.Close () ' Close Excel table Appexcel.quit () ' Destroying the Excel.exe process

The above

is visual Basic. NET some basic methods of operation, other ways to deal with Excel tables are many, but they are similar to the above methods, due to the limited length of the introduction, I believe that after mastering these methods, it will not be difficult to master them. Here's a more practical example of how this example is implemented primarily through the basic operations described above, which is the ability to import the contents of a datasheet from a SQL Server database into an Excel table.

three. Visual Basic. NET Import database data to an Excel table:

This example makes it easy to import a datasheet from a SQL Server database into an Excel table, and if you also need to import data to an Excel table for other types of databases, You just need to modify the code for the Data connection section in this example. The following is in visual Basic. Net to import a SQL Server database into an Excel table:

1. Start visual Studio. Net.

2. When you select the menu "file" | "new" | "Project", The New Project dialog box pops up.

3. Set the project type to Visual basic project.

4. Set "template" to "Windows Application".

5. In the Name text box, enter Visual Basic. NET Import database data to Excel table.

6. In the text box in position, enter E:\VS. NET project, and then click the OK button, so that in the E:\VS. NET project directory, a name called "Visual Basic" is generated. NET and creates a name called "Visual Basic" inside the folder where the Word document is manipulated. NET Import database data to an Excel table project file.

7. Take visual Studio. NET to the Form1.vb window, and from the Windows Forms Components tab in the Toolbox, drag the following components into the Form1 form and do the appropriate action:

Five label components.
Five textbox components.

A button component that implements the import of a SQL Server database into an Excel table, and then double-clicks the button component after it has been dragged into the Form1 design form, and then the visual Stuido. NET produces the processing code corresponding to the click event for this component in the Form1.vb file.

8. Select Solution Manager, right-click References, select Add Reference in the pop-up menu, and add the COM component to reference, as shown in Figure 01, "Microsoft Excel 9.0 Object Library":

Figure 01: "Visual Basic." NET Import database data to Excel Table Project Add Reference dialog box

9. Take visual Studio. NET switch to the Form1.vb Code editing window and add the following code to the header of the Form1.vb file, the following code is the namespace in which the data classes used below are imported:

Imports System.Data.OleDb

10. Replace the code for the InitializeComponent procedure in Form1.vb with the following code that initializes the components that are joined in the form:

Private Sub InitializeComponent () Me.label1 = New System.Windows.Forms.Label Me.label2 = New System.Windows.Forms.Label Me.label3 = New System.Windows.Forms.Label Me.label4 = New System.Windows.Forms.Label Me.label5 = New System.Windows.Forms.Label Me.textBox1 = New System.Windows.Forms.TextBox Me.textBox2 = New System.Windows.Forms.TextBox Me.textbox3 = New System.Windows.Forms.TextBox ME.TEXTBOX5 = New System.Windows.Forms.TextBox Me.button1 = New System.Windows.Forms.Button Me.textbox4 = New System.Windows.Forms.TextBox Me.suspendlayout () Me.Label1.Location = New System.Drawing.Point (40, 28) Me.Label1.Name = "Label1" Me.Label1.Size = New System.Drawing.Size (114, 23) Me.Label1.TabIndex = 0 Me.Label1.Text = "Database server name:" Me.Label2.Location = New System.Drawing.Point (64, 59) Me.Label2.Name = "Label2" Me.Label2.TabIndex = 1 Me.Label2.Text = "Database name:" Me.Label3.Location = New System.Drawing.Point (64, 90) Me.Label3.Name = "Label3" Me.Label3.TabIndex = 2 Me.Label3.Text = "Datasheet Name:" Me.Label4.Location = New System.Drawing.Point (88, 121) Me.Label4.Name = "Label4" Me.Label4.TabIndex = 3 Me.Label4.Text = "User name:" Me.Label5.Location = New System.Drawing.Point (98, 152) Me.Label5.Name = "Label5" Me.Label5.TabIndex = 4 Me.Label5.Text = "Password:" Me.TextBox1.Location = New System.Drawing.Point (160, 26) Me.TextBox1.Name = "TextBox1" Me.TextBox1.Size = New System.Drawing.Size (148, 21) Me.TextBox1.TabIndex = 5 Me.TextBox1.Text = "" Me.TextBox2.Location = New System.Drawing.Point (160, 56) Me.TextBox2.Name = "TextBox2" Me.TextBox2.Size = New System.Drawing.Size (148, 21) Me.TextBox2.TabIndex = 6 Me.TextBox2.Text = "" Me.TextBox3.Location = New System.Drawing.Point (160, 86) Me.TextBox3.Name = "TextBox3" Me.TextBox3.Size = New System.Drawing.Size (148, 21) Me.TextBox3.TabIndex = 7 Me.TextBox3.Text = "" Me.TextBox5.Location = New System.Drawing.Point (160, 146) Me.TextBox5.Name = "TextBox5" Me.TextBox5.PasswordChar = Microsoft.VisualBasic.ChrW (42) Me.TextBox5.Size = New System.Drawing.Size (148, 21) Me.TextBox5.TabIndex = 9 Me.TextBox5.Text = "" Me.Button1.Location = New System.Drawing.Point (120, 198) Me.Button1.Name = "Button1" Me.Button1.Size = New System.Drawing.Size (126, 36) Me.Button1.TabIndex = 10 Me.Button1.Text = "Export excle table" Me.TextBox4.Location = New System.Drawing.Point (160, 116) Me.TextBox4.Name = "TextBox4" Me.TextBox4.Size = New System.Drawing.Size (148, 21) Me.TextBox4.TabIndex = 8 Me.TextBox4.Text = "" Me.autoscalebasesize = New System.Drawing.Size (6, 14) Me.clientsize = New System.Drawing.Size (374, 271) ME.CONTROLS.ADD (Me.button1) ME.CONTROLS.ADD (ME.TEXTBOX5) ME.CONTROLS.ADD (ME.TEXTBOX4) ME.CONTROLS.ADD (ME.TEXTBOX3) ME.CONTROLS.ADD (Me.textBox2) ME.CONTROLS.ADD (Me.textBox1) ME.CONTROLS.ADD (ME.LABEL5) ME.CONTROLS.ADD (ME.LABEL4) ME.CONTROLS.ADD (ME.LABEL3) ME.CONTROLS.ADD (ME.LABEL2) ME.CONTROLS.ADD (ME.LABEL1) Me.formborderstyle = System.Windows.Forms.FormBorderStyle.FixedSingle Me.maximizebox = False Me.Name = "Form1" Me.Text = "Visual Basic." NET Import database data to Excel table Me.resumelayout (False) End Sub

This is the Visual Basic. NET Import database data to Excel table "the preparation of the interface design and functional implementation of the project is complete, as shown in Figure 02:

Figure 02: "Visual Basic." NET Import database data to Excel Table project design interface

11. Use the following code to replace the Button1 click event in Form1.vb, the following code function is based on the user input database information, set up a data connection, open the datasheet, and import into the Excel table, if the error pop-up error message:

Private Sub button1_click (ByVal sender as System.Object, ByVal e as System.EventArgs) Handles Button1.Click ' Create a DataSet Dim myDataSet as DataSet = New DataSet ' Connect the database, get the dataset Try ' Set the data connection string, which means to open the SQL Server database, the server name is local, the database is temp Dim Strcon as String = "Provider = sqloledb.1;" Persist security Info = False; User ID = "& Textbox4.text &"; Psw= "& Textbox5.text _ & "; Initial Catalog = "& TextBox2.Text &";D ata Source = "& TextBox1.Text ' Data connection code, for which you can import other types of databases to the Excle table Dim myconn as OleDbConnection = New OleDbConnection (Strcon) MyConn.Open () Dim strcom as String = "SELECT * from" & TextBox3.Text Dim mycommand as OleDbDataAdapter = New OleDbDataAdapter (strcom, myconn) Mycommand.fill (myDataSet, "table01") ' Close this OleDbConnection Myconn.close () Catch ey as Exception MessageBox.Show ("Connection Error!" + EY. ToString (), "error") End Try Dim table as DataTable = Mydataset.tables ("table01") ' Create an empty Excel spreadsheet document Dim Appexcel as Excel.Application = New Excel.Application APPEXCEL.APPLICATION.WORKBOOKS.ADD (True) ' Read the field name of the data and display it in the first row of the resulting Excel table Dim Colindex as Integer = 0 Dim col as DataColumn = New DataColumn For each col in table. Columns Colindex = Colindex + 1 Appexcel.cells (1, colindex) = Col. ColumnName Next ' Convert a dataset to an Excel table Dim RowIndex as Integer = 1 Dim Row as DataRow For each row in table. Rows RowIndex = RowIndex + 1 Colindex = 0 Dim col1 as DataColumn For each col1 in table. Columns Colindex = Colindex + 1 Appexcel.cells (RowIndex, Colindex) = row (col1. ColumnName). ToString () Next Next Appexcel.visible = True End Sub

Now that the above steps are completed correctly, "Visual Basic." NET Import database data to Excel Table project is basically done. Click the shortcut key F5 to run the program, in database server name, enter the name of the machine where the SQL Server database is located, enter the name of the database you want to open in database name, and enter the name of the datasheet to import into the Excel table in Datasheet name, in User name and password When you open this SQL Server database account and password, click the Import Excel Table button, and the program imports the appropriate datasheet into the Excel table. Figure 03 and Figure 04 are "Visual Basic." NET Import database data to Excel Table project's running interface and Excel table after importing data:

Figure 03: "Visual Basic." NET Import database data to Excel Table project's running interface

Figure 04: Excel table formed after importing SQL Server database data

　　 Four. Summarize:

This article uses a practical example to introduce visual Basic. NET handles the various typical operations of Excel tables, which can be done by exporting data from a SQL Server database to an Excel table. COM components are a rich resource that is readily available and perhaps COM is a shortcut when the. Net FrameWork SDK does not help you solve the problem. Finally, I want this article to be available to you in Visual Basic. NET, how to use COM components in the. Help.