Example usage for org.apache.poi.hssf.usermodel HSSFSheet getRow

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFSheet getRow.

Prototype

@Override
public HSSFRow getRow(int rowIndex) 

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:at.htlpinkafeld.beans.UserDetailsBean.java

public void postProcessXLS(Object document) {
        HSSFWorkbook wb = (HSSFWorkbook) document;
        HSSFSheet sheet = wb.getSheetAt(0);

        sheet.shiftRows(0, sheet.getLastRowNum(), 2);

        HSSFRow topRow = sheet.createRow(0);

        topRow.createCell(0)/*from w ww  .  jav  a2s  .  co m*/
                .setCellValue("Monatsbersicht - " + selectedDate.format(DateTimeFormatter.ofPattern("MM.yyyy")));
        topRow.createCell(7).setCellValue("von " + selectedUser);
        sheet.createRow(1).createCell(0).setCellValue(" ");

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

        HSSFRow header = sheet.getRow(2);
        HSSFRow footer = sheet.getRow(sheet.getLastRowNum());

        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
            HSSFCell cell = header.getCell(i);
            cell.setCellStyle(cellStyle);

            cell = footer.getCell(i);
            cell.setCellStyle(cellStyle);

            sheet.autoSizeColumn(i);
        }

        HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2);
        bottomRow.createCell(0)
                .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy")));

    }

---

From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java

License:Open Source License

/**
 * Transfers the spreadsheet data from the POI <code>HSSFWorkbook</code> into the <code>IGridWMServer</code>.
 * Only the sheet on the given sheetIndex is copied.
        /* w ww  .j a v  a2 s. c om*/
 * @param igrid the XMA model where to copy the data
 * @param book the POI represntation of the data
 * @param sheetIndex the index of the sheet to copy
 * @return a list containing all SysExceptions describing problems with individual cell formulas or ranges
 */
