Delete 3rd column in excel sheets using SSIS package

Question

Hi,

I have 10 Excel files in a folder D:\Excel_Files\ (file_1.xlsx, File_2.xlsx ......and so on)

Each excel file has one sheet. The name of the sheet is same as file name.

This is what I wanted to do;
The 3rd column of all 10 excel sheets needs to be deleted. I am using Microsoft Visual studio 2010 and I want a script task code which can delete the 3rd column of all 10 excel sheets.

thanks in advance.

Sajid

Answer 1

Hi @SAJID RAZA KHAN ,

Please check below code.

string[] files = Directory.GetFiles(@"C:\Test", "*.xlsx");  
foreach (var item in files)  
{  
Excel.Application excel = new Excel.Application();  
Excel.Workbook workbook = excel.Workbooks.Open(item);  
Excel.Worksheet worksheet = workbook.Worksheets["Sheet1"];  
worksheet.Columns[3].Delete();  
workbook.Save();  
Console.WriteLine("yes");  
}  

Don't forget to add Microsoft.Office.Interop.Excel in NuGet package.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.