Merge multiple Excel column Value to One Consolidate Excel

string FilesPath = @"D:\Files";
//Get the all Files from the specific Path
DirectoryInfo dirDocument = new DirectoryInfo(FilesPath);
var files = dirDocument.GetFiles();
DataSet ds = new DataSet();
// Get array of Files
FileInfo[] filesD = dirDocument.GetFiles();
// Loop for files
foreach (FileInfo fileD in filesD)
{
string ExtensionC = fileD.Extension;
string FilePathC = txtFolderPath.Text;
string FileNameC = fileD.Name;
//Condition for Check and get the main Consolidate File
if (FileNameC.ToLower().Contains("SpecificMainExcel"))
{
//code for Main Consolidate excel initialise and opens an Excel workbook.
Excel.Application xlAppC = new Excel.Application();
Excel.Workbook xlWorkbookC = xlAppC.Workbooks.Open(@"" + FilesPath + "/" + FileNameC);
Excel._Worksheet xlWorksheetC = xlWorkbookC.Sheets[1];
Excel.Range xlRangeC = xlWorksheetC.UsedRange;
xlAppC.DisplayAlerts = false;
//xlAppC.ScreenUpdating = false;
//xlAppC.Visible = false;
// Get the count of Rows and Columns
int rowCountC = xlRangeC.Rows.Count;
int colCountC = xlRangeC.Columns.Count;
int count = 0;
// Loop for get All files and check and match columns with Consolidate excel sheet columns.
foreach (FileInfo file in files)
{
string Extension = file.Extension;
string FilePath = txtFolderPath.Text;
string FileName = file.Name;
int Start, End;
Start = FileName.IndexOf('-', 0);
End = FileName.Length;
//Get Excel Actual File Name
string ActualFileName = FileName.Substring(Start + 2, End - 2 - Start);
switch (Extension)
{
case ".xls": //Excel 97-03
ActualFileName = ActualFileName.Remove(ActualFileName.Length - 4);
break;
case ".xlsx": //Excel 07
ActualFileName = ActualFileName.Remove(ActualFileName.Length - 5);
break;
}
//code for initialise and opens an Excel workbook.
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"" + FilesPath + "/" + FileName);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
// Loop for columns in file
for (int col = 1; col <= colCountC; col++)
{
string filedec = Convert.ToString(xlRangeC.Cells[1, col].Value2);
if (filedec != null)
{
// Match the columns name
if (filedec.ToLower().Contains(ActualFileName.ToLower()))
{
//for (int row = 2; row <= rowCountC; row++)
//{
for (int i = 2; i <= rowCount; i++)
{
// Assign the value file to consolidate file column
xlRangeC.Cells[i, col] = Convert.ToString(xlRange.Cells[i, 6].Value2);
}
// }
}
}
}
// Close the worksheet
xlWorkbook.Close();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorksheet);
}
// Close the worksheet
xlWorkbookC.Close(true, Type.Missing, Type.Missing);
xlAppC.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlAppC);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbookC);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorksheetC);
}
}
MessageBox.Show("Records insert successfully !", "Done");