Example usage for org.apache.poi.ss.usermodel Workbook write

List of usage examples for org.apache.poi.ss.usermodel Workbook write

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. write

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook write.

Prototype

void write(OutputStream stream) throws IOException;

Source Link

Document

Write out this workbook to an Outputstream.

Usage

From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java

License:Open Source License

@SuppressWarnings("rawtypes")
public static void exportToExcel(String[][] table, ExportMode exportMode) throws IOException {
    Class[] types = getTypes(table);
    Workbook wb = new XSSFWorkbook();
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;/*w ww. j a va 2  s  .c  o  m*/
    DataFormat df = wb.createDataFormat();

    Font font = wb.createFont();
    font.setFontName("Serif");
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 15);
    style = wb.createCellStyle();
    style.setFont(font);
    styles.put("title", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 10);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(font);
    style.setWrapText(true);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("th", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font);
    style.setWrapText(true);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font);
    style.setWrapText(true);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-border", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-double", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-right", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-bold", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setDataFormat(df.getFormat("d.mm.yyyy h:MM"));
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-date", style);

    Sheet sheet = wb.createSheet();
    sheet.setFitToPage(true);

    Cell cell;

    int maxRows = 0;
    for (int r = 0; r < table.length; r++) {
        Row row = sheet.createRow(r);
        if (r == 0) {
            row.setRowStyle(styles.get("th"));
        }

        int rows = 1;
        for (int c = 0; c < table[r].length; c++) {
            cell = row.createCell(c);
            String s = table[r][c];
            if (s == null)
                continue;
            rows = Math.max(rows, s.split("\n").length);
            try {
                if (exportMode == ExportMode.HEADERS_TOP && r == 0) {
                    cell.setCellStyle(styles.get("th"));
                    cell.setCellValue(s);

                } else if (exportMode == ExportMode.HEADERS_TOPLEFT && (r == 0 || c == 0)) {
                    if (r == 0 && c == 0) {
                        cell.setCellStyle(styles.get("td"));
                    } else {
                        cell.setCellStyle(styles.get("th"));
                    }
                    cell.setCellValue(s);
                } else if (types[c] == Double.class) {
                    cell.setCellStyle(styles.get("td-double"));
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(Double.parseDouble(s));
                } else if (types[c] == String.class) {
                    cell.setCellStyle(
                            styles.get(exportMode == ExportMode.HEADERS_TOPLEFT ? "td-border" : "td"));
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(s);
                } else {
                    cell.setCellStyle(styles.get("td-right"));
                    cell.setCellValue(s);
                }
            } catch (Exception e) {
                cell.setCellStyle(styles.get("td"));
                cell.setCellValue(s);
            }
        }
        maxRows = Math.max(maxRows, rows);
        row.setHeightInPoints(rows * 16f);

    }

    // Add footer notes
    if (footerData.size() > 0) {
        Row row = sheet.createRow(table.length);
        row.setHeightInPoints((footerData.size() * sheet.getDefaultRowHeightInPoints()));
        cell = row.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), //first row (0-based)
                row.getRowNum(), //last row  (0-based)
                0, //first column (0-based)
                table[0].length - 1 //last column  (0-based)
        ));
        //for ( String data : footerData ) {
        style = wb.createCellStyle();
        style.setWrapText(true);
        cell.setCellStyle(style);
        cell.setCellValue(MiscUtils.flatten(footerData, "\n"));
        //}
    }
    footerData.clear();

    autoSizeColumns(sheet);
    if (table.length > 0) {
        for (int c = 0; c < table[0].length; c++) {
            if (sheet.getColumnWidth(c) > 10000)
                sheet.setColumnWidth(c, 3000);
        }
    }

    if (exportMode == ExportMode.HEADERS_TOPLEFT) {
        for (int r = 1; r < table.length; r++) {
            sheet.getRow(r).setHeightInPoints(maxRows * 16f);
        }
    }

    File reportFile = IOUtils.createTempFile("export_", ".xlsx");
    FileOutputStream out = new FileOutputStream(reportFile);
    wb.write(out);
    wb.close();
    out.close();
    Desktop.getDesktop().open(reportFile);
}

From source file:com.admin.poi.ExcelUtils.java

