3

So I was trying to make an excel sheet aggregator. In my line of work we get people who send us a bunch of individual excel files that are all related each with only 1 sheet used.

I was following to some degree this previous post's ideas. But upon doing so, some of the excel sheets I copied were coming up blank. Only certain ones. I have no idea why some are blank and others are fine.

Here is the code I use to open and copy the excel files

         OpenFileDialog browse = new OpenFileDialog();
            browse.Multiselect = true;
            DialogResult result = browse.ShowDialog();

            if (result == DialogResult.OK)

                try //try to open it. If its a proper excel file
                {   
                    excel = new Excel.Application();
                    excel.Workbooks.Add("");
                    finalized = excel.Workbooks[1];
                    excel.SheetsInNewWorkbook = 1;
                    for(int i=0; i< browse.FileNames.Length; i++)
                    {
                        excel.Workbooks.Add(browse.FileNames[i]);
                    }
                    //skip the first workbook as it is the finalized one
                    //also note everything in excel starts at 1 and not 0
                    for(int i=2; i<excel.Workbooks.Count; i++)
                    {
                        int count = excel.Workbooks[i].Worksheets.Count;
                        excel.Workbooks[i].Activate();
                        for (int j = 1; j < count; j++)
                        {

                            Excel._Worksheet pastee = (Excel._Worksheet)excel.Workbooks[i].Worksheets[j];
                            Excel._Worksheet sheet = (Excel._Worksheet)finalized.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                            //Excel._Worksheet sheet = finalized.Sheets[1];
                            pastee.Copy(Before: sheet);


                        }//end of for j
                    }//end of for i
                }//end of try

Here is the code I use to save the excel file

            SaveFileDialog browse = new SaveFileDialog();
            browse.Title = "Save as Excel";
            browse.Filter = "Excel workbook | *.xlsx";
            DialogResult result = browse.ShowDialog();

            finalized.SaveAs(browse.FileName, Excel.XlFileFormat.xlWorkbookDefault);

            MessageBox.Show("Success", "Message");
            //unlock the file
            Global.releaseComObjects(finalized, excel);
Community
  • 1
  • 1
Alexander Ryan Baggett
  • 2,347
  • 4
  • 34
  • 61
  • What do you want to do actually? You want to copy the excel files which are not blank or are the excel files getting while you are copying? @Alexander Ryan Baggett – Sarath Subramanian Dec 04 '14 at 04:33
  • As I tried to explain in the description. I want to open multiple excel files. Copy every sheet from those files into 1 single new excel file. – Alexander Ryan Baggett Dec 04 '14 at 05:50
  • The excel sheets on the files I am opening are not blank. – Alexander Ryan Baggett Dec 04 '14 at 05:50
  • Actually found a freely available add-in for that which works great: http://www.rondebruin.nl/win/addins/rdbmerge.htm – Robbert Koppenol Dec 04 '14 at 08:40
  • Does this answer your question? [How to merge two excel files into one with their sheet names?](https://stackoverflow.com/questions/7568613/how-to-merge-two-excel-files-into-one-with-their-sheet-names) – Jim G. Aug 09 '22 at 14:49

2 Answers2

2

In your inner loop you add a new worksheet to your 'finalized' workbook ('sheet') AND copy a worksheet before it for every source sheet. So every 'sheet' created by your Add command will be empty as in fact you create two sheets for each source sheet. Another problem is, that - as you mentioned - arrays in excel are 1-based; so you have to loop until j <= count not j < count.

So I think that code would work better:

Excel.Worksheet dummy = finalized.Worksheets[1];

for (int i = 2; i <= excel.Workbooks.Count; i++)
{
    int count = excel.Workbooks[i].Worksheets.Count;

    for (int j = 1; j <= count; j++)
    {
        Excel._Worksheet pastee = (Excel._Worksheet)excel.Workbooks[i].Worksheets[j];
        pastee.Copy(dummy);
    }
}

dummy.Delete();
Fratyx
  • 5,717
  • 1
  • 12
  • 22
  • I tried it and it seems to copy text only - without any style/fonts/colors etc. could be issue with Excel.Interop version, I'm not sure. – itsho Jan 18 '17 at 12:08
0

The simplest way to merge worksheets into one is through a third part component called Spire.Xls. It’s a standalone .NET component. 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Spire.Xls;
using System.Data;


namespace Spire.XLS
{
    class Program
    {
        static void Main(string[] args)
    {

        Workbook workbook = new Workbook();
        //load the first workbook
        workbook.LoadFromFile(@"merge1.xlsx");
        //load the second workbook
        Workbook workbook2 = new Workbook();
        workbook2.LoadFromFile(@"merge2.xlsx");

        //import the second workbook's worksheet into the first workbook using a datatable
        Worksheet sheet2 = workbook2.Worksheets[0];
        DataTable dataTable = sheet2.ExportDataTable();
        Worksheet sheet1 = workbook.Worksheets[0];
        sheet1.InsertDataTable(dataTable, false, sheet1.LastRow + 1, 1);


        //save the workbook
        workbook.SaveToFile("result.xlsx");
    }
    }
}
monkSinha
  • 341
  • 3
  • 10