Example usage for org.apache.poi.ss.usermodel Cell setCellValue

List of usage examples for org.apache.poi.ss.usermodel Cell setCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. setCellValue

Introduction

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

Prototype

void setCellValue(boolean value);

Source Link

Document

Set a boolean value for the cell

Usage

From source file:com.axelor.studio.service.data.validator.ValidatorService.java

License:Open Source License

public void addLog(String log, String sheetName, int rowNum) throws IOException {

    if (logFile == null) {
        logFile = File.createTempFile("ImportLog", ".xlsx");
        logBook = new XSSFWorkbook();
    }/*from w  w w .j a  va  2  s . c  o  m*/

    XSSFSheet sheet = logBook.getSheet(sheetName);

    if (sheet == null) {
        sheet = logBook.createSheet(sheetName);
        XSSFRow titleRow = sheet.createRow(0);
        titleRow.createCell(0).setCellValue("Row Number");
        titleRow.createCell(1).setCellValue("Issues");
    }

    Iterator<Row> rowIterator = sheet.rowIterator();
    Row logRow = null;
    while (rowIterator.hasNext()) {
        Row sheetRow = rowIterator.next();
        Cell cell = sheetRow.getCell(0);
        if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
            continue;
        }
        double value = cell.getNumericCellValue();
        if (value == rowNum + 1) {
            logRow = sheetRow;
            break;
        }
    }

    if (logRow == null) {
        logRow = sheet.createRow(sheet.getPhysicalNumberOfRows());
    }

    Cell cell = logRow.getCell(0);
    if (cell == null) {
        cell = logRow.createCell(0);
        cell.setCellValue(rowNum + 1);
    }
    cell = logRow.getCell(1);
    if (cell == null) {
        cell = logRow.createCell(1);
    }
    String oldValue = cell.getStringCellValue();
    if (oldValue == null) {
        cell.setCellValue(log);
    } else {
        cell.setCellValue(oldValue + "\n" + log);
    }

}

From source file:com.b2international.snowowl.datastore.server.importer.AbstractTerminologyExcelExporter.java

License:Apache License

/**
 * Creates the index sheet based on the given sheet names.
 * //from  w  w  w .java 2s. co  m
 * @param sheetNames
 */
protected void createIndexSheet(final Collection<T> components) {

    final Sheet indexSheet = workbook.createSheet("INDEX");

    final List<T> filteredComponents = Lists.newArrayList(Iterables.filter(components, new Predicate<T>() {
        @Override
        public boolean apply(T input) {
            return isToExport(getComponentId(input));
        }
    }));

    final List<String> sheetNames = extractSheetNamesFromTerminologyComponents(filteredComponents);

    final Row firstRow = indexSheet.createRow(0);
    createCell(firstRow, getIndexSheetHeaderName(), BOLD_STYLE, 0);

    for (int i = 0; i < sheetNames.size(); i++) {

        final String sheetName = getFinalSheetName(i + 1, sheetNames.get(i));
        final Hyperlink hyperlink = workbook.getCreationHelper().createHyperlink(XSSFHyperlink.LINK_DOCUMENT);

        hyperlink.setLabel(sheetName);
        hyperlink.setAddress(String.format("'%s'!A1", sheetName));

        final Row row = indexSheet.createRow(i + 1);
        final Cell cell = row.createCell(0);

        cell.setCellValue(sheetName);
        cell.setCellStyle(hyperlinkStyle);
        cell.setHyperlink(hyperlink);

    }

    indexSheet.autoSizeColumn(0);

}

From source file:com.b2international.snowowl.datastore.server.importer.AbstractTerminologyExcelExporter.java

License:Apache License

/**
 * Creates a property row in the excel with the given property name and value.
 * //www  .  j ava2s.  c  om
 * @param sheet
 *            the sheet where the property is created.
 * @param rowNumber
 *            the number of the row where the property is created.
 * @param propertyName
 *            the name of the property.
 * @param propertyValue
 *            the value of the property.
 */
protected void createProperty(final Sheet sheet, final int rowNumber, final String propertyName,
        final String propertyValue) {
    final Row row = sheet.createRow(rowNumber);
    Cell cell = row.createCell(0);
    cell.setCellValue(propertyName);
    cell.setCellStyle(BOLD_STYLE);

    cell = row.createCell(1);
    cell.setCellValue(propertyValue);
    cell.setCellStyle(defaultStyle);
}

From source file:com.b2international.snowowl.datastore.server.importer.AbstractTerminologyExcelExporter.java

License:Apache License

/**
 * Creates a metadata row in the excel with the given group name and keyword.
 * /*from  ww w.  j ava  2  s .  c o m*/
 * @param sheet
 *            the sheet where the metadata is created.
 * @param rowNum
 *            the number of the row where the metadata is created.
 * @param groupName
 *            the name of the group.
 * @param keyword
 *            the name of the keyword.
 */
