How to Merge two excel sheets into one in c#.

In this blog I am going to explain how we could merge two different excel sheets into one using C# programing. For this demo I have taken a console application, check the below steps and use the code as it is.



1. First of all create two different Excel files and put some data into those sheets, remember to RENAME the worksheet name because same name will create Error while processing Merging process. eg., Plz check the encircled area in below images::


2. Add References. At minimum, you will need below referances.

static void Main(string[] args)
{
  MergeExcelNew();
}    

private static void MergeExcelNew()
{
    Application app = new Microsoft.Office.Interop.Excel.Application();
    Workbook bookDest = null;
    Worksheet sheetDest = null;
    Workbook bookSource = null;
    Worksheet sheetSource = null;
    string sTempPath = @"D:\Temp\Sheet1_Folder\Sheet1.xlsx";
    string sFinalPath = @"D:\Temp\Sheet2_Folder\Sheet2.xlsx";

    try
    {
        //OpenBook
        bookDest = app.Workbooks._Open(sFinalPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        bookSource = app.Workbooks._Open(sTempPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        sheetSource = (Worksheet)bookDest.Worksheets[1];
        sheetDest = (Worksheet)bookSource.Worksheets[1];

        //CopyData
        sheetDest = (Worksheet)bookDest.Worksheets[1];
        int ss = bookDest.Sheets.Count;
        sheetSource.Copy(After: bookSource.Worksheets[ss]);
        bookDest.Close(false, Missing.Value, Missing.Value);

        //Save
        bookDest.Saved = true;
        bookSource.Saved = true;
    }
    catch (Exception ex)
    {
        Console.WriteLine("Exception Occured while releasing object " + ex.ToString());
    }
    finally
    {
        app.ActiveWorkbook.Save();
        app.Quit();
        Marshal.ReleaseComObject(app);
        GC.Collect();
    }

}

Comments

Popular posts from this blog

How to show enlarge image when mouse hover on image or link in Asp.Net(c#) using JavaScript

StateCode and StatusCode Values for mostly used entities in Microsoft Dynamics CRM 2013

How to add custom filter lookup field based on OptionSet field selection in Dynamics CRM.

go to top image