Bagaimana untuk mengubah DataSet untuk spreadsheet XML untuk Excel dengan menggunakan Visual Basic.NET dan ASP.NET

Artikel ini selangkah demi selangkah menjelaskan cara untuk mengubah DataSet untuk Spreadsheet XML yang dapat diberikan dalam Excel. Excel Spreadsheet XML format mendukung elemen tag dan atribut untuk Excel fungsi seperti multi-sheet buku kerja, rumus, dan sel format.

Artikel ini mengasumsikan bahwa Anda sudah familiar dengan berikut topik:

• Extensible Stylesheet Language Transformations
• Excel Spreadsheet XML
• ASP.NET

Contoh aplikasi web

Sekilas pandang

Sampel langkah demi langkah ini menggambarkan bagaimana sisi server dan komponen sisi klien bekerja sama untuk memberikan dan menampilkan Excel Spreadsheet XML.

• Default.htm adalah bingkai yang memiliki dua bingkai. Bingkai header berisi daftar urutan id dan bingkai utama menampilkan informasi permintaan setelah pesanan ID dipilih dari daftar.
• Header.htm berisi elemen HTML tag dan script untuk header bingkai.
• GetData.VB adalah sebuah handler HTTP yang melayani dua tujuan. Handler menambahkan Daftar agar ID header frame, dan itu proses permintaan pesanan informasi yang ditampilkan dalam bingkai utama. Ketika menerima HTTP handler permintaan untuk informasi permintaan, menciptakan DataSet untuk perintah dan mengembalikan representasi XML yang DataSet untuk penelepon.
• Transform.XSLT adalah XSLT file yang digunakan untuk mengubah DataSet XML untuk Excel Spreadsheet XML.

