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.evidon.areweprivateyet.Aggregator.java

License:Open Source License

private void createHeader(Workbook wb, Sheet s, String sheetTitle, int skipCell) {
    int rownum = 0, cellnum = 0;
    Row r = null;// w  ww  .  j a  va 2 s  . com
    Cell c = null;

    // Header
    r = s.createRow(rownum);
    c = r.createCell(0);
    c.setCellValue(sheetTitle);

    rownum++;
    r = s.createRow(rownum);

    if (skipCell > 0) {
        c = r.createCell(cellnum);
        c.setCellValue("");
        cellnum++;
    }

    for (String database : results.keySet()) {
        c = r.createCell(cellnum);
        c.setCellValue(database);

        CellStyle cs = wb.createCellStyle();
        Font f = wb.createFont();
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);
        cs.setFont(f);

        c.setCellStyle(cs);
        cellnum++;
    }
}

From source file:com.evidon.areweprivateyet.Aggregator.java

License:Open Source License

public void createSpreadSheet() throws Exception {
    int row = 2, cell = 0, sheet = 0;
    FileOutputStream file = new FileOutputStream(path + "analysis.xls");

    Workbook wb = new HSSFWorkbook();

    // content: total content length sheet.
    Sheet s = wb.createSheet();/* w  ww. j  a  va  2s  . c o m*/
    wb.setSheetName(sheet, "Content Length");
    this.createHeader(wb, s, "Total Content Length in MB", 0);

    Row r = s.createRow(row);
    for (String database : results.keySet()) {
        Cell c = r.createCell(cell);
        c.setCellValue(results.get(database).totalContentLength / 1024 / 1024);
        cell++;
    }

    row++;
    cell = 0;
    r = s.createRow(row);

    for (String database : results.keySet()) {
        Cell c = r.createCell(cell);
        if (database.equals("baseline")) {
            c.setCellValue("Decrease:");

            Map<String, String> contents = new LinkedHashMap<String, String>();
            contents.put(s.getSheetName(), "0");
            decrease.put(database, contents);
        } else {
            c = r.createCell(cell);
            c.setCellType(Cell.CELL_TYPE_FORMULA);
            c.setCellFormula("ROUND((100-(" + getCellLetter(cell - 1) + "3*100/A3)),0)");

            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
            evaluator.evaluateFormulaCell(c);

            Map<String, String> contents = new LinkedHashMap<String, String>();
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");
            decrease.put(database, contents);
        }
        cell++;
    }
    sheet++;

    // When content is created, baseline is used as a base for every entry. For example,
    // if baseline contained doubleclick.com, this will be output and each other analyzer's
    // map, like ghosterys analyzer is then asked for the content's mapping for doubleclick.
    // So, if baseline does not contain blah.com, yet ghostery map does, this entry is never
    // shown in the spreadsheet or any other results.

    // so this means if we have tracker/whatever URLs in a non-baseline profile
    // and these URLs are NOT in the baseline profile,
    // we wouldn't see those trackers/whatever in the final comparison.

    // content: HTTP Requests
    s = wb.createSheet();
    wb.setSheetName(sheet, "HTTP Requests");
    this.createHeader(wb, s, "Pages with One or More HTTP Requests to the Public Suffix", 1);
    this.createContent(wb, s, "requestCountPerDomain");
    sheet++;

    // content: HTTP Set-Cookie Responses
    s = wb.createSheet();
    wb.setSheetName(sheet, "HTTP Set-Cookie Responses");
    this.createHeader(wb, s,
            "Pages with One or More HTTP Responses from the Public Suffix That Include a Set-Cookie Header", 1);
    this.createContent(wb, s, "setCookieResponses");
    sheet++;

    // content: Cookie Added - Cookie Deleted
    s = wb.createSheet();
    wb.setSheetName(sheet, "Cookies Added-Deleted");
    this.createHeader(wb, s, "Cookies Added - Cookies Deleted Per Domain", 1);
    this.createContent(wb, s, "cookieTotals");
    sheet++;

    // content: Local Storage counts per domain
    s = wb.createSheet();
    wb.setSheetName(sheet, "Local Storage");
    this.createHeader(wb, s, "Local Storage counts per domain", 1);
    this.createContent(wb, s, "localStorageContents");
    sheet++;

    // content: Pretty Chart
    s = wb.createSheet();
    wb.setSheetName(sheet, "Overall");

    int rownum = 0, cellnum = 0;

    // Header
    r = s.createRow(rownum);
    Cell c = r.createCell(0);
    s.setColumnWidth(0, 8000);
    c.setCellValue(
            "Overall effectiveness measured by percentage of decrease vs baseline (0 for any negative effect)");

    rownum++;
    r = s.createRow(rownum);

    cellnum++;

    for (String database : decrease.keySet()) {
        if (database.equals("baseline")) {
            continue;
        }

        c = r.createCell(cellnum);
        c.setCellValue(database);

        CellStyle cs = wb.createCellStyle();
        Font f = wb.createFont();
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);
        cs.setFont(f);

        c.setCellStyle(cs);
        cellnum++;
    }

    CellStyle numberStyle = wb.createCellStyle();
    numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("number"));

    // Content
    for (String type : decrease.get("baseline").keySet()) {
        cellnum = 0;
        rownum++;

        r = s.createRow(rownum);

        c = r.createCell(cellnum);
        c.setCellValue(type);
        cellnum++;

        for (String database : decrease.keySet()) {
            if (database.equals("baseline")) {
                continue;
            }

            c = r.createCell(cellnum);
            c.setCellStyle(numberStyle);

            double decreaseValue = Double.parseDouble(decrease.get(database).get(type));

            if (decreaseValue < 0)
                decreaseValue = 0;

            c.setCellValue(decreaseValue);
            cellnum++;
        }
    }

    /*
    for (String database : decrease.keySet()) {
       for (String type : decrease.get(database).keySet()) {
    System.out.println(database + "|" + type + "|" + decrease.get(database).get(type));
       }
    }
    */

    wb.write(file);
    file.close();
}

