Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I Change the Sheet Name from C# on an Excel Spreadsheet

Tags:

c#

excel

I have a C# application where I am creating numerous Excel Files from Data in a Database. This part is working fine. However, my user asked if the sheet tab could be modified to reflect a field from the database. This sounds simple, however, when I try to reset the name, it tells me that it is read only and cannot be set. I have tried the following and it has not worked:

xlApp.Sheets[0].Range["A1"].Value = "NewTabName";

ALSO TRIED:

xlApp.Name = "NewTabName";

I did a google search and saw some other approaches which did not work for me as well. And a few responses indicated that it is readonly and could not be done.

This seems like something that should be simple. How can I do it.

like image 858
Steve Goedker Avatar asked Dec 01 '09 21:12

Steve Goedker


People also ask

How do I change the sheet language in Excel?

Open Excel, go to the File tab, and choose Options, and then Language. Check if the language that you want is installed under Office Display Language. You will get a window with a list of languages, you can choose the language that you need.

Why can't I rename my Excel sheet?

However you can rename the sheet tabs when the worksheet is protected. If you have used the protect workbook (protect workbook for structure) option then you will not be able to rename sheet tabs.


2 Answers

You need to get access to the actual worksheet. Try something like:

  Microsoft.Office.Interop.Excel.Worksheet worksheet = (Worksheet)xlApp.Worksheets["Sheet1"];
  worksheet.Name = “NewTabName”;
like image 62
mikemurf22 Avatar answered Sep 27 '22 22:09

mikemurf22


Here is a fairly complete example I am copying in from existing code.

Works perfectly on Windows 10 with Excel from Office 365

Ensure you add a reference to - Microsoft.Office.Interop.Excel

My path for this DLL (may differ depending on office version) - C:\WINDOWS\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll

// Add this at top of C# file -
using Excel = Microsoft.Office.Interop.Excel;

// In your class or function -
private static Excel.Application XlApp = null;
private static Excel.Workbook XlWorkbook = null;

// In your function -
XlApp = new Excel.ApplicationClass();

// Load workbook
XlWorkbook = XlApp.Workbooks.Open(@"Filename.xls",
    0, false, Type.Missing, "", "", true, Excel.XlPlatform.xlWindows,
    Type.Missing, Type.Missing, Type.Missing,
    Type.Missing, true, Type.Missing,
    Type.Missing);

// Get reference to sheet
XlWorksheet = (Excel.Worksheet)XlWorkbook.Worksheets["Sheet1"];
                        
int numsheets = XlWorkbook.Sheets.Count;

// iterates through all sheets (1-n inclusive, not zero based)
for(int i=1;i<=numsheets;i++)
{
    Excel.Worksheet sht = (Excel.Worksheet)XlWorkbook.Worksheets[i];
    
    // Show sheet name
    Console.WriteLine(i+" "+sht.Name);
}

// To save with a same or different filename
XlWorkbook.SaveAs(@"Filename.xls",
    Excel.XlFileFormat.xlWorkbookNormal, "",
    "", false, false,
    Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
    true, Type.Missing, Type.Missing, Type.Missing);

// Close Excel  
XlWorkbook.Close(true, Type.Missing, Type.Missing);
XlApp.Quit();

// Ensure you release resources
releaseObject(XlApp);
releaseObject(XlWorkbook);
releaseObject(XlWorksheet);

Separate function called from above

private static void releaseObject(object obj)
{
  // try .. catch
  System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
}
like image 28
tonyb Avatar answered Sep 27 '22 21:09

tonyb