THE BLOG

News, tips and tricks from 2Gears

Ext JS Excel Import Export made easy

Posted · 4 Comments
Gearbox Import Export Gear image

Today, we are proud to release the first Gearbox Gear: the Import Export Gear. Importing and exporting Excel documents from your ExtJS apps has never been easier.

TL;DR

So you’re in a hurry right? Then cut the chase and see for yourself how easy it is.

Gears

Gears are hyper productivity tools  aimed at dramatically speeding up software development. Some Gears are standalone pieces of code that solve a particular problem. Other Gears are development tools to aid the developer to work more efficiently with their code, the framework or related tools. The Import-Export Gear helps Ext JS developers import and export Excel data files, such as CSV and XLS(X) by just dropping in a package and adding a handful of lines of code.

Dreadful data

Data driven applications like CRM systems are only as good as the data they operate on. Typically there are 2 hard-to-solve problems;  1. getting large amounts of data into the system and 2. exporting data from the system to be able to share it with external systems or people. Many companies use spreadsheets to enter, store and distribute data. This means that import and export is not just a one-of task but an ongoing requirement of the application. Manually copy-pasting between the application and Excel is enough to drive any person crazy. Yet, in most companies that is exactly what is happening. State of the art systems are designed but when data has to imported or exported, we seem to jump back to 1998 and do it manually. So why aren’t there any good tools around to help application developers with this?

Working with files is hard

Since web applications run inside the client’s browser, javascript (and thus Ext JS) can never access files on the client’s machine, because of security reasons. The first hurdle is getting the file data into our application. Usually this is solved by uploading the file to a server, inserting the data into the server’s database or parsing it in memory and then returning the data to the client. When the client application is finished working on the data it is sent back to the server. In an Ext JS application, you’ll need to create a store, a grid, and a model that exactly match the structure of your data file. You’ll also need a custom way of uploading the file to the server, and a proxy to read and write the application data. This is already a lot of work, but it gets worse:

  1. You’ll need to create a new server side importer for every structure of data you want to import.
  2. Your server is doing the heavy lifting. The more users upload files simultaneously, the more processing power you will need or the requests will just start queuing up.
  3. Your server is polluting its database with records with every file import before the client is actually finished working on it.
  4. Any typos or other discrepancies between the file data headers and the fields of your model and your import will fail.
  5. Reading this paragraph probably took you longer than implementing an entire solution using our Gearbox Import Export Gear.

The last is a bold claim, so please allow us to show you how easy working with files can be.

Working with files is easy with Gearbox

With the Gearbox Import / Export Gear, you can create an Ext JS application that can import excel files in under 2 minutes. Oh, and while you’re at it, you’ll add CSV support and export functionality as well, still well under 2 minutes, and in less than 50 lines of code. That includes pretty formatting and whitespace, because what good is code if it isn’t readable? Imagine creating a grid that will handle file imports for you as simple as this:

Ext.define('ImportApp.view.Main', {
	extend: 'Ext.grid.Panel',
	requires: [
		'Gearbox.data.file.Store'
	],

	xtype: 'app-main',

	title: 'Simpsons',

	store: {
		type: 'file',
		storeId: 'Example',
		fields: ['name', 'email', 'phone'],

		proxy: {
			type: 'file'
		}
	},

	columns: [{
		text: 'Name',
		dataIndex: 'name'
	}, {
		text: 'Email',
		dataIndex: 'email',
		flex: 1
	}, {
		text: 'Phone',
		dataIndex: 'phone'
	}],

	tools: [{
		callback: function(grid) {
			var store = grid.getStore();
			store.exportFile(grid.columns, grid.title);
		}
	}],

	afterRender: function() {
		this.getStore().bindDrop(this);
		this.callParent(arguments);
	}
});

Seasoned ExtJS developers will immediately understand and recognize the above code and structure, but please indulge us to elaborate a bit on what is going on. The most important thing to notice is that all file processing takes place client side. This means that concurrent user imports will will not slow each other down. First, a simple grid and store are created with information about what data to expect (31 lines). Then, in the afterRender function, the grid’s store is instructed to listen for files dropped on the grid (4 lines). This can only be done in the afterRender method since we need the actual DOM elements being created already.

afterRender: function() {
	this.getStore().bindDrop(this);
	this.callParent(arguments);
}

Lastly, a simple button is added that will export the data as a file (6 lines). The button just calls the exportFile method on the store which generates an Excel or CSV file. The Export is also created client-side, not a single line of server code needed.

tools: [{
   callback: function(grid) {
		var store = grid.getStore();
		store.exportFile(grid.columns, grid.title);
	}
}]

The observant reader will have noticed that the above example does not contain any code for mapping the Excel columns to model fields. That’s because (unless you choose to do it manually) that too is handled for you. As long as your column headers and field names are close enough to guess, the Import-Export Gear will automatically map your data to your model. This is more than enough to tackle typos and differences in casing.

Still not convinced?

If the above did not convice you, why not try it yourself? The following example doesn’t even require creating a model, it just reads the fields from your files and does everything for you. Just drop a file onto the grid and see it perform it’s magic.

Below are some example files for you to play with. just download the files and drag-drop them onto the grid above. Easy peasy!

Updates Ahead!

While CSV and XLS(X) import and export provide a very solid foundation for the Gear, there’s even more up ahead. In the next weeks we will provide an Import Wizard for easy importing of documents when the mapping cannot be guessed or fine-grained control is needed. On top of that, we’re working on a PDF export the likes of which has never been seen before.

Gearbox Philosophy

Again, the above numbers of lines include proper line breaks and pretty JSON. Because we believe code should be well written, reusable whenever possible and any member of your team should be able to understand it at first sight. To that aim, we create Gears. To help developers create awesome apps in no-time, while improving the quality, readability and maintainability of their code. This Gear is the first of many, so if we’ve caught your attention, be sure to check back regularly for more awesome software development goodies.


4 Responses to "Ext JS Excel Import Export made easy"
  1. Fortunate says:

    I have managed to purchase gearbox-import-export, and import it into my ExtJs 5.1 project. I am using Sencha Architect 3.2 to build my project. My challenge is that the parser is failing to be selected as a property of ‘window’. window.CSV is undefined, window.XLSX is undefined. How can I get past this challenge?

    • Rob Boerman says:

      Can you tell me which version of the import/export package you have? The package contains a number of requirements in the form of NPM packages. These should be available in your package and be loaded by the Sencha Loader. If you send me the package version I can check if I can import it in an architect project.

  2. Ilan says:

    Where can I download the code Gearbox.data.file.Store?

Leave a Reply

Your email address will not be published. Required fields are marked *

2 × 3 =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>