If you're interested in functional programming, you might also want to checkout my second blog which i'm actively working on!!

Friday, January 18, 2013

Generating HTML and Excel with Xquery

I just wanted to share a small sample how you can generate a table in both HTML and Excel with one XQuery. Just in case the sample gets lost I will still share the code in this article as well.
A few remarks. You can save the result to a file test.html and test.xls. For the excel file you will need to remove the xml declaration (first line).
let $orders :=
  <order id="1231">
    <product>Samsung Galaxy S3</product>
    <price valuta="EURO">467</price>
  <order id="1232">
    <product>iPhone 5</product>
    <price valuta="EURO">689</price>
  <order id="1233">
    <product>LG E610 Optimus L5</product>
    <price valuta="EURO">140</price>

<html xmlns:x="urn:schemas-microsoft-com:office:excel">
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> </meta>
    <meta http-equiv="Content-Type" content="application/vnd.ms-excel; charset=utf-8"> </meta>
    <style type="text/css">
      table {{
        border-collapse: collapse;
      .header {{
        background-color: #E6F1F3;
        text-align: left;
      td, th {{
        border: 1px dotted #B3D4DB;
        padding: 2px;
      .number {{
        text-align: right;
      .subtotal {{
        background-color: #E3E4E5; 
        text-align: right;
        font-weight: bold;
      .total {{
        background-color: #ADAEAF;
        text-align: right;
        font-weight: bold;        
        <col style="width: 100px;"/>
        <col style="width: 200px;"/>
        <col style="width: 100px;"/>
        <col style="width: 100px;"/>
          <th class="header">Order No</th>
          <th class="header">Product</th>
          <th class="header">Price</th>
          <th class="header">Quantity</th>
        for $order in $orders/order
        let $price := data($order/price)
        let $quantity := data($order/quantity)
        let $subtotal := $price * $quantity
          <td class="number">{$price}</td>
          <td class="number">{$quantity}</td>
          <td class="subtotal"/>
          <td class="subtotal"/>
          <td class="subtotal">Subtotal</td>
          <td class="subtotal">{$subtotal}</td>
          <td class="total"/>
          <td class="total"/>
          <td class="total">Total</td>
          <td class="total">{sum(for $order in $orders/order return data($order/price) * data($order/quantity))}</td>

You will get a warning from Excel but I already looked into this and there is no workaround available at this moment. I also noticed that when opening such a file from Internet Explorer it seems to hang the first time. A workaround it to Save the file the first time and open it. From that moment on Internet Explorer will be able to open the files correctly.

No comments:

Post a Comment