[xsl] RE: Trouble with Excel spreadsheet using VB.NET to generate the XML

["Hewitt, Cheryl" <Cheryl.Hewitt@xxxxxxxxxxxxxxxxxxx>]

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"