Example usage for org.apache.poi.ss.usermodel Sheet 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.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 * Use Excel conditional formatting to shade alternating rows on the worksheet
 *///from   ww  w  . ja va  2  s .co m
static void shadeAlt(Sheet sheet) {
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:Z100") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.createRow(0).createCell(1).setCellValue("Shade Alternating Rows");
    sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is  =MOD(ROW(),2)   (Light Green Fill)");
}

---

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 * You can use Excel conditional formatting to shade bands of rows on the worksheet. 
 * In this example, 3 rows are shaded light grey, and 3 are left with no shading.
 * In the MOD function, the total number of rows in the set of banded rows (6) is entered.
 *//*from w ww.jav a  2 s  .  c  om*/
static void shadeBands(Sheet sheet) {
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),6)<3");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:Z100") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.createRow(0).createCell(1).setCellValue("Shade Bands of Rows");
    sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is  =MOD(ROW(),6)<2   (Light Grey Fill)");
}

---

From source file:com.wantdo.stat.excel.poi_src.formula.SettingExternalFunction.java

License:Apache License

public static void main(String[] args) throws IOException {

    Workbook wb = new XSSFWorkbook(); // or new HSSFWorkbook()

    // register the add-in
    wb.addToolPack(new BloombergAddIn());

    Sheet sheet = wb.createSheet();
    Row row = sheet.createRow(0);
    row.createCell(0).setCellFormula("BDP(\"GOOG Equity\",\"CHG_PCT_YTD\")/100");
    row.createCell(1).setCellFormula(/*from www . ja v  a2 s .c  o  m*/
            "BDH(\"goog us equity\",\"EBIT\",\"1/1/2005\",\"12/31/2009\",\"per=cy\",\"curr=USD\") ");
    row.createCell(2).setCellFormula("BDS(\"goog us equity\",\"top_20_holders_public_filings\") ");

    FileOutputStream out = new FileOutputStream("bloomberg-demo.xlsx");
    wb.write(out);
    out.close();

}

---

From source file:com.wantdo.stat.excel.poi_src.SSPerformanceTest.java

License:Apache License

