所以我试图制作一个 Excel 表格聚合器。在我的工作中,有人向我们发送一堆单独的 Excel 文件,这些文件都是相关的,每个文件只使用了一张纸。
我在某种程度上遵循上一篇文章的想法。但这样做后,我复制的一些 Excel 工作表变成了空白。只有某些。我不知道为什么有些是空白的,而另一些却很好。
这是我用来打开和复制 Excel 文件的代码
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文件的代码
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
not j < count
.
所以我认为代码会更好:
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();
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)