Estrazione in excel tramite ASP

[orcim]

Ciao, ho uno strano problema con una esportazione di una tabella mysql tramite ASP.

Nella tabella del DB il campo Numero è previsto come decimale 10,2 e visualizzo correttamente i valori in esso contenuti sia nel db che nel foglio xml (6.60) dal quale poi genero l'xls.

Ma quando estraggo in excel il valore 6,6 si trasforma nella cella corrispondente del foglio excel generato come 0,291666666666667.

Da che potrebbe dipendere ?
Grazie x la risposta.

[optime]

da qualche cosa nel codice

[orcim]

Originariamente inviato da optime
da qualche cosa nel codice

Allora posto il codice:

Pagina ASP:

<% 

 Session.Timeout = 200 
 Response.Buffer = True 
  
 filename = "_output.xml"
 
 Set fso = Server.CreateObject("Scripting.FileSystemObject") 
    if fso.FileExists(Server.MapPath("_XML/" & filename & "")) then 
    fso.DeleteFile(Server.MapPath("_XML/" & filename & "")) 
 end if 

 Set fso = Nothing 

 Set xmlDoc = CreateObject("MSXML2.DOMDocument.4.0")
 Set xslDoc = CreateObject("MSXML2.DOMDocument.4.0")

%>



<%


   sql = "SELECT * FROM tbl_c" 
            
   Set Rs = CreateObject("ADODB.Recordset")
   Rs.Open SQL, objconn
      
   if not Rs.eof then 
   
   Rs.Save xmlDoc, 1 
   xmlDoc.Save Server.MapPath("_XML/" & filename & "")
   
   end if

   Rs.Close() 
   Set Rs = Nothing 

%>

<%

 Set oDOM = CreateObject("MSXML2.DOMDocument.4.0")
 oDOM.async = False 
 
 oDOM.Load Server.Mappath("_XML/" & filename & "")
  
 Set oXSL = CreateObject("MSXML2.DOMDocument.4.0") 
 oXSL.async = False 
 
 oXSL.Load Server.Mappath("/_inc/Trasform_csv.xsl") 
 
 strTransform = oDOM.transformNode(oXSL) 
 
 Set fso = CreateObject("Scripting.FileSystemObject")
  
 strPath = Server.Mappath("_XML/_output.xls") 
 Session("FileName") = Server.Mappath("_XML/_output.xls") 
   
 Set file = fso.opentextfile(strPath, 2, True)
 file.write strTransform

 file.Close() 
 Set file = Nothing
  
 Set fso = Nothing
                        
 Set oDOM = Nothing 
 Set oXML = Nothing 
 Set oXSL = Nothing 
   
%>

File XSL per la trasformazione da XML in XLS:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
	<xsl:output omit-xml-declaration="yes"/>
	<xsl:template match="/">
		
		<xsl:for-each select="/xml/s:Schema/s:ElementType/s:AttributeType">
			<xsl:value-of select="@name"/>
			<xsl:choose>
				<xsl:when test="position()!=last()">
					<xsl:text>,</xsl:text>
				</xsl:when>
				<xsl:otherwise>
					<xsl:text>&#xa;</xsl:text>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:for-each>
		
		<xsl:for-each select="/xml/rs:data/z:row">
      <xsl:variable name="row" select="."/>
      
			<xsl:for-each select="/xml/s:Schema/s:ElementType/s:AttributeType">
			   
			  <xsl:variable name="columnName" select="@name"/>
				
				<xsl:value-of select="$row/@*[name()=$columnName]"/>
				<xsl:if test="position()!=last()">
					<xsl:text>,</xsl:text>
				</xsl:if>
	    </xsl:for-each>
			<xsl:text>&#xa;</xsl:text>
	  </xsl:for-each>
	</xsl:template>
</xsl:stylesheet>

[orcim]

Originariamente inviato da orcim
Allora posto il codice:

Pagina ASP:

<% 

 Session.Timeout = 200 
 Response.Buffer = True 
  
 filename = "_output.xml"
 
 Set fso = Server.CreateObject("Scripting.FileSystemObject") 
    if fso.FileExists(Server.MapPath("_XML/" & filename & "")) then 
    fso.DeleteFile(Server.MapPath("_XML/" & filename & "")) 
 end if 

 Set fso = Nothing 

 Set xmlDoc = CreateObject("MSXML2.DOMDocument.4.0")
 Set xslDoc = CreateObject("MSXML2.DOMDocument.4.0")

