如何在Excel文件c#中创建一个新的工作表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16705651/
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
How to create a new worksheet in Excel file c#?
提问by Bryuk
I need to create a very big Excel file, but excel file in one worksheet can contain up to 65k rows. So, i want to divide all my info into several worksheets dynamical. This is my approximate code
我需要创建一个非常大的 Excel 文件，但一个工作表中的 excel 文件最多可以包含 65k 行。所以，我想将我所有的信息动态地分成几个工作表。这是我的大概代码
//------------------Create Excel App--------------------
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(numberOfLetters);
foreach (string letter in letters)
{
xlWorkSheet.Cells[rowIndex, 1] = letter;
rowIndex++;
}
xlWorkBook.SaveAs(pathXL, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
How I can add new worksheet inside of my foreach loop and using some condition give a name to worksheet (which user can see in Excel at the bottom of the page in list)?
如何在我的 foreach 循环中添加新工作表并使用某些条件为工作表命名（用户可以在列表页面底部的 Excel 中看到该名称）？
Some like that
有些像这样
foreach (string letter in letters)
{
if (letter == SOME)
{
AddNewWorksheet and give name SOME
}
xlWorkSheet.Cells[rowIndex, 1] = letter;
rowIndex++;
}
And how to save all worksheets at the end?
最后如何保存所有工作表？
采纳答案by Andrey Gordeev
To add a new worksheet to the workbook use this code:
要将新工作表添加到工作簿，请使用以下代码：
var xlSheets = xlWorkBook.Sheets as Excel.Sheets;
var xlNewSheet = (Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
xlNewSheet.Name = "newsheet";
// Uncomment a line below if you want the inserted sheet to be the last one
//xlWorkBook.Sheets.Move(After: xlWorkBook.Sheets.Count);
To save the workbook call Save()method:
要保存工作簿调用Save()方法：
xlWorkBook.Save();
回答by Tom Stickel
You are limited to 65,000 records with .xls , but if you are "allowed" to step beyond .xls / 2003 and into 2007 and above with .xlsx you should have a lot more rows.
使用 .xls 限制为 65,000 条记录，但如果“允许”使用 .xls / 2003 超出 .xls / 2003 并进入 2007 年及更高版本，则应该有更多行。
Side note, nothing to do with your question, but a while back I had export to excel issues with RDLC and sheet names I renamed using NPOI library, since then I started using NPOI a lot more it is free /open source and very powerful (ported from Java POI to .net NPOI) again while I say it is not really a part of what your question is I wouldn't be surprised if it had examples on doing this (no I don't work for them ) http://npoi.codeplex.com/
旁注，与您的问题无关，但不久前，我使用 NPOI 库重命名了 RDLC 和工作表名称的导出到 excel 问题，从那时起我开始更多地使用 NPOI，它是免费/开源且非常强大（再次从 Java POI 移植到 .net NPOI）虽然我说这不是你问题的真正一部分，如果它有这样做的例子我不会感到惊讶（不，我不为他们工作） http：/ /npoi.codeplex.com/
Here is the code I had written for renaming sheets (which ends up re-creating the sheets with another memorystream
这是我为重命名工作表而编写的代码（最终用另一个内存流重新创建工作表
------------------------------------------------------------
var excelHelper = new ExcelHelper(bytes);
bytes = excelHelper.RenameTabs("Program Overview", "Go Green Plan", "Milestones", "MAT and EOC", "Annual Financials", "Risk Log", "Risk & Opportunity Log", "RAIL", "Meeting Minutes");
Response.BinaryWrite(bytes);
Response.End();
------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using NPOI.HSSF.UserModel;
namespace Company.Project.Core.Tools
{
public class ExcelHelper
{
private byte[] _ExcelFile;
public ExcelHelper(byte[] excelFile)
{
_ExcelFile = excelFile;
}
public byte[] RenameTabs(params string[] tabNames)
{
byte[] bytes = null;
using (MemoryStream ms = new MemoryStream())
{
ms.Write(_ExcelFile, 0, _ExcelFile.Length);
var workBook = new HSSFWorkbook(ms, true);
if (tabNames != null)
{
using (MemoryStream memoryStream = new MemoryStream())
{
for (int i = 0; i < tabNames.Length; i++)
{
workBook.SetSheetName(i, tabNames[i]);
}
workBook.Write(memoryStream);
bytes = memoryStream.ToArray();
}
}
}
_ExcelFile = bytes;
return bytes;
}
}
回答by Chamath Jeevan
This is the correct code that is given in MSDN.
这是 MSDN 中给出的正确代码。
Excel.Worksheet newWorksheet;
newWorksheet = (Excel.Worksheet)Globals.ThisWorkbook.Worksheets.Add(
missing, missing, missing, missing);
回答by AmiNadimi
In General when you want to create new sheet just do :
一般来说，当您想创建新工作表时，只需执行以下操作：
var worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add();
knowing that you already created the workbook like below :
知道您已经创建了如下工作簿：
var workbook = excel.Workbooks.Add(Type.Missing);
