DotNet实现多Excel档合并成一个Excel档（包含格式，图片的复制）

　　Excel档是由reporting报表导出。逻辑流程为 :

　　for reporting导出Excel

　　    if 第一个Excel档

　　　　　　continue for

　　　  else

               把该Excel档的sheet全部copy到第一个Excel中

          end if

 

一個Excel檔只有一個sheet，實現的代碼如下

            Dim strFilePath As String = saveFileDialog1.FileName
            Dim pathend As Int16 = strFilePath.LastIndexOf("\")
            Dim pathstr As String = ""
            Dim fs As FileStream = Nothing            
            Dim appexcel As New Excel.Application
            Dim booknew As Excel.Workbook = Nothing
            Dim workbook As Excel.Workbook = Nothing
            Dim worksheet As Excel.Worksheet = Nothing
            Dim sheet As Excel.Worksheet = Nothing
            Try
               'bytes報表byte流集合
                For i As Integer = 0 To bytes.Count - 1
                    If i = 0 Then
                        pathstr = strFilePath
                    Else
                        pathstr = strFilePath.Substring(0, pathend + 1) & strFileNames(i) & ".xls"
                    End If
                    '報表bytes寫入單個的Excel文件中
                    fs = New FileStream(pathstr, FileMode.Create)
                    fs.Write(bytes.Item(i), 0, bytes.Item(i).Length)
                    fs.Close()
                    If i = 0 Then
                        workbook = appexcel.Workbooks.Open(pathstr)
                        Continue For
                    End If
                    'Excel檔sheet複製
                    booknew = appexcel.Workbooks.Open(pathstr)
                    worksheet = CType(booknew.Worksheets.Item(1), Excel.Worksheet)
                    worksheet.Copy(After:=workbook.Sheets(1))
                    booknew.Close(Excel.XlSaveAction.xlSaveChanges, booknew.FullName, Type.Missing)
                    File.Delete(pathstr)
                Next
                workbook.Close(Excel.XlSaveAction.xlSaveChanges, workbook.FullName, Type.Missing)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                appexcel.Quit()
                appexcel = Nothing
                GC.Collect()
            End Try