Example usage for org.apache.poi.ss.usermodel Sheet createRow

List of usage examples for org.apache.poi.ss.usermodel Sheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. createRow

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet createRow.

Prototype

Row createRow(int rownum);

Source Link

Document

Create a new row within the sheet and return the high level representation

Usage

From source file:com.NRC.NMEA.main.NMEA.java

public static Sheet writeAbsElevationsToExcel(TestMap m, Sheet sheet, int startRow) throws IOException {
    PseudoCollectionSet s = new PseudoCollectionSet();

    for (DataPointInTime d : m) {
        Double f = (d.elevation);
        if (!(f == null)) {
            s.add(f);/*from  w  w  w .j a va2  s .  c  o  m*/
        }
    }
    Row row1 = sheet.createRow((short) startRow);
    Row row2 = sheet.createRow((short) startRow + 1);
    Row row3 = sheet.createRow((short) startRow + 2);
    Cell row1c = row1.createCell(0);
    row1c.setCellValue(m.name);
    Cell row2c = row2.createCell(0);
    row2c.setCellValue("Elevation");
    Cell row3c = row3.createCell(0);
    row3c.setCellValue("Multiples");
    int coli = 1;
    for (Object c : s) {
        int i = 0;
        for (i = 0; i < s.getMultiples(c); i++) {
            row2c = row2.createCell(coli + i);
            row2c.setCellValue((Double) c);
        }

        coli += i;
    }
    return sheet;
}

From source file:com.NRC.NMEA.main.NMEA.java

public static Sheet writePositionsToExcel(TestMap m, Sheet sheet, int startRow) throws IOException {
    Map<Coordinate, Integer> s = new HashMap();

    for (DataPointInTime d : m) {
        Coordinate f = (d.coordinate);//from www . jav a2s  .  c  o m
        if (!((f == null) || (f.getLatitude() == -1))) {
            if (s.containsKey(f)) {
                s.put(f, s.get(f) + 1);
            } else {
                s.put(f, 1);
            }
        }
    }
    Row row1 = sheet.createRow((short) startRow);
    Row row2 = sheet.createRow((short) startRow + 1);
    Row row3 = sheet.createRow((short) startRow + 2);
    Row row4 = sheet.createRow((short) startRow + 3);
    Cell row1c = row1.createCell(0);
    row1c.setCellValue(m.name);
    Cell row2c = row2.createCell(0);
    row2c.setCellValue("lat");
    Cell row3c = row3.createCell(0);
    row3c.setCellValue("long");
    Cell row4c = row4.createCell(0);
    row4c.setCellValue("mult");

    int coli = 1;
    for (Coordinate c : s.keySet()) {
        row2c = row2.createCell(coli);
        row3c = row3.createCell(coli);
        row4c = row4.createCell(coli);
        row2c.setCellValue(c.getLatitude());
        row3c.setCellValue(-c.getLongitude());
        row4c.setCellValue(s.get(c));
        coli++;
    }
    return sheet;
}

From source file:com.oleke.facebookcrawler.ExcelAPI.java

License:Apache License

/**
 * This method creates a new row on a sheet
 *
 * @param sh the sheet to write on/*from   w  ww  . j  av  a 2  s  . c om*/
 * @return Returns a new row
 */
public Row createRow(Sheet sh) {
    int rowNo = sh.getPhysicalNumberOfRows();
    Row rw = sh.createRow(rowNo);
    return rw;
}

From source file:com.opendoorlogistics.core.tables.io.PoiIO.java

License:Open Source License

private static void addSchema(ODLDatastore<? extends ODLTableDefinition> ds, Workbook wb) {
    ODLTableReadOnly table = SchemaIO.createSchemaTable(ds);
    Sheet sheet = wb.createSheet(SCHEMA_SHEET_NAME);

    // write out key-value table
    Row row = sheet.createRow(0);
    row.createCell(0).setCellValue(SchemaIO.KEY_COLUMN);
    row.createCell(1).setCellValue(SchemaIO.VALUE_COLUMN);
    row = sheet.createRow(1);/*from w  w  w  . j a v  a2s . c o m*/
    row.createCell(0).setCellValue(SchemaIO.APP_VERSION_KEY);
    row.createCell(1).setCellValue(AppConstants.getAppVersion().toString());

    // write schema table
    exportTable(sheet, table, sheet.getLastRowNum() + 2, null, null);

    // hide the sheet from users
    wb.setSheetHidden(wb.getNumberOfSheets() - 1, Workbook.SHEET_STATE_VERY_HIDDEN);
}

