title: Grab Spreadsheet names then read each in Foreach
body: <p>Ok, I am trying to figure out the best way to accomplish this in code. I have a spreadsheet with numerous tabs and what I want to do is in C# read the name of each sheet and then its corresponding rows of data. Currently, this is what I have:</p>
  
  <pre><code>            if (!DirectoryIsEmpty(DropZone.ToString())) 
          { 
  
              DirectoryInfo di = new DirectoryInfo(DropZone); 
              FileInfo[] fi = di.GetFiles(); 
              // step through each found file 
              foreach (FileInfo fiTemp in fi) 
              { 
                  //    Connection String to Excel Workbook 
                  string excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + DropZone + "\\" + fiTemp + "'; Extended Properties=\"Excel 8.0; HDR=YES; IMEX=1;\""; 
                  string SheetName = string.Empty; 
                  //    Create Connection to Excel Workbook 
                  using (OleDbConnection connection = new OleDbConnection(excelConnStr)) 
                  { 
                      try 
                      { 
                          string curaddy2 = string.Empty; 
                          string priaddy2 = string.Empty; 
                          connection.Open(); 
                          DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 
                          if (dt != null) 
                          { 
                              string[] excelSheets = new String[dt.Rows.Count]; 
                              int i = 0; 
                              foreach (DataRow row in dt.Rows) 
                              { 
                                  excelSheets[i] = row["TABLE_NAME"].ToString();                                     
                                  #region ExcelReading 
                                  SheetName = excelSheets[i].ToString(); 
                                  OleDbCommand command = new OleDbCommand("SELECT [Name], [Address], [City], [State], [Zip] FROM '" + SheetName.ToString() + "'", connection); 
                                  using (DbDataReader dr = command.ExecuteReader()) 
                                  { 
                                      // SQL Server Connection String 
  
                                      string sqlConnectionString = DBUtils.DBString; 
                                      // Bulk Copy to SQL Server 
                                      using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString)) 
                                      { 
                      // Columns get copied from sheet and into bulk copy statement 
                                          logger.MyLogFile("" + DateTime.Now.ToString() + " Bulk Copy: ", "ID Number " + dr.GetValue(0).ToString() + ""); 
                                          // now that matrix is created, do the insert 
                                          bulkCopy.DestinationTableName = "Table"; 
                                          bulkCopy.WriteToServer(dr); 
                                          logger.MyLogFile("" + DateTime.Now.ToString() + " Bulk Copy: ", "All values inserted as burst"); 
                                      } 
                                  } 
                                  #endregion 
                                  i++; 
                              } 
                          } 
                          dt.Dispose(); 
                      } 
                      catch (Exception ex) 
                      { 
                          logger.MyLogFile("" + DateTime.Now.ToString() + " Importer \n", "Import failed, error code: " + ex.ToString() + ""); 
                      }                         
                      finally 
                      {                             
                          connection.Close(); 
                      } 
                  } 
              } 
  </code></pre>