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:com.solidmaps.webapp.report.utils.ExcelMapCivilGenerator.java

private void createCellUtilization(XSSFSheet sheetBuy, XSSFRow row, String value) {
    XSSFCell cell = row.createCell(7);//from  w w w  . j  a v  a2 s .  c om
    sheetBuy.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 7, 8));
    cell.setCellStyle(styleProductList);
    cell.setCellValue(value);
}

From source file:com.solidmaps.webapp.report.utils.ExcelMapCivilGenerator.java

private void createCellSell(XSSFSheet sheetBuy, XSSFRow row, String value) {
    XSSFCell cell = row.createCell(9);/*from  www  . j  a  va 2s . c  om*/
    sheetBuy.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 9, 11));
    cell.setCellStyle(styleProductList);
    cell.setCellValue(value);
}

From source file:com.solidmaps.webapp.report.utils.ExcelMapCivilGenerator.java

private void createCellNextTrimester(XSSFSheet sheetBuy, XSSFRow row, String value) {
    XSSFCell cell = row.createCell(12);//from   ww  w .  ja  v a  2 s .  c o  m
    sheetBuy.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 12, 13));
    cell.setCellStyle(styleProductList);
    cell.setCellValue(value);
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.BitacoraReporteCliente.java

License:Apache License

@Override
public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) {
    Sheet sheet = contexto.getSheet();// w w  w.  ja  va 2s . c o  m
    XSSFWorkbook wb = contexto.getWb();
    int initialRow = borde.getUpperRow();
    int initialColumn = borde.getLeftColumn();
    BordeSeccion r = new BordeSeccion();
    r.setLeftColumn(initialColumn);
    r.setUpperRow(initialRow);

    Row row = getRow(sheet, initialRow);
    Cell cell = row.createCell(initialColumn);
    cell.setCellValue("Bitacora");
    XSSFCellStyle cellStyle = wb.createCellStyle();
    addHeaderStyle(cellStyle, wb);
    addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
    cell.setCellStyle(cellStyle);

    for (int i = 1; i < 4; i++) {
        cell = row.createCell(initialColumn + i);
        cellStyle = wb.createCellStyle();
        addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
        cell.setCellStyle(cellStyle);
    }

    //merge de celdas
    sheet.addMergedRegion(new CellRangeAddress(initialRow, //first row (0-based)
            initialRow, //last row  (0-based)
            initialColumn, //first column (0-based)
            initialColumn + 3 //last column  (0-based)
    ));

    CreationHelper createHelper = wb.getCreationHelper();
    for (EventoRC x : datos.getBitacora()) {
        initialRow = initialRow + 1;
        row = getRow(sheet, initialRow);
        for (int i = 0; i < atributos.length; i++) {
            cell = row.createCell(initialColumn + i);
            cellStyle = wb.createCellStyle();
            try {
                if (atributos[i].equals("fecha")) {
                    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yy/m/d h:mm:ss"));
                    cell.setCellValue((Date) PropertyUtils.getProperty(x, atributos[i]));
                } else {
                    cell.setCellValue(PropertyUtils.getProperty(x, atributos[i]).toString());
                }
            } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException ex) {
                cell.setCellValue("");
            }
            addBorders(wb, cellStyle, CellStyle.BORDER_THIN);
            cell.setCellStyle(cellStyle);
        }
    }
    r.setLowerRow(initialRow);
    r.setRightColumn(initialColumn + 3);
    paintBorder(wb, sheet, CellStyle.BORDER_MEDIUM, r);
    return r;
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.CostoHojalateriaReporteCliente.java

License:Apache License

