Example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. CellRangeAddress
  6. CellRangeAddress

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress.

Prototype

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

Source Link

Document

Creates new cell range.

Usage

From source file:invoiceapplication.CopyRowOriginal.java

private static void doMerge(Sheet worksheet, int rowIndex, int columnIndex, int rowSpan, int columnSpan) {
    Cell cell = worksheet.getRow(rowIndex).getCell(columnIndex);
    CellRangeAddress range = new CellRangeAddress(rowIndex, rowIndex + rowSpan - 1, columnIndex,
            columnIndex + columnSpan - 1);
    worksheet.addMergedRegion(range);//from w w  w . ja v  a  2 s. c  o m
    RegionUtil.setBorderTop(CellStyle.BORDER_THIN, range, worksheet, worksheet.getWorkbook());
    RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, range, worksheet, worksheet.getWorkbook());
    RegionUtil.setBorderRight(CellStyle.BORDER_THIN, range, worksheet, worksheet.getWorkbook());
    RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, range, worksheet, worksheet.getWorkbook());
}

From source file:invoiceapplication.CopyRowOriginal.java

private static void copyMergeRegion(Sheet worksheet, Row sourceRow, Row newRow, CellRangeAddress mergedRegion) {
    CellRangeAddress range = mergedRegion;
    if (range.getFirstRow() == sourceRow.getRowNum()) {
        //System.out.println(range.formatAsString());
        int lastRow = newRow.getRowNum() + (range.getFirstRow() - range.getLastRow());
        worksheet.addMergedRegion(new CellRangeAddress(newRow.getRowNum(), lastRow, range.getFirstColumn(),
                range.getLastColumn()));
    }//  w  w  w  .java 2s. c om
}

From source file:io.vertigo.quarto.plugins.export.xls.XLSExporter.java

License:Apache License

private void exportList(final ExportSheet parameters, final HSSFWorkbook workbook, final HSSFSheet sheet,
        final Map<Integer, Double> maxWidthPerColumn) {
    // exporte le header
    final HSSFRow headerRow = sheet.createRow(0);
    int cellIndex = 0;
    for (final ExportField exportColumn : parameters.getExportFields()) {
        final HSSFCell cell = headerRow.createCell(cellIndex);
        final String displayedLabel = exportColumn.getLabel().getDisplay();
        cell.setCellValue(new HSSFRichTextString(displayedLabel));
        cell.setCellStyle(createHeaderCellStyle(workbook));

        updateMaxWidthPerColumn(displayedLabel, 1.2, cellIndex, maxWidthPerColumn); // +20% pour les majuscules
        cellIndex++;/*from  w  ww . j  a  v a  2 s. c o  m*/
    }
    //La premiere ligne est rptable
    sheet.setRepeatingRows(new CellRangeAddress(0, 0, -1, -1));

    int rowIndex = 1;
    for (final DtObject dto : parameters.getDtList()) {
        final HSSFRow row = sheet.createRow(rowIndex);
        cellIndex = 0;
        Object value;
        for (final ExportField exportColumn : parameters.getExportFields()) {
            final HSSFCell cell = row.createCell(cellIndex);

            value = ExportUtil.getValue(storeManager, referenceCache, denormCache, dto, exportColumn);
            putValueInCell(value, cell, rowIndex % 2 == 0 ? evenHssfStyleCache : oddHssfStyleCache, cellIndex,
                    maxWidthPerColumn, exportColumn.getDtField().getDomain());

            cellIndex++;
        }
        rowIndex++;
    }
}

From source file:it.eng.spagobi.engines.qbe.crosstable.exporter.CrosstabXLSExporter.java

License:Mozilla Public License

/**
 * Builds the rows' headers recursively with this order:
 * |-----|-----|-----|// w  w  w  .  j a  v a  2s. c  o m
 * |     |     |  3  |
 * |     |     |-----|
 * |     |  2  |  4  |
 * |     |     |-----|
 * |  1  |     |  5  |
 * |     |-----|-----|
 * |     |     |  7  |
 * |     |  6  |-----|
 * |     |     |  8  |
 * |-----|-----|-----|
 * |     |     |  11 |
 * |  9  |  10 |-----|
 * |     |     |  12 |
 * |-----|-----|-----|
 * 
 * @param sheet The sheet of the XLS file
 * @param siblings The siblings nodes of the headers structure
 * @param rowNum The row number where the first sibling must be inserted
 * @param columnNum The column number where the siblings must be inserted
 * @param createHelper The file creation helper
 * @throws JSONException
 */
