This project has moved. For the latest updates, please go here.

How to get the sheet names in Excel?

Apr 17, 2013 at 9:49 AM
Well, i used in the beginning
Workbooks.Open 
to open the workbook with one sheet for data.
Afterwards added a template file (xltx) with
((Excel.Worksheet)myApp.ActiveWorkbook.Sheets[1]).Select(Type.Missing);
Curiously i just looked up the Sheet Names with
Console.WriteLine("Codename: " + ((Excel.Worksheet)myApp.ActiveWorkbook.Sheets[1]).Name);
Console.WriteLine("Codename: " + ((Excel.Worksheet)myApp.ActiveWorkbook.Sheets[2]).Name);
  1. Whats the cause for the identical names?
  2. Does anyone know how to select the sheets with the sheet names?
Apr 17, 2013 at 9:59 AM
            Excel.Application xlsApp = new Excel.Application();
            workbook = xlsApp.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                            Type.Missing, Type.Missing);
            int worksheetnumber = 1;
            Excel.Worksheet workSheet;                   
            foreach (Excel.Worksheet sheet in workbook.Sheets)
            {
                    workSheet = (Excel.Worksheet)xlsApp.Worksheets[worksheetnumber];
                    Console.WriteLine(workSheet.Name);
                    foreach (Excel.Range cell in workSheet.UsedRange)
                    {
                        int rowIndex = cell.Row;
                        int columnIndex = cell.Column;
                        // you can work with used range values
                    }
                    worksheetnumber++;
                }
Hope this might help you somehow.
Apr 17, 2013 at 10:38 AM
Edited Apr 17, 2013 at 10:53 AM
No, that can't work for me. After adding the template file to the worksheets i should have 2 worksheets. Indeed there are 2 worksheets.
But the operations to transform my data should be in first worksheet, which was opened with
Workbooks.Open 
I know how to select the sheets in VBA, but it doesn't work with C#.

But thank u for your code sample. I am just asking why do you use the same worksheetnumber? I appreciate that you wanted to help me ;)
Apr 17, 2013 at 11:34 AM
Edited Apr 17, 2013 at 11:35 AM
Why don't you do a copy of your original template?
File.Copy(originaltemplate, destinationoutput);
Keep the original, create a new one according from your template and only change what you need to be changed.

worksheetnumber helps me to go through each worksheets, that's why I increase it.
workSheet = (Excel.Worksheet)xlsApp.Worksheets[worksheetnumber];
worksheetnumber++;
workSheet is the current worksheet that I select, in your case, if you always do a modification on 1 worksheet, it should be
workSheet = (Excel.Worksheet)xlsApp.Worksheets[1];
But if you want only one specific worksheet to be changed, you can do it with a condition.
if (workSheet.Name="Test")
{
   <Do something>
   workSheet.Name="NewTest";
}
I was doing the same, opening a template and replacing images/tables/charts according to the one I import.
Apr 17, 2013 at 11:43 AM
Edited Apr 17, 2013 at 12:06 PM
I've solved it with
((Excel.Worksheet)cscExcelApp.ActiveWorkbook.Sheets["my_data"]).Select(Type.Missing);
My goal was to reference the sheets by name. I have to switch between the sheets to copy the block from the template to a third sheet and put the data in this copied block. Well, maybe i don't need 3 sheets for my needs, but i am still unsure.
Apr 17, 2013 at 11:50 AM
Hey Mike, do you know how to unset a defined style for specific cells in a range e.g. of B2:K2?