How to add new worksheet in Excel file using asp.net

Description:-

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 you project. In the previous chapter you can see a step by step instruction on how to add Excel library to your project.

programmatically Add New Worksheets to Workbooks

In order to add new worksheet to the excel file, this program open 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 promt. DisplayAlerts set to False for suppress prompts and alert messages while a macro is running. When a message need 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 user open 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 any 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

Source Code:-

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

Author : Umesh Patel

Related Posts