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.waku.mmdataextract.IMEIMerge.java

License:Open Source License

@SuppressWarnings("unchecked")
public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("IMEI");
    int colIndex = 0;
    int rowIndex = 0;
    Row row = sheet.createRow(rowIndex++);
    row.createCell(colIndex++).setCellValue("");
    row.createCell(colIndex++).setCellValue("?");
    row.createCell(colIndex++).setCellValue("?");
    row.createCell(colIndex++).setCellValue("IMEI");
    int i = 0;//w  ww .ja va  2s. c  o m
    InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream("Brand.xml");
    for (Element brand : (List<Element>) new SAXReader().read(in).selectNodes("/brand/option")) {
        String brandName = brand.getText();
        System.out.println(brandName);
        File file = new File("output/" + brandName + ".csv");
        if (file.exists()) {
            System.out.println("Found file ->" + file.getAbsolutePath());
            BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(file)));
            String line = null;
            boolean noDataAvailable = true;
            while ((line = br.readLine()) != null) {
                if (line.indexOf(",") != -1) {
                    rowIndex = addRow(line.split(","), sheet, rowIndex);
                    noDataAvailable = false;
                }
            }
            if (noDataAvailable) {
                System.out.println("No data for " + brandName);
                rowIndex = addRow(new String[] { "", brandName, "N/A", "N/A" }, sheet, rowIndex);
            }
            br.close();
        } else {
            System.out.println("No file for " + brandName);
            rowIndex = addRow(new String[] { "", brandName, "N/A", "N/A" }, sheet, rowIndex);
        }
        System.out.println(i++);
    }
    System.out.println(i);
    System.out.println(" ---------------------------------- File saved!");
    wb.write(new FileOutputStream(new File("IMEI.xls")));
}

---

From source file:com.waku.mmdataextract.IMEIMerge.java

License:Open Source License

private static int addRow(String[] line, Sheet sheet, int rowIndex) {
    System.out.println(line[0] + "," + line[1] + "," + line[2] + "," + line[3]);
    int colIndex = 0;
    Row row = sheet.createRow(rowIndex++);
    row.createCell(colIndex++).setCellValue(line[0]);
    row.createCell(colIndex++).setCellValue(line[1]);
    row.createCell(colIndex++).setCellValue(line[2]);
    row.createCell(colIndex++).setCellValue(line[3]);
    return rowIndex;
}

---

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

License:Apache License

public static void main(String[] args) throws IOException {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();

    Sheet sheet = wb.createSheet();
    Row row = sheet.createRow((short) 2);
    row.setHeightInPoints(30);/*w  w w  .ja  v  a2  s.  c  om*/
    for (int i = 0; i < 8; i++) {
        //column width is set in units of 1/256th of a character width
        sheet.setColumnWidth(i, 256 * 15);
    }

    createCell(wb, row, (short) 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM);
    createCell(wb, row, (short) 1, CellStyle.ALIGN_CENTER_SELECTION, CellStyle.VERTICAL_BOTTOM);
    createCell(wb, row, (short) 2, CellStyle.ALIGN_FILL, CellStyle.VERTICAL_CENTER);
    createCell(wb, row, (short) 3, CellStyle.ALIGN_GENERAL, CellStyle.VERTICAL_CENTER);
    createCell(wb, row, (short) 4, CellStyle.ALIGN_JUSTIFY, CellStyle.VERTICAL_JUSTIFY);
    createCell(wb, row, (short) 5, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_TOP);
    createCell(wb, row, (short) 6, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_TOP);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("ss-example-align.xlsx");
    wb.write(fileOut);
    fileOut.close();
}

---

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

License:Apache License

/**
 * Highlight cells based on their values
 *///w  w  w  .ja  va  2  s  . c o  m
static void sameCell(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue(84);
    sheet.createRow(1).createCell(0).setCellValue(74);
    sheet.createRow(2).createCell(0).setCellValue(50);
    sheet.createRow(3).createCell(0).setCellValue(51);
    sheet.createRow(4).createCell(0).setCellValue(49);
    sheet.createRow(5).createCell(0).setCellValue(41);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Cell Value Is   greater than  70   (Blue Fill)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "70");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    // Condition 2: Cell Value Is  less than      50   (Green Fill)
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "50");
    PatternFormatting fill2 = rule2.createPatternFormatting();
    fill2.setFillBackgroundColor(IndexedColors.GREEN.index);
    fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

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

    sheetCF.addConditionalFormatting(regions, rule1, rule2);

    sheet.getRow(0).createCell(2).setCellValue("<== Condition 1: Cell Value Is greater than 70 (Blue Fill)");
    sheet.getRow(4).createCell(2).setCellValue("<== Condition 2: Cell Value Is less than 50 (Green Fill)");
}

---

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

License:Apache License

/**
 * Highlight multiple cells based on a formula
 */// w ww  . ja  v  a 2 s.c  o m
static void multiCell(Sheet sheet) {
    // header row
    Row row0 = sheet.createRow(0);
    row0.createCell(0).setCellValue("Units");
    row0.createCell(1).setCellValue("Cost");
    row0.createCell(2).setCellValue("Total");

    Row row1 = sheet.createRow(1);
    row1.createCell(0).setCellValue(71);
    row1.createCell(1).setCellValue(29);
    row1.createCell(2).setCellValue(2059);

    Row row2 = sheet.createRow(2);
    row2.createCell(0).setCellValue(85);
    row2.createCell(1).setCellValue(29);
    row2.createCell(2).setCellValue(2059);

    Row row3 = sheet.createRow(3);
    row3.createCell(0).setCellValue(71);
    row3.createCell(1).setCellValue(29);
    row3.createCell(2).setCellValue(2059);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =$B2>75   (Blue Fill)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("$A2>75");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:C4") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(2).createCell(4).setCellValue("<== Condition 1: Formula Is =$B2>75   (Blue Fill)");
}

