Introduction

in cases merging excel files or copying worksheets from differect excel files are confuising, in this sample I will show hot to copy worksheets from different excel files and put them in one file. in this sample I assume that there are only one worksheet in each excel file. the indea can be exteded to copy all worksheets by looping statment

Building the Sample

reference to Microsoft.Office.Interop.Excel is require

Description

in cases merging excel files or copying worksheets from differect excel files are confuising, in this sample I will show hot to copy worksheets from different excel files and put them in one file. in this sample I assume that there are only one worksheet in each excel file. the indea can be exteded to copy all worksheets by looping statment  .

Assuming that you have list of file, and you want to merge all of them in one file and delete other files in this sample. we assume that all excel files conatins only one wroksheet per file. so our target is to combine all wroksheet in one file and delate all other files. 

in case you want to extend the sample to cover the case when you have more than on worksheet in each file you can add on more loop to cover all other worksheets. 

 


 

C#
Edit|Remove
 static void CombineFiles(List<string> FiletNames, string ExcleOutPutFilePath) 
        { 
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); 
 
            try //try to open it. If its a proper excel file 
            { 
                Microsoft.Office.Interop.Excel.Workbook TargetWorkbook = app.Workbooks.Open(FiletNames[0]); 
                ((Microsoft.Office.Interop.Excel.Worksheet)TargetWorkbook.Sheets[1]).Name = Path.GetFileNameWithoutExtension(FiletNames[0]); 
 
                for (int i = 1; i < FiletNames.Count(); i++) 
                { 
                    Microsoft.Office.Interop.Excel.Workbook curWorkBook = app.Workbooks.Open(FiletNames[i]); 
 
                    Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)curWorkBook.Sheets[1]; 
                    workSheet.Name = Path.GetFileNameWithoutExtension(FiletNames[i]); 
                     
                    //Copy from source to destination 
                    workSheet.Copy(Type.Missing, TargetWorkbook.Sheets[TargetWorkbook.Sheets.Count]); 
 
                    // Save both workboooks 
                    TargetWorkbook.Save(); 
                    curWorkBook.Save(); 
                    curWorkBook.Close(); 
 
 
                } 
                TargetWorkbook.SaveAs(Path.ChangeExtension(ExcleOutPutFilePath, "xlsx"), Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook); 
                TargetWorkbook.Close(); 
            } 
            catch (System.Exception ex) 
            { 
 
            } 
            finally 
            { 
                app.Quit(); 
                foreach (var item in FiletNames) 
                { 
                    if (File.Exists(item)) 
                        File.Delete(item); 
                } 
            } 
        } 

Source Code Files

More Information

For more information on X, see ...?