public static List poi2xma(IGridWM igrid, HSSFWorkbook book, int sheetIndex) {
    GridWM grid = (GridWM) igrid;
    try {
        List errorList = new ArrayList();
        grid.setSheetName(book.getSheetName(sheetIndex));

        grid.colors.clear();
        grid.initBuildInColors();
        short ic = GridWM.HSSF_FIRST_COLOR_INDEX;
        HSSFPalette palette = book.getCustomPalette();
        for (HSSFColor color = palette.getColor(ic); ic < 64 && color != null; color = palette.getColor(++ic)) {
            grid.colors.add(ic, new GridColor(color.getTriplet()));
        }

        grid.fonts.clear();
        int numFonts = book.getNumberOfFonts();
        if (numFonts > 4) {
            // adjust for "There is no 4" see code of org.apache.poi.hssf.model.Workbook.getFontRecordAt()
            numFonts += 1;
        }
        for (short i = 0; i < numFonts; i++) {
            HSSFFont font = book.getFontAt(i);
            byte fontstyle = GridFont.FONT_NORML;
            if (font.getBoldweight() >= HSSFFont.BOLDWEIGHT_BOLD)
                fontstyle |= GridFont.FONT_BOLD;
            if (font.getItalic())
                fontstyle |= GridFont.FONT_ITALIC;
            grid.fonts.add(i, new GridFont(font.getFontName(), fontstyle, font.getColor()));
        }

        grid.styles.clear();
        for (short i = 0, numStyles = book.getNumCellStyles(); i < numStyles; i++) {
            HSSFCellStyle style = book.getCellStyleAt(i);
            grid.styles.add(i, new GridCellStyle(style.getFontIndex(), style.getFillForegroundColor()));
        }

        grid.namedRanges.clear();
        for (int i = 0, numRanges = book.getNumberOfNames(); i < numRanges; i++) {
            HSSFName name = book.getNameAt(i);
            String rangeName = name.getNameName();
            String rangeRef = null;
            try { // ranges not defined but referenced by formulas have a name but no reference in HSSF
                rangeRef = name.getReference();
            } catch (Exception exc) {
                errorList.add(new SysException(exc, ((GridWM) grid).getMessage("inconsistentRange", rangeName))
                        .setCode(GridWM.CODE_inconsistentRange));
            }
            if (rangeRef != null) {
                try {
                    GridRange range = grid.getJeksDelegate().toRange(rangeRef);
                    range.setKey(rangeName);
                    grid.namedRanges.put(rangeName, range);
                } catch (Exception exc) {
                    errorList.add(new SysException(exc,
                            ((GridWM) grid).getMessage("unsupportedReference", rangeName, rangeRef))
                                    .setCode(GridWM.CODE_unsupportedReference));
                }
            }
        }

        grid.rows.clear();
        grid.cols.clear();
        grid.cells.clear();
        grid.delegate = new GridJeksDelegate(grid);
        HSSFSheet sheet = book.getSheetAt(sheetIndex);
        int firstColNum = Integer.MAX_VALUE;
        int lastColNum = Integer.MIN_VALUE;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null)
                continue;
            if (row.getFirstCellNum() >= 0)
                firstColNum = Math.min(firstColNum, row.getFirstCellNum());
            lastColNum = Math.max(lastColNum, row.getLastCellNum());
            if (lastColNum > 255)
                lastColNum = 255;
            for (short j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                HSSFCell hssfcell = row.getCell(j);
                if (hssfcell == null)
                    continue;
                GridCell gridcell = grid.getOrCreateCell(i, j);
                switch (hssfcell.getCellType()) {
                case HSSFCell.CELL_TYPE_BLANK:
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    gridcell.setValue(hssfcell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    // TODO: recherche error text
                    byte errorCode = hssfcell.getErrorCellValue();
                    //                    gridcell.setValue(errorCode);
                    gridcell.setValue("#ERROR");
                    errorList.add(new SysException(((GridWM) grid).getMessage("errorRecord",
                            grid.getJeksDelegate().toExcelRef(i, j), Byte.toString(errorCode)))
                                    .setCode(GridWM.CODE_errorRecord));
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    String formula = null;
                    try {
                        formula = hssfcell.getCellFormula();
                        gridcell.setFormula(formula);
                    } catch (SysException e) {
                        if (formula != null)
                            gridcell.setValue("=" + formula); //set it as text without interpretation
                        errorList.add(e);
                    }
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (isDateCell(book, hssfcell)) {
                        gridcell.setValue(hssfcell.getDateCellValue());
                    } else {
                        gridcell.setValue(hssfcell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    gridcell.setValue(hssfcell.getStringCellValue());
                    break;
                default:
                    throw new SysException("unknown cell type " + hssfcell.getCellType());
                }
                gridcell.setEditable(!hssfcell.getCellStyle().getLocked());
                gridcell.setStyle(hssfcell.getCellStyle().getIndex());
            }
        }

        final int scalefactor = 256 / 7; // empirically testet
        //        int width = sheet.getDefaultColumnWidth();  // returns nonsense
        //        width = width/scalefactor;
        //        grid.setDefaultColumnWidth(width);
        for (short i = (short) firstColNum; i <= lastColNum; i++) {
            int width = sheet.getColumnWidth(i);
            width = width / scalefactor;
            grid.getOrCreateColumn(i).setWidth(width);
        }

        if (firstColNum == Integer.MAX_VALUE)
            firstColNum = 0;
        if (lastColNum == Integer.MIN_VALUE)
            lastColNum = 0;
        grid.setMaxRange(
                new GridRange(grid, sheet.getFirstRowNum(), firstColNum, sheet.getLastRowNum(), lastColNum));
        grid.setVisibleRange(grid.getMaxRange());
        return errorList;
    } finally {
        grid.handle(grid.new GridReloadEvent());
    }
}

---

From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java

License:Open Source License

/**
 * Calculates the alignement of each column of the given grid.
 * In Excel alignements are defined per cell, in SWT alignement are defined per column.
 * So the alignment for SWT is calculated by choosing the most used alignement of the visible
 * cells of each column./* w w  w  . j  av  a 2s .co  m*/
 *
 * @param igrid the XMA model where to set the alignemnets
 * @param sheet the POI representation from where to read the alignements
 */
static public void calcAlignements(IGridWM igrid, HSSFSheet sheet) {
    GridWM grid = (GridWM) igrid;
    GridRange range = grid.getVisibleRange();
    for (int col = range.getFirstColumn(), lastCol = range.getLastColumn(); col <= lastCol; col++) {
        GridColumn gridColumn = grid.getOrCreateColumn(col);
        if (gridColumn.isHidden())
            continue;
        int left = 0, right = 0, center = 0;
        for (int i = range.getFirstRow(), lastRow = range.getLastRow(); i <= lastRow; i++) {
            GridRow gridRow = grid.getRow(i);
            if (gridRow != null && gridRow.isHidden())
                continue;
            HSSFRow hrow = sheet.getRow(i);
            if (hrow == null)
                continue;
            HSSFCell hcell = hrow.getCell((short) col);
            if (hcell == null)
                continue;
            HSSFCellStyle hstyle = hcell.getCellStyle();
            if (hstyle == null)
                continue;
            switch (hstyle.getAlignment()) {
            case HSSFCellStyle.ALIGN_CENTER:
            case HSSFCellStyle.ALIGN_CENTER_SELECTION:
                center++;
                break;
            case HSSFCellStyle.ALIGN_LEFT:
            case HSSFCellStyle.ALIGN_FILL:
            case HSSFCellStyle.ALIGN_JUSTIFY:
                left++;
                break;
            case HSSFCellStyle.ALIGN_RIGHT:
                right++;
                break;
            case HSSFCellStyle.ALIGN_GENERAL:
                switch (hcell.getCellType()) {
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    center++;
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                case 42: // CELL_TYPE_DATE:
                    right++;
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    left++;
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                case HSSFCell.CELL_TYPE_ERROR:
                case HSSFCell.CELL_TYPE_FORMULA:
                default:
                    break;
                }
                break;
            default:
                break;
            }
        }
        if (left >= right && left >= center) {
            gridColumn.setAlignement(GridColumn.ALIGN_LEFT);
        } else if (right > left && right >= center) {
            gridColumn.setAlignement(GridColumn.ALIGN_RIGHT);
        } else if (center > left && center > right) {
            gridColumn.setAlignement(GridColumn.ALIGN_CENTER);
        }
    }
}

---

From source file:be.vds.jtbdive.client.view.core.dive.profile.DiveProfileExcelParser.java

License:Open Source License

public DiveProfile read(File file) throws IOException {
    DiveProfile dp = new DiveProfile();
    Map<Double, Double> depthEntries = new HashMap<Double, Double>();
    Set<Double> ascentWarnings = new HashSet<Double>();
    Set<Double> decoWarnings = new HashSet<Double>();
    Set<Double> remainingBottomTimeWarnings = new HashSet<Double>();
    Set<Double> decoEntries = new HashSet<Double>();

    HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
    HSSFSheet sheet = wb.getSheetAt(0);

    int rowMax = sheet.getLastRowNum();
    for (int i = 1; i <= rowMax; i++) {
        HSSFRow row = sheet.getRow(i);
        double second = row.getCell(0).getNumericCellValue();
        depthEntries.put(second, row.getCell(1).getNumericCellValue());

        boolean b = row.getCell(2).getBooleanCellValue();
        if (b)//from   w  w w . jav a  2 s .  co  m
            ascentWarnings.add(second);

        b = row.getCell(3).getBooleanCellValue();
        if (b)
            remainingBottomTimeWarnings.add(second);

        b = row.getCell(4).getBooleanCellValue();
        if (b)
            decoWarnings.add(second);

        b = row.getCell(5).getBooleanCellValue();
        if (b)
            decoEntries.add(second);
    }

    dp.setDepthEntries(depthEntries);

    if (ascentWarnings.size() > 0)
        dp.setAscentWarnings(ascentWarnings);

    if (decoWarnings.size() > 0)
        dp.setDecoCeilingWarnings(decoWarnings);

    if (remainingBottomTimeWarnings.size() > 0)
        dp.setRemainingBottomTimeWarnings(remainingBottomTimeWarnings);

    if (decoEntries.size() > 0)
        dp.setDecoEntries(decoEntries);

    return dp;
}

---

From source file:Beans.GeminusCompen.java

public void postProcessXLS(Object document) throws IOException {
    HSSFWorkbook wb = (HSSFWorkbook) document;

    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow header = sheet.getRow(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);

        cell.setCellStyle(cellStyle);//  w  w w  . j  a v  a  2s  .c om
    }
}

