Opening MS Excel worksheet in C#

2
votes

I know we can open a particular worksheet of MS Excel using C# by providing the sheet number (1,2,3..) or name (sheet1, sheet2, sheet3...)

I have a excel file which has 2 sheets, 1. Values, 2. Results

Is there a way to open a sheet giving the sheet name ,i.e, "Values" instead of 1 or [s|S]heet1 in C# ??

I looked thru the old posts but didnt find anything useful...

so again, what I am trying to do is, open a Excel worksheet by using its user defined name (Values) instead of the system defined name(1 or [s|S]heet1)

any inputs would be greatly appreciated!

2
c#excel

2 Answers

3
votes

Add reference to Microsoft.Office.Interop.Excel in your project and you can use next code as base

Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;

//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = false;
oXL.DisplayAlerts = false; // prevents message from popping up

try
{

    //Get a new workbook.
    oWB = (Excel._Workbook)(oXL.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, Type.Missing, Type.Missing));

    oSheet = (Excel._Worksheet)oWB.ActiveSheet;

    int nCounter = 1;
    oSheet.Copy(oSheet, Type.Missing);
    Excel._Worksheet oSheetGame = (Excel._Worksheet)oWB.Worksheets["MyTemplate"];
    oSheetGame.Name = "MyNewWorksheetName";
    // do something with worksheet

    ((Excel._Worksheet)oWB.Sheets["MyTemplate"]).Delete(); // delete template
    ((Excel._Worksheet)oWB.Worksheets["MyNewWorksheetName"]).Activate();

}
catch (Exception e)
{
    //throw e;
    throw;
}
finally
{
    //Make sure Excel is visible and give the user control
    //of Microsoft Excel's lifetime.
    oXL.Visible = true;
    oXL.UserControl = true;
}

oXL.Save(Type.Missing);
0
votes

@volody, thanks! your post helped a lot. I took the worksheet declaration part from yours and changed mine into following:

        Excel.Application excelApplication;
        Excel.Worksheet excelWorksheet;
        Excel.Workbook excelWorkbook;
        Excel.Range range;

        excelApplication = new Excel.Application();

        excelWorkbook = (Excel.Workbook)(excelApplication.Workbooks.Open(
            <FILENAME>,
            Type.Missing, true, Type.Missing, Type.Missing, Type.Missing,
            true, Type.Missing, Type.Missing, false, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing));

        excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[<WORKSHEETNAME>];

The code above helps in opening a Excel Worksheet with a User defined name. For example, to open a Worksheet named Test instead of Sheet1.

  • Ivar