excel

Ruby 2.2.2, UTF-8 CSV FILES And Excel

Okay, I’ve taken a bit of a detour with work and find myself supporting a Ruby on Rails application. All brand new to me.

Recently I had to get a CSV file, generated in the Rails app to open in Excel with accents correctly displayed.  The post at Exporting data to CSV and Excel in your Rails apps from plataformatec was very helpful, but for an older version of Ruby.  Here’s what I did to get it working w/Ruby 2.2.2:

The important point here is that if the file is in UTF-16LE with a BOM, Excel will correctly interpret the file. I found various information about this around the web.  The platformatec posting summarizes the three points that got this working:

  1. Use tabulations, not commas.
  2. Fields must NOT contain newlines.
  3. Use UTF-16 Little Endian to send the file to the user. And include a Little Endian BOM manually.

The open mode indicates the data is UTF-8 encoded but should be written UTF-16LE encoded.

w+:UTF-16LE:UTF-8

Helpful Links:

Version Info:

  • Ruby 2.2.2
Advertisements

ExtJs 4 Export to Excel (server-side)

I currently have a few grids (and just forms) in my app with a requirement to export to them to Excel. I am doing the export server side with Apache POI.

The html I have for my app is minimal b/c this is a single page app, using the Ext JS viewport, but I did add a hidden form and iframe to handle the export.


<body>
	<iframe id="exportframe" name="exportframe" class="x-hidden"></iframe>
	<form id="exportform" method="post" target="exportframe">
		<input type="hidden" id="excelconfig" name="config" value="" />
	</form>
</body>

Then in my javascript, I did the following:

// Following code lifted from downLoadFile method of Saki filetree - http://filetree.extjs.eu/
exportToExcel : function (params, url) {
	var id, frame, form, hidden, callback;

	frame = Ext.fly('exportframe').dom;
	frame.src = Ext.SSL_SECURE_URL;

	form = Ext.fly('exportform').dom;
	form.action = url;
	hidden = document.getElementById('excelconfig');
	hidden.value = Ext.encode(params);

	form.submit();
 }
 

These links were helpful for me:

The code from those links is dynamically adding and removing the iframe and form, but for some reason that I didn’t spend time on, the load events were not being handled in IE and I was ending up with a lot of extra iframes and forms in my dom. So, I just put the one directly in my jsp instead.

The exportToExcel method is in part of my overall Ext.app.Application, and I call it from various other locations in the javascript. params is a JavaScript object that I then deserialize server-side using Jackson.

My Spring 3.0 controller handles the request (in the example SearchBy is my class that I know the config param will deserialize into:

@Controller
@RequestMapping("/users")
public class UserSearchController {
	private static final ObjectMapper localMapper = new ObjectMapper();

	@RequestMapping(value="/xls")
	public void getXLS(HttpServletResponse response, @RequestParam("config") String config) {
			SearchBy searchBy = localMapper.readValue(config, SearchBy.class);

			// get the data from the db and do what is needed by POI to 
			// create the excel workbook...
	}
}

I found this tutorial very helpful:

I also looked at the Spring AbstractExcelView but I was having issues with Spring trying to resolve to a jsp view and although I would have liked to figure it out, I just moved on w/the solution above.

Update:
IE is particular about headers when exporting to Excel using https.
Helpful link: http://stackoverflow.com/questions/4672073/export-to-excel-doesnt-work-on-ie-under-ssl-https
Which suggests these http headers (which worked for me):

    Cache-Control: cache, must-revalidate
    Pragma: public
    Content-Type: application/vnd.ms-excel  // MIME type
    Content-Disposition: attachment; filename="excelDownload.xls"
    Content-Transfer-Encoding: binary
    Content-Length: 1024  // size as appropriate