I found an example for generating excel file using apache poi. I have modified it to work with List<Map<String, Object>>
but now i have problem with formatting the document. Hope some will know how to make it.
For now the document which is generated is good but it needs some formatting.
public void generateReport(List<Map<String, Object>> metas, HttpServletResponse response, HttpServletRequest request)
throws IOException, InvalidFormatException {
String template = request.getSession().getServletContext().getRealPath("/WEB-INF/excel/template.xlsx");
Workbook book = WorkbookFactory.create(new FileInputStream(template));
Sheet sheet = book.getSheetAt(0);
CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
int interator = 0;
int columnsNames = 0;
int columnsLen = metas.get(0).size();
Set<Object> keys = new LinkedHashSet<Object>();
Map<String, Object[]> data = new TreeMap<String, Object[]>();
String[] args = new String[columnsLen];
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment; filename=filename_.xlsx");
for (Map.Entry<String, Object> entry : metas.get(0).entrySet()) {
keys.add(entry.getKey());
}
for (Map<String, Object> meta : metas) {
if (columnsNames == 0) {
String[] colNames = keys.toArray(new String[metas.size()]);
data.put("0", colNames);
}
String[] keyLine = keys.toArray(new String[keys.size()]);
for (int i = 0; i < metas.size(); i++) {
for (String s : keyLine) {
args[interator] = metas.get(i).get(s).toString();
interator++;
}
StringBuilder sb = new StringBuilder();
sb.append("");
sb.append(i + 1);
String key = sb.toString();
data.put(key, args);
interator = 0;
args = new String[columnsLen];
}
Set<String> nKey = data.keySet();
int rownum = 12;
for (String kkey : nKey) {
Row row = sheet.createRow(rownum++);
Object[] objArr = data.get(kkey);
int cellnum = 0;
for (Object obj : objArr) {
Cell cell = row.createCell(cellnum++);
if (obj instanceof String)
cell.setCellValue((String) obj);
else if (obj instanceof Integer)
cell.setCellValue((Integer) obj);
}
}
}
HSSFRow row1 = (HSSFRow) book.getSheetAt(0).getRow(0);
for (int i = 0; i < row1.getLastCellNum(); i++) {
book.getSheetAt(0).autoSizeColumn(i);
}
Font font = book.getFontAt((short) 0);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
try {
book.write(response.getOutputStream());
book = new XSSFWorkbook();
} catch (IndexOutOfBoundsException e) {
e.getMessage();
} catch (FileNotFoundException e) {
e.getMessage();
}
}