在vb.net中合并Excel文件 [英] Merging Excel files within vb.net

问题描述

各位程序员，

我有一个小问题，需要一些建议/帮助.

我有一个应用程序，该应用程序填充包含4个图表的Excel电子表格中的单元格，每个图表都链接到填充的单元格.然后，将填充的模板另存为新文件(例如file_1.xls)，并根据数据库中保存的记录的需要，将过程重复多次.

问题的下一步是合并Excel文件以生成报告时.理想情况下，我想复制第二个文件(例如file_2.xls)，并将其粘贴到第一个文件(例如file_1.xls)中的下一页，并重复此过程，直到所有文件都在1个Excel工作表中为止.或者，我可以将文件合并到1个Excel文件中的单独工作表中.我有一些代码(如下)，部分实现了第二个选项(合并到单独的工作表).这段代码的问题是，我在工作表1上得到第一个文件，然后在随后的工作表上没有任何文件(尽管添加的工作表数量正确).

代码如下；

Hi fellow programmers,

I have a small problem with which I need some advice/help with.

I have an application that populates cells in an Excel spreadsheet that contains 4 charts, each of which is linked to the populated cells. The populated template is then saved as a new file (eg file_1.xls) and the process is repeated as many times as required by records saved in a database.

The next step which is the issue is when it comes to merging the Excel files to make a report. Ideally I would like to copy the second file (eg file_2.xls), and past it to the next page in the first file (eg file_1.xls) and repeat this process until all files are in the 1 Excel sheet. Alternatively I could live with the files merging to separate sheets within 1 Excel file. I have some code (below) which partly achieves the second option (merge to separate sheets). This issue with this code is that I get the first file on sheet 1, and then nothing on the subsequent sheets (the amount of sheets added is correct though).

The code is as follows;

