Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. createSheet

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet.

Prototype

@Override
public XSSFSheet createSheet(String sheetname)

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

From source file:org.nekorp.workflow.desktop.servicio.reporte.global.GeneradorReporteGlobal.java

License:Apache License

@Override
public void generaReporte(ParametrosReporteGlobal param) {
    FileOutputStream fileOut = null;
    try {//from   w w  w  .java 2  s  .  com
        //List<Servicio> datos = servicioDAO.getByDate(new DateTime("2013-05-1T00:00:00.000-00:00"), new DateTime("2013-05-1T23:59:59.999-00:00"));
        List<Servicio> datos = servicioDAO.getByDate(param.getFechaInicial(), param.getFechaFinal());
        GeneradorReporteGlobal.LOGGER.debug("file:" + param.getDestination());
        GeneradorReporteGlobal.LOGGER.debug("fecha Inicial:" + param.getFechaInicial());
        GeneradorReporteGlobal.LOGGER.debug("fecha Final:" + param.getFechaFinal());
        XSSFWorkbook wb = new XSSFWorkbook();
        //estilo para las fechas
        XSSFCellStyle dateCellStyle = wb.createCellStyle();
        CreationHelper createHelper = wb.getCreationHelper();
        dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
        //estilos para las cantidades
        XSSFCellStyle moneyCellStyle = wb.createCellStyle();
        XSSFDataFormat mf = wb.createDataFormat();
        moneyCellStyle.setDataFormat(mf.getFormat("$#,##0.00"));
        Sheet sheet = wb.createSheet("Hoja1");
        int rowCount = 1;
        Cell actual;
        Row r;
        for (Servicio x : datos) {
            RenglonRG datRen = renglonFactory.build(x);
            r = sheet.createRow(rowCount);
            rowCount = rowCount + 1;
            for (int i = 0; i < valueMap.length; i++) {
                actual = r.createCell(i);
                if (valueMap[i].getType() == CellValueType.TEXT) {
                    Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue());
                    actual.setCellValue(obj.toString());
                }
                if (valueMap[i].getType() == CellValueType.DATE) {
                    Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue());
                    if (obj != null) {
                        Date val = (Date) obj;
                        actual.setCellValue(val);
                        actual.setCellStyle(dateCellStyle);
                    }
                }
                if (valueMap[i].getType() == CellValueType.AMOUNT) {
                    Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue());
                    double val = (double) obj;
                    actual.setCellValue(val);
                    actual.setCellStyle(moneyCellStyle);
                }
                if (valueMap[i].getType() == CellValueType.FORMULA) {
                    String formulaRaw = valueMap[i].getValue();
                    ST formula = new ST(formulaRaw);
                    formula.add("row", rowCount + "");
                    actual.setCellFormula(formula.render());
                    actual.setCellStyle(moneyCellStyle);
                }
            }
        }
        llenarEncabezado(sheet, 0, 0);
        fileOut = new FileOutputStream(param.getDestination());
        wb.write(fileOut);
    } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException | FormulaParseException
            | IOException ex) {
        GeneradorReporteGlobal.LOGGER.error("error al generar reporte", ex);
    } finally {
        try {
            fileOut.close();
        } catch (IOException | NullPointerException ex) {
            GeneradorReporteGlobal.LOGGER.error("error al cerrar archivo de reporte", ex);
        }
    }
}

From source file:org.obiba.mica.dataset.search.rest.harmonization.ExcelContingencyWriter.java

License:Open Source License

private void writeBody(XSSFWorkbook workbook, Mica.DatasetVariableContingenciesDto dto) {
    List<String> terms = getTermsHeaders(variable, dto);
    List<String> values = getValuesHeaders(crossVariable, dto);

    for (Mica.DatasetVariableContingencyDto c : dto.getContingenciesList()) {
        addOpalTableSheet(workbook, c, terms, values);
    }//from www. ja v a  2  s.  com

    XSSFSheet sheet = workbook.createSheet("All");
    Mica.DatasetVariableContingencyDto c = dto.getAll();
    writeTable(sheet, c, "All", terms, values);
}

From source file:org.obiba.mica.dataset.search.rest.harmonization.ExcelContingencyWriter.java

License:Open Source License