---

From source file:br.com.nfsconsultoria.azcontrole.bean.VendaBean.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow header = sheet.getRow(0);

    HSSFFont font = wb.createFont();/*from   w  w  w  .  ja va 2 s  .  c  om*/
    font.setBold(true);
    font.setColor(HSSFColor.WHITE.index);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    cellStyle.setWrapText(true);
    cellStyle.setAlignment(CellStyle.ALIGN_JUSTIFY);
    cellStyle.setFont(font);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);

        cell.setCellStyle(cellStyle);
    }
}

---

From source file:br.com.primetestes.TableBean.java

License:Apache License

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow header = sheet.getRow(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);

        cell.setCellStyle(cellStyle);//from  w  ww .ja  v  a2s .  co  m
        //TODO fucking fix that HSSCell class pls
    }
}

---

From source file:br.luck.managerbean.PrincipalBean.java

/***
 * Exporta para arquivo xls//ww w .jav  a 2 s.  c  o m
 * @param document 
 */
public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow header = sheet.getRow(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);

        cell.setCellStyle(cellStyle);
    }
}

---

From source file:br.rio.puc.inf.les.controller.ApartamentoBean.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow header = sheet.getRow(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);

        cell.setCellStyle(cellStyle);//  w ww  .j a  v a 2s.  co  m
    }
}

