如何合并文件夹中所有Excel文件的已用范围？ [英] How to merge a used range from all Excel files in a folder?
问题描述
我整理了一些C＃代码，它们从文件夹中的所有Excel文件中获取使用范围，并将其合并到一个统一文件中。该脚本大多数工作，但我注意到它是从源文件复制标头。所以，我修改了一下脚本。
现在，我试图从第一个文件（包括第1行）复制usedrange，然后从源文件复制第2行（和向下）和所有列。以下是我最常用的代码。
I put together some C# code that gets the used range from all Excel files in a folder and merges it into a single consolidated file. The script mostly worked, but I noticed it was copying the headers from the source files. So, I modified the script a bit. Now, I am trying to copy the usedrange from the first file (which includes row 1), and then copy row 2 (and down) and all columns from the source files. Below is my mostly working code.
我几乎可以肯定问题在这里。
I'm almost certain the problem is here.
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;
}
}
}
}我在这里有很多评论，因为我正在测试几个不同的想法。
I have a lot of comments here because I was testing several different ideas.
推荐答案
嗨ryguy72 ，
Hi ryguy72,
感谢您发布此处。
如果您的问题与Excel有关，我会将其移至
Excel for Developers 论坛以获得合适的支持。
For your question is more related to Excel, I will move it to Excel for Developers forum for suitable support.
Visual C＃讨论并询问C＃编程语言，IDE，库，示例和工具。
The Visual C# discuss and ask the C# programming language, IDE, libraries, samples and tools.
如果您有一些语法或代码错误，请随时与我们联系。我们将尽力为您提供解决方案。
If you have some grammar or code errors, please feel free to contact us. We will try our best to give you a solution.
感谢您的理解与合作。
最诚挚的问候，
Wendy
这篇关于如何合并文件夹中所有Excel文件的已用范围？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
