C＃ - 如何以编程方式添加Excel Worksheet - 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异常，我的谷歌没有给我任何

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

我希望有人可能把我摆脱我的痛苦。

解决方案

你需要在您的项目中添加一个COM引用到 - 或任何适合的版本。

此代码适用于我：

  private void AddWorksheetToExcelWorkbook（string fullFilename，string worksheetName） 
 {
 Microsoft.Office.Interop.Excel.Application xlApp = null; 
工作簿xlWorkbook = null; 
表格xlSheets = null; 
工作表xlNewSheet = null; 
 
 try {
 xlApp = new Microsoft.Office.Interop.Excel.Application（）; 
 
 if（xlApp == null）
 return; 
 
 //如果要查看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; 
 
 //下面的第一个参数插入新的工作表作为第一个
 xlNewSheet =（Worksheet）xlSheets.Add（xlSheets [1]，Type.Missing，Type.Missing，Type。失踪）; 
 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问题是一个有用的经验法则：永远不要使用2点与COM对象。在你的代码中你会有真正的麻烦。

其他一些在查询此问题时，我发现有用的链接：

• 使用C＃打开和浏览Excel


• 如何使用COM Interop创建Excel电子表格（C＃编程指南）


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

根据MSDN

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

希望有所帮助。 / p>

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