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:cn.baiweigang.qtaf.toolkit.dbunit.data.excel.XlsTable.java

License:Open Source License

public XlsTable(HSSFWorkbook workbook, String sheetName, HSSFSheet sheet) throws DataSetException {
    int rowCount = sheet.getLastRowNum();
    if (rowCount >= 0 && sheet.getRow(0) != null) {
        _metaData = createMetaData(sheetName, sheet.getRow(0), workbook);
    } else {/*  w ww  . ja v a2s .  c  om*/
        _metaData = new DefaultTableMetaData(sheetName, new Column[0]);
    }

    _sheet = sheet;

    // Needed for later "BigDecimal"/"Number" conversion
    symbols.setDecimalSeparator('.');
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static List getMonthInfo(int index, HSSFRow row, HSSFSheet templateSheet) {
    List months = new ArrayList();
    row = templateSheet.getRow(index - 1);
    for (int j = 3; j < 1000; j++) {
        HSSFCell monthCell = row.getCell((short) j);
        if (monthCell == null) {
            if (row.getCell((short) (j + 1)) == null) {
                break;
            }//  w ww .  j  a v  a2s . c om
        } else {
            String monthValue = monthCell.getStringCellValue();
            if (monthValue != null && !monthValue.equals("")) {
                months.add(monthCell.getStringCellValue());
            }
        }
    }
    return months;
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static void writeSubActivityTime() {
    Map userHours = getHoursInfo();
    HSSFSheet templateSheet = templateWbk.getSheet("Individual Time Usage");
    String lastSubActivityName = null;
    List months = new ArrayList();
    int count = 0;
    for (int i = 5; i < 10000; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            }//w w  w  .j  a  va  2 s  .c o  m
        }
        HSSFCell cell = row.getCell((short) 4);
        HSSFCell userNameCell = row.getCell((short) 2);
        if (cell != null) {
            String userName = userNameCell.getStringCellValue();
            if ("Month".equals(userName.trim()) || "User Name".equals(userName.trim())) {
                continue;
            }
            if ("Normal Working day of a Month".equals(userName.trim())) {
                months = getMonthInfo(i, row, templateSheet);
                continue;
            }
            if (lastSubActivityName == null) {
                lastSubActivityName = cell.getStringCellValue();
                count = 1;
            } else {
                String newSubActivityName = cell.getStringCellValue();
                if (newSubActivityName != null) {
                    if (newSubActivityName.equals(lastSubActivityName)) {
                        count++;
                        for (int j = 4, k = 0; j < 1000;) {
                            HSSFCell detailCell = row.getCell((short) (j));
                            if (detailCell == null) {
                                if (row.getCell((short) (j + 1)) == null) {
                                    break;
                                }
                            } else {
                                percentageStyle.setFont(templateSheet.getRow(i - 1).getCell((short) (j + 5))
                                        .getCellStyle().getFont(templateWbk));
                                HSSFCell precOfType = row.getCell((short) (j + 2));
                                HSSFCell precOfSubActivity = row.getCell((short) (j + 3));
                                //                                    String prec = precOfType.getStringCellValue().trim();
                                double prec = precOfType.getNumericCellValue();
                                String key = userName + "." + months.get(k);
                                TimeUsage timeUsage = (TimeUsage) userHours.get(key);

                                Region region = new Region();
                                region.setRowTo(i);
                                region.setRowFrom(i - 1);
                                region.setColumnFrom((short) (j));
                                region.setColumnTo((short) (j));
                                templateSheet.addMergedRegion(region);
                                templateSheet.getRow(i - 1).getCell((short) j).getCellStyle()
                                        .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

                                region = new Region();
                                region.setRowTo(i);
                                region.setRowFrom(i - 1);
                                region.setColumnFrom((short) (j + 3));
                                region.setColumnTo((short) (j + 3));
                                templateSheet.addMergedRegion(region);
                                templateSheet.getRow(i - 1).getCell((short) (j + 3)).getCellStyle()
                                        .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                                if (prec > 0 && timeUsage.getPercentage() > 0) {
                                    templateSheet.getRow(i - count + 1).getCell((short) (j + 3))
                                            .setCellStyle(percentageStyle);
                                    double oldValue = templateSheet.getRow(i - count + 1)
                                            .getCell((short) (j + 3)).getNumericCellValue();
                                    templateSheet.getRow(i - count + 1).getCell((short) (j + 3))
                                            .setCellValue(oldValue + prec);
                                }
                                j += 6;
                                k++;
                                continue;
                            }
                            j++;
                        }
                    } else {
                        lastSubActivityName = newSubActivityName;
                        writePrecOfSubActivity(templateSheet, months, userName, userHours, row, i);
                        count = 1;
                    }
                } else {
                    lastSubActivityName = newSubActivityName;
                    count = 1;
                }
            }
        }
    }
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static void writeIndividualTime() {
    Map userHours = getHoursInfo();
    HSSFSheet templateSheet = templateWbk.getSheet("Individual Time Usage");
    String lastUserName = null;//from w ww .j a  v  a 2 s  .  co m
    List months = new ArrayList();
    for (int i = 5; i < 10000; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            }
        }
        HSSFCell cell = row.getCell((short) 2);
        if (cell != null) {
            if (lastUserName == null) {
                lastUserName = cell.getStringCellValue();
            } else {
                String newUserName = cell.getStringCellValue();
                if ("Month".equals(newUserName.trim()) || "User Name".equals(newUserName.trim())) {
                    continue;
                }
                if ("Normal Working day of a Month".equals(newUserName.trim())) {
                    months = new ArrayList();
                    row = templateSheet.getRow(i - 1);
                    for (int j = 3; j < 1000; j++) {
                        HSSFCell monthCell = row.getCell((short) j);
                        if (monthCell == null) {
                            if (row.getCell((short) (j + 1)) == null) {
                                break;
                            }
                        } else {
                            String monthValue = monthCell.getStringCellValue();
                            if (monthValue != null && !monthValue.equals("")) {
                                months.add(monthCell.getStringCellValue());
                            }
                        }
                    }
                    continue;
                }
                if (newUserName != null) {
                    if (newUserName.equals(lastUserName)) {
                        Region region = new Region();
                        region.setRowTo(i);
                        region.setRowFrom(i - 1);
                        region.setColumnFrom((short) 2);
                        region.setColumnTo((short) 2);
                        templateSheet.addMergedRegion(region);
                        templateSheet.getRow(i - 1).getCell((short) 2).getCellStyle()
                                .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

                        HSSFCell activityCell = row.getCell((short) 3);
                        HSSFCell oldActivityCell = templateSheet.getRow(i - 1).getCell((short) 3);
                        if (activityCell.getStringCellValue().equals(oldActivityCell.getStringCellValue())) {
                            region = new Region();
                            region.setRowTo(i);
                            region.setRowFrom(i - 1);
                            region.setColumnFrom((short) 3);
                            region.setColumnTo((short) 3);
                            templateSheet.addMergedRegion(region);
                            templateSheet.getRow(i - 1).getCell((short) 3).getCellStyle()
                                    .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                        }

                        for (int j = 4, k = 0; j < 1000;) {
                            HSSFCell detailCell = row.getCell((short) (j));
                            if (detailCell == null) {
                                if (row.getCell((short) (j + 1)) == null) {
                                    break;
                                }
                            } else {
                                percentageStyle.setFont(templateSheet.getRow(i - 1).getCell((short) (j + 5))
                                        .getCellStyle().getFont(templateWbk));
                                HSSFCell precOfType = row.getCell((short) (j + 2));
                                String prec = precOfType.getStringCellValue().trim();
                                String key = newUserName + "." + months.get(k);
                                TimeUsage timeUsage = (TimeUsage) userHours.get(key);
                                if (!prec.equals("")) {
                                    if (timeUsage.getHours() > 0) {
                                        precOfType.setCellStyle(percentageStyle);
                                        precOfType.setCellValue(
                                                (new BigDecimal(prec)).doubleValue() / timeUsage.getHours());
                                    }
                                }

                                region = new Region();
                                region.setRowTo(i);
                                region.setRowFrom(i - 1);
                                region.setColumnFrom((short) (j + 4));
                                region.setColumnTo((short) (j + 4));
                                templateSheet.addMergedRegion(region);
                                if (timeUsage.getHours() > 0) {
                                    templateSheet.getRow(i - 1).getCell((short) (j + 4))
                                            .setCellValue(timeUsage.getHours());
                                }

                                region = new Region();
                                region.setRowTo(i);
                                region.setRowFrom(i - 1);
                                region.setColumnFrom((short) (j + 5));
                                region.setColumnTo((short) (j + 5));
                                templateSheet.addMergedRegion(region);
                                if (timeUsage.getPercentage() > 0) {
                                    templateSheet.getRow(i - 1).getCell((short) (j + 5))
                                            .setCellStyle(percentageStyle);
                                    templateSheet.getRow(i - 1).getCell((short) (j + 5))
                                            .setCellValue(timeUsage.getPercentage());
                                }
                                j += 6;
                                k++;
                                continue;
                            }
                            j++;
                        }
                    } else {
                        lastUserName = newUserName;
                        writePrec(templateSheet, months, newUserName, userHours, row, i);
                    }
                } else {
                    lastUserName = newUserName;
                }
            }
        }
    }
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static void writePrecOfSubActivity(HSSFSheet templateSheet, List months, String newUserName,
        Map userHours, HSSFRow row, int rowIndex) {
    for (int j = 4, k = 0; j < 1000;) {
        HSSFCell detailCell = row.getCell((short) (j));
        if (detailCell == null) {
            if (row.getCell((short) (j + 1)) == null) {
                break;
            }/*from   ww  w  .  j  ava 2s  .c  o  m*/
        } else {
            percentageStyle.setFont(templateSheet.getRow(rowIndex - 1).getCell((short) (j + 5)).getCellStyle()
                    .getFont(templateWbk));
            HSSFCell precOfType = row.getCell((short) (j + 2));
            HSSFCell precOfSubActivity = row.getCell((short) (j + 3));
            try {
                double prec = precOfType.getNumericCellValue();
                String key = newUserName + "." + months.get(k);
                TimeUsage timeUsage = (TimeUsage) userHours.get(key);
                if (prec > 0) {
                    if (timeUsage.getHours() > 0) {
                        precOfSubActivity.setCellStyle(percentageStyle);
                        precOfSubActivity.setCellValue(prec);
                    }
                }
            } catch (Exception e) {
            }
            j += 6;
            k++;
        }
    }
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static void writePrec(HSSFSheet templateSheet, List months, String newUserName, Map userHours,
        HSSFRow row, int rowIndex) {
    for (int j = 4, k = 0; j < 1000;) {
        HSSFCell detailCell = row.getCell((short) (j));
        if (detailCell == null) {
            if (row.getCell((short) (j + 1)) == null) {
                break;
            }//from   w  w w  .ja  va2s.  com
        } else {
            percentageStyle.setFont(templateSheet.getRow(rowIndex - 1).getCell((short) (j + 5)).getCellStyle()
                    .getFont(templateWbk));
            HSSFCell precOfType = row.getCell((short) (j + 2));
            String prec = precOfType.getStringCellValue().trim();
            String key = newUserName + "." + months.get(k);
            TimeUsage timeUsage = (TimeUsage) userHours.get(key);
            if (!prec.equals("")) {
                if (timeUsage.getHours() > 0) {
                    precOfType.setCellStyle(percentageStyle);
                    precOfType.setCellValue((new BigDecimal(prec)).doubleValue() / timeUsage.getHours());
                }
            }
            j += 6;
            k++;
        }
    }
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static Map getHoursInfo() {
    HSSFSheet templateSheet = templateWbk.getSheet("Individual Time Usage (1)");
    boolean fillMap = false;
    Map userHours = new HashMap();
    List months = new ArrayList();
    for (int i = 4; i < 10000; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            } else {
                fillMap = false;//from   w w  w.j ava  2 s.  c  om
            }
        } else {
            HSSFCell userNameCell = row.getCell((short) 2);
            if (userNameCell == null) {
                continue;
            }
            String userNameCellValue = userNameCell.getStringCellValue();
            if ("Month".equals(userNameCellValue.trim())) {
                months = new ArrayList();
                for (int j = 3; j < 1000; j++) {
                    HSSFCell monthCell = row.getCell((short) j);
                    if (monthCell == null) {
                        if (row.getCell((short) (j + 1)) == null) {
                            break;
                        }
                    } else {
                        String monthValue = monthCell.getStringCellValue();
                        if (monthValue != null && !monthValue.equals("")) {
                            months.add(monthCell.getStringCellValue());
                        }
                    }
                }
                continue;
            }
            if ("Normal Working day of a Month".equals(userNameCellValue.trim())) {
                continue;
            }
            if ("User Name".equals(userNameCellValue)) {
                fillMap = true;
            } else if (fillMap) {
                for (int j = 3, k = 0; j < 1000; j++) {
                    HSSFCell detailCell = row.getCell((short) j);
                    if (detailCell == null) {
                        break;
                    } else {
                        if (j % 2 == 1) {
                            TimeUsage timeUsage = new TimeUsage();
                            String hoursStr = detailCell.getStringCellValue().trim();
                            String month = (String) months.get(k);
                            userHours.put(userNameCellValue + "." + month, timeUsage);
                            if (hoursStr != null && !hoursStr.equals("")) {
                                double hours = (new BigDecimal(hoursStr)).doubleValue();
                                timeUsage.setHours(hours);
                            }
                        } else {
                            String month = (String) months.get(k);
                            TimeUsage timeUsage = (TimeUsage) userHours.get(userNameCellValue + "." + month);
                            timeUsage.setPercentage(detailCell.getNumericCellValue());
                            k++;
                        }
                    }
                }
            }
        }
    }
    return userHours;
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static void writeEffortTotal() {
    HSSFSheet templateSheet = templateWbk.getSheet("Effort Total");
    String lastProjectName = null;
    Date startDateValue = null;//from  ww w.jav a 2 s.com
    Date endDateValue = null;
    double totalDaysValue = 0;
    for (int i = 4; i < 10000; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            }
        } else {
            HSSFCell projectNameCell = row.getCell((short) 1);
            HSSFCell startDateCell = row.getCell((short) 2);
            HSSFCell endDateCell = row.getCell((short) 3);
            HSSFCell totalDaysCell = row.getCell((short) 4);
            if (totalDaysCell == null) {
                break;
            }
            String newProjectName = projectNameCell.getStringCellValue();
            Date _startDateValue = startDateCell.getDateCellValue();
            Date _endDateValue = endDateCell.getDateCellValue();
            double _totalDaysValue = totalDaysCell.getNumericCellValue();
            if (lastProjectName == null) {
                lastProjectName = newProjectName;
                startDateValue = _startDateValue;
                endDateValue = _endDateValue;
                totalDaysValue = _totalDaysValue;
            } else {
                if (newProjectName.equals(lastProjectName)) {
                    totalDaysValue += _totalDaysValue;
                    templateSheet.getRow(i - 1).getCell((short) 4).setCellValue(totalDaysValue);
                    if (startDateValue.compareTo(_startDateValue) > 0) {
                        startDateValue = _startDateValue;
                        templateSheet.getRow(i - 1).getCell((short) 2).setCellValue(startDateValue);
                    }
                    if (endDateValue.compareTo(_endDateValue) < 0) {
                        endDateValue = _endDateValue;
                        templateSheet.getRow(i - 1).getCell((short) 3).setCellValue(endDateValue);
                    }
                    templateSheet.removeRow(row);
                    templateSheet.shiftRows(i + 1, 109, -1);
                    i--;
                } else {
                    lastProjectName = newProjectName;
                    startDateValue = _startDateValue;
                    endDateValue = _endDateValue;
                    totalDaysValue = _totalDaysValue;
                }
            }
        }
    }
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