@Override
public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) {
    Sheet sheet = contexto.getSheet();//  w w  w .  jav a 2 s.  c o  m
    XSSFWorkbook wb = contexto.getWb();
    int initialRow = borde.getUpperRow();
    int initialColumn = borde.getLeftColumn();
    BordeSeccion r = new BordeSeccion();
    r.setLeftColumn(initialColumn);
    r.setUpperRow(initialRow);

    Cell cell;
    Row row = getRow(sheet, initialRow);
    cell = row.createCell(initialColumn);
    cell.setCellValue("Hojalteria y pintura");
    XSSFCellStyle cellStyle = wb.createCellStyle();
    addHeaderStyle(cellStyle, wb);
    addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
    cell.setCellStyle(cellStyle);
    for (int i = 1; i < 3; i++) {
        cell = row.createCell(initialColumn + i);
        cellStyle = wb.createCellStyle();
        addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
        cell.setCellStyle(cellStyle);
    }
    //merge de celdas
    sheet.addMergedRegion(new CellRangeAddress(initialRow, //first row (0-based)
            initialRow, //last row  (0-based)
            initialColumn, //first column (0-based)
            initialColumn + 2 //last column  (0-based)
    ));

    //detalle
    cellStyle = wb.createCellStyle();
    XSSFDataFormat df = wb.createDataFormat();
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    Cell inicio = null;
    Cell fin = null;
    for (RegistroCostoRC x : datos.getRegistroHojalateriaPintura()) {
        initialRow = initialRow + 1;
        row = getRow(sheet, initialRow);
        //tipo
        cell = row.createCell(initialColumn);
        cell.setCellValue(x.getTipo());
        //descripcion
        cell = row.createCell(initialColumn + 1);
        cell.setCellValue(x.getDescripcion());
        //costo
        cell = row.createCell(initialColumn + 2);
        cell.setCellValue(x.getCosto());
        cell.setCellStyle(cellStyle);
        if (inicio == null) {
            inicio = cell;
        }
    }
    if (inicio != null) {
        fin = cell;
    }
    initialRow = initialRow + 1;
    row = getRow(sheet, initialRow);
    cell = row.createCell(initialColumn + 1);
    cell.setCellValue("Total");
    cellStyle = wb.createCellStyle();
    XSSFFont font = wb.createFont();
    font.setBold(true);
    cellStyle.setFont(font);
    cell.setCellStyle(cellStyle);

    cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    cell = row.createCell(initialColumn + 2);
    cell.setCellStyle(cellStyle);
    if (inicio != null) {
        String formula = "SUM(" + getSimpleReference(inicio) + ":" + getSimpleReference(fin) + ")";
        cell.setCellFormula(formula);
        contexto.put("totalHojalateria", cell);
    } else {
        cell.setCellValue(0.0);
    }
    r.setLowerRow(initialRow);
    r.setRightColumn(initialColumn + 2);
    BordeSeccion sinTotal = new BordeSeccion(r);
    sinTotal.setLowerRow(r.getLowerRow() - 1);
    paintBorder(wb, sheet, CellStyle.BORDER_MEDIUM, sinTotal);
    return r;
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.CostoMecanicaReporteCliente.java

License:Apache License

@Override
public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) {
    Sheet sheet = contexto.getSheet();//from w  w  w.ja  v  a2s  .c om
    XSSFWorkbook wb = contexto.getWb();
    int initialRow = borde.getUpperRow();
    int initialColumn = borde.getLeftColumn();
    BordeSeccion r = new BordeSeccion();
    r.setLeftColumn(initialColumn);
    r.setUpperRow(initialRow);

    Cell cell;
    Row row = getRow(sheet, initialRow);
    cell = row.createCell(initialColumn);
    cell.setCellValue("Mecanica");
    XSSFCellStyle cellStyle = wb.createCellStyle();
    addHeaderStyle(cellStyle, wb);
    addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
    cell.setCellStyle(cellStyle);
    for (int i = 1; i < 3; i++) {
        cell = row.createCell(initialColumn + i);
        cellStyle = wb.createCellStyle();
        addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
        cell.setCellStyle(cellStyle);
    }
    //merge de celdas
    sheet.addMergedRegion(new CellRangeAddress(initialRow, //first row (0-based)
            initialRow, //last row  (0-based)
            initialColumn, //first column (0-based)
            initialColumn + 2 //last column  (0-based)
    ));

    //detalle
    cellStyle = wb.createCellStyle();
    XSSFDataFormat df = wb.createDataFormat();
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    Cell inicio = null;
    Cell fin = null;
    for (RegistroCostoRC x : datos.getRegistroMecanica()) {
        initialRow = initialRow + 1;
        row = getRow(sheet, initialRow);
        //tipo
        cell = row.createCell(initialColumn);
        cell.setCellValue(x.getTipo());
        //descripcion
        cell = row.createCell(initialColumn + 1);
        cell.setCellValue(x.getDescripcion());
        //costo
        cell = row.createCell(initialColumn + 2);
        cell.setCellValue(x.getCosto());
        cell.setCellStyle(cellStyle);
        if (inicio == null) {
            inicio = cell;
        }
    }
    if (inicio != null) {
        fin = cell;
    }
    initialRow = initialRow + 1;
    row = getRow(sheet, initialRow);
    cell = row.createCell(initialColumn + 1);
    cell.setCellValue("Total");
    cellStyle = wb.createCellStyle();
    XSSFFont font = wb.createFont();
    font.setBold(true);
    cellStyle.setFont(font);
    cell.setCellStyle(cellStyle);

    cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    cell = row.createCell(initialColumn + 2);
    cell.setCellStyle(cellStyle);
    if (inicio != null) {
        String formula = "SUM(" + getSimpleReference(inicio) + ":" + getSimpleReference(fin) + ")";
        cell.setCellFormula(formula);
        contexto.put("totalMecanica", cell);
    } else {
        cell.setCellValue(0.0);
    }
    r.setLowerRow(initialRow);
    r.setRightColumn(initialColumn + 2);
    BordeSeccion sinTotal = new BordeSeccion(r);
    sinTotal.setLowerRow(r.getLowerRow() - 1);
    paintBorder(wb, sheet, CellStyle.BORDER_MEDIUM, sinTotal);
    return r;
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.DatosAutoReporteCliente.java

License:Apache License

@Override
public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) {
    Sheet sheet = contexto.getSheet();/*from w ww.j a va 2 s  . co  m*/
    XSSFWorkbook wb = contexto.getWb();
    int initialRow = borde.getUpperRow();
    int initialColumn = borde.getLeftColumn();
    BordeSeccion r = new BordeSeccion();
    r.setLeftColumn(initialColumn);
    r.setUpperRow(initialRow);

    //escribir el primer renglon
    Row row = getRow(sheet, initialRow);
    Cell cell = row.createCell(initialColumn);
    cell.setCellValue("Auto");
    //estilo .-.
    XSSFCellStyle cellStyle = wb.createCellStyle();
    addHeaderStyle(cellStyle, wb);
    addBorders(wb, cellStyle, CellStyle.BORDER_THIN);
    cell.setCellStyle(cellStyle);
    for (int i = 1; i < encabezados.length; i++) {
        cell = row.createCell(initialColumn + i);
        cellStyle = wb.createCellStyle();
        addBorders(wb, cellStyle, CellStyle.BORDER_THIN);
        cell.setCellStyle(cellStyle);
    }
    //merge de celdas
    sheet.addMergedRegion(new CellRangeAddress(initialRow, //first row (0-based)
            initialRow, //last row  (0-based)
            initialColumn, //first column (0-based)
            initialColumn + 7 //last column  (0-based)
    ));
    //segundo renglon encabezado
    initialRow = initialRow + 1;
    row = getRow(sheet, initialRow);
    for (int i = 0; i < encabezados.length; i++) {
        cell = row.createCell(initialColumn + i);
        cell.setCellValue(encabezados[i]);
        cellStyle = wb.createCellStyle();
        addHeaderStyle(cellStyle, wb);
        addBorders(wb, cellStyle, CellStyle.BORDER_THIN);
        cell.setCellStyle(cellStyle);
    }
    //tercer renglon encabezado
    initialRow = initialRow + 1;
    row = getRow(sheet, initialRow);
    for (int i = 0; i < atributos.length; i++) {
        cell = row.createCell(initialColumn + i);
        try {
            cell.setCellValue(PropertyUtils.getProperty(datos, "auto." + atributos[i]).toString());
        } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException ex) {
            cell.setCellValue("");
        }
        cellStyle = wb.createCellStyle();
        addBorders(wb, cellStyle, CellStyle.BORDER_THIN);
        cell.setCellStyle(cellStyle);
    }

    r.setLowerRow(initialRow);
    r.setRightColumn(initialColumn + 7);
    paintBorder(wb, sheet, CellStyle.BORDER_MEDIUM, r);
    return r;
}