License:Apache License

/**
 *  excel// w ww . j a  v  a2 s  . c  o m
 *
 * @param excelSheet   sheet ?
 * @param workbook     
 * @param outputStream ?
 */
private static void export(ExcelSheet excelSheet, Workbook workbook, OutputStream outputStream)
        throws IOException, InvocationTargetException, IllegalAccessException {

    Sheet sheet;
    sheet = workbook.createSheet();
    workbook.setSheetOrder(sheet.getSheetName(), excelSheet.getSheetIndex());

    // write head
    writeHead(excelSheet, sheet);
    // sheet
    int writeRowIndex = excelSheet.getStartRowIndex();
    if (excelSheet.getDataList() != null && !excelSheet.getDataList().isEmpty()) {
        for (Object rowData : excelSheet.getDataList()) {
            // proc row
            Row row = Optional.ofNullable(sheet.getRow(writeRowIndex)).orElse(sheet.createRow(writeRowIndex));

            writeRow(excelSheet, row, rowData);
            writeRowIndex++;
        }
    }
    workbook.write(outputStream);
}

From source file:com.adobe.acs.commons.mcp.impl.GenericReportExcelServlet.java

License:Apache License

@Override
protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response)
        throws ServletException, IOException {
    GenericReport report = request.getResource().adaptTo(GenericReport.class);
    if (report != null) {
        String title = report.getName();
        String fileName = JcrUtil.createValidName(title) + ".xlsx";

        Workbook workbook = createSpreadsheet(report);
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Expires", "0");
        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
        response.setHeader("Pragma", "public");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
        try (ServletOutputStream out = response.getOutputStream()) {
            workbook.write(out);
            out.flush();//from  w  ww. j  a v  a  2s . c  om
        } catch (Exception ex) {
            LOG.error("Error generating excel export for " + request.getResource().getPath(), ex);
            throw ex;
        }
    } else {
        LOG.error("Unable to process report stored at " + request.getResource().getPath());
        throw new ServletException("Unable to process report stored at " + request.getResource().getPath());
    }
}

From source file:com.adobe.acs.commons.mcp.impl.ProcessErrorReportExcelServlet.java

License:Apache License

@Override
protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response)
        throws ServletException, IOException {
    ManagedProcess report = request.getResource().adaptTo(ManagedProcess.class);
    if (report != null) {
        String title = report.getName();
        String fileName = JcrUtil.createValidName(title) + ".xlsx";

        Workbook workbook = createSpreadsheet(report);
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Expires", "0");
        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
        response.setHeader("Pragma", "public");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
        try (ServletOutputStream out = response.getOutputStream()) {
            workbook.write(out);
            out.flush();/* w  ww  .  j  a v a2  s  .c  o m*/
        } catch (Exception ex) {
            LOG.error("Error generating excel export for " + request.getResource().getPath(), ex);
            throw ex;
        }
    } else {
        LOG.error("Unable to process report stored at " + request.getResource().getPath());
        throw new ServletException("Unable to process report stored at " + request.getResource().getPath());
    }
}

From source file:com.alibaba.ims.platform.util.ExcelUtil.java

License:Open Source License

private static void write(List<List<String>> rows, File file) throws IOException {
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("sheet1");

    for (int i = 0; i < rows.size(); i++) {
        Row row = sheet.createRow(i);/*from  ww  w.  j  a  v  a  2 s  . co  m*/
        for (int j = 0; j < rows.get(i).size(); j++) {
            Cell cell = row.createCell(j);
            cell.setCellValue(rows.get(i).get(j));
        }
    }

    FileOutputStream fos = null;
    try {
        fos = new FileOutputStream(file);
        workbook.write(fos);
    } finally {
        if (fos != null) {
            IOUtils.closeQuietly(fos);
        }
    }
}

From source file:com.alibaba.stonelab.webxsample.sample.web.module.screen.Download.java

License:Open Source License

