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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. 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.biomeris.i2b2.export.engine.ExcelCreator.java

License:Open Source License

public void colorTest() {
    for (short k = (short) 0; k < 3; k++) {
        currentSheet = workbook.createSheet("ct " + k);
        currentSheetRow = 0;// w ww .  j av  a2s  .  c o  m

        for (short i = (short) 0; i < 82; i++) {
            Row headerRow_XLSX = currentSheet.createRow(currentSheetRow++);

            Cell c2 = headerRow_XLSX.createCell(0);
            c2.setCellValue("" + i);

            for (short j = (short) 0; j < 19; j++) {
                CellStyle colorStyle = workbook.createCellStyle();
                colorStyle.setFillForegroundColor(k);
                colorStyle.setFillBackgroundColor(i);
                colorStyle.setFillPattern(j);
                Cell c1 = headerRow_XLSX.createCell(j + 1);
                c1.setCellStyle(colorStyle);
                c1.setCellValue("" + j);
            }
        }
    }

}

From source file:com.biomeris.i2b2.export.engine.ExcelCreator.java

License:Open Source License

public void writeHeader(List<String> headerElements) {
    Row headerRow_XLSX = currentSheet.createRow(currentSheetRow++);
    headerRow_XLSX.setHeight((short) 350);

    for (int i = 0; i < headerElements.size(); i++) {
        Cell c = headerRow_XLSX.createCell(i);
        c.setCellStyle(headerStyle);/*from w ww  . j  a va 2 s . co m*/
        c.setCellValue(headerElements.get(i));
    }
}

From source file:com.biomeris.i2b2.export.engine.ExcelCreator.java

License:Open Source License

public void writeObservations(Concept concept, List<WObservation> observations, boolean maskPatientids) {
    for (WObservation obs : observations) {
        Row row = currentSheet.createRow(currentSheetRow++);

        // 0// ww  w .j  a  v a 2 s.  co m
        if (maskPatientids) {
            String patientId = obs.getPatientId();
            Integer maskedId;
            if (maskedPatientMaps.containsKey(patientId)) {
                maskedId = maskedPatientMaps.get(patientId);
            } else {
                maskedId = nextMaskedId;
                maskedPatientMaps.put(patientId, nextMaskedId++);
            }
            row.createCell(0).setCellValue(maskedId);
        } else {
            row.createCell(0).setCellValue(obs.getPatientId());
        }

        // 1
        row.createCell(1).setCellValue(obs.getEncounterId());

        // 2
        Cell cell2 = row.createCell(2);
        cell2.setCellStyle(dateStyle);
        cell2.setCellValue(obs.getStartDate().getTime());

        // 3
        if (obs.getEndDate() != null) {
            Cell cell3 = row.createCell(3);
            cell3.setCellStyle(dateStyle);
            cell3.setCellValue(obs.getEndDate().getTime());
        }

        // 4
        row.createCell(4).setCellValue(obs.getInstanceNum());

        // 5
        row.createCell(5).setCellValue(concept.getName());

        // 6
        if (concept.getType().equals(ExportConstants.LEAF)) {
            row.createCell(6).setCellValue(obs.getValueTypeCd());
        } else {
            row.createCell(6).setCellValue(ExportConstants.FOLDER);
        }

        // 7
        row.createCell(7).setCellValue(obs.getConceptCd());

        // 8
        row.createCell(8).setCellValue(concept.getChildrenMap().get(obs.getConceptCd()));

        if (concept.getModifier() != null) {
            // 9
            row.createCell(9).setCellValue(concept.getModifier().getName());

            // 10
            row.createCell(10).setCellValue(obs.getModifierCd());

            // 11
            row.createCell(11).setCellValue(concept.getModifier().getChildrenMap().get(obs.getModifierCd()));
        }

        // 12
        // 13
        switch (obs.getValueTypeCd()) {
        case ExportConstants.VALTYPE_N:
            row.createCell(12).setCellValue(operator(obs.getTvalChar()));
            row.createCell(13).setCellValue(obs.getNvalNum());
            break;
        default:
            row.createCell(13).setCellValue(obs.getTvalChar());
            break;
        }

        // 14
        row.createCell(14).setCellValue(obs.getUnitsCd());
    }
}

From source file:com.biomeris.i2b2.export.engine.ExcelCreator.java

License:Open Source License

