Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
335 views
in Technique[技术] by (71.8m points)

c# - Excel Interop - Add a new worksheet after all of the others

I am trying to add a new worksheet to an Excel workbook and make this the last worksheet in the book in C# Excel Interop.

It seems really simple, and I thought the below code would do it:

using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            var excel = new Excel.Application();

            var workbook = excel.Workbooks.Open(@"C:estTest.xlsx");
            workbook.Sheets.Add(After: workbook.Sheets.Count);

            workbook.Save();
            workbook.Close();

            Marshal.ReleaseComObject(excel);
        }
    }
}

No such luck. I get this helpful error:

COMException was unhandled - Exception from HRESULT: 0x800A03EC

I found this page on Microsoft.com which suggested I try and add the sheet first and then move it so I tried that as shown below. I know that this webpage targets Excel 95 but the VBA is still there to use so I was hoping it would still work:

using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            var excel = new Excel.Application();

            var workbook = excel.Workbooks.Open(@"C:estTest.xlsx");
            workbook.Sheets.Add();
            workbook.Sheets.Move(After: workbook.Sheets.Count);

            workbook.Save();
            workbook.Close();

            Marshal.ReleaseComObject(excel);
        }
    }
}

I get the same error as above. I have also tried passing the name of my last worksheet as a string as the After parameter in both the Add and Move methods, no joy!

That is what I have tried, so my question is how do I add a worksheet to an Excel workbook and make this the last sheet in the workbook using C# Excel Interop?

Thanks

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Looking at the documentation here http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.move(v=vs.80).aspx, it indicates that the 'after' object isn't a numerical position; it's the object representing the sheet you want to position your sheet after. The code should probably be something like (untested):

workbook.Sheets.Add(After: workbook.Sheets[workbook.Sheets.Count]); 

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...