Private Sub mergetest(ByVal WO_number As String, ByVal FileName As String)

        Dim SourceBook, TargetBook, ReportBook As Excel.Workbook
        Dim SourceSheet, TargetSheet As Excel.Worksheet
        Dim SourceRange, TargetRange As Excel.Range
        Dim TempFiles(500), FileString, ReportFile As String

        ' count number of excel charts in current work order number & add file names to temp memory
        Try
            FileString = "\" & WO_number & "_"
            For i = 1 To TempFiles.Length - 1
                If System.IO.File.Exists(Application.StartupPath & FileString & (i) & ".xls") = True Then
                    TempFiles(i) = (Application.StartupPath & "\" & WO_number & "_" & (i) & ".xls")
                Else
                    Exit For
                End If
            Next

            ReportFile = (Application.StartupPath & "\" & FileName)

            If System.IO.File.Exists(ReportFile) = True Then
                Call KillExcel()
                MsgBox("Report has already been created", vbInformation, "Report Generator")
                Return
            Else
                FileCopy(TempFiles(1), ReportFile)
            End If
        Catch ex As Exception
            Call KillExcel()
            MsgBox(ex.ToString, vbExclamation)
            Return
        End Try

        ' merge excel charts to one report file
        Try
            TargetBook = xlapp.Workbooks.Open(ReportFile)
            xlapp.Visible = False
            xlapp.DisplayAlerts = False
            For i = 2 To TempFiles.Length - 1
                If System.IO.File.Exists(TempFiles(i)) = True Then
                    SourceBook = xlapp.Workbooks.Open(TempFiles(i))
                    SourceSheet = SourceBook.Sheets(1)
                    SourceRange = SourceSheet.Range("A1", "IV65536")
                    SourceRange.Select()
                    SourceRange.Copy()
                    TargetSheet = TargetBook.Worksheets.Add
                    TargetSheet = TargetBook.Sheets(i)
                    TargetRange = TargetSheet.Range("A1", "IV65536")
                    SourceRange.PasteSpecial(Excel.XlPasteType.xlPasteAll,  Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, False, False)
                    SourceBook.Close()
                Else
                    Exit For
                End If
            Next           
            TargetBook.SaveAs(ReportFile)
            TargetBook.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
            xlapp.Quit()
            releaseObject(xlapp)
        End Try

        ' delete separate chart files
        Try
            For i = 1 To TempFiles.Length - 1
                If System.IO.File.Exists(TempFiles(i)) = True Then
                    System.IO.File.Delete(TempFiles(i))
                Else
                    Exit For
                End If
            Next
        Catch ex As Exception
            Call KillExcel()
            MsgBox(ex.ToString, vbExclamation)
            Return
        End Try

        'sort report file sheets (order by sheet ascending)
        Try
            ReportBook = xlapp.Workbooks.Open(ReportFile)
            xlapp.Visible = False
            For j = 1 To ReportBook.Sheets.Count
                For k = 1 To ReportBook.Sheets.Count - 1
                    If UCase$(ReportBook.Sheets(k).Name) > UCase$(ReportBook.Sheets(k + 1).Name) Then
                        ReportBook.Sheets(k).Move(After:=ReportBook.Sheets(k + 1))
                    End If
                Next
            Next
            ReportBook.SaveAs(ReportFile)
            ReportBook.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
            xlapp.Quit()
            releaseObject(xlapp)
        End Try
        Return
    End Sub

    Public Sub releaseObject(ByVal obj As Object)

        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch
        Finally
            GC.Collect()
        End Try
    End Sub

    Public Sub KillExcel()

        For Each Process As Process In Process.GetProcessesByName("EXCEL")
            Process.Kill()
        Next
        Return
    End Sub

非常感谢任何帮助/想法或建议，因为我对这个问题做了很多Google研究，但我肯定仍然缺少一些东西.

就像我之前说的，如果有人可以指出我的首选选项(将文件合并到同一张工作表)的方向，那也很好.

在此先感谢
问候
安迪




肯尼思，你好，

感谢您快速答复，它给了我一些详细的考虑，为此，我今天发现了这个问题，使我可以打开excel文件，将数据复制并粘贴到''母版表''.我正在使用单元格范围方法来执行此操作，它按照我最初的需要复制了file_2中的单元格格式和数据，并将其添加到file_1中的下一个可用单元格中，但是并没有复制我的标题(图片框)或我的4个图表在工作表上.我为此的新试用代码是:

Any help/ideas or advice is very much appreciated as I have done a lot of google research on the problem and I am definately still missing something.

As I said earlier on, if someone can point me in the direction of my preferred option (merge files to same sheet) that would be great as well

Thanks in advance
regards
Andy




Hi Kenneth,

Thanks for you''re speedy reply to this, it gave me some detail to think about and to that end I have made a discovery with this problem today that allows me to open the excel files, copy and paste the data to the ''master sheet''. I am using the cell range method to do this and it copies the cell format and data from file_2 and adds it to the next available cell in file_1 as I originally wanted, but DOES not copy my heading (a picture box) or my 4 charts on the sheet. My new trial code for this is:

Public Sub mergetest()

    Dim SourceBook, TargetBook As Excel.Workbook
    Dim SourceRange, TargetRange As Excel.Range
    Dim ReportFile, tempfiles(500) As String
    Dim sheetcount As Integer = 5
    Dim cellrange(sheetcount) As String
    Dim report1 As String = Application.StartupPath & "\10001_1.xls"

    ReportFile = (Application.StartupPath & "\" & "Test_CustomerRPT.xls")
    FileCopy(Report1, ReportFile)
    cellrange(1) = "A1:IV386"

    Try
        For i = 1 To sheetcount
            If System.IO.File.Exists(Application.StartupPath & "\10001_" & (i) & ".xls") = True Then
                tempfiles(i) = (Application.StartupPath & "\10001_" & (i) & ".xls")
            End If
            If i >= 2 Then
                cellrange(i) = "A" & (386 * (i - 1)) & ":IV" & (386 * i)
            End If
        Next

        TargetBook = xlapp.Workbooks.Open(ReportFile)
        xlapp.Visible = False
        xlapp.DisplayAlerts = False
        For i = 2 To sheetcount
            SourceBook = xlapp.Workbooks.Open(tempfiles(i))
            SourceRange = SourceBook.Sheets(1).range(cellrange(1))
            SourceRange.Copy()
            TargetRange = TargetBook.Sheets(1).Range(cellrange(i))
            TargetRange.PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, False, False)
        Next
        TargetBook.SaveAs(ReportFile)
        TargetBook.Close()
    Catch ex As Exception
        MsgBox(ex.ToString)
        xlapp.Quit()
        releaseObject(xlapp)
    End Try
    Return
End Sub

如您所见，我的工作表将以递增的单元格范围粘贴到主文件中.这很好用，但是没有人知道如何也复制图表和图片框吗?
我已经看过使用剪贴板，以确保如果我要在工作表上突出显示/复制/粘贴时，这就是Excel会做的事情.到目前为止，这还没有解决.

还有其他想法吗?

在此先感谢
Andy

as you can see my worksheets are pasted into the master file at an incremented cell range. This is working great, but does anyone know how to also copy the charts and pictureboxes as well??
I have looked at using the clipboard as im sure this is how Excel would do it if I was to highlight/copy/paste while working on the sheet. So far this hasn''t worked out.

Any other ideas guys?

Thanks in advance
Andy

推荐答案

(ReportBook.Sheets(k).Name)> UCase

(ReportBook.Sheets(k).Name) > UCase

(ReportBook.Sheets(k + 1 ).Name)然后 ReportBook.Sheets(k).Move(After:= ReportBook.Sheets(k + 1 )) 结束 如果 下一步 下一步 ReportBook.SaveAs(ReportFile) ReportBook.Close() 捕获，例如 As 异常 MsgBox(例如ToString) xlapp.Quit() releaseObject(xlapp) 结束 尝试 返回 结束 子 公共 Sub releaseObject( ByVal obj 目标 对象) 尝试 System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = 没什么 捕获 最后 GC.Collect() 结束 尝试 结束 子 公共 Sub KillExcel() 对于 每个流程 As 流程在中Process.GetProcessesByName(" ) Process.Kill() 下一步 返回 结束 子

(ReportBook.Sheets(k + 1).Name) Then ReportBook.Sheets(k).Move(After:=ReportBook.Sheets(k + 1)) End If Next Next ReportBook.SaveAs(ReportFile) ReportBook.Close() Catch ex As Exception MsgBox(ex.ToString) xlapp.Quit() releaseObject(xlapp) End Try Return End Sub Public Sub releaseObject(ByVal obj As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = Nothing Catch Finally GC.Collect() End Try End Sub Public Sub KillExcel() For Each Process As Process In Process.GetProcessesByName("EXCEL") Process.Kill() Next Return End Sub

非常感谢任何帮助/想法或建议，因为我对这个问题做了很多Google研究，但我肯定仍然缺少一些东西.

就像我之前说的，如果有人可以指出我的首选选项(将文件合并到同一张工作表)的方向，那也很好.

在此先感谢
问候
安迪




肯尼思，你好，

感谢您快速答复，它给了我一些详细的考虑，为此，我今天发现了这个问题，使我可以打开excel文件，将数据复制并粘贴到''母版表''.我正在使用单元格范围方法来执行此操作，它按照我最初的需要复制了file_2中的单元格格式和数据，并将其添加到file_1中的下一个可用单元格中，但是并没有复制我的标题(图片框)或我的4个图表在工作表上.我为此的新试用代码是:

Any help/ideas or advice is very much appreciated as I have done a lot of google research on the problem and I am definately still missing something.

As I said earlier on, if someone can point me in the direction of my preferred option (merge files to same sheet) that would be great as well

Thanks in advance
regards
Andy




Hi Kenneth,

Thanks for you''re speedy reply to this, it gave me some detail to think about and to that end I have made a discovery with this problem today that allows me to open the excel files, copy and paste the data to the ''master sheet''. I am using the cell range method to do this and it copies the cell format and data from file_2 and adds it to the next available cell in file_1 as I originally wanted, but DOES not copy my heading (a picture box) or my 4 charts on the sheet. My new trial code for this is:

Public Sub mergetest()

    Dim SourceBook, TargetBook As Excel.Workbook
    Dim SourceRange, TargetRange As Excel.Range
    Dim ReportFile, tempfiles(500) As String
    Dim sheetcount As Integer = 5
    Dim cellrange(sheetcount) As String
    Dim report1 As String = Application.StartupPath & "\10001_1.xls"

    ReportFile = (Application.StartupPath & "\" & "Test_CustomerRPT.xls")
    FileCopy(Report1, ReportFile)
    cellrange(1) = "A1:IV386"

    Try
        For i = 1 To sheetcount
            If System.IO.File.Exists(Application.StartupPath & "\10001_" & (i) & ".xls") = True Then
                tempfiles(i) = (Application.StartupPath & "\10001_" & (i) & ".xls")
            End If
            If i >= 2 Then
                cellrange(i) = "A" & (386 * (i - 1)) & ":IV" & (386 * i)
            End If
        Next

        TargetBook = xlapp.Workbooks.Open(ReportFile)
        xlapp.Visible = False
        xlapp.DisplayAlerts = False
        For i = 2 To sheetcount
            SourceBook = xlapp.Workbooks.Open(tempfiles(i))
            SourceRange = SourceBook.Sheets(1).range(cellrange(1))
            SourceRange.Copy()
            TargetRange = TargetBook.Sheets(1).Range(cellrange(i))
            TargetRange.PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, False, False)
        Next
        TargetBook.SaveAs(ReportFile)
        TargetBook.Close()
    Catch ex As Exception
        MsgBox(ex.ToString)
        xlapp.Quit()
        releaseObject(xlapp)
    End Try
    Return
End Sub

如您所见，我的工作表将以递增的单元格范围粘贴到主文件中.这很好用，但是没有人知道如何也复制图表和图片框吗?
我已经看过使用剪贴板，以确保如果我要在工作表上突出显示/复制/粘贴时，这就是Excel会做的事情.到目前为止，这还没有解决.

还有其他想法吗?

在此先感谢
安迪

as you can see my worksheets are pasted into the master file at an incremented cell range. This is working great, but does anyone know how to also copy the charts and pictureboxes as well??
I have looked at using the clipboard as im sure this is how Excel would do it if I was to highlight/copy/paste while working on the sheet. So far this hasn''t worked out.

Any other ideas guys?

Thanks in advance
Andy

好吧，首先，您可以避免使用此行

范围("A1"，"IV65536")

并替换为

UsedRange

这使事情进展得有些快.请记住，单元格中的信息存储在XML文件中，并且仅在单元格中有内容的情况下存储信息.如果将扩展xlsx替换为zip并打开带有zip标记的文件夹，则可以自己看到.它仅符合xlsx而不符合xls，因为存储过程随扩展而改变.如果键入Range，则将搜索所有单元格，或者至少是我前一段时间被告知的内容.但是，我可以验证它是否更快.

查看代码的另一件事是复制功能.过去完成此操作后，我将所有内容复制到对象的array(，)中，并手动写入了所有值.我确信那行得通，但是除了我有问题之外，您的代码似乎再也没有其他缺点了.

无法编写类似SourceBook.Sheet(1).After之后的内容:= Workbook.Sheets(3)吗?

看起来可以:
http://social.msdn.microsoft.com /Forums/zh-CN/vsto/thread/b31bf687-2549-436a-8997-c11c9f3258ed/ [

Well, First off you could aviod this line

Range("A1", "IV65536")

and replaced it with

UsedRange

This sould speed things along somewhat. Remember that the information in a cell is stored in an XML file, and it only stores informtion were there is a content in the cell. You could see this for yourself if you replace the extention xlsx with zip and open the zip''ed folder. It only complies with the xlsx and not with xls, as the storage procedure was change with the extention. If you type Range is searches through all the cells, or at least is what I was told a while back. I can however verify that it is faster.

One more thing that got me while looking at the code was the copy function. When I have done this in the past I copyed all the content to an array(,) of object and wrote all the values in by hand. Im surtain that would work, but than again there does not seem to be any other flaude with your code except the question I had.

Cant you write something like SourceBook.Sheet(1).Copy After:=Workbook.Sheets(3) ?

Looks like you can:
http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/b31bf687-2549-436a-8997-c11c9f3258ed/[^]

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