Contoh kode menggunakan urutan data di Microsoft SQL Server Database contoh Northwind. Sampel mengasumsikan bahwa Anda akan membuat dan menguji Aplikasi web di komputer lokal (http://localhost). Jika Anda tidak akan menggunakan Web server pada komputer lokal, menggantikan localhost dalam URL sampel dengan nama server Web Anda.

Menghasilkan DataSet

Dalam bagian ini, Anda membuat HTTP handler dan sisi klien komponen yang diperlukan untuk mengambil informasi permintaan. The DataSet kembali ke bingkai utama sebagai polos XML; The DataSet tidak berubah.

1. Membuat proyek Web kosong yang bernama ExcelTransform. Untuk melakukan ini, ikuti langkah berikut:
   1. Pada Berkas menu dalam Visual Studio. NET, klik Baru, lalu klik Project.
   2. Klik Proyek-proyek Visual Basic, lalu klik Proyek Web kosong template.
   3. Nama proyek http://localhost/ExcelTransform, dan kemudian klik Oke.
2. Menambahkan referensi ke proyek. Untuk melakukannya, ikuti langkah-langkah:
   1. Pada Project menu, klik Menambahkan referensi.
   2. Dalam daftar komponen pada .NET tab, klik System.data.dll, lalu klik Pilih.
   3. Ulangi langkah sebelumnya untuk System.dll, untuk System.web.dll, dan untuk System.xml.dll.
   4. Klik Oke.
3. Pada Project menu, klik Menambahkan kelas, nama kelas GetData.VB, lalu klik Oke.
4. Ganti kode GetData.VB dengan berikut.
   
   Catatan Anda harus mengubah ID pengguna <username> dan password =<strong password=""> untuk nilai-nilai yang benar sebelum Anda menjalankan ini kode. Pastikan bahwa ID pengguna memiliki izin yang sesuai untuk melakukan ini operasi pada database.</strong> </username>

   Imports System.Web
   Imports System.Xml
   Imports System.Xml.Xsl
   Imports System.Data
   Imports System.Data.SqlClient
   
   Public Class GetData
      Implements IHttpHandler
   
      Private sConn As String = _
         "User ID=<username>;Password=<strong password>;Initial Catalog=Northwind;Data Source=YourSQLServer;"
   
      Public ReadOnly Property IsReusable() As Boolean _
      Implements IHttpHandler.IsReusable
         Get
            Return False
         End Get
      End Property
   
      Public Sub ProcessRequest(ByVal context As HttpContext) _
      Implements IHttpHandler.ProcessRequest
   
         Dim conn As SqlConnection
         Dim sOrderRequested As String
         sOrderRequested = context.Request.Item("OrderID")
   
         If Not (sOrderRequested > "") Then
   
            '=== If no order is requested, assume that this is a request
            '=== to fill the drop-down list in the Header.htm template
            '=== with the list of OrderIDs.
   
            'Get a DataSet for a list of OrderIDs.
            Dim sSQL As String = "Select OrderID from Orders"
            conn = New SqlConnection(sConn)
            conn.Open()
            Dim cmd As New SqlCommand(sSQL, conn)
            Dim rdr As SqlDataReader = cmd.ExecuteReader
   
            'Open the header template for the frameset and fill
            'in the <option> child nodes for the drop-down lists.
            Dim sHTML As String, sOrderID As String
            Dim xmlDoc As New XmlDocument()
            xmlDoc.Load(context.Server.MapPath("header.htm"))
            Dim oElem As XmlElement = _
               xmlDoc.DocumentElement.GetElementsByTagName("select").Item(0)
            Dim oChild As XmlElement
            Do While rdr.Read
               sOrderID = rdr.GetInt32(0).ToString
               oChild = xmlDoc.CreateElement("option")
               oChild.SetAttribute("value", sOrderID)
               oChild.InnerText = sOrderID
               oElem.AppendChild(oChild)
            Loop
            rdr.Close()
            conn.Close()
   
            'Return the modified header template.
            context.Response.Write(xmlDoc.InnerXml)
   
         Else
   
            '=== If an order is requested, create a DataSet for that
            '=== order and return the results to the client browser.
   
            'Build a DataSet for the order.
            conn = New SqlConnection(sConn)
            conn.Open()
            Dim ds As DataSet = New DataSet("Order")
            Dim CustDa As SqlDataAdapter = New SqlDataAdapter( _
               "SELECT OrderID, CompanyName, Address, City, Region, PostalCode, Country, Freight " & _
               "FROM Customers " & _
               "INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID " & _
               "WHERE (((Orders.OrderID)=" & sOrderRequested & "))", conn)
            CustDa.Fill(ds, "Customer")
            Dim ItemsDa As SqlDataAdapter = New SqlDataAdapter( _
               "SELECT Products.ProductName, [Order Details].Quantity, " & _
               "  [Order Details].[UnitPrice]*[Quantity]*(1-[Discount]) AS ItemTotal " & _
               "FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID " & _
               "WHERE (([Order Details].[OrderID]) = " & sOrderRequested & ")", conn)
            ItemsDa.Fill(ds, "Items")
            conn.Close()
   
            SendResults(context, ds)
            context.Response.End()
   
         End If
   
      End Sub
   
       Private Sub SendResults(ByVal context As HttpContext, ByVal ds As DataSet)
            'Write the XML for the DataSet.
            context.Response.ContentType = "text/xml"
            context.Response.Output.Write(ds.GetXml)
            context.Response.End()
       End Sub
   End Class

   CATATAN: GetData.VB, mengubah tugas untuk sConn untuk koneksi sah string untuk komputer yang menjalankan SQL Server dan yang berisi Northwind sampel database.
   
   
5. Pada Project menu, klik Menambah Item baru, klik File konfigurasi web template, dan kemudian klik Oke.
6. Ganti kode dalam Web.config dengan berikut:

   <?xml version="1.0" encoding="utf-8" ?>
   <configuration>
      <system.web>
         <httpHandlers>
            <add verb="*" path="GetData.aspx" type="ExcelTransform.GetData, ExcelTransform" />
         </httpHandlers>
      </system.web>
   </configuration> 

7. Pada Project menu, klik Tambahkan halaman HTML, nama halaman Header.htm, dan kemudian klik Oke.
8. Pada Lihat menu, klik Sumber HTML.
9. Ganti kode dalam Header.htm dengan berikut:

   <html>
      <script language="javascript">
      <!--
      function OrderSelect_onchange() {
         window.parent.frames("main").location.replace("about:blank");
         if(OrderSelect.selectedIndex>0) {
            window.setTimeout("OpenOrder()", 50); 
         }
      }
      function OpenOrder() {
         var order = OrderSelect.options(OrderSelect.selectedIndex).value;
         window.parent.frames("main").location.href=
            "http://localhost/exceltransform/getdata.aspx?orderid=" + order;
      }
      //-->
      </script>
      <body>
         <select id="OrderSelect" onchange="return OrderSelect_onchange()">
            <option value="0">Select an Order</option>
         </select>
      </body>
   </html>

   CATATAN: HTTP handler beban Header.htm sebagai dokumen XML. Untuk me-load tanpa kesalahan, Header.htm harus well-formed XML. Jika Anda mengubah Header.htm, Pastikan bahwa well-formed; semua elemen harus tepat awal dan akhir Tag, dan semua atribut tugas harus tertutup dalam kutipan menandai.
   
   
10. Pada Project menu, klik Tambahkan halaman HTML, klik Bingkai template, nama file Default.htm, dan kemudian klik Oke. Ketika diminta, klik Header jenis bingkai, dan kemudian klik Oke.
11. Pada Lihat menu, klik Sumber HTML.
12. Pada Frameset.htm, src dan bergulir atribut untuk header frame sebagai berikut:

    <frame name="header" src="http://localhost/exceltransform/getdata.aspx" scrolling="yes" noresize>

13. Dalam solusi Explorer, klik kanan Default.htm, lalu klik Set sebagai halaman awal.
14. Ikuti langkah-langkah ini untuk menguji sampel:
    1. Pada Debug menu, klik Mulai tanpa Debugging. Rangkaian bingkai akan terbuka dalam browser dengan daftar drop-down di bingkai header yang berisi daftar urutan id.
    2. Pilih order ID dari daftar drop-down.
    3. Memeriksa XML yang muncul dalam bingkai utama. XML adalah representasi DataSet yang GetData HTTP handler dibuat menurut permintaan Anda.
    4. Pilih perintah tambahan id dari daftar drop-down untuk memeriksa hasil.
    5. Ketika Anda selesai menguji aplikasi Web, berhenti Microsoft Internet Explorer.

Mengubah DataSet untuk Excel XML

Dalam bagian ini, Anda menambahkan kode tambahan untuk mengubah DataSet untuk ditampilkan di Excel.

1. Buka proyek ExcelTransform Web yang Anda hanya dibuat.
2. Pada Project menu, klik Menambah Item baru, klik XSLT File template, nama proyek Transform.xslt, dan kemudian klik Oke.
3. Ganti isi Transform.xslt dengan berikut:

   <xsl:stylesheet version="1.0"
       xmlns="urn:schemas-microsoft-com:office:spreadsheet"
       xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
   	xmlns:msxsl="urn:schemas-microsoft-com:xslt"
   	xmlns:user="urn:my-scripts"
   	xmlns:o="urn:schemas-microsoft-com:office:office"
   	xmlns:x="urn:schemas-microsoft-com:office:excel"
   	xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >   
   
   <xsl:template match="Order">
   
   <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:html="http://www.w3.org/TR/REC-html40">
   
    <Styles>
     <Style ss:ID="Default" ss:Name="Normal">
      <Alignment ss:Vertical="Bottom"/>
      <Borders/>
      <Font/>
      <Interior/>
      <NumberFormat/>
      <Protection/>
     </Style>
     <Style ss:ID="s21">
      <Font ss:Bold="1"/>
      <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
     </Style>
     <Style ss:ID="s22">
      <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
      <Font ss:Bold="1"/>
      <Interior ss:Color="#99CCFF" ss:Pattern="Solid"/>
     </Style>
     <Style ss:ID="s23" ss:Name="Currency">
      <NumberFormat
       ss:Format="_(&quot;$&quot;* #,##0.00_);_(&quot;$&quot;* \(#,##0.00\);_(&quot;$&quot;* &quot;-&quot;??_);_(@_)"/>
     </Style>
     <Style ss:ID="s24">
      <NumberFormat ss:Format="_(* #,##0.00_);_(* \(#,##0.00\);_(* &quot;-&quot;??_);_(@_)"/>
     </Style>
     <Style ss:ID="s25">
      <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
     </Style>
    </Styles>
   
    <Worksheet>
    <xsl:attribute name="ss:Name">
      <xsl:value-of select='concat("Order #", Customer/OrderID)'/>
    </xsl:attribute>
     <Table ss:ExpandedColumnCount="3">
     <xsl:attribute name="ss:ExpandedRowCount" >
   	<xsl:value-of select="count(Items)+10"/>
     </xsl:attribute>
   
      <Column ss:AutoFitWidth="0" ss:Width="150"/>
      <Column ss:AutoFitWidth="0" ss:Width="100"/>
      <Column ss:AutoFitWidth="0" ss:Width="75"/>
      
      <xsl:apply-templates select="Customer"/>
   
      <Row>
       <Cell ss:StyleID="s21"><Data ss:Type="String">Item</Data></Cell>
       <Cell ss:StyleID="s21"><Data ss:Type="String">Quantity</Data></Cell>
       <Cell ss:StyleID="s21"><Data ss:Type="String">Total</Data></Cell>
      </Row>
   
      <xsl:apply-templates select="Items"/>
      
      <Row>
       <Cell ss:Index="2"><Data ss:Type="String">Subtotal</Data></Cell>
       <Cell ss:StyleID="s23" ss:Formula="=SUM(R8C:R[-1]C)"/>
      </Row>
      <Row>
       <Cell ss:Index="2"><Data ss:Type="String">Freight</Data></Cell>
       <Cell ss:StyleID="s23"><Data ss:Type="Number"><xsl:value-of select="Customer/Freight"/></Data></Cell>
      </Row>
      <Row>
       <Cell ss:Index="2"><Data ss:Type="String">Total</Data></Cell>
       <Cell ss:StyleID="s23" ss:Formula="=R[-2]C+R[-1]C"/>
      </Row>
     </Table>
    </Worksheet>
   </Workbook>
   
   </xsl:template>
   
   <xsl:template match="Customer">
      <Row>
       <Cell><Data ss:Type="String"><xsl:value-of select="CompanyName"/></Data></Cell>
      </Row>
      <Row>
       <Cell><Data ss:Type="String"><xsl:value-of select="Address"/></Data></Cell>
      </Row>
      <Row>
       <Cell><Data ss:Type="String"><xsl:value-of select='concat(City, ", ", Region, " ", PostalCode)'/></Data></Cell>
      </Row>
      <Row>
       <Cell><Data ss:Type="String"><xsl:value-of select="Country"/></Data></Cell>
      </Row>
      <Row ss:Index="6">
       <Cell ss:MergeAcross="2" ss:StyleID="s22">
        <Data ss:Type="String">Order #<xsl:value-of select="OrderID"/></Data>
       </Cell>
      </Row>   
   </xsl:template>
   
   <xsl:template match="Items">
      <Row>
       <Cell><Data ss:Type="String"><xsl:value-of select="ProductName"/></Data></Cell>
       <Cell ss:StyleID="s25"><Data ss:Type="Number"><xsl:value-of select="Quantity"/></Data></Cell>
       <Cell ss:StyleID="s24"><Data ss:Type="Number"><xsl:value-of select="ItemTotal"/></Data></Cell>
      </Row>
   </xsl:template>
   
   </xsl:stylesheet>

4. Dalam GetData.VB, menggantikan SendResults fungsi dengan berikut:

   Private Sub SendResults(ByVal context As HttpContext, ByVal ds As DataSet)
       Dim sOrderID As String = ds.Tables(0).Rows(0).Item(0)
   
       'Set up the response for Excel.
       context.Response.ContentType = "application/vnd.ms-excel"
       context.Response.Charset = ""
   
       'Transform the DataSet XML using transform.xslt 
       'and return the results to the client in Response.Outputstream.
       Dim tw As XmlTextWriter
       Dim xmlDoc As XmlDataDocument = New XmlDataDocument(ds)
       Dim xslTran As XslTransform = New XslTransform()
       xslTran.Load(context.Server.MapPath("transform.xslt"))
       xslTran.Transform(xmlDoc, Nothing, context.Response.OutputStream)
       context.Response.End()
   End Sub

5. Ikuti langkah-langkah ini untuk menguji sampel:
   1. Pada Debug menu, klik Mulai tanpa Debugging. Rangkaian bingkai akan terbuka dalam browser dengan daftar drop-down di bingkai header yang berisi daftar urutan id.
   2. Pilih order ID dari daftar drop-down. Perhatikan bahwa The DataSet Anda diminta dibangun, dan berubah menjadi Spreadsheet XML dan ditampilkan di Excel. Data dalam Excel berisi format dan perhitungan.
   3. Ketika Anda selesai menguji aplikasi Web, berhenti Internet Explorer.

Menyimpan XML berubah dalam file (opsional)

Dalam bagian sebelumnya, HTTP penangan sungai berubah XML untuk klien. Anda mungkin ingin menyimpan DataSet XML dan hasil transformasi dalam file. Ini dapat langkah pemecahan masalah yang berguna jika Anda menemukan bahwa transformasi tidak memberikan Anda hasil yang Anda harapkan. Anda dapat menggunakan pendekatan ini untuk memeriksa DataSet XML dan XML berubah untuk potensi kesalahan.

CATATAN: Contoh menyimpan file XML dalam folder Aplikasi Web. Anda mungkin harus mengubah izin untuk map, seperti yang ditunjukkan dalam langkah-langkah berikut.

1. Menetapkan izin pada folder untuk ASP.NET proses:
   1. Mulai Windows Explorer.
   2. Cari folder Aplikasi Web. Default path adalah C:\Inetpub\Wwwroot\ExcelTransform.
   3. Klik kanan ExcelTransform folder, dan kemudian klik Properti.
   4. Pada Keamanan tab, klik Tambahkan.
   5. Di bawah Masukkan nama objek untuk memilih, ketik nama objek YourComputerName\aspnet, dan kemudian klik Oke.
   6. Pada Keamanan tab, klik untuk memilih Menulis untuk memungkinkan izin menulis YourComputerName\aspnet account, dan kemudian klik Oke.
2. Dalam GetData.VB, menggantikan SendResults fungsi dengan berikut:

   Private Sub SendResults(ByVal context As HttpContext, ByVal ds As DataSet)
       Dim sOrderID As String = ds.Tables(0).Rows(0).Item(0)
   
       'First, save the XML representation of the DataSet in a file
       'and add a processing instruction to the XML so that it can be
       'transformed client-side.
       Dim tw As XmlTextWriter
       tw = New XmlTextWriter(context.Server.MapPath("order" & sOrderID & ".xml"), System.Text.Encoding.UTF8)
       tw.Formatting = Formatting.Indented
       tw.Indentation = 3
       tw.WriteStartDocument()
       tw.WriteProcessingInstruction("xml-stylesheet", _
   	"type='text/xsl' href='http://localhost/ExcelTransform/transform.xslt'")
       ds.WriteXml(tw)
       tw.Close()
   
       'Second, transform the DataSet XML and save it to a file.
       Dim xmlDoc As XmlDataDocument = New XmlDataDocument(ds)
       Dim xslTran As XslTransform = New XslTransform()
       xslTran.Load(context.Server.MapPath("transform.xslt"))
       tw = New XmlTextWriter(context.Server.MapPath("order" & sOrderID & ".xls"), System.Text.Encoding.UTF8)
       tw.Formatting = Formatting.Indented
       tw.Indentation = 3
       tw.WriteStartDocument()
       xslTran.Transform(xmlDoc, Nothing, tw)
       tw.Close()
   
       'Optionally, redirect to the saved transformation.
       context.Response.Redirect( _
   		"http://localhost/ExcelTransform/order" & sOrderID & ".xls")
       context.Response.End()
   End Sub

3. Ikuti langkah-langkah ini untuk menguji sampel:
   1. Pada Debug menu, klik Mulai tanpa Debugging.
   2. Pilih perintah dalam daftar drop-down. Berubah XML muncul dalam bingkai utama di Excel.
   3. Memeriksa mentah DataSet XML, C:\Inetpub\Wwwroot\ExcelTransform\orderNNNNN.xml. Anda dapat membuka XML dalam editor teks apapun atau Excel. Jika Anda membuka Order.xml di Excel, Anda diminta untuk menerapkan stylesheet.
   4. Memeriksa Spreadsheet XML, C:\Inetpub\Wwwroot\ExcelTransform\orderNNNNN.xls. Anda dapat membuka XML dalam editor teks apapun atau Excel.

Komentar

Ketika Anda membangun Anda sendiri XSLT file Excel, pertama kali membuat buku kerja template dalam Excel yang berisi format dan rumus yang Anda perlu, dan kemudian Simpan buku kerja dalam Spreadsheet XML. Anda kemudian dapat memodifikasi XML sehingga itu mengandung XSL ekspresi dan unsur-unsur yang Anda butuhkan untuk dengan benar mengubah Anda DataSet XML. ketika Anda mengubah XML yang Anda diselamatkan dari Excel, catatan berikut:

• Sel-sel pada lembar kerja yang diwakili oleh <table></table> unsur dalam XML. The <table></table> memiliki dua atribut, SS:ExpandedColumnCount dan SS:ExpandedRowCount, yang menetapkan sel-sel yang digunakan dalam lembar kerja (itu adalah, "digunakan kisaran"). Jika lembar kerja memiliki berbagai jumlah baris atau kolom (atau keduanya), mengatur atribut ini selama transformasi. Perhatikan bahwa SS:ExpandedRowCount atribut di Transform.XSLT set didasarkan pada jumlah node tertentu di DataSet XML.
• Rumus dalam sel diwakili dalam notasi RC, dan tidak dalam notasi A1.
• Masing-masing berbeda format kombinasi yang Anda Terapkan untuk sel-sel pada lembar kerja diberikan gaya sendiri. Gaya yang disimpan dalam <style></style> elemen XML Spreadsheet.

Catatan Pengembang yang menggunakan.NET Framework 1.1 harus mengetahui perubahan-perubahan yang telah dibuat untuk XslTransform kelas antara.NET Framework 1.0 dan.NET Framework 1.1. Sebagai contoh, beberapa versi kelebihan beban beban dan mengubah metode (termasuk versi yang digunakan dalam artikel ini) yang dianggap usang dimulai dengan.NET Framework 1.1. Untuk informasi tambahan tentang XslTransform.Transform metode, kunjungi Web site Microsoft Developer Network (MSDN) berikut:Untuk informasi tambahan, klik nomor artikel di bawah ini untuk melihat artikel pada Basis Pengetahuan Microsoft: