C# - 如何以编程方式添加 Excel 工作表 - Office XP/2003

Question

我刚刚开始通过 C# 摆弄 Excel，以便能够自动创建和添加到 Excel 文件。

我可以打开文件并更新其数据并浏览现有工作表。我的问题是如何添加新工作表？

我试过:

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

但我遇到了 COM 异常，我的谷歌搜索没有给我任何答案。

Exception from HRESULT: 0x800A03EC Source is: "Interop.Excel"

我希望有人能把我从痛苦中解救出来。

Answer 1

您需要在项目中将 COM 引用添加到 "Microsoft Excel 11.0 Object Library" - 或任何合适的版本。

这段代码对我有用:

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 code; 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!

我在研究这个问题时发现一些其他有用的链接:

• 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

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

希望对您有所帮助。