C＃如何添加Excel工作表编程的Office XP / 2003 [英] C# how to add Excel Worksheet programatically Office XP / 2003

问题描述

我刚开始通过C＃与Excel摆弄要能够自动创建，并且除了一个Excel文件。

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?

我试过：

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

但我得到一个COM异常，我的谷歌搜索没有给我一个答案。

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

我希望有人也许能够把我赶出missery的。

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

推荐答案

如果你还没有，你必须在你的项目添加到Microsoft Excel中11.0对象库COM引用 - 或任何版本为宜。

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.

这code对我的作品...

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;
        }
    }

这要非常小心<一注href=\"http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c\">properly清理和释放您的COM对象引用。包括在StackOverflow的问题是经验的一个有用的规则：不要使用2点与COM对象。在您的例子code以上你将有真正的麻烦。的我的演示code以上没有正确清理Excel的应用程序，但它是一个开始！的

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:

• 开放和导航的Excel使用C＃


• 如何：使用COM互操作创建一个Excel US preadsheet（ C＃编程指南）


• 如何添加新的工作表到工作簿

• 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

根据MSDN

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

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

希望有所帮助。

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