public static void writeEffortSummary() {
    HSSFSheet templateSheet = templateWbk.getSheet("Effort Summary");
    String lastCellValue = null;/*from   w w w  . j av a 2 s.  com*/
    double days = 0;
    int count = 1;
    for (int i = 6; i < 10000; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            }
        } else {
            HSSFCell cell = row.getCell((short) 2);
            HSSFCell dayCell = row.getCell((short) 6);
            if (cell != null) {
                if (lastCellValue == null) {
                    lastCellValue = cell.getStringCellValue();
                    count = 1;
                    String dayStr = dayCell.getStringCellValue().trim();
                    days = new BigDecimal(dayStr).doubleValue();
                    templateSheet.getRow(i).getCell((short) 3).setCellValue((double) count);
                    templateSheet.getRow(i).getCell((short) 4).setCellValue(days);
                } else {
                    String newCellValue = cell.getStringCellValue();
                    if ("Project Name".equals(newCellValue)) {
                        continue;
                    }
                    if (newCellValue != null) {
                        if (newCellValue.equals(lastCellValue)) {
                            Region region = new Region();
                            region.setRowTo(i);
                            region.setRowFrom(i - 1);
                            region.setColumnFrom((short) 2);
                            region.setColumnTo((short) 2);
                            templateSheet.addMergedRegion(region);
                            templateSheet.getRow(i - 1).getCell((short) 3).getCellStyle()
                                    .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

                            region = new Region();
                            region.setRowTo(i);
                            region.setRowFrom(i - 1);
                            region.setColumnFrom((short) 3);
                            region.setColumnTo((short) 3);
                            templateSheet.addMergedRegion(region);
                            count++;
                            templateSheet.getRow(i - count + 1).getCell((short) 3).setCellValue((double) count);

                            region = new Region();
                            region.setRowTo(i);
                            region.setRowFrom(i - 1);
                            region.setColumnFrom((short) 4);
                            region.setColumnTo((short) 4);
                            templateSheet.addMergedRegion(region);

                            String dayStr = dayCell.getStringCellValue().trim();
                            days += new BigDecimal(dayStr).doubleValue();
                            templateSheet.getRow(i - count + 1).getCell((short) 4).setCellValue(days);
                        } else {
                            lastCellValue = newCellValue;
                            count = 1;
                            templateSheet.getRow(i).getCell((short) 3).setCellValue((double) count);

                            String dayStr = dayCell.getStringCellValue().trim();
                            days = new BigDecimal(dayStr).doubleValue();
                            templateSheet.getRow(i).getCell((short) 4).setCellValue(days);
                        }
                    } else {
                        lastCellValue = newCellValue;
                        count = 1;
                        templateSheet.getRow(i).getCell((short) 3).setCellValue((double) count);

                        String dayStr = dayCell.getStringCellValue().trim();
                        days = new BigDecimal(dayStr).doubleValue();
                        templateSheet.getRow(i).getCell((short) 4).setCellValue(days);
                    }
                }
            }
        }
    }
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

public static void writeEffort(int index, String sheetName) {
    HSSFSheet templateSheet = templateWbk.getSheet(sheetName);
    String lastCellValue = null;//from   w w w.  j  a v  a  2s.c om
    for (int i = 0; i < 10000; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            }
        } else {
            HSSFCell cell = row.getCell((short) index);
            if (cell != null) {
                if (lastCellValue == null && cell.getStringCellValue() != null
                        && !cell.getStringCellValue().trim().equals("")) {
                    lastCellValue = cell.getStringCellValue();
                } else {
                    String newCellValue = cell.getStringCellValue();
                    if (newCellValue != null && !newCellValue.trim().equals("")) {
                        if (newCellValue.equals(lastCellValue)) {
                            Region region = new Region();
                            region.setRowTo(i);
                            region.setRowFrom(i - 1);
                            region.setColumnFrom((short) index);
                            region.setColumnTo((short) index);
                            templateSheet.addMergedRegion(region);
                            templateSheet.getRow(i - 1).getCell((short) index).getCellStyle()
                                    .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                        } else {
                            lastCellValue = newCellValue;
                        }
                    } else {
                        lastCellValue = newCellValue;
                    }
                }
            }
        }
    }
}