Use Visual Basic. NET to process Excle tables

Visual Basic. net processes the Excel table format and processes Word documents, and also through interoperability, the COM component is introduced, so the precondition is that the computer running the program described in this article must install the Excel software in Office 2000. If Office 2000 is installed on your computer, the COM component is Microsoft Excel 9.0 Object Library. Once this COM component is introduced, Visual Basic. Net can process it just like manually editing an Excel table. The following describes the typical operations of Visual Basic. Net in processing Excel tables, such as creating tables and editing tables, and how to import data from a database to an Excel table.

　　I. Environment for program design, debugging and operation in this article:

(1). Microsoft Windows 2000 Server Edition.

(2). Visual Studio. Net2003 enterprise build,. Net FrameWork SDK version 4322.

(3). Office 2000 suite.

　　Ii. Basic operations of Visual Basic. Net in processing Excel tables and its implementation methods:

Before using Visual Basic. Net to process an Excel table, you must first introduce the COM component. For detailed implementation steps, see Step 8 in Section 3 of this article. After the Microsoft Excel 9.0 Object LibraryCOM component is introduced. The following describes how to process a typical Excel table in Visual Basic. Net:

1. Create the excel.exe process. The Excel page is displayed. Create an Excel table:

After successful introduction to the comkit, you can create the excel.exe process by passing the following sentence. Visual Basic. Net is to operate the Excel table by processing the process:

Private AppExcel As Excel. Application = New Excel. Application

After the statement is completed, the Excel program running interface is displayed after the Task Manager completes the process. When the Excel program running interface is not displayed, add the following code after the statement, and the Excel running interface is displayed:

AppExcel. Visible = True

The Excel running interface is displayed, but no table exists. The following Code creates a new table in the Excel program:

AppExcel. Application. Workbooks. Add (True)

2. Use Visual Basic. Net to process an Excel table:

As mentioned above, Visual Basic .netprocesses the exceltable through the created excel.exe process. Through the task manager, we can see that the excel.exe process corresponds to AppExcel in the above Code. The following code sets the value of the (2, 3) unit of an Excel table to Temp:

AppExcel. Cells (2, 3) = Temp

3. Shut down the excel.exe table and destroy the excel.exe process:

After the Excel table is edited, you can close the Excel table after passing the following sentence and destroy the excel.exe process:

AppExcel. Application. Workbooks. Close ()
'Close the Excel worksheet
AppExcel. Quit ()
'Destroy excel.exe Process

The above is Visual Basic. net some basic operation methods, and other methods for processing Excel tables, but they are similar to the above methods. Due to space limitations, I will not describe them one by one, I believe that it will not be difficult to master these methods. The following describes a more practical example. The implementation process of this example is mainly through the basic operation methods described above, the function of this example is to import the content of data tables in the SQL Server database to an Excel table.

　　3. Import the database data to the Excel table using Visual Basic. Net:

This example allows you to easily import data tables in the SQL Server database to an Excel table. For other types of databases, if you want to import data to an Excel table, you only need to modify the data connection code in this example. The following describes how to import an SQL Server database to an Excel table in Visual Basic. Net:

1. Start Visual Studio. Net.

2. Select File, new, and project. The new project dialog box is displayed.

3. Set project type to Visual Basic Project ].

4. Set template to Windows application ].

5. In the Name text box, enter Visual Basic. Net to import database data to an Excel table ].

6. in the location text box, enter E: \.. NET project, and then click OK. in the. NET project directory, the file named Visual Basic is generated. and create a folder named Visual Basic.. Net.

7. set Visual Studio. switch the current window of Net to the Form1.vb window, drag the following components into the Form1 form from the Windows Forms components tab in the toolbox, and perform the corresponding operations:

Five Label components.
Five TextBox components.

A Button component is used to import the SQL Server database to an Excel table, drag the Button component into the Form1 design form, and double-click them, Then Visual Stuido. net will generate the processing code corresponding to the Click event of this component in the Form1.vb file respectively.

8. select solution manager, right-click reference, and select Add reference in the pop-up menu, as shown in Figure 01, add the COM component Microsoft Excel 9.0 Object Library to be referenced:

Figure 01: [import database data to an Excel table using Visual Basic. Net] project [add reference] dialog box

9. Switch the current window of Visual Studio. Net to the code editing window of Form1.vb, and add the following code in the header of Form1.vb. The following code imports the namespace of the Data class used below:

Imports System. Data. OleDb

10. Use the following code to replace the code corresponding to the InitializeComponent process in Form1.vb. The following code serves to initialize the components added to the form:

<System. Diagnostics. DebuggerStepThrough ()> 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 = data table 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 the 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 an Excel table
Me. ResumeLayout (False)
End Sub

At this point, the interface design and function implementation of the project [Visual Basic. Net import database data to Excel table] have been completed, as shown in 02:

Figure 02: [import database data to an Excel worksheet using Visual Basic. Net] design page

11. use the following code to replace the processing code corresponding to the Click event of Button1 in Form1.vb. The following code function is to establish a data connection based on the user's input database information, open the data table, and import it to an Excel table, if an error occurs, the following error message is displayed:

Private Sub button#click (ByVal sender As System. Object, ByVal e As System. EventArgs) Handles Button1.Click
'Create a DataSet
Dim myDataSet As DataSet = New DataSet
'Connect to the database and get the dataset.
Try
'Set the data connection string, which means to open the SQL server database, the server name is local, and 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 &; Data Source = & TextBox1.Text
'Data connection code. For this modification, 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 worksheet document
Dim AppExcel As Excel. Application = New Excel. Application
AppExcel. Application. Workbooks. Add (True)
'Name of the field for reading data, which is displayed in the first row of the generated 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 worksheet
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

After the above steps are completed correctly, all the work of the project [import database data to Excel table in Visual Basic. Net] is basically completed. 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, and enter the name of the database to be opened in Database Name, in data table name, enter the name of the data table to be imported to the Excel table. In user name and password, enter the SQL Server database account and password respectively, click the import Excel table button to import the corresponding data table to the Excel table. Figure 03 and figure 04 show the running interface of the project and the Excel table after data import in Visual Basic. Net:

Figure 03: Visual Basic. Net import database data to an Excel table

Figure 04: an Excel table formed after the SQL Server database data is imported

　　Iv. Summary:

This article uses a practical example to show how to use Visual Basic. Net to process Excel tables. This example can export data from the SQL Server database to an Excel table. The COM component is a ready-made resource. When the. Net FrameWork SDK cannot help you solve the problem, it may be a shortcut to use COM. Finally, we hope that you can learn how to use the COM component in Visual Basic. Net. It is helpful.

Transfer from: dynamic network Production Guide www.xue163.com