protected void buildRowsHeaders(Sheet sheet, CrossTab cs, List<Node> siblings, int rowNum, int columnNum,
        CreationHelper createHelper, Locale locale, CellStyle cellStyle) throws JSONException {
    int rowsCounter = rowNum;

    for (int i = 0; i < siblings.size(); i++) {
        Node aNode = siblings.get(i);
        List<Node> childs = aNode.getChilds();
        Row row = sheet.getRow(rowsCounter);
        Cell cell = row.createCell(columnNum);
        String text = (String) aNode.getDescription();

        if (cs.isMeasureOnRow() && (childs == null || childs.size() <= 0)) {
            //apply the measure scale factor
            text = MeasureScaleFactorOption.getScaledName(text, cs.getMeasureScaleFactor(text), locale);
        }
        cell.setCellValue(createHelper.createRichTextString(text));
        cell.setCellType(this.getCellTypeString());

        cell.setCellStyle(cellStyle);

        int descendants = aNode.getLeafsNumber();
        if (descendants > 1) {
            sheet.addMergedRegion(new CellRangeAddress(rowsCounter, //first row (0-based)
                    rowsCounter + descendants - 1, //last row  (0-based)
                    columnNum, //first column (0-based)
                    columnNum //last column  (0-based)
            ));
        }

        if (childs != null && childs.size() > 0) {
            buildRowsHeaders(sheet, cs, childs, rowsCounter, columnNum + 1, createHelper, locale, cellStyle);
        }
        int increment = descendants > 1 ? descendants : 1;
        rowsCounter = rowsCounter + increment;
    }
}

From source file:it.eng.spagobi.engines.qbe.crosstable.exporter.CrosstabXLSExporter.java

License:Mozilla Public License

/**
 * Builds the columns' headers recursively with this order:
 * |------------------------------------------|
 * |              1              |     9      |
 * |------------------------------------------|
 * |     2     |        5        |     10     |
 * |-----------|-----------------|------------|
 * |  3  |  4  |  6  |  7  |  8  |  11  | 12  |
 * |------------------------------------------|
 * //from   www. ja  va  2 s  . c o m
 * @param sheet The sheet of the XLS file
 * @param siblings The siblings nodes of the headers structure
 * @param rowNum The row number where the siblings must be inserted
 * @param columnNum The column number where the first sibling must be inserted
 * @param createHelper The file creation helper
 * @param dimensionCellStyle The cell style for cells containing dimensions (i.e. attributes' names)
 * @param memberCellStyle The cell style for cells containing members (i.e. attributes' values)
 * @throws JSONException
 */
