Tuesday, September 30, 2014

Ext JS CSV Data Reader

In a previous post, I showed how you can convert CSV to JSON in JavaScript. In this post, I will show how you can display CSV data in an Ext JS grid panel using a custom Reader.

Currently, there are three kinds of Readers available in Ext JS 4.2.2:

A CSV reader isn't available but is quite easy to create, simply by extending the JSON reader. The code for my CSV Reader is shown below. It first converts the CSV response from the server into JSON format and then invokes the parent JSON reader.

/**
 * The CSV Reader is used by a Proxy to read a server response
 * that is sent back in CSV format.
 */
Ext.define('CsvReader', {
    extend: 'Ext.data.reader.Json',
    alias : 'reader.csv',

    // converts csv into json
    toJson: function(csvData){
      var lines = csvData.split("\n");
      var colNames = lines[0].split(",");
      var records = [];
      for(var i = 1; i < lines.length; i++) {
        if (lines[i] == "") continue;
        var record = {};
        var bits = lines[i].split(",");
        for (var j = 0; j < bits.length; j++) {
          record[colNames[j]] = bits[j];
        }
        records.push(record);
      }
      return records;
    },

    // override
    getResponseData: function(response) {
        try {
            return this.readRecords(response.responseText);
        } catch (ex) {
            error = new Ext.data.ResultSet({
                total  : 0,
                count  : 0,
                records: [],
                success: false,
                message: ex.message
            });
            this.fireEvent('exception', this, response, error);
            console.log(error);
            return error;
        }
    },

    // override
    readRecords: function(strData) {
        var result = this.toJson(strData);
        return this.callParent([result]);
    }
});

Now let's use it to display an example CSV file containing book data in a grid.

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

Here is some sample code to read the CSV file into a grid using the CSV reader defined above:

Ext.define('Book', {
    extend: 'Ext.data.Model',
    fields: [
        {name: 'author', type: 'string'},
        {name: 'title', type: 'string'},
        {name: 'publishDate', type: 'string'}
    ]
});

var bookStore = Ext.create('Ext.data.Store', {
    model: 'Book',
    autoLoad: true,
    proxy: {
        type: 'ajax',
        reader: 'csv',
        url: 'data.csv'
    }
});

Ext.application({
    name: 'MyApp',
    launch: function() {
        Ext.create('Ext.Viewport', {
            layout: 'fit',
            items: [{
                xtype:'grid',
                title: 'Books',
                store: bookStore,
                columns: [
                   { text: 'Author', dataIndex: 'author' },
                   { text: 'Title', dataIndex: 'title', flex:1 },
                   { text: 'Publish Date', dataIndex: 'publishDate' }
                ],
                height: 200,
                width: 400
            }],
            renderTo: Ext.getBody()
        });
    }
});

Related posts:
Converting CSV to JSON in JavaScript

Sunday, August 31, 2014

My Git Aliases

A git alias gives you the ability to run a long or hard-to-remember git command using a simple name. They are configured in your .gitconfig file.

One of my favourite aliases is git ls which lists all your commits in a nice format. In addition, git ll shows you what files were committed in each commit.

My git aliases are shown below. (For the latest version of my .gitconfig, visit my GitHub dotfiles repository):

[alias]
    st = status
    co = checkout
    br = branch
    df = diff
    ci = commit
    ca = commit -a --amend -C HEAD
    desc = describe
    rb = rebase -i master --autosquash
    cp = cherry-pick

    who = shortlog -s --
    ls = log --graph --pretty=format:'%Cred%h%Creset -%C(yellow)%d%Creset %s %Cgreen(%cr) %C(bold blue)[%an]%Creset' --abbrev-commit --date=relative
    ll = log --pretty=format:'%Cred%h%Creset -%C(yellow)%d%Creset %s %Cgreen(%cr) %C(bold blue)[%an]%Creset' --decorate --numstat
    ld = log --pretty=format:'%C(red)%h %Cgreen%ad%C(yellow)%d %Creset%s%C(bold blue) [%cn]%Creset' --decorate --date=short

    # list aliases
    la = "!git config -l | grep alias | cut -c 7-"

If you have any useful aliases, please share them in the comments section below.

You might also like:
My Bash Profile
My Bash Aliases

Saturday, August 30, 2014

Converting CSV to JSON in JavaScript

This post shows how you can convert a simple CSV file to JSON in JavaScript.

Consider the following sample CSV:

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

The desired JSON output is:

[{"author":"Dan Simmons","title":"Hyperion","publishDate":"1989"},
{"author":"Douglas Adams","title":"The Hitchhiker's Guide to the Galaxy","publishDate":"1979"}]

The following JavaScript function transforms CSV into JSON. (Note that this implementation is quite naive and will not handle quoted fields containing commas!)

function toJson(csvData) {
  var lines = csvData.split("\n");
  var colNames = lines[0].split(",");
  var records=[];
  for(var i = 1; i < lines.length; i++) {
    var record = {};
    var bits = lines[i].split(",");
    for (var j = 0 ; j < bits.length ; j++) {
      record[colNames[j]] = bits[j];
    }
    records.push(record);
  }
  return records;
}

A simple test:

csv="author,title,publishDate\nDan Simmons,Hyperion,1989\nDouglas Adams,The Hitchhiker's Guide to the Galaxy,1979";
json = toJson(csv);
console.log(JSON.stringify(json));

To read a CSV file in JavaScript and convert it to JSON:

var rawFile = new XMLHttpRequest();
rawFile.open("GET", "books.csv", true);
rawFile.onreadystatechange = function () {
  if (rawFile.readyState === 4) {
    if (rawFile.status === 200 || rawFile.status == 0) {
      var allText = rawFile.responseText;
      var result = toJson(allText);
      console.log(JSON.stringify(result));
    }
  }
}
rawFile.send(null);

You might also like:
Converting XML to CSV using XSLT 1.0

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