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.
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.
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.
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”;
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);
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With