excelexport.vb

Data Export成Exce C# Source Code

                objFsXSL.Close()
                objFsXSL = Nothing
            End If

            If Not objstrWrtXSL Is Nothing Then
                objstrWrtXSL.Close()
                objstrWrtXSL = Nothing
            End If

            If Not objXmlTxtWrt Is Nothing Then
                objXmlTxtWrt.Close()
                objXmlTxtWrt = Nothing
            End If

            If Not objFsXML Is Nothing Then
                objFsXML.Close()
                objFsXML = Nothing
            End If

            If Not objStrRdr Is Nothing Then
                objStrRdr.Close()
                objStrRdr = Nothing
            End If

            If Not objXmlTxtRdr Is Nothing Then
                objXmlTxtRdr.Close()
                objXmlTxtRdr = Nothing
            End If

            objXPath = Nothing
            objXslTran = Nothing
            xslRes = Nothing
        End Try

    End Function

    Public Function TransformXMLDocumentToExcel(ByVal XMLDoc As XmlDataDocument, ByVal strXSLFullFilePath As String) As String
        Dim strExcelFile As String
        Dim objStrRdr As StringReader
        Dim objXMLRdr As XmlTextReader
        Dim objXPthDoc As XPath.XPathDocument
        Dim fs As System.IO.FileStream
        Dim objXMLTxtWrtr As XmlTextWriter
        Dim objXslTran As XslCompiledTransform
        Dim objXslRes As XmlResolver


        Try
            'Create An Xpath Doc
            objStrRdr = New StringReader(XMLDoc.OuterXml)
            objXMLRdr = New XmlTextReader(objStrRdr)
            objXPthDoc = New XPath.XPathDocument(objXMLRdr)

            strExcelFile = TempFolder & TEMP_EXCEL_FILE_NAME & Now.ToString("MM-dd-yy") & " " & Now.Hour.ToString & Now.Minute.ToString _
                    & Now.Second.ToString & Now.Millisecond.ToString & ".xls"

            fs = New System.IO.FileStream(strExcelFile, _
             System.IO.FileMode.Create)

            'Create an XmlTextWriter for the FileStream.
            objXMLTxtWrtr = New XmlTextWriter(fs, _
                System.Text.Encoding.Unicode)
            'Transform the XML using the stylesheet.

            objXslTran = New XslCompiledTransform

            strXSLFullFilePath = strXSLFullFilePath.Replace(XSLStyleSheetFolder, "")

            strXSLFullFilePath = XSLStyleSheetFolder & strXSLFullFilePath

            objXslTran.Load(strXSLFullFilePath)

            objXslTran.Transform(objXPthDoc, objXMLTxtWrtr)

            Return strExcelFile

        Catch exptn As Exception
            Throw
        Finally
            If Not objXMLTxtWrtr Is Nothing Then
                objXMLTxtWrtr.Close()
                objXMLTxtWrtr = Nothing
            End If

            If Not objStrRdr Is Nothing Then
                objStrRdr.Close()
                objStrRdr = Nothing
            End If

            If Not objXMLRdr Is Nothing Then
                objXMLRdr.Close()
                objXMLRdr = Nothing
            End If

            If Not fs Is Nothing Then
                fs.Close()
                fs = Nothing
            End If

            If Not objXMLTxtWrtr Is Nothing Then
                objXMLTxtWrtr.Close()
                objXMLTxtWrtr = Nothing
            End If

            objXPthDoc = Nothing
            objXslTran = Nothing
            objXslRes = Nothing
        End Try
    End Function

    Public Overloads Function AddExcelSheetToExcelTemplate(ByVal strExcelFile As String, ByVal strExcelTemplate As String) As String
        Try
            Return AddExcelSheetToExcelTemplate(strExcelFile, strExcelTemplate, DEFAULT_EXCEL_INDEX, DEFAULT_TEMP_EXCEL_SHEET_NAME)
        Catch exptn As Exception
            Throw
        End Try
    End Function

    Public Overloads Function AddExcelSheetToExcelTemplate(ByVal strExcelFile As String, ByVal strExcelTemplate As String, ByVal strExcelSheetName As String) As String
        Try
            Return AddExcelSheetToExcelTemplate(strExcelFile, strExcelTemplate, DEFAULT_EXCEL_INDEX, strExcelSheetName)
        Catch exptn As Exception
            Throw
        End Try
    End Function

    Public Overloads Function AddExcelSheetToExcelTemplate(ByVal strExcelFile As String, ByVal strExcelTemplate As String, ByVal intIndexOfExcelSheetToBeCopied As Integer) As String
        Try
            Return AddExcelSheetToExcelTemplate(strExcelFile, strExcelTemplate, intIndexOfExcelSheetToBeCopied, DEFAULT_TEMP_EXCEL_SHEET_NAME)
        Catch exptn As Exception
            Throw
        End Try
    End Function

    Public Overloads Function AddExcelSheetToExcelTemplate(ByVal strExcelFile As String, ByVal strExcelTemplate As String, ByVal intIndexOfExcelSheetToBeCopied As Integer, ByVal strExcelSheetName As String) As String

        Dim objBooks As Object
        Dim objBook As Object
        Dim objSheets As Object
        Dim objSheet As Object
        Dim strFinalExcelFile As String

        Try

            Dim objtest As Type
            objtest = Type.GetTypeFromProgID("Excel.Application")

            objExcel = Activator.CreateInstance(objtest)
            'objExcel = New Excel.Application

            objExcel.Visible = False : objExcel.DisplayAlerts = False

            strExcelTemplate = strExcelTemplate.Replace(TemplateFolder, "")
            strExcelFile = strExcelFile.Replace(TempFolder, "")

            objBooks = objExcel.Workbooks
            objBooks.Open(TemplateFolder & strExcelTemplate)
            objBooks.Open(TempFolder & strExcelFile)

            strFinalExcelFile = TempFolder & strExcelTemplate.Replace(".xls", "") & Now.ToString("MM-dd-yy") & " " & Now.Hour.ToString & Now.Minute.ToString _
                            & Now.Second.ToString & Now.Millisecond.ToString & ".xls"

            objBooks.Item(1).SaveAs(strFinalExcelFile)

            objBooks.Item(2).Worksheets.Item(intIndexOfExcelSheetToBeCopied).copy(objBooks.Item(1).Worksheets.Item(1))

            objBooks.Item(1).Worksheets.Item(1).Name = strExcelSheetName

            objBooks.Item(2).Close()

            objBooks.Item(1).Save()
            objBooks.Item(1).Close()

            objExcel.Quit()

            Return strFinalExcelFile

        Catch exptn As Exception
            Throw
        Finally
            ReleaseComObject(objExcel)
            ReleaseComObject(objBooks)

            objExcel = Nothing
            objBooks = Nothing
            objBook = Nothing
            objSheets = Nothing
            objSheet = Nothing

            System.GC.Collect()
        End Try
    End Function

    Public Sub SendExcelToClient(ByVal strExcelFile As String)
        Try
            HttpContext.Current.Response.Clear()
            HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=NewFile.xls")
            HttpContext.Current.Response.Charset = ""
            HttpContext.Current.Response.ContentType = "application/vnd.xls"
            HttpContext.Current.Response.WriteFile(strExcelFile)
            HttpContext.Current.Response.End()
        Catch exptn As Exception
            Throw
        End Try
    End Sub

    Public Sub CleanUpTemporaryFiles()
        Dim strFile As String
        Try
            If TempFolder <> HttpContext.Current.Server.MapPath(HttpContext.Current.Request.ApplicationPath) Then
                For Each strFile In Directory.GetFiles(TempFolder)
                    If File.GetLastAccessTime(strFile) < DateTime.Now.AddMinutes(600) Then
                        File.Delete(strFile)
                    End If
                Next
            End If
        Catch exptn As Exception
            Throw
        End Try
    End Sub

End Class