---

From source file:br.solutio.licita.controlador.ControladorPregao.java

public void editandoXlsParaExportar(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet planilha = wb.getSheetAt(0);

    //Move as celulas selecionadas para baixo de acordo com o valor informado
    planilha.shiftRows(planilha.getFirstRowNum(), planilha.getLastRowNum(), 5);

    HSSFRow linha0 = planilha.createRow(0);
    linha0.createCell(0).setCellValue("Instituio Licitadora:");
    planilha.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
    linha0.createCell(2)/*from  www  .j a  v a 2s.co m*/
            .setCellValue(" " + getEntidade().getInstituicaoLicitadora().getPessoaJuridica().getNomeFantasia());
    planilha.addMergedRegion(new CellRangeAddress(0, 0, 2, 6));

    HSSFRow linha1 = planilha.createRow(1);
    linha1.createCell(0).setCellValue("Numero do Pregao:");
    planilha.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
    linha1.createCell(2).setCellValue(" " + getEntidade().getNumeroPregao());
    planilha.addMergedRegion(new CellRangeAddress(1, 1, 2, 6));

    HSSFRow linha2 = planilha.createRow(2);
    linha2.createCell(0).setCellValue("Numero do Processo:");
    planilha.addMergedRegion(new CellRangeAddress(2, 2, 0, 1));
    linha2.createCell(2).setCellValue(" " + getEntidade().getNumeroProcesso());
    planilha.addMergedRegion(new CellRangeAddress(2, 2, 2, 6));

    HSSFRow linha3 = planilha.createRow(3);
    linha3.createCell(0).setCellValue("Empresa Licitante:");
    planilha.addMergedRegion(new CellRangeAddress(3, 3, 0, 1));
    linha3.createCell(2).setCellValue("Preencha com o nome de sua Empresa");
    planilha.addMergedRegion(new CellRangeAddress(3, 3, 2, 6));

    HSSFRow linha4 = planilha.createRow(4);

    //Nova coluna para a empresas adicionarem seus valores
    HSSFRow linha5 = planilha.getRow(5);
    HSSFCell celula5 = linha5.createCell(5);
    celula5.setCellValue("Valor do Licitante");

    //for para ajustar automaticamente o tamnho das colunas
    for (int i = 0; i < 6; i++) {
        planilha.autoSizeColumn(i);
    }

    //Cor da linha de titulos da tabela
    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    for (int i = 0; i < linha5.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = linha5.getCell(i);

        cell.setCellStyle(cellStyle);
    }

    CellStyle unlockedCellStyle = wb.createCellStyle();
    unlockedCellStyle.setLocked(false);

    HSSFCell celula2 = linha3.getCell(2);
    celula2.setCellStyle(unlockedCellStyle);

}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »