Export Spread to a DataTable

People often come up with the requirement to create a DataTable from an Excel file over the web. It can be easily achieved using Spread for ASP.Net and this blog explains the implementation for the same. To begin with you need to simply import the excel file to a Spread control. Use the given code to load an Excel file in the Spread.

FpSpread1.OpenExcel("\\\data.xls");  

Once the Excel data is imported to Spread, create an empty DataTable and name it MyTable as shown in code below.

DataSet ds = new DataSet();  
DataTable dt = new DataTable();  
dt.TableName = "MyTable";  

Create DataFields in the new table depending upon the column names in the Spread Sheet.

foreach (FarPoint.Web.Spread.Column col in FpSpread1.ActiveSheetView.Columns)  
  dt.Columns.Add(col.Label, typeof(string));  

Now loop through each cell in the Rows and fill data in MyTable.

int rcount = 0;  
foreach (FarPoint.Web.Spread.Row sRow in FpSpread1.ActiveSheetView.Rows)  
{  
    DataRow dtRow = dt.NewRow();  
    for(int j= 0;j<FpSpread1.Sheets[0].Columns.Count-1;j++)  
    {  
     dtRow[j]=FpSpread1.Sheets[0].Cells[rcount,j].Value.ToString();  
    }  
    rcount = rcount + 1;  
    dt.Rows.Add(dtRow);  
}  

Now this table is ready and can be added to a DataSet which you can use further as a DataSource. In the current example, this DataSet acts as a DataSource for another Spread control.

ds.Tables.Add(dt);  
FpSpread2.DataSource = ds;  

Spread supports importing data from different formats like Excel, XML, and CSV which means you can create a DataTable from these available formats using Spread. Download the attached samples for complete implementation. Download VB.Net Sample Download C# Sample