C# - Need to Merge two excel sheet into one Excel Sheet

Question

for proccessing excel need to append shee2 into shet1 after sheet1 data with cell style and not using any nuget

var App = new Microsoft.Office.Interop.Excel.Application();

Workbook  book1 = App.Workbooks.Open(@"path");
Worksheet sheet1 = book1.Worksheets\[1\];

Workbook  book2 = App.Workbooks.Open(@"path");
Worksheet sheet2 = book2.Worksheets\[1\];

Tried lot of solution but still not working...

pranav i tried thing but that are not useful and there is no point to add irrelavent code — Nilesh Patil
– Nilesh Patil, Commented Mar 3, 2023 at 7:34

Raj Gandhi · Accepted Answer · 2023-03-03 07:47:17Z

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Text;  
using System.Reflection;  
using Microsoft.Office.Interop.Excel;  
using System.IO;  
using Excel = Microsoft.Office.Interop.Excel;  
  
namespace ConsoleApplication3  
{  
    class Program  
    {  
        static void Main(string[] args)  
        {  
            MergeExcel.DoMerge(new string[]   
            {  
                @"D:\Test Files\ConsoleApplication3\ConsoleApplication3\merge1.xlsx",   
                @"D:\Test Files\ConsoleApplication3\ConsoleApplication3\merge2.xlsx"   
            },  
                @"D:\Test Files\ConsoleApplication3\ConsoleApplication3\result.xlsx", "E", 2);  
        }  
    }  
    public class MergeExcel  
    {  
        Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();  
        Excel.Workbook bookDest = null;  
        Excel.Worksheet sheetDest = null;   
        Excel.Workbook bookSource = null;  
        Excel.Worksheet sheetSource = null;  
        string[] _sourceFiles = null;  
        string _destFile = string.Empty;  
        string _columnEnd = string.Empty;  
        int _headerRowCount = 0;  
        int _currentRowCount = 0;  
        public MergeExcel(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount)  
        {  
            bookDest = (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);  
            sheetDest = bookDest.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;  
            sheetDest.Name = "Data";  
            _sourceFiles = sourceFiles;  
            _destFile = destFile;  
            _columnEnd = columnEnd;  
            _headerRowCount = headerRowCount;  
        }  
        void OpenBook(string fileName)  
        {  
            bookSource = app.Workbooks._Open(fileName, 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 = bookSource.Worksheets[1] as Excel.Worksheet;  
        }  
        void CloseBook()  
        {  
            bookSource.Close(false, Missing.Value, Missing.Value);  
        }  
  
        void CopyHeader()  
        {  
            Excel.Range range = sheetSource.get_Range("A1", _columnEnd + _headerRowCount.ToString());  
            range.Copy(sheetDest.get_Range("A1", Missing.Value));  
            _currentRowCount += _headerRowCount;  
        }  
        void CopyData()  
        {  
            int sheetRowCount = sheetSource.UsedRange.Rows.Count;  
            Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount), _columnEnd + sheetRowCount.ToString());  
            range.Copy(sheetDest.get_Range(string.Format("A{0}", _currentRowCount), Missing.Value));  
            _currentRowCount += range.Rows.Count;  
        }  
        void Save()  
        {  
            bookDest.Saved = true;  
            bookDest.SaveCopyAs(_destFile);  
        }  
        void Quit()  
        {  
            app.Quit();  
        }  
        void DoMerge()  
        {  
            bool b = false;  
            foreach (string strFile in _sourceFiles)  
            {  
                OpenBook(strFile);  
                if (b == false)  
                {  
                    CopyHeader();  
                    b = true;  
                }  
                CopyData();  
                CloseBook();  
            }  
            Save();  
            Quit();  
        }  
        public static void DoMerge(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount)  
        {  
            new MergeExcel(sourceFiles, destFile, columnEnd, headerRowCount).DoMerge();  
        }  
    }  
}

c-sharpcorner.com/UploadFile/a08113/… You can refer this link.
Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, can you edit your answer to include an explanation of what you're doing and why you believe it is the best approach?

Collectives™ on Stack Overflow

C# - Need to Merge two excel sheet into one Excel Sheet

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related