1

I want to rename excel sheetname without using Microsoft.Office.Interop.Excel bec Ms Office is not installed on my server .

public void Main()
        {
            // TODO: Add your code here
            var path = Dts.Variables["User::FilePath"].Value.ToString();
            //Declare and initilize variables
            string fileFullPath = path.ToString();
            //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\"";
            OleDbConnection cnn = new OleDbConnection(ConStr);
            //Get Sheet Name
            cnn.Open();
            DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string sheetname;
            sheetname = "";
            //Only read data from provided SheetNumber
            dtSheet.Rows[0]["TABLE_NAME"]=Dts.Variables["User::WorksheetName"].Value.ToString();

           

            cnn.Close();
            Dts.TaskResult = (int)ScriptResults.Success;
        }

what i need to modify in my code to update the worksheet name with the value imported by the variable 'WorksheetName'.

Uppala Akash Rao IN
  • 11
  • 1
  • I'm pretty sure (but not entirely sure) this can't be done through OLE DB. There are better approaches to processing Excel sheets in any case (OLE DB is better than automation, but not *much*), though advocating any particular library is outside the scope of SO. – Jeroen Mostert Feb 17 '21 at 15:37
  • can you suggest me any approaches on how to update the sheet name – Uppala Akash Rao IN Feb 18 '21 at 04:36
  • An alternative could be to use the openXML framework [1]: https://learn.microsoft.com/en-us/office/open-xml/open-xml-sdk – Ockert Feb 22 '21 at 12:42

0 Answers0