protected void buildColumnsHeader(Sheet sheet, CrossTab cs, List<Node> siblings, int rowNum, int columnNum,
        CreationHelper createHelper, Locale locale, CellStyle memberCellStyle, CellStyle dimensionCellStyle)
        throws JSONException {
    int columnCounter = columnNum;

    for (int i = 0; i < siblings.size(); i++) {
        Node aNode = (Node) siblings.get(i);
        List<Node> childs = aNode.getChilds();
        Row row = sheet.getRow(rowNum);
        Cell cell = row.createCell(columnCounter);
        String text = (String) aNode.getDescription();
        if (!cs.isMeasureOnRow() && (childs == null || childs.size() <= 0)) {
            //apply the measure scale factor
            text = MeasureScaleFactorOption.getScaledName(text, cs.getMeasureScaleFactor(text), locale);
        }

        cell.setCellValue(createHelper.createRichTextString(text));
        cell.setCellType(this.getCellTypeString());
        int descendants = aNode.getLeafsNumber();
        if (descendants > 1) {
            sheet.addMergedRegion(new CellRangeAddress(rowNum, //first row (0-based)
                    rowNum, //last row  (0-based)
                    columnCounter, //first column (0-based)
                    columnCounter + descendants - 1 //last column  (0-based)
            ));
        }

        /*
         * Now we have to set the style properly according to the nature of
         * the node: if it contains the name of a dimension or a member.
         * Since the structure foresees that a list of members follows a
         * dimension, we calculate the position of the node with respect to
         * the leaves; in case it is odd, the cell contains a dimension; in
         * case it is even, the cell contains a dimension.
         */
        int distanceToLeaves = aNode.getDistanceFromLeaves();
        if (!cs.isMeasureOnRow()) {
            distanceToLeaves--;
        }
        boolean isDimensionNameCell = distanceToLeaves > 0 && (distanceToLeaves % 2) == 1;
        if (isDimensionNameCell) {
            cell.setCellStyle(dimensionCellStyle);
        } else {
            cell.setCellStyle(memberCellStyle);
        }

        if (childs != null && childs.size() > 0) {
            buildColumnsHeader(sheet, cs, childs, rowNum + 1, columnCounter, createHelper, locale,
                    memberCellStyle, dimensionCellStyle);
        }
        int increment = descendants > 1 ? descendants : 1;
        columnCounter = columnCounter + increment;
    }
}

From source file:it.inspired.exporter.ExcelExporter.java

License:Open Source License

@Override
protected void writeHeader() {
    Row row0 = sheet.createRow(0);/* w ww.  jav a  2  s .  co m*/
    Row row1 = sheet.createRow(1);

    int coll = 0;
    for (Header header : headers) {
        Cell cell0 = row0.createCell(coll);
        //cell0.setCellValue( StringUtils.capitalizeMethodName( header.getType().getSimpleName() ) );
        cell0.setCellValue(super.getHeaderName(header));

        CellRangeAddress region = new CellRangeAddress(0, 0, coll, coll + header.getProperties().size() - 1);
        sheet.addMergedRegion(region);

        CellStyle style = workbook.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        cell0.setCellStyle(style);

        for (PropertyHeader ph : header.getProperties()) {
            String collName = null;

            Cell cell1 = row1.createCell(coll);

            // Check if property labekKey is overrided
            if (ph.isOverrided()) {
                String key = AnnotationHelper.getLabelKey(ph.getOverridingEpoProperty());
                if (key != null) {
                    collName = getText(key);
                }
            }

            if (collName == null) {
                collName = super.getPropertyHeaderName(ph.getProperty());
            }

            cell1.setCellValue(collName);
            coll++;
        }
    }
}

From source file:it.redev.parco.ext.ExportableModelEntityQuery.java

License:Open Source License

private void exportHeaders(Sheet sheet) {
    Row row0 = sheet.createRow(0);/*from   ww w  .j a  v  a 2  s . c o  m*/
    Row row1 = sheet.createRow(1);

    int coll = 0;
    for (Header header : headers) {
        Cell cell0 = row0.createCell(coll);
        cell0.setCellValue(StringUtils.capitalizeMethodName(header.clazz.getSimpleName()));

        CellRangeAddress region = new CellRangeAddress(0, 0, coll, coll + header.properties.size() - 1);
        sheet.addMergedRegion(region);

        CellStyle style = workbook.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        cell0.setCellStyle(style);

        for (String pd : header.properties) {
            Cell cell1 = row1.createCell(coll);
            cell1.setCellValue(StringUtils.capitalizeMethodName(pd));
            coll++;
        }
    }
}

From source file:jdbreport.model.io.xls.poi.Excel2003Writer.java

License:Apache License

