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.firstonesoft.poi.TimesheetDemo.java

License:Apache License

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

    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("Resumen de Horas");
    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 = "D://timesheet.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.fjn.helper.common.io.file.office.excel.ExcelUtil.java

License:Apache License

/**
 * ??java Bean//  w w w  .  ja  v a2  s.co m
 *
 * @param sheet
 * @param headers
 */
public static void addHeaderRow(Sheet sheet, int rowIndex, List<String> headers) {
    Row row = null;
    Cell cell = null;

    // Field[] fields=clazz.getDeclaredFields();
    // Excelheader,?
    row = sheet.createRow(rowIndex);
    for (int i = 0; i < headers.size(); i++) {
        cell = row.createCell(i);
        cell.setCellValue(headers.get(i));
    }
}

From source file:com.fjn.helper.common.io.file.office.excel.ExcelUtil.java

License:Apache License

/**
 * ??java Bean ???@ExcelHeader,/*from  www.  ja va 2  s. c o  m*/
 *
 * @param sheet
 * @param clazz
 * @param fieldnames
 */
public static void addHeaderRow(Sheet sheet, int rowIndex, Class clazz, List<String> fieldnames) {
    Row row = null;
    Cell cell = null;

    // Field[] fields=clazz.getDeclaredFields();

    // Excelheader,?
    row = sheet.createRow(rowIndex);
    for (int i = 0; i < fieldnames.size(); i++) {
        Field tempfield = null;
        try {
            tempfield = clazz.getDeclaredField(fieldnames.get(i));
        } catch (Exception ex) {
            if (logger.isEnabledFor(Priority.ERROR)) {
                logger.error("??");
            }
        }

        if (tempfield != null) {
            ExcelHeader excelheader = tempfield.getAnnotation(ExcelHeader.class);
            if (excelheader != null) {
                cell = row.createCell(i);
                cell.setCellValue(excelheader.value());
            }
        } else {
            logger.warn("" + fieldnames.get(i) + "@ExcelHeader ???");
            fieldnames.remove(i);
        }
    }
}

From source file:com.fjn.helper.common.io.file.office.excel.ExcelUtil.java

License:Apache License

/**
 * ??/* w w  w. java  2s . c om*/
 *
 * @param sheet
 * @param clazz
 *            ???java bean
 * @param fieldnames
 *            java bean???ExeclHeader
 * @param list
 *            ?
 * @return
 */
@SuppressWarnings("unused")
public static Sheet addDataToSheet(Sheet sheet, Class clazz, List<String> fieldnames, List<?> list) {
    // ?
    Row row = null;
    Cell cell = null;
    int lastRowNum = sheet.getLastRowNum();
    for (int i = 0; i < list.size(); i++) {
        row = sheet.createRow(lastRowNum + 1 + i);
        for (int j = 0; j < fieldnames.size(); j++) {
            try {
                Field field = clazz.getDeclaredField(fieldnames.get(j));
                field.setAccessible(true);
                cell = row.createCell(j);
                Class fieldType = field.getType();
                // cell.setCellType(Cell.);
                Object value = field.get(list.get(i));
                setCellValue(cell, value);

            } catch (Exception ex) {
                if (logger.isEnabledFor(Priority.ERROR)) {
                    logger.error("" + (i + 1) + "" + j + "", ex);
                }
            }
        }
    }

    return sheet;
}

From source file:com.fjn.helper.common.io.file.office.excel.ListExcelSheetEditor.java

License:Apache License

public void addTitle(String title) {
    Sheet sheet = excelSheet.getSheet();
    Row row = sheet.createRow(excelSheet.sheet.getFirstRowNum());
    Cell cell = row.createCell(0);/*from   w w  w  .  j  a  v  a2 s  .c o  m*/
    /*cell.setCellType(Cell.CELL_TYPE_STRING);
    cell.setCellValue(title);*/
    setCellValue(cell, title);
}

From source file:com.fjn.helper.common.io.file.office.excel.ListExcelSheetEditor.java

License:Apache License

/**
 * //from  www. j a va 2 s .  c om
 * @param rowIndex
 */
public void addHeaderRow(int rowIndex) {
    Sheet sheet = excelSheet.getSheet();
    Row row = null;
    Cell cell = null;
    row = sheet.createRow(rowIndex + 1);
    List<String> headers = excelSheet.getHeaders();
    for (int i = 0; i < headers.size(); i++) {
        cell = row.createCell(i);
        cell.setCellValue(headers.get(i));
    }
}

From source file:com.fjn.helper.common.io.file.office.excel.ListExcelSheetEditor.java

License:Apache License

