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.b510.excel.client.LoanCalculator.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;// w w  w .  java  2s.c  o  m

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);
    Sheet sheet = wb.createSheet("Loan Calculator");
    sheet.setPrintGridlines(false);
    sheet.setDisplayGridlines(false);

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    sheet.setColumnWidth(0, 3 * 256);
    sheet.setColumnWidth(1, 3 * 256);
    sheet.setColumnWidth(2, 11 * 256);
    sheet.setColumnWidth(3, 14 * 256);
    sheet.setColumnWidth(4, 14 * 256);
    sheet.setColumnWidth(5, 14 * 256);
    sheet.setColumnWidth(6, 14 * 256);

    createNames(wb);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(35);
    for (int i = 1; i <= 7; i++) {
        titleRow.createCell(i).setCellStyle(styles.get("title"));
    }
    Cell titleCell = titleRow.getCell(2);
    titleCell.setCellValue("Simple Loan Calculator");
    sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1"));

    Row row = sheet.createRow(2);
    Cell cell = row.createCell(4);
    cell.setCellValue("Enter values");
    cell.setCellStyle(styles.get("item_right"));

    row = sheet.createRow(3);
    cell = row.createCell(2);
    cell.setCellValue("Loan amount");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_$"));
    cell.setAsActiveCell();

    row = sheet.createRow(4);
    cell = row.createCell(2);
    cell.setCellValue("Annual interest rate");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_%"));

    row = sheet.createRow(5);
    cell = row.createCell(2);
    cell.setCellValue("Loan period in years");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_i"));

    row = sheet.createRow(6);
    cell = row.createCell(2);
    cell.setCellValue("Start date of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_d"));

    row = sheet.createRow(8);
    cell = row.createCell(2);
    cell.setCellValue("Monthly payment");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(9);
    cell = row.createCell(2);
    cell.setCellValue("Number of payments");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")");
    cell.setCellStyle(styles.get("formula_i"));

    row = sheet.createRow(10);
    cell = row.createCell(2);
    cell.setCellValue("Total interest");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(11);
    cell = row.createCell(2);
    cell.setCellValue("Total cost of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    // Write the output to a file
    String file = "loan-calculator.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.b510.excel.client.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//from w ww.  j  ava  2s .  c o m

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    //title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    //header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    //row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    //set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    //finally set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 30 * 256); //30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); //6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); //10 characters wide

    // Write the output to a file
    String file = "timesheet.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.base2.kagura.core.ExportHandler.java

License:Apache License

/**
 * Takes the output and transforms it into a Excel file.
 * @param out Output stream./*from   w  w w.j av  a2 s . com*/
 * @param rows Rows of data from reporting-core
 * @param columns Columns to list on report
 */
public void generateXls(OutputStream out, List<Map<String, Object>> rows, List<ColumnDef> columns) {
    try {
        Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();
        String safeName = WorkbookUtil.createSafeSheetName("Report"); // returns " O'Brien's sales   "
        Sheet reportSheet = wb.createSheet(safeName);

        short rowc = 0;
        Row nrow = reportSheet.createRow(rowc++);
        short cellc = 0;
        if (rows == null)
            return;
        if (columns == null && rows.size() > 0) {
            columns = new ArrayList<ColumnDef>(CollectionUtils.collect(rows.get(0).keySet(), new Transformer() {
                @Override
                public Object transform(final Object input) {
                    return new ColumnDef() {
                        {
                            setName((String) input);
                        }
                    };
                }
            }));
        }
        if (columns != null) {
            for (ColumnDef column : columns) {
                Cell cell = nrow.createCell(cellc++);
                cell.setCellValue(column.getName());
            }
        }
        for (Map<String, Object> row : rows) {
            nrow = reportSheet.createRow(rowc++);
            cellc = 0;
            for (ColumnDef column : columns) {
                Cell cell = nrow.createCell(cellc++);
                cell.setCellValue(String.valueOf(row.get(column.getName())));
            }
        }
        wb.write(out);
    } catch (IOException e) {
        e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
    }

}

From source file:com.bizosys.dataservice.dao.ReadXLS.java

License:Apache License

@Override
protected List<String> populate() throws SQLException {

    checkCondition();//from  w  ww  .j av  a2s.  c om

    Workbook workbook = getWorkbook();
    Sheet sheet = workbook.createSheet();

    ResultSetMetaData md = rs.getMetaData();
    int totalCol = md.getColumnCount();
    String[] cols = createLabels(md, totalCol);

    try {

        if (null != templateFile) {
            File templateFileObject = new File(templateFile);
            if (templateFileObject.exists()) {
                Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject));
                Sheet templatesheet = templateWorkbook.getSheetAt(0);
                Iterator<Row> rowIterator = templatesheet.iterator();

                while (rowIterator.hasNext()) {
                    Row templateRow = rowIterator.next();
                    Row row = sheet.createRow(startRowIndex++);

                    Iterator<Cell> cellIterator = templateRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell templateCell = cellIterator.next();
                        Cell cell = row.createCell(templateCell.getColumnIndex());
                        cell.setCellType(templateCell.getCellType());
                        switch (templateCell.getCellType()) {
                        case Cell.CELL_TYPE_BLANK:
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cell.setCellValue(templateCell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            cell.setCellValue(templateCell.getErrorCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cell.setCellValue(templateCell.getCellFormula());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            cell.setCellValue(templateCell.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            cell.setCellValue(templateCell.getStringCellValue());
                            break;
                        }
                    }
                }
            } else {
                System.err.println("Can not read " + templateFileObject.getAbsolutePath());
            }

        }

        while (this.rs.next()) {
            createRecord(totalCol, cols, sheet);
        }
        workbook.write(out);
    } catch (IOException ex) {
        throw new SQLException(ex);
    }
    return null;
}

From source file:com.bizosys.dataservice.dao.ReadXLS.java

License:Apache License

private void createRecord(int colsT, String[] cols, Sheet sheet) throws SQLException {

    Object colObj = null;/*from  ww  w  . ja  v  a2 s .c  om*/
    String colStr = null;

    Row row = sheet.createRow(startRowIndex++);
    for (int colI = 0; colI < colsT; colI++) {
        colObj = rs.getObject(colI + 1);
        colStr = (null == colObj) ? EMPTY_STRING : colObj.toString().trim();
        Cell cell = row.createCell(colI);
        cell.setCellValue(colStr);
    }
}

From source file:com.bizosys.dataservice.dao.ReadXLS.java

License:Apache License

private void writeHeader(String[] headerCols, Sheet sheet) {
    Row headerRow = sheet.createRow(startRowIndex++);
    int cellIndex = 0;
    for (String aCol : headerCols) {
        Cell headerCell = headerRow.createCell(cellIndex++);
        headerCell.setCellValue(aCol);/*from  ww  w. j  av  a 2s .co  m*/
    }
}

From source file:com.bizosys.dataservice.dao.WriteToXls.java

License:Apache License

public void write(List<Object[]> records) throws Exception {
    Workbook workbook = getWorkbook();/* ww w .j a va 2 s  . c o  m*/
    Sheet sheet = workbook.createSheet();

    if (null != templateFile) {
        File templateFileObject = new File(templateFile);
        if (templateFileObject.exists()) {
            Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject));
            Sheet templatesheet = templateWorkbook.getSheetAt(0);
            Iterator<Row> rowIterator = templatesheet.iterator();

            while (rowIterator.hasNext()) {
                Row templateRow = rowIterator.next();
                Row row = sheet.createRow(startRowIndex++);

                Iterator<Cell> cellIterator = templateRow.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell templateCell = cellIterator.next();
                    Cell cell = row.createCell(templateCell.getColumnIndex());
                    cell.setCellType(templateCell.getCellType());
                    switch (templateCell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        cell.setCellValue(templateCell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        cell.setCellValue(templateCell.getErrorCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        cell.setCellValue(templateCell.getCellFormula());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        cell.setCellValue(templateCell.getNumericCellValue());
                        break;
                    case Cell.CELL_TYPE_STRING:
                        cell.setCellValue(templateCell.getStringCellValue());
                        break;
                    }
                }
            }
        } else {
            System.err.println("Can not read " + templateFileObject.getAbsolutePath());
        }
    }

    for (Object[] cols : records) {
        createRecord(cols, sheet);
    }
    workbook.write(out);

}