private void saveRow(Workbook wb, Sheet sheet, ReportBook reportBook, ReportModel model, int row,
        CreationHelper createHelper) throws SaveReportException {

    TableRow tableRow = model.getRowModel().getRow(row);
    Row sheetRow = sheet.getRow(row);//w  w  w  .jav  a 2s  .c  o  m

    for (int column = 0; column < tableRow.getColCount(); column++) {
        jdbreport.model.Cell cell = tableRow.getCellItem(column);
        if (!cell.isChild()) {
            Cell newCell = sheetRow.getCell(column);
            if (newCell == null) {
                newCell = sheetRow.createCell(column);
            }

            Object styleId = cell.getStyleId();
            if (styleId != null) {
                CellStyle newStyle = styleMap.get(styleId);
                if (newStyle != null) {
                    newCell.setCellStyle(newStyle);
                    if (cell.isSpan()) {
                        for (int row1 = row; row1 <= row + cell.getRowSpan(); row1++) {
                            Row spanedRow = sheet.getRow(row1);
                            if (spanedRow == null) {
                                spanedRow = sheet.createRow(row1);
                            }
                            for (int column1 = column; column1 <= column + cell.getColSpan(); column1++) {
                                if (row1 != row || column1 != column) {
                                    Cell newCell1 = spanedRow.createCell(column1);
                                    newCell1.setCellStyle(newStyle);
                                }
                            }
                        }
                    }
                }
            }

            Object value = cell.getValue();

            if (value != null) {
                if (cell.getValueType() == Type.BOOLEAN) {
                    newCell.setCellType(CellType.BOOLEAN);
                    newCell.setCellValue((Boolean) value);
                } else if (cell.getValueType() == Type.CURRENCY || cell.getValueType() == Type.FLOAT) {
                    setDoubleValue(wb, createHelper, newCell, styleId, (Number) value);
                } else if (cell.getValueType() == Type.DATE) {
                    newCell.setCellStyle(getStyle(styleId, Type.DATE, wb, createHelper));
                    newCell.setCellValue((Date) value);
                } else if (reportBook.getStyles(cell.getStyleId()).getDecimal() != -1) {
                    try {
                        setDoubleValue(wb, createHelper, newCell, styleId, Utils.parseDouble(value.toString()));
                    } catch (Exception e) {
                        newCell.setCellValue(0);
                    }
                } else {
                    String text = null;
                    if (value instanceof CellValue<?>) {
                        StringWriter strWriter = new StringWriter();
                        PrintWriter printWriter = new PrintWriter(strWriter);
                        if (!((CellValue<?>) value).write(printWriter, model, row, column, this,
                                ReportBook.XLS)) {
                            java.awt.Image img = ((CellValue<?>) cell.getValue()).getAsImage(model, row,
                                    column);
                            if (img instanceof RenderedImage) {
                                createImage(wb, model, cell, (RenderedImage) img, row, column, createHelper);
                            }

                        } else {
                            text = strWriter.getBuffer().toString();
                        }
                    } else {
                        newCell.setCellType(CellType.STRING);

                        if (jdbreport.model.Cell.TEXT_HTML.equals(cell.getContentType())) {

                            HTMLDocument doc = getHTMLDocument(cell);
                            List<Content> contentList = Content.getHTMLContentList(doc);
                            if (contentList != null) {
                                RichTextString richText = createRichTextFromContent(contentList, createHelper,
                                        wb, newCell.getCellStyle().getFontIndex());
                                if (richText != null) {
                                    newCell.setCellValue(richText);
                                }
                            }
                        } else {
                            text = model.getCellText(cell);
                        }
                    }
                    if (text != null) {
                        newCell.setCellValue(text);
                    }
                }
            }

            if (cell.getPicture() != null) {
                createImage(wb, model, cell, Utils.getRenderedImage(cell.getPicture().getIcon()), row, column,
                        createHelper);
            }

            if (cell.getCellFormula() != null) {
                newCell.setCellFormula(cell.getCellFormula());
            }

            if (cell.isSpan()) {
                sheet.addMergedRegion(
                        new CellRangeAddress(row, row + cell.getRowSpan(), column, column + cell.getColSpan()));
                column += cell.getColSpan();
            }

        }
    }
}

From source file:jgnash.engine.budget.BudgetResultsExport.java

License:Open Source License

