Utilizzo di Visual Basic o ASP per creare un foglio di calcolo XML per Excel 2002 ed Excel 2003

Sommario

Microsoft Excel 2002 e Microsoft Office Excel 2003 supportano un formato XML con cui è possibile caricare e salvare cartelle di lavoro o fogli di calcolo XML. Con questo formato XML per fogli di calcolo è possibile creare in Excel cartelle di lavoro formattate con più fogli senza utilizzare l'automazione. Questo approccio è preferibile quando occorre creare una cartella di lavoro di Excel ma non è possibile automatizzare Excel, ad esempio su un server Web o da un servizio, oppure quando Excel non è installato nel sistema in cui il codice viene eseguito.

In questo articolo viene illustrato come creare un modello XML che, se utilizzato con la trasformazione XSL (Extensible Stylesheet Language), consente di generare una cartella di lavoro formattata apribile direttamente in Excel. Viene mostrata la trasformazione XML per ASP (Active Server Pages) e Visual Basic. Per quanto riguarda il codice di esempio di Visual Basic, i dati del foglio di calcolo vengono generati unicamente con XML/XSL, e per aprire i risultati in Excel viene utilizzato un livello minimo di automazione.

Nota L'esempio descritto in questo articolo è disponibile per il download. Le istruzione per il download sono fornite nella sezione Download alla fine di questo articolo.

Informazioni

Creazione del modello XML per la cartella di lavoro

  1. Creare una nuova cartella, C:\ExcelXML.
  2. In Excel avviare una nuova cartella di lavoro. Aggiungere i dati seguenti alle celle A1:F2 della cartella di lavoro nelle celle indicate. La cella F2 deve essere immessa come formula.

    A1: Order ID B1: Product ID C1: Unit Price D1: Quantity E1: Discount F1: Total
    A2: aaa B2: 111 C2: 222 D2: 333 E2: 0 F2: =C2*D2*(1-E2)
  3. Nella cella F3 digitare la formula seguente:
    =SOMMA(F$2:F2)
  4. Selezionare le celle A1:F1. Scegliere
    Celle dal menu Formato. Applicare un tipo di carattere in grassetto, un bordo inferiore e una tinta unita per l'ombreggiatura delle celle. Scegliere OK.
  5. Selezionare le colonne A:F. Scegliere
    Colonna dal menu Formato, quindi fare clic su
    Larghezza. Digitare 15 come larghezza della nuova colonna, quindi scegliere OK. Con le colonne A:F ancora selezionate, scegliere Celle dal menu
    Formato. Nella scheda Allineamentoselezionare Al centro nell'elenco dell'orientamento orizzontale, quindi scegliere OK.
  6. Selezionare la colonna E. Scegliere Celledal menu Formato. Nella scheda Numero fare clic su Percentuale e specificare 0 posizioni decimali, quindi scegliere OK.
  7. Selezionare la colonna F. Scegliere Celledal menu Formato. Nella scheda Numeroscegliere Contabilità, quindi OK.
  8. Selezionare le celle A3:F3. Scegliere Altezza righe dal menu Modifica, digitare
    25, quindi scegliere OK. Con le celle A3:F3 ancora selezionate, scegliere Celle dal menu
    Formato. Applicare un bordo superiore alle celle, quindi scegliere OK.
  9. Scegliere Opzioni dal menu
    Strumenti. Nella scheda Visualizzadeselezionare la casella di controllo Griglia, quindi scegliere OK.
  10. Selezionare la riga 2. Dal menu Finestrascegliere Blocca riquadri.
  11. Selezionare la cella A1.
  12. Scegliere Salva con nome dal menu
    File. Selezionare la cartella C:\ExcelXML e salvare la cartella di lavoro come "Orders.xsl" nel formato Foglio di calcolo XML.

    Nota: nella casella del nome file della finestra di dialogo
    Salva con nome racchiudere il nome tra virgolette in modo che non venga aggiunta al nome del file l'estensione xml.
  13. Chiudere Excel.
  14. Aprire Orders.xsl in un editor di testo, ad esempio il Blocco note.
  15. Inserire quanto riportato di seguito tra il tag <?xml version="1.0"?> e il tag <Workbook>:
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
    <xsl:template match="/">
    <xsl:pi name="xml">version="1.0"</xsl:pi>
  16. Alla fine di Orders.xsl aggiungere le due seguenti righe:
    </xsl:template>
    </xsl:stylesheet>
  17. Individuare la seguente coppia di tag in Orders.xsl (rappresenta le celle A2:F2 nel foglio di lavoro):
       <Row ss:Height="14.25">
    <Cell><Data ss:Type="String">aaa</Data></Cell>
    <Cell><Data ss:Type="Number">111</Data></Cell>
    <Cell><Data ss:Type="Number">222</Data></Cell>
    <Cell><Data ss:Type="Number">333</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:Formula="=RC[-3]*RC[-2]*1*(1-RC[-1])"><Data ss:Type="Number">73926</Data></Cell>
    </Row>
    e sostituirla con il seguente codice XSL:
    <xsl:for-each select="xml/rs:data/z:row">
    <Row ss:AutoFitHeight="0" ss:Height="13.5">
    <Cell><Data ss:Type="String"><xsl:value-of select="@OrderID"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="@ProductID"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="@UnitPrice"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="@Quantity"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="@Discount"/></Data></Cell>
    <Cell ss:Formula="=RC[-3]*RC[-2]*(1-RC[-1])"><Data ss:Type="Number">0</Data></Cell>
    </Row>
    </xsl:for-each>
  18. Salvare le modifiche in Orders.xsl e chiudere il file.

