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(
        // Open connection
        // 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");
        // Close connection

    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,
          log.Append(string.Format("FAILURE: No Location Found {0}:{1}. n", row[0], row[1]));

Leave a Reply

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

You are commenting using your 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