public void execute() {
    response.setContentType("application/x-download");
    response.setHeader("Content-Disposition", "attachment; filename=" + FILE_NAME);

    try {//from w ww .  jav  a 2s  .  c om
        OutputStream out = response.getOutputStream();

        Workbook wb = getWorkbook();
        wb.write(out);

        out.flush();
        out.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:com.AllenBarr.CallSheetGenerator.Generator.java

License:Open Source License

public int generateSheet(File file, Contributor contrib) {
    //create workbook file
    final String fileName = file.toString();
    final Workbook wb;
    if (fileName.endsWith(".xlsx")) {
        wb = new XSSFWorkbook();
    } else if (fileName.endsWith(".xls")) {
        wb = new HSSFWorkbook();
    } else {//from  ww w  . j av  a 2s. c om
        return 1;
    }
    //create sheet
    final Sheet sheet = wb.createSheet("Call Sheet");
    final Header header = sheet.getHeader();
    header.setCenter("Anderson for Iowa Call Sheet");
    //add empty cells
    final Row[] row = new Row[22 + contrib.getDonationsLength()];
    final Cell[][] cell = new Cell[6][22 + contrib.getDonationsLength()];
    for (int i = 0; i < (22 + contrib.getDonationsLength()); i++) {
        row[i] = sheet.createRow((short) i);
        for (int j = 0; j < 6; j++) {
            cell[j][i] = row[i].createCell(j);
        }
    }
    //populate cells with data
    //column 1
    cell[0][0].setCellValue(contrib.getName());
    cell[0][3].setCellValue("Sex:");
    cell[0][4].setCellValue("Party:");
    cell[0][5].setCellValue("Phone #:");
    cell[0][6].setCellValue("Home #:");
    cell[0][7].setCellValue("Cell #:");
    cell[0][8].setCellValue("Work #:");
    cell[0][10].setCellValue("Email:");
    cell[0][12].setCellValue("Employer:");
    cell[0][13].setCellValue("Occupation:");
    cell[0][15].setCellValue("Past Contact:");
    cell[0][17].setCellValue("Notes:");
    cell[0][21].setCellValue("Contribution History:");
    //column 2
    cell[1][3].setCellValue(contrib.getSex());
    cell[1][4].setCellValue(contrib.getParty());
    cell[1][5].setCellValue(contrib.getPhone());
    cell[1][6].setCellValue(contrib.getHomePhone());
    cell[1][7].setCellValue(contrib.getCellPhone());
    cell[1][8].setCellValue(contrib.getWorkPhone());
    cell[1][9].setCellValue("x" + contrib.getWorkExtension());
    cell[1][10].setCellValue(contrib.getEmail());
    cell[1][12].setCellValue(contrib.getEmployer());
    cell[1][13].setCellValue(contrib.getOccupation());
    cell[1][17].setCellValue(contrib.getNotes());
    //column 4
    cell[3][3].setCellValue("Salutation:");
    cell[3][4].setCellValue("Age:");
    cell[3][5].setCellValue("Spouse:");
    cell[3][7].setCellValue("Address:");
    cell[3][10].setCellValue("TARGET:");
    //column 5
    cell[4][0].setCellValue("VANID:");
    cell[4][3].setCellValue(contrib.getSalutation());
    cell[4][4].setCellValue(contrib.getAge());
    cell[4][5].setCellValue(contrib.getSpouse());
    cell[4][7].setCellValue(contrib.getStreetAddress());
    cell[4][8].setCellValue(contrib.getCity() + ", " + contrib.getState() + " " + contrib.getZip());
    //column 6
    cell[5][0].setCellValue(contrib.getVANID());
    //contribution cells
    for (int i = 0; i < contrib.getDonationsLength(); i++) {
        cell[0][i + 22].setCellValue(contrib.getDonation(i).getDonationDate());
        cell[1][i + 22].setCellValue(contrib.getDonation(i).getRecipient());
        cell[5][i + 22].setCellValue(contrib.getDonation(i).getAmount());
    }

    //format cells
    //Name cell
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
    final CellStyle leftBoldUnderline14Style = wb.createCellStyle();
    final Font boldUnderline14Font = wb.createFont();
    boldUnderline14Font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldUnderline14Font.setUnderline(Font.U_SINGLE);
    boldUnderline14Font.setFontHeightInPoints((short) 14);
    boldUnderline14Font.setFontName("Garamond");
    leftBoldUnderline14Style.setFont(boldUnderline14Font);
    leftBoldUnderline14Style.setAlignment(CellStyle.ALIGN_LEFT);
    cell[0][0].setCellStyle(leftBoldUnderline14Style);
    //field name cells
    final CellStyle rightBold10Style = wb.createCellStyle();
    final Font bold10Font = wb.createFont();
    bold10Font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    bold10Font.setFontHeightInPoints((short) 10);
    bold10Font.setFontName("Garamond");
    rightBold10Style.setFont(bold10Font);
    rightBold10Style.setAlignment(CellStyle.ALIGN_RIGHT);
    for (int i = 3; i < 22; i++) {
        cell[0][i].setCellStyle(rightBold10Style);
    }
    sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 1));
    for (int i = 3; i < 11; i++) {
        cell[3][i].setCellStyle(rightBold10Style);
    }
    cell[4][0].setCellStyle(rightBold10Style);
    //field content cells
    final CellStyle left10Style = wb.createCellStyle();
    final Font garamond10Font = wb.createFont();
    garamond10Font.setFontHeightInPoints((short) 10);
    garamond10Font.setFontName("Garamond");
    left10Style.setFont(garamond10Font);
    left10Style.setAlignment(CellStyle.ALIGN_LEFT);
    for (int i = 3; i < 5; i++) {
        cell[1][i].setCellStyle(left10Style);
    }
    //phone number cells
    final CellStyle phoneStyle = wb.createCellStyle();
    phoneStyle.setFont(garamond10Font);
    phoneStyle.setAlignment(CellStyle.ALIGN_LEFT);
    final CreationHelper createHelper = wb.getCreationHelper();
    phoneStyle.setDataFormat(createHelper.createDataFormat().getFormat("[<=9999999]###-####;(###) ###-####"));
    for (int i = 5; i < 9; i++) {
        cell[1][i].setCellStyle(phoneStyle);
        sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));

    }
    cell[1][9].setCellStyle(left10Style);
    //email through past contact
    for (int i = 10; i < 16; i++) {
        cell[1][i].setCellStyle(left10Style);
    }
    //notes
    CellStyle noteStyle = wb.createCellStyle();
    noteStyle.cloneStyleFrom(left10Style);
    noteStyle.setWrapText(true);
    cell[1][17].setCellStyle(noteStyle);
    //column E
    for (int i = 3; i < 11; i++) {
        cell[4][i].setCellStyle(left10Style);
    }
    //VanID Cell
    final CellStyle right10Style = wb.createCellStyle();
    right10Style.setFont(garamond10Font);
    right10Style.setAlignment(CellStyle.ALIGN_RIGHT);
    cell[5][0].setCellStyle(right10Style);
    //Notes cell
    sheet.addMergedRegion(new CellRangeAddress(17, 19, 1, 5));
    //contribution cells
    final CellStyle date10Style = wb.createCellStyle();
    date10Style.setFont(garamond10Font);
    date10Style.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy"));
    date10Style.setBorderBottom(CellStyle.BORDER_THIN);
    date10Style.setBorderTop(CellStyle.BORDER_THIN);
    date10Style.setBorderLeft(CellStyle.BORDER_THIN);
    date10Style.setBorderRight(CellStyle.BORDER_THIN);
    final CellStyle contributionStyle = wb.createCellStyle();
    contributionStyle.cloneStyleFrom(left10Style);
    contributionStyle.setBorderBottom(CellStyle.BORDER_THIN);
    contributionStyle.setBorderTop(CellStyle.BORDER_THIN);
    contributionStyle.setBorderLeft(CellStyle.BORDER_THIN);
    contributionStyle.setBorderRight(CellStyle.BORDER_THIN);
    final CellStyle money10Style = wb.createCellStyle();
    money10Style.setFont(garamond10Font);
    money10Style.setDataFormat(
            createHelper.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"));
    money10Style.setBorderBottom(CellStyle.BORDER_THIN);
    money10Style.setBorderTop(CellStyle.BORDER_THIN);
    money10Style.setBorderLeft(CellStyle.BORDER_THIN);
    money10Style.setBorderRight(CellStyle.BORDER_THIN);
    for (int i = 22; i < 22 + contrib.getDonationsLength(); i++) {
        cell[0][i].setCellStyle(date10Style);
        cell[1][i].setCellStyle(contributionStyle);
        cell[2][i].setCellStyle(contributionStyle);
        cell[3][i].setCellStyle(contributionStyle);
        cell[4][i].setCellStyle(contributionStyle);
        sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 4));
        cell[5][i].setCellStyle(money10Style);

    }
    //resize columns
    sheet.autoSizeColumn(0);
    sheet.autoSizeColumn(1);
    try {
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
    } catch (FileNotFoundException e) {
        return 1;
    } catch (IOException ex) {
        return 1;
    }

    return 0;
}