private void addOpalTableSheet(XSSFWorkbook workbook, Mica.DatasetVariableContingencyDto dto,
        List<String> terms, List<String> values) {
    String tableName;/*from  w ww .j a va2 s .c  om*/

    if (dto.hasStudyTable()) {
        tableName = String.format("%s %s", dto.getStudyTable().getTable(), dto.getStudyTable().getDceId());
    } else {
        tableName = String.format("%s %s", dto.getHarmonizationStudyTable().getTable(),
                dto.getHarmonizationStudyTable().getPopulationId());
    }

    XSSFSheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(tableName, '-'));
    writeTable(sheet, dto, tableName, terms, values);
}

From source file:org.ohdsi.jCdmBuilder.EtlReport.java

License:Apache License

/**
 * Generates the patient-specific Problem report
 * // w  w w.  j  av  a2  s . c o  m
 * @return The full path to the generated Problem report
 */
public String generateProblemReport() {
    StringUtilities.outputWithTime("Generating ETL problem list");
    String filename = generateETLProblemListFilename(folder);
    XSSFWorkbook workbook = new XSSFWorkbook();

    XSSFSheet sheet = workbook.createSheet("Problems");
    addRow(sheet, "Table", "Problem", "Person_id");
    for (Problem problem : problems.values()) {
        for (String personId : problem.personId)
            addRow(sheet, problem.table, problem.problemType, personId);
        // for (int i = 0; i < Math.min(MAX_REPORT_PROBLEM, problem.count); i++)
        // addRow(sheet, problem.table, problem.problemType, problem.personId.get(i));
        if (problem.count > MAX_REPORT_PROBLEM)
            addRow(sheet, problem.table, problem.problemType,
                    "in " + (problem.count - MAX_REPORT_PROBLEM) + " other persons");
    }
    try {
        FileOutputStream out = new FileOutputStream(new File(filename));
        workbook.write(out);
        out.close();
    } catch (IOException e) {
        throw new RuntimeException(e.getMessage());
    }
    return filename;
}

From source file:org.ohdsi.jCdmBuilder.EtlReport.java

License:Apache License

/**
 * Generates the overall ETL report/* w  w  w  .j a va  2s . com*/
 * 
 * @param codeToConceptMaps
 *            the codeToConceptMaps used in the ETL
 * @return the full path to the ETL report
 */
public String generateETLReport(CodeToConceptMap... codeToConceptMaps) {
    StringUtilities.outputWithTime("Generating ETL report");
    String filename = generateFilename(folder);
    XSSFWorkbook workbook = new XSSFWorkbook();

    XSSFSheet sheet = workbook.createSheet("Overview");
    addRow(sheet, "Source tables");
    addRow(sheet, "");
    addRow(sheet, "Table name", "Number of records");
    for (String table : incomingTableRowCounts)
        addRow(sheet, table, Integer.valueOf(incomingTableRowCounts.getCount(table)));
    addRow(sheet, "");

    addRow(sheet, "CDM tables");
    addRow(sheet, "");
    addRow(sheet, "Table name", "Number of records");
    for (String table : outgoingTableRowCounts)
        addRow(sheet, table, Integer.valueOf(outgoingTableRowCounts.getCount(table)));
    addRow(sheet, "");

    addRow(sheet, "Number of problems encountered", Long.valueOf(totalProblemCount));
    addRow(sheet, "");
    addRow(sheet, "Mapping", "Mapped unique codes", "Unmapped unique codes", "Mapped total codes",
            "Unmapped total codes");
    for (CodeToConceptMap codeToConceptMap : codeToConceptMaps) {
        int uniqueMapped = 0;
        int uniqueUnmapped = 0;
        long totalMapped = 0;
        long totalUnmapped = 0;
        CountingSet<String> codeCounts = codeToConceptMap.getCodeCounts();
        for (String code : codeCounts) {
            if (!codeToConceptMap.getConceptId(code).equals(0)) {
                uniqueMapped++;
                totalMapped += codeCounts.getCount(code);
            } else {
                uniqueUnmapped++;
                totalUnmapped += codeCounts.getCount(code);
            }
        }
        addRow(sheet, codeToConceptMap.getName(), Integer.valueOf(uniqueMapped),
                Integer.valueOf(uniqueUnmapped), Long.valueOf(totalMapped), Long.valueOf(totalUnmapped));
    }

    sheet = workbook.createSheet("Problems");
    addRow(sheet, "Table", "Description", "Nr of rows");
    for (Problem problem : problems.values())
        addRow(sheet, problem.table, problem.problemType, Long.valueOf(problem.count));

    for (CodeToConceptMap codeToConceptMap : codeToConceptMaps) {
        sheet = workbook.createSheet(codeToConceptMap.getName());
        addRow(sheet, "Frequency", "Source code", "Source code description", "Target concept ID", "Target code",
                "Target concept description");
        CountingSet<String> codeCounts = codeToConceptMap.getCodeCounts();
        List<Map.Entry<String, CountingSet.Count>> codes = new ArrayList<Map.Entry<String, CountingSet.Count>>(
                codeCounts.key2count.entrySet());
        reverseFrequencySort(codes);
        for (Map.Entry<String, CountingSet.Count> code : codes) {
            CodeData codeData = codeToConceptMap.getCodeData(code.getKey());
            if (codeData == null)
                addRow(sheet, Integer.valueOf(code.getValue().count), code.getKey(), "", Integer.valueOf(0), "",
                        "");
            else
                for (int i = 0; i < codeData.targetConceptIds.length; i++)
                    addRow(sheet, Integer.valueOf(code.getValue().count), code.getKey(), codeData.description,
                            Integer.valueOf(codeData.targetConceptIds[i]), codeData.targetCodes[i],
                            codeData.targetDescriptions[i]);
        }
    }

    try {
        FileOutputStream out = new FileOutputStream(new File(filename));
        workbook.write(out);
        out.close();
    } catch (IOException e) {
        throw new RuntimeException(e.getMessage());
    }

    return filename;
}

From source file:org.openbase.bco.ontology.lib.testing.Measurement.java

License:Open Source License

private void saveMemoryTestValues(final String sheetName, final List<Long> simpleQuMeasuredValues,
        final List<Long> complexQuMeasuredValues, final DataVolume dataVolume) {
    XSSFWorkbook workbook;
    XSSFSheet sheet;/*from w ww . j  av a2s  .  co m*/
    Row rowSimple;
    Row rowComplex;

    try {
        FileInputStream excelFile = new FileInputStream(new File(FILE_NAME));
        workbook = new XSSFWorkbook(excelFile);
        sheet = workbook.getSheet(sheetName);
        rowSimple = sheet.getRow(1);
        rowComplex = sheet.getRow(2);
    } catch (IOException ex) {
        workbook = new XSSFWorkbook();
        sheet = workbook.createSheet(sheetName);
        final Row row = sheet.createRow(0);
        rowSimple = sheet.createRow(1);
        rowComplex = sheet.createRow(2);

        row.createCell(1).setCellValue("ConfigData only");
        row.createCell(2).setCellValue("ConfigData and dayData");
        row.createCell(3).setCellValue("ConfigData and 4x dayData");
    }

    long sumSimple = 0L;
    long sumComplex = 0L;

    for (final long valueSimple : simpleQuMeasuredValues) {
        sumSimple += valueSimple;
    }
    for (final long valueComplex : complexQuMeasuredValues) {
        sumComplex += valueComplex;
    }

    int column = 0;

    switch (dataVolume) {
    case CONFIG:
        column = 1;
        break;
    case CONFIG_DAY:
        column = 2;
        break;
    case CONFIG_WEEK:
        column = 3;
        break;
    default:
        break;
    }

    System.out.println("Save date in column: " + column);

    // mean of simple trigger time
    final Cell cellMeanSimple = rowSimple.createCell(column);
    cellMeanSimple.setCellValue(sumSimple / simpleQuMeasuredValues.size());

    // mean of complex trigger time
    final Cell cellMeanComplex = rowComplex.createCell(column);
    cellMeanComplex.setCellValue(sumComplex / complexQuMeasuredValues.size());

    try {
        final File file = new File(FILE_NAME);
        file.setReadable(true, false);
        file.setExecutable(true, false);
        file.setWritable(true, false);
        System.out.println("Save data row ...");
        final FileOutputStream outputStream = new FileOutputStream(file);
        workbook.write(outputStream);
        workbook.close();
    } catch (IOException ex) {
        ExceptionPrinter.printHistory(ex, LOGGER);
    }
}

