Example usage 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));
    }
}

---

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