protected void createMetadata(Sheet sheet, int rowNum, String groupName, String keyword) {
    final Row row = sheet.createRow(rowNum);

    Cell cell = row.createCell(0);
    cell.setCellValue(groupName);
    cell.setCellStyle(defaultStyle);

    cell = row.createCell(1);
    cell.setCellValue(keyword);
    cell.setCellStyle(defaultStyle);
}

From source file:com.b2international.snowowl.datastore.server.importer.AbstractTerminologyExcelExporter.java

License:Apache License

/**
 * Creates a cell with the given string value.
 * //from www .jav  a  2  s .  c  om
 * @param row
 *            the row where the cell is created.
 * @param cellValue
 *            the string value of the cell.
 * @param cellStyle
 *            the style of the cell.
 * @param cellIndex
 *            the index of the cell in the row.
 */
protected void createCell(final Row row, final String cellValue, final CellStyle cellStyle,
        final int cellIndex) {
    final Cell cell = row.createCell(cellIndex);
    cell.setCellValue(cellValue);
    cell.setCellStyle(cellStyle);
}

From source file:com.b2international.snowowl.datastore.server.importer.AbstractTerminologyExcelExporter.java

License:Apache License

/**
 * Creates a cell with the given int value.
 * //from w  ww .ja  v  a 2 s.  c o m
 * @param row
 *            the row where the cell is created.
 * @param cellValue
 *            the int value of the cell.
 * @param cellStyle
 *            the style of the cell.
 * @param cellIndex
 *            the index of the cell in the row.
 */
protected void createCell(final Row row, final int cellValue, final CellStyle cellStyle, final int cellIndex) {
    final Cell cell = row.createCell(cellIndex);
    cell.setCellValue(cellValue);
    cell.setCellStyle(cellStyle);
}

From source file:com.b2international.snowowl.snomed.exporter.server.dsv.SnomedSimpleTypeRefSetExcelExporter.java

License:Apache License

private void createCell(final Row row, final String value, final int columnNumber, final CellStyle style) {
    final Cell cell = row.createCell(columnNumber);
    cell.setCellValue(value);
    cell.setCellStyle(style);/*from ww w .  ja  v  a 2 s . c om*/
}

From source file:com.b510.excel.client.BusinessPlan.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//from w ww .j  av  a2  s  . co 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("Business Plan");

    //turn off gridlines
    sheet.setDisplayGridlines(false);
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    //the following three statements are required only for HSSF
    sheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 1);

    //the header row: centered text in 48pt font
    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);
    for (int i = 0; i < titles.length; i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(styles.get("header"));
    }
    //columns for 11 weeks starting from 9-Jul
    Calendar calendar = Calendar.getInstance();
    int year = calendar.get(Calendar.YEAR);

    calendar.setTime(fmt.parse("9-Jul"));
    calendar.set(Calendar.YEAR, year);
    for (int i = 0; i < 11; i++) {
        Cell cell = headerRow.createCell(titles.length + i);
        cell.setCellValue(calendar);
        cell.setCellStyle(styles.get("header_date"));
        calendar.roll(Calendar.WEEK_OF_YEAR, true);
    }
    //freeze the first row
    sheet.createFreezePane(0, 1);

    Row row;
    Cell cell;
    int rownum = 1;
    for (int i = 0; i < data.length; i++, rownum++) {
        row = sheet.createRow(rownum);
        if (data[i] == null)
            continue;

        for (int j = 0; j < data[i].length; j++) {
            cell = row.createCell(j);
            String styleName;
            boolean isHeader = i == 0 || data[i - 1] == null;
            switch (j) {
            case 0:
                if (isHeader) {
                    styleName = "cell_b";
                    cell.setCellValue(Double.parseDouble(data[i][j]));
                } else {
                    styleName = "cell_normal";
                    cell.setCellValue(data[i][j]);
                }
                break;
            case 1:
                if (isHeader) {
                    styleName = i == 0 ? "cell_h" : "cell_bb";
                } else {
                    styleName = "cell_indented";
                }
                cell.setCellValue(data[i][j]);
                break;
            case 2:
                styleName = isHeader ? "cell_b" : "cell_normal";
                cell.setCellValue(data[i][j]);
                break;
            case 3:
                styleName = isHeader ? "cell_b_centered" : "cell_normal_centered";
                cell.setCellValue(Integer.parseInt(data[i][j]));
                break;
            case 4: {
                calendar.setTime(fmt.parse(data[i][j]));
                calendar.set(Calendar.YEAR, year);
                cell.setCellValue(calendar);
                styleName = isHeader ? "cell_b_date" : "cell_normal_date";
                break;
            }
            case 5: {
                int r = rownum + 1;
                String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")";
                cell.setCellFormula(fmla);
                styleName = isHeader ? "cell_bg" : "cell_g";
                break;
            }
            default:
                styleName = data[i][j] != null ? "cell_blue" : "cell_normal";
            }

            cell.setCellStyle(styles.get(styleName));
        }
    }

    //group rows for each phase, row numbers are 0-based
    sheet.groupRow(4, 6);
    sheet.groupRow(9, 13);
    sheet.groupRow(16, 18);

    //set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 256 * 6);
    sheet.setColumnWidth(1, 256 * 33);
    sheet.setColumnWidth(2, 256 * 20);
    sheet.setZoom(3, 4);

    // Write the output to a file
    String file = "businessplan.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.b510.excel.client.CalendarDemo.java

