C# .NET - How to Add Two Excel Sheets into One Single sheet using C#?

Dear All,

            I have to Add two Different Excel Sheets and Make thwm into One Single Excel Sheet using C#.Net. I have the idea to read the Data From and Excel sheets kindly help me in this task.

u can merge two excelsheet into one by using InteropServices.

using Excel;
using System.Runtime.InteropServices;

here this is function which merge more than one excel sheet  u just need to pass excel sheet name in arrReportName array and pass it to function mergeExcel and add referance of InteropServices this works perfectly.

 #region Const
  const int LOGON32_LOGON_INTERACTIVE       = 2;
  const int LOGON32_LOGON_NETWORK           = 3;
  const int LOGON32_LOGON_BATCH             = 4;
  const int LOGON32_LOGON_SERVICE           = 5;
  const int LOGON32_LOGON_UNLOCK            = 7;
  const int LOGON32_LOGON_NETWORK_CLEARTEXT = 8;
  const int LOGON32_LOGON_NEW_CREDENTIALS   = 9;
  const int LOGON32_PROVIDER_DEFAULT        = 0;

  [DllImport("advapi32.dll", SetLastError=true)]
  public static extern int LogonUser(
   string lpszUsername,
   string lpszDomain,
   string lpszPassword,
   int dwLogonType,
   int dwLogonProvider,
   out IntPtr phToken
   );
  [DllImport("advapi32.dll", SetLastError=true)]
  public static extern int ImpersonateLoggedOnUser(
   IntPtr hToken
   );

  [DllImport("advapi32.dll", SetLastError=true)]
  static extern int RevertToSelf();

  [DllImport("kernel32.dll", SetLastError=true)]
  static extern int CloseHandle(IntPtr hObject);

  #endregion

public void  mergeExcel( string[] arrReportName)
  {
   string dirPath=AppDomain.CurrentDomain.BaseDirectory + "Uploads/Reports";
 
   try
   {
    IntPtr lnToken;
    string Serverusername,Serverpassword,Serverdomain;
    Serverusername = ConfigurationSettings.AppSettings["Serverusername"].ToString();
    Serverpassword = ConfigurationSettings.AppSettings["Serverpassword"].ToString();
    Serverdomain = ConfigurationSettings.AppSettings["Serverdomain"].ToString();
    int TResult = LogonUser(Serverusername,Serverdomain,Serverpassword,LOGON32_LOGON_NETWORK,LOGON32_PROVIDER_DEFAULT, out lnToken);
    if ( TResult > 0 )
    {
     ImpersonateLoggedOnUser(lnToken);
     StringBuilder sb = new StringBuilder(80,80);
  
     uint Size = 79;
    
     Excel.ApplicationClass excelApp = new Excel.ApplicationClass();
     //excelApp.Visible = true;
     string file1,file2,workbookPath,workbookPath2,currentSheet,currentSheet2;
     int RowCount1,RowCount2,TotalRow;
     file1 = arrReportName[0].ToString();
     workbookPath = file1;
     Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
     Excel.Sheets excelSheets = excelWorkbook.Worksheets;
     currentSheet = "rptItemLocationWise";
     Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

     for(int D=1;D<arrReportName.Length;D++)
     {
      file2 = arrReportName[D].ToString();
     
      Excel.Range objR1 = excelWorksheet.UsedRange;
      RowCount1 = objR1.Rows.Count;
      workbookPath2 =file2;
      Excel.Workbook excelWorkbook2 = excelApp.Workbooks.Open(workbookPath2, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
      Excel.Sheets excelSheets2 = excelWorkbook2.Worksheets;

     
      currentSheet2 = "rptItemLocationWise";
      Excel.Worksheet excelWorksheet2 = (Excel.Worksheet)excelSheets2.get_Item(currentSheet2);

      Excel.Range objR2 = excelWorksheet2.UsedRange;
      RowCount2 = objR2.Rows.Count;
      TotalRow = RowCount1 + RowCount2 + 1;
      RowCount1 = RowCount1 + 1;
      RowCount2 = RowCount2 - 1;
      Excel.Range destination = (Excel.Range)excelWorksheet.get_Range("A" + RowCount1.ToString(), "H"+TotalRow.ToString());
      try
      {
       excelWorksheet2.get_Range("A4", "H"+ RowCount2.ToString()).Copy((Excel.Range)destination);
       excelWorkbook2.Close(false,workbookPath2,0);
      }
      catch(Exception ex)
      {
       excelApp.Application.Quit();
       RevertToSelf();
       CloseHandle(lnToken);
       return;
      }
     }
     
     string MergedFile;
     
     MergedFile = dirPath + "\\" + "FinalItemsByLocation.xls";

     if(File.Exists(MergedFile))
      File.Delete(MergedFile);
     //
//     Excel.Range objR3 = excelWorksheet.UsedRange;
//     RowCount1 = objR3.Rows.Count;
//     RowCount1 = RowCount1 + 1;
//     Excel.Range excelCell1 = (Excel.Range)excelWorksheet.get_Range("G" + RowCount1.ToString(),"G" + RowCount1.ToString());
//     Excel.Range excelCell2 = (Excel.Range)excelWorksheet.get_Range("H" + RowCount1.ToString(),"H" + RowCount1.ToString());
//     excelCell1.Value2 = "Grand Total";
//     excelCell1.Font.Bold = true;
//     //string strTotal = "$" + GrandTotal.ToString();
//     excelCell2.Value2 = GrandTotal.ToString();
//     excelCell2.Font.Bold = true;
  
     //

     excelWorkbook.SaveAs(MergedFile,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,0,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
     excelWorkbook.Close(true,workbookPath,0);
     excelApp.Application.Quit();
     RevertToSelf();
     CloseHandle(lnToken);
     //Session["PrintExcelFileName"] = MergedFile;
    }
    else
    {
     //Response.Write("Not logged on: " + Environment.UserName);
    }
   }
   catch(Exception ex)
   {
    string str = ex.Message;
    //    WriteToFile(ex.Message);
    str = "";
   }
  }

For that you need to merge excel sheets not add.

Go thru the links,

http://stackoverflow.com/questions/830841/merge-excel-files-into-one

Regards,

http://www.codecollege.net

using Excel = Microsoft.Office.Interop.Excel;

var excelFile = new ExcelFile();

 

excelFile.LoadXls("MyData.xls");

 

var dataTable1 = excelFile.Worksheets[0].CreateDataTable(ColumnTypeResolution.Auto);

var dataTable2 = excelFile.Worksheets[1].CreateDataTable(ColumnTypeResolution.Auto);

 

var mergedSheet = excelFile.Worksheets.Add("Merged Sheet");

mergedSheet.InsertDataTable(dataTable1, 0, 0, false);

mergedSheet.InsertDataTable(dataTable2, dataTable1.Rows.Count, 0, false);

 

excelFile.SaveXls("MyData.xls");