So my applications display as a nice table the data stored in a database. After filtering and highlighting the displayed data, the users of my applications want to download some selected columns of the table as an excel file. I use very simple solution. Upon clicking a button, the visible contents of the table are posted via ajax to the backend as a two-dimensional array in the json format. When the ajax response is received, the second ordinary http GET request is made to download the excel file generated with Apache POI from the posted data.
My RESTEasy-based backend REST resource class:
@Path("/excel")
@Produces(APPLICATION_JSON_UTF8)
public class ExcelResource {
@Context
HttpServletRequest req;
static String DATA_SESSION_ATTRIBUTE = "DATA_SESSION_ATTRIBUTE";
@POST
@Consumes(MediaType.APPLICATION_JSON)
public void postData(List<List<String>> rows) throws SQLException, IOException, ParseException {
req.getSession().setAttribute(DATA_SESSION_ATTRIBUTE, rows);
}
@GET
@Produces(MediaType.APPLICATION_OCTET_STREAM)
public Object getExcel() throws SQLException, IOException, ParseException {
List<List<String>> rows = (List<List<String>>) req.getSession().getAttribute(DATA_SESSION_ATTRIBUTE);
return Response.ok(new Excel().convert(rows))
.header("content-disposition", "attachment; filename = export.xlsx").build();
}
}
The REST resource uses auxilliary class Excel. For the sake of simplicity I show a short class producing excel files with plain text without any colors:
public class Excel {
public StreamingOutput convert(List<List<String>> rows) throws FileNotFoundException, IOException, SQLException {
return new StreamingOutput() {
public void write(OutputStream out) throws IOException {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Data");
for (int r = 0; r < rows.size(); r++) {
List<String> sourceRow = rows.get(r);
Row row = sheet.createRow(r);
for (int c = 0; c < sourceRow.size(); c++) {
Cell cell = row.createCell(c);
cell.setCellValue(sourceRow.get(c));
}
}
wb.write(out);
}
};
}
}
The Javascript part is as simple. The value of each td cell is contained inside a div element (because it is easier to manipulate div css properties such as dimensions in response to user actions). Here is a method converting the selected columns of the HTML table into a two dimensional array. Jquery html() method can be used instead of text() to retain the user-generated tags (e.g. html bold tag labeling some search string matches).
function getSelectedColumns() {
var $headers = $resultTable.find('thead th').filter('.' + constants.SELECTED_HEADER);
if (!$headers.length) // nothing is selected
$headers = $resultTable.find('thead th');
var columnIndexes = [];
var rows = [];
var row = [];
for (var i = 0; i < $headers.length; i++) {
var $header = $headers.eq(i);
console.log($header.text() + "; " + $header.index());
columnIndexes.push($header.index());
row.push($header.text());
}
rows.push(row);
var $tableRows = $resultTable.find('tbody tr');
for (var i = 0; i < $tableRows.length; i++) {
var row = [];
var $rowTds = $tableRows.eq(i).find('td div');
for (var c = 0; c < columnIndexes.length; c++) {
var $div = $rowTds.eq(columnIndexes[c]);
row.push($div.text());
}
rows.push(row);
}
excel.send(rows);
}
The array is passed to a short require.js module Excel that posts the json, and upon receiving the response triggers the generated file download by changing window.location:
define(['jquery'],
function ($) {
return function ( ) {
this.send = function (params) {
$.ajax({
method: "POST",
url: "api/excel",
data: JSON.stringify(params),
processData: false,
contentType: 'application/json'
})
.done(onLoaded);
};
function onLoaded( ) {
window.location = "api/excel";
}
};
});
