Subject: [xsl] RE: Trouble with Excel spreadsheet using VB.NET to generate the XML|
From: "Hewitt, Cheryl" <Cheryl.Hewitt@xxxxxxxxxxxxxxxxxxx>
Date: Wed, 7 May 2008 10:09:49 -0500
Tony, Thanks for the help. I have it working now, but the solution I came up with uses the browser window to display the data rather than opening up an Excel workbook. The result is that if the user clicks on the back button to return to the application, many times they will get an error message saying that "The process cannot access the file '_______________' because it is being used by another process." I've added code to the Page_Load, and also right before the file creation, that deletes the previous file, but to no avail. I've read that it's a bug and that one work around is to make a SQL call so that it releases the connection, but it seems that simply opening the results in Excel rather than the browser would solve the issue. I'm just not sure how to do that. Here is the VB code: strSELECT += " FROM " & strFROM strSQL = strSELECT & " " & filterstring 'Response.Write("strSQL: " & strSQL & "<br>") 'Response.End() strCon = "Integrated Security=SSPI;data source=SQL3Cluster5\LAB;initial catalog=dbExecutive" dsQuery = SqlHelper.ExecuteDataset(strCon, CommandType.Text, strSQL) 'Create the XML Data Document from the dataset. XMLDoc = New XmlDataDocument(dsQuery) 'clean out temp folder Dim strFile As String Dim TempFolder As String TempFolder = Server.MapPath("Excel/temp") For Each strFile In Directory.GetFiles(TempFolder) File.Delete(strFile) Next Response.ContentType = "application/vnd.ms-excel" Response.Charset = "" ' create a filestream with the unique file name - will use the xml writer below Dim strExcelFileName As String = "ExportedExcel" & Now.ToString("MM-dd-yy") & " " & Now.Hour.ToString & Now.Minute.ToString _ & Now.Second.ToString & Now.Millisecond.ToString & ".xls" Dim fs As System.IO.FileStream = New System.IO.FileStream(Server.MapPath("excel/temp/" & strExcelFileName), System.IO.FileMode.Create) 'Create an XmlTextWriter for the FileStream. Dim objXMLTxtWrtr As XmlTextWriter = New XmlTextWriter(fs, System.Text.Encoding.Unicode) Dim xt As New XslTransform xt.Load(Server.MapPath("excel/XSLStyleSheet/Excel_1.xsl")) xt.Transform(XMLDoc, Nothing, Response.OutputStream) myConnection.Dispose() objDR = Nothing myConnection = Nothing objXMLTxtWrtr = Nothing Response.End() ****************** Thanks! Cheryl -----Original Message----- From: Tony Nassar [mailto:tnassar@xxxxxxxxxxxxxxxx] Sent: Tuesday, May 06, 2008 3:05 PM To: Hewitt, Cheryl Cc: xsl-list@xxxxxxxxxxxxxxxxxxxxxx Subject: RE: Trouble with Excel spreadsheet using VB.NET to generate the XML Cheryl, did anyone respond to your query? If this is .NET, you may have to simply take a few more steps. Explicitly create an XmlWriter with an XmlWriterSettings object (http://msdn.microsoft.com/en-us/library/system.xml.xmlwritersettings_me mbers(VS.80).aspx), and make sure that the OmitXmlDeclaration property is set to false. It's not clear to me how much control you have over the XML generation; I hope you have complete control. If so, please investigate XmlTextWriter.WriteProcessingInstruction Method (http://msdn.microsoft.com/en-us/library/system.xml.xmltextwriter.writep rocessinginstruction(VS.80).aspx). Sorry about the broken URLs... -----Original Message----- From: Hewitt, Cheryl [mailto:Cheryl.Hewitt@xxxxxxxxxxxxxxxxxxx] Sent: Thursday, May 01, 2008 4:34 PM To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx Subject: [xsl] Trouble with Excel spreadsheet using VB.NET to generate the XML I trying to create an Excel spreadsheet using VB.NET to generate the XML. The application queries a SQL database dependent on user selections via a form with the results being output to Excel. The issue I am having is that the generated xml does not have the following code at the top: <?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?> This is the generated xml I am getting: <Workbook xmlns:msxsl="urn:schemas-microsoft-com:xslt"