@SuppressWarnings({ "unchecked", "unused", "deprecation" })
public <T> void insertItemToSheet(int rowIndex, T t) {
    if (t == null)
        return;/* ww  w.j  av a 2s . c  o m*/
    // ?
    Row row = null;
    Cell cell = null;
    Class<T> clazz = (Class<T>) t.getClass();
    Sheet sheet = excelSheet.sheet;
    row = sheet.createRow(rowIndex);
    for (int j = 0; j < excelSheet.getFieldList().size(); j++) {
        try {
            Field field = clazz.getDeclaredField(excelSheet.getFieldList().get(j));
            field.setAccessible(true);
            cell = row.createCell(j);
            Class fieldType = field.getType();
            //   cell.setCellType(Cell.);
            Object value = field.get(t);
            setCellValue(cell, value);

        } catch (Exception ex) {
            if (logger.isEnabledFor(Priority.ERROR)) {
                logger.error("" + row.getRowNum() + "" + j + "", ex);
            }
        }
    }

}

From source file:com.fjn.helper.common.io.file.office.excel.ListExcelSheetEditor.java

License:Apache License

@SuppressWarnings({ "unused", "deprecation" })
public void addItemsToSheet() {
    // ?//w  w w. java  2s  .co  m
    Class clazz = excelSheet.getBeanClass();
    if (clazz == null) {
        return;
    }
    List<String> fieldnames = null;
    fieldnames = excelSheet.getFieldList();
    if (fieldnames == null)
        return;
    List<?> items = null;
    items = excelSheet.getDataList();
    if (items == null)
        return;
    Sheet sheet = excelSheet.sheet;
    Row row = null;
    Cell cell = null;
    int lastRowNum = sheet.getLastRowNum();
    for (int i = 0; i < items.size(); i++) {
        row = sheet.createRow(lastRowNum + 1 + i);
        for (int j = 0; j < fieldnames.size(); j++) {
            try {
                Field field = clazz.getDeclaredField(fieldnames.get(j));
                field.setAccessible(true);
                cell = row.createCell(j);
                Class fieldType = field.getType();
                //   cell.setCellType(Cell.);
                Object value = field.get(items.get(i));
                setCellValue(cell, value);

            } catch (Exception ex) {
                if (logger.isEnabledFor(Priority.ERROR)) {
                    logger.error("" + (i + 1) + "" + j + "", ex);
                }
            }
        }
    }

}

From source file:com.github.autoprimer3.Primer3ResultViewController.java

License:Open Source License