From source file:org.openbase.bco.ontology.lib.testing.Measurement.java

License:Open Source License

private void putIntoExcelFile(final String sheetName, final List<Long> simpleQuMeasuredValues,
        final List<Long> complexQuMeasuredValues, int daysCurCount) {
    // https://www.mkyong.com/java/apache-poi-reading-and-writing-excel-file-in-java/
    XSSFWorkbook workbook;
    XSSFSheet sheet;/*w  w  w.  j a v a2  s  .c om*/
    Row row;

    try {
        FileInputStream excelFile = new FileInputStream(new File(FILE_NAME));
        workbook = new XSSFWorkbook(excelFile);
        sheet = workbook.getSheet(sheetName);
    } catch (IOException ex) {
        workbook = new XSSFWorkbook();
        sheet = workbook.createSheet(sheetName);
        row = sheet.createRow(daysCurCount);

        row.createCell(0).setCellValue("Days");
        row.createCell(1).setCellValue("Triple");
        row.createCell(2).setCellValue("Mean of simple trigger");
        row.createCell(3).setCellValue("Mean of complex trigger");
    }

    row = sheet.createRow(daysCurCount + 1);

    System.out.println("simple: " + simpleQuMeasuredValues);
    System.out.println("simple count: " + simpleQuMeasuredValues.size());
    System.out.println("complex: " + complexQuMeasuredValues);
    System.out.println("complex count: " + complexQuMeasuredValues.size());

    long sumSimple = 0L;
    long sumComplex = 0L;

    for (final long valueSimple : simpleQuMeasuredValues) {
        sumSimple += valueSimple;
    }
    for (final long valueComplex : complexQuMeasuredValues) {
        sumComplex += valueComplex;
    }

    // number of days
    final Cell cellDay = row.createCell(0);
    cellDay.setCellValue(daysCurCount + 1);

    // number of triple
    final Cell cellTriple = row.createCell(1);
    cellTriple.setCellValue(numberOfTriple);

    // mean of simple trigger time
    final Cell cellMeanSimple = row.createCell(2);
    cellMeanSimple.setCellValue(sumSimple / simpleQuMeasuredValues.size());

    // mean of complex trigger time
    final Cell cellMeanComplex = row.createCell(3);
    cellMeanComplex.setCellValue(sumComplex / complexQuMeasuredValues.size());

    try {
        final File file = new File(FILE_NAME);
        file.setReadable(true, false);
        file.setExecutable(true, false);
        file.setWritable(true, false);
        System.out.println("Save data row ...");
        final FileOutputStream outputStream = new FileOutputStream(file);
        workbook.write(outputStream);
        workbook.close();
    } catch (IOException ex) {
        ExceptionPrinter.printHistory(ex, LOGGER);
    }
}

From source file:org.openelis.bean.ToDoExcelHelperBean.java

License:Open Source License

private void fillLoggedInSheet(XSSFWorkbook wb, ArrayList<AnalysisViewVO> list, boolean mySection) {
    int r;/*  www  .j  a  va  2s .  c om*/
    SystemUserPermission perm;
    ModulePermission modPerm;
    XSSFSheet sheet;

    perm = userCache.getPermission();
    modPerm = perm.getModule("patient");
    if (modPerm == null)
        modPerm = new ModulePermission();

    sheet = wb.createSheet(Messages.get().todo_loggedIn());
    r = 0;

    addHeaderRow(sheet.createRow(r++), Messages.get().todo_accNum(), Messages.get().todo_priority(),
            Messages.get().todo_domain(), Messages.get().todo_section(), Messages.get().todo_test(),
            Messages.get().todo_method(), Messages.get().todo_collected(), Messages.get().todo_received(),
            Messages.get().todo_override(), Messages.get().todo_holding(), Messages.get().todo_expCompletion(),
            Messages.get().todo_domainSpecField(), Messages.get().todo_reportTo());

    for (AnalysisViewVO a : list) {
        /*
         * if this sample has patient info, the user must have the right
         * permission to see it; also, show this analysis only if
         * doesn't belong to a deactivated domain e.g. private well
         */
        if (mySection && perm.getSection(a.getSectionName()) == null || !canViewSample(a.getDomain(), modPerm)
                || domains.get(a.getDomain()) == null)
            continue;

        addDataRow(sheet.createRow(r++), a.getAccessionNumber(), a.getPriority(), domains.get(a.getDomain()),
                a.getSectionName(), a.getTestName(), a.getMethodName(),
                TurnaroundUtil.getCombinedYM(a.getCollectionDate(), a.getCollectionTime()), a.getReceivedDate(),
                a.getAnalysisResultOverride(),
                TurnaroundUtil.getPercentHoldingUsed(a.getStartedDate(), a.getCollectionDate(),
                        a.getCollectionTime(), a.getTimeHolding()),
                TurnaroundUtil.getPercentExpectedCompletion(a.getCollectionDate(), a.getCollectionTime(),
                        a.getReceivedDate(), a.getPriority(), a.getTimeTaAverage()),
                a.getToDoDescription(), a.getPrimaryOrganizationName());
    }

    setSize(sheet, 13);
}

