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

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. 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:mx.avanti.siract.ui.FiltrosBeanUI.java

public HSSFSheet setExDat(HSSFSheet sheet, int prow, int pcol, int valor) {
    if (sheet.getRow(prow) != null) {
        HSSFRow row = sheet.getRow(prow);
        HSSFCell cell = row.createCell(pcol);
        cell.setCellValue(valor);/*from  w ww . ja  va  2  s  . c o  m*/
    } else {
        HSSFRow row = sheet.createRow(prow);
        HSSFCell cell = row.createCell(pcol);
        cell.setCellValue(valor);
    }
    return sheet;
}

From source file:mx.avanti.siract.ui.FiltrosBeanUI.java

public HSSFSheet setExDat(HSSFSheet sheet, int prow, int pcol, float valor) {
    if (sheet.getRow(prow) != null) {
        HSSFRow row = sheet.getRow(prow);
        HSSFCell cell = row.createCell(pcol);
        cell.setCellValue(valor);/*from   ww w .  ja  v  a 2 s.c  o  m*/
    } else {
        HSSFRow row = sheet.createRow(prow);
        HSSFCell cell = row.createCell(pcol);
        cell.setCellValue(valor);
    }
    return sheet;
}

From source file:mx.avanti.siract.ui.FiltrosBeanUI.java

public HSSFSheet setStyleCell(HSSFSheet sheet, HSSFCellStyle style, int prow, int pcol) {
    if (sheet.getRow(prow) != null) {
        HSSFRow row = sheet.getRow(prow);
        if (row.getCell(pcol) != null) {
            HSSFCell cell = row.getCell(pcol);
            cell.setCellStyle(style);//from   w  ww  .ja  v a2 s .  co  m
        }
    }
    return sheet;
}

From source file:mx.dr.util.report.impl.PoiService.java

License:Open Source License

/**
 * @see mx.dr.util.report.IPoiService#doReport(InputStream, Object, OutputStream)
 */// w  w w. j  a  va2 s .  co  m
public void doReport(InputStream is, Object dto, OutputStream out) throws Exception {

    POIFSFileSystem fs = null;
    HSSFWorkbook wb = null;

    fs = new POIFSFileSystem(is);

    wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    DRCoordinateReport anotCoordenada;
    DRCoordinateLabelReport anotEtiqueta;
    DRRelativeCoordinateReport anotRelativa;
    Object valor;
    Object valor2;

    HSSFRow row;
    HSSFCell cerda;
    int offset;

    CellDTO celdaDto;

    for (Method method : dto.getClass().getMethods()) {
        anotEtiqueta = method.getAnnotation(DRCoordinateLabelReport.class);
        if (anotEtiqueta != null) {
            dto.getClass().getMethod("set" + method.getName().substring(3), CellDTO.class).invoke(dto,
                    new CellDTO(sheet.getRow(anotEtiqueta.y()).getCell(anotEtiqueta.x()).getCellStyle(),
                            sheet.getRow(anotEtiqueta.y()).getCell(anotEtiqueta.x()).getRichStringCellValue()));
        }
    }

    for (Method method : dto.getClass().getMethods()) {
        anotCoordenada = method.getAnnotation(DRCoordinateReport.class);
        if (anotCoordenada != null) {
            valor = method.invoke(dto);
            if (valor != null) {
                cerda = sheet.getRow(anotCoordenada.y()).getCell(anotCoordenada.x());
                ingresaValor(cerda, valor);
                if (valor instanceof List) {
                    offset = anotCoordenada.y();
                    int index = 0;
                    while (index < ((List) valor).size()) {
                        Object detalle = ((List) valor).get(index);
                        row = sheet.createRow(index + offset);
                        for (Method methodMan : detalle.getClass().getMethods()) {
                            anotCoordenada = methodMan.getAnnotation(DRCoordinateReport.class);
                            if (anotCoordenada != null) {
                                cerda = row.createCell(anotCoordenada.x());
                                valor2 = methodMan.invoke(detalle);
                                if (valor2 != null) {
                                    ingresaValor(cerda, valor2);
                                }
                            }
                        }
                        index++;
                    }
                    index -= 1;
                    for (Method m : dto.getClass().getMethods()) {
                        anotEtiqueta = m.getAnnotation(DRCoordinateLabelReport.class);
                        anotRelativa = m.getAnnotation(DRRelativeCoordinateReport.class);
                        if (anotEtiqueta != null) {
                            row = sheet.getRow(anotEtiqueta.y() + index);
                            if (row == null) {
                                row = sheet.createRow(anotEtiqueta.y() + index);
                            }
                            cerda = row.createCell(anotEtiqueta.x());
                            celdaDto = (CellDTO) m.invoke(dto);
                            cerda.setCellStyle(celdaDto.getStyle());
                            cerda.setCellValue(celdaDto.getLabel());
                        } else if (anotRelativa != null) {
                            row = sheet.getRow(anotRelativa.y() + index);
                            if (row == null) {
                                row = sheet.createRow(anotRelativa.y() + index);
                            }
                            cerda = row.createCell(anotRelativa.x());
                            valor2 = m.invoke(dto);
                            if (valor2 != null) {
                                ingresaValor(cerda, valor2);
                            }
                        }
                    }
                }
            }
        }
    }
    //String res = "/mailConfig.properties";
    //tempPath=PoiService.class.getResource(res).getPath().replaceFirst(res, "")
    //        + "/../../" + Labels.getLabel("parametro.adjuntos.folder") + "/" + archivo;
    //FileUtils.writeToFile(archivo,new ByteArrayInputStream(doc.getDataContent()));

    //tempPath = this.getPath(TEMP_DIR)+"\\"+archivo;
    wb.write(out);//new FileOutputStream(tempPath ,false));

}