From source file:com.tremolosecurity.scale.ui.reports.GenerateSpreadsheet.java

License:Apache License

@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

    resp.setHeader("Cache-Control", "private, no-store, no-cache, must-revalidate");
    resp.setHeader("Pragma", "no-cache");

    ReportViewer scaleReport = (ReportViewer) req.getSession().getAttribute("scaleReportCached");

    Workbook wb = new XSSFWorkbook();

    Font font = wb.createFont();//from   ww w . j  a  va  2  s  .c o m
    font.setBold(true);

    Font titleFont = wb.createFont();
    titleFont.setBold(true);
    titleFont.setFontHeightInPoints((short) 16);

    Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(scaleReport.getReportInfo().getName()));

    //Create a header
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);

    RichTextString title = new XSSFRichTextString(scaleReport.getReportInfo().getName());
    title.applyFont(titleFont);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

    cell.setCellValue(title);

    row = sheet.createRow(1);
    cell = row.createCell(0);
    cell.setCellValue(scaleReport.getReportInfo().getDescription());

    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));

    row = sheet.createRow(2);
    cell = row.createCell(0);
    cell.setCellValue(scaleReport.getRunDateTime());

    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 3));

    row = sheet.createRow(3);

    int rowNum = 4;

    if (scaleReport.getResults().getGrouping().isEmpty()) {
        row = sheet.createRow(rowNum);
        cell = row.createCell(0);
        cell.setCellValue("There is no data for this report");
    } else {

        for (ReportGrouping group : scaleReport.getResults().getGrouping()) {
            for (String colHeader : scaleReport.getResults().getHeaderFields()) {
                row = sheet.createRow(rowNum);
                cell = row.createCell(0);

                RichTextString rcolHeader = new XSSFRichTextString(colHeader);
                rcolHeader.applyFont(font);

                cell.setCellValue(rcolHeader);
                cell = row.createCell(1);
                cell.setCellValue(group.getHeader().get(colHeader));

                rowNum++;
            }

            row = sheet.createRow(rowNum);

            int cellNum = 0;
            for (String colHeader : scaleReport.getResults().getDataFields()) {
                cell = row.createCell(cellNum);

                RichTextString rcolHeader = new XSSFRichTextString(colHeader);
                rcolHeader.applyFont(font);
                cell.setCellValue(rcolHeader);
                cellNum++;
            }

            rowNum++;

            for (Map<String, String> dataRow : group.getData()) {
                cellNum = 0;
                row = sheet.createRow(rowNum);
                for (String colHeader : scaleReport.getResults().getDataFields()) {
                    cell = row.createCell(cellNum);
                    cell.setCellValue(dataRow.get(colHeader));
                    cellNum++;
                }
                rowNum++;
            }

            row = sheet.createRow(rowNum);
            rowNum++;
        }

    }

    resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    wb.write(resp.getOutputStream());

}

