Powered by SpreadsheetGear
<%@ Page Language="VB" EnableViewState="false" %>
<%@ Import Namespace="System.Data" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>ASP.NET Excel to Custom DataTable Sample Using VB.NET and Microsoft Excel Compatible Spreadsheet Component for Microsoft .NET, ASP.NET, C#, VB.NET, XLS and Microsoft Visual Studio .NET</title>
<meta content="ASP.NET Excel to Custom DataTable sample using Visual Basic .NET and SpreadsheetGear, a royalty free Microsoft Excel compatible spreadsheet component for the Microsoft .NET Framework featuring the fastest and most complete calculation engine available. Create, read, modify, calculate and write Microsoft Excel workbooks from your Microsoft .NET, ASP.NET, C#, VB.NET and Microsoft Office solutions. Integrates with Microsoft Visual Studio .NET, including IntelliSense and Dynamic Help." name="description" />
</head>
<script language="VB" runat="server">
    
    Sub Page_Load(sender As Object, e As EventArgs)
        ' Create a workbook from an Excel file
        Dim ssFile As [String] = Server.MapPath("files/spiceorder.xls")
        Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook(ssFile)

        ' Get a range from an existing defined name.
        Dim range As SpreadsheetGear.IRange = workbook.Names("orderrange").RefersToRange

        ' Hide rows which do not have units > 100.
        Dim unitsCol As Integer = 2
        For Each units As SpreadsheetGear.IRange In range(1, unitsCol, range.RowCount - 1, unitsCol)
            Dim val As Object = units.Value
            If Not ((TypeOf val Is Double) AndAlso CDbl(val) > 100.0R) Then
                units.Rows.Hidden = True
            End If
        Next

        ' Get a DataTable from the range ignoring the hidden rows.
        Dim dataTable As DataTable = GetDataTable(range, SpreadsheetGear.Data.GetDataFlags.FormattedText)

        ' Bind a DataGrid to the DataTable
        DataGrid1.DataSource = dataTable
        DataGrid1.DataBind()
    End Sub 'Page_Load
    
    Private Function GetDataTable(ByVal range As SpreadsheetGear.IRange, ByVal flags As SpreadsheetGear.Data.GetDataFlags) As DataTable
        ' Get a reference to the worksheet.
        Dim worksheet As SpreadsheetGear.IWorksheet = range.Worksheet
    
        ' Get a reference to all the worksheet cells.
        Dim cells As SpreadsheetGear.IRange = worksheet.Cells
    
        ' Get a reference to the advanced API.
        Dim values As SpreadsheetGear.Advanced.Cells.IValues = _
            DirectCast(worksheet, SpreadsheetGear.Advanced.Cells.IValues)
    
        ' Create a new DataTable.
        Dim dataTable As New DataTable()
    
        ' Determine the row and column coordinates of the range.
        Dim row1 As Integer = range.Row
        Dim col1 As Integer = range.Column
        Dim rowCount As Integer = range.RowCount
        Dim colCount As Integer = range.ColumnCount
        Dim row2 As Integer = row1 + rowCount - 1
        Dim col2 As Integer = col1 + colCount - 1
        Dim row As Integer = row1
    
        ' If the first row is not used for column headers...
        If (flags And SpreadsheetGear.Data.GetDataFlags.NoColumnHeaders) <> 0 Then
            ' Create columns using simple column names.
            For col As Integer = col1 To col2
                Dim colName As String = "Column" & (col - col1 + 1)
                dataTable.Columns.Add(colName)
            Next
        Else
            ' Create columns using the first row in the range for column names.
            For col As Integer = col1 To col2
                ' Use the IRange API to get formatted text.
                Dim colName As String = cells(row, col).Text
                dataTable.Columns.Add(colName)
            Next
            row += 1
        End If
    
        ' If the DataTable column data types should be set...
        If (flags And SpreadsheetGear.Data.GetDataFlags.NoColumnTypes) = 0 AndAlso row <= row2 Then
            For col As Integer = col1 To col2
                ' Get a reference to the DataTable column.
                Dim dataCol As System.Data.DataColumn = dataTable.Columns(col - col1)
            
                ' If formatted text is to be used for all cell values...
                If (flags And SpreadsheetGear.Data.GetDataFlags.FormattedText) <> 0 Then
                    ' Set the data type to a string.
                    dataCol.DataType = GetType(String)
                Else
                    ' Set the data type based on the type of data in the cell.
                    '
                    ' Note that this will cause problems if a column does not contain
                    ' consistent data types - for example a column of formulas where
                    ' the first is numeric but one of the following is an error.
                    Dim value As SpreadsheetGear.Advanced.Cells.IValue = values(row, col)
                    If value IsNot Nothing Then
                        Select Case value.Type
                            Case SpreadsheetGear.Advanced.Cells.ValueType.Number
                                dataCol.DataType = GetType(Double)
                                Exit Select
                            Case SpreadsheetGear.Advanced.Cells.ValueType.Text, SpreadsheetGear.Advanced.Cells.ValueType.[Error]
                                dataCol.DataType = GetType(String)
                                Exit Select
                            Case SpreadsheetGear.Advanced.Cells.ValueType.Logical
                                dataCol.DataType = GetType(Boolean)
                                Exit Select
                        End Select
                    End If
                End If
            Next
        End If
    
        ' If formatted text is to be used for all cell values...
        If (flags And SpreadsheetGear.Data.GetDataFlags.FormattedText) <> 0 Then
            ' Create the row data as an array of strings.
            Dim rowData As String() = New String(colCount - 1) {}
            While row <= row2
                ' If the row is not hidden...
                If Not cells(row, 0).Rows.Hidden Then
                    For col As Integer = col1 To col2
                        ' Use the IRange API to get formatted text.
                        Dim text As String = cells(row, col).Text
                        rowData(col - col1) = text
                    Next
                
                    ' Add a new row using the array of formatted strings.
                    dataTable.Rows.Add(rowData)
                End If
                row += 1
            End While
        Else
            ' Create the row data as an array of objects.
            Dim rowData As Object() = New Object(colCount - 1) {}
            While row <= row2
                ' If the row is not hidden...
                If Not cells(row, 0).Rows.Hidden Then
                    For col As Integer = col1 To col2
                        ' Use the advanced API to get the raw data values.
                        Dim value As SpreadsheetGear.Advanced.Cells.IValue = values(row, col)
                        Dim obj As Object = Nothing
                        If value IsNot Nothing Then
                            Select Case value.Type
                                Case SpreadsheetGear.Advanced.Cells.ValueType.Number
                                    obj = value.Number
                                    Exit Select
                                Case SpreadsheetGear.Advanced.Cells.ValueType.Text
                                    obj = value.Text
                                    Exit Select
                                Case SpreadsheetGear.Advanced.Cells.ValueType.Logical
                                    obj = value.Logical
                                    Exit Select
                                Case SpreadsheetGear.Advanced.Cells.ValueType.[Error]
                                    ' This will create problems if it is a column type
                                    ' of double or bool.
                                    obj = "#" & value.[Error].ToString().ToUpper() & "!"
                                    Exit Select
                            End Select
                        End If
                        rowData(col - col1) = obj
                    Next
                
                    ' Add a new row using the array of objects.
                    dataTable.Rows.Add(rowData)
                End If
                row += 1
            End While
        End If
    
        ' Return the DataTable.
        Return dataTable
    End Function
    
</script>
<body style="font-family: Verdana;">
<h5>ASP.NET Excel to Custom DataTable Sample Using<br />Visual Basic .NET and <a href="https://www.spreadsheetgear.com/products/spreadsheetgear.net.aspx">SpreadsheetGear</a></h5>
<form id="form1" runat="server">
  <ASP:DataGrid ID="DataGrid1" BorderWidth="1px" BorderColor="#AAAAAA" CellPadding="4" runat="server">
    <HeaderStyle BackColor="#FF6600" ForeColor="#FFFFFF" Font-Size="8pt" Font-Bold="True" />
    <ItemStyle BackColor="#FFFFEE" ForeColor="#000000" Font-Size="8pt" /> 
    <AlternatingItemStyle BackColor="#FFFFFF" ForeColor="#000000" Font-Size="8pt"/>
  </ASP:DataGrid>
</form>
<h6>Copyright 2005 © SpreadsheetGear LLC. All Rights Reserved.<br /><a href="https://www.spreadsheetgear.com">www.spreadsheetgear.com</a></h6>
</body>
</html>