Utilizzo di Visual Basic per trasformare in un foglio di calcolo XML un recordset ADO con dati XML conservati

  1. In Visual Basic creare un nuovo progetto EXE standard.
  2. Scegliere Riferimenti dal menu
    Progetto. Selezionare le librerie di tipi per
    Microsoft ActiveX Data Object 2.5 (o versione successiva) e
    Microsoft XML 3.0.
  3. Aggiungere CommandButton a Form1, quindi aggiungere il codice seguente all'evento Click del pulsante:
        Const sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    "C:\program files\microsoft office\office10\samples\Northwind.mdb"
    Const sOutXML = "C:\ExcelXML\Orders.xml"
    Const sXSL = "C:\ExcelXML\Orders.xsl"

    'Retrieve an ADO recordset of the Orders Detail table in Northwind.
    Dim nRecords As Long, nFields As Long
    Dim rs As New ADODB.Recordset
    rs.Open "SELECT * FROM [Order Details]", sConn, adOpenStatic, adLockOptimistic

    'Persist the recordset to a new DOMDocument and store the record count.
    Dim oXML As New DOMDocument
    rs.Save oXML, adPersistXML
    nRecords = rs.RecordCount
    nFields = rs.Fields.Count
    rs.Close

    'Load the XSL (the workbook template with XSL directives) into a DOMDocument.
    Dim oXSL As New DOMDocument
    oXSL.Load sXSL

    'Transform the XML using the style sheet.
    Dim oResults As New DOMDocument
    oXML.transformNodeToObject oXSL, oResults

    If oXSL.parseError.errorCode <> 0 Then
    MsgBox "Parse Error: " & oResults.parseError.reason
    Else
    'Modify the ss:ExpandedRowCount attribute for the <table> node to
    'indicate the correct number of rows (count of records + 1 row for
    'the header + 1 row for the total).
    Dim oTable As MSXML2.IXMLDOMElement
    Set oTable = oResults.selectSingleNode("Workbook/Worksheet/Table")
    oTable.setAttribute "ss:ExpandedRowCount", nRecords + 2

    '***************************************
    'Save the results to a file.
    Open sOutXML For Output As #1
    Print #1, oResults.xml
    Close #1

    'Open the XML in Excel.
    Dim oExcel As Object
    Set oExcel = CreateObject("Excel.Application")
    oExcel.Workbooks.Open sOutXML
    oExcel.Visible = True
    oExcel.UserControl = True
    '***************************************
    End If
    Nota Se necessario, modificare la stringa di connessione (Const sConn) in modo che contenga il corretto percorso di installazione del database di esempio di Access Northwind.
  4. Premere F5 per eseguire il programma.
  5. Fare clic sul pulsante in Form1. I dati della tabella Dettagli ordini del database Northwind vengono visualizzati nel modello di cartella di lavoro di Excel creato.
L'esempio genera il foglio di calcolo XML ed automatizza Excel utilizzando il metodo Open dell'insieme Workbooks per aprire i dati XML come nuova cartella di lavoro. Come approccio alternativo è possibile inserire direttamente i dati XML in una specifica cella di un foglio di lavoro esistente. Per illustrare tale alternativa, sostituire con quanto segue il codice contenuto tra gli asterischi:
        'Display the data in a workbook starting at cell B2.
Dim oExcel As Object, oBook As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
oBook.Worksheets(1).Range("B2").Resize(nRecords + 2, nFields + 1).Value(11) = _
oResults.xml 'Note: xlRangeValueXMLSpreadsheet=11
oExcel.Visible = True
oExcel.UserControl = True
Dopo avere apportato questa modifica, eseguire di nuovo il programma. Notare che questa volta i dati del foglio di calcolo XML vengono inseriti in una nuova cartella di lavoro a partire dalla cella B2 del primo foglio di lavoro. I dati e i formati di cella vengono applicati all'intervallo, tuttavia questo approccio non consente di conservare le impostazioni specifiche delle righe, delle colonne, del foglio di lavoro e della cartella di lavoro.