private void writePrimersToExcel(final File f) throws IOException {
    final Service<Void> service = new Service<Void>() {
        @Override//from  w  w  w  .j  av a2  s .  co m
        protected Task<Void> createTask() {
            return new Task<Void>() {
                @Override
                protected Void call() throws IOException {
                    BufferedOutputStream bo = new BufferedOutputStream(new FileOutputStream(f));
                    Workbook wb = new XSSFWorkbook();
                    CellStyle hlink_style = wb.createCellStyle();
                    Font hlink_font = wb.createFont();
                    hlink_font.setUnderline(Font.U_SINGLE);
                    hlink_font.setColor(IndexedColors.BLUE.getIndex());
                    hlink_style.setFont(hlink_font);
                    CreationHelper createHelper = wb.getCreationHelper();
                    Sheet listSheet = wb.createSheet();
                    Sheet detailsSheet = wb.createSheet();
                    Row row = null;
                    int rowNo = 0;
                    int sheetNo = 0;
                    wb.setSheetName(sheetNo++, "List");
                    wb.setSheetName(sheetNo++, "Details");

                    row = listSheet.createRow(rowNo++);
                    String header[] = { "Primer", "Sequence", "Product Size (bp)" };
                    for (int col = 0; col < header.length; col++) {
                        Cell cell = row.createCell(col);
                        cell.setCellValue(header[col]);
                    }

                    updateMessage("Writing primers . . .");
                    updateProgress(0, data.size() * 3);
                    int n = 0;
                    for (Primer3Result r : data) {
                        n++;
                        updateMessage("Writing primer list " + n + " . . .");
                        row = listSheet.createRow(rowNo++);
                        int col = 0;
                        Cell cell = row.createCell(col++);
                        cell.setCellValue(r.getName() + "F");
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getLeftPrimer());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getProductSize());
                        updateProgress(n, data.size() * 3);
                        updateMessage("Writing primer list " + n + " . . .");
                        row = listSheet.createRow(rowNo++);
                        col = 0;
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getName() + "R");
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getRightPrimer());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getProductSize());
                        n++;
                        updateProgress(n, data.size() * 3);
                    }
                    rowNo = 0;
                    row = detailsSheet.createRow(rowNo++);
                    ArrayList<String> detailsHeader = new ArrayList<>(Arrays.asList("Name", "Other IDs",
                            "Left Primer", "Right Primer", "Product Size (bp)", "Region", "in-silico PCR"));
                    if (ispcrResCol.isVisible()) {
                        detailsHeader.add("in-silico PCR Results");
                    }
                    for (int col = 0; col < detailsHeader.size(); col++) {
                        Cell cell = row.createCell(col);
                        cell.setCellValue(detailsHeader.get(col));
                    }
                    int m = 0;
                    for (Primer3Result r : data) {
                        m++;
                        updateMessage("Writing details for pair " + m + " . . .");
                        row = detailsSheet.createRow(rowNo++);
                        int col = 0;
                        Cell cell = row.createCell(col++);
                        cell.setCellValue(r.getName());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getTranscripts());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getLeftPrimer());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getRightPrimer());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getProductSize());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getRegion());
                        cell = row.createCell(col++);
                        if (r.getIsPcrUrl() != null) {
                            cell.setCellValue("isPCR");
                            org.apache.poi.ss.usermodel.Hyperlink hl = createHelper
                                    .createHyperlink(org.apache.poi.ss.usermodel.Hyperlink.LINK_URL);
                            hl.setAddress(r.getIsPcrUrl());
                            cell.setHyperlink(hl);
                            cell.setCellStyle(hlink_style);
                        } else {
                            cell.setCellValue("");
                        }
                        if (ispcrResCol.isVisible()) {
                            cell = row.createCell(col++);
                            if (r.getIsPcrResults() != null) {
                                cell.setCellValue(r.getIsPcrResults());
                            } else {
                                cell.setCellValue("");
                            }
                        }
                        updateProgress(n + m, data.size() * 3);
                    }

                    updateMessage("Wrote " + data.size() + " primer pairs to file.");
                    wb.write(bo);
                    bo.close();
                    return null;
                }
            };
        }

    };

    service.setOnSucceeded(new EventHandler<WorkerStateEvent>() {
        @Override
        public void handle(WorkerStateEvent e) {

            Action response = Dialogs.create().title("Done").masthead("Finished writing")
                    .message("Primers successfully written to " + f.getAbsolutePath()
                            + "\n\nDo you want to open " + "this file now?")
                    .actions(Dialog.ACTION_YES, Dialog.ACTION_NO).styleClass(Dialog.STYLE_CLASS_NATIVE)
                    .showConfirm();

            if (response == Dialog.ACTION_YES) {
                try {
                    openFile(f);
                } catch (IOException ex) {
                    Action openFailed = Dialogs.create().title("Open failed")
                            .masthead("Could not open output file")
                            .message("Exception encountered when attempting to open "
                                    + "the saved file. See below:")
                            .styleClass(Dialog.STYLE_CLASS_NATIVE).showException(ex);
                }
            }
            progressBar.progressProperty().unbind();
            progressBar.setVisible(false);
            summaryLabel.textProperty().unbind();
            summaryLabel.setText(summary);
            closeButton.setDisable(false);
            closeMenuItem.setDisable(false);
            setCheckIsPcrButton();
        }
    });
    service.setOnCancelled(new EventHandler<WorkerStateEvent>() {
        @Override
        public void handle(WorkerStateEvent e) {
            Dialogs writeCancelled = Dialogs.create().title("Writing Cancelled")
                    .masthead("Cancelled writing to file").message("User cancelled writing primers to file.")
                    .styleClass(Dialog.STYLE_CLASS_NATIVE);
            writeCancelled.showInformation();
            progressBar.progressProperty().unbind();
            progressBar.setVisible(false);
            summaryLabel.textProperty().unbind();
            summaryLabel.setText(summary);
            closeButton.setDisable(false);
            closeMenuItem.setDisable(false);
            setCheckIsPcrButton();
        }
    });
    service.setOnFailed(new EventHandler<WorkerStateEvent>() {
        @Override
        public void handle(WorkerStateEvent e) {
            Action writeFailed = Dialogs.create().title("Writing failed")
                    .masthead("Could not write primers to file")
                    .message("Exception encountered when attempting to write " + "primers to file. See below:")
                    .styleClass(Dialog.STYLE_CLASS_NATIVE).showException(e.getSource().getException());
            progressBar.progressProperty().unbind();
            progressBar.setVisible(false);
            summaryLabel.textProperty().unbind();
            summaryLabel.setText(summary);
            closeButton.setDisable(false);
            closeMenuItem.setDisable(false);
            setCheckIsPcrButton();
        }
    });
    progressBar.setVisible(true);
    progressBar.progressProperty().bind(service.progressProperty());
    summaryLabel.textProperty().bind(service.messageProperty());
    closeButton.setDisable(true);
    closeMenuItem.setDisable(true);
    checkIsPcrButton.setText("Cancel");
    checkIsPcrButton.setOnAction(new EventHandler<ActionEvent>() {
        @Override
        public void handle(ActionEvent actionEvent) {
            service.cancel();

        }
    });
    service.start();
}

From source file:com.github.camaral.sheeco.Sheeco.java

License:Apache License

private Row createRow(final Sheet sheet) {
    sheet.createRow(0);
    final Row row = sheet.getRow(0);
    return row;
}