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

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

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

Introduction

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

Prototype

@SuppressWarnings("resource")
public final void write(OutputStream stream) throws IOException

Source Link

Document

Write out this document to an Outputstream.

Usage

From source file:org.isatools.isacreatorconfigurator.configui.io.Utils.java

License:Open Source License

public static String createTableConfigurationEXL(String outputDir,
        Map<MappingObject, List<Display>> tableFields)
        throws DataNotCompleteException, InvalidFieldOrderException, IOException {

    String excelFileName = "ISA-config-template.xlsx";
    FileOutputStream fos = new FileOutputStream(outputDir + File.separator + excelFileName);

    String tableName = "";

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet hiddenSheet = workbook.createSheet("hiddenCV");

    Map<String, List<String>> nodups = new HashMap<String, List<String>>();
    XSSFSheet ontologyRestriction = workbook.createSheet("Restrictions");
    XSSFRow ontorow0 = ontologyRestriction.createRow((short) 0);

    ontorow0.createCell(0).setCellValue("Column Name");
    ontorow0.createCell(1).setCellValue("Ontology");
    ontorow0.createCell(2).setCellValue("Branch");
    ontorow0.createCell(3).setCellValue("Version");

    CreationHelper factory = workbook.getCreationHelper();

    //  int counting=0;
    //  int ontocounter=0;
    int lastposition = 0;

    for (MappingObject mo : tableFields.keySet()) {

        tableName = mo.getAssayName().replace("\\s", "");

        List<Display> elements = tableFields.get(mo);

        System.out.println("creating worksheet: " + tableName);

        //we create a table with 50 records by default for anything that is not an investigation file
        if (!tableName.contains("investigation")) {

            XSSFSheet tableSheet = workbook.createSheet(tableName);
            Drawing drawing = tableSheet.createDrawingPatriarch();
            CellStyle style = workbook.createCellStyle();
            XSSFRow rowAtIndex;/* www . j  a  v a 2s  .  c  o m*/

            //we create 51 rows by default for each table
            for (int index = 0; index <= 50; index++) {
                rowAtIndex = tableSheet.createRow((short) index);
            }

            //the first row is the header we need to build from the configuration declaration
            XSSFRow header = tableSheet.getRow(0);

            //we now iterated through the element found in the xml table configuration
            for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) {

                if (elements.get(fieldIndex).getFieldDetails() != null) {

                    if (elements.get(fieldIndex).getFieldDetails().isRequired() == true) {

                        XSSFCell cell = header.createCell(fieldIndex);
                        Font font = workbook.createFont();
                        font.setBoldweight(Font.BOLDWEIGHT_BOLD);

                        style.setFont(font);
                        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
                        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                        font.setColor(IndexedColors.RED.index);
                        cell.setCellStyle(style);
                        //create the header field by setting to FieldName as Cell name
                        cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName());
                        System.out.println("REQUIRED field number " + fieldIndex + " is: "
                                + elements.get(fieldIndex).getFieldDetails().getFieldName());

                        //using the ISA field description to create a Comment attached to the set
                        ClientAnchor anchor = factory.createClientAnchor();
                        Comment comment = drawing.createCellComment(anchor);
                        RichTextString rts = factory.createRichTextString(
                                elements.get(fieldIndex).getFieldDetails().getDescription());
                        comment.setString(rts);
                        cell.setCellComment(comment);
                        tableSheet.autoSizeColumn(fieldIndex);

                    } else {
                        XSSFCell cell = header.createCell(fieldIndex);
                        Font font = workbook.createFont();
                        font.setBoldweight(Font.BOLDWEIGHT_BOLD);

                        style.setFont(font);
                        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
                        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                        font.setColor(IndexedColors.BLACK.index);
                        cell.setCellStyle(style);
                        //create the header field by setting to FieldName as Cell name
                        cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName());
                        //using the ISA field description to create a Comment attached to the set
                        ClientAnchor anchor = factory.createClientAnchor();
                        Comment comment = drawing.createCellComment(anchor);
                        RichTextString rts = factory.createRichTextString(
                                elements.get(fieldIndex).getFieldDetails().getDescription());
                        comment.setString(rts);
                        cell.setCellComment(comment);
                        tableSheet.autoSizeColumn(fieldIndex);
                    }

                    //checking if the field requires controled values, i.e ISA datatype is List

                    if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.LIST) {

                        //create a hidden spreadsheet and named range with the list of val
                        //counting++; //incrementing the counter defining the position where to start the new namedrange in the hidden spreadsheet

                        //obtain the name of the ISA fields and extracting key information needed to create a unique name for the Named Range to be
                        String rangeName = elements.get(fieldIndex).getFieldDetails().getFieldName()
                                .replace("'", "").replace(" ", "").replace("Comment[", "")
                                .replace("ParameterValue[", "").replace("Characteristics[", "").replace("]", "")
                                .replace("(", "").replace(")", "");

                        //getting all the values allowed by the List Field
                        String[] fieldValues = elements.get(fieldIndex).getFieldDetails().getFieldList();

                        //System.out.println("CV : "+elements.get(fieldIndex).getFieldDetails().getFieldName()+ " values: "  + Arrays.asList(fieldValues).toString()+ "size :" +fieldValues.length);

                        //iterating through the values and creating a cell for each
                        for (int j = 0; j < fieldValues.length; j++) {
                            hiddenSheet.createRow(lastposition + j).createCell(0).setCellValue(fieldValues[j]);
                        }

                        Name namedCell = workbook.createName();

                        workbook.getNumberOfNames();

                        int k = 0;
                        int position = 0;

                        //this is to handle ISA Fields sharing the same name (in different assays)
                        //namedRanges in Excel must be unique

                        while (k < workbook.getNumberOfNames()) { //we can the total number of field to type list we have found so far.

                            //something already exists...
                            if (workbook.getNameAt(k).equals(rangeName)) {
                                // namedCell.setNameName(workbook.getNameAt(k).toString());
                                //no need to go further, we exit here and set the parameter position to use the value
                                position = k;
                                k = -1;
                            } else {
                                k++;
                            }
                        }

                        if (k > 0) { //this means this field already existed list of that type
                            //we name the new cell after it
                            namedCell.setNameName(rangeName + k);
                            System.out.println("Name Name: " + namedCell.getNameName());
                        } else { //there is already one, so we just point back to it using the position parameter
                            namedCell.setNameName(workbook.getNameAt(k).toString()); //workbook.getNameAt(position).toString()
                            System.out.println("Name Name: " + namedCell.getNameName());
                        }

                        int start = 0;
                        int end = 0;
                        start = lastposition + 1;
                        System.out.println("start: + " + start);
                        end = lastposition + fieldValues.length;
                        System.out.println("end: + " + end);

                        //                                    String reference ="hiddenCV"+"!"+convertNumToColString(0)+start+":"+ convertNumToColString(0)+end;
                        String reference = "hiddenCV" + "!$" + convertNumToColString(0) + "$" + start + ":$"
                                + convertNumToColString(0) + "$" + end;
                        namedCell.setRefersToFormula(reference);

                        start = 0;
                        end = 0;
                        DataValidationHelper validationHelper = new XSSFDataValidationHelper(tableSheet);
                        DataValidationConstraint constraint = validationHelper
                                .createFormulaListConstraint(reference);
                        CellRangeAddressList addressList = new CellRangeAddressList(1, 50, fieldIndex,
                                fieldIndex);

                        System.out.println("field index: " + fieldIndex);
                        DataValidation dataValidation = validationHelper.createValidation(constraint,
                                addressList);

                        tableSheet.addValidationData(dataValidation);

                        lastposition = lastposition + fieldValues.length;
                        System.out.println("lastposition: + " + lastposition);
                        System.out.println("reference: " + reference);
                    }

                    //                                //TODO: reformat date but this is pain in Excel
                    //                                if (elements.get(fieldIndex).getFieldDetails().getDatatype()== DataTypes.DATE) {
                    //                                    //do something
                    //                                }

                    //  If a default value has been specified in the ISAconfiguration, we set it in the Excel spreadsheet
                    if (elements.get(fieldIndex).getFieldDetails().getDefaultVal() != null) {
                        for (int i = 1; i < 51; i++) {
                            rowAtIndex = tableSheet.getRow(i);
                            XSSFCell cellThere = rowAtIndex.createCell(fieldIndex);
                            cellThere.setCellValue(elements.get(fieldIndex).getFieldDetails().getDefaultVal());
                        }
                    }

                    if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.ONTOLOGY_TERM) {
                        int count = elements.get(fieldIndex).getFieldDetails().getRecommmendedOntologySource()
                                .values().size();
                        Collection<RecommendedOntology> myList = elements.get(fieldIndex).getFieldDetails()
                                .getRecommmendedOntologySource().values();
                        for (RecommendedOntology recommendedOntology : myList) {
                            System.out.println("ONTOLOGY :" + recommendedOntology.getOntology());
                            try {
                                if (recommendedOntology.getOntology() != null) {
                                    ArrayList<String> ontoAttributes = new ArrayList<String>();
                                    ontoAttributes.add(recommendedOntology.getOntology().getOntologyID());
                                    ontoAttributes.add(recommendedOntology.getOntology().getOntologyVersion());
                                    //  ontocounter++;
                                    //                                              XSSFRow ontoRowj = ontologyRestriction.createRow(ontocounter);
                                    //                                              ontoRowj.createCell(0).setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName());
                                    //                                              ontoRowj.createCell(1).setCellValue(recommendedOntology.getOntology().getOntologyID());
                                    //                                              ontoRowj.createCell(3).setCellValue(recommendedOntology.getOntology().getOntologyVersion());

                                    if (recommendedOntology.getBranchToSearchUnder() != null) {
                                        System.out.println("ONTOLOGY BRANCH :"
                                                + recommendedOntology.getBranchToSearchUnder());
                                        //                                                  ontoRowj.createCell(2).setCellValue(recommendedOntology.getBranchToSearchUnder().toString());
                                        ontoAttributes
                                                .add(recommendedOntology.getBranchToSearchUnder().toString());
                                    } else {
                                        ontoAttributes.add("");
                                    }

                                    nodups.put(elements.get(fieldIndex).getFieldDetails().getFieldName(),
                                            ontoAttributes);
                                }
                            } catch (NullPointerException npe) {
                                System.out.println(npe);
                            }
                        }
                    }
                }
            }
        } else {

            //we now create with the Investigation Sheet
            XSSFSheet tableSheet = workbook.createSheet(tableName);

            Drawing drawing = tableSheet.createDrawingPatriarch();

            CellStyle style = workbook.createCellStyle();
            Font font = workbook.createFont();

            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            style.setFont(font);

            for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) {
                XSSFRow row = tableSheet.createRow((short) fieldIndex);
                if (elements.get(fieldIndex).getFieldDetails() != null) {
                    XSSFCell cell = row.createCell(0);
                    //create the header field by setting to FieldName as Cell name
                    cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName());

                    //using the ISA field description to create a Comment attached to the set
                    ClientAnchor anchor = factory.createClientAnchor();
                    Comment comment = drawing.createCellComment(anchor);
                    RichTextString rts = factory
                            .createRichTextString(elements.get(fieldIndex).getFieldDetails().getDescription());
                    comment.setString(rts);
                    cell.setCellComment(comment);
                    cell.setCellStyle(style);
                    tableSheet.autoSizeColumn(fieldIndex);

                    SheetConditionalFormatting sheetCF = tableSheet.getSheetConditionalFormatting();

                    //condition: if the output of the FIND function is equal to 1, then, set cell to a blue font
                    ConditionalFormattingRule rule = sheetCF
                            .createConditionalFormattingRule("FIND(Investigation,$A$1:$A$21)>1");
                    //ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(ComparisonOperator.) ;
                    FontFormatting font1 = rule.createFontFormatting();
                    font1.setFontStyle(false, true);
                    font1.setFontColorIndex(IndexedColors.BLUE.index);

                    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A21") };

                    sheetCF.addConditionalFormatting(regions, rule);
                }
            }
            tableSheet.setSelected(true);
            workbook.setSheetOrder(tableName, 0);

        }
    }

    //writes the values of ontology resources used to restrict selection in ISA fields
    int compteur = 1;

    for (Map.Entry<String, List<String>> entry : nodups.entrySet()) {
        String key = entry.getKey();
        // Object value = entry.getValue();

        System.out.println("UNIQUE RESOURCE: " + key);
        XSSFRow ontoRowj = ontologyRestriction.createRow(compteur);
        ontoRowj.createCell(0).setCellValue(key);
        ontoRowj.createCell(1).setCellValue(entry.getValue().get(0));
        ontoRowj.createCell(2).setCellValue(entry.getValue().get(2));
        ontoRowj.createCell(3).setCellValue(entry.getValue().get(1));

        compteur++;

    }

    //moving support worksheet to be the rightmost sheets in the workbook.
    //if the table corresponds to the study sample table, we move it to first position
    if (tableName.toLowerCase().contains("studysample")) {
        workbook.setSheetOrder(tableName, 1);
    }
    workbook.setSheetOrder("hiddenCV", tableFields.keySet().size() + 1);
    workbook.setSheetOrder("Restrictions", tableFields.keySet().size() + 1);
    workbook.write(fos);
    fos.close();

    String message = "Files have been saved in ";

    if (outputDir.equals("")) {
        message += "this programs directory";
    } else {
        message += outputDir;
    }

    return message;
}

