C＃如何以编程方式添加Excel Worksheet Office XP / 2003 [英] C# how to add Excel Worksheet programatically Office XP / 2003

问题描述

我可以打开文件并更新数据并移动通过现有工作表。我的问题是如何添加新工作表？

我试过：

  Excel.Worksheet newWorksheet; 
 newWorksheet =（Excel.Worksheet）excelApp.ThisWorkbook.Worksheets.Add（
 Type.Missing，Type.Missing，Type.Missing，Type.Missing）; 
  

但是我得到了一个COM异常，我的Google搜索没有给我答案。

$

解决方案

如果有人可能会把我从错误中解救出来你还没有，你必须在你的项目中添加一个COM引用到Microsoft Excel 11.0对象库 - 或任何版本是合适的。

为我...

  private void AddWorksheetToExcelWorkbook（string fullFilename，string worksheetName）
 {
 Microsoft。 Office.Interop.Excel.Application xlApp = null; 
 Workbook xlWorkbook = null; 
 Sheets xlSheets = null; 
工作表xlNewSheet = null; 
 
 try {
 xlApp = new Microsoft.Office.Interop.Excel.Application（）; 
 
 if（xlApp == null）
 return; 
 
 //如果你想看看在Excel中发生了什么，取消注释下面的行
 // xlApp.Visible = true; 
 
 xlWorkbook = xlApp.Workbooks.Open（fullFilename，0，false，5，，，
 false，XlPlatform.xlWindows，，
 true，false ，0，true，false，false）; 
 
 xlSheets = xlWorkbook.Sheets as Sheets; 
 
 //下面的第一个参数插入新的工作表作为第一个
 xlNewSheet =（Worksheet）xlSheets.Add（xlSheets [1]，Type.Missing，Type.Missing，Type。失踪）; 
 xlNewSheet.Name = worksheetName; 
 
 xlWorkbook.Save（）; 
 xlWorkbook.Close（Type.Missing，Type.Missing，Type.Missing）; 
 xlApp.Quit（）; 
} 
 finally {
 Marshal.ReleaseComObject（xlNewSheet）; 
 Marshal.ReleaseComObject（xlSheets）; 
 Marshal.ReleaseComObject（xlWorkbook）; 
 Marshal.ReleaseComObject（xlApp）; 
 xlApp = null; 
} 
} 
  

请注意，您要非常小心正确清理和释放COM对象引用。包括在StackOverflow问题是一个有用的经验规则：从不使用2点与COM对象。在你的示例代码中，你会有真正的麻烦。

我在上面的示例代码并没有正确清理Excel应用程序，问题：

• 使用C＃打开和浏览Excel




• 如何：将新工作表添加到工作簿

根据MSDN

要使用COM互操作，必须有
管理员或高级用户安全
权限。

希望有帮助。

I am just starting to fiddle with Excel via C# to be able to automate the creation, and addition to an Excel file.

I can open the file and update the data and move through the existing worksheets. My problem is how can I add new sheets?

I tried:

Excel.Worksheet newWorksheet;
newWorksheet = (Excel.Worksheet)excelApp.ThisWorkbook.Worksheets.Add(
                Type.Missing, Type.Missing, Type.Missing, Type.Missing);

But I get a COM Exception and my googling has not given me an answer.

I am hoping someone maybe able to put me out of my missery.

解决方案

If you don't already, you must have add a COM reference in your project to the "Microsoft Excel 11.0 Object Library" - or whatever version is appropriate.

This code works for me...

    private void AddWorksheetToExcelWorkbook(string fullFilename,string worksheetName)
    {
        Microsoft.Office.Interop.Excel.Application xlApp = null;
        Workbook xlWorkbook = null;
        Sheets xlSheets = null;
        Worksheet xlNewSheet = null;

        try {
            xlApp = new Microsoft.Office.Interop.Excel.Application();

            if (xlApp == null)
                return;

            // Uncomment the line below if you want to see what's happening in Excel
            // xlApp.Visible = true;

            xlWorkbook = xlApp.Workbooks.Open(fullFilename, 0, false, 5, "", "",
                    false, XlPlatform.xlWindows, "",
                    true, false, 0, true, false, false);

            xlSheets = xlWorkbook.Sheets as Sheets;

            // The first argument below inserts the new worksheet as the first one
            xlNewSheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
            xlNewSheet.Name = worksheetName;

            xlWorkbook.Save();
            xlWorkbook.Close(Type.Missing,Type.Missing,Type.Missing);
            xlApp.Quit();
        }
        finally {
            Marshal.ReleaseComObject(xlNewSheet);
            Marshal.ReleaseComObject(xlSheets);
            Marshal.ReleaseComObject(xlWorkbook);
            Marshal.ReleaseComObject(xlApp);
            xlApp = null;
        }
    }

Note that you want to be very careful about properly cleaning up and releasing your COM object references. Included in that StackOverflow question is a useful rule of thumb: "Never use 2 dots with COM objects". In your example code above you're going to have real trouble with that. My demo code above does NOT properly clean up the Excel app, but it's a start!

Some other links I found useful when looking into this question:

• Opening and Navigating Excel with C#
• How to: Use COM Interop to Create an Excel Spreadsheet (C# Programming Guide)
• How to: Add New Worksheets to Workbooks

According to MSDN

To use COM interop, you must have administrator or Power User security permissions.

Hope that helps.

这篇关于C＃如何以编程方式添加Excel Worksheet Office XP / 2003的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！