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

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

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.  j ava2 s.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("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

/**
 * sheetrowIndexcolIndex?/*from w  ww.  j  a va  2  s . c  o  m*/
 *
 * @param sheet
 * @param rowIndex
 * @param colIndex
 * @param value
 */
public static void setValue(Sheet sheet, int rowIndex, int colIndex, Object value) {
    Row row = sheet.getRow(rowIndex);
    Cell cell = row.getCell(colIndex);
    setCellValue(cell, value);
}

---

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

License:Apache License

/**
 * ??/*from   w w  w.  ja  v a2s  .c om*/
 *
 * @param sheet
 * @param columnIndex
 * @param style
 * @return
 */
public static boolean setColumnStyle(Sheet sheet, short columnIndex, int rowFirstIndex, int rowLastIndex,
        CellStyle style) {
    if (sheet == null)
        return false;

    int rowNum = sheet.getLastRowNum();
    CellStyle newCellStyle = sheet.getWorkbook().createCellStyle();
    // ??
    if (rowFirstIndex < rowLastIndex) {
        int temp = rowFirstIndex;
        rowFirstIndex = rowLastIndex;
        rowLastIndex = temp;
    }
    // TODO 
    if (rowNum < rowFirstIndex) {// ?
        return false;
    }
    // 
    for (int i = rowFirstIndex; i <= rowNum; i++) {
        Row row = sheet.getRow(i);
        if (row == null)
            return false;
        Cell cell = row.getCell(columnIndex);
        if (cell == null)
            return false;
        newCellStyle.cloneStyleFrom(cell.getCellStyle());// ??
        newCellStyle.cloneStyleFrom(style); // ??
        cell.setCellStyle(newCellStyle);

    }

    return true;
}

---

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

License:Apache License

/**
 * ????//from  w w  w. j  a v  a2 s  .c o m
 *
 * @param sheet
 * @param rowIndex
 * @param style
 * @return
 */
public static boolean setRowStyle(Sheet sheet, int rowIndex, CellStyle style) {
    if (sheet != null) {
        Row row = sheet.getRow(rowIndex);
        if (row != null) {
            short firstColumnIndex = row.getFirstCellNum();
            short lastColumnIndex = row.getLastCellNum();
            for (short colunmIndex = firstColumnIndex; colunmIndex < lastColumnIndex; colunmIndex++) {
                CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
                Cell cell = row.getCell(colunmIndex);
                if (cell != null) {
                    cellStyle.cloneStyleFrom(cell.getCellStyle());
                    cellStyle.cloneStyleFrom(style);
                    cell.setCellStyle(cellStyle);
                }
            }
        }
    }

    return true;
}

---

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

License:Apache License

/**
 * ???//from ww  w .jav  a2 s. c o m
 *
 * @param sheet
 * @param rowIndex
 * @param columnIndex
 * @param style
 * @return
 */
public static boolean setCellStyle(Sheet sheet, int rowIndex, int columnIndex, CellStyle style) {
    if (sheet == null)
        return false;
    if (rowIndex < 0 || columnIndex <= 0)
        return false;

    Cell cell = sheet.getRow(rowIndex).getCell(columnIndex);
    if (cell == null)
        return false;

    CellStyle newCellStyle = sheet.getWorkbook().createCellStyle();
    newCellStyle.cloneStyleFrom(cell.getCellStyle());
    newCellStyle.cloneStyleFrom(style);
    cell.setCellStyle(newCellStyle);
    return true;
}

---

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

License:Apache License

/**
 * ?/*from  ww w. jav  a  2 s  . c o m*/
 * @param rowIndex
 * @param colIndex
 * @return
 */
protected boolean cellExist(int rowIndex, int colIndex) {
    Sheet sheet = excelSheet.getSheet();
    if (rowIndex < 0 || rowIndex > sheet.getLastRowNum())
        return false;
    Row row = sheet.getRow(rowIndex);
    Cell cell = row.getCell(colIndex);
    return cell != null;
}

---

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

License:Apache License

public void setTitle(String title) {
    Sheet sheet = excelSheet.getSheet();
    Row row = sheet.getRow(0);
    Cell cell = row.getCell(0);//  w  ww . java  2s  .c o  m
    cell.setCellType(Cell.CELL_TYPE_STRING);
    cell.setCellValue(title);
}

---

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

License:Apache License

/**
 * {@inheritDoc}//from www . j  a  v  a  2 s  .c  o m
 */
public void setCellValue(int rowIndex, int colIndex, Object value) {
    Sheet sheet = excelSheet.getSheet();
    if (cellExist(rowIndex, colIndex)) {
        Row row = sheet.getRow(rowIndex);
        Cell cell = row.getCell(colIndex);
        setCellValue(cell, value);
    }
}

---

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

License:Apache License

/**
 * ?//w w w . j ava 2 s .  c o m
 * @param rowIndex
 * @param colIndex
 * @param value
 */
public void setHeader(int rowIndex, int colIndex, Object value) {
    Sheet sheet = excelSheet.getSheet();
    if (cellExist(rowIndex, colIndex)) {
        Cell cell = sheet.getRow(rowIndex).getCell(colIndex);
        setCellValue(cell, value);
    }
}

---

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

License:Apache License

/**
 * ??/*from  w w w. j  a  va  2  s. c  o m*/
 * @param sheet
 * @param columnIndex
 * @param style
 * @return
 */
public boolean setColumnStyle(int columnIndex, int rowFirstIndex, int rowLastIndex, CellStyle style) {
    Sheet sheet = excelSheet.sheet;
    if (sheet == null)
        return false;

    int rowNum = sheet.getLastRowNum();
    CellStyle newCellStyle = sheet.getWorkbook().createCellStyle();
    // ??
    if (rowFirstIndex > rowLastIndex) {
        int temp = rowFirstIndex;
        rowFirstIndex = rowLastIndex;
        rowLastIndex = temp;
    }
    if (rowNum < rowFirstIndex) {// ?
        return false;
    }
    // 
    for (int i = rowFirstIndex; i <= rowNum; i++) {
        Row row = sheet.getRow(i);
        if (row == null)
            return false;
        Cell cell = row.getCell(columnIndex);
        if (cell == null)
            return false;
        newCellStyle.cloneStyleFrom(cell.getCellStyle());// ??
        newCellStyle.cloneStyleFrom(style); //   ??
        cell.setCellStyle(newCellStyle);

    }

    return true;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »