How to merge excel-sheet in one sheet in asp.net

Description:-

In this blog I am going to explain how we could merge two different excel sheets into one using C# programming. For this demo I have taken a console application, check the below steps and use the code as it is. Here I have Created Sample Code to Understand how to Merger Two different file in to one.

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.

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

using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Runtime.InteropServices;

Now Create Method and Apply below code for Merge two ExcelSheet into One.

private static void MergeExcelNew()
{
  Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
  Workbook bookDest = null;
  Worksheet sheetDest = null;
  Workbook bookSource = null;
  Worksheet sheetSource = null;
  string sTempPath = @"C:\Users\UmesH\Desktop\J.xlsx";
  string sFinalPath = @"C:\Users\UmesH\Desktop\H.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();
    Console.WriteLine("Done");
    Console.ReadLine();
    Marshal.ReleaseComObject(app);
    GC.Collect();
  }
}

Now Call this Method in Main method.

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

Output:-

Now run your Application and you will get First ExcelSheet Sheet into Second ExcelSheet Sheet with Same name. So you don’t need to Enter Manually data from Different ExcelSheet.

Author : Umesh Patel

Related Posts