From source file:com.alvexcore.repo.documents.generation.ExportDataListToXlsx.java

License:Open Source License

protected Map<String, Object> executeImpl(WebScriptRequest req, Status status, Cache cache) {
    Map<String, Object> model = new HashMap<String, Object>();

    final String FOLDER_NAME = "datalists-exports";
    final String XLS_SHEET_NAME = "DataList";
    // Parse the JSON, if supplied
    JSONObject json = null;//from  www  .  j  av  a 2 s.  co  m
    String contentType = req.getContentType();
    if (contentType != null && contentType.indexOf(';') != -1) {
        contentType = contentType.substring(0, contentType.indexOf(';'));
    }
    if (MimetypeMap.MIMETYPE_JSON.equals(contentType)) {
        try {
            json = (JSONObject) JSONValue.parse(req.getContent().getContent());
        } catch (Exception e) {
            status.setCode(500);
            model.put("message", "Invalid JSON");
            return model;
        }
    }

    String siteName;
    String fileName;
    JSONArray rows;

    try {
        siteName = (String) json.get("site");
        fileName = (String) json.get("fileName");
        rows = (JSONArray) json.get("rows");
    } catch (Exception e) {
        status.setCode(500);
        model.put("message", "Mandatory fields were not provided");
        return model;
    }

    fileName += ".xlsx";

    SiteInfo site = siteService.getSite(siteName);

    NodeRef container = siteService.getContainer(siteName, "documentLibrary");
    if (container == null)
        container = siteService.createContainer(siteName, "documentLibrary", null, null);

    NodeRef folder = getChildByName(container, FOLDER_NAME);

    if (folder == null) {
        Map<QName, Serializable> properties = new HashMap<QName, Serializable>(11);
        properties.put(ContentModel.PROP_NAME, FOLDER_NAME);

        folder = nodeService.createNode(container, ContentModel.ASSOC_CONTAINS,
                QName.createQName(NamespaceService.CONTENT_MODEL_1_0_URI, FOLDER_NAME),
                ContentModel.TYPE_FOLDER, properties).getChildRef();
    }

    NodeRef file = getChildByName(folder, fileName);
    if (file == null) {
        Map<QName, Serializable> properties = new HashMap<QName, Serializable>(11);
        properties.put(ContentModel.PROP_NAME, fileName);

        file = nodeService.createNode(folder, ContentModel.ASSOC_CONTAINS,
                QName.createQName(NamespaceService.CONTENT_MODEL_1_0_URI, fileName), ContentModel.TYPE_CONTENT,
                properties).getChildRef();
    }

    Workbook wb;

    try {
        wb = createXlsx(rows, XLS_SHEET_NAME);
    } catch (Exception e) {
        status.setCode(500);
        model.put("message", "Can not create file");
        return model;
    }

    try {
        ContentWriter writer = contentService.getWriter(file, ContentModel.PROP_CONTENT, true);
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        ByteArrayInputStream bais = new ByteArrayInputStream(baos.toByteArray());
        writer.setMimetype(MimetypeMap.MIMETYPE_EXCEL);
        writer.putContent(bais);
    } catch (Exception e) {
        status.setCode(500);
        model.put("message", "Can not save file");
        return model;
    }

    model.put("nodeRef", file.toString());
    model.put("name", fileName);
    status.setCode(200);
    return model;
}