From source file:com.tremolosecurity.scalejs.ws.ScaleMain.java

License:Apache License

private void exportToExcel(HttpFilterRequest request, HttpFilterResponse response, Gson gson)
        throws IOException {
    int lastslash = request.getRequestURI().lastIndexOf('/');
    int secondlastslash = request.getRequestURI().lastIndexOf('/', lastslash - 1);

    String id = request.getRequestURI().substring(secondlastslash + 1, lastslash);

    ReportResults res = (ReportResults) request.getSession().getAttribute(id);

    if (res == null) {
        response.setStatus(404);/*from  w  w w.  ja  v  a2 s  .c  om*/
        ScaleError error = new ScaleError();
        error.getErrors().add("Report no longer available");
        ScaleJSUtils.addCacheHeaders(response);
        response.getWriter().print(gson.toJson(error).trim());
        response.getWriter().flush();
    } else {

        response.setHeader("Cache-Control", "private, no-store, no-cache, must-revalidate");
        response.setHeader("Pragma", "no-cache");

        Workbook wb = new XSSFWorkbook();

        Font font = wb.createFont();
        font.setBold(true);

        Font titleFont = wb.createFont();
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 16);

        Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(res.getName()));

        //Create a header
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);

        RichTextString title = new XSSFRichTextString(res.getName());
        title.applyFont(titleFont);

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

        cell.setCellValue(title);

        row = sheet.createRow(1);
        cell = row.createCell(0);
        cell.setCellValue(res.getDescription());

        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));

        row = sheet.createRow(2);
        cell = row.createCell(0);
        //cell.setCellValue(new DateTime().toString("MMMM Do, YYYY h:mm:ss a"));

        sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 3));

        row = sheet.createRow(3);

        int rowNum = 4;

        if (res.getGrouping().isEmpty()) {
            row = sheet.createRow(rowNum);
            cell = row.createCell(0);
            cell.setCellValue("There is no data for this report");
        } else {

            for (ReportGrouping group : res.getGrouping()) {
                for (String colHeader : res.getHeaderFields()) {
                    row = sheet.createRow(rowNum);
                    cell = row.createCell(0);

                    RichTextString rcolHeader = new XSSFRichTextString(colHeader);
                    rcolHeader.applyFont(font);

                    cell.setCellValue(rcolHeader);
                    cell = row.createCell(1);
                    cell.setCellValue(group.getHeader().get(colHeader));

                    rowNum++;
                }

                row = sheet.createRow(rowNum);

                int cellNum = 0;
                for (String colHeader : res.getDataFields()) {
                    cell = row.createCell(cellNum);

                    RichTextString rcolHeader = new XSSFRichTextString(colHeader);
                    rcolHeader.applyFont(font);
                    cell.setCellValue(rcolHeader);
                    cellNum++;
                }

                rowNum++;

                for (Map<String, String> dataRow : group.getData()) {
                    cellNum = 0;
                    row = sheet.createRow(rowNum);
                    for (String colHeader : res.getDataFields()) {
                        cell = row.createCell(cellNum);
                        cell.setCellValue(dataRow.get(colHeader));
                        cellNum++;
                    }
                    rowNum++;
                }

                row = sheet.createRow(rowNum);
                rowNum++;
            }

        }

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        wb.write(response.getOutputStream());
    }
}