From source file:net.chaosserver.timelord.data.ExcelDataReaderWriter.java

License:Open Source License

/**
 * Generates the actual workbook of data.
 *
 * @param timelordData the data to generate a workbook for
 * @return the workbook//  w  w w  .j av a 2s.  c o  m
 */
protected HSSFWorkbook generateWorkbook(TimelordData timelordData) {
    HSSFWorkbook wb = new HSSFWorkbook();

    // Build the Map of the Styles that will be applied to cells
    // in the workbook
    Map<String, HSSFCellStyle> styleMap = buildStyleMap(wb);
    Map<String, List<String>> sheetToNotes = new TreeMap<String, List<String>>(new DateComparator());

    // Since there is an issue re-ordering sheets after they
    // have been created.  First create the book with all needed
    // sheets
    preCreateAllSheets(wb, timelordData, sheetToNotes, styleMap);

    // After all the sheets have been pre-created, iterate through all
    // the tasks to add them into the sheets.
    int rowNum = addAllTasks(wb, timelordData, sheetToNotes, styleMap);

    // This section applies all the styles, creates the footers and adds
    // the notes onto the sheet.
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        HSSFSheet sheet = wb.getSheetAt(i);
        String sheetName = wb.getSheetName(i);
        createFooterRows(sheet, rowNum, styleMap);

        // This will apply styles to the rows that had no task associated
        // for a given week.
        for (int j = 1; j < rowNum - 1; j++) {
            HSSFRow row = sheet.getRow(j);
            if (row == null) {
                row = sheet.createRow(j);
                row.setHeight((short) 0);
                HSSFCell cell = row.createCell((short) 0);
                cell.setCellStyle((HSSFCellStyle) styleMap.get("taskNameStyle"));
                cell.setCellValue("");

                cell = row.createCell(MAX_COLUMN);
                cell.setCellStyle((HSSFCellStyle) styleMap.get("totalColumnStyle"));
                cell.setCellFormula("SUM(B" + (j + 1) + ":H" + (j + 1) + ")");
            }
        }

        List<String> noteList = sheetToNotes.get(sheetName);
        createNotesRows(sheet, noteList);

        HSSFPrintSetup ps = sheet.getPrintSetup();
        ps.setLandscape(true);
    }

    // Finally order the sheets properly
    if (logger.isDebugEnabled()) {
        logger.debug("Re-ordering sheets under final order.");
    }

    return wb;
}

From source file:net.chaosserver.timelord.data.ExcelDataReaderWriter.java

License:Open Source License

/**
 * Adds all of the tasks to sheets that have already been created inside
 * the workbook and adds any notes associated with the tasks to the
 * sheetToNotes map.//  w w  w. j av a2s .c o  m
 *
 * @param workbook the workbook to create sheets on
 * @param timelordData the timelord data used for the sheets.
 * @param sheetToNotes the map of sheets to the notes associated with it
 * @param styleMap the map of styles
 *
 * @return the total rows added to Excel after adding all the tasks
 */