public void writeAggregates(Map<String, List<ConceptAggregate>> patientAggregate, int conceptNumber,
        boolean maskPatientids) {
    Set<Integer> usedColumns = new HashSet<>();

    for (String patientId : patientAggregate.keySet()) {
        Row row = currentSheet.createRow(currentSheetRow++);

        // 0/*from w  ww  .jav  a 2  s.  co m*/
        if (maskPatientids) {
            Integer maskedId;
            if (maskedPatientMaps.containsKey(patientId)) {
                maskedId = maskedPatientMaps.get(patientId);
            } else {
                maskedId = nextMaskedId;
                maskedPatientMaps.put(patientId, nextMaskedId++);
            }
            row.createCell(0).setCellValue(maskedId);
        } else {
            row.createCell(0).setCellValue(patientId);
        }
        usedColumns.add(0);

        for (int i = 0; i < patientAggregate.get(patientId).size(); i++) {
            ConceptAggregate ca = patientAggregate.get(patientId).get(i);

            if (ca == null) {
                row.createCell((i * 5) + 1).setCellStyle(leftSeparatorStyle);
                row.createCell((i * 5) + 2);
                row.createCell((i * 5) + 3);
                row.createCell((i * 5) + 4);
                row.createCell((i * 5) + 5);
                continue;
            }

            // (i*5)+1
            Cell countCell = row.createCell((i * 5) + 1);
            countCell.setCellStyle(leftSeparatorStyle);
            if (ca.getCount() != null) {
                countCell.setCellValue(ca.getCount());
                usedColumns.add((i * 5) + 1);
            }
            // (i*5)+2
            if (ca.getMean() != null) {
                row.createCell((i * 5) + 2).setCellValue(ca.getMean());
                usedColumns.add((i * 5) + 2);
            }
            // (i*5)+3
            if (ca.getSd() != null) {
                row.createCell((i * 5) + 3).setCellValue(ca.getSd());
                usedColumns.add((i * 5) + 3);
            }
            // (i*5)+4
            if (ca.getMedian() != null) {
                row.createCell((i * 5) + 4).setCellValue(ca.getMedian());
                usedColumns.add((i * 5) + 4);
            }
            // (i*5)+5
            if (ca.getMode() != null) {
                row.createCell((i * 5) + 5).setCellValue(ca.getMode());
                usedColumns.add((i * 5) + 5);
            }
        }

    }

    int colNum = (conceptNumber * 5) + 1;
    for (int i = 0; i < colNum; i++) {
        if (!usedColumns.contains(i)) {
            currentSheet.setColumnHidden(i, true);
        }
    }
}

From source file:com.biomeris.i2b2.export.engine.ExcelCreator.java

License:Open Source License

public void writePatientInfo(List<WPatientInfo> patientInfos, PatientSheetElements pse,
        boolean maskPatientids) {
    for (WPatientInfo pInfo : patientInfos) {
        Row row = currentSheet.createRow(currentSheetRow++);

        int colIndex = 0;

        if (maskPatientids) {
            String patientId = pInfo.getPatientId();
            Integer maskedId;//  w w  w.ja v a2  s  . c  om
            if (maskedPatientMaps.containsKey(patientId)) {
                maskedId = maskedPatientMaps.get(patientId);
            } else {
                maskedId = nextMaskedId;
                maskedPatientMaps.put(patientId, nextMaskedId++);
            }
            row.createCell(colIndex++).setCellValue(maskedId);
        } else {
            row.createCell(colIndex++).setCellValue(pInfo.getPatientId());
        }

        if (pse == null || pse.isVitalStatus()) {
            row.createCell(colIndex++).setCellValue(pInfo.getVitalStatus());
        }

        if (pse == null || pse.isBirthDate()) {
            if (pInfo.getDateOfBirth() != null) {
                Cell cell = row.createCell(colIndex++);
                cell.setCellStyle(dateStyle);
                cell.setCellValue(pInfo.getDateOfBirth().getTime());

                row.createCell(colIndex++).setCellValue(getAge(pInfo.getDateOfBirth()));
            } else {
                colIndex = colIndex + 2;
            }
        }

        if (pse == null || pse.isDeathDate()) {
            if (pInfo.getDateOfDeath() != null) {
                Cell cell = row.createCell(colIndex++);
                cell.setCellStyle(dateStyle);
                cell.setCellValue(pInfo.getDateOfDeath().getTime());
            } else {
                colIndex++;
            }
        }
        if (pse == null || pse.isSex()) {
            if (pInfo.getSex() != null) {
                row.createCell(colIndex++).setCellValue(pInfo.getSex());
            } else {
                colIndex++;
            }
        }
        if (pse == null || pse.isLanguage()) {
            if (pInfo.getLanguage() != null) {
                row.createCell(colIndex++).setCellValue(pInfo.getLanguage());
            } else {
                colIndex++;
            }
        }
        if (pse == null || pse.isRace()) {
            if (pInfo.getRace() != null) {
                row.createCell(colIndex++).setCellValue(pInfo.getRace());
            } else {
                colIndex++;
            }
        }
        if (pse == null || pse.isMaritalStatus()) {
            if (pInfo.getMaritalStatus() != null) {
                row.createCell(colIndex++).setCellValue(pInfo.getMaritalStatus());
            } else {
                colIndex++;
            }

        }
        if (pse == null || pse.isReligion()) {
            if (pInfo.getReligion() != null) {
                row.createCell(colIndex++).setCellValue(pInfo.getReligion());
            } else {
                colIndex++;
            }

        }
        if (pse == null || pse.isZipCode()) {
            if (pInfo.getZipCode() != null) {
                row.createCell(colIndex++).setCellValue(pInfo.getZipCode());
            } else {
                colIndex++;
            }

        }
        if (pse == null || pse.isIncome()) {
            if (pInfo.getIncome() != null) {
                row.createCell(colIndex++).setCellValue(pInfo.getIncome());
            } else {
                colIndex++;
            }

        }
    }
}

