Private Overloads Function TransformDataTableToExcel(ByVal dtTable As DataTable, ByVal blnDisplayColumnHeader As Boolean, ByVal strXSLFile As String) As String Dim strXSL As String Dim strXSLTempFile As String Dim strExcelFile As String Dim dsDataSet As DataSet Dim objFsXSL As FileStream Dim objstrWrtXSL As StreamWriter Dim objFsXML As System.IO.FileStream Dim objXmlTxtWrt As XmlTextWriter Dim objStrRdr As StringReader Dim objXmlTxtRdr As XmlTextReader Dim objXPath As XPath.XPathDocument Dim objXslTran As Xsl.XslCompiledTransform Dim xslRes As XmlResolver Try dsDataSet = New DataSet dsDataSet.Tables.Add(dtTable.Copy) If strXSLFile = "" Then strXSL = CreateXSL(dtTable, blnDisplayColumnHeader) strXSLTempFile = "D:\TestXSL" & dtTable.TableName & Now.ToString("MM-dd-yy") & " " & Now.Hour.ToString & Now.Minute.ToString _ & Now.Second.ToString & Now.Millisecond.ToString & ".xsl" objFsXSL = New FileStream(strXSLTempFile, FileMode.Create) objstrWrtXSL = New StreamWriter(objFsXSL) objstrWrtXSL.Write(strXSL) objstrWrtXSL.Flush() objstrWrtXSL.Close() End If strExcelFile = "D:\Excel" & dtTable.TableName & Now.ToString("MM-dd-yy") & " " & Now.Hour.ToString & Now.Minute.ToString _ & Now.Second.ToString & Now.Millisecond.ToString & ".xls" 'Create Output Stream to write the file to disk objFsXML = New System.IO.FileStream(strExcelFile, _ System.IO.FileMode.Create) objXmlTxtWrt = New XmlTextWriter(objFsXML, _ System.Text.Encoding.Unicode) 'Create Xpath Doc to be given as used while doing the XSL Trannsfor objStrRdr = New StringReader(dsDataSet.GetXml) objXmlTxtRdr = New XmlTextReader(objStrRdr) objXPath = New XPath.XPathDocument(objXmlTxtRdr) objXslTran = New Xsl.XslCompiledTransform If strXSLFile = "" Then ''error objXslTran.Load(objXmlTxtRdr) Else ' strXSLFile.Replace(XSLStyleSheetFolder, "") ' strXSLFile = XSLStyleSheetFolder & strXSLFile objXslTran.Load(strXSLFile) End If objXslTran.Transform(objXPath, objXmlTxtWrt) Return strExcelFile Catch exptn As Exception Throw Finally strXSL = Nothing strXSLTempFile = Nothing dsDataSet = Nothing If Not objFsXSL Is Nothing Then 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
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)