protected int addAllTasks(HSSFWorkbook workbook, TimelordData timelordData,
        Map<String, List<String>> sheetToNotes, Map<String, HSSFCellStyle> styleMap) {

    // Start on row number two.  The first row contains the header data.
    int rowNum = 2;

    List<TimelordTask> taskCollection = timelordData.getTaskCollection();
    Iterator<TimelordTask> taskIterator = taskCollection.iterator();
    while (taskIterator.hasNext()) {
        TimelordTask timelordTask = (TimelordTask) taskIterator.next();
        if (timelordTask.isExportable()) {
            if (logger.isDebugEnabled()) {
                logger.debug("Processing exportable task named [" + timelordTask.getTaskName() + "]");
            }

            String taskName = timelordTask.getTaskName();
            List<TimelordTaskDay> taskDayList = timelordTask.getTaskDayList();

            Iterator<TimelordTaskDay> taskDayIterator = taskDayList.iterator();

            while (taskDayIterator.hasNext()) {
                TimelordTaskDay timelordTaskDay = (TimelordTaskDay) taskDayIterator.next();

                Date timelordDate = timelordTaskDay.getDate();
                double hours = timelordTaskDay.getHours();

                if (logger.isDebugEnabled()) {
                    logger.debug("Processing task named [" + timelordTask.getTaskName() + "] for date ["
                            + timelordDate + "] with hours [" + hours + "]");
                }

                if (hours > 0) {
                    Date weekStartDate = convertToWeekStart(timelordDate);
                    String sheetName = sheetNameFormat.format(weekStartDate);

                    HSSFSheet sheet = workbook.getSheet(sheetName);
                    if (sheet == null) {
                        throw new NullPointerException(
                                "Failed to find " + "sheet with name [" + sheetName + "]");
                    }
                    List<String> noteList = sheetToNotes.get(sheetName);

                    HSSFRow row = sheet.getRow(rowNum);
                    if (row == null) {
                        row = sheet.createRow(rowNum);

                        // First create the left column "header" with the
                        // name of the task on column 0.
                        HSSFCell cell = row.createCell((short) 0);
                        cell.setCellStyle((HSSFCellStyle) styleMap.get("taskNameStyle"));
                        cell.setCellValue(taskName);

                        // Over in the far right column create the sum
                        // column
                        cell = row.createCell(MAX_COLUMN);
                        cell.setCellStyle((HSSFCellStyle) styleMap.get("totalColumnStyle"));
                        cell.setCellFormula("SUM(B" + (rowNum + 1) + ":H" + (rowNum + 1) + ")");
                    }

                    // Process the task day and add the hours into the
                    // given row.
                    addTaskDay(row, taskName, timelordTaskDay, noteList, styleMap);
                }
            }
            rowNum++;
        } else {
            if (logger.isDebugEnabled()) {
                logger.debug("Skipping non-exportable task named [" + timelordTask.getTaskName() + "]");
            }
        }
    }

    return rowNum;
}

From source file:net.intelliant.util.UtilImport.java

License:Open Source License

public static List<String> readExcelFirstRow(String excelFilePath, boolean isFirstRowHeader, int sheetIndex)
        throws FileNotFoundException, IOException {
    List<String> columnIndices = new ArrayList<String>();
    File file = new File(excelFilePath);
    if (file != null && file.canRead()) {
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        // HSSFSheet sheet = wb.getSheet(wb.getActiveSheetIndex());
        HSSFSheet sheet = wb.getSheetAt(sheetIndex);
        if (sheet != null) {
            HSSFRow firstRow = sheet.getRow(sheet.getFirstRowNum());
            if (firstRow != null) {
                firstRow.getPhysicalNumberOfCells();
                Iterator<?> cells = firstRow.cellIterator();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    if (isFirstRowHeader) {
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            columnIndices.add(cell.toString());
                        } else {
                            columnIndices.add("N/A - " + cell.getCellNum());
                        }// ww  w  .  j  a  v  a 2 s.  co  m
                    } else {
                        columnIndices.add(String.valueOf(cell.getCellNum()));
                    }
                }
            }
        }
    }
    return columnIndices;
}

From source file:net.openchrom.xxd.processor.supplier.rscripting.ui.jobs.LoadExcelJob.java

License:Open Source License

