In this code snippet, I write the code for consolidating the multiple excel value in single excel common column wise.
For example, we maintain the company wise daily report excel and end of the week we want to merge company excel to one excel that shows company daily report day wise.
- string FilesPath = @"D:\Files";
-
- DirectoryInfo dirDocument = new DirectoryInfo(FilesPath);
- var files = dirDocument.GetFiles();
- DataSet ds = new DataSet();
-
- FileInfo[] filesD = dirDocument.GetFiles();
-
- foreach (FileInfo fileD in filesD)
- {
- string ExtensionC = fileD.Extension;
- string FilePathC = txtFolderPath.Text;
- string FileNameC = fileD.Name;
-
- if (FileNameC.ToLower().Contains("SpecificMainExcel"))
- {
-
- 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;
-
-
-
- int rowCountC = xlRangeC.Rows.Count;
- int colCountC = xlRangeC.Columns.Count;
- int count = 0;
-
- 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;
-
- string ActualFileName = FileName.Substring(Start + 2, End - 2 - Start);
- switch (Extension)
- {
- case ".xls":
- ActualFileName = ActualFileName.Remove(ActualFileName.Length - 4);
- break;
- case ".xlsx":
- ActualFileName = ActualFileName.Remove(ActualFileName.Length - 5);
- break;
- }
-
- 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;
-
- for (int col = 1; col <= colCountC; col++)
- {
- string filedec = Convert.ToString(xlRangeC.Cells[1, col].Value2);
- if (filedec != null)
- {
-
- if (filedec.ToLower().Contains(ActualFileName.ToLower()))
- {
-
-
- for (int i = 2; i <= rowCount; i++)
- {
-
- xlRangeC.Cells[i, col] = Convert.ToString(xlRange.Cells[i, 6].Value2);
- }
-
- }
- }
- }
-
- xlWorkbook.Close();
- System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
- System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbook);
- System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorksheet);
- }
-
- 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");
If any query please comments below.