HomeDownloadsSource Code LibraryTables (13)Importing Excel data from a spreadsheet

Importing Excel data from a spreadsheet

Author:	TX Text Control Support Department
Language:	Visual Basic .NET
Version:	1.0
Released:	June 17, 2005
Last modified:	January 11, 2008
Requirements:	TX Text Control .NET with Visual Basic .NET
Download code:	tx_sample_activex_excel.zip  [22.61 KB, ZIP]

Reporting applications merge data from various data sources into MS Word or Adobe PDF document. Sometimes, it is required to include data from Microsoft Excel spreadsheets into a document. This sample shows how to import Excel data into a TX Text Control table.

An Excel document can be accessed like any other database, if a specific range has been specified in the document. We use the standard OleDbConnection to access the Excel document. The statement to select all data from the range is used like the following:

Dim cmdExcel As New OleDbCommand("Select * From StockList, cnExcel)

To create such a range in Microsoft Excel, you have to load an Excel document and select the desired cells like in the following screenshot:

Click on the Insert menu item from the menu bar and choose Define... from the Name submenu. Type in a name for the range, click Add and confirm with OK. After that, you can save the document. This range acts like a Table in a normal database.

After connecting to the Excel document, a table is inserted into the TX Text Control document. The number of rows and columns are given by the number of columns and data rows in the dataset. First, the column names are inserted in the table header. After that, the row data is inserted into the table.

Dim curColumn As Integer = 1
Dim curRow As Integer = 1
 
' connect to the Excel spreadsheet range
If connectToExcelSheet(Application.StartupPath + "\stocks.xls", "StockList") Then
 
' create the table due to the number of rows and cols in the current range
TextControl1.Tables.Add(dtExcel.Rows.Count + 1, dtExcel.Columns.Count, 10)
 
' create table header
For Each column As DataColumn In dtExcel.Columns
	TextControl1.Tables.GetItem(10).Cells.GetItem(1, curColumn).Text = column.ColumnName
	curColumn += 1
Next
 
' fill the table with data
For Each row As DataRow In dtExcel.Rows
	For curColumn = 0 To dtExcel.Columns.Count - 1
		TextControl1.Tables.GetItem(10).Cells.GetItem(curRow + 1, curColumn + 1).Text = row(curColumn)
	Next
	curRow += 1
Next
 
End If

The minimum requirements for this sample application are TX Text Control .NET trial version and Visual Studio .NET 2003.

top