Export to specific Excel worksheet name

Question

I am trying to export data to an Excel template. I have multiple tabs in the workbook. The worksheet tab I want to export to is called "Feasibility". Question is: how can I export to this specific worksheet name?

using Excel = Microsoft.Office.Interop.Excel;

//excel output variables
    private string excelFileName = SqlDB.GetFolderTemplates() + SqlDB.GetFileEngOrd();
    private static Excel.Application xlsApp;
    private static Excel.Workbooks workbooks;
    private static Excel.Workbook workbook;
    private Excel.Worksheet worksheet;

private void btnFeasibility_Click(object sender, EventArgs e)
    {
        xlsApp = new Excel.ApplicationClass();
        if (xlsApp == null)
        {
            MessageBox.Show(Constants.EXCEL_INSTALL);
            return;
        }

        try
        {

            xlsApp.Visible = true;
            workbooks = xlsApp.Workbooks;
            workbook = xlsApp.Workbooks.Open(excelFileName);
//PROBLEM IS HERE -- HOW CAN I GO TO THE WORKSHEET NAMED "FEASIBILITY"
            worksheet = (Excel.Worksheet)workbook.Sheets[1];
            worksheet.Select();

            worksheet.Cells[3, 4] = newEngOrd.CustName;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            //release excel
            System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
            worksheet = null;
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
            workbook = null;
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp);
            xlsApp = null;

            GC.GetTotalMemory(false);
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.GetTotalMemory(true);

            MessageBox.Show("Export  Complete");
        }

    }

I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Oct 23 '13 at 15:05

score 0 · Accepted Answer · edited May 23 '17 at 12:21

0

Run a for look through all the sheets, and see if the name match.

int foundNr=-1;
InteropExcel.Sheets sheets = workbook.Sheets;
InteropExcel.Sheet tempSheet = null;
for (int sheetIndex = 1; sheetIndex <= sheets.Count; sheetIndex++)
{
  tempSheet = (InteropExcel.Worksheet)sheets[sheetIndex];
  if (tempSheet.Name == "Feasibility")
  {
    foundNr = sheetIndex;
    Marshal.FinalReleaseComObject(tempSheet);
    tempSheet = null;        
    break
  }

  Marshal.FinalReleaseComObject(tempSheet);
  tempSheet = null;
}

if (foundNr != -1)
{
  sheet = (InteropExcel.Worksheet)sheets[foundNr];
}

The way I do release is to assign null to your COM variables. Then you call FinalReleaseComObject if it is not null in the finally statement. That way it gets released even if there is an exception in the method.

Excel process not closing

InteropExcel.Sheets sheets = null
try
{
  sheets = ....;
}
finally
{
  if (sheets != null)
  {
    Marshal.FinalReleaseComObject(sheets);
    sheets = null;
  }
}

edited May 23 '17 at 12:21

Community

1
1

answered Oct 23 '13 at 15:05

Gerhard Powell

5,965
5
48
59

this found the worksheet and exported to it. However, Excel.exe is still open and not closing. Any other thoughts? I don't want to do a kill on Excel.exe – maverick07281975 Oct 23 '13 at 16:16
The link covers most of the things you have to look out for. Summary: 1) Make sure you release ALL Interop variables. 2) Do not re-use/re-assign any variable. Release it first. 3) Do not use variable inside structure. (a.b = x; rather c = a.b; c = x; and then release c.) 3) Comment all your code and add back until it does not release. Then you know where your problem is. – Gerhard Powell Oct 23 '13 at 16:47
i ended up making a hashtable before exporting to excel of all the open excel process id's. After export I then kill the process id of the one that was not in the hashtable. not elegant, but it works. – maverick07281975 Oct 23 '13 at 18:15
It was a big job for me to find every single variable to release, but it released Excel without killing the thread, even for very complex Excel processing. I did something similar for killing the application / stop debugging. http://stackoverflow.com/questions/3342941/kill-child-process-when-parent-process-is-killed – Gerhard Powell Oct 23 '13 at 18:41

score 0 · Answer 2 · answered Oct 23 '13 at 15:15

0

did you tried the Name property? check this Worksheet documentation: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.worksheet_members.aspx

answered Oct 23 '13 at 15:15

André Leal

186
1
8

Export to specific Excel worksheet name

2 Answers2