Getsuyodev, Programming is mainly used when constructing an application. Programming requires knowledge of application domains, algorithms, and programming language expertise. Programming can be developed in different logic based on developer knowledge.

Add new worksheet in Excel file



You can programmatically insert a worksheet and then add that worksheet to the collection of worksheets in the existing workbook. The following program shows how to add a new worksheet to an existing Excel file.

Excel Library

To access the object model from Visual C# .NET, you have to add the Microsoft Excel 12.0 Object Library to your project. In the previous chapter, you can see a step by step instruction on how to add the Excel library to your project.

programmatically Add New Worksheets to Workbooks

To add a new worksheet to the excel file, this program opens an existing Excel file and add a new worksheet in the existing excel file.

var xlNewSheet = (Excel.Worksheet)worksheets.Add(worksheets[1],
Type.Missing, Type.Missing, Type.Missing);
xlNewSheet.Name = "newsheet";
xlNewSheet.Cells[1, 1] = "New sheet content";

Add Excel Worksheet without prompts

xlApp.DisplayAlerts = false;

You can use the above code to disable Excel overwrite prompt. DisplayAlerts set to False for suppressing prompts and alert messages while a macro is running. When a message needs a response from the end-user, Microsoft Excel chooses the default response. After you complete the running process, Microsoft Excel sets this property to True, unless you are running cross-process code.

Programmatically Select Worksheets

You can Programmatically select Worksheet and set focus on that worksheet when the user opens the Excel document.

xlNewSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
xlNewSheet.Select();

Above method shows how to select a specified worksheet, in this way you can select an existing worksheet in an Excel document.

releaseObject()

Finally, we have to properly clean up Excel interop objects or release Excel COM objects. Here using a function releaseObject() to clean up the Excel object properly.

The following source code shows how to insert new worksheet in an excel file

using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel; 

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            if (xlApp == null)
            {
                MessageBox.Show("Excel is not properly installed!!");
                return;
            }

            xlApp.DisplayAlerts = false;
            string filePath = @"d:\test.xlsx";
            Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true,false, 0, true, false, false);
            Excel.Sheets worksheets = xlWorkBook.Worksheets;

            var xlNewSheet = (Excel.Worksheet)worksheets.Add(worksheets[1], Type.Missing, Type.Missing, Type.Missing);
            xlNewSheet.Name = "newsheet";
            xlNewSheet.Cells[1, 1] = "New sheet content";

            xlNewSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlNewSheet.Select();

            xlWorkBook.Save();
            xlWorkBook.Close();

            releaseObject(xlNewSheet);
            releaseObject(worksheets);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("New Worksheet Created!");
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}

Comments are closed.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More