尝试添加多张创先争优 [英] Trying to add multiple sheets to excel

问题描述

我试图以编程方式表添加到一个新的Excel文档。

我的预期成果是名为test1-20表，而是我得到'Sheet1- 19，test20'。

为什么不这项工作？

 工作簿簿; 
应用objExcel; 
 
 objExcel =新的应用程序（）; 
 objExcel.Visible = FALSE; 
 objExcel.DisplayAlerts = FALSE; 
 
为（VAR I = 0; I< worksheets.Count;我++）
 {
 =工作簿objExcel.Workbooks.Add（Missing.Value）; 
变种表=（作业单）workbook.Worksheets.get_Item第（i + 1）; 
 worksheet.Name =的String.Format（测试{0}我+ 1）; 
} 
  

解决方案

试试这个：

 使用系统;使用System.Runtime.InteropServices 
;使用Excel =的Microsoft.Office.Interop.Excel 
; 
 
无效的MyMethod（）
 {
试
 {
变种_excel =新的Excel（）; 
 
变种WB = _excel.Workbooks.Add（）; 
变种集合=新Microsoft.Office.Interop.Excel.Worksheet [20]; 
 
为（VAR我= 19; I> = 0;我 - ）
 {
集合[I] = wb.Worksheets.Add（）; 
集合[I] .Name点=的String.Format（测试{0}我+ 1）;为（;我3;;变种I = 0 I ++）
} 
 
 
 {
 wb.Worksheets [21] .Delete（）; 
} 
 
 //收集工作表对象，
 //工作簿中的工作表对象的数组。 
 //可以访问每一个人工作和
 //工作，它在你的数组
 
 VAR thisWorksheet =集合[9]访问任何对象以相同的方式; 
 VAR thisRange = thisWorksheet.Range [A1]; 
 thisRange.Value =Hello World的; 
 
 wb.SaveAs（@C：\test\whatever.xlsx）; 
 wb.Close（）; 
} 
终于
 {
对Marshal.ReleaseComObject（_excel）; 
} 
} 
  

您visible属性默认设置为false，所以其时并不需要显式地做到这一点，没有警报显示在上面的代码，所以这个时并不需要任何。我已经测试了上面的代码，可以确认它的工作原理。

i'm trying to programmatically add sheets to a new excel document.

my expected output is sheets named 'test1-20' but instead i get 'Sheet1-19, test20'.

why doesn't this work?

Workbook workbook;
Application objExcel;

objExcel = new Application();
objExcel.Visible = false;
objExcel.DisplayAlerts = false;

for (var i = 0; i < worksheets.Count; i++)
{
    workbook= objExcel.Workbooks.Add(Missing.Value);
    var worksheet = (Worksheet)workbook.Worksheets.get_Item(i + 1);
    worksheet.Name = string.Format("test{0}", i + 1);
}

解决方案

Try this:

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

void MyMethod()
{
    try
    {
        var _excel = new Excel();

        var wb = _excel.Workbooks.Add();
        var collection = new Microsoft.Office.Interop.Excel.Worksheet[20];

        for (var i = 19; i >= 0; i--)
        {
            collection[i] = wb.Worksheets.Add();
            collection[i].Name = String.Format("test{0}", i + 1);
        }

        for (var i = 0; i < 3; i++)
        {
            wb.Worksheets[21].Delete();
        }

        //collection is an array of worksheet objects,
        //the worksheet objects in your workbook.
        //You can access each individual worksheet and
        //work with it in the same way you access any object in an array

        var thisWorksheet = collection[9];
        var thisRange = thisWorksheet.Range["A1"];
        thisRange.Value = "Hello World";

        wb.SaveAs(@"c:\test\whatever.xlsx");
        wb.Close();
    }
    finally
    {
        Marshal.ReleaseComObject(_excel);
    }
}

Your visible property is set to false by default, so it is not neccessary to do this explicitly, no alerts are displayed in the above code so this isn't neccessary either. I have tested the above code and can confirm it works.

这篇关于尝试添加多张创先争优的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！