From source file:com.opendoorlogistics.core.tables.io.PoiIO.java

License:Open Source License

private static void exportTable(Sheet sheet, ODLTableReadOnly table, int firstOutputRow,
        ProcessingApi processingApi, ExecutionReport report) {
    UpdateTimer timer = new UpdateTimer(250);

    int nbOversized = 0;

    // create header row
    int nc = table.getColumnCount();
    Row header = sheet.createRow(firstOutputRow);
    for (int col = 0; col < nc; col++) {
        Cell cell = header.createCell(col);
        cell.setCellValue(table.getColumnName(col));
    }// ww  w .  ja  va2s. c o  m

    // set header style
    styleHeader(sheet.getWorkbook(), sheet);

    // write data
    for (int srcRow = 0; srcRow < table.getRowCount(); srcRow++) {
        Row row = sheet.createRow(firstOutputRow + 1 + srcRow);
        for (int col = 0; col < nc; col++) {
            Cell cell = row.createCell(col);
            if (saveElementToCell(table, srcRow, col, cell) == SaveElementResult.OVERSIZED) {
                nbOversized++;
            }

        }

        if (processingApi != null && processingApi.isCancelled()) {
            return;
        }

        if (processingApi != null && timer.isUpdate()) {
            processingApi.postStatusMessage(
                    "Saving - processed row " + (srcRow + 1) + " of sheet " + table.getName());
        }
    }

    if (nbOversized > 0 && report != null) {
        report.log(getOversizedWarningMessage(nbOversized, table.getName()));
    }
}

From source file:com.opendoorlogistics.speedregions.excelshp.io.ExcelWriter.java

License:Apache License

public static void writeSheets(File file, ExportTable... tables) {
    // create empty workbook with a bold font style
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFCellStyle headerStyle = wb.createCellStyle();
    XSSFFont boldfont = wb.createFont();
    boldfont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    boldfont.setFontHeight(12);//from w  ww.ja  va 2 s.c  o  m
    headerStyle.setFont(boldfont);

    // fill workbook
    for (ExportTable table : tables) {
        Sheet sheet = wb.createSheet(table.getName());

        Row headerRow = sheet.createRow(0);
        for (int c = 0; c < table.getHeader().size(); c++) {
            Cell cell = headerRow.createCell(c);
            cell.setCellStyle(headerStyle);
            cell.setCellValue(table.getHeader().get(c).getName());
        }

        List<List<String>> rows = table.getRows();

        int nr = rows.size();
        for (int r = 0; r < nr; r++) {
            Row row = sheet.createRow(r + 1);
            List<String> srcRow = rows.get(r);
            int nc = srcRow.size();
            for (int c = 0; c < nc; c++) {
                //JsonFormatTypes type = table.getColumnType(c);
                Cell cell = row.createCell(c);
                String value = srcRow.get(c);

                writeToCell(value, c < table.getHeader().size() ? table.getHeader().get(c).getFormatType()
                        : JsonFormatTypes.STRING, cell);

            }
        }
    }

    // try saving
    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream(file);
        wb.write(fileOut);
    } catch (Exception e) {
        // TODO: handle exception
    } finally {

        try {
            if (fileOut != null) {
                fileOut.close();
            }
        } catch (Exception e2) {
            throw new RuntimeException(e2);
        }

        try {
            if (wb != null) {
                wb.close();
            }
        } catch (Exception e2) {
            throw new RuntimeException(e2);
        }
    }

    LOGGER.info("Wrote Excel file " + file.getAbsolutePath());
}

From source file:com.opendoorlogistics.studio.LoadedDatastore.java

License:Open Source License

