Click here to Skip to main content
13,256,012 members (49,336 online)
Click here to Skip to main content
Add your own
alternative version


105 bookmarked
Posted 8 Jan 2009

Import Excel File to DataSet

, 29 May 2012
Rate this:
Please Sign up or sign in to vote.
Allows for inputting Excel tables into a DataSet.


I just wanted to put out two ways to import data from Excel into a DataSet.

  1. Preferred - Importing from an XLS file using the OLE Jet engine (simple).
    1. UPDATED: I'm now setting the IMEX flag to 0. This may cause worse performance. Check out for more information.
    2. UPDATED: I switched it to use ACE driver if it has an XLSX extension. You will need to ensure ACE is installed on the server you deploy to.
    3. UPDATED: Please refer to these links for good information about connecting to xls and xlsx files. 
  2. Importing from an Excel XML file. (The XML format that Excel uses, not just any XML file.) 
    1. Note: This is a long-winded custom solution. Should work, but might require tweaks.
    2. This works well if you're sure the data will be valid, or if you don't require it to do type-detection (flag for this on procedure). 
    3. Exporting to an Excel XML file can be found here

Using the code

Download the file for specifics, but here's a summary:

XLS Import 

public static DataSet ImportExcelXLS(string FileName, bool hasHeaders) {
    string HDR = hasHeaders ? "Yes" : "No";
    string strConn;
    if (FileName.Substring(FileName.LastIndexOf('.')).ToLower() == ".xlsx")
        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";

    DataSet output = new DataSet();

    using (OleDbConnection conn = new OleDbConnection(strConn)) {

        DataTable schemaTable = conn.GetOleDbSchemaTable(
            OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

        foreach (DataRow schemaRow in schemaTable.Rows) {
            string sheet = schemaRow["TABLE_NAME"].ToString();

            if (!sheet.EndsWith("_")) {
                try {
                    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
                    cmd.CommandType = CommandType.Text;

                    DataTable outputTable = new DataTable(sheet);
                    new OleDbDataAdapter(cmd).Fill(outputTable);
                } catch (Exception ex) {
                    throw new Exception(ex.Message + string.Format("Sheet:{0}.File:F{1}", sheet, FileName), ex);
    return output;

Excel XML Import (Summary)

public static DataSet ImportExcelXML(Stream inputFileStream, 
                      bool hasHeaders, bool autoDetectColumnType) {
    XmlDocument doc = new XmlDocument();
    doc.Load(new XmlTextReader(inputFileStream));
    XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable);

    nsmgr.AddNamespace("o", "urn:schemas-microsoft-com:office:office");
    nsmgr.AddNamespace("x", "urn:schemas-microsoft-com:office:excel");
    nsmgr.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");

    DataSet ds = new DataSet();

    foreach (XmlNode node in 
      doc.DocumentElement.SelectNodes("//ss:Worksheet", nsmgr)) {
        DataTable dt = new DataTable(node.Attributes["ss:Name"].Value);
        XmlNodeList rows = node.SelectNodes("ss:Table/ss:Row", nsmgr);
        if (rows.Count > 0) {

            //Add Columns To Table from header row
            List<ColumnType> columns = new List<ColumnType>();
            int startIndex = 0;
            if (hasHeaders) {
                foreach (XmlNode data in rows[0].SelectNodes("ss:Cell/ss:Data", nsmgr)) {
                    columns.Add(new ColumnType(typeof(string)));//default to text
                    dt.Columns.Add(data.InnerText, typeof(string));
            //Update Data-Types of columns if Auto-Detecting
            if (autoDetectColumnType && rows.Count > 0) {
                XmlNodeList cells = rows[startIndex].SelectNodes("ss:Cell", nsmgr);
                int actualCellIndex = 0;
                for (int cellIndex = 0; cellIndex < cells.Count; cellIndex++) {
                    XmlNode cell = cells[cellIndex];
                    if (cell.Attributes["ss:Index"] != null)
                        actualCellIndex = 
                          int.Parse(cell.Attributes["ss:Index"].Value) - 1;

                    ColumnType autoDetectType = 
                      getType(cell.SelectSingleNode("ss:Data", nsmgr));

                    if (actualCellIndex >= dt.Columns.Count) {
                        dt.Columns.Add("Column" + 
                          actualCellIndex.ToString(), autoDetectType.type);
                    } else {
                        dt.Columns[actualCellIndex].DataType = autoDetectType.type;
                        columns[actualCellIndex] = autoDetectType;

            //Load Data
            for (int i = startIndex; i < rows.Count; i++) {
                DataRow row = dt.NewRow();
                XmlNodeList cells = rows[i].SelectNodes("ss:Cell", nsmgr);
                int actualCellIndex = 0;
                for (int cellIndex = 0; cellIndex < cells.Count; cellIndex++) {
                    XmlNode cell = cells[cellIndex];
                    if (cell.Attributes["ss:Index"] != null)
                        actualCellIndex = int.Parse(cell.Attributes["ss:Index"].Value) - 1;

                    XmlNode data = cell.SelectSingleNode("ss:Data", nsmgr);

                    if (actualCellIndex >= dt.Columns.Count) {
                        for (int i = dt.Columns.Count; i < actualCellIndex; i++) {
                            dt.Columns.Add("Column" + 
                                       actualCellIndex.ToString(), typeof(string));
                        ColumnType autoDetectType = 
                           getType(cell.SelectSingleNode("ss:Data", nsmgr));
                        dt.Columns.Add("Column" + actualCellIndex.ToString(), 
                    if (data != null)
                        row[actualCellIndex] = data.InnerText;


    return ds;
    //Format of file, in case you're wondering

    //<?xml version="1.0"?>
    //<?mso-application progid="Excel.Sheet"?>
    // <Worksheet ss:Name="Sheet1">
    //  <Table>
    //   <Row>
    //    <Cell><Data ss:Type="String">Item Number</Data></Cell>
    //    <Cell><Data ss:Type="String">Description</Data></Cell>
    //    <Cell ss:StyleID="s21"><Data ss:Type="String">Item Barcode</Data></Cell>
    //   </Row>
    // </Worksheet>


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer
United States United States
likes boardgames, computer games, and enjoys his .net programming job.

You may also be interested in...

Comments and Discussions

QuestionExcel source file still in use after ImportExcelXLS(...) done Pin
Member 115226558-Dec-15 4:40
memberMember 115226558-Dec-15 4:40 
AnswerRe: Excel source file still in use after ImportExcelXLS(...) done Pin
ColinBashBash8-Dec-15 5:11
memberColinBashBash8-Dec-15 5:11 
Questionmicrosoft.ace.oledb.12.0' provider is not registered on the local machine Pin
Code10812-Nov-14 14:14
memberCode10812-Nov-14 14:14 
QuestionLarge files Pin
Member 1115758316-Oct-14 3:47
memberMember 1115758316-Oct-14 3:47 
SuggestionRe: Large files Pin
ColinBashBash16-Oct-14 5:34
memberColinBashBash16-Oct-14 5:34 
QuestionHttpPostedFile ? Pin
Member 245846716-Sep-14 23:19
memberMember 245846716-Sep-14 23:19 
QuestionRe: HttpPostedFile ? Pin
ColinBashBash17-Sep-14 5:09
memberColinBashBash17-Sep-14 5:09 
QuestionShowing the read data from excel in grid Pin
Arjun Menon U.K25-May-13 7:12
memberArjun Menon U.K25-May-13 7:12 
QuestionVoted 5 Pin
antew17-Dec-12 2:38
memberantew17-Dec-12 2:38 
GeneralMy vote of 5 Pin
parslej15-Oct-12 7:17
memberparslej15-Oct-12 7:17 
QuestionHttpPostedFile Pin
luffyripper29-Aug-12 4:46
memberluffyripper29-Aug-12 4:46 
AnswerRe: HttpPostedFile Pin
ColinBashBash29-Aug-12 4:50
memberColinBashBash29-Aug-12 4:50 
GeneralRe: HttpPostedFile Pin
luffyripper29-Aug-12 6:43
memberluffyripper29-Aug-12 6:43 
GeneralMy 5! Pin
_Amy10-Aug-12 4:11
member_Amy10-Aug-12 4:11 
GeneralWorking very well! Pin
Pavan Gayakwad6-Aug-12 21:50
memberPavan Gayakwad6-Aug-12 21:50 
Thanks for sharing such a very good piece of source code. It worked just fine without much hassle.
One small correction, in ExcelImport.cs file, line 125, for-loop using already used variable (i), I had to change it to unique variable name to get it compiled. (Me using VS.Net 2010 IDE, .Net 4.0)
- Pavan Gayakwad

GeneralMy Vote of 5 Pin
Srinivasan from Chennai20-Jun-12 20:33
memberSrinivasan from Chennai20-Jun-12 20:33 
GeneralMy vote of 5 Pin
kaedei29-May-12 16:47
memberkaedei29-May-12 16:47 
QuestionData is not filled in enough into Dataset Pin
botngot8328-May-12 21:37
memberbotngot8328-May-12 21:37 
AnswerRe: Data is not filled in enough into Dataset Pin
ColinBashBash29-May-12 4:39
memberColinBashBash29-May-12 4:39 
GeneralRe: Data is not filled in enough into Dataset Pin
botngot8329-May-12 23:00
memberbotngot8329-May-12 23:00 
GeneralRe: Data is not filled in enough into Dataset Pin
botngot8331-May-12 18:18
memberbotngot8331-May-12 18:18 
GeneralRe: Data is not filled in enough into Dataset Pin
Bruce Goodman10-Jun-12 23:32
memberBruce Goodman10-Jun-12 23:32 
Bug'inputFileStream.ReadTimeout' threw an exception of type 'System.InvalidOperationException' Pin
Member 35080023-Apr-12 22:59
memberMember 35080023-Apr-12 22:59 
GeneralRe: 'inputFileStream.ReadTimeout' threw an exception of type 'System.InvalidOperationException' Pin
ColinBashBash4-Apr-12 4:54
memberColinBashBash4-Apr-12 4:54 
QuestionI recive this error message "Could not find installable ISAM" Pin
pachiflyer5-Apr-11 4:45
memberpachiflyer5-Apr-11 4:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.171114.1 | Last Updated 29 May 2012
Article Copyright 2009 by ColinBashBash
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid