1

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();
            }
        }
Cœur
  • 37,241
  • 25
  • 195
  • 267
cool
  • 33
  • 8
  • what kind of formatting? – sidgate Aug 16 '16 at 12:27
  • I am trying to make all names of the columns bold. I also need all columns to have width to show all the content of every line. – cool Aug 16 '16 at 12:36
  • Possible duplicate of [How to apply bold text style for an entire row using Apache POI?](http://stackoverflow.com/questions/12286662/how-to-apply-bold-text-style-for-an-entire-row-using-apache-poi) – sidgate Aug 16 '16 at 12:37

0 Answers0