From source file:com.excelsiorsoft.transformer.TransformationHandler.java

License:Apache License

/**
 * Actual Spring Integration transformation handler.
 *
 * @param inputMessage Spring Integration input message
 * @return New Spring Integration message with updated headers
 *///from  w w w  .ja  v  a2  s  .  c o  m
@Transformer
public Message<byte[]> handleFile(final Message<File> inputMessage) {

    final File inputFile = inputMessage.getPayload();
    final String filename = inputFile.getName();

    final String inputAsString;

    try {
        inputAsString = FileUtils.readFileToString(inputFile);
    } catch (IOException e) {
        throw new IllegalStateException(e);
    }

    ByteArrayOutputStream bout = new ByteArrayOutputStream();

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("Sample Sheet");

    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 0);
    // Create a cell and put a value in it.
    Cell cell = row.createCell(0);

    cell.setCellValue(inputAsString);

    try {
        wb.write(bout);
    } catch (IOException e) {
        throw new IllegalStateException(e);
    }

    final Message<byte[]> message = MessageBuilder.withPayload(bout.toByteArray())
            .setHeader(FileHeaders.FILENAME, filename + ".xls").setHeader(FileHeaders.ORIGINAL_FILE, inputFile)
            .setHeader("file_size", inputFile.length()).build();

    return message;
}

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

private void setValue(Cell cell, Value value) {
    if (value == null || value.isNull()) {
        cell.setCellValue(EMPTY_STRING);
        return;//from  w ww.  ja v  a2s .c  o m
    }
    switch (value.getValueType()) {
    case BOOLEAN:
        cell.setCellValue(value.getBooleanValue());
        break;

    case DATE:
        cell.setCellValue(value.getDateValue());
        break;

    case DECIMAL:
        cell.setCellValue(value.getDecimalValue());
        break;

    case INTEGRAL:
        cell.setCellValue(value.getIntegralValue());
        break;

    case NULL:
        cell.setCellValue(EMPTY_STRING);
        break;

    case TEXT:
        cell.setCellValue(value.getTextValue());
        break;

    case TIMESTAMP:
        cell.setCellValue(value.getDateValue());
        break;

    default:
        cell.setCellValue(value.getTextValue());
        break;
    }
}

From source file:com.ferid.app.classroom.statistics.StatisticsFragment.java

License:Apache License

/**
 * Converts all attendances into excel format
 *///  w w  w .ja va2s .  c om
