Question

我正在开始使用C＃玩弄Excel，以便自动创建和添加到Excel文件。

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

我尝试了。

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

但是我遇到了COM异常，我搜索了很多但没有找到答案。

HRESULT 异常: 0x800A03EC 源自: "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;
    }
}

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

当我探索这个问题时，我发现以下几个链接也很有用：

根据MSDN

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

希望有所帮助。

Answer 2

想要感谢您提供的出色回复。 @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:	emp	est.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();
            }
        }
    }
}

谢谢您的帮助。

Answer 3

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) SpreadSheetGear is also excellent but pricey.

我的两分钱。

Answer 4

不要忘记包含对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;
}

Answer 5

您可以使用OLEDB创建和操作Excel文件。请参见此问题以获取链接和示例。

Answer 6

这是我想出来的几点事情：

你不能同时打开同一个对象的多个实例。例如，如果你实例化一个名为 xlsheet1 的新的Excel表对象，那么在创建另一个Excel表对象 ex xlsheet2之前，必须释放它。似乎COM会丢失对象的跟踪，并在服务器上留下僵尸进程。
使用与excel.workbooks相关联的打开方法，如果您有多个用户访问同一文件，则关闭也变得困难。相反，使用Add方法，它可以很好地工作而不锁定文件。例如：xlBook = xlBooks.Add("C:locationXlTemplate.xls")。
在释放COM对象之后，将垃圾收集放在一个单独的块或方法中。

Answer 7

“COM”绝对不是一个好的选择。特别是在处理网络环境时，这是不能接受的。

我已经成功使用以下开源项目：

ExcelPackage 用于 OOXML 格式（Office 2007）。
NPOI用于 .XLS 格式（Office 2003）

看一下这些博客文章：

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

使用Excel表格和动态图表的NPOI

Answer 8

这就是我曾经用来添加附加工作表的方法。

Workbook workbook = null;
Worksheet worksheet = null;

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

Worksheet additionalWorksheet = workbook.ActiveSheet;

Answer 9

我在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.

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

然后我终于意识到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);

友情链接