Example usage for org.apache.poi.xssf.usermodel XSSFSheet createRow

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. createRow

Introduction

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

Prototype

@Override
public XSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation Note: If a row already exists at this position, it is removed/overwritten and any existing cell is removed!

Usage

From source file:org.javasoft.ciclope.servlets.ExportMaterialePratica.java

private static synchronized File createExportFile(String fileName, List<Object[]> materialeRows,
        List<Object[]> oreLavorateRows, List<Object[]> lavoriEseguitiRows) {
    //Blank workbook
    XSSFWorkbook workbook = new XSSFWorkbook();
    //Add general style for title headers to the workbook
    XSSFCellStyle titleStyle = createTitleFont(workbook);
    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Dati Pratica");
    int nextRow = 0;
    Row r = sheet.createRow(nextRow++);
    Cell c = r.createCell(0);/*from  w w w.ja  va  2  s  .c om*/
    c.setCellStyle(titleStyle);
    c.setCellValue(EXPORTED_ELEMENT.MATERIALE.getTitle());
    nextRow = AddElements(nextRow, titleStyle, EXPORTED_ELEMENT.MATERIALE, sheet, materialeRows);
    r = sheet.createRow(nextRow++);
    c = r.createCell(0);
    c.setCellStyle(titleStyle);
    c.setCellValue(EXPORTED_ELEMENT.LAVORI_ESEGUITI.getTitle());
    nextRow = AddElements(nextRow, titleStyle, EXPORTED_ELEMENT.LAVORI_ESEGUITI, sheet, lavoriEseguitiRows);
    r = sheet.createRow(nextRow++);
    c = r.createCell(0);
    c.setCellStyle(titleStyle);
    c.setCellValue(EXPORTED_ELEMENT.ORE_LAVORATE.getTitle());
    AddElements(nextRow, titleStyle, EXPORTED_ELEMENT.ORE_LAVORATE, sheet, oreLavorateRows);
    //adjust some known column size
    sheet.autoSizeColumn(1);
    try {
        //Write the workbook in file system
        FileOutputStream out = new FileOutputStream(new File(fileName));
        workbook.write(out);
        out.close();
        File f = new File(fileName);
        if (f.exists()) {
            return f;
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return null;
}

From source file:org.javasoft.ciclope.servlets.ExportMaterialePratica.java

/**
 * Aggiunge il materiale utilizzato nello sheet.
 *
 * @param nextRowFree l'indice zero-based della prima riga da cui scrivere.
 * @param element Il tipo di gruppo di elementi costituenti le righe.
 * @param sheet Il foglio di lavoro su cui aggiungere il contenuto.
 * @param rows Le righe dei dati.// w  w w  .j  av  a  2 s.co  m
 * @return L'indice zero-based della prima riga libera da cui poter
 * continuare a modificare <param>sheet</param>
 */
private static int AddElements(int nextRowFree, XSSFCellStyle titleStyle, EXPORTED_ELEMENT element,
        XSSFSheet sheet, List<Object[]> rows) {
    //Crea l'intestazione...
    int rid = nextRowFree;
    int cid = 0;
    Row head = sheet.createRow(rid);
    Cell headCell = head.createCell(cid);
    headCell.setCellStyle(titleStyle);
    //imposta l'header
    headCell.setCellValue(element.getTitle());
    //Crea lo style di default delle celle
    XSSFCellStyle defStyle = sheet.getWorkbook().createCellStyle();
    defStyle.setAlignment(CellStyle.ALIGN_LEFT);
    //metti i valori
    SimpleDateFormat sdf = new SimpleDateFormat("EEEEEEEEEEE dd-MM-yyyy", Locale.ITALY);
    //Iterate over data and write to sheet
    try {
        for (Object[] objs : rows) {
            Row row = sheet.createRow(rid++);
            int cellnum = 0;
            if (objs != null) {
                for (Object obj : objs) {
                    Cell cell = row.createCell(cellnum++);
                    //sets left alignement
                    cell.setCellStyle(defStyle);
                    if (obj instanceof String) {
                        cell.setCellValue((String) obj);
                    } else if (obj instanceof Float) {
                        cell.setCellValue((Float) obj);
                    } else if (obj instanceof Integer) {
                        cell.setCellValue((Integer) obj);
                    } else if (obj instanceof BigDecimal) {
                        cell.setCellValue(((BigDecimal) obj).floatValue());
                    } else if (obj instanceof Date) {
                        cell.setCellValue(sdf.format((Date) obj));
                    }
                }
            }
        }
    } catch (ClassCastException ex) {
        for (Object obj : rows) {
            Row row = sheet.createRow(rid++);
            int cellnum = 0;
            Cell cell = row.createCell(cellnum++);
            //sets left alignement
            cell.setCellStyle(defStyle);
            if (obj instanceof String) {
                cell.setCellValue((String) obj);
            } else if (obj instanceof Float) {
                cell.setCellValue((Float) obj);
            } else if (obj instanceof Integer) {
                cell.setCellValue((Integer) obj);
            } else if (obj instanceof BigDecimal) {
                cell.setCellValue(((BigDecimal) obj).floatValue());
            } else if (obj instanceof Date) {
                cell.setCellValue(sdf.format((Date) obj));
            }
        }
    }
    //lascia alcune celle per spaziare i gruppi di elementi.
    sheet.createRow(rid++);
    sheet.createRow(rid++);
    return rid++;
}

From source file:org.jboss.windup.reporting.spreadsheet.ScorecardReporter.java

License:Open Source License

private static void appendTotalRow(XSSFWorkbook wb, XSSFSheet sheet, int rowNum) {
    Font boldFont = wb.createFont();
    boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldFont.setColor((short) 0x0);

    XSSFCellStyle commentCell = wb.createCellStyle();
    commentCell.setBorderTop(CellStyle.BORDER_THIN);

    XSSFCellStyle totalCell = wb.createCellStyle();
    totalCell.setBorderTop(CellStyle.BORDER_THIN);
    totalCell.setFont(boldFont);//from w ww. j  a  v  a 2 s. co  m

    XSSFCellStyle totalCellRight = wb.createCellStyle();
    totalCellRight.setBorderTop(CellStyle.BORDER_THIN);
    totalCellRight.setAlignment(HorizontalAlignment.RIGHT);
    totalCellRight.setFont(boldFont);

    XSSFRow row = sheet.createRow(rowNum);
    XSSFCell t1 = row.createCell(0);
    t1.setCellValue("Total:");
    t1.setCellStyle(totalCellRight);

    XSSFCell t2 = row.createCell(1);
    t2.setCellFormula("SUM(B1:B" + rowNum + ")*" + TEST_PADDING);
    t2.setCellStyle(totalCell);

    XSSFCell t3 = row.createCell(2);
    t3.setCellStyle(totalCell);

    XSSFCell t4 = row.createCell(3);
    t4.setCellValue("Total with Testing & App Migration Factors");
    t4.setCellStyle(commentCell);
}

From source file:org.jboss.windup.reporting.spreadsheet.ScorecardReporter.java

License:Open Source License

private static void appendTitleRow(XSSFWorkbook wb, XSSFSheet sheet, int rowNum) {
    XSSFCellStyle titleCell = wb.createCellStyle();
    Color titleCellGrey = new Color(0xECECEC);
    XSSFColor color = new XSSFColor(titleCellGrey);
    titleCell.setFillForegroundColor(color);
    titleCell.setBorderBottom(CellStyle.BORDER_MEDIUM);
    titleCell.setFillPattern(CellStyle.SOLID_FOREGROUND);

    Font titleFormat = wb.createFont();
    titleFormat.setBoldweight(Font.BOLDWEIGHT_BOLD);
    titleFormat.setColor((short) 0x0);
    titleCell.setFont(titleFormat);//from  ww  w.j  a v a  2s.  c  om

    XSSFRow row = sheet.createRow(rowNum);
    XSSFCell t1 = row.createCell(0);
    t1.setCellValue("Application Migration Estimate");
    t1.setCellStyle(titleCell);

    XSSFCell t2 = row.createCell(1);
    t2.setCellValue("Effort (Points)");
    t2.setCellStyle(titleCell);

    XSSFCell t3 = row.createCell(2);
    t3.setCellStyle(titleCell);

    XSSFCell t4 = row.createCell(3);
    t4.setCellValue("Notes");
    t4.setCellStyle(titleCell);
}

From source file:org.jboss.windup.reporting.spreadsheet.ScorecardReporter.java

License:Open Source License

private static void appendMentoringTitleRow(XSSFWorkbook wb, XSSFSheet sheet, int rowNum) {
    XSSFCellStyle titleCell = wb.createCellStyle();
    Color titleCellGrey = new Color(0xECECEC);
    XSSFColor color = new XSSFColor(titleCellGrey);
    titleCell.setFillForegroundColor(color);
    titleCell.setBorderBottom(CellStyle.BORDER_MEDIUM);
    titleCell.setBorderTop(CellStyle.BORDER_MEDIUM);
    titleCell.setFillPattern(CellStyle.SOLID_FOREGROUND);

    Font titleFormat = wb.createFont();
    titleFormat.setBoldweight(Font.BOLDWEIGHT_BOLD);
    titleFormat.setColor((short) 0x0);
    titleCell.setFont(titleFormat);/*from   w ww  . ja va 2s .c om*/

    XSSFRow row = sheet.createRow(rowNum);
    XSSFCell t1 = row.createCell(0);
    t1.setCellValue("Migration Service Estimate");
    t1.setCellStyle(titleCell);

    XSSFCell t2 = row.createCell(1);
    t2.setCellStyle(titleCell);

    XSSFCell t3 = row.createCell(2);
    t3.setCellStyle(titleCell);

    XSSFCell t4 = row.createCell(3);
    t4.setCellStyle(titleCell);
}

From source file:org.jboss.windup.reporting.spreadsheet.ScorecardReporter.java

License:Open Source License

private static void appendMentoringTotalRow(XSSFWorkbook wb, XSSFSheet sheet, int rowNum, int start, int end) {
    Font boldFont = wb.createFont();
    boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldFont.setColor((short) 0x0);

    XSSFCellStyle commentCell = wb.createCellStyle();
    commentCell.setBorderTop(CellStyle.BORDER_THIN);

    XSSFCellStyle totalCell = wb.createCellStyle();
    totalCell.setBorderTop(CellStyle.BORDER_THIN);
    totalCell.setFont(boldFont);/*from   w w  w . j av a2s.c  o  m*/

    XSSFCellStyle totalCellRight = wb.createCellStyle();
    totalCellRight.setBorderTop(CellStyle.BORDER_THIN);
    totalCellRight.setAlignment(HorizontalAlignment.RIGHT);
    totalCellRight.setFont(boldFont);

    XSSFRow row = sheet.createRow(rowNum);
    XSSFCell t1 = row.createCell(0);
    t1.setCellValue("Total:");
    t1.setCellStyle(totalCellRight);

    XSSFCell t2 = row.createCell(1);
    t2.setCellFormula("SUM(B" + start + ":B" + end + ")");
    t2.setCellStyle(totalCell);

    XSSFCell t3 = row.createCell(2);
    t3.setCellStyle(totalCell);

    XSSFCell t4 = row.createCell(3);
    t4.setCellStyle(commentCell);
}

From source file:org.jboss.windup.reporting.spreadsheet.ScorecardReporter.java

License:Open Source License

private static void appendNotesRow(XSSFWorkbook wb, XSSFSheet sheet, int rowNum, String app, double effort,
        String notes) {//from ww w  .jav a2  s .co  m
    XSSFRow row = sheet.createRow(rowNum);
    XSSFCell t1 = row.createCell(0);
    t1.setCellValue(app);

    XSSFCell t2 = row.createCell(1);
    XSSFCellStyle t2s = wb.createCellStyle();
    t2s.setAlignment(HorizontalAlignment.RIGHT);
    t2.setCellStyle(t2s);

    t2.setCellValue(effort);
    row.createCell(2);

    XSSFCell t4 = row.createCell(3);
    t4.setCellValue(notes);
}

From source file:org.jmesa.view.excel.Excel2007View.java

License:Apache License

@Override
public Object render() {

    XSSFWorkbook workbook = new XSSFWorkbook();
    Table table = this.getTable();
    String caption = table.getCaption();
    if (!StringUtils.hasText(caption)) {
        caption = "JMesa Export";
    }//from  ww  w .j  a  v  a  2s  .co  m
    XSSFSheet sheet = workbook.createSheet(caption);

    Row row = table.getRow();
    row.getRowRenderer();
    List<Column> columns = table.getRow().getColumns();

    // renderer header
    XSSFRow hssfRow = sheet.createRow(0);
    int columncount = 0;
    for (Column col : columns) {
        XSSFCell cell = hssfRow.createCell(columncount++);
        cell.setCellValue(new XSSFRichTextString(col.getTitle()));
    }

    // renderer body
    Collection<?> items = getCoreContext().getPageItems();
    int rowcount = 1;
    for (Object item : items) {
        XSSFRow r = sheet.createRow(rowcount++);
        columncount = 0;
        for (Column col : columns) {
            XSSFCell cell = r.createCell(columncount++);
            Object value = col.getCellRenderer().render(item, rowcount);
            if (value == null) {
                value = "";
            }

            if (value instanceof Number) {
                Double number = Double.valueOf(value.toString());
                cell.setCellValue(number);
            } else {
                cell.setCellValue(new XSSFRichTextString(value.toString()));
            }
        }
    }
    return workbook;
}

From source file:org.kuali.test.runner.output.PoiHelper.java

License:Educational Community License

private void copySheets(XSSFSheet newSheet, XSSFSheet sheet) {
    int maxColumnNum = 0;
    Map<Integer, XSSFCellStyle> styleMap = new HashMap<Integer, XSSFCellStyle>();

    int mergedReqionsCount = sheet.getNumMergedRegions();

    for (int i = 0; i < mergedReqionsCount; ++i) {
        newSheet.addMergedRegion(sheet.getMergedRegion(i));
    }//  www  .ja  v  a  2 s.  co m

    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
        XSSFRow srcRow = sheet.getRow(i);
        XSSFRow destRow = newSheet.createRow(i);
        if (srcRow != null) {
            copyRow(srcRow, destRow, styleMap);
            if (srcRow.getLastCellNum() > maxColumnNum) {
                maxColumnNum = srcRow.getLastCellNum();
            }
        }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
        newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
    }
}

From source file:org.mifosplatform.infrastructure.dataexport.helper.XlsFileHelper.java

License:Mozilla Public License

public static void createFile(final SqlRowSet sqlRowSet, final File file,
        final HashMap<Long, CodeValueData> codeValueMap, final HashMap<Long, AppUserData> appUserMap,
        final DataExportCoreTable coreTable) {
    try {//  ww w . ja  v a 2 s.c o  m
        final SqlRowSetMetaData sqlRowSetMetaData = sqlRowSet.getMetaData();
        final int columnCount = sqlRowSetMetaData.getColumnCount();

        // Create a new spreadsheet workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        // Create a blank sheet for the workbook
        XSSFSheet sheet = workbook.createSheet();
        // create a new cell style object
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        // create a new data format object 
        XSSFDataFormat dataFormat = workbook.createDataFormat();

        int rowIndex = 0;
        int columnIndex = 0;
        Row row = sheet.createRow(rowIndex++);

        for (int i = 1; i <= columnCount; i++) {
            // create a new cell for each columns for the header row
            Cell cell = row.createCell(columnIndex++);
            // get the column label of the dataset
            String columnLabel = DataExportUtils
                    .createHumanReadableTableColumnLabel(sqlRowSetMetaData.getColumnLabel(i), coreTable);
            // set the value of the cell
            cell.setCellValue(WordUtils.capitalize(columnLabel));
        }

        while (sqlRowSet.next()) {
            columnIndex = 0;
            row = sheet.createRow(rowIndex++);

            for (int i = 1; i <= columnCount; i++) {
                Cell cell = row.createCell(columnIndex++);
                String columnTypeName = sqlRowSetMetaData.getColumnTypeName(i);
                MysqlDataType mysqlDataType = MysqlDataType.newInstance(columnTypeName);
                String columnValue = sqlRowSet.getString(i);
                String columnName = sqlRowSetMetaData.getColumnName(i);

                // replace code value id with the code value name
                AbstractMap.SimpleEntry<String, MysqlDataType> columnValueDataType = DataExportUtils
                        .replaceCodeValueIdWithValue(codeValueMap, columnName, columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                // replace app user id with respective username
                columnValueDataType = DataExportUtils.replaceAppUserIdWithUserName(appUserMap, columnName,
                        columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                if (columnValue != null) {
                    switch (mysqlDataType.getCategory()) {
                    case NUMERIC:

                        // TINYINT(1), BIT(1), etc are also treated as an alias for a BOOL in MySQL in certain versions of the JDBC connector, option tinyInt1isBit
                        // See: http://stackoverflow.com/questions/16798744/why-does-tinyint1-function-as-a-boolean-but-int1-does-not/35488212#35488212
                        if (sqlRowSetMetaData.getPrecision(i) == 1
                                && (columnValue.equals("true") || columnValue.equals("false"))) {
                            // Handle the cell as string, it is already a casted boolean:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(columnValue);

                        } else {
                            double numberAsDouble = Double.parseDouble(columnValue);

                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(numberAsDouble);
                        }
                        break;

                    case DATE_TIME:
                        DateFormat dateFormat;
                        Date date;

                        switch (mysqlDataType) {
                        case DATE:
                        case DATETIME:
                            String mysqlDateFormat = "yyyy-MM-dd";
                            String excelDateFormat = "MM/DD/YYYY";

                            if (mysqlDataType.equals(MysqlDataType.DATETIME)) {
                                mysqlDateFormat = "yyyy-MM-dd HH:mm:ss";
                                excelDateFormat = "MM/DD/YYYY HH:MM:SS";
                            }

                            dateFormat = new SimpleDateFormat(mysqlDateFormat);
                            date = dateFormat.parse(columnValue);

                            cellStyle.setDataFormat(dataFormat.getFormat(excelDateFormat));

                            cell.setCellValue(date);
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellStyle(cellStyle);
                            break;

                        default:
                            cell.setCellValue(columnValue);
                            break;
                        }
                        break;

                    default:
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(columnValue);
                        break;
                    }

                } else {
                    cell.setCellValue(columnValue);
                }
            }
        }

        //Write the workbook in file system
        FileOutputStream fileOutputStream = new FileOutputStream(file);

        workbook.write(fileOutputStream);
        fileOutputStream.close();

    } catch (Exception exception) {
        exception.printStackTrace();
    }
}