Saturday, July 19, 2014

Converting XML to CSV using XSLT 1.0

This post shows you how can convert a simple XML file to CSV using XSLT.

Consider the following sample XML:

<library>
  <book>
    <author>Dan Simmons</author>
    <title>Hyperion</title>
    <publishDate>1989</publishDate>
  </book>
  <book>
    <author>Douglas Adams</author>
    <title>The Hitchhiker's Guide to the Galaxy</title>
    <publishDate>1979</publishDate>
  </book>
</library>

This is the desired CSV output:

author,title,publishDate
Dan Simmons,Hyperion,1989
Douglas Adams,The Hitchhiker's Guide to the Galaxy,1979

The following XSL Style Sheet (compatible with XSLT 1.0) can be used to transform the XML into CSV. It is quite generic and can easily be configured to handle different xml elements by changing the list of fields defined ar the beginning.

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="text" />

  <xsl:variable name="delimiter" select="','" />

  <!-- define an array containing the fields we are interested in -->
  <xsl:variable name="fieldArray">
    <field>author</field>
    <field>title</field>
    <field>publishDate</field>
  </xsl:variable>
  <xsl:param name="fields" select="document('')/*/xsl:variable[@name='fieldArray']/*" />

  <xsl:template match="/">

    <!-- output the header row -->
    <xsl:for-each select="$fields">
      <xsl:if test="position() != 1">
        <xsl:value-of select="$delimiter"/>
      </xsl:if>
      <xsl:value-of select="." />
    </xsl:for-each>

    <!-- output newline -->
    <xsl:text>&#xa;</xsl:text>

    <xsl:apply-templates select="library/book"/>
  </xsl:template>

  <xsl:template match="book">
    <xsl:variable name="currNode" select="." />

    <!-- output the data row -->
    <!-- loop over the field names and find the value of each one in the xml -->
    <xsl:for-each select="$fields">
      <xsl:if test="position() != 1">
        <xsl:value-of select="$delimiter"/>
      </xsl:if>
      <xsl:value-of select="$currNode/*[name() = current()]" />
    </xsl:for-each>

    <!-- output newline -->
    <xsl:text>&#xa;</xsl:text>
  </xsl:template>
</xsl:stylesheet>

Let's try it out:

$ xsltproc xml2csv.xsl books.xml
author,title,publishDate
Dan Simmons,Hyperion,1989
Douglas Adams,The Hitchhiker's Guide to the Galaxy,1979

Saturday, June 28, 2014

Parsing an Excel File into JavaBeans using jXLS

This post shows how you can use jXLS to parse an Excel file into a list of JavaBeans.

Here is a generic utility method I wrote to do that:

/**
* Parses an excel file into a list of beans.
*
* @param <T> the type of the bean
* @param xlsFile the excel data file to parse
* @param jxlsConfigFile the jxls config file describing how to map rows to beans
* @return the list of beans or an empty list there are none
* @throws Exception if there is a problem parsing the file
*/
public static <T> List<T> parseExcelFileToBeans(final File xlsFile,
                                                final File jxlsConfigFile)
                                                throws Exception {
  final XLSReader xlsReader = ReaderBuilder.buildFromXML(jxlsConfigFile);
  final List<T> result = new ArrayList<>();
  final Map<String, Object> beans = new HashMap<>();
  beans.put("result", result);
  try (InputStream inputStream = new BufferedInputStream(new FileInputStream(xlsFile))) {
    xlsReader.read(inputStream, beans);
  }
  return result;
}

Example:
Consider the following Excel file containing person information:

FirstName LastName Age
Joe Bloggs 25
John Doe 30

Create the following Person bean to bind each Excel row to:

package model;

public class Person {

  private String firstName;
  private String lastName;
  private int age;

  public Person() {
  }
  public String getFirstName() {
    return firstName;
  }
  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }
  public String getLastName() {
    return lastName;
  }
  public void setLastName(String lastName) {
    this.lastName = lastName;
  }
  public int getAge() {
    return age;
  }
  public void setAge(int age) {
    this.age = age;
  }
}

Create a jXLS configuration file which tells jXLS how to process your Excel file and map rows to Person objects:

<workbook>
  <worksheet name="Sheet1">
    <section startRow="0" endRow="0" />
    <loop startRow="1" endRow="1" items="result" var="person" varType="model.Person">
      <section startRow="1" endRow="1">
        <mapping row="1" col="0">person.firstName</mapping>
        <mapping row="1" col="1">person.lastName</mapping>
        <mapping row="1" col="2">person.age</mapping>
      </section>
      <loopbreakcondition>
        <rowcheck offset="0">
          <cellcheck offset="0" />
        </rowcheck>
      </loopbreakcondition>
    </loop>
  </worksheet>
</workbook>

Now you can parse the Excel file into a list of Person objects with this one-liner:

List<Person> persons = Utils.parseExcelFileToBeans(new File("/path/to/personData.xls"),
                                                   new File("/path/to/personConfig.xml"));

