Monday, August 15, 2011

How to import an Excel file into DataSet/DataTable from client.

The following lines of code illustrate simple way of importing and .xls file using OLE Jet engine:

using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Xml;
using System.Web;
 
namespace ConvertToExcel {
    public class ExcelImport {
        public static DataSet ImportExcelXLS(HttpPostedFile file, bool hasHeaders) {
            string fileName = Path.GetTempFileName();
            file.SaveAs(fileName);
 
            return ImportExcelXLS(fileName, hasHeaders);
        }
        private static DataSet ImportExcelXLS(string FileName, bool hasHeaders) {
            string HDR = hasHeaders ? "Yes" : "No";
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=1\"";
 
            DataSet output = new DataSet();
 
            using (OleDbConnection conn = new OleDbConnection(strConn)) {
                conn.Open();
 
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { nullnullnull"TABLE" });
 
                foreach (DataRow row in dt.Rows) {
                    string sheet = row["TABLE_NAME"].ToString();
 
                    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
                    cmd.CommandType = CommandType.Text;
 
                    DataTable outputTable = new DataTable(sheet);
                    output.Tables.Add(outputTable);
                    new OleDbDataAdapter(cmd).Fill(outputTable);
                }
            }
            return output;
        }
    }
}

6 comments:

  1. Nice post!

    a little improvement:
    if you need import data from .xlsx files you should use
    Microsoft.ACE.OLEDB.12.0 provider.
    your connections string will look like
    string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0 xml;HDR=" + HDR + ";IMEX=1\"";

    ReplyDelete
  2. Thanks a lot for your valuable comments. This is a good suggestion for working with Excel 2007 or later.

    ReplyDelete
  3. it's very help full..........

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. Hi,

    it's exactly what I need, but I don't find how I can have the HttpPostedFile file on the client side. I need a popup witch return a path but I don't know how. Could someone help me ?

    ReplyDelete
  6. I recently found code in c# and vb.net to export worksheet data to datatable using Aspose.Cells for .NET Library and it was very helpful for me as it offers to export worksheet data to other data sources also.

    ReplyDelete