Example usage for org.apache.poi.ss.usermodel Row createCell

List of usage examples for org.apache.poi.ss.usermodel Row createCell

Introduction

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

Prototype

Cell createCell(int column);

Source Link

Document

Use this to create new cells within the row and return it.

Usage

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  .ja  va 2  s  . co 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  ww.  ja v  a 2  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 Workbook addSheet(Workbook workbook, String[][] data, String sheetName) {
    Sheet sheet = workbook.createSheet(sheetName);
    int rowIdx = 0;
    for (String[] dataRow : data) {
        Row row = sheet.createRow(rowIdx);
        rowIdx++;//from  www  .  ja va  2 s.c o m
        int cellIdx = 0;
        for (String val : dataRow) {
            row.createCell(cellIdx).setCellValue(val);
            cellIdx++;
        }
    }
    return workbook;
}

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

License:Open Source License

private Workbook copyToWorkbook(DataCollection dc, Workbook workbook) {
    /**//from   w  w  w .  ja  v  a2  s  .c om
     * values first
     */
    Sheet sheet = workbook.createSheet(CommonFieldNames.VALUES_TABLE_NAME);
    Row header = sheet.createRow(0);
    Row dataRow = sheet.createRow(1);
    int colIdx = 0;
    String[] fieldNames = dc.getFieldNames();
    for (String fieldName : fieldNames) {
        header.createCell(colIdx).setCellValue(fieldName);
        this.setValue(dataRow.createCell(colIdx), dc.getValue(fieldName));
        colIdx++;
    }
    /**
     * would like to blatantly ignore lists as NO ONE is using it. Will wait
     * for a bug report :-)
     */
    String[] gridNames = dc.getGridNames();
    if (gridNames != null && gridNames.length == 0) {
        for (String gridName : gridNames) {
            this.addSheet(workbook, dc.getGrid(gridName));
        }
    }
    return workbook;
}

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

License:Open Source License

private void addRows(Sheet sheet, String[][] rows) {
    int rowIdx = 0;
    for (String[] row : rows) {
        Row xlRow = sheet.createRow(rowIdx);
        int colIdx = 0;
        for (String columnValue : row) {
            xlRow.createCell(colIdx).setCellValue(columnValue);
            colIdx++;// w  w  w  .ja v  a2  s .  c o m
        }
        rowIdx++;
    }
}

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

License:Open Source License

private void addMissingRows(Sheet sheet, String[][] rows) {
    /**//from  w ww.ja v  a 2s. c o m
     * create a set of existing labels
     */
    Set<String> existingEntries = new HashSet<String>();
    int lastRow = sheet.getLastRowNum();
    for (int i = 0; i <= lastRow; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        Cell cell = row.getCell(0);
        if (cell == null) {
            continue;
        }
        existingEntries.add(cell.getStringCellValue());
    }
    /**
     * now, add rows, only if they are not there already
     */
    for (String[] row : rows) {
        if (existingEntries.contains(row[0])) {
            continue;
        }
        lastRow++;
        Row xlRow = sheet.createRow(lastRow);
        int colIdx = 0;
        for (String columnValue : row) {
            xlRow.createCell(colIdx).setCellValue(columnValue);
            colIdx++;
        }
    }
}

From source file:com.eyeq.pivot4j.export.poi.ExcelExporter.java

License:Common Public License

/**
 * @param row
 * @return
 */
protected Cell createCell(Row row, int colIndex) {
    return row.createCell(colIndex);
}

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

License:Apache License

/**
 * Converts all attendances into excel format
 *//*from   w w  w  . j  av a  2  s.c  o  m*/
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;// www. j  ava 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("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/*ww w .ja  va  2s. c om*/
 *
 * @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));
    }
}