Monday, May 6, 2013

How to create/Add multiple worksheet in excel workbook using C#. net

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel=Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;

namespace ExcelDemo
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private void flBrowse_Click(object sender, EventArgs e)
        {
            var dialog = new OpenFileDialog();
            dialog.Multiselect = false;
            dialog.Filter = "Excel File |*.xlsx";
            dialog.ShowDialog();
            txtFile.Text = dialog.FileName;

        }

        private void button1_Click(object sender, EventArgs e)
        {

            if (System.IO.File.Exists(txtFile.Text))
            {
                Excel.Application excelApp = null;
                Excel.Workbook workbook = null;
                Excel.Sheets sheets = null;
                Excel.Worksheet newSheet = null;

                try
                {
                    excelApp = new Excel.Application();
                    var isexists = false;
                    workbook = excelApp.Workbooks.Open(txtFile.Text);
                    foreach (Excel.Worksheet ws in workbook.Sheets)
                    {
                        if (ws.Name.ToLower() == "NewSheet")
                            isexists = true;
                    }

                    sheets = workbook.Sheets;
                    if (!isexists)
                    {
                        newSheet = (Excel.Worksheet)sheets.Add(Type.Missing, sheets[sheets.Count - 1]);
                        newSheet.Name = "NewSheet";
                    }
                    workbook.Save();
                    workbook.Close(null, null, null);
                    // wb.Quit();
                }
                finally
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

                    newSheet = null;
                    sheets = null;
                    workbook = null;
                    excelApp = null;

                    GC.Collect();
                }

            }
        }
    }
}