private void updateWorkbookWithModifications(Workbook wb, ExecutionReport report) {
    // parse the original tables; these will be held in the datastore with the same index as the sheet
    int nbOriginal = originalLoadedDs.getTableCount();
    if (nbOriginal != wb.getNumberOfSheets()) {
        throw new RuntimeException();
    }//from w w w . j av  a 2 s  .co m

    ArrayList<ODLTableReadOnly> oldOnesToReadd = new ArrayList<>();
    for (int i = nbOriginal - 1; i >= 0; i--) {
        ODLTableReadOnly originalTable = originalLoadedDs.getTableAt(i);
        ODLTableReadOnly newTable = ds.getTableByImmutableId(originalTable.getImmutableId());

        if (newTable == null) {
            // table was deleted
            wb.removeSheetAt(i);
        } else if (DatastoreComparer.isSame(originalTable, newTable, DatastoreComparer.CHECK_ALL) == false) {
            Sheet sheet = wb.getSheetAt(i);

            boolean sameStructure = DatastoreComparer.isSameStructure(originalTable, newTable,
                    DatastoreComparer.CHECK_ALL);
            if (sameStructure) {
                // re-write all values but skip the header row
                int nbOversized = 0;
                for (int iRow = 0; iRow < newTable.getRowCount(); iRow++) {
                    int iTargetRow = iRow + 1;
                    Row row = sheet.getRow(iTargetRow);
                    if (row == null) {
                        row = sheet.createRow(iTargetRow);
                    }

                    int nc = newTable.getColumnCount();
                    for (int col = 0; col < nc; col++) {
                        Cell cell = row.getCell(col);
                        if (cell != null && cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                            // don't set the value of formula cells...
                            continue;
                        }
                        if (cell == null) {
                            cell = row.createCell(col);
                        }

                        String sval = TableUtils.getValueAsString(newTable, iRow, col);
                        if (sval != null && sval.length() > PoiIO.MAX_CHAR_COUNT_IN_EXCEL_CELL) {
                            nbOversized++;
                        }
                        cell.setCellValue(sval);
                    }
                }

                // delete any rows after the last row (including 1 for the header)
                int lastOKRow = newTable.getRowCount();
                while (sheet.getLastRowNum() > lastOKRow) {
                    sheet.removeRow(sheet.getRow(sheet.getLastRowNum()));
                }

                if (nbOversized > 0 && report != null) {
                    report.log(PoiIO.getOversizedWarningMessage(nbOversized, newTable.getName()));
                    ;
                }

            } else {
                // delete and replace. replace after parsing all original tables as we can get table name conflicts
                wb.removeSheetAt(i);
                oldOnesToReadd.add(newTable);
            }

        }

    }

    // re-add any totally replaced tables
    for (ODLTableReadOnly table : oldOnesToReadd) {
        Sheet sheet = wb.createSheet(table.getName());
        if (sheet != null) {
            PoiIO.exportTable(sheet, table, report);
        }
    }

    // add new tables at the end
    for (int i = 0; i < ds.getTableCount(); i++) {
        ODLTableReadOnly newTable = ds.getTableAt(i);
        if (originalLoadedDs.getTableByImmutableId(newTable.getImmutableId()) == null) {
            // new table...
            Sheet sheet = wb.createSheet(newTable.getName());
            if (sheet != null) {
                PoiIO.exportTable(sheet, newTable, report);
            }
        }

    }
}

From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java

public FileInputStream generateAllEmployeeReport(File file) throws ExceptionWrapper {
    try {//from   ww  w. j  a v a 2 s.  c  o m
        FileInputStream inp = new FileInputStream(file);
        //InputStream inp = new FileInputStream("workbook.xlsx");

        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);

        int iRowCounter = 1;
        for (EmployeeBO employeeBO : employeeBL.getAllEmployees().values()) {
            Row row = sheet.getRow(iRowCounter);
            if (row == null)
                row = sheet.createRow(iRowCounter);
            Cell cell = row.getCell(0);
            if (cell == null)
                cell = row.createCell(0);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getEmployeeId());

            cell = row.getCell(1);
            if (cell == null)
                cell = row.createCell(1);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getEmployeeUid());

            cell = row.getCell(2);
            if (cell == null)
                cell = row.createCell(2);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getEmployeeName());

            cell = row.getCell(3);
            if (cell == null)
                cell = row.createCell(3);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getEmail());

            cell = row.getCell(4);
            if (cell == null)
                cell = row.createCell(4);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getManager());

            cell = row.getCell(5);
            if (cell == null)
                cell = row.createCell(5);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getMobileNumber());

            cell = row.getCell(6);
            if (cell == null)
                cell = row.createCell(6);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getExtensionNum());

            cell = row.getCell(7);
            if (cell == null)
                cell = row.createCell(7);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getWorkspace());

            iRowCounter = iRowCounter + 1;
        }
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
        return new FileInputStream(file);
    } catch (Exception ex) {
        throw new ExceptionWrapper(ex);
    }

}