---

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

License:Apache License

/**
 *  Use Excel conditional formatting to check for errors,
 *  and change the font colour to match the cell colour.
 *  In this example, if formula result is  #DIV/0! then it will have white font colour.
 *///from  www  .j  ava 2  s . com
static void errors(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue(84);
    sheet.createRow(1).createCell(0).setCellValue(0);
    sheet.createRow(2).createCell(0).setCellFormula("ROUND(A1/A2,0)");
    sheet.createRow(3).createCell(0).setCellValue(0);
    sheet.createRow(4).createCell(0).setCellFormula("ROUND(A6/A4,0)");
    sheet.createRow(5).createCell(0).setCellValue(41);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =ISERROR(C2)   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("ISERROR(A1)");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontColorIndex(IndexedColors.WHITE.index);

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

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(2).createCell(1).setCellValue(
            "<== The error in this cell is hidden. Condition: Formula Is   =ISERROR(C2)   (White Font)");
    sheet.getRow(4).createCell(1).setCellValue(
            "<== The error in this cell is hidden. Condition: Formula Is   =ISERROR(C2)   (White Font)");
}

---

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

License:Apache License

/**
 * Use Excel conditional formatting to hide the duplicate values,
 * and make the list easier to read. In this example, when the table is sorted by Region,
 * the second (and subsequent) occurences of each region name will have white font colour.
 *//*w  ww  . ja va2 s  .  c om*/
static void hideDupplicates(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("City");
    sheet.createRow(1).createCell(0).setCellValue("Boston");
    sheet.createRow(2).createCell(0).setCellValue("Boston");
    sheet.createRow(3).createCell(0).setCellValue("Chicago");
    sheet.createRow(4).createCell(0).setCellValue("Chicago");
    sheet.createRow(5).createCell(0).setCellValue("New York");

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("A2=A1");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontColorIndex(IndexedColors.WHITE.index);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A6") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(1).createCell(1)
            .setCellValue("<== the second (and subsequent) "
                    + "occurences of each region name will have white font colour.  "
                    + "Condition: Formula Is   =A2=A1   (White Font)");
}

---

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

License:Apache License

/**
 * Use Excel conditional formatting to highlight duplicate entries in a column.
 *///from w  w  w. ja v  a2s  .  co  m
static void formatDuplicates(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Code");
    sheet.createRow(1).createCell(0).setCellValue(4);
    sheet.createRow(2).createCell(0).setCellValue(3);
    sheet.createRow(3).createCell(0).setCellValue(6);
    sheet.createRow(4).createCell(0).setCellValue(3);
    sheet.createRow(5).createCell(0).setCellValue(5);
    sheet.createRow(6).createCell(0).setCellValue(8);
    sheet.createRow(7).createCell(0).setCellValue(0);
    sheet.createRow(8).createCell(0).setCellValue(2);
    sheet.createRow(9).createCell(0).setCellValue(8);
    sheet.createRow(10).createCell(0).setCellValue(6);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontStyle(false, true);
    font.setFontColorIndex(IndexedColors.BLUE.index);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A11") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted.  "
            + "Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1   (Blue Font)");
}

---

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

License:Apache License

/**
 * Use Excel conditional formatting to highlight items that are in a list on the worksheet.
 *//*from   w  ww . j a  v a  2s . c  o  m*/
static void inList(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Codes");
    sheet.createRow(1).createCell(0).setCellValue("AA");
    sheet.createRow(2).createCell(0).setCellValue("BB");
    sheet.createRow(3).createCell(0).setCellValue("GG");
    sheet.createRow(4).createCell(0).setCellValue("AA");
    sheet.createRow(5).createCell(0).setCellValue("FF");
    sheet.createRow(6).createCell(0).setCellValue("XX");
    sheet.createRow(7).createCell(0).setCellValue("CC");

    sheet.getRow(0).createCell(2).setCellValue("Valid");
    sheet.getRow(1).createCell(2).setCellValue("AA");
    sheet.getRow(2).createCell(2).setCellValue("BB");
    sheet.getRow(3).createCell(2).setCellValue("CC");

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($C$2:$C$4,A2)");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.LIGHT_BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A8") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(2).createCell(3).setCellValue(
            "<== Use Excel conditional formatting to highlight items that are in a list on the worksheet");
}

---

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

License:Apache License

/**
 *  Use Excel conditional formatting to highlight payments that are due in the next thirty days.
 *  In this example, Due dates are entered in cells A2:A4.
 *///from  w ww  .  j ava2  s . c  o  m
static void expiry(Sheet sheet) {
    CellStyle style = sheet.getWorkbook().createCellStyle();
    style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("d-mmm"));

    sheet.createRow(0).createCell(0).setCellValue("Date");
    sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29");
    sheet.createRow(2).createCell(0).setCellFormula("A2+1");
    sheet.createRow(3).createCell(0).setCellFormula("A3+1");

    for (int rownum = 1; rownum <= 3; rownum++)
        sheet.getRow(rownum).getCell(0).setCellStyle(style);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF
            .createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontStyle(false, true);
    font.setFontColorIndex(IndexedColors.BLUE.index);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A4") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted");
}

---

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