Related posts:
Parsing a CSV file into JavaBeans using OpenCSV

Sunday, May 25, 2014

Code Formatting Shortcut in Outlook 2010

I often have to write code snippets in my emails and find it such a hassle having to move my mouse to the font selection drop-down to change my font to Consolas every time. I thought that it would be so much easier if I had a keyboard shortcut to switch the font for me (a bit like stackoverflow). After searching around, I found that this can be achieved in Microsoft Outlook by creating a macro that changes the font of your selected text and then assigning a shortcut for that macro.

Creating the macro:
  1. In Outlook, press Alt+F8 to open the Macros dialog
  2. Enter a name for the macro e.g. SetCodeFont and press the Create button
  3. Paste the following macro code into the Visual Basic Editor that opens:
    Sub SetCodeFont()
      Dim objItem As Object
      Dim objInsp As Outlook.Inspector
    
      Dim objWord As Word.Application
      Dim objDoc As Word.Document
      Dim objSel As Word.Selection
      On Error Resume Next
    
      Set objItem = Application.ActiveInspector.CurrentItem
      If Not objItem Is Nothing Then
        If objItem.Class = olMail Then
          Set objInsp = objItem.GetInspector
          If objInsp.EditorType = olEditorWord Then
            Set objDoc = objInsp.WordEditor
            Set objWord = objDoc.Application
            Set objSel = objWord.Selection
            objSel.Font.Name = "Consolas"
          End If
        End If
      End If
    
      Set objItem = Nothing
      Set objWord = Nothing
      Set objSel = Nothing
      Set objInsp = Nothing
    End Sub
    
  4. On the menu bar of the Visual Basic Editor, click on Tools > References... and tick Microsoft Word 14.0 Object Library
  5. Save (Ctrl+S) and close the Visual Basic Editor
Assigning a keyboard shortcut for the macro:
  1. Open a new mail message
  2. Click on the small drop-down arrow on the Quick Access Toolbar (usually located at the very top of the message window) and select More Commands...
  3. In the Outlook Options dialog that opens, click on the Choose commands from: drop-down list and select Macros
  4. Pick Project1.SetCodeFont and press Add >> to add it to the toolbar
  5. Press OK and you should now see the SetCodeFont macro button appear on the Quick Access Toolbar
Running the macro:

You can run the macro by using Alt+NUM, where NUM is the position of the macro button on the toolbar. For example, if the macro button is the first button on the toolbar, use Alt+1 to run it. Try it out by typing some text in your email message, selecting it and pressing Alt+1 to change the font to Consolas. You can use Ctrl+Space to switch back to the default font.

Note: You may get a security popup when you run the macro, asking you to Allow or Deny access. You can change your security settings by going into File > Options > Trust Center > Trust Center Settings... > Macro Settings, but this will require Administrator privileges (I haven't tried this).

Reference:

Use Word Macro to Apply Formatting to Outlook Email by Diane Poremsky [www.slipstick.com]

Saturday, May 24, 2014

glibc detected - double free or corruption (fasttop)

I've been bashing my head against the wall trying to solve this error for days now!

*** glibc detected *** double free or corruption (fasttop): 0x0000002aa63dca00 ***

I've finally found the answer. I set the MALLOC_CHECK_ environment variable to 0 in my startup script to resolve the issue.

MALLOC_CHECK_=0

According to the GNU C Library Reference Manual, MALLOC_CHECK_ is used to check for and guard against bugs in the use of malloc, realloc and free. If MALLOC_CHECK_ is set to 0, any detected heap corruption is silently ignored; if set to 1, a diagnostic is printed on stderr; if set to 2, abort is called immediately.

Saturday, May 10, 2014

stackoverflow - 80k rep

Five months after crossing the 70k milestone, I have now reached a reputation of 80k on stackoverflow!

The following table shows some stats about my journey so far:

0-10k 10-20k 20-30k 30-40k 40-50k 50-60k 60-70k 70-80k Total
Date achieved 01/2011 05/2011 01/2012 09/2012 02/2013 07/2013 12/2013 05/2014
Questions answered 546 376 253 139 192 145 66 58 1775
Questions asked 46 1 6 0 1 0 0 0 54
Tags covered 609 202 83 10 42 14 11 14 985
Badges
(gold, silver, bronze)
35
(2, 10, 23)
14
(0, 4, 10)
33
(2, 8, 23)
59
(3, 20, 36)
49
(0, 19, 30)
65
(2, 26, 37)
60
(5, 22, 33)
50
(2, 24, 24)
365
(16, 133, 216)

I have been very busy over the last few months and haven't had much time to go on stackoverflow. As you can see, I only answered 58 questions over the last 5 months, but my previous answers have helped keep my reputation ticking along nicely. For me, stackoverflow has not simply been a quest for reputation, but more about learning new technologies and picking up advice from other people on the site. I like to take on challenging questions, rather than the easy ones, because it pushes me to do research into areas I have never looked at before, and I learn so much during the process.

90k, here I come!