Utilizzo di ASP per trasformare in un foglio di calcolo XML un recordset ADO con dati XML conservati

  1. Incollare il codice riportato di seguito nel Blocco note. Salvare il codice con il nome ExcelXML.asp nella cartella radice virtuale del server Web. Nota La cartella radice virtuale predefinita è C:\inetpub\wwwroot.
    <%@ Language="vbscript"%>

    <%

    Const sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\program files\microsoft office\office10\samples\Northwind.mdb"
    Const sXSL = "C:\ExcelXML\Orders.xsl"

    Response.Buffer = True

    'Retrieve an ADO recordset of the Orders Detail table in Northwind.
    Dim rs, nRecords
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SELECT * FROM [Order Details]", sConn, 3, 3

    'Persist the recordset to a new DOMDocument and store the record count.
    Dim oXML
    Set oXML = CreateObject("Microsoft.XMLDOM")
    rs.Save oXML, 1
    nRecords = rs.RecordCount
    rs.Close

    'Load the XSL (the workbook template with XSL directives) into a DOMDocument.
    Dim oXSL
    Set oXSL = CreateObject("Microsoft.XMLDOM")
    oXSL.Load sXSL

    'Transform the XML using the style sheet.
    Dim oResults
    Set oResults = CreateObject("Microsoft.XMLDOM")
    oXML.transformNodeToObject oXSL, oResults

    If oXSL.parseError.errorCode <> 0 Then
    Response.Write "Parse Error: " & oResults.parseError.reason
    Else

    'Modify the ss:ExpandedRowCount attribute for the <table> node in the XSL.
    Dim oTable
    Set oTable = oResults.selectSingleNode("Workbook/Worksheet/Table")
    oTable.setAttribute "ss:ExpandedRowCount", nRecords + 2

    'Return the resulting XML Spreadsheet for display in Excel.
    Response.ContentType = "application/vnd.ms-excel"
    Response.Charset = "ISO-8859-1"
    Response.Write oResults.XML
    Response.Flush

    End If

    %>
    Nota Se necessario, modificare la stringa di connessione (Const sConn) in modo che contenga il corretto percorso di installazione del database di esempio di Access Northwind.

  2. Avviare Internet Explorer e collegarsi a http://ServerWeb/ExcelXML.asp, dove ServerWeb è il nome del server Web. I dati della tabella Dettagli ordini del database Northwind vengono visualizzati nel modello di cartella di lavoro di Excel creato.

Note aggiuntive

Le seguenti caratteristiche di Excel non possono essere conservate nel formato Foglio di calcolo XML:
  • Grafici
  • Oggetti OLE
  • Disegno di forme
  • Progetti VBA
  • Gruppi e bordi

Download

XMLSpread.exe contiene il foglio di stile XML, il progetto di Visual Basic e lo script ASP descritti in questo articolo.
Il seguente file è disponibile per il download dall'Area download Microsoft (l'installazione è in inglese):
Download del pacchetto Xmlsprd.exe Data di rilascio: 27 marzo 2001

Per ulteriori informazioni sul download di file di supporto Microsoft, fare clic sul numero dell'articolo della Microsoft Knowledge Base riportato di seguito:
119591 Come ottenere file di supporto Microsoft dai servizi online
Il file è stato controllato e non contiene virus. Microsoft ha utilizzato il software antivirus più recente disponibile al momento della data di pubblicazione del file. Il file viene salvato su server con un livello di protezione avanzata che impedisce modifiche non autorizzate.

Riferimenti

Per ulteriori informazioni sul salvataggio di recordset ADO nel formato XML, visitare il seguente sito Web MSDN (informazioni in lingua inglese): Per ulteriori informazioni sui flussi di contenuto MIME per le applicazioni di Microsoft Office, fare clic sui numeri degli articoli della Microsoft Knowledge Base riportati di seguito:
266263 BUG: Visualizzazione dell'origine ASP in Word 2000 e in Excel 2000 durante l'utilizzo di tipi MIME per il flusso di dati

199841 Visualizzazione dei risultati ASP utilizzando Excel in IE con tipi MIME

271572 Formattazione di una cartella di lavoro di Excel con il flusso di contenuto MIME

307021 Trasferimento di dati XML a Microsoft Excel 2002 mediante Visual Basic .NET

Proprietà

ID articolo: 285891 - Ultima revisione: 14 giu 2007 - Revisione: 1

Feedback