Fetching excel sheets in script task SSIS using C# and renaming them

Question

I am a novice in C#. but I am working on a SSIS C# script which has to deal with list of files and rename sheets of each file. I have a list of excel files it can be .xls or .xlsx format. I am writing a c# script in SSIS script task to loop through the file names and sheet names and want to rename the sheet names with some hard coded values for example: a folder has file1.xls : sheet 1,sheet2, sheet3 ,file2.xls : sheet 1, sheet2 ,file3.xls : sheet1, sheet2, sheet3,sheet4

So, every time if a sheet name called sheet1 is available it should be renamed as ABC, sheet2 is available it should be DEF , if the one of the file has sheet3 missing it should be ignored. I have written a piece of code where I got stuck at fetching the sheet names. Please help me in fixing the code or suggest a better idea, if you have any

 

using System.IO; using System.Data.OleDb; using System.Data.SqlClient;  Added this in Main method  public void Main() { // TODO: Add your code here String FolderPath = Dts.Variables["User::SourceFolderPath"].Value.ToString(); String fileFullPath = ""; var directory = new DirectoryInfo(FolderPath); FileInfo[] files = directory.GetFiles(); foreach (FileInfo file in files) {                 fileFullPath = FolderPath + "\\" + file.Name; //Create Excel Connection string ConStr; string HDR;                 HDR = "YES"; ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\""; string sheetname;                 sheetname = ""; #region ScriptResults declaration /// <summary> /// This enum provides a convenient shorthand within the scope of this class for setting the /// result of the script. ///  /// This code was generated automatically. /// </summary> enum ScriptResults { Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure }; #endregion