How to create a new worksheet in Excel file c#?

Question

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

 //------------------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)?

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?

Excel 2010 supports 1 million rows. Which version are you on? — Mitch Wheat, May 23 '13 at 04:26
But even if i will save in 2010 version my question is still makes sense, because it gonna be more readable format. — Bryuk, May 23 '13 at 04:28
.xls - format before 2007 version, so my answer implies that I use version less than 2010 — Bryuk, May 23 '13 at 04:29

score 17 · Accepted Answer · answered May 23 '13 at 04:49

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:

xlWorkBook.Save();

score 4 · Answer 2 · answered May 23 '13 at 05:12

4

This is the correct code that is given in MSDN.

Excel.Worksheet newWorksheet;
newWorksheet = (Excel.Worksheet)Globals.ThisWorkbook.Worksheets.Add(
    missing, missing, missing, missing);

For more information please click the link

answered May 23 '13 at 05:12

Chamath Jeevan

5,072
1
24
27

score 4 · Answer 3 · answered Jun 25 '17 at 11:58

4

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);

answered Jun 25 '17 at 11:58

AmiNadimi

5,129
3
39
55

score 1 · Answer 4 · answered May 23 '13 at 04:35

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.

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/

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;
        }
    }

Thanks a lot. I will take a look on it. But my question is still open=) I wrote a full functionality and that's last step that i need. — Bryuk, May 23 '13 at 04:39
I know I am looking for what you need I figured that I was not answering your question just yet, I planned to edit it by now... :P — Tom Stickel, May 23 '13 at 04:44

How to create a new worksheet in Excel file c#?

4 Answers4

Linked