如何将多个Excel文件合并到一个Excel文件中 [英] How do I merge multiple excel files to a single excel file

问题描述

所以我试图制作一个Excel工作表聚合器.在我的工作中，我们得到了一些人，这些人向我们发送了一堆单独的excel文件，这些文件都相互关联，每个文件只用了一张纸.

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.

这是我用来打开和复制Excel文件的代码

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

这是我用来保存Excel文件的代码

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);

推荐答案

在您的内部循环中，您将一个新工作表添加到最终"工作簿(工作表")中，并为每个源工作表复制一个工作表.因此，由Add命令创建的每个工作表"都将为空，因为实际上您为每个源工作表创建了两个工作表.另一个问题是，就像您提到的那样，excel中的数组是基于1的.因此您必须循环播放，直到j <= count而不是j < count.

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();

这篇关于如何将多个Excel文件合并到一个Excel文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！