Excel Automation Using C #

Question

I have a folder with 400 excel files. I need to copy worksheets in all of these excel files into a single excel file.

Using Interop and Reflection namespaces heres is what I have done so far.

I use folderBrowserDialog to go to the folder and select it, this will allow me to get the names of the files in the folder and skip them as much as possible, any help would be appreciated.

if (result == DialogResult.OK) { string path = fbd1.SelectedPath; //get the path int pathLength = path.Length + 1; string[] files = Directory.GetFiles(fbd1.SelectedPath);// getting the names of files in that folder foreach (string i in files) { MessageBox.Show("1 " + i); myExcel.Application excelApp = new myExcel.ApplicationClass(); excelApp.Visible = false; MessageBox.Show("2 " + i); myExcel.Workbook excelWorkbook = excelApp.Workbooks.Add(excelApp.Workbooks._Open(i, 0, false, 5, "", "", false, myExcel.XlPlatform.xlWindows, "", true, false, 0, true)); myExcel.Sheets excelSheets = excelWorkbook.Worksheets; MessageBox.Show("3 " + i); excelApp.Workbooks.Close(); excelApp.Quit(); } MessageBox.Show("Done!"); } 

How to add copied sheets to the destination file. Hope the question is clear?

thanks.

Answer 1

use Worksheet.Copy (Before, After) and specify as the last worksheet any main file you want. Note that you may need to create a new worksheet in mainApp so that it has a worksheet for input after that so that it does not throw an exception.

Try the following:

  Excel.Application mainApp = new Excel.ApplicationClass(); mainApp.Visible = false; Excel.Workbook mainWorkbook = excelApp.Workbooks.Add(null); Excel.Sheets mainWorkSheets = mainWorkbook.Worksheets; foreach (string i in files) { MessageBox.Show("1 " + i); Excel.Application exApp = new Excel.ApplicationClass(); exApp.Visible = false; MessageBox.Show("2 " + i); Excel.Workbook exWorkbook = exApp.Workbooks.Open(i, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); MessageBox.Show("3 " + i); foreach(Excel.Worksheet sheet in exWorkbook.Worksheets) { sheet.Copy(Type.Missing, mainWorkSheets[mainWorkSheets.Count -1]); } } mainApp.Save("NewExcel"); 

Answer 2

How about something like: Merge(@"C:\ExcelFolder", @"C:\FinalDestination.xls"); works for me, directly from the working sample - it is cropped for you.

I hope you do not need any tricks, but if you do, then do it (0:

See the following code:

 private void Merge(string strSourceFolder, string strDestinationFile) { try { //1. Validate folder, //2. Instantiate excel object //3. Loop through the files //4. Add sheets //5. Save and enjoy! object missing = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); ExcelApp.Visible = false; //Create destination object Microsoft.Office.Interop.Excel.Workbook objBookDest = ExcelApp.Workbooks.Add(missing); foreach (string filename in Directory.GetFiles(strSourceFolder)) { if (File.Exists(filename)) { //create an object Microsoft.Office.Interop.Excel.Workbook objBookSource = ExcelApp.Workbooks._Open (filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //Browse through all files. foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in objBookSource.Worksheets) { sheet.Copy(Type.Missing, objBookDest.Worksheets[objBookSource.Worksheets.Count]); } objBookSource.Close(Type.Missing, Type.Missing, Type.Missing); objBookSource = null; } } objBookDest.SaveAs(strDestinationFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); objBookDest.Close(Type.Missing, Type.Missing, Type.Missing); objBookDest = null; ExcelApp = null; } catch (System.Exception e) { //Catch } }