private void convertToExcel() {
    //create workbook
    Workbook wb = new HSSFWorkbook();

    for (Classroom classroom : classroomArrayList) { //each sheet

        //create sheet
        Sheet sheet = wb.createSheet(classroom.getName());

        //header
        HashMap<String, Integer> date_column_map = new HashMap<>();
        ArrayList<String> dates = new ArrayList<>();
        int rowNumber = 0;
        int colNumber = 1;
        Row row = sheet.createRow(rowNumber);

        //dates columns
        for (Attendance attendance : attendanceArrayList) {

            if (classroom.getId() == attendance.getClassroomId() && !dates.contains(attendance.getDateTime())) {

                Cell cellDate = row.createCell(colNumber);
                cellDate.setCellStyle(ExcelStyleManager.getHeaderCellStyle(wb));

                cellDate.setCellValue(attendance.getDateTime());

                dates.add(attendance.getDateTime());
                date_column_map.put(attendance.getDateTime(), colNumber);

                //set width of the dates columns
                sheet.setColumnWidth(colNumber,
                        getResources().getInteger(R.integer.statistics_excel_column_width_dates));

                colNumber++;
            }
        }

        //set width of the students column
        //it is always the first column
        sheet.setColumnWidth(0, getResources().getInteger(R.integer.statistics_excel_column_width_students));

        //students list at the left column
        HashMap<Integer, Integer> student_row_map = new HashMap<>();
        ArrayList<Integer> studentIds = new ArrayList<>();
        rowNumber = 1;
        for (Attendance attendance : attendanceArrayList) {

            if (classroom.getId() == attendance.getClassroomId()
                    && !studentIds.contains(attendance.getStudentId())) { //another student

                row = sheet.createRow(rowNumber);

                Cell cellStudent = row.createCell(0);
                cellStudent.setCellStyle(ExcelStyleManager.getHeaderCellStyle(wb));

                cellStudent.setCellValue(attendance.getStudentName());

                studentIds.add(attendance.getStudentId());
                student_row_map.put(attendance.getStudentId(), rowNumber);

                rowNumber++;
            }
        }

        //now get column number from date columns
        //and get row number from student rows
        //match row-column pair and print into cell
        for (Attendance attendance : attendanceArrayList) {

            if (classroom.getId() == attendance.getClassroomId()) {
                rowNumber = student_row_map.get(attendance.getStudentId());
                colNumber = date_column_map.get(attendance.getDateTime());

                row = sheet.getRow(rowNumber);

                Cell cellPresence = row.createCell(colNumber);
                cellPresence.setCellStyle(ExcelStyleManager.getContentCellStyle(wb));

                cellPresence.setCellValue(attendance.getPresent());
            }
        }
    }

    if (classroomArrayList.size() > 0) {
        writeIntoFile(wb);
    }

    swipeRefreshLayout.setRefreshing(false);
}

From source file:com.firstonesoft.poi.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;/*from  ww  w  . ja va 2  s  .  c om*/

    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

/**
 * ??java Bean//from   ww  w.  j a v a  2s  .  com
 *
 * @param sheet
 * @param headers
 */
public static void addHeaderRow(Sheet sheet, int rowIndex, List<String> headers) {
    Row row = null;
    Cell cell = null;

    // Field[] fields=clazz.getDeclaredFields();
    // Excelheader,?
    row = sheet.createRow(rowIndex);
    for (int i = 0; i < headers.size(); i++) {
        cell = row.createCell(i);
        cell.setCellValue(headers.get(i));
    }
}

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

License:Apache License

/**
 * ??java Bean ???@ExcelHeader,/*  ww  w  . j a v  a 2  s . c  o  m*/
 *
 * @param sheet
 * @param clazz
 * @param fieldnames
 */
public static void addHeaderRow(Sheet sheet, int rowIndex, Class clazz, List<String> fieldnames) {
    Row row = null;
    Cell cell = null;

    // Field[] fields=clazz.getDeclaredFields();

    // Excelheader,?
    row = sheet.createRow(rowIndex);
    for (int i = 0; i < fieldnames.size(); i++) {
        Field tempfield = null;
        try {
            tempfield = clazz.getDeclaredField(fieldnames.get(i));
        } catch (Exception ex) {
            if (logger.isEnabledFor(Priority.ERROR)) {
                logger.error("??");
            }
        }

        if (tempfield != null) {
            ExcelHeader excelheader = tempfield.getAnnotation(ExcelHeader.class);
            if (excelheader != null) {
                cell = row.createCell(i);
                cell.setCellValue(excelheader.value());
            }
        } else {
            logger.warn("" + fieldnames.get(i) + "@ExcelHeader ???");
            fieldnames.remove(i);
        }
    }
}

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

License:Apache License

/**
 * ?// w w  w . j ava 2s . c om
 *
 * @param cell
 * @param value
 */
private static void setCellValue(Cell cell, Object value) {
    if (value == null) {
        cell.setCellType(Cell.CELL_TYPE_BLANK);
    } else {
        if (value instanceof Date) {
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            cell.setCellValue((Date) value);
        } else if (value instanceof String) {
            cell.setCellValue(value.toString());
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Number) {
            cell.setCellValue(Double.valueOf(value.toString()));
        } else if (value instanceof Calendar) {
            cell.setCellValue((Calendar) value);
        }
    }
}

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);//  w  w  w  .  j a v  a2  s  . com
    Cell cell = row.getCell(0);
    cell.setCellType(Cell.CELL_TYPE_STRING);
    cell.setCellValue(title);
}