From source file:com.alvexcore.repo.documents.generation.ExportReportToXlsx.java

License:Open Source License

protected Map<String, Object> executeImpl(WebScriptRequest req, Status status, Cache cache) {
    Map<String, Object> model = new HashMap<String, Object>();

    final String XLS_SHEET_NAME = "Report";
    // Parse the JSON, if supplied
    JSONObject json = null;//  w  w w  .  ja v a  2  s . c  o  m
    String contentType = req.getContentType();
    if (contentType != null && contentType.indexOf(';') != -1) {
        contentType = contentType.substring(0, contentType.indexOf(';'));
    }
    if (MimetypeMap.MIMETYPE_JSON.equals(contentType)) {
        try {
            json = (JSONObject) JSONValue.parse(req.getContent().getContent());
        } catch (Exception e) {
            status.setCode(500);
            model.put("message", "Invalid JSON");
            return model;
        }
    }

    String siteName;
    String fileName;
    JSONArray rows;

    try {
        fileName = (String) json.get("fileName");
        rows = (JSONArray) json.get("rows");
    } catch (Exception e) {
        status.setCode(500);
        model.put("message", "Mandatory fields were not provided");
        return model;
    }

    fileName += ".xlsx";

    NodeRef folder = createPath(EXPORTS_PATH, EXPORTS_ASSOCS, null);

    NodeRef file = getChildByName(folder, fileName);
    if (file == null) {
        Map<QName, Serializable> properties = new HashMap<QName, Serializable>(11);
        properties.put(ContentModel.PROP_NAME, fileName);

        file = nodeService.createNode(folder, ContentModel.ASSOC_CONTAINS,
                QName.createQName(NamespaceService.CONTENT_MODEL_1_0_URI, fileName), ContentModel.TYPE_CONTENT,
                properties).getChildRef();
    }

    Workbook wb;

    try {
        wb = createXlsx(rows, XLS_SHEET_NAME);
    } catch (Exception e) {
        status.setCode(500);
        model.put("message", "Can not create file");
        return model;
    }

    try {
        ContentWriter writer = contentService.getWriter(file, ContentModel.PROP_CONTENT, true);
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        ByteArrayInputStream bais = new ByteArrayInputStream(baos.toByteArray());
        writer.setMimetype(MimetypeMap.MIMETYPE_EXCEL);
        writer.putContent(bais);
    } catch (Exception e) {
        status.setCode(500);
        model.put("message", "Can not save file");
        return model;
    }

    model.put("nodeRef", file.toString());
    model.put("name", fileName);
    status.setCode(200);
    return model;
}

From source file:com.antonov.elparser.impl.domain.ExcelWorker.java

public void write(List<User> listUser) throws Exception {

    try (FileInputStream is = new FileInputStream(filePath)) {

        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = wb.getSheetAt(0);/*  w w w .j a  v a  2  s .  c  o m*/

        for (User user : listUser) {

            int row = user.getRow();
            UserInfo info = user.getInfo();

            if (info != null) {
                Long amountLetters = info.getAMOUNT_LETTERS();
                Long hirsh = info.getHIRSH();
                Double impactPublish = info.getIMPACT_PUBLISH();

                if (amountLetters != null) {
                    sheet.getRow(row).getCell(COLUMN_AMOUNT_LETTERS).setCellValue(amountLetters);
                } else {
                    sheet.getRow(row).getCell(COLUMN_AMOUNT_LETTERS)
                            .setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
                }

                if (hirsh != null) {
                    sheet.getRow(row).getCell(COLUMN_HIRSH).setCellValue(hirsh);
                } else {
                    sheet.getRow(row).getCell(COLUMN_HIRSH)
                            .setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
                }

                if (impactPublish != null) {
                    sheet.getRow(row).getCell(COLUMN_IMPACT_PUBLISH).setCellValue(impactPublish);
                } else {
                    sheet.getRow(row).getCell(COLUMN_IMPACT_PUBLISH)
                            .setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
                }
            } else {
                sheet.getRow(row).getCell(COLUMN_AMOUNT_LETTERS)
                        .setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
                sheet.getRow(row).getCell(COLUMN_HIRSH).setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
                sheet.getRow(row).getCell(COLUMN_IMPACT_PUBLISH)
                        .setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
            }
        }

        try (FileOutputStream os = new FileOutputStream(filePath)) {
            wb.write(os);
        }
    } catch (Throwable ex) {
        String message = "  ?     ";
        logger.error(message, ex);
        throw new Exception(message, ex);
    }
}