# Friday, March 15, 2013

OVERVIEW

Microsoft Excel data can be treated like many other data sources from within a .NET application: we can connect to an Excel data source and we can query it using Structured Query Language (SQL). We don't even need to launch Excel or even have Excel installed in order to do so. All we need is the appropriate drivers.
This is good news if we need to read Excel data on a server, such as a web server. It’s possible to user Office Interop code to launch Excel and manipulate a spreadsheet, but Microsoft advises against doing so (http://support.microsoft.com/kb/257757).

SAMPLE

Here is sample code for querying Excel data and loading it into an ADO.NET DataTable

public static DataTable GetExcelData(string fullPath)
{
    var connectionString = string.Format
        (
        @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES'",
        fullPath
        );
    var adapter = new OleDbDataAdapter
        (
        "SELECT * FROM [Sheet1$]", 
        connectionString
        );
    var ds = new DataSet();
    adapter.Fill(ds, "ExcelData");
    DataTable excelTable = ds.Tables["ExcelData"];
    return excelTable;
}

The Connection String contains the driver and the full path to the Excel file. The “HDR” in the connection string indicates that the first row of the spreadsheet contains column headers. These headers will become the column names in our ADO.NET data table.
We can call this code from a web page to read an Excel file uploaded with the ASP.NET FileUpload control. The code is shown below.

protected void ReadExcelButton_Click(object sender, EventArgs e) 
{ 
    if (ExcelUploaderValidator.IsValid) 
    { 
        string fileName = ""; 
        string filePath = Server.MapPath("Upload"); 
        var fullPath = ""; 
        //var fullPath = @"C:\Test\ReadExcel\ReadExcel\bin\Debug" + @"\" + fileName; 
        if (ExcelUploader.HasFile) 
        { 
            var rand = new Random(); 
            fileName = String.Format("{0:000000}-{1}", rand.Next(0, 999999), ExcelUploader.FileName); 
            fullPath = filePath + @"\" + fileName; 
            ExcelUploader.SaveAs(fullPath); 
            DataTable excelTable = ExcelUtils.GetExcelData(fullPath); 
            ExcelGrid.DataSource = excelTable; 
            ExcelGrid.DataBind(); 
        } 
    } 
} 

In this example, we save the uploaded file to a folder on the server; then, call our function, pointing to that file, so we can pass it in as part of the connection string.
For completeness, here is the web page markup in this sample:

<div> 
    <br /> 
    <asp:FileUpload ID="ExcelUploader" runat="server"  /> 
    <asp:RegularExpressionValidator 
        runat="server" 
        ID="ExcelUploaderValidator" 
        ControlToValidate="ExcelUploader" 
        ErrorMessage="Only Excel files or CSV files are allowed" 
        ValidationExpression="^.*\.(xls|XLS|xlsx|XLSX|csv|CSV)$"> 
    </asp:RegularExpressionValidator> 
    <br /> 
</div> 
<asp:Button ID="ReadExcelButton" runat="server" onclick="ReadExcelButton_Click" 
    Text="Read Excel Document" /> 
<asp:GridView ID="ExcelGrid" runat="server"> 
</asp:GridView> 

The query assumes that the workbook we are reading contains a worksheet named “Sheet1” and it reads all the data in that worksheet, copying it into a DataTable.

LIMITATIONS

The Excel data driver only works in 32-bit .NET projects, so you will need to configure the project properties to run in 32-bit mode.

CONCLUSION

In this article, we demonstrated a simple way to read data from an Excel document and copy it into a DataTable for further manipulation and processing.

Friday, March 15, 2013 2:29:32 PM (GMT Standard Time, UTC+00:00)