C# Check for a WorkSheet name of an excel file and rename it

Question

I need help in writing a C# code which basically should take excel file as input and check if the excel file contains a specific worksheet or tab and if yes rename that tab with another name. The program should support .net 3.5 and excel 2007. Please provide me some examples. Thanks in advance.

Just a quick note - Don't forget, if you are using a static name as the renamed sheet, it is not guaranteed that another sheet in the book doesn't already have that name. Something similar bit me once, so I thought I'd mention it. :) — ZombieSheep, Apr 04 '11 at 13:03

score 5 · Answer 1 · answered Apr 04 '11 at 13:01

5

Application excel = new Application();
Workbook wb = excel.Workbooks.Open(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

    string lookFor="TabName1";

    foreach (Microsoft.Off.Interop.Excel.Worksheet w in wb.Worksheets)
     {
       if (w.Name == lookFor)
         //match exists rename
         w.Name = lookFor + "_renamed";
     }

answered Apr 04 '11 at 13:01

JonH

32,732
12
87
145

As per ZombieSheep's comment above, you need to verify that the new name doesn't already exist in the workbook. – Peter M Apr 04 '11 at 13:26
@ Peter and ZombieSheep....Yes you are 100% correct i will try to loop through the above code and try to give guide lines to the users explaining them the situation..... – S.. Apr 04 '11 at 13:47
John....The code is working fine, but when i open the file, its saying the file is locked for editing by "S.."(which is my name in my system). I understand the program internally opens the file to rename but how do we close that. – S.. Apr 04 '11 at 16:44
@S.. - When you open the file make sure when you are done with it to close the workbook `wb.Close` and `excel.Quit` `excel=null` – JonH Apr 04 '11 at 17:00
@John..This below code solved the problem GC.Collect(); GC.WaitForPendingFinalizers(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(WB); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_Excel); – S.. Apr 04 '11 at 17:20
Ahh yes there was a stack overflow thread about that one time. You might want to post the link to that. – JonH Apr 04 '11 at 17:22

score 3 · Answer 2 · answered Apr 04 '11 at 13:01

3

I'm using EPPlus for all Excel-related code.

answered Apr 04 '11 at 13:01

Jakub Konecki

45,581
7
87
126

I'm also using EPPlus and like it as well. – Peter M Apr 04 '11 at 13:24

score 1 · Accepted Answer · edited May 23 '17 at 10:28

1

Full length code From one of the stack over posts

edited May 23 '17 at 10:28

Community

1
1

answered Apr 04 '11 at 17:27

S..

1,242
8
29
49

C# Check for a WorkSheet name of an excel file and rename it

3 Answers3