Joseph Michael Pesch
VP Programming

Open Excel as .Net DataSet in C#

by 12. October 2010 17:26

This entry shows how to open an Excel file as a standard .Net dataset.  This method uses an OleDb driver provided by Microsoft, it does not require that Excel is loaded on the machine, only the OleDb driver is needed.

 

OleDbConnection con = new OleDbConnection(

  "Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;"

  + "Data Source=" + FilePath);

 

 

Driver Download:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

 

 

static public class ExcelDataSetUtil

  {

 

    static public DataSet GetDataSet(string FilePath, string SheetName, bool HasHeader)

    {

      //OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

      //                    + FilePath + ";Extended Properties=\"Excel 8.0;"

      //                    + "HDR=" + (HasHeader ? "Yes" : "No") + ";IMEX=1\"");

 

      OleDbConnection con = new OleDbConnection(

        "Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;"

        + "Data Source=" + FilePath);

      con.Open();

      DataSet ExcelDS = new DataSet();

      try

      {

        //Create Dataset and fill with imformation from the Excel Spreadsheet for easier reference

        OleDbDataAdapter cmd = new OleDbDataAdapter(" SELECT * FROM [" + SheetName + "$]", con);

        cmd.Fill(ExcelDS);

        con.Close();

      }

      catch (Exception ex)

      {

        System.Diagnostics.Debug.WriteLine(ex.ToString());

        throw;

      }

      finally

      {

        con.Close();

      }

      return ExcelDS;

    }

 

    static public void GetDataSet_SampleProcessing(string FilePath, string SheetName, bool HasHeader)

    {

      DataSet ExcelDS = GetDataSet(FilePath, SheetName, HasHeader);

 

      try

      {

        //Travers through each row in the dataset

        foreach (DataRow row in ExcelDS.Tables[0].Rows)

        {

          //Stores info in Datarow into an array

          Object[] cells = row.ItemArray;

          //Traverse through each array and put into object cellContent as type Object

          //Using Object as for some reason the Dataset reads some blank value which

          //causes a hissy fit when trying to read. By using object I can convert to

          //String at a later point.

          foreach (object cellContent in cells)

          {

            //Convert object cellContect into String to read whilst replacing Line Breaks with a defined character

            string cellText = cellContent.ToString();

            cellText = cellText.Replace("\n", "|");

            //Read the string and put into Array of characters chars

            System.Diagnostics.Debug.WriteLine(cellText);

          }

        }

      }

      catch (Exception ex)

      {

        System.Diagnostics.Debug.WriteLine(ex.ToString());

        throw;

      }

    }

  }

 

 

Tags:

[None]

Comments are closed