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

C# - How to add an Excel Worksheet programmatically - Office XP / 2003

提问by Jon

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

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

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 Exceptionand my googling has not given me any answer.

但是我低于COM Exception并且我的谷歌搜索没有给我任何答案。

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

HRESULT 的异常：0x800A03EC 来源是：“Interop.Excel”

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

我希望有人能让我摆脱痛苦。

采纳答案by AR.

You need to add a COM reference in your project to the "Microsoft Excel 11.0 Object Library"- or whatever version is appropriate.

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

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!

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

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

• 使用 C# 打开和导航 Excel
• 如何：使用 COM Interop 创建 Excel 电子表格（C# 编程指南）
• 如何：将新工作表添加到工作簿

According to MSDN

根据 MSDN

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

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

Hope that helps.

希望有帮助。

回答by Panos

You can use OLEDB to create and manipulate Excel files. See this questionfor links and samples.

您可以使用 OLEDB 来创建和操作 Excel 文件。有关链接和示例，请参阅此问题。

回答by sbeskur

Another "Up Tick" for AR..., but if you don't have to use interop I would avoid it altogether. This product is actually quite interesting: http://www.clearoffice.com/and it provides a very intuitive, fully managed, api for manipulation excel files and seems to be free. (at least for the time being) SpreadSheetGearis also excellent but pricey.

AR 的另一个“Up Tick”...，但如果您不必使用互操作，我会完全避免它。这个产品实际上很有趣：http: //www.clearoffice.com/它提供了一个非常直观、完全托管的 api 用于操作 excel 文件，而且似乎是免费的。（至少就目前而言） SpreadSheetGear也很出色，但价格昂贵。

my two cents.

我的两分钱。

回答by Jon

Would like to thank you for some excellent replies. @AR., your a star and it works perfectly. I had noticed last night that the Excel.exewas not closing; so I did some research and found out about how to release the COM objects. Here is my final code:

非常感谢您的一些精彩回复。@AR.，你的明星，它工作得很好。我昨晚注意到Excel.exe没有关闭；所以我做了一些研究，发现了如何释放 COM 对象。这是我的最终代码：

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.IO;
using Excel;

namespace testExcelconsoleApp
{
    class Program
    {
        private String fileLoc = @"C:\temp\test.xls";

        static void Main(string[] args)
        {
            Program p = new Program();
            p.createExcel();
        }

        private void createExcel()
        {
            Excel.Application excelApp = null;
            Excel.Workbook workbook = null;
            Excel.Sheets sheets = null;
            Excel.Worksheet newSheet = null;

            try
            {
                FileInfo file = new FileInfo(fileLoc);
                if (file.Exists)
                {
                    excelApp = new Excel.Application();
                    workbook = excelApp.Workbooks.Open(fileLoc, 0, false, 5, "", "",
                                                        false, XlPlatform.xlWindows, "",
                                                        true, false, 0, true, false, false);

                    sheets = workbook.Sheets;

                    //check columns exist
                    foreach (Excel.Worksheet sheet in sheets)
                    {
                        Console.WriteLine(sheet.Name);
                        sheet.Select(Type.Missing);

                        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                    }

                    newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                    newSheet.Name = "My New Sheet";
                    newSheet.Cells[1, 1] = "BOO!";

                    workbook.Save();
                    workbook.Close(null, null, null);
                    excelApp.Quit();
                }
            }
            finally
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

                newSheet = null;
                sheets = null;
                workbook = null;
                excelApp = null;

                GC.Collect();
            }
        }
    }
}

Thank you for all your help.

谢谢你的帮助。

回答by hmm

Here are a couple things I figured out:

以下是我想出的几件事：

1. You can't open more than one instance of the same object at the same time. For Example if you instanciate a new excel sheet object called xlsheet1you have to release it before creating another excel sheet object ex xlsheet2. It seem as COM looses track of the object and leaves a zombie process on the server.

2. Using the open method associated with excel.workbooksalso becomes difficult to close if you have multiple users accessing the same file. Use the Add method instead, it works just as good without locking the file. eg. xlBook = xlBooks.Add("C:\location\XlTemplate.xls")