From source file:com.bizosys.dataservice.dao.WriteToXls.java

License:Apache License

private void createRecord(Object[] cols, Sheet sheet) throws SQLException {

    String colStr = null;//from   ww w  . java  2s  .  c  o  m

    Row row = sheet.createRow(startRowIndex++);
    int colI = 0;
    for (Object colObj : cols) {
        colStr = (null == colObj) ? EMPTY_STRING : colObj.toString().trim();
        Cell cell = row.createCell(colI);
        cell.setCellValue(colStr);
        colI++;
    }
}

From source file:com.blackducksoftware.tools.commonframework.standard.datatable.writer.DataSetWriterExcel.java

License:Apache License

private Sheet initNewSheet(RecordDef recordDef) {
    Sheet sheet = workbook.createSheet();
    // Write header row
    Row row = sheet.createRow(0);
    int cellIndex = 0;
    for (FieldDef fieldDef : recordDef) {
        Cell cell = row.createCell(cellIndex++);
        String cellValue = fieldDef.getDescription();
        cell.setCellValue(cellValue);//  ww w.ja v  a 2 s.  c o  m
    }
    return sheet;
}

From source file:com.blackducksoftware.tools.commonframework.standard.datatable.writer.DataSetWriterExcel.java

License:Apache License

private void addDataRow(Sheet sheet, RecordDef recordDef, Record record, int rowIndex) throws Exception {
    Row row = sheet.createRow(rowIndex++);
    int cellIndex = 0;
    for (FieldDef fieldDef : recordDef) {
        Cell cell = row.createCell(cellIndex++);
        switch (fieldDef.getType()) {
        case STRING:
        case DATE:
            populateStringCell(record, fieldDef, cell);
            break;
        case HYPERLINK:
            populateHyperlinkCell(record, fieldDef, cell);
            break;

        }// w  ww.  ja v  a2 s. c  o  m
    }
}