From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java

public FileInputStream generateAllEmployeeTeamWiseReport(File file, Boolean isScrumTeam)
        throws ExceptionWrapper {
    try {//from   w  ww. j av  a 2 s.c  o  m
        FileInputStream inp = new FileInputStream(file);
        //InputStream inp = new FileInputStream("workbook.xlsx");

        Workbook wb = WorkbookFactory.create(inp);
        int iSheetCounter = 1;
        for (Integer groupID : employeeBL.getAllEmployeesKeyedByGroupId().keySet()) {
            GroupBO groupBO = groupsBL.getAllGroups().get(groupID);
            if (isScrumTeam && !groupBO.getRallyGroup())
                continue;
            if (!isScrumTeam && groupBO.getRallyGroup())
                continue;

            Sheet sheet = wb.cloneSheet(0);
            wb.setSheetName(wb.getSheetIndex(sheet), groupBO.getGroupName());
            //wb.setSheetName(iSheetCounter,groupBO.getGroupName());

            int iRowCounter = 1;
            for (EmployeeBO employeeBO : employeeBL.getAllEmployeesKeyedByGroupId().get(groupID)) {
                Row row = sheet.getRow(iRowCounter);
                if (row == null)
                    row = sheet.createRow(iRowCounter);
                Cell cell = row.getCell(0);
                if (cell == null)
                    cell = row.createCell(0);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeId());

                cell = row.getCell(1);
                if (cell == null)
                    cell = row.createCell(1);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeUid());

                cell = row.getCell(2);
                if (cell == null)
                    cell = row.createCell(2);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeName());

                cell = row.getCell(3);
                if (cell == null)
                    cell = row.createCell(3);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmail());

                cell = row.getCell(4);
                if (cell == null)
                    cell = row.createCell(4);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getManager());

                cell = row.getCell(5);
                if (cell == null)
                    cell = row.createCell(5);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getMobileNumber());

                cell = row.getCell(6);
                if (cell == null)
                    cell = row.createCell(6);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getExtensionNum());

                cell = row.getCell(7);
                if (cell == null)
                    cell = row.createCell(7);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getWorkspace());

                if (isScrumTeam) {
                    cell = row.getCell(8);
                    if (cell == null)
                        cell = row.createCell(8);
                    setCellBorder(wb, cell);
                    cell.setCellValue(employeeBO.getSpecificRoleInScrumTeam());
                }

                iRowCounter = iRowCounter + 1;
            }
        }
        iSheetCounter = iSheetCounter + 1;
        wb.removeSheetAt(0);
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
        return new FileInputStream(file);
    } catch (Exception ex) {
        throw new ExceptionWrapper(ex);
    }

}

From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java