3. Place your garbage collection in a separate block or method after releasing the COM objects.

1. 您不能同时打开同一个对象的多个实例。例如，如果您实例化一个名为的新 Excel 工作表对象xlsheet1，则必须在创建另一个 Excel 工作表对象 ex 之前释放它xlsheet2。似乎 COM 失去了对对象的跟踪，并在服务器上留下了一个僵尸进程。

2. excel.workbooks如果您有多个用户访问同一个文件，使用 关联的打开方法也变得难以关闭。改用 Add 方法，它在不锁定文件的情况下也能正常工作。例如。xlBook = xlBooks.Add("C:\location\XlTemplate.xls")

3. 释放 COM 对象后，将垃圾回收放在单独的块或方法中。

回答by Leniel Maccaferri

COMis definitely not a good way to go. More specifically, it's a no go if you're dealing with web environment...

COM绝对不是一个好方法。更具体地说，如果您正在处理网络环境，这是不行的……

I've used with success the following open source projects:

我成功地使用了以下开源项目：

• ExcelPackage for OOXML formats (Office 2007)

• NPOI for .XLS format (Office 2003)

• OOXML 格式的 ExcelPackage (Office 2007)

• .XLS 格式的 NPOI (Office 2003)

Take a look at these blog posts:

看看这些博客文章：

Creating Excel spreadsheets .XLS and .XLSX in C#

在 C# 中创建 Excel 电子表格 .XLS 和 .XLSX

NPOI with Excel Table and dynamic Chart

带有 Excel 表格和动态图表的 NPOI

回答by Gokul

Do not forget to include Referenceto Microsoft Excel 12.0/11.0 object Library

不要忘记，包括参考，以Microsoft Excel 12.0/11.0 object Library

using Excel = Microsoft.Office.Interop.Excel;
// Include this Namespace

Microsoft.Office.Interop.Excel.Application xlApp = null;
Excel.Workbook xlWorkbook = null;
Excel.Sheets xlSheets = null;
Excel.Worksheet xlNewSheet = null;
string worksheetName ="Sheet_Name";
object readOnly1 = false;

object isVisible = true;

object missing = System.Reflection.Missing.Value;

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(@"C:\Book1.xls", missing, readOnly1, missing, missing, missing, missing, missing, missing, missing, missing, isVisible, missing, missing, missing);

    xlSheets = (Excel.Sheets)xlWorkbook.Sheets;

    // The first argument below inserts the new worksheet as the first one
    xlNewSheet = (Excel.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
{
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlNewSheet);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheets);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
    //xlApp = null;
}

回答by Dobermaxx99

This is what i used to add addtional worksheet

这是我用来添加附加工作表的内容

Workbook workbook = null;
Worksheet worksheet = null;

workbook = app.Workbooks.Add(1);
workbook.Sheets.Add();

Worksheet additionalWorksheet = workbook.ActiveSheet;

回答by Jiri Tersel

I had a similar problem application-level add-in in VSTO, the exception HRESULT: 0x800A03EC when adding new sheet.

我在 VSTO 中有一个类似的问题应用程序级加载项，添加新工作表时出现异常 HRESULT: 0x800A03EC。

The error code 0x800A03EC (or -2146827284) means NAME_NOT_FOUND; in other words, you've asked for something, and Excel can't find it.

错误代码 0x800A03EC（或 -2146827284）表示 NAME_NOT_FOUND；换句话说，您要求了某些东西，而 Excel 找不到它。

Dominic Zukiewicz @ Excel error HRESULT: 0x800A03EC while trying to get range with cell's name

Dominic Zukiewicz @ Excel 错误 HRESULT: 0x800A03EC 尝试使用单元格名称获取范围时

Then I finally realized ThisWorkbooktriggered the exception. ActiveWorkbookwent OK.

然后我终于意识到ThisWorkbook触发了异常。ActiveWorkbook 运行正常。

Excel.Worksheet newSheetException = Globals.ThisAddIn.Application.ThisWorkbook.Worksheets.Add(Type.Missing, sheet, Type.Missing, Type.Missing);
Excel.Worksheet newSheetNoException = Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets.Add(Type.Missing, sheet, Type.Missing, Type.Missing);