public static void exportBudgetResultsModel(final File file, final BudgetResultsModel model) {

    Resource rb = Resource.get();

    Workbook wb;//from w ww . j  a v a2 s .c  o  m

    String extension = FileUtils.getFileExtension(file.getAbsolutePath());

    if (extension.equals("xlsx")) {
        wb = new XSSFWorkbook();
    } else {
        wb = new HSSFWorkbook();
    }

    CreationHelper createHelper = wb.getCreationHelper();

    // create a new sheet
    Sheet s = wb.createSheet(model.getBudget().getName());

    // create header cell styles
    CellStyle headerStyle = wb.createCellStyle();

    // create 2 fonts objects
    Font amountFont = wb.createFont();
    Font headerFont = wb.createFont();

    amountFont.setFontHeightInPoints((short) 10);
    amountFont.setColor(IndexedColors.BLACK.getIndex());

    headerFont.setFontHeightInPoints((short) 11);
    headerFont.setColor(IndexedColors.BLACK.getIndex());
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

    DataFormat df = wb.createDataFormat();

    // Set the other cell style and formatting
    headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
    headerStyle.setBorderTop(CellStyle.BORDER_THIN);
    headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
    headerStyle.setBorderRight(CellStyle.BORDER_THIN);
    headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    headerStyle.setDataFormat(df.getFormat("text"));
    headerStyle.setFont(headerFont);
    headerStyle.setAlignment(CellStyle.ALIGN_CENTER);

    int row = 0;
    Row r = s.createRow(row);

    // create period headers
    for (int i = 0; i < model.getDescriptorList().size(); i++) {
        Cell c = r.createCell(i * 3 + 1);
        c.setCellValue(
                createHelper.createRichTextString(model.getDescriptorList().get(i).getPeriodDescription()));
        c.setCellStyle(headerStyle);
        s.addMergedRegion(new CellRangeAddress(row, row, i * 3 + 1, i * 3 + 3));
    }

    {
        int col = model.getDescriptorList().size() * 3 + 1;
        Cell c = r.createCell(col);
        c.setCellValue(createHelper.createRichTextString(rb.getString("Title.Summary")));
        c.setCellStyle(headerStyle);
        s.addMergedRegion(new CellRangeAddress(row, row, col, col + 2));
    }

    // create results header columns
    row++;
    r = s.createRow(row);

    {
        Cell c = r.createCell(0);
        c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Account")));
        c.setCellStyle(headerStyle);

        for (int i = 0; i <= model.getDescriptorList().size(); i++) {
            c = r.createCell(i * 3 + 1);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Budgeted")));
            c.setCellStyle(headerStyle);

            c = r.createCell(i * 3 + 2);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Change")));
            c.setCellStyle(headerStyle);

            c = r.createCell(i * 3 + 3);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Remaining")));
            c.setCellStyle(headerStyle);
        }
    }

    // must sort the accounts, otherwise child structure is not correct
    List<Account> accounts = new ArrayList<>(model.getAccounts());
    Collections.sort(accounts);

    // create account rows
    for (Account account : accounts) {

        CellStyle amountStyle = wb.createCellStyle();
        amountStyle.setFont(amountFont);

        DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(account.getCurrencyNode());
        String pattern = format.toLocalizedPattern().replace("", account.getCurrencyNode().getPrefix());
        amountStyle.setDataFormat(df.getFormat(pattern));

        row++;

        int col = 0;

        r = s.createRow(row);

        CellStyle cs = wb.createCellStyle();
        cs.cloneStyleFrom(headerStyle);
        cs.setAlignment(CellStyle.ALIGN_LEFT);
        cs.setIndention((short) (model.getDepth(account) * 2));

        Cell c = r.createCell(col);
        c.setCellValue(createHelper.createRichTextString(account.getName()));
        c.setCellStyle(cs);

        List<CellReference> budgetedRefList = new ArrayList<>();
        List<CellReference> changeRefList = new ArrayList<>();
        List<CellReference> remainingRefList = new ArrayList<>();

        for (int i = 0; i < model.getDescriptorList().size(); i++) {

            BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), account);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(results.getBudgeted().doubleValue());
            c.setCellStyle(amountStyle);

            CellReference budgetedRef = new CellReference(row, col);
            budgetedRefList.add(budgetedRef);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(results.getChange().doubleValue());
            c.setCellStyle(amountStyle);

            CellReference changeRef = new CellReference(row, col);
            changeRefList.add(changeRef);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_FORMULA);
            c.setCellStyle(amountStyle);
            c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString());

            CellReference remainingRef = new CellReference(row, col);
            remainingRefList.add(remainingRef);
        }

        // add summary columns                               
        addSummaryCell(r, ++col, budgetedRefList, amountStyle);
        addSummaryCell(r, ++col, changeRefList, amountStyle);
        addSummaryCell(r, ++col, remainingRefList, amountStyle);
    }

    // add group summary rows
    for (AccountGroup group : model.getAccountGroupList()) {

        CellStyle amountStyle = wb.createCellStyle();
        amountStyle.setFont(amountFont);
        amountStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        amountStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        amountStyle.setBorderBottom(CellStyle.BORDER_THIN);
        amountStyle.setBorderTop(CellStyle.BORDER_THIN);
        amountStyle.setBorderLeft(CellStyle.BORDER_THIN);
        amountStyle.setBorderRight(CellStyle.BORDER_THIN);

        DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(model.getBaseCurrency());
        String pattern = format.toLocalizedPattern().replace("", model.getBaseCurrency().getPrefix());
        amountStyle.setDataFormat(df.getFormat(pattern));

        row++;

        int col = 0;

        r = s.createRow(row);

        CellStyle cs = wb.createCellStyle();
        cs.cloneStyleFrom(headerStyle);
        cs.setAlignment(CellStyle.ALIGN_LEFT);

        Cell c = r.createCell(col);
        c.setCellValue(createHelper.createRichTextString(group.toString()));
        c.setCellStyle(cs);

        List<CellReference> budgetedRefList = new ArrayList<>();
        List<CellReference> changeRefList = new ArrayList<>();
        List<CellReference> remainingRefList = new ArrayList<>();

        for (int i = 0; i < model.getDescriptorList().size(); i++) {

            BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), group);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(results.getBudgeted().doubleValue());
            c.setCellStyle(amountStyle);

            CellReference budgetedRef = new CellReference(row, col);
            budgetedRefList.add(budgetedRef);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(results.getChange().doubleValue());
            c.setCellStyle(amountStyle);

            CellReference changeRef = new CellReference(row, col);
            changeRefList.add(changeRef);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_FORMULA);
            c.setCellStyle(amountStyle);
            c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString());

            CellReference remainingRef = new CellReference(row, col);
            remainingRefList.add(remainingRef);
        }

        // add summary columns                               
        addSummaryCell(r, ++col, budgetedRefList, amountStyle);
        addSummaryCell(r, ++col, changeRefList, amountStyle);
        addSummaryCell(r, ++col, remainingRefList, amountStyle);
    }

    // force evaluation
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    evaluator.evaluateAll();

    short columnCount = s.getRow(1).getLastCellNum();

    // autosize all of the columns + 10 pixels
    for (int i = 0; i <= columnCount; i++) {
        s.autoSizeColumn(i);
        s.setColumnWidth(i, s.getColumnWidth(i) + 10);
    }

    // Save
    String filename = file.getAbsolutePath();

    if (wb instanceof XSSFWorkbook) {
        filename = FileUtils.stripFileExtension(filename) + ".xlsx";
    } else {
        filename = FileUtils.stripFileExtension(filename) + ".xls";
    }

    try (FileOutputStream out = new FileOutputStream(filename)) {
        wb.write(out);
    } catch (Exception e) {
        Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
    }
}