%>



<%


   sql = "SELECT * FROM tbl_c" 
            
   Set Rs = CreateObject("ADODB.Recordset")
   Rs.Open SQL, objconn
      
   if not Rs.eof then 
   
   Rs.Save xmlDoc, 1 
   xmlDoc.Save Server.MapPath("_XML/" & filename & "")
   
   end if

   Rs.Close() 
   Set Rs = Nothing 

%>

<%

 Set oDOM = CreateObject("MSXML2.DOMDocument.4.0")
 oDOM.async = False 
 
 oDOM.Load Server.Mappath("_XML/" & filename & "")
  
 Set oXSL = CreateObject("MSXML2.DOMDocument.4.0") 
 oXSL.async = False 
 
 oXSL.Load Server.Mappath("/_inc/Trasform_csv.xsl") 
 
 strTransform = oDOM.transformNode(oXSL) 
 
 Set fso = CreateObject("Scripting.FileSystemObject")
  
 strPath = Server.Mappath("_XML/_output.xls") 
 Session("FileName") = Server.Mappath("_XML/_output.xls") 
   
 Set file = fso.opentextfile(strPath, 2, True)
 file.write strTransform

 file.Close() 
 Set file = Nothing
  
 Set fso = Nothing
                        
 Set oDOM = Nothing 
 Set oXML = Nothing 
 Set oXSL = Nothing 
   
%>

File XSL per la trasformazione da XML in XLS:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
	<xsl:output omit-xml-declaration="yes"/>
	<xsl:template match="/">
		
		<xsl:for-each select="/xml/s:Schema/s:ElementType/s:AttributeType">
			<xsl:value-of select="@name"/>
			<xsl:choose>
				<xsl:when test="position()!=last()">
					<xsl:text>&#x09;</xsl:text>
				</xsl:when>
				<xsl:otherwise>
					<xsl:text>&#xa;</xsl:text>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:for-each>
		
		<xsl:for-each select="/xml/rs:data/z:row">
      <xsl:variable name="row" select="."/>
      
			<xsl:for-each select="/xml/s:Schema/s:ElementType/s:AttributeType">
			   
			  <xsl:variable name="columnName" select="@name"/>
				
				<xsl:value-of select="$row/@*[name()=$columnName]"/>
				<xsl:if test="position()!=last()">
					<xsl:text>&#x09;</xsl:text>
				</xsl:if>
	    </xsl:for-each>
			<xsl:text>&#xa;</xsl:text>
	  </xsl:for-each>
	</xsl:template>
</xsl:stylesheet>

[optime]

quindi se apri l'xml il valore è corretto?

[orcim]

Originariamente inviato da optime
quindi se apri l'xml il valore è corretto?

Si, ecco l'output xml:

<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
	<s:Schema id="RowsetSchema">
		<s:ElementType name="row" content="eltOnly" rs:CommandTimeout="30">

			<s:AttributeType name="xID" rs:number="1" rs:writeunknown="true">

				<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
			</s:AttributeType>

			<s:AttributeType name="DATA_INS" rs:number="2" rs:nullable="true" rs:writeunknown="true">
				<s:datatype dt:type="date" dt:maxLength="6" rs:fixedlength="true"/>
			</s:AttributeType>

			<s:AttributeType name="P" rs:number="3" rs:nullable="true" rs:writeunknown="true">
				<s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19" rs:scale="2" rs:precision="10" rs:fixedlength="true"/>
			</s:AttributeType>
		
			<s:extends type="rs:rowbase"/>
		</s:ElementType>
	</s:Schema>
	<rs:data>
           <z:row xID="20375" DATA_INS="2011-02-15" P="6.60"/>
	</rs:data>
</xml>

[orcim]

Avrei risolto cambiando questa riga:

<xsl:value-of select="$row/@*[name()=$columnName]"/>

con:

<xsl:value-of select="translate($row/@*[name()=$columnName,'.',',')]" />

Cioè facendo un replace del punto con la virgola ed excel legge correttamente 6,6.
Di contro però tutti i punti dei campi presenti nella tbl del db vengono sostituiti con la virgola...