C# - 如何以编程方式添加 Excel 工作表 - Office XP/2003 [英] C# - How to add an Excel Worksheet programmatically - Office XP / 2003

问题描述

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

我可以打开文件并更新其数据并在现有工作表中移动.我的问题是如何添加新工作表?

我试过了:

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

但是我得到了 COM Exception 并且我的谷歌搜索没有给我任何答案.

<块引用>

来自 HRESULT 的异常:0x800A03EC 源是:Interop.Excel"

我希望有人能够让我摆脱痛苦.

解决方案

你需要在你的项目中添加一个COM引用到"Microsoft Excel 11.0 Object Library"- 或任何合适的版本.

此代码适用于我:

private void AddWorksheetToExcelWorkbook(string fullFilename,string worksheetName){Microsoft.Office.Interop.Excel.Application xlApp = null;工作簿 xlWorkbook = null;表格 xlSheets = null;工作表 xlNewSheet = null;尝试 {xlApp = new Microsoft.Office.Interop.Excel.Application();如果(xlApp == null)返回;//如果您想查看 Excel 中发生的情况，请取消注释下面的行//xlApp.Visible = true;xlWorkbook = xlApp.Workbooks.Open(fullFilename, 0, false, 5, "", "",假，XlPlatform.xlWindows，"，真、假、0、真、假、假)；xlSheets = xlWorkbook.Sheets 作为工作表；//下面的第一个参数将新工作表作为第一个插入xlNewSheet = (工作表)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);xlNewSheet.Name = 工作表名称；xlWorkbook.Save();xlWorkbook.Close(Type.Missing,Type.Missing,Type.Missing);xlApp.Quit();}最后 {Marshal.ReleaseComObject(xlNewSheet);Marshal.ReleaseComObject(xlSheets);Marshal.ReleaseComObject(xlWorkbook);Marshal.ReleaseComObject(xlApp);xlApp = null;}}

<块引用>

请注意，您要非常小心 正确清理和释放您的 COM 对象引用.StackOverflow 问题中包含一条有用的经验法则:永远不要对 COM 对象使用 2 个点".在您的代码中；你会遇到真正的麻烦.我上面的演示代码没有正确清理 Excel 应用程序，但这是一个开始！

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

• 使用 C# 打开和导航 Excel
• 如何:使用 COM 互操作创建 Excel电子表格(C# 编程指南)
• 如何:将新工作表添加到工作簿

根据 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 its 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 below COM Exception and my googling has not given me any answer.

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

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

解决方案

You need to 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 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!

Some other links that 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 工作表 - Office XP/2003的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！