From source file:org.javasoft.ciclope.servlets.ExportMaterialePratica.java

private static synchronized File createExportFile(String fileName, List<Object[]> materialeRows,
        List<Object[]> oreLavorateRows, List<Object[]> lavoriEseguitiRows) {
    //Blank workbook
    XSSFWorkbook workbook = new XSSFWorkbook();
    //Add general style for title headers to the workbook
    XSSFCellStyle titleStyle = createTitleFont(workbook);
    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Dati Pratica");
    int nextRow = 0;
    Row r = sheet.createRow(nextRow++);/*www . j a  v  a 2 s .c  o  m*/
    Cell c = r.createCell(0);
    c.setCellStyle(titleStyle);
    c.setCellValue(EXPORTED_ELEMENT.MATERIALE.getTitle());
    nextRow = AddElements(nextRow, titleStyle, EXPORTED_ELEMENT.MATERIALE, sheet, materialeRows);
    r = sheet.createRow(nextRow++);
    c = r.createCell(0);
    c.setCellStyle(titleStyle);
    c.setCellValue(EXPORTED_ELEMENT.LAVORI_ESEGUITI.getTitle());
    nextRow = AddElements(nextRow, titleStyle, EXPORTED_ELEMENT.LAVORI_ESEGUITI, sheet, lavoriEseguitiRows);
    r = sheet.createRow(nextRow++);
    c = r.createCell(0);
    c.setCellStyle(titleStyle);
    c.setCellValue(EXPORTED_ELEMENT.ORE_LAVORATE.getTitle());
    AddElements(nextRow, titleStyle, EXPORTED_ELEMENT.ORE_LAVORATE, sheet, oreLavorateRows);
    //adjust some known column size
    sheet.autoSizeColumn(1);
    try {
        //Write the workbook in file system
        FileOutputStream out = new FileOutputStream(new File(fileName));
        workbook.write(out);
        out.close();
        File f = new File(fileName);
        if (f.exists()) {
            return f;
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return null;
}

From source file:org.jboss.windup.reporting.spreadsheet.ScorecardReporter.java

License:Open Source License

@Override
public void process(ArchiveMetadata archive, File reportDirectory) {
    Validate.notNull(archive, "Archive is required, but null.");
    Validate.notNull(reportDirectory, "Report directory is required, but null.");

    File output = generateScorecardName(archive, reportDirectory);

    List<ArchiveMetadata> results = unwind(archive);

    FileOutputStream out = null;//from  w w w . j  av  a2 s  . c  om
    try {
        out = new FileOutputStream(output);
        XSSFWorkbook workbook = new XSSFWorkbook();
        // create a new sheet
        XSSFSheet s = workbook.createSheet("MigrationScoreCard");

        s.setColumnWidth(0, 70 * 256);
        s.setColumnWidth(1, 20 * 256);
        s.setColumnWidth(2, 10 * 256);
        s.setColumnWidth(3, 255 * 256);

        appendTitleRow(workbook, s, 0);
        int rownum = 1;
        for (ArchiveMetadata result : results) {
            StringBuilder notes = new StringBuilder();

            for (AbstractDecoration dr : result.getDecorations()) {
                if (dr instanceof Version) {
                    notes.append(dr.toString());
                }
            }

            Set<String> classifications = new HashSet<String>();
            double estimate = 0;
            // calculate the nodes..
            for (FileMetadata ir : result.getEntries()) {
                for (AbstractDecoration dr : ir.getDecorations()) {
                    if (dr instanceof Classification) {
                        String tempDesc = dr.getDescription();
                        tempDesc = StringUtils.removeStart(tempDesc, "Classification: ");
                        classifications.add(tempDesc);
                    }
                    if (dr.getEffort() != null && dr.getEffort() instanceof StoryPointEffort) {
                        estimate += ((StoryPointEffort) dr.getEffort()).getHours();
                    }
                }

            }

            if (classifications.size() > 0) {
                for (String classification : classifications) {
                    if (LOG.isDebugEnabled()) {
                        LOG.debug("Found: " + classification);
                    }
                    notes.append(", ").append(classification);
                }
            }
            String notesExtracted = StringUtils.removeStart(notes.toString(), ", ");
            appendNotesRow(workbook, s, rownum, result.getRelativePath(), estimate, notesExtracted);
            rownum++;
        }
        appendTotalRow(workbook, s, rownum++);

        // empty row.
        rownum++;

        appendMentoringTitleRow(workbook, s, rownum++);
        int start = rownum + 1;
        appendNotesRow(workbook, s, rownum++, "JBoss Configuration / Documentation / Mentoring", 80, "");
        appendNotesRow(workbook, s, rownum++, "JBoss Server Setup for Apps", 80, "");
        appendNotesRow(workbook, s, rownum++, "JBoss Operations Network Setup / Documentation / Mentoring", 120,
                "");
        appendNotesRow(workbook, s, rownum++, "Deployment / Fail Over Plans", 80, "");
        int end = rownum;
        appendMentoringTotalRow(workbook, s, rownum++, start, end);

        // write the workbook to the output stream
        // close our file (don't blow out our file handles
        workbook.write(out);
    } catch (IOException e) {
        LOG.error("Exception writing scorecard to: " + output.getAbsolutePath());
    } finally {
        IOUtils.closeQuietly(out);
    }
}

From source file:org.jmesa.view.excel.Excel2007ViewExporter.java

License:Apache License

@Override
public void export() throws Exception {

    XSSFWorkbook workbook = (XSSFWorkbook) this.getView().render();
    responseHeaders();/*from  ww  w  .ja  v a2s.co  m*/
    workbook.write(getHttpServletResponse().getOutputStream());
}

From source file:org.keyboardplaying.xtt.xlsx.XlsxBuilder.java

License:Apache License

/**
 * Saves the supplied workbook to the supplied file.
 *
 * @param workbook//w  w w . j  a va  2  s. co  m
 *            the workbook to write
 * @param file
 *            the destination file
 * @throws IOException
 *             if an I/O error occurs
 */
public void writeWorkbookToFile(XSSFWorkbook workbook, File file) throws IOException {
    try (FileOutputStream out = new FileOutputStream(file)) {
        workbook.write(out);
    }
}

From source file:org.mifosplatform.infrastructure.dataexport.helper.XlsFileHelper.java

License:Mozilla Public License

public static void createFile(final SqlRowSet sqlRowSet, final File file,
        final HashMap<Long, CodeValueData> codeValueMap, final HashMap<Long, AppUserData> appUserMap,
        final DataExportCoreTable coreTable) {
    try {// ww  w. j a  v  a 2 s.  c  o  m
        final SqlRowSetMetaData sqlRowSetMetaData = sqlRowSet.getMetaData();
        final int columnCount = sqlRowSetMetaData.getColumnCount();

        // Create a new spreadsheet workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        // Create a blank sheet for the workbook
        XSSFSheet sheet = workbook.createSheet();
        // create a new cell style object
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        // create a new data format object 
        XSSFDataFormat dataFormat = workbook.createDataFormat();

        int rowIndex = 0;
        int columnIndex = 0;
        Row row = sheet.createRow(rowIndex++);

        for (int i = 1; i <= columnCount; i++) {
            // create a new cell for each columns for the header row
            Cell cell = row.createCell(columnIndex++);
            // get the column label of the dataset
            String columnLabel = DataExportUtils
                    .createHumanReadableTableColumnLabel(sqlRowSetMetaData.getColumnLabel(i), coreTable);
            // set the value of the cell
            cell.setCellValue(WordUtils.capitalize(columnLabel));
        }

        while (sqlRowSet.next()) {
            columnIndex = 0;
            row = sheet.createRow(rowIndex++);

            for (int i = 1; i <= columnCount; i++) {
                Cell cell = row.createCell(columnIndex++);
                String columnTypeName = sqlRowSetMetaData.getColumnTypeName(i);
                MysqlDataType mysqlDataType = MysqlDataType.newInstance(columnTypeName);
                String columnValue = sqlRowSet.getString(i);
                String columnName = sqlRowSetMetaData.getColumnName(i);

                // replace code value id with the code value name
                AbstractMap.SimpleEntry<String, MysqlDataType> columnValueDataType = DataExportUtils
                        .replaceCodeValueIdWithValue(codeValueMap, columnName, columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                // replace app user id with respective username
                columnValueDataType = DataExportUtils.replaceAppUserIdWithUserName(appUserMap, columnName,
                        columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                if (columnValue != null) {
                    switch (mysqlDataType.getCategory()) {
                    case NUMERIC:

                        // TINYINT(1), BIT(1), etc are also treated as an alias for a BOOL in MySQL in certain versions of the JDBC connector, option tinyInt1isBit
                        // See: http://stackoverflow.com/questions/16798744/why-does-tinyint1-function-as-a-boolean-but-int1-does-not/35488212#35488212
                        if (sqlRowSetMetaData.getPrecision(i) == 1
                                && (columnValue.equals("true") || columnValue.equals("false"))) {
                            // Handle the cell as string, it is already a casted boolean:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(columnValue);

                        } else {
                            double numberAsDouble = Double.parseDouble(columnValue);

                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(numberAsDouble);
                        }
                        break;

                    case DATE_TIME:
                        DateFormat dateFormat;
                        Date date;

                        switch (mysqlDataType) {
                        case DATE:
                        case DATETIME:
                            String mysqlDateFormat = "yyyy-MM-dd";
                            String excelDateFormat = "MM/DD/YYYY";

                            if (mysqlDataType.equals(MysqlDataType.DATETIME)) {
                                mysqlDateFormat = "yyyy-MM-dd HH:mm:ss";
                                excelDateFormat = "MM/DD/YYYY HH:MM:SS";
                            }

                            dateFormat = new SimpleDateFormat(mysqlDateFormat);
                            date = dateFormat.parse(columnValue);

                            cellStyle.setDataFormat(dataFormat.getFormat(excelDateFormat));

                            cell.setCellValue(date);
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellStyle(cellStyle);
                            break;

                        default:
                            cell.setCellValue(columnValue);
                            break;
                        }
                        break;

                    default:
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(columnValue);
                        break;
                    }

                } else {
                    cell.setCellValue(columnValue);
                }
            }
        }

        //Write the workbook in file system
        FileOutputStream fileOutputStream = new FileOutputStream(file);

        workbook.write(fileOutputStream);
        fileOutputStream.close();

    } catch (Exception exception) {
        exception.printStackTrace();
    }
}

From source file:org.nekorp.workflow.desktop.servicio.reporte.cliente.GeneradorReporteImp.java

License:Apache License

@Override
public void generaReporte(ParametrosReporte param) {
    FileOutputStream fileOut = null;
    ReporteCliente datos = datosReporteClienteFactory.getData();
    try {//from   w  w w.  j  a v  a  2 s  .  c  o m
        GeneradorReporteImp.LOGGER.info("file:" + param.getDestination());
        //Workbook wb = WorkbookFactory.create(template);
        XSSFWorkbook wb = new XSSFWorkbook();
        int maxColumn = 0;
        //CreationHelper createHelper = wb.getCreationHelper();
        //se crea la primera hoja
        Sheet sheet = wb.createSheet("Hoja1");
        BordeSeccion borde = new BordeSeccion();
        ContextoSeccion contexto = new ContextoSeccion();
        contexto.setWb(wb);
        contexto.setSheet(sheet);
        //encabezado
        borde.setLeftColumn(1);
        borde.setUpperRow(1);
        BordeSeccion bordeEncabezado = encabezadoReporteCliente.generar(borde, contexto, datos);
        if (bordeEncabezado.getRightColumn() > maxColumn) {
            maxColumn = bordeEncabezado.getRightColumn();
        }
        //datos auto
        borde.setLeftColumn(1);
        borde.setUpperRow(bordeEncabezado.getLowerRow() + 2);
        BordeSeccion bordeAuto = datosAutoReporteCliente.generar(borde, contexto, datos);
        if (bordeAuto.getRightColumn() > maxColumn) {
            maxColumn = bordeAuto.getRightColumn();
        }
        //mecanica
        borde.setLeftColumn(1);
        borde.setUpperRow(bordeAuto.getLowerRow() + 2);
        BordeSeccion bordeMecanica = costoMecanicaReporteCliente.generar(borde, contexto, datos);
        if (bordeMecanica.getRightColumn() > maxColumn) {
            maxColumn = bordeMecanica.getRightColumn();
        }
        //hojalateria
        borde.setLeftColumn(bordeMecanica.getRightColumn() + 1);
        BordeSeccion bordeHojalateria = costoHojalateriaReporteCliente.generar(borde, contexto, datos);
        if (bordeHojalateria.getRightColumn() > maxColumn) {
            maxColumn = bordeHojalateria.getRightColumn();
        }
        //total servicio
        if (bordeMecanica.getLowerRow() < bordeHojalateria.getLowerRow()) {
            borde.setUpperRow(bordeHojalateria.getLowerRow() + 2);
        } else {
            borde.setUpperRow(bordeMecanica.getLowerRow() + 2);
        }
        borde.setLeftColumn(1);
        BordeSeccion bordeTotalServicio = totalServicioReporteCliente.generar(borde, contexto, datos);
        if (bordeTotalServicio.getRightColumn() > maxColumn) {
            maxColumn = bordeTotalServicio.getRightColumn();
        }
        //bitacora
        borde.setLeftColumn(1);
        borde.setUpperRow(bordeTotalServicio.getLowerRow() + 3);
        BordeSeccion bordeBitacora = bitacoraReporteCliente.generar(borde, contexto, datos);
        if (bordeBitacora.getRightColumn() > maxColumn) {
            maxColumn = bordeBitacora.getRightColumn();
        }
        for (int i = 1; i <= maxColumn; i++) {
            sheet.autoSizeColumn(i);
        }
        fileOut = new FileOutputStream(param.getDestination());
        wb.write(fileOut);
        fileOut.close();
    } catch (Exception ex) {
        GeneradorReporteImp.LOGGER.error("error al generar reporte", ex);
    } finally {
        try {
            fileOut.close();
        } catch (IOException | NullPointerException ex) {
            GeneradorReporteImp.LOGGER.error("error al cerrar archivo de reporte", ex);
        }
    }
}

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  .ja  v  a2s . c om*/
        //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

public ByteArrayOutputStream write(Mica.DatasetVariableContingenciesDto dto) throws IOException {
    XSSFWorkbook workbook = createWorkbook();
    writeBody(workbook, dto);/* w w  w.  java  2 s  .co m*/
    ByteArrayOutputStream ba = new ByteArrayOutputStream();
    workbook.write(ba);

    return ba;
}

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

License:Open Source License

public ByteArrayOutputStream write(Mica.DatasetVariableContingencyDto dto) throws IOException {
    XSSFWorkbook workbook = createWorkbook();
    writeBody(workbook, dto);/*w w w.  j a  va  2 s  . c o  m*/
    ByteArrayOutputStream ba = new ByteArrayOutputStream();
    workbook.write(ba);

    return ba;
}