Net-informations.com
SiteMap  | About    

Add new worksheet in Excel file

How to create multiple worksheets in Excel file using c#

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.

How to add Excel Library

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;

Save Excel file without asking to overwrite it in c#

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

How to programmatically Add New Worksheets to Workbooks from C# application

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




net-informations.com (C) 2016    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.