C# 尝试将多个工作表添加到 excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12425746/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trying to add multiple sheets to excel
提问by CurlyFro
i'm trying to programmatically add sheets to a new excel document.
我正在尝试以编程方式将工作表添加到新的 Excel 文档中。
my expected output is sheets named 'test1-20' but instead i get 'Sheet1-19, test20'.
我的预期输出是名为“test1-20”的工作表，但我得到的是“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);
}
采纳答案by JMK
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.
默认情况下，您的可见属性设置为 false，因此没有必要显式执行此操作，上面的代码中没有显示警报，因此这也不是必需的。我已经测试了上面的代码并且可以确认它有效。
回答by vbstus
Here is my code that does this:
这是我执行此操作的代码：
' first worksheet
If oExcel.Application.Sheets.Count() < 1 Then
oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
Else
oSheet = oExcel.Worksheets(1)
End If
oSheet.Name = "one"
oSheet.Range("B1").Value = "First One"
' second
If oExcel.Application.Sheets.Count() < 2 Then
oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
Else
oSheet = oExcel.Worksheets(2)
End If
oSheet.Name = "two"
oSheet.Range("B1").Value = "Second one"
' third
If oExcel.Application.Sheets.Count() < 3 Then
oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
Else
oSheet = oExcel.Worksheets(3)
End If
oSheet.Name = "three"
oSheet.Range("B1").Value = "Thrid"
' next
If oExcel.Application.Sheets.Count() < 4 Then
oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
Else
oSheet = oExcel.Worksheets(4)
End If
oSheet.Name = "four"
oSheet.Range("B1").Value = "Four"