From source file:com.biomeris.i2b2.export.engine.ExcelCreator.java

License:Open Source License

public void writeExportInfo(WExport export) {
    // User/*from   w  w w .  j a  va2s.  c o m*/
    Row rowUser = currentSheet.createRow(currentSheetRow++);
    rowUser.createCell(0).setCellValue("User");
    rowUser.createCell(1).setCellValue(export.getUsername());

    // Export date
    Row rowDate = currentSheet.createRow(currentSheetRow++);
    rowDate.createCell(0).setCellValue("Export date");
    Cell todayCell = rowDate.createCell(1);
    todayCell.setCellStyle(dateStyle);
    todayCell.setCellValue(new Date());

    // Export type
    Row rowET = currentSheet.createRow(currentSheetRow++);
    rowET.createCell(0).setCellValue("Export type");
    rowET.createCell(1).setCellValue(export.getExportParams().getExportType());

    // Concepts and modifiers
    int conceptNum = 1;
    for (Concept c : export.getConcepts()) {
        Row rowC1 = currentSheet.createRow(currentSheetRow++);
        rowC1.createCell(0).setCellValue("Concept " + conceptNum + " (name)");
        rowC1.createCell(1).setCellValue(c.getName());
        Row rowC2 = currentSheet.createRow(currentSheetRow++);
        rowC2.createCell(0).setCellValue("Concept " + conceptNum + " (key)");
        rowC2.createCell(1).setCellValue(c.getItemKey());
        if (c.getModifier() != null) {
            Row rowM1 = currentSheet.createRow(currentSheetRow++);
            rowM1.createCell(0).setCellValue("Concept " + conceptNum + " (modifier name)");
            rowM1.createCell(1).setCellValue(c.getModifier().getName());
            Row rowM2 = currentSheet.createRow(currentSheetRow++);
            rowM2.createCell(0).setCellValue("Concept " + conceptNum + " (modifier key)");
            rowM2.createCell(1).setCellValue(c.getModifier().getModifierKey());
        }
        conceptNum++;
    }

    // Filter date ( min start date)
    if (export.getExportParams().getStartDate() != null) {
        Row rowSD = currentSheet.createRow(currentSheetRow++);
        rowSD.createCell(0).setCellValue("Min start date");
        Cell sdCell = rowSD.createCell(1);
        sdCell.setCellStyle(dateStyle);
        sdCell.setCellValue(export.getExportParams().getStartDate().getTime());
    }

    // Filter date (max start date)
    if (export.getExportParams().getEndDate() != null) {
        Row rowED = currentSheet.createRow(currentSheetRow++);
        rowED.createCell(0).setCellValue("Max start date");
        Cell sdCell = rowED.createCell(1);
        sdCell.setCellStyle(dateStyle);
        sdCell.setCellValue(export.getExportParams().getEndDate().getTime());
    }
}

