Reading Excel Files with C#

To read an excel file (.xls or .xlsx) use the following code.

    private const string excelConnectionXls = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=HMMasterlist.xls;Extended Properties=Excel 8.0";    private const string excelConnectionXlsx = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.xlsx;Extended Properties=Excel 12.0";

    private void LoadSourceDocument()
    {
      log.Append("Begin Loading Excel n");
      OleDbConnection oledbConn = new OleDbConnection(
excelConnectionXls);
      try
      {
        // Open connection
        oledbConn.Open();
        // Create OleDbCommand object and select data from worksheet Sheet1
        OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
        // Create new OleDbDataAdapter
        OleDbDataAdapter oleda = new OleDbDataAdapter();
        oleda.SelectCommand = cmd;
        // Fill the DataSet from the data extracted from the worksheet.
        oleda.Fill(sourceDS, "Locations");
        log.Append("Excel file loaded successfully. n");
      }
      catch (Exception ex)
      {
        log.Append("Loading Excel Spreadsheet Failed, ex: " + ex.Message + "n");
      }
      finally
      {
        // Close connection
        oledbConn.Close();
      }
    }

    private void UpdateLogoNames()
    {
      foreach (DataRow row in sourceDS.Tables[0].Rows)
      {
        string affiliateId = CheckAffiliateIdLength(row[0].ToString());
        List locations =
          SCCRetailPortal.SW_Locations.Where(x => x.AffiliateId.Equals(affiliateId)).ToList();
        if (locations.Count == 1)
        {
          locations[0].LocationShortName = row[1].ToString();
          log.Append(string.Format("Location {0}:{1} successfully updated. n", locations[0].LocationId,
                                   locations[0].LocationName));
        }
        else
        {
          log.Append(string.Format("FAILURE: No Location Found {0}:{1}. n", row[0], row[1]));
        }
      }
      SCCRetailPortal.SubmitChanges();
    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s