private void loadExcel(final String file) {

    final File fil = new File(file);
    if (fil.exists()) {
        canRead = true;//w  w w  .  jav  a2  s .  c  om
        if (grid != null) {
            try {
                InputStream inp = new FileInputStream(file);
                try {
                    wb = new HSSFWorkbook(inp);
                } catch (Exception e) {
                    MsgDialog.message("Wrong format!\nOnly Excel 97-2007 is supported!");
                    canRead = false;
                }
            } catch (IOException ex) {
                ex.printStackTrace();
            }
            if (canRead) {
                for (s = 0; s < wb.getNumberOfSheets(); s++) {
                    Display display = PlatformUI.getWorkbench().getDisplay();
                    display.syncExec(new Runnable() {

                        public void run() {

                            String name = fil.getName();
                            grid = new Spread().spread(SampleView.getTabFolder(), 0, 0, name);
                            SampleView.setGrid(grid);
                            HSSFSheet sheet = wb.getSheetAt(s);
                            int colCount = grid.getColumnCount();
                            int rowCount = grid.getItemCount();
                            int exelRow = endOfRow(sheet);
                            int exelColumn = endOfColumn(sheet);
                            // System.out.println(exelRow + " " + exelColumn
                            // + "---" + sheet.getPhysicalNumberOfRows() +
                            // " " +
                            // sheet.getRow(0).getPhysicalNumberOfCells());
                            if (colCount < exelColumn) {
                                int diff = exelColumn - colCount;
                                for (int i = 0; i < diff; i++) {
                                    GridColumn column = new GridColumn(grid, SWT.NONE);
                                    column.setText("C " + (i + 1 + colCount));
                                    column.setWidth(50);
                                }
                            }
                            if (rowCount < exelRow) {
                                int diff = exelRow - rowCount;
                                for (int i = 0; i < diff; i++) {
                                    new GridItem(grid, SWT.NONE).setHeight(16);
                                }
                            }
                            // Iterate over each row in the sheet
                            int rows = sheet.getPhysicalNumberOfRows();
                            for (int i = 0; i < exelRow; i++) {
                                HSSFRow row = sheet.getRow(i);
                                if (row == null) {
                                    for (int u = 0; u < exelColumn; u++) {
                                        grid.getItem(i).setText(u, " ");
                                    }
                                } else {
                                    for (int u = 0; u < exelColumn; u++) {
                                        HSSFCell cell = row.getCell(u);
                                        if (cell != null) {
                                            switch (cell.getCellType()) {
                                            case HSSFCell.CELL_TYPE_NUMERIC:
                                                String val = String.valueOf(cell.getNumericCellValue());
                                                grid.getItem(i).setText(u, val);
                                                break;
                                            case HSSFCell.CELL_TYPE_STRING:
                                                HSSFRichTextString st = cell.getRichStringCellValue();
                                                String val2 = st.getString();
                                                grid.getItem(i).setText(u, val2);
                                                break;
                                            case HSSFCell.CELL_TYPE_FORMULA:
                                                try {
                                                    String val3 = String.valueOf(cell.getNumericCellValue());
                                                    grid.getItem(i).setText(u, val3);
                                                } catch (Exception e) {
                                                    String s2 = cell.getCellFormula();
                                                    grid.getItem(i).setText(u, s2);
                                                }
                                                break;
                                            case HSSFCell.CELL_TYPE_BLANK:
                                                grid.getItem(i).setText(u, " ");
                                                break;
                                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                                boolean s4 = cell.getBooleanCellValue();
                                                if (s4) {
                                                    grid.getItem(i).setText(u, "TRUE");
                                                } else {
                                                    grid.getItem(i).setText(u, "FALSE");
                                                }
                                                break;
                                            default:
                                                break;
                                            }
                                        } else {
                                            grid.getItem(i).setText(u, " ");
                                        }
                                    }
                                }
                            }
                        }
                    });
                }
                wb = null;
            }
        }
    } else {
        MsgDialog.message("File not found!");
    }
}

From source file:net.openchrom.xxd.processor.supplier.rscripting.ui.jobs.LoadExcelJob.java

License:Open Source License

public static int endOfColumn(HSSFSheet sheet) {

    int rowCount = endOfRow(sheet);
    int maxCellNum = 0;
    for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
        HSSFRow row = sheet.getRow(rowIndex);
        if (row != null) {
            maxCellNum = Math.max(maxCellNum, row.getLastCellNum());
        }/*from   w  w w  .jav  a  2 s  . co  m*/
    }
    return maxCellNum;
}

From source file:net.sourceforge.fenixedu.domain.phd.reports.PhdReport.java

License:Open Source License

protected void addHeaderCell(HSSFSheet sheet, String value, int columnNumber) {
    HSSFRow row = sheet.getRow(0);
    if (row == null) {
        row = sheet.createRow(0);/*w ww. jav a 2 s . co m*/
    }

    HSSFCell cell = row.createCell(columnNumber);

    cell.setCellValue(value);
    cell.setCellStyle(headerStyle);

    cell.setCellValue(value);

    sheet.addMergedRegion(new CellRangeAddress(0, 1, columnNumber, columnNumber));
}