From source file:com.bizosys.dataservice.dao.ReadXLS.java

License:Apache License

@Override
protected List<String> populate() throws SQLException {

    checkCondition();//from w ww. j  a  v  a2  s.c  o m

    Workbook workbook = getWorkbook();
    Sheet sheet = workbook.createSheet();

    ResultSetMetaData md = rs.getMetaData();
    int totalCol = md.getColumnCount();
    String[] cols = createLabels(md, totalCol);

    try {

        if (null != templateFile) {
            File templateFileObject = new File(templateFile);
            if (templateFileObject.exists()) {
                Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject));
                Sheet templatesheet = templateWorkbook.getSheetAt(0);
                Iterator<Row> rowIterator = templatesheet.iterator();

                while (rowIterator.hasNext()) {
                    Row templateRow = rowIterator.next();
                    Row row = sheet.createRow(startRowIndex++);

                    Iterator<Cell> cellIterator = templateRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell templateCell = cellIterator.next();
                        Cell cell = row.createCell(templateCell.getColumnIndex());
                        cell.setCellType(templateCell.getCellType());
                        switch (templateCell.getCellType()) {
                        case Cell.CELL_TYPE_BLANK:
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cell.setCellValue(templateCell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            cell.setCellValue(templateCell.getErrorCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cell.setCellValue(templateCell.getCellFormula());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            cell.setCellValue(templateCell.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            cell.setCellValue(templateCell.getStringCellValue());
                            break;
                        }
                    }
                }
            } else {
                System.err.println("Can not read " + templateFileObject.getAbsolutePath());
            }

        }

        while (this.rs.next()) {
            createRecord(totalCol, cols, sheet);
        }
        workbook.write(out);
    } catch (IOException ex) {
        throw new SQLException(ex);
    }
    return null;
}

From source file:com.bizosys.dataservice.dao.ReadXLS.java

License:Apache License

private void createRecord(int colsT, String[] cols, Sheet sheet) throws SQLException {

    Object colObj = null;/* w w  w .  ja  va2 s .c o  m*/
    String colStr = null;

    Row row = sheet.createRow(startRowIndex++);
    for (int colI = 0; colI < colsT; colI++) {
        colObj = rs.getObject(colI + 1);
        colStr = (null == colObj) ? EMPTY_STRING : colObj.toString().trim();
        Cell cell = row.createCell(colI);
        cell.setCellValue(colStr);
    }
}

From source file:com.bizosys.dataservice.dao.ReadXLS.java

License:Apache License

private void writeHeader(String[] headerCols, Sheet sheet) {
    Row headerRow = sheet.createRow(startRowIndex++);
    int cellIndex = 0;
    for (String aCol : headerCols) {
        Cell headerCell = headerRow.createCell(cellIndex++);
        headerCell.setCellValue(aCol);/* w w w  . j  a va2 s. c om*/
    }
}

From source file:com.bizosys.dataservice.dao.WriteToXls.java

License:Apache License

public void write(List<Object[]> records) throws Exception {
    Workbook workbook = getWorkbook();/*  w w  w  . j ava  2 s  . co m*/
    Sheet sheet = workbook.createSheet();

    if (null != templateFile) {
        File templateFileObject = new File(templateFile);
        if (templateFileObject.exists()) {
            Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject));
            Sheet templatesheet = templateWorkbook.getSheetAt(0);
            Iterator<Row> rowIterator = templatesheet.iterator();

            while (rowIterator.hasNext()) {
                Row templateRow = rowIterator.next();
                Row row = sheet.createRow(startRowIndex++);

                Iterator<Cell> cellIterator = templateRow.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell templateCell = cellIterator.next();
                    Cell cell = row.createCell(templateCell.getColumnIndex());
                    cell.setCellType(templateCell.getCellType());
                    switch (templateCell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        cell.setCellValue(templateCell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        cell.setCellValue(templateCell.getErrorCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        cell.setCellValue(templateCell.getCellFormula());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        cell.setCellValue(templateCell.getNumericCellValue());
                        break;
                    case Cell.CELL_TYPE_STRING:
                        cell.setCellValue(templateCell.getStringCellValue());
                        break;
                    }
                }
            }
        } else {
            System.err.println("Can not read " + templateFileObject.getAbsolutePath());
        }
    }

    for (Object[] cols : records) {
        createRecord(cols, sheet);
    }
    workbook.write(out);

}