Method to Add and Delete Worksheets in One Workbook with C#

By default, one Excel workbook presents three worksheets. We can add or delete sheets according to our requirements. For example, we want to create a sales report which includes sales information from January to June and each sheet saves data of one month. So, we need to add three more sheets to show complete data. If we just want to show data in January, we can delete other sheets.

Next, I will show my method to add and delete worksheets with C#. Firstly, I have prepared an Excel file which shows some sales information and just has one worksheet. What I will do is to add a new sheet and then delete the original sheet.

The following screenshot presents the original workbook.

A C# .NET Excel component, Spire.XLS for .NET is used in this example to realize the function more easily and quickly.

Steps:

  1. Load the file “Sales Report” from my computer.
  2. Add one new worksheet.
  3. Initialize this worksheet. Add text and format.
  4. Delete the original worksheet.
  5. Save and launch new file.

Using the Code: 

using System;
using System.Drawing;
using Spire.Xls;

namespace AddnDeleteSheet
{
    class AddandDelete
    {
        static void Main(string[] args)
        {
            //Load File
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@”D:\work\My Documents\Sales Report.xlsx”);

            //Add One New Sheet
            workbook.CreateEmptySheet(“NewSheet”);

            //Add Text in New Sheet and Format
            Worksheet sheet = workbook.Worksheets[1];
            sheet.Range[“A1”].Text = “This is a new sheet”;
            sheet.Range[“A1:C2”].Merge();
            sheet.Range[“A1”].Style.Font.FontName = “Calibri”;
            sheet.Range[“A1”].Style.Font.Size = 12;
            sheet.Range[“A1”].Style.Font.IsBold = true;
            sheet.Range[“A1”].Style.Font.Color = Color.DarkCyan;
            sheet.Range[“A1”].HorizontalAlignment = HorizontalAlignType.Center;
            sheet.Range[“A1”].VerticalAlignment = VerticalAlignType.Center;

            //Delete the First Sheet
            Worksheet worksheet = workbook.Worksheets[0];
            worksheet.Remove();
            workbook.SaveToFile(“Add&Del Sheet.xlsx”, ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Notice:

If want to delete the new added worksheet but not the original one, just replace these sentences

            Worksheet worksheet = workbook.Worksheets[0];
            worksheet.Remove();
as
            Worksheet worksheet = workbook.Worksheets[1];
            worksheet.Remove();

RESULT:

P.S. The warning sheet is generated by Spire.XLS for .NET automatically. 

Download Spire.XLS for .NET Here

Advertisements

Author: janewdaisy

.NET Program Beginner. Share methods about how to use C#/VB.NET to export data, operate Word, Excel, PDF and other useful skills.

3 thoughts on “Method to Add and Delete Worksheets in One Workbook with C#”

  1. I have learned some essential things through your blog post post. One other subject I would like to express is that there are lots of games in the marketplace designed mainly for preschool age young children. They include pattern identification, colors, animals, and styles. These normally focus on familiarization in lieu of memorization. This will keep a child engaged without having a sensation like they are studying. Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s