Powered by SpreadsheetGear
<%@ Page Language="C#" 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 C# 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 C# 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="C#" runat="server">
    
    void Page_Load(Object sender, EventArgs e)
    {
        // Create a workbook from an Excel file
        String ssFile = Server.MapPath("files/spiceorder.xls");
        SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(ssFile);

        // Get a range from an existing defined name.
        SpreadsheetGear.IRange range = workbook.Names["orderrange"].RefersToRange;
        
        // Hide rows which do not have units > 100.
        int unitsCol = 2;
        foreach (SpreadsheetGear.IRange units in range[1, unitsCol, range.RowCount - 1, unitsCol])
        {
            object val = units.Value;
            if (!((val is double) && (double)val > 100.0))
                units.Rows.Hidden = true;
        }
        
        // Get a DataTable from the range ignoring the hidden rows.
        DataTable dataTable = GetDataTable(
            range, SpreadsheetGear.Data.GetDataFlags.FormattedText);
        
        // Bind a DataGrid to the DataTable
        DataGrid1.DataSource = dataTable;
        DataGrid1.DataBind();
    }

    DataTable GetDataTable(SpreadsheetGear.IRange range, SpreadsheetGear.Data.GetDataFlags flags)
    {
        // Get a reference to the worksheet.
        SpreadsheetGear.IWorksheet worksheet = range.Worksheet;

        // Get a reference to all the worksheet cells.
        SpreadsheetGear.IRange cells = worksheet.Cells;
        
        // Get a reference to the advanced API.
        SpreadsheetGear.Advanced.Cells.IValues values = 
            (SpreadsheetGear.Advanced.Cells.IValues)worksheet;
        
        // Create a new DataTable.
        DataTable dataTable = new DataTable();
        
        // Determine the row and column coordinates of the range.
        int row1 = range.Row;
        int col1 = range.Column;
        int rowCount = range.RowCount;
        int colCount = range.ColumnCount;
        int row2 = row1 + rowCount - 1;
        int col2 = col1 + colCount - 1;
        int row = row1;
        
        // If the first row is not used for column headers...
        if ((flags & SpreadsheetGear.Data.GetDataFlags.NoColumnHeaders) != 0)
        {
            // Create columns using simple column names.
            for (int col = col1; col <= col2; col++)
            {
                string colName = "Column" + (col - col1 + 1);
                dataTable.Columns.Add(colName);
            }
        }
        else
        {
            // Create columns using the first row in the range for column names.
            for (int col = col1; col <= col2; col++)
            {
                // Use the IRange API to get formatted text.
                string colName = cells[row, col].Text;
                dataTable.Columns.Add(colName);
            }
            row++;
        }
        
        // If the DataTable column data types should be set...
        if ((flags & SpreadsheetGear.Data.GetDataFlags.NoColumnTypes) == 0 && row <= row2)
        {
            for (int col = col1; col <= col2; col++)
            {
                // Get a reference to the DataTable column.
                System.Data.DataColumn dataCol = dataTable.Columns[col - col1];
                
                // If formatted text is to be used for all cell values...
                if ((flags & SpreadsheetGear.Data.GetDataFlags.FormattedText) != 0)
                {
                    // Set the data type to a string.
                    dataCol.DataType = typeof(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.
                    SpreadsheetGear.Advanced.Cells.IValue value = values[row, col];
                    if (value != null)
                    {
                        switch (value.Type)
                        {
                            case SpreadsheetGear.Advanced.Cells.ValueType.Number:
                                dataCol.DataType = typeof(double);
                                break;
                            case SpreadsheetGear.Advanced.Cells.ValueType.Text:
                            case SpreadsheetGear.Advanced.Cells.ValueType.Error:
                                dataCol.DataType = typeof(string);
                                break;
                            case SpreadsheetGear.Advanced.Cells.ValueType.Logical:
                                dataCol.DataType = typeof(bool);
                                break;
                        }
                    }
                }
            }
        }

        // If formatted text is to be used for all cell values...
        if ((flags & SpreadsheetGear.Data.GetDataFlags.FormattedText) != 0)
        {
            // Create the row data as an array of strings.
            string[] rowData = new string[colCount];
            for (; row <= row2; row++)
            {
                // If the row is not hidden...
                if (!cells[row, 0].Rows.Hidden)
                {
                    for (int col = col1; col <= col2; col++)
                    {
                        // Use the IRange API to get formatted text.
                        string text = cells[row, col].Text;
                        rowData[col - col1] = text;
                    }

                    // Add a new row using the array of formatted strings.
                    dataTable.Rows.Add(rowData);
                }
            }
        }
        else
        {
            // Create the row data as an array of objects.
            object[] rowData = new object[colCount];
            for (; row <= row2; row++)
            {
                // If the row is not hidden...
                if (!cells[row, 0].Rows.Hidden)
                {
                    for (int col = col1; col <= col2; col++)
                    {
                        // Use the advanced API to get the raw data values.
                        SpreadsheetGear.Advanced.Cells.IValue value = values[row, col];
                        object obj = null;
                        if (value != null)
                        {
                            switch (value.Type)
                            {
                                case SpreadsheetGear.Advanced.Cells.ValueType.Number:
                                    obj = value.Number;
                                    break;
                                case SpreadsheetGear.Advanced.Cells.ValueType.Text:
                                    obj = value.Text;
                                    break;
                                case SpreadsheetGear.Advanced.Cells.ValueType.Logical:
                                    obj = value.Logical;
                                    break;
                                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() + "!";
                                    break;
                            }
                        }
                        rowData[col - col1] = obj;
                    }

                    // Add a new row using the array of objects.
                    dataTable.Rows.Add(rowData);
                }
            }
        }
        
        // Return the DataTable.
        return dataTable;
    }
    
</script>
<body style="font-family: Verdana;">
<h5>ASP.NET Excel to Custom DataTable Sample<br />Using C# 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>