From source file:kp.servlet.ExportRpt.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods.//from   w  w  w .j  a  va 2s. c o m
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");

    Logger.getLogger(ExportRpt.class.getName()).log(Level.SEVERE,
            "accRole :" + request.getParameter("accRole"));
    Logger.getLogger(ExportRpt.class.getName()).log(Level.SEVERE, "Unit :" + request.getParameter("unit"));
    ArrayList<MocWfTran> Mocstatus = new ArrayList<>();
    TranDao tdao = new TranDao();
    Mocstatus = tdao.getMocStatusList(request.getParameter("accRole"), request.getParameter("unit"),
            request.getParameter("user"));

    //Developing Metadata
    String rptName = "MOC Status Excel Report";
    ArrayList<String> colLabel = new ArrayList<>();
    colLabel.add("Case Id");
    colLabel.add("Moc NO");
    colLabel.add("Moc Title");
    colLabel.add("Moc Status");
    colLabel.add("Creation Date");
    colLabel.add("Owner's Name");
    colLabel.add("Unit");
    colLabel.add("Plant");
    colLabel.add("Current Stage");
    colLabel.add("Pending At");

    //Starting EXCEL Creating
    //XLS Variable
    XSSFSheet spreadsheet;
    XSSFWorkbook workbook;
    XSSFRow row;
    XSSFCell cell;
    XSSFFont xfont = null;
    XSSFCellStyle xstyle = null;

    //2.Create WorkBook and Sheet
    workbook = new XSSFWorkbook();
    spreadsheet = workbook.createSheet(rptName);

    //set header style
    xfont = workbook.createFont();
    xfont.setFontHeight(11);
    xfont.setFontName("Calibri");
    xfont.setBold(true);

    //Set font into style
    CellStyle borderStyle = workbook.createCellStyle();
    borderStyle.setAlignment(CellStyle.ALIGN_CENTER);
    borderStyle.setFont(xfont);
    xstyle = workbook.createCellStyle();
    xstyle.setFont(xfont);

    //header
    row = spreadsheet.createRow(0);
    cell = row.createCell(0);
    cell.setCellValue(rptName);
    cell.setCellStyle(borderStyle);
    spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colLabel.size() - 1));

    //3.Get First Row and Set Headers
    row = spreadsheet.createRow(1);

    for (int i = 0; i < colLabel.size(); i++) {
        cell = row.createCell(i);
        cell.setCellValue(colLabel.get(i));
        cell.setCellStyle(xstyle);
    }

    //Itrate or Database data and write
    int i = 2;
    for (MocWfTran bean : Mocstatus) {
        row = spreadsheet.createRow(i);
        cell = row.createCell(0);
        cell.setCellValue(bean.getCaseId());
        cell = row.createCell(1);
        cell.setCellValue(bean.getMocNo());
        cell = row.createCell(2);
        cell.setCellValue(bean.getCaseName());
        cell = row.createCell(3);
        cell.setCellValue(bean.getMocStatus());
        cell = row.createCell(4);
        cell.setCellValue(bean.getCrDateString());
        cell = row.createCell(5);
        cell.setCellValue(bean.getCaseOwnerName());
        cell = row.createCell(6);
        cell.setCellValue(bean.getUnitId());
        cell = row.createCell(7);
        cell.setCellValue(bean.getPlantId());
        cell = row.createCell(8);
        cell.setCellValue(bean.getStgNname());
        cell = row.createCell(9);
        cell.setCellValue(bean.getUserNname());
        i++;
    }

    //Export to Excel
    String file_name = "MocStatus";
    String path = getServletContext().getRealPath("/");
    String full_path = path + "/report/" + file_name + ".xlsx";
    //        FileOutputStream out = new FileOutputStream(new File("D://" + file_name + ".xlsx"));
    FileOutputStream out = new FileOutputStream(new File(full_path));
    workbook.write(out);

    //Download code 
    // reads input file from an absolute path
    File downloadFile = new File(full_path);
    OutputStream outStream;
    // obtains ServletContext
    try (FileInputStream inStream = new FileInputStream(downloadFile)) {
        //obtains ServletContext
        ServletContext context = getServletContext();
        // gets MIME type of the file
        String mimeType = context.getMimeType(full_path);
        if (mimeType == null) {
            // set to binary type if MIME mapping not found
            mimeType = "application/octet-stream";
        } // modifies response
        response.setContentType(mimeType);
        response.setContentLength((int) downloadFile.length());
        // forces download
        String headerKey = "Content-Disposition";
        String headerValue = String.format("attachment; filename=\"%s\"", downloadFile.getName());
        response.setHeader(headerKey, headerValue);
        // obtains response's output stream
        outStream = response.getOutputStream();
        byte[] buffer = new byte[4096];
        int bytesRead = -1;
        while ((bytesRead = inStream.read(buffer)) != -1) {
            outStream.write(buffer, 0, bytesRead);
        }
    }
    outStream.close();
    //        response.sendRedirect("mocstatus.jsp");
}