c# - 如何合并文件夹中所有 Excel 文件的已用范围？

Question

如果这是微不足道的，很抱歉打扰大家，但我很难过。我将一些 C# 代码放在一起，这些代码从一个文件夹中的所有 Excel 文件中获取已使用的范围，并将其合并到一个单一的合并文件中。该脚本大部分都有效，但我注意到它正在从源文件中复制 header 。所以，我稍微修改了脚本。现在，我试图从第一个文件(包括第 1 行)复制 usedrange，然后从源文件复制第 2 行(及以下)和所有列。下面是我主要工作的代码。

我几乎可以肯定问题出在这里。

  for (int i = 2; i <= rCnt; i++)
    {
    range = Worksheet.range[i, cCnt] as Excel.Range;
    if (range.Value != null)
      {
        Add(range.Value.ToString());
      }
    }

这是整个脚本。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
using System.IO;

namespace WindowsFormsApplication3
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Main();
        }

        public void Main()
        {                     
            string filePath = "C:\\Users\\Excel\\Desktop\\excel_files\\MainExcel.xlsx";
            Microsoft.Office.Interop.Excel.Application xlobj = new Microsoft.Office.Interop.Excel.Application();
            Workbook w = default(Workbook);
            Workbook w1 = default(Workbook);
            Worksheet s = default(Worksheet);
            Worksheet s1 = default(Worksheet);
            Excel.Range range;
            int rCnt = 0;
            int cCnt = 0;

            //Worksheet xlsht = default(Worksheet);
            int intItem = 1;                                                   
            DirectoryInfo dirSrc = new DirectoryInfo(@"C:\Users\Excel\Desktop\excel_files\");
            foreach (FileInfo ChildFile in dirSrc.GetFiles())
            {
                try
                {
                    // Renaming the excel sheet
                    w = xlobj.Workbooks._Open(ChildFile.FullName,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);

                    w1 = xlobj.Workbooks.Open(filePath);
                    xlobj.Visible = true;

                    w1 = xlobj.Workbooks._Open(filePath,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);

                    // There is no sheets(2) in your MainExcel workbook when copying the 2nd workbook.
                    // if (intItem > 3)
                    // {
                    //     Excel.Worksheet lastSht =
                    //          (Excel.Worksheet)w1.Worksheets[w1.Worksheets.Count];
                    //     xlsht = (Excel.Worksheet)w1.Worksheets.Add(Type.Missing,
                    //         lastSht, 
                    //         Type.Missing, Type.Missing);
                    // }
                    s = (Excel.Worksheet)w.Worksheets[1];
                    s1 = (Excel.Worksheet)w1.Worksheets[1];
                    s1.Name = ChildFile.Name;

                    // it will copy and paste sheet from one to another
                    // Excel.Range s = s.Cells[s.UsedRange.Rows.Count + 1, 1];
                    if (intItem == 1)
                        {
                        s.UsedRange.Copy(Type.Missing);
                        }   
                    else
                        {
                        range = s.UsedRange;
                        rCnt = range.Rows.Count;
                        cCnt = range.Columns.Count;

                            for (int i = 2; i <= rCnt; i++)
                            {
                                range = Worksheet.range[i, cCnt] as Excel.Range;
                                if (range.Value != null)
                                {
                                    Add(range.Value.ToString());
                                }
                            }
                        }

                    // Excel.Range r = s1.Cells[1, 1];
                    // Excel.Range r = (s1.UsedRange.Row + s1.UsedRange.Rows.Count - 1);
                    // Excel.Range r = s1.get_Range(s1.UsedRange.Row + 1, Type.Missing);
                    Excel.Range r = s1.Cells[s1.UsedRange.Rows.Count + 1, 1];
                    r.PasteSpecial(Excel.XlPasteType.xlPasteValues,
                          Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
                          Type.Missing, Type.Missing);

                    // with formula
                    // s1.UsedRange.Formula = s.UsedRange.Formula;

                    // Renaming the excel sheet
                    w.Save();
                    w1.Save();
                    w.Close(false, Type.Missing, Type.Missing);
                    w1.Close(false, Type.Missing, Type.Missing);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                    w.Save();
                    w1.Save();
                    w.Close(false, Type.Missing, Type.Missing);
                    w1.Close(false, Type.Missing, Type.Missing);
                }
                intItem = intItem + 1;
            }

        }
    }
}

我在这里有很多评论，因为我正在测试几个不同的想法。

Answer 1

我不得不承认，我并没有解构你所有的代码。我看到两个，在您引用为您可能的问题区域的代码段中似​​乎是语法错误:

range = Worksheet.range[i, cCnt] as Excel.Range;
                  ^- I think that should be capitalized, as the indexer is

还有……

Add(range.Value.ToString());

Add 方法在哪里？这意味着它是与表单类关联的方法。

就是说，我正确理解了您的最终目标，也许这足以解决问题。我在一小部分文件上执行此操作，并将每个文件复制到一个新工作簿，除了第一行之外的所有文件的第 1 行除外。

这有点蛮力，因为我实际上是在复制标题行然后将其删除，但代码库很短且易于维护。

这是您的 Main() 方法:

Microsoft.Office.Interop.Excel.Application xlobj = 
    new Microsoft.Office.Interop.Excel.Application();
xlobj.Visible = true;
xlobj.DisplayAlerts = false;

Excel.Workbook w = xlobj.Workbooks.Add();
Excel.Worksheet sh = w.Worksheets[1];
int row = 1;

DirectoryInfo dirSrc = new DirectoryInfo(@"C:\Users\Excel\Desktop\excel_files\");
foreach (FileInfo ChildFile in dirSrc.GetFiles())
{
    Excel.Workbook wb = xlobj.Workbooks.Open(ChildFile.FullName);
    Excel.Range r = wb.Worksheets[1].UsedRange;

    r.Copy();
    sh.Cells[row, 1].PasteSpecial(Excel.XlPasteType.xlPasteValues,
        Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone);

    if (row > 1)
        sh.Cells[row--, 1].EntireRow.Delete();

    row += r.Rows.Count;

    wb.Close();
}

w.SaveAs("C:\\Users\\Excel\\Desktop\\excel_files\\MainExcel.xlsx");

这是前后结果的屏幕截图: