Fitur baru XML Literal ini seperti yang dapat dilihat di artikel-artikel saya sebelumnya tidak terbatas pemakaiannya. Kita dapat gunakan untuk membuat file xml, file html. Kali ini saya akan buatkan contoh penggunaan XML Literal untuk membuat file excel yang berekstensi .xml.
Versi Office yang akan saya gunakan yaitu Office 2007. File excel yang dibuat akhirnya nanti disimpan dengan tipe xml spreadsheet 2003 (.xml). File ini karena memiliki ekstensi .xml maka kita bisa membukanya dengan text editor seperti Notepad atau Wordpad. Nah konten file xml inilah yang nantinya akan dimanipulasi oleh XML Literal. Namun tipe xml spreadsheet ini memiliki kekurangan diantaranya adalah tidak dapat menampilkan Chart object...sayang sekali...padahal kalau bisa kita bisa manipulasi object Chart secara run time dengan XML Literal ini.
Pada artikel kali ini saya akan membuat file excel yang diisi dengan record yang terdapat didalam tabel database Northwind (database favorit :))...Mari kita lihat langkah-langkahnya.
1. Buat file excel baru...ketikkan header kolom yang akan ditampilkan pada tiap cell nya seperti pada gambar dibawah ini (isi recordnya nanti akan diisi secara run time) :
2. Kemudian save file excel tersebut dengan tipe "Xml Spreadsheet 2003 (.xml)"
3. Buat sebuah windows form application. Tambahkan item "Linq to Sql Classes", drag and drop beberapa tabel yang ada di database Northwind yang berhubungan dengan tabel Products ke OR designer...atau anda bisa buat sebuah view lalu drag n drop ke OR designer nya..pada contoh ini saya akan buat sebuah view untuk mengakomodasi data yang dibutuhkan di atas :
4. Buka file excel yang telah dibuat dengan menggunakan text editor, misalnya Notepad. Kemudian copy semua konten file xml tersebut dan di paste pada variabel yang dideklarasikan di vb code nya. Variabel ini nantinya akan inference type berdasarkan xml literal yang digunakan, dapat berupa XElement atau XDocument. Berikut code nya :
1: Private Sub btnCreateExcelFile_Click() Handles btnCreateExcelFile.Click
2:
3: Dim xmlExcel = <?xml version="1.0"?>
4: <?mso-application progid="Excel.Sheet"?>
5: <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
6: xmlns:o="urn:schemas-microsoft-com:office:office"
7: xmlns:x="urn:schemas-microsoft-com:office:excel"
8: xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
9: xmlns:html="http://www.w3.org/TR/REC-html40">
10: <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
11: <Author>Rully YMF</Author>
12: <LastAuthor>Rully YMF</LastAuthor>
13: <Created>2008-01-03T04:22:46Z</Created>
14: <LastSaved>2008-01-03T04:33:11Z</LastSaved>
15: <Company>Roel</Company>
16: <Version>12.00</Version>
17: </DocumentProperties>
18: <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
19: <WindowHeight>8220</WindowHeight>
20: <WindowWidth>19005</WindowWidth>
21: <WindowTopX>120</WindowTopX>
22: <WindowTopY>30</WindowTopY>
23: <ProtectStructure>False</ProtectStructure>
24: <ProtectWindows>False</ProtectWindows>
25: </ExcelWorkbook>
26: <Styles>
27: <Style ss:ID="Default" ss:Name="Normal">
28: <Alignment ss:Vertical="Bottom"/>
29: <Borders/>
30: <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
31: <Interior/>
32: <NumberFormat/>
33: <Protection/>
34: </Style>
35: <Style ss:ID="s62">
36: <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
37: ss:Bold="1"/>
38: </Style>
39: </Styles>
40: <Worksheet ss:Name="Sheet1">
41: <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="1" x:FullColumns="1"
42: x:FullRows="1" ss:DefaultRowHeight="15">
43: <Column ss:AutoFitWidth="0" ss:Width="99.75"/>
44: <Column ss:AutoFitWidth="0" ss:Width="105.75"/>
45: <Column ss:AutoFitWidth="0" ss:Width="90.75"/>
46: <Column ss:Index="5" ss:AutoFitWidth="0" ss:Width="66.75"/>
47: <Row>
48: <Cell ss:StyleID="s62"><Data ss:Type="String">Product Name</Data></Cell>
49: <Cell ss:StyleID="s62"><Data ss:Type="String">Category</Data></Cell>
50: <Cell ss:StyleID="s62"><Data ss:Type="String">Supplier</Data></Cell>
51: <Cell ss:StyleID="s62"><Data ss:Type="String">Stock</Data></Cell>
52: <Cell ss:StyleID="s62"><Data ss:Type="String">Price</Data></Cell>
53: </Row>
54: </Table>
55: <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
56: <PageSetup>
57: <Header x:Margin="0.3"/>
58: <Footer x:Margin="0.3"/>
59: <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
60: </PageSetup>
61: <Print>
62: <ValidPrinterInfo/>
63: <PaperSizeIndex>128</PaperSizeIndex>
64: <HorizontalResolution>120</HorizontalResolution>
65: <VerticalResolution>72</VerticalResolution>
66: </Print>
67: <Selected/>
68: <Panes>
69: <Pane>
70: <Number>3</Number>
71: <ActiveRow>1</ActiveRow>
72: <ActiveCol>5</ActiveCol>
73: </Pane>
74: </Panes>
75: <ProtectObjects>False</ProtectObjects>
76: <ProtectScenarios>False</ProtectScenarios>
77: </WorksheetOptions>
78: </Worksheet>
79: <Worksheet ss:Name="Sheet2">
80: <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
81: x:FullRows="1" ss:DefaultRowHeight="15">
82: </Table>
83: <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
84: <PageSetup>
85: <Header x:Margin="0.3"/>
86: <Footer x:Margin="0.3"/>
87: <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
88: </PageSetup>
89: <ProtectObjects>False</ProtectObjects>
90: <ProtectScenarios>False</ProtectScenarios>
91: </WorksheetOptions>
92: </Worksheet>
93: <Worksheet ss:Name="Sheet3">
94: <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
95: x:FullRows="1" ss:DefaultRowHeight="15">
96: </Table>
97: <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
98: <PageSetup>
99: <Header x:Margin="0.3"/>
100: <Footer x:Margin="0.3"/>
101: <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
102: </PageSetup>
103: <ProtectObjects>False</ProtectObjects>
104: <ProtectScenarios>False</ProtectScenarios>
105: </WorksheetOptions>
106: </Worksheet>
107: </Workbook>
108:
109: End Sub
karena jumlah Worksheet yang akan saya pakai hanya satu, jadi kita bisa hapus elemen Worksheet mulai line-79 sampai line-106.
5. Perhatikan potongan baris kode dibawah ini yang berada pada line 47-53 :
1: <Row>
2: <Cell ss:StyleID="s62"><Data ss:Type="String">Product Name</Data></Cell>
3: <Cell ss:StyleID="s62"><Data ss:Type="String">Category</Data></Cell>
4: <Cell ss:StyleID="s62"><Data ss:Type="String">Supplier</Data></Cell>
5: <Cell ss:StyleID="s62"><Data ss:Type="String">Stock</Data></Cell>
6: <Cell ss:StyleID="s62"><Data ss:Type="String">Price</Data></Cell>
7: </Row>
Konten xml itulah yang nantinya akan kita duplikasi untuk diisi dengan record yang diambil dari view telah dibuat.
6. Modifikasi kode event handler yang terdapat pada langkah keempat dengan menambahkan xml literal yang akan digunakan untuk iterate record. XML Literal ini dibuat dengan melakukan copy-paste potongan kode pada langkah-5. Untuk mengisi data pada tiap cell nya menggunakan query expression linq. Akan tetapi sebelumnya kita harus melakukan Import xmlns yang digunakan pada elemen Data, karena potongan kode pada langkah 5 diatas akan di copy ke variabel xml literal. Perhatikan elemen xml namespace pada line 5-8 di langkah ke 4. Xml namespace itulah yang akan di copy dan di import. Berikut kode nya :
1: Imports <xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
2: Imports <xmlns="urn:schemas-microsoft-com:office:spreadsheet">
3: Imports <xmlns:o="urn:schemas-microsoft-com:office:office">
4: Imports <xmlns:x="urn:schemas-microsoft-com:office:excel">
7. Modifikasi kode pada langkah-4 dengan menambahkan potongan kode berikut :
1: Private Sub btnCreateExcelFile_Click() Handles btnCreateExcelFile.Click
2: Dim db As New ProductDetailsDataContext
3:
4: Dim data = (From record In db.vProductDetails _
5: Select <Row>
6: <Cell><Data ss:Type="String"><%= record.ProductName %></Data></Cell>
7: <Cell><Data ss:Type="String"><%= record.CategoryName %></Data></Cell>
8: <Cell><Data ss:Type="String"><%= record.CompanyName %></Data></Cell>
9: <Cell><Data ss:Type="Number"><%= record.UnitsInStock %></Data></Cell>
10: <Cell><Data ss:Type="Number"><%= record.UnitPrice %></Data></Cell>
11: </Row>).Take(10)
Pada langkah ke 7 ini saya gunakan linq yang digabungkan dengan xml literal untuk generate record secara run time pada tiap cell nya. Tipe data untuk cell stock dan price saya rubah ke "Number" agar bisa dilakukan operasi matematik, misalnya menghitung jumlah total stock. Data yang digunakan berasal dari view yang telah dibuat sebelumnya. Disini saya hanya akan mengambil 10 record saja, bagaimana caranya? Gunakan saja method Take dengan input parameter berupa jumlah record yang akan diambil...tiap-tiap cell diisi dengan kolom yang bersesuaian yang ingin ditampilkan.
8. Cari elemen berikut dibawah ini :
1: <Worksheet ss:Name="Sheet1">
2: <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount=<%= data.Count + 1 %> x:FullColumns="1"
3: x:FullRows="1" ss:DefaultRowHeight="15">
perhatikan atribut "ss:ExpandedRowCount" ...atribut ini digunakan untuk menampilkan jumlah record yang ingin ditampilkan. Nilainya diisi dengan menghitung jumlah record yang ada di variabel "data" berdasarkan hasil query linq.
9. Cari elemen berikut dibawah ini :
1: <Row>
2: <Cell ss:StyleID="s62"><Data ss:Type="String">Product Name</Data></Cell>
3: <Cell ss:StyleID="s62"><Data ss:Type="String">Category</Data></Cell>
4: <Cell ss:StyleID="s62"><Data ss:Type="String">Supplier</Data></Cell>
5: <Cell ss:StyleID="s63"><Data ss:Type="String">Stock</Data></Cell>
6: <Cell ss:StyleID="s63"><Data ss:Type="String">Price</Data></Cell>
7: </Row>
8:
9: <%= data %>
tambahkan kode seperti yang dapat dilihat pada line-9...akhirnya nanti variabel data ini akan generate record secara run time yang diisi pada cell-cell yang sudah ditentukan.
10. Tambahkan kode dibawah ini setelah deklarasi XML Literal seperti dibawah ini untuk simpan file excel xml spreadsheet tersebut.
1: xmlExcel.Save(CurDir() & "/prodCat.xml")
2: Process.Start(CurDir() & "/prodCat.xml")
hasilnya dapat dilihat seperti pada gambar dibawah ini :
lalu bagaimana kalau saya ingin membuat sebuah formula? misalkan saya ingin tambahkan kolom yang merupakan hasil perkalian antara "Stock * Price"...cukup tambahkan kolom yang diperlukan, edit formulanya seperti gambar dibawah ini :
lalu simpan file tersebut. Buka dengan Notepad...copy-paste kode xml nya dan replace xml literal yang sudah dibuat sebelumnya di vb code. Perhatikan disitu terdapat kode tambahan berupa cell yang berisi formula perkalian dua kolom tadi. Lakukan manipulasi kode di baris awal seperti dibawah ini :
1: Private Sub btnCreateExcelFile_Click() Handles btnCreateExcelFile.Click
2: Dim db As New ProductDetailsDataContext
3:
4: Dim data = (From record In db.vProductDetails _
5: Select <Row>
6: <Cell><Data ss:Type="String"><%= record.ProductName %></Data></Cell>
7: <Cell><Data ss:Type="String"><%= record.CategoryName %></Data></Cell>
8: <Cell><Data ss:Type="String"><%= record.CompanyName %></Data></Cell>
9: <Cell><Data ss:Type="Number"><%= record.UnitsInStock %></Data></Cell>
10: <Cell><Data ss:Type="Number"><%= record.UnitPrice %></Data></Cell>
11: <Cell ss:Formula="=RC[-2]*RC[-1]"><Data ss:Type="Number"></Data></Cell>
12: </Row>).Take(10)
perhatikan kode pada line-11..kode tersebut merupakan cell hasil edit formula yang dilakukan...just copy-paste from the excel to this variable, then delete the text inside "Data" element...run this application...you can see the result shown by the picture below :
That's it...i've been told you many times that the new XML Literal feature in VB 9.0 is a powerfull technique for creating or modyfing some particular extension file :) What's next?? i'm thinking :) ...maybe Linq to xml...or..???