public static void main(String[] args) {
    if (args.length != 4)
        usage("need four command arguments");

    String type = args[0];/*from  www.ja v  a2  s  .  c  o  m*/
    long timeStarted = System.currentTimeMillis();
    Workbook workBook = createWorkbook(type);
    boolean isHType = workBook instanceof HSSFWorkbook;

    int rows = parseInt(args[1], "Failed to parse rows value as integer");
    int cols = parseInt(args[2], "Failed to parse cols value as integer");
    boolean saveFile = parseInt(args[3], "Failed to parse saveFile value as integer") != 0;

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

    Sheet sheet = workBook.createSheet("Main Sheet");

    Cell headerCell = sheet.createRow(0).createCell(0);
    headerCell.setCellValue("Header text is spanned across multiple cells");
    headerCell.setCellStyle(styles.get("header"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1"));

    int sheetNo = 0;
    int rowIndexInSheet = 1;
    double value = 0;
    Calendar calendar = Calendar.getInstance();
    for (int rowIndex = 0; rowIndex < rows; rowIndex++) {
        if (isHType && sheetNo != rowIndex / 0x10000) {
            sheet = workBook.createSheet("Spillover from sheet " + (++sheetNo));
            headerCell.setCellValue("Header text is spanned across multiple cells");
            headerCell.setCellStyle(styles.get("header"));
            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1"));
            rowIndexInSheet = 1;
        }

        Row row = sheet.createRow(rowIndexInSheet);
        for (int colIndex = 0; colIndex < cols; colIndex++) {
            Cell cell = row.createCell(colIndex);
            String address = new CellReference(cell).formatAsString();
            switch (colIndex) {
            case 0:
                // column A: default number format
                cell.setCellValue(value++);
                break;
            case 1:
                // column B: #,##0
                cell.setCellValue(value++);
                cell.setCellStyle(styles.get("#,##0.00"));
                break;
            case 2:
                // column C: $#,##0.00
                cell.setCellValue(value++);
                cell.setCellStyle(styles.get("$#,##0.00"));
                break;
            case 3:
                // column D: red bold text on yellow background
                cell.setCellValue(address);
                cell.setCellStyle(styles.get("red-bold"));
                break;
            case 4:
                // column E: boolean
                // TODO booleans are shown as 1/0 instead of TRUE/FALSE
                cell.setCellValue(rowIndex % 2 == 0);
                break;
            case 5:
                // column F:  date / time
                cell.setCellValue(calendar);
                cell.setCellStyle(styles.get("m/d/yyyy"));
                calendar.roll(Calendar.DAY_OF_YEAR, -1);
                break;
            case 6:
                // column F: formula
                // TODO formulas are not yet supported  in SXSSF
                //cell.setCellFormula("SUM(A" + (rowIndex+1) + ":E" + (rowIndex+1)+ ")");
                //break;
            default:
                cell.setCellValue(value++);
                break;
            }
        }
        rowIndexInSheet++;
    }
    if (saveFile) {
        String fileName = type + "_" + rows + "_" + cols + "." + getFileSuffix(args[0]);
        try {
            FileOutputStream out = new FileOutputStream(fileName);
            workBook.write(out);
            out.close();
        } catch (IOException ioe) {
            System.err
                    .println("Error: failed to write to file \"" + fileName + "\", reason=" + ioe.getMessage());
        }
    }
    long timeFinished = System.currentTimeMillis();
    System.out.println("Elapsed " + (timeFinished - timeStarted) / 1000 + " seconds");
}

---

From source file:com.waveconn.Excel2MySQL.java

License:Apache License

private void saveError() {
    String error_file;/*from  www .j a  v  a2s  . c o m*/
    long now = System.currentTimeMillis();
    if (excel_error_file_path.endsWith(".xlsx"))
        error_file = excel_error_file_path.split("[.]")[0] + "_" + now + ".xlsx";
    else
        error_file = excel_error_file_path + "_" + now + ".xlsx";

    try (Workbook wb = new XSSFWorkbook(); FileOutputStream out = new FileOutputStream(error_file)) {

        Sheet sheet = wb.createSheet("Errors");

        for (int i = 0; i < errorRows.size(); i++) {
            Row row = sheet.createRow(i);
            ArrayList<String> rowData = errorRows.get(i);
            for (int j = 0; j < rowData.size(); j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(rowData.get(j));
            }
        }

        // Write the output to a file
        wb.write(out);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    System.out.println(errorRows.size() + " invalid rows found. Saved to " + error_file);
}

---

From source file:com.webbfontaine.valuewebb.report.AirCargoReporter.java

License:Open Source License

private int createCells(ResultSet rs, Map<String, CellStyle> cellStyleMap, Sheet sheet) throws SQLException {
    int rowNumber = DEFAULT_STARTING_ROW_NUMBER;
    while (rs.next()) {
        Row sheetRow = sheet.createRow(rowNumber);
        createCell(sheetRow, 'A', rs.getBigDecimal("tt_id"), cellStyleMap.get("GENERAL_CENTER_TEXT_STYLE"));
        createCell(sheetRow, 'B', rs.getDate("tt_dat"), cellStyleMap.get("GENERAL_DATE_STYLE"));
        createCell(sheetRow, 'C', rs.getString("status"), cellStyleMap.get("GENERAL_CENTER_TEXT_STYLE"));
        createCell(sheetRow, 'D', rs.getString("trs_ship_mod"), cellStyleMap.get("GENERAL_CENTER_TEXT_STYLE"));
        createCell(sheetRow, 'E', rs.getString("imp_nam"), cellStyleMap.get("GENERAL_LEFT_TEXT_STYLE"));
        createCell(sheetRow, 'F', rs.getString("app_nam"), cellStyleMap.get("GENERAL_LEFT_TEXT_STYLE"));
        createCell(sheetRow, 'G', rs.getString("fcvr_num"), cellStyleMap.get("GENERAL_CENTER_TEXT_STYLE"));
        createCell(sheetRow, 'H', rs.getString("trs_bl_num"), cellStyleMap.get("GENERAL_CENTER_TEXT_STYLE"));
        createCell(sheetRow, 'I', rs.getString("trs_gds_dsc"), cellStyleMap.get("GENERAL_LEFT_TEXT_STYLE"));
        createCell(sheetRow, 'J', rs.getString("cty_supp"), cellStyleMap.get("GENERAL_LEFT_TEXT_STYLE"));
        rowNumber++;/*from  w  w  w . ja va2s. c  om*/
    }
    return rowNumber;
}

---

From source file:com.webbfontaine.valuewebb.report.fcvrprocessing.FcvrTimeReportEngine.java

License:Open Source License

private static int createRows(HSSFWorkbook workbook, ResultSet resultSet) throws SQLException {
    Sheet sheet1 = workbook.getSheetAt(0);
    Sheet sheet2 = workbook.getSheetAt(1);
    int rowNumber = DEFAULT_STARTING_ROW_NUMBER;
    Map<CellType, CellStyle> cellStyleMap = retrieveNecessaryStyles(workbook);

    ProcessingTime processingTime = new ProcessingTime();
    while (resultSet.next()) {
        Row sheet1Row = sheet1.createRow(rowNumber);
        Row sheet2Row = sheet2.createRow(rowNumber);
        processingTime.calculate(resultSet.getLong("tt_id"));

        CellStyleUtils.createCell(sheet1Row, 'A', resultSet.getString("fcvr_num"),
                cellStyleMap.get(CellType.TEXT_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'B', resultSet.getString("imp_nam"),
                cellStyleMap.get(CellType.TEXT_LEFT));
        CellStyleUtils.createCell(sheet1Row, 'C', resultSet.getBigDecimal("fob_assessed"),
                cellStyleMap.get(CellType.NUMBER_RIGHT));
        CellStyleUtils.createCell(sheet1Row, 'D', resultSet.getString("inv_cur"),
                cellStyleMap.get(CellType.TEXT_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'E', resultSet.getDate("tt_dat"),
                cellStyleMap.get(CellType.DATE_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'F', resultSet.getDate("fcvr_dat"),
                cellStyleMap.get(CellType.DATE_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'G', resultSet.getInt("num_of_queries"),
                cellStyleMap.get(CellType.DIGIT_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'H', resultSet.getInt("num_of_holidays"),
                cellStyleMap.get(CellType.DIGIT_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'I', resultSet.getInt("num_of_weekends"),
                cellStyleMap.get(CellType.DIGIT_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'J',
                Utils.convertProcessingTimeFromLongToString(processingTime.getProcessingTimeWithQuery()
                        - processingTime.getProcessingTimeWithoutQuery()),
                cellStyleMap.get(CellType.TEXT_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'K',
                Utils.convertProcessingTimeFromLongToString(processingTime.getProcessingTimeWithQuery()),
                cellStyleMap.get(CellType.TEXT_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'L', resultSet.getString("status"),
                cellStyleMap.get(CellType.TEXT_CENTER));

        CellStyleUtils.createCell(sheet2Row, 'A', resultSet.getString("fcvr_num"),
                cellStyleMap.get(CellType.TEXT_CENTER));
        CellStyleUtils.createCell(sheet2Row, 'B', resultSet.getString("containers"),
                cellStyleMap.get(CellType.TEXT_LEFT));

        rowNumber++;//from ww  w.  j a v  a  2  s.  com
    }

    return rowNumber;
}

---

From source file:com.webbfontaine.valuewebb.report.ScanSelectivityReporter.java

License:Open Source License

private int createCells(ResultSet rs, Map<String, CellStyle> cellStyleMap, Sheet sheet) throws SQLException {
    int rowNumber = DEFAULT_STARTING_ROW_NUMBER;
    while (rs.next()) {
        Row sheetRow = sheet.createRow(rowNumber);
        createCell(sheetRow, 'A', rs.getBigDecimal("tt_id"), cellStyleMap.get("GENERAL_CENTER_TEXT_STYLE"));
        createCell(sheetRow, 'B', rs.getString("crt_cod1"), cellStyleMap.get("GENERAL_LEFT_TEXT_STYLE"));
        createCell(sheetRow, 'C', rs.getString("crt_cod2"), cellStyleMap.get("GENERAL_CENTER_TEXT_STYLE"));
        createCell(sheetRow, 'D', rs.getBigDecimal("num_of_cont"), cellStyleMap.get("CENTER_DIGIT_CELL_STYLE"));
        createCell(sheetRow, 'E', rs.getString("dai_num"), cellStyleMap.get("GENERAL_LEFT_TEXT_STYLE"));
        createCell(sheetRow, 'F', rs.getString("bl_num"), cellStyleMap.get("GENERAL_LEFT_TEXT_STYLE"));
        createCell(sheetRow, 'G', rs.getString("prod_description"),
                cellStyleMap.get("GENERAL_LEFT_TEXT_STYLE"));
        createCell(sheetRow, 'H', rs.getBigDecimal("declared_quantity"),
                cellStyleMap.get("GENERAL_RIGHT_NUMBER_STYLE"));
        createCell(sheetRow, 'I', rs.getString("d_unit"), cellStyleMap.get("GENERAL_CENTER_TEXT_STYLE"));
        createCell(sheetRow, 'J', rs.getBigDecimal("trs_grs_wgt"),
                cellStyleMap.get("GENERAL_CENTER_NUMBER_STYLE"));
        createCell(sheetRow, 'K', rs.getBigDecimal("trs_net_wgt"),
                cellStyleMap.get("GENERAL_CENTER_NUMBER_STYLE"));
        createCell(sheetRow, 'L', rs.getString("hit_date"), cellStyleMap.get("GENERAL_CENTER_TEXT_STYLE"));
        rowNumber++;//from w  ww.  j ava  2  s  . co m
    }
    return rowNumber;
}

---

From source file:com.wuliu.biz.util.export.strategy.CarIndexExport.java

License:Open Source License

private void fillSheet(Sheet sheet, List<WuliuMergedOrderModel> mergedOrderModels) {
    int index = 0;
    for (WuliuMergedOrderModel item : mergedOrderModels) {
        index++;//from w  w w.  j  a  v  a 2 s.c  o  m
        Row row = sheet.getRow(index);
        if (row == null) {
            row = sheet.createRow(index);
        }

        Cell cell = row.getCell(0);
        if (cell == null) {
            cell = row.createCell(0);
        }
        cell.setCellValue(item.getOrderNumber());
    }
}

---

From source file:com.ykun.commons.utils.excel.ExcelUtils.java

License:Apache License

/**
 * xlsheaders/*  ww w . ja  v a2s  . c  o m*/
 *
 * @param list    the list
 * @param headers the headers
 * @param out     the out
 */
public static <T> void export(List<T> list, List<String> headers, OutputStream out) {
    // ?
    if (list == null || list.size() == 0) {
        return;
    }

    try {
        Workbook workbook = new XSSFWorkbook(); // XSSFWorkbook
        Sheet sheet = workbook.createSheet(); // ?Sheet

        // ?
        int rowNo = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        if (headers != null && headers.size() > 0) {
            Row row = sheet.createRow(rowNo++);
            for (int i = 0; i < headers.size(); i++) {
                Cell cell = row.createCell(i);
                cell.setCellStyle(headerStyle);
                cell.setCellValue(headers.get(i));
            }
        }

        // ?
        CellStyle normalStyle = createNormalStyle(workbook);
        for (T t : list) {
            Row row = sheet.createRow(rowNo++);
            Field[] fields = t.getClass().getDeclaredFields();
            int column = 0;
            for (int i = 0; i < fields.length; i++) {
                Object value;
                Field field = fields[i];
                ExcelField excelField = field.getAnnotation(ExcelField.class);
                if (excelField != null && !excelField.ignore()) {
                    String methodName = PREFIX_GETTER + StringUtils.capitalize(field.getName()); // get???getisEnable?
                    Method method = t.getClass().getMethod(methodName, new Class[] {});
                    value = method.invoke(t, new Object[] {});
                } else if (excelField != null && excelField.ignore()) {
                    continue;
                } else {
                    String methodName = PREFIX_GETTER + StringUtils.capitalize(field.getName()); // get???getisEnable?
                    Method method = t.getClass().getMethod(methodName, new Class[] {});
                    value = method.invoke(t, new Object[] {});
                }
                row.setRowStyle(normalStyle);
                addCell(row, column++, value, excelField);
            }
        }
        workbook.write(out);
    } catch (Exception e) {
        logger.error("Export error:", e);
        throw new RuntimeException(e);
    }
}

---

• «
• 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
• »