From source file:com.vaadin.addon.spreadsheet.CellSelectionManager.java

License:Open Source License

/**
 * This is called when the sheet's address field has been changed and the
 * sheet selection and function field must be updated.
 * // w  ww .  j a  v a 2  s . com
 * @param value
 *            New value of the address field
 */
protected void onSheetAddressChanged(String value, boolean initialSelection) {
    try {
        if (value.contains(":")) {
            CellRangeAddress cra = spreadsheet.createCorrectCellRangeAddress(value);
            // need to check the range for merged regions
            MergedRegion region = MergedRegionUtil.findIncreasingSelection(
                    spreadsheet.getMergedRegionContainer(), cra.getFirstRow() + 1, cra.getLastRow() + 1,
                    cra.getFirstColumn() + 1, cra.getLastColumn() + 1);
            if (region != null) {
                cra = new CellRangeAddress(region.row1 - 1, region.row2 - 1, region.col1 - 1, region.col2 - 1);
            }
            handleCellRangeSelection(cra);
            selectedCellReference = new CellReference(cra.getFirstRow(), cra.getFirstColumn());
            paintedCellRange = cra;
            cellRangeAddresses.clear();
            cellRangeAddresses.add(cra);
        } else {
            final CellReference cellReference = new CellReference(value);
            MergedRegion region = MergedRegionUtil.findIncreasingSelection(
                    spreadsheet.getMergedRegionContainer(), cellReference.getRow() + 1,
                    cellReference.getRow() + 1, cellReference.getCol() + 1, cellReference.getCol() + 1);
            if (region != null && (region.col1 != region.col2 || region.row1 != region.row2)) {
                CellRangeAddress cra = spreadsheet.createCorrectCellRangeAddress(region.row1, region.col1,
                        region.row2, region.col2);
                handleCellRangeSelection(cra);
                selectedCellReference = new CellReference(cra.getFirstRow(), cra.getFirstColumn());
                paintedCellRange = cra;
                cellRangeAddresses.clear();
                cellRangeAddresses.add(cra);
            } else {
                handleCellAddressChange(cellReference.getRow() + 1, cellReference.getCol() + 1,
                        initialSelection);
                paintedCellRange = spreadsheet.createCorrectCellRangeAddress(cellReference.getRow() + 1,
                        cellReference.getCol() + 1, cellReference.getRow() + 1, cellReference.getCol() + 1);
                selectedCellReference = cellReference;
                cellRangeAddresses.clear();
            }
        }
        individualSelectedCells.clear();
        spreadsheet.loadCustomEditorOnSelectedCell();
        ensureClientHasSelectionData();
        fireNewSelectionChangeEvent();
    } catch (Exception e) {
        spreadsheet.getRpcProxy().invalidCellAddress();
    }
}