我试图将文件夹中的所有Excel文件合并到主Excel文件中。 [英] I am trying to merge all Excel files in a folder into a Master Excel file.
问题描述
我正在测试这段代码,我认为它非常接近,但有些东西已经关闭了。 我相信它是s1文件中的范围。 我认为问题出在这里。
Excel.Range r = s1.get_Range(s1.UsedRange.Row + 1,Type.Missing);
或者,它可能是这样的。
Excel.Range r =(s1.UsedRange.Row + s1.UsedRange.Rows .Count - 1);
我试过这两个,但都没有为我工作。
这是我的示例代码。
使用System;
使用System.Collections.Generic;使用System.ComponentModel
;
使用System.Data;使用System.Drawing
;
使用System.Linq;
使用System.Text;
使用System.Windows.Forms;
使用Excel = Microsoft.Office.Interop.Excel;
使用Microsoft.Office.Interop.Excel;
使用System.IO;
命名空间WindowsFormsApplication3
{
公共部分类Form1:表格
{
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();
工作簿w =默认(工作簿);
工作簿w1 =默认(工作簿);
工作表s =默认(工作表);
工作表s1 =默认(工作表);
工作表xlsht =默认(工作表);
int intItem = 1;
DirectoryInfo dirSrc = new DirectoryInfo(@"C:\Users\Excel\Desktop\excel_files \");
foreach(dirSrc.GetFiles()中的FileInfo ChildFile)
{
try
{
//重命名excel表
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);
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 [intItem];
s1.Name = ChildFile.Name;
//它将使用公式
s.UsedRange.Copy(Type.Missing)将工作表从一个复制并粘贴到另一个;
//Excel.Range r =(s1.UsedRange.Row + s1.UsedRange.Rows.Count - 1);
Excel.Range r = s1.get_Range(s1.UsedRange.Row + 1,Type.Missing);
r.PasteSpecial(Excel.XlPasteType.xlPasteValues,
Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
Type.Missing,Type.Missing);
s1.UsedRange.Formula = s.UsedRange.Formula;
//重命名excel表
//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()= false;
w1.Save();
w.Close(false,Type.Missing,Type.Missing);
w1.Close(false,Type.Missing,Type.Missing);
}
intItem = intItem + 1;
}
}
}
}
请测试以下代码是否适合您。
Excel.Range r = s1.Cells [s1.UsedRange.Rows.Count + 1,1];
如果我们想使用get_Range获取单元格然后粘贴,我们需要指定与复制范围相同的大小。
问候,
Celeste
I'm testing this code and I think it's pretty darn close, but something is off here. I believe it is the range in the s1 file. I think the problem lies here.
Excel.Range r = s1.get_Range(s1.UsedRange.Row + 1, Type.Missing);
Or, it may be like this.
Excel.Range r = (s1.UsedRange.Row + s1.UsedRange.Rows.Count - 1);
I tried both and neither worked for me.
Here is my sample code.
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); 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); 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[intItem]; s1.Name = ChildFile.Name; // it will copy and paste sheet from one to another with formula s.UsedRange.Copy(Type.Missing); //Excel.Range r = (s1.UsedRange.Row + s1.UsedRange.Rows.Count - 1); Excel.Range r = s1.get_Range(s1.UsedRange.Row + 1, Type.Missing); r.PasteSpecial(Excel.XlPasteType.xlPasteValues, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, Type.Missing, Type.Missing); 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() = false; w1.Save(); w.Close(false, Type.Missing, Type.Missing); w1.Close(false, Type.Missing, Type.Missing); } intItem = intItem + 1; } } } }
Hi,
Please test if the following code works for you.
Excel.Range r = s1.Cells[s1.UsedRange.Rows.Count + 1, 1];
If we want to use get_Range to get the cell and then paste, we need to specify the same size like copied range.
Regards,
Celeste
这篇关于我试图将文件夹中的所有Excel文件合并到主Excel文件中。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!