public FileInputStream generateLicenseReport(File file) throws ExceptionWrapper {
    try {/*from ww w  . j a v a2s  .c  om*/
        FileInputStream inp = new FileInputStream(file);
        //InputStream inp = new FileInputStream("workbook.xlsx");

        Workbook wb = WorkbookFactory.create(inp);
        HashMap<String, ArrayList<String>> licenseInfoKeyedByLicenseName = licensesBL.getLicenseRelatedInfo();
        //Create The Summary Sheet
        Sheet sheetSummary = wb.getSheetAt(1);
        int iSummaryRowCounter = 1;
        for (String licenseType : licenseInfoKeyedByLicenseName.keySet()) {

            Row row = sheetSummary.getRow(iSummaryRowCounter);
            if (row == null)
                row = sheetSummary.createRow(iSummaryRowCounter);
            Cell cell = row.getCell(0);
            if (cell == null)
                cell = row.createCell(0);
            setCellBorder(wb, cell);
            cell.setCellValue(licenseType);

            row = sheetSummary.getRow(iSummaryRowCounter);
            if (row == null)
                row = sheetSummary.createRow(iSummaryRowCounter);
            cell = row.getCell(1);
            if (cell == null)
                cell = row.createCell(1);
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            row = sheetSummary.getRow(iSummaryRowCounter);
            if (row == null)
                row = sheetSummary.createRow(iSummaryRowCounter);
            cell = row.getCell(2);
            if (cell == null)
                cell = row.createCell(2);
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            row = sheetSummary.getRow(iSummaryRowCounter);
            if (row == null)
                row = sheetSummary.createRow(iSummaryRowCounter);
            cell = row.getCell(3);
            if (cell == null)
                cell = row.createCell(3);
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0))
                    - Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            iSummaryRowCounter = iSummaryRowCounter + 1;
        }
        int iSheetCounter = 1;

        for (String licenseType : licenseInfoKeyedByLicenseName.keySet()) {
            Sheet sheet = wb.cloneSheet(0);
            wb.setSheetName(wb.getSheetIndex(sheet), licenseType);
            CellReference cellReference = new CellReference("B1");
            Row row = sheet.getRow(cellReference.getRow());
            Cell cell = row.getCell(cellReference.getCol());
            if (cell == null)
                cell = row.createCell(cellReference.getCol());
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            cellReference = new CellReference("B2");
            row = sheet.getRow(cellReference.getRow());
            cell = row.getCell(cellReference.getCol());
            if (cell == null)
                cell = row.createCell(cellReference.getCol());
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            cellReference = new CellReference("B3");
            row = sheet.getRow(cellReference.getRow());
            cell = row.getCell(cellReference.getCol());
            if (cell == null)
                cell = row.createCell(cellReference.getCol());
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0))
                    - Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            ArrayList<EmployeeBO> allEmployees = new ArrayList<EmployeeBO>(
                    employeeBL.getAllEmployees().values());
            final String selectedLicenseTypeName = licenseType;
            CollectionUtils.filter(allEmployees, new Predicate() {
                @Override
                public boolean evaluate(Object o) {
                    EmployeeBO emp = (EmployeeBO) o;
                    if (CollectionUtils.exists(emp.getLicenses(), new Predicate() {
                        @Override
                        public boolean evaluate(Object o) {
                            return ((LicenseBO) o).getLicenseTypeName()
                                    .equalsIgnoreCase(selectedLicenseTypeName); //To change body of implemented methods use File | Settings | File Templates.
                        }
                    }))
                        return true;
                    else
                        return false;
                }
            });

            int iRowCounter = 5;
            for (EmployeeBO employeeBO : allEmployees) {
                row = sheet.getRow(iRowCounter);
                if (row == null)
                    row = sheet.createRow(iRowCounter);
                cell = row.getCell(0);
                if (cell == null)
                    cell = row.createCell(0);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeId());

                cell = row.getCell(1);
                if (cell == null)
                    cell = row.createCell(1);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeUid());

                cell = row.getCell(2);
                if (cell == null)
                    cell = row.createCell(2);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeName());

                cell = row.getCell(3);
                if (cell == null)
                    cell = row.createCell(3);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmail());

                cell = row.getCell(4);
                if (cell == null)
                    cell = row.createCell(4);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getManager());

                cell = row.getCell(5);
                if (cell == null)
                    cell = row.createCell(5);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getMobileNumber());

                cell = row.getCell(6);
                if (cell == null)
                    cell = row.createCell(6);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getExtensionNum());

                cell = row.getCell(7);
                if (cell == null)
                    cell = row.createCell(7);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getWorkspace());

                iRowCounter = iRowCounter + 1;
            }
        }
        iSheetCounter = iSheetCounter + 1;
        wb.removeSheetAt(0);
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
        return new FileInputStream(file);
    } catch (Exception ex) {
        throw new ExceptionWrapper(ex);
    }

}