C# copy and append excel file contents to a new excel file

Question

I'm trying to build an installer project (windows forms) using which I can select multiple excel files from a directory, copy contents from each file and append it in a new single output file. I am using Microsoft.Office.Interop.Excel package in VS 2017 Community edition. I'm still working on it but a bit push to the right direction will help a lot.

Say, I have 100's of excel files in a directory, each excel workbook contains at least 1 sheet. The structure of each sheet are identical. Using OpenFileDialog:

1. I select the files and display the selected file name and its path in a listbox.
2. I display the file count in a label.
3. Read the content of each file and paste it to a new excel file. The output file is single. after reading each file I want to append the contents to the end of the output file. So, I simply want to copy all the rows in one worksheet and append at the end of another worksheet.

I don't know where exactly I'm going wrong, I'm not even able to create the output excel file. Here is my work till now:

Excel.Application excel;
    Stream myStrm;
    OpenFileDialog ofd = new OpenFileDialog();

    private void button1_Click(object sender, EventArgs e)
    {
        ofd.InitialDirectory = "c:\\";
        ofd.Title = "Please select the files to merge";
        ofd.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
        //ofd.FileName = "ExportedData";
        //ofd.DefaultExt = ".xlsx";
        ofd.RestoreDirectory = true;
        ofd.Multiselect = true;

        //To store file path info
        List<System.IO.FileInfo> fList = new List<System.IO.FileInfo>();

        if (ofd.ShowDialog() == DialogResult.OK)
        {
            var fileName = ofd.FileName;
            var tempFolderPath = Path.GetTempPath();
            var templateFileLocation = Path.Combine(Directory.GetCurrentDirectory(), "Model", "ExcelTemplate.xlsx");
            templateFileLocation = templateFileLocation.Replace("\\bin\\Debug\\", "\\");

            foreach (String file in ofd.FileNames)
            {
                try
                {
                    if((myStrm = ofd.OpenFile()) != null)
                    {
                        using (myStrm)
                        {
                            //to display file name in the 1st listbox
                            fileListBox.Items.Add(file.Substring(file.LastIndexOf('\\') + 1));
                            //to display the path of the file
                            pathListBox.Items.Add(file);
                        }
                    }
                    //to display the selected file count 
                    fileCntLbl.Text = "You have selected " + ofd.FileNames.Length + " files";

                    ApplicationClass app = new ApplicationClass();
                    Workbook curWorkBook = null;
                    Workbook destWorkbook = null;
                    Worksheet workSheet = null;
                    Worksheet newWorksheet = null;
                    Object defaultArg = Type.Missing;
                    //FileInfo fi = null;
                    Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

                    try
                    {
                        // Copy the source sheet
                        curWorkBook = app.Workbooks.Open(file, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg);
                        workSheet = (Worksheet)curWorkBook.Sheets[1];
                        workSheet.UsedRange.Copy(defaultArg);

                        // Paste on destination sheet
                        destWorkbook = app.Workbooks.Open(@"c:\\Results.xlxs", defaultArg, false, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg);
                        newWorksheet = (Worksheet)destWorkbook.Worksheets.Add(defaultArg, defaultArg, defaultArg, defaultArg);
                        newWorksheet.UsedRange._PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);


                        }
                    }
                    catch (Exception exc)
                    {
                        System.Windows.Forms.MessageBox.Show(exc.Message);
                    }
                    finally
                    {
                        if (curWorkBook != null)
                        {
                            curWorkBook.Save();
                            curWorkBook.Close(defaultArg, defaultArg, defaultArg);
                        }

                        if (destWorkbook != null)
                        {
                            destWorkbook.Save();
                            destWorkbook.Close(defaultArg, defaultArg, defaultArg);
                        }
                    }
                    app.Quit();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
                }
        }
    }

    private void button2_Click(object sender, EventArgs e)
    {
        System.Windows.Forms.Application.Exit();
    }

This solution works but doesn't copy the contents and produces error. The error is like this:

Any help/suggestions appreciated. Thanks for help.

I have already tried all the solutions on stackexchange, codeproject and many other sites. So please do not mark it as duplicate or a homework stuff.

Answer 1

I know I'm late to answer this but still this could help someone.

You can have two functions here, one to open the files and the other to copy the contents.

In the first function, you can open as many files as you want through a for loop, something like this:

void OpenFiles()
{
    foreach (string strFile in sourceFiles) //sourceFiles is a list containing the file paths
    {
        bool b = false;
        Excel.Workbook bookSource = app.Workbooks._Open(strFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        Excel.Worksheet sheetSource = bookSource.Worksheets[1] as Excel.Worksheet;

        CopyData();

    }
    //finally save the file as your requirements and close all the open workbooks
}

void CopyData()
{
    Excel.Worksheet lastsheet = null;   //last sheet in the workbook
    int limit = 1000000; //variable to check if your sheet has exceeded
    try
    {
        var sheets = bookDest.Sheets;
        lastsheet = (Excel.Worksheet)bookDest.Sheets[sheets.Count];
        hc.ReleaseObject(sheets);
        drc = lastsheet.UsedRange.Rows.Count;       //no of rows used in result workbook
        src = sheetSource.UsedRange.Rows.Count;     //no of rows used in source workbook

        //if else loop to check if you have exceeded 1st sheet limit before start copying
        if ((drc + src) <= limit)
        {
            int sheetRowCount = sheetSource.UsedRange.Rows.Count;
            Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount), _columnEnd + sheetRowCount.ToString());
            range.Copy(lastsheet.get_Range(string.Format("A{0}", _currentRowCount), Missing.Value));
            _currentRowCount += range.Rows.Count;
        }
        else if ((drc >= limit && src >= limit) || drc >= limit || src >= limit || (drc + src) >= limit)
        {
                Excel.Worksheet newSheet = (Excel.Worksheet)bookDest.Worksheets.Add(After: lastsheet);
                newSheet.Name = "Result " + (cnt++);
                hc.ReleaseObject(lastsheet);
                lastsheet = newSheet;
                lastsheet.Activate();
                CopyHeader(lastsheet);

                //((Excel.Worksheet) this.app.ActiveWorkbook.Sheets[lastsheet]).Select();
                int sheetRowCount = sheetSource.UsedRange.Rows.Count;
                Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount), _columnEnd + sheetRowCount.ToString());
                range.Copy(lastsheet.get_Range(string.Format("A{0}", _currentRowCount), Missing.Value));
                _currentRowCount += range.Rows.Count;
            }
        }
        else
        {
            int sheetRowCount = sheetSource.UsedRange.Rows.Count;
            Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount), _columnEnd + sheetRowCount.ToString());
            range.Copy(lastsheet.get_Range(string.Format("A{0}", _currentRowCount), Missing.Value));
            _currentRowCount += range.Rows.Count;
        }
    }
    catch (IndexOutOfRangeException)
    {
        MessageBox.Show("Some problem with the source file", "Copy error");
    }
    finally
    {
        ReleaseObject(lastsheet);
    }
}

I have provided the limit to exactly a million. If you feel this limit might hang your excel than you can reduce it.

You're invited for any code changes.

Thank you.

Answer 2

Alternatively, using System.IO you can use the following to copy an excel file into a new file:

File.WriteAllBytes(newFilePath, File.ReadAllBytes(initialFilePath));

Answer 3

Instead of copy/pasting UsedRange, try using the Sheet.Copy function. It will simplify your code substantially and reduce the potential error points. C# - How to copy a single Excel worksheet from one workbook to another?