Add new worksheet in Excel file

By: Rajesh P.S.

It is possible to dynamically insert a worksheet into an existing Excel file and subsequently include it within the collection of worksheets. The program provided below exemplifies the process of adding a new worksheet to an already established Excel workbook.

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;

To disable the Excel overwrite prompt and suppress prompts and alert messages during the execution of a macro, you can incorporate the following code. By setting the DisplayAlerts property to False, you can prevent Excel from displaying prompts that require user response. In situations where user input is required, Microsoft Excel will proceed with the default response. Following the completion of the execution process, Microsoft Excel automatically resets the DisplayAlerts property to True, unless cross-process code is being executed.

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

Full Source C#

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

Next > How to delete worksheet from an excel file

Related Topics

More Related Topics.....