License:Apache License

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

    Calendar calendar = Calendar.getInstance();
    boolean xlsx = true;
    for (int i = 0; i < args.length; i++) {
        if (args[i].charAt(0) == '-') {
            xlsx = args[i].equals("-xlsx");
        } else {/*from   w  ww  .  ja  v a  2  s .c  om*/
            calendar.set(Calendar.YEAR, Integer.parseInt(args[i]));
        }
    }
    int year = calendar.get(Calendar.YEAR);

    Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook();

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

    for (int month = 0; month < 12; month++) {
        calendar.set(Calendar.MONTH, month);
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        //create a sheet for each month
        Sheet sheet = wb.createSheet(months[month]);

        //turn off gridlines
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);

        //the following three statements are required only for HSSF
        sheet.setAutobreaks(true);
        printSetup.setFitHeight((short) 1);
        printSetup.setFitWidth((short) 1);

        //the header row: centered text in 48pt font
        Row headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(80);
        Cell titleCell = headerRow.createCell(0);
        titleCell.setCellValue(months[month] + " " + year);
        titleCell.setCellStyle(styles.get("title"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

        //header with month titles
        Row monthRow = sheet.createRow(1);
        for (int i = 0; i < days.length; i++) {
            //set column widths, the width is measured in units of 1/256th of a character width
            sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide
            sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide
            sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1));
            Cell monthCell = monthRow.createCell(i * 2);
            monthCell.setCellValue(days[i]);
            monthCell.setCellStyle(styles.get("month"));
        }

        int cnt = 1, day = 1;
        int rownum = 2;
        for (int j = 0; j < 6; j++) {
            Row row = sheet.createRow(rownum++);
            row.setHeightInPoints(100);
            for (int i = 0; i < days.length; i++) {
                Cell dayCell_1 = row.createCell(i * 2);
                Cell dayCell_2 = row.createCell(i * 2 + 1);

                int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);
                if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) {
                    dayCell_1.setCellValue(day);
                    calendar.set(Calendar.DAY_OF_MONTH, ++day);

                    if (i == 0 || i == days.length - 1) {
                        dayCell_1.setCellStyle(styles.get("weekend_left"));
                        dayCell_2.setCellStyle(styles.get("weekend_right"));
                    } else {
                        dayCell_1.setCellStyle(styles.get("workday_left"));
                        dayCell_2.setCellStyle(styles.get("workday_right"));
                    }
                } else {
                    dayCell_1.setCellStyle(styles.get("grey_left"));
                    dayCell_2.setCellStyle(styles.get("grey_right"));
                }
                cnt++;
            }
            if (calendar.get(Calendar.MONTH) > month)
                break;
        }
    }

    // Write the output to a file
    String file = "calendar.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.b510.excel.client.LoanCalculator.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//from   w  w  w.  j a va  2s .  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("Loan Calculator");
    sheet.setPrintGridlines(false);
    sheet.setDisplayGridlines(false);

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    sheet.setColumnWidth(0, 3 * 256);
    sheet.setColumnWidth(1, 3 * 256);
    sheet.setColumnWidth(2, 11 * 256);
    sheet.setColumnWidth(3, 14 * 256);
    sheet.setColumnWidth(4, 14 * 256);
    sheet.setColumnWidth(5, 14 * 256);
    sheet.setColumnWidth(6, 14 * 256);

    createNames(wb);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(35);
    for (int i = 1; i <= 7; i++) {
        titleRow.createCell(i).setCellStyle(styles.get("title"));
    }
    Cell titleCell = titleRow.getCell(2);
    titleCell.setCellValue("Simple Loan Calculator");
    sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1"));

    Row row = sheet.createRow(2);
    Cell cell = row.createCell(4);
    cell.setCellValue("Enter values");
    cell.setCellStyle(styles.get("item_right"));

    row = sheet.createRow(3);
    cell = row.createCell(2);
    cell.setCellValue("Loan amount");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_$"));
    cell.setAsActiveCell();

    row = sheet.createRow(4);
    cell = row.createCell(2);
    cell.setCellValue("Annual interest rate");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_%"));

    row = sheet.createRow(5);
    cell = row.createCell(2);
    cell.setCellValue("Loan period in years");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_i"));

    row = sheet.createRow(6);
    cell = row.createCell(2);
    cell.setCellValue("Start date of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_d"));

    row = sheet.createRow(8);
    cell = row.createCell(2);
    cell.setCellValue("Monthly payment");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(9);
    cell = row.createCell(2);
    cell.setCellValue("Number of payments");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")");
    cell.setCellStyle(styles.get("formula_i"));

    row = sheet.createRow(10);
    cell = row.createCell(2);
    cell.setCellValue("Total interest");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(11);
    cell = row.createCell(2);
    cell.setCellValue("Total cost of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    // Write the output to a file
    String file = "loan-calculator.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}