From source file:org.openelis.bean.ToDoExcelHelperBean.java

License:Open Source License

private void fillInitiatedSheet(XSSFWorkbook wb, ArrayList<AnalysisViewVO> list, boolean mySection) {
    int r, initDays;
    SystemUserPermission perm;/*from ww  w. j a v  a  2 s.  c om*/
    ModulePermission modPerm;
    XSSFSheet sheet;
    Datetime now;

    perm = userCache.getPermission();
    modPerm = perm.getModule("patient");
    if (modPerm == null)
        modPerm = new ModulePermission();
    now = Datetime.getInstance();
    sheet = wb.createSheet(Messages.get().todo_initiated());
    r = 0;

    addHeaderRow(sheet.createRow(r++), Messages.get().todo_accNum(), Messages.get().todo_priority(),
            Messages.get().todo_domain(), Messages.get().todo_section(), Messages.get().todo_test(),
            Messages.get().todo_method(), Messages.get().todo_holding(), Messages.get().todo_expCompletion(),
            Messages.get().todo_daysInInitiated(), Messages.get().todo_domainSpecField(),
            Messages.get().todo_reportTo());

    for (AnalysisViewVO a : list) {
        /*
         * if this sample has patient info, the user must have the right
         * permission to see it; also, show this analysis only if
         * doesn't belong to a deactivated domain e.g. private well
         */
        if (mySection && perm.getSection(a.getSectionName()) == null || !canViewSample(a.getDomain(), modPerm)
                || domains.get(a.getDomain()) == null)
            continue;

        initDays = 0;
        if (a.getStartedDate() != null)
            initDays = TurnaroundUtil.diffDays(a.getStartedDate(), now);

        addDataRow(sheet.createRow(r++), a.getAccessionNumber(), a.getPriority(), domains.get(a.getDomain()),
                a.getSectionName(), a.getTestName(), a.getMethodName(),
                TurnaroundUtil.getPercentHoldingUsed(a.getStartedDate(), a.getCollectionDate(),
                        a.getCollectionTime(), a.getTimeHolding()),
                TurnaroundUtil.getPercentExpectedCompletion(a.getCollectionDate(), a.getCollectionTime(),
                        a.getReceivedDate(), a.getPriority(), a.getTimeTaAverage()),
                initDays, a.getToDoDescription(), a.getPrimaryOrganizationName());
    }

    setSize(sheet, 11);
}

From source file:org.openelis.bean.ToDoExcelHelperBean.java

License:Open Source License

private void fillWorksheetSheet(XSSFWorkbook wb, ArrayList<ToDoWorksheetVO> list, boolean mySection) {
    int r;//from w w  w.ja v  a2s .  co  m
    SystemUserPermission perm;
    XSSFSheet sheet;

    perm = userCache.getPermission();

    sheet = wb.createSheet(Messages.get().todo_worksheet());
    r = 0;

    addHeaderRow(sheet.createRow(r++), Messages.get().todo_worksheetNum(), Messages.get().gen_username(),
            Messages.get().todo_section(), Messages.get().gen_description(), Messages.get().todo_created());

    for (ToDoWorksheetVO w : list) {
        if (mySection && perm.getSection(w.getSectionName()) == null)
            continue;

        addDataRow(sheet.createRow(r++), w.getId(), w.getSystemUserName(), w.getSectionName(),
                w.getDescription(), w.getCreatedDate());
    }

    setSize(sheet, 5);
}