Example usage for org.apache.poi.ss.usermodel Workbook createCellStyle

List of usage examples for org.apache.poi.ss.usermodel Workbook createCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. createCellStyle

Introduction

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

Prototype

CellStyle createCellStyle();

Source Link

Document

Create a new Cell style and add it to the workbook's style table

Usage

From source file:org.isisaddons.module.excel.dom.ExcelConverter.java

License:Apache License

protected CellStyle createDateFormatCellStyle(final Workbook wb) {
    final CreationHelper createHelper = wb.getCreationHelper();
    final short dateFormat = createHelper.createDataFormat().getFormat("yyyy-mm-dd");
    final CellStyle dateCellStyle = wb.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat);
    return dateCellStyle;
}

From source file:org.isisaddons.wicket.excel.cpt.ui.ExcelFileModel.java

License:Apache License

protected CellStyle createDateFormatCellStyle(final Workbook wb) {
    CreationHelper createHelper = wb.getCreationHelper();
    short dateFormat = createHelper.createDataFormat().getFormat("yyyy-mm-dd");
    CellStyle dateCellStyle = wb.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat);
    return dateCellStyle;
}

From source file:org.jaffa.qm.finder.apis.ExcelExportService.java

License:Open Source License

public static Workbook generateExcel(QueryServiceConfig master, QueryServiceConfig child, String sheetName)
        throws ClassNotFoundException, NoSuchMethodException, InstantiationException, IllegalAccessException,
        IllegalArgumentException, InvocationTargetException {

    Workbook wb = null;
    String legacyExport = (String) ContextManagerFactory.instance()
            .getProperty("jaffa.widgets.exportToExcel.legacy");
    if (legacyExport != null && legacyExport.equals("T")) {
        wb = new HSSFWorkbook();
    } else {/*w  w w.ja  v a2s.c o m*/
        wb = new SXSSFWorkbook(100);
    }
    try {
        // Creating worksheet
        Sheet sheet = null;
        if (sheetName != null)
            sheet = wb.createSheet(sheetName);
        else
            sheet = wb.createSheet();

        // creating a custom palette for the workbook
        CellStyle style = wb.createCellStyle();
        style = wb.createCellStyle();

        // setting the foreground color to gray
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        // Setting the border for the cells
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setAlignment(CellStyle.ALIGN_CENTER);

        // setting font weight
        Font titleFont = wb.createFont();
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(titleFont);

        int rowNum = 0;
        Row headerRow = sheet.createRow(rowNum);
        int colIndex = 0;
        for (Object o : master.getColumnModel()) {
            String columnTitle = (String) ((DynaBean) o).get("header");
            if (columnTitle == null || columnTitle.length() == 0)
                columnTitle = (String) ((DynaBean) o).get("mapping");

            headerRow.createCell(colIndex).setCellValue(columnTitle);
            Cell cell = headerRow.getCell(colIndex);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(colIndex);
            colIndex += 1;
        }

        // Generate the Excel output by creating a simple HTML table
        if (child != null) {
            for (Object o : child.getColumnModel()) {
                String columnTitle = (String) ((DynaBean) o).get("header");
                if (columnTitle == null || columnTitle.length() == 0)
                    columnTitle = (String) ((DynaBean) o).get("mapping");

                headerRow.createCell(colIndex).setCellValue(columnTitle);
                Cell cell = headerRow.getCell(colIndex);
                cell.setCellStyle(style);
                sheet.autoSizeColumn(colIndex);
                colIndex += 1;

            }
        }

        // Invoke the query and obtain an array of Graph objects
        Object[] queryOutput = invokeQueryService(master.getCriteriaClassName(), master.getCriteriaObject(),
                master.getServiceClassName(), master.getServiceClassMethodName());

        // Add the data rows
        if (queryOutput != null) {
            for (Object row : queryOutput) {
                Object[] detailQueryOutput = new Object[0];
                if (child == null) {
                    rowNum += 1;
                    Row dataRow = sheet.createRow((short) rowNum);
                    int colNum = 0;
                    // extract the columns from master object
                    for (Object o : master.getColumnModel()) {
                        String mapping = (String) ((DynaBean) o).get("mapping");
                        Object value = null;
                        if (mapping.startsWith("appFields.")) {
                            mapping = mapping.substring(10);
                            try {
                                Object[] appFields = (Object[]) PropertyUtils.getProperty(row,
                                        "applicationFields");
                                for (Object field : appFields) {
                                    String name = (String) PropertyUtils.getProperty(field, "name");
                                    if (name.equals(mapping)) {
                                        value = (String) PropertyUtils.getProperty(field, "value");
                                    }
                                }
                            } catch (Exception e) {
                                if (log.isDebugEnabled())
                                    log.debug("Property not found: " + mapping, e);
                            }
                        } else {
                            try {
                                value = PropertyUtils.getProperty(row, mapping);
                            } catch (Exception e) {
                                if (log.isDebugEnabled())
                                    log.debug("Property not found: " + mapping, e);
                            }
                        }
                        dataRow.createCell(colNum).setCellValue(format(value, (DynaBean) o));
                        colNum += 1;
                    }
                } else { //child is not null
                    // load the child rows
                    String detailCriteriaObject = child.getCriteriaObject();
                    for (int i = 0; i < child.getMasterKeyFieldNames().length; i++) {
                        String kfn = child.getMasterKeyFieldNames()[i];
                        try {
                            String keyValue = (String) PropertyUtils.getProperty(row, kfn);
                            detailCriteriaObject = detailCriteriaObject.replace("{" + i + "}", keyValue);
                        } catch (Exception e) {
                            if (log.isDebugEnabled())
                                log.debug("Key property not found: " + kfn, e);
                        }
                    }
                    detailQueryOutput = invokeQueryService(child.getCriteriaClassName(), detailCriteriaObject,
                            child.getServiceClassName(), "query");

                    // add the child columns
                    if (detailQueryOutput != null && detailQueryOutput.length > 0) {
                        for (Object detailRow : detailQueryOutput) {
                            rowNum += 1;
                            Row dataRow = sheet.createRow((short) rowNum);

                            int colNum = 0;
                            // extract the columns from master object
                            for (Object obj : master.getColumnModel()) {
                                String masterMapping = (String) ((DynaBean) obj).get("mapping");
                                Object masterValue = null;
                                try {
                                    masterValue = PropertyUtils.getProperty(row, masterMapping);
                                } catch (Exception e) {
                                    if (log.isDebugEnabled())
                                        log.debug("Property not found: " + masterMapping, e);
                                }

                                dataRow.createCell(colNum).setCellValue(format(masterValue, (DynaBean) obj));
                                colNum += 1;
                            }

                            for (Object o : child.getColumnModel()) {
                                String mapping = (String) ((DynaBean) o).get("mapping");
                                Object value = null;
                                try {
                                    value = PropertyUtils.getProperty(detailRow, mapping);
                                } catch (Exception e) {
                                    if (log.isDebugEnabled())
                                        log.debug("Property not found in child result: " + mapping, e);
                                }

                                dataRow.createCell(colNum).setCellValue(format(value, (DynaBean) o));
                                colNum += 1;
                            }
                        }
                    }
                }
            }
        }

    } catch (Exception e) {
        e.printStackTrace();
    }
    return wb;
}

From source file:org.jaffa.ria.finder.apis.ExcelExportService.java

License:Open Source License

public static Workbook generateExcel(QueryServiceConfig master, QueryServiceConfig child, String sheetName)
        throws ClassNotFoundException, NoSuchMethodException, InstantiationException, IllegalAccessException,
        IllegalArgumentException, InvocationTargetException {

    Workbook wb = null;
    String legacyExport = (String) ContextManagerFactory.instance()
            .getProperty("jaffa.widgets.exportToExcel.legacy");
    if (legacyExport != null && legacyExport.equals("T")) {
        wb = new HSSFWorkbook();
    } else {/* w  w w. ja  v a 2  s.c o  m*/
        wb = new SXSSFWorkbook(100);
    }
    try {
        // Creating worksheet
        Sheet sheet = null;
        if (sheetName != null) {
            if (sheetName.length() > 31)
                sheetName = sheetName.substring(0, 31);
            char replaceChar = '_';
            sheetName = sheetName.replace('\u0003', replaceChar).replace(':', replaceChar)
                    .replace('/', replaceChar).replace("\\\\", Character.toString(replaceChar))
                    .replace('?', replaceChar).replace('*', replaceChar).replace(']', replaceChar)
                    .replace('[', replaceChar);
            sheet = wb.createSheet(sheetName);
        } else
            sheet = wb.createSheet();

        // creating a custom palette for the workbook
        CellStyle style = wb.createCellStyle();
        style = wb.createCellStyle();

        // setting the foreground color to gray
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        // Setting the border for the cells
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setAlignment(CellStyle.ALIGN_CENTER);

        // setting font weight
        Font titleFont = wb.createFont();
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(titleFont);

        int rowNum = 0;
        Row headerRow = sheet.createRow(rowNum);
        int colIndex = 0;
        for (Object o : master.getColumnModel()) {
            String columnTitle = (String) ((DynaBean) o).get("header");
            if (columnTitle == null || columnTitle.length() == 0)
                columnTitle = (String) ((DynaBean) o).get("mapping");

            headerRow.createCell(colIndex).setCellValue(columnTitle);
            Cell cell = headerRow.getCell(colIndex);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(colIndex);
            colIndex += 1;
        }

        // Generate the Excel output by creating a simple HTML table
        if (child != null) {
            for (Object o : child.getColumnModel()) {
                String columnTitle = (String) ((DynaBean) o).get("header");
                if (columnTitle == null || columnTitle.length() == 0)
                    columnTitle = (String) ((DynaBean) o).get("mapping");

                headerRow.createCell(colIndex).setCellValue(columnTitle);
                Cell cell = headerRow.getCell(colIndex);
                cell.setCellStyle(style);
                sheet.autoSizeColumn(colIndex);
                colIndex += 1;

            }
        }

        // Invoke the query and obtain an array of Graph objects
        Object[] queryOutput = invokeQueryService(master.getCriteriaClassName(), master.getCriteriaObject(),
                master.getServiceClassName(), master.getServiceClassMethodName());

        // Add the data rows
        if (queryOutput != null) {
            for (Object row : queryOutput) {
                Object[] detailQueryOutput = new Object[0];
                if (child == null) {
                    rowNum += 1;
                    Row dataRow = sheet.createRow((short) rowNum);
                    int colNum = 0;
                    // extract the columns from master object
                    for (Object o : master.getColumnModel()) {
                        String mapping = (String) ((DynaBean) o).get("mapping");
                        Object value = null;
                        try {
                            value = PropertyUtils.getProperty(row, mapping);
                        } catch (Exception e) {
                            if (log.isDebugEnabled())
                                log.debug("Property not found: " + mapping, e);
                        }

                        dataRow.createCell(colNum).setCellValue(format(value, (DynaBean) o));
                        colNum += 1;
                    }
                } else { //child is not null
                    // load the child rows
                    String detailCriteriaObject = child.getCriteriaObject();
                    for (int i = 0; i < child.getMasterKeyFieldNames().length; i++) {
                        String kfn = child.getMasterKeyFieldNames()[i];
                        try {
                            String keyValue = (String) PropertyUtils.getProperty(row, kfn);
                            detailCriteriaObject = detailCriteriaObject.replace("{" + i + "}", keyValue);
                        } catch (Exception e) {
                            if (log.isDebugEnabled())
                                log.debug("Key property not found: " + kfn, e);
                        }
                    }
                    detailQueryOutput = invokeQueryService(child.getCriteriaClassName(), detailCriteriaObject,
                            child.getServiceClassName(), "query");

                    // add the child columns
                    if (detailQueryOutput != null && detailQueryOutput.length > 0) {
                        for (Object detailRow : detailQueryOutput) {
                            rowNum += 1;
                            Row dataRow = sheet.createRow((short) rowNum);

                            int colNum = 0;
                            // extract the columns from master object
                            for (Object obj : master.getColumnModel()) {
                                String masterMapping = (String) ((DynaBean) obj).get("mapping");
                                Object masterValue = null;
                                try {
                                    masterValue = PropertyUtils.getProperty(row, masterMapping);
                                } catch (Exception e) {
                                    if (log.isDebugEnabled())
                                        log.debug("Property not found: " + masterMapping, e);
                                }

                                dataRow.createCell(colNum).setCellValue(format(masterValue, (DynaBean) obj));
                                colNum += 1;
                            }

                            for (Object o : child.getColumnModel()) {
                                String mapping = (String) ((DynaBean) o).get("mapping");
                                Object value = null;
                                try {
                                    value = PropertyUtils.getProperty(detailRow, mapping);
                                } catch (Exception e) {
                                    if (log.isDebugEnabled())
                                        log.debug("Property not found in child result: " + mapping, e);
                                }

                                dataRow.createCell(colNum).setCellValue(format(value, (DynaBean) o));
                                colNum += 1;
                            }
                        }
                    }
                }
            }
        }

    } catch (Exception e) {
        e.printStackTrace();
    }
    return wb;
}

From source file:org.jboss.dashboard.displayer.table.ExportTool.java

License:Apache License

private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;//from w ww. j a  va 2  s . c  o m

    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 12);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(titleFont);
    style.setWrapText(false);
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
    styles.put("header", style);

    Font cellFont = wb.createFont();
    cellFont.setFontHeightInPoints((short) 10);
    cellFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(3)));
    styles.put("integer_number_cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(4)));
    styles.put("decimal_number_cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text"));
    styles.put("text_cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat()
            .getFormat(DateFormatConverter.convert(LocaleManager.currentLocale(), dateFormatPattern)));
    styles.put("date_cell", style);
    return styles;
}

From source file:org.jcvi.ometa.utils.JsonProducer.java

License:Open Source License

public void jsonHelper(String projectNames, String attributes, String screenAttributes, String sorting,
        String fileName, String filePath, String domain) {
    String PROJECT_STATUS = "Project Status";
    try {//from w  w  w. j  av a 2s.co  m
        JSONObject json = new JSONObject();

        File directory = new File(filePath);
        if (!directory.exists() || !directory.isDirectory()) {
            if ((new File(directory.getParent())).canWrite())
                directory.mkdir();
            else
                throw new Exception();
        }
        //Json file Creation
        File tempFile = new File(filePath + File.separator + fileName + "_temp.json");
        FileWriter fileWriter = new FileWriter(tempFile);
        BufferedWriter bufferedWriter = new BufferedWriter(fileWriter);

        //Normal status data retrieval
        LookupValue tempLookupValue;

        List<String> projectNameList = new ArrayList<String>();
        if (projectNames.contains(","))
            projectNameList.addAll(Arrays.asList(projectNames.split(",")));
        else
            projectNameList.add(projectNames);

        List<String> availableAttributes = new ArrayList<String>();
        availableAttributes.add("Sample Name");

        List<Project> projects = pseEjb.getProjects(projectNameList);
        List<Long> projectIds = new ArrayList<Long>();
        Map<String, Long> projectNameVsId = new HashMap<String, Long>();
        for (Project project : projects) {
            projectIds.add(project.getProjectId());
            projectNameVsId.put(project.getProjectName(), project.getProjectId());
        }

        List<ProjectMetaAttribute> allProjectMetaAttributes = pseEjb.getProjectMetaAttributes(projectIds);
        for (ProjectMetaAttribute pma : allProjectMetaAttributes) {
            if (!availableAttributes.contains(pma.getLookupValue().getName()))
                availableAttributes.add(pma.getLookupValue().getName());
        }
        List<SampleMetaAttribute> allSampleMetaAttributes = pseEjb.getSampleMetaAttributes(projectIds);
        for (SampleMetaAttribute sma : allSampleMetaAttributes) {
            if (!availableAttributes.contains(sma.getLookupValue().getName()))
                availableAttributes.add(sma.getLookupValue().getName());
        }
        List<EventMetaAttribute> allEventMetaAttributes = pseEjb.getEventMetaAttributes(projectIds);
        for (EventMetaAttribute ema : allEventMetaAttributes) {
            if (!availableAttributes.contains(ema.getLookupValue().getName()))
                availableAttributes.add(ema.getLookupValue().getName());
        }

        List<String> parameterizedAttributes = null;
        if (attributes == null || attributes.equals("") || "ALL".equals(attributes)) {
            parameterizedAttributes = availableAttributes;
        } else {
            parameterizedAttributes = new ArrayList<String>();

            ArrayList<String> tokenizedAttribute = new ArrayList<String>(Arrays.asList(attributes.split(",")));

            for (String tempAttribute : tokenizedAttribute) {
                if (availableAttributes.contains(tempAttribute))
                    parameterizedAttributes.add(tempAttribute);
            }
        }
        parameterizedAttributes.removeAll(Arrays.asList(forbiddenAttributes));

        /*------------ XLS Part ------------*/
        //Excel file Creation
        Workbook workBook = new HSSFWorkbook();
        Sheet workSheet = workBook.createSheet();
        int cellIndex = 0, rowIndex = 0;
        Row singleRow = workSheet.createRow(rowIndex++);
        Cell headerCell = null;

        //Header row cell style
        CellStyle style = workBook.createCellStyle();
        style.setFillBackgroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex());
        style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        Font font = workBook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(font);
        /*------------ XLS Part END ------------*/

        List<String> attributeList = new ArrayList<String>();

        for (String tempAttribute : parameterizedAttributes) {
            attributeList.add(tempAttribute);
            headerCell = singleRow.createCell(cellIndex++);
            headerCell.setCellValue(tempAttribute);
            headerCell.setCellStyle(style);
        }

        if (screenAttributes == null || screenAttributes.equals("") || screenAttributes.equals("ALL")) {
            json.put("attributes", attributeList);
        } else {
            json.put("attributes", Arrays.asList(screenAttributes.split(",")));
        }

        json.put("sorting", (sorting == null || sorting.isEmpty() || sorting.equals("-") ? null : sorting));
        json.put("projectNames", projectNames);

        List<ProjectAttribute> allProjectAttributes = pseEjb.getProjectAttributes(projectIds);
        Map<Long, List<ProjectAttribute>> projIdVsAttributes = new HashMap<Long, List<ProjectAttribute>>();
        for (ProjectAttribute pa : allProjectAttributes) {
            List<ProjectAttribute> paList = projIdVsAttributes.get(pa.getProjectId());
            if (paList == null) {
                paList = new ArrayList<ProjectAttribute>();
                projIdVsAttributes.put(pa.getProjectId(), paList);
            }
            paList.add(pa);
        }

        List<Sample> allSamplesAllProjects = pseEjb.getSamplesForProjects(projectIds);
        Map<Long, List<Sample>> projectIdVsSampleList = new HashMap<Long, List<Sample>>();
        for (Sample sample : allSamplesAllProjects) {
            List<Sample> thisProjectsSamples = projectIdVsSampleList.get(sample.getProjectId());
            if (thisProjectsSamples == null) {
                thisProjectsSamples = new ArrayList<Sample>();
                projectIdVsSampleList.put(sample.getProjectId(), thisProjectsSamples);
            }
            thisProjectsSamples.add(sample);
        }

        /************* Main LOOP starts *****************/
        List<JSONObject> sampleList = new ArrayList<JSONObject>();
        List<String> statusList = new ArrayList<String>();
        List<JSONObject> sumList = new ArrayList<JSONObject>();

        for (Project project : projects) {
            JSONObject currSum = new JSONObject();

            if (project.getIsPublic() == 0)
                continue;

            Long tempProjectId = project.getProjectId();
            List<ProjectAttribute> paList = projIdVsAttributes.get(tempProjectId);
            Map<String, Object> projectAttrMap = new HashMap<String, Object>();
            if (paList != null) {
                for (ProjectAttribute pa : paList) {
                    ProjectMetaAttribute projectMeta = pa.getMetaAttribute();
                    tempLookupValue = projectMeta.getLookupValue();
                    projectAttrMap.put(tempLookupValue.getName(),
                            ModelValidator.getModelValue(tempLookupValue, pa));

                    if (projectMeta.getLabel() != null) { //add another key-value pair for a labeled attribute
                        projectAttrMap.put(projectMeta.getLabel(),
                                ModelValidator.getModelValue(tempLookupValue, pa));
                    }
                }
            }

            if (!projectAttrMap.containsKey(Constants.ATTR_PROJECT_NAME))
                projectAttrMap.put(Constants.ATTR_PROJECT_NAME, project.getProjectName());

            currSum.put("p_n", project.getProjectName());
            currSum.put("p_s", projectAttrMap.get(PROJECT_STATUS));
            currSum.put("p_g", projectAttrMap.get("Project Group"));

            List<Long> sampleIdList = getSampleIdList(getSamplesFromList(projectIdVsSampleList, tempProjectId));
            Map<Long, List<SampleAttribute>> sampleIdVsAttributeList = getSampleVsAttributeList(sampleIdList);
            Map<Long, List<Event>> sampleIdVsEventList = getSampleIdVsEventList(sampleIdList);

            List<Sample> samplesForProject = getSamplesFromList(projectIdVsSampleList, tempProjectId);
            currSum.put("tot", samplesForProject.size());

            for (Sample sample : samplesForProject) {
                Map<String, Object> sampleAttrMap = new HashMap<String, Object>();
                sampleAttrMap.putAll(projectAttrMap);
                sampleAttrMap.put(Constants.ATTR_SAMPLE_NAME, sample.getSampleName());
                sampleAttrMap.put("sampleId", sample.getSampleId());

                List<SampleAttribute> sampleAttributes = sampleIdVsAttributeList.get(sample.getSampleId());
                if (sampleAttributes != null && sampleAttributes.size() > 0) {
                    for (SampleAttribute sa : sampleAttributes) {
                        if (sa.getMetaAttribute() == null)
                            continue;
                        SampleMetaAttribute sampleMeta = sa.getMetaAttribute();
                        tempLookupValue = sampleMeta.getLookupValue();
                        Object sav = ModelValidator.getModelValue(tempLookupValue, sa);
                        sampleAttrMap.put(tempLookupValue.getName(), sav);

                        if (sampleMeta.getLabel() != null) { //add another key-value pair for a labeled attribute
                            sampleAttrMap.put(sampleMeta.getLabel(), sav);
                        }

                        if (SAMPLE_STATUS.equals(tempLookupValue.getName())) {
                            String currStatus = (String) sav;
                            if (!statusList.contains(currStatus)) //add new status value
                                statusList.add(currStatus);
                            currSum.put(currStatus,
                                    currSum.has(currStatus) ? currSum.getInt(currStatus) + 1 : 1); //count
                        }

                    }
                }

                List<Event> sampleEvents = sampleIdVsEventList.get(sample.getSampleId());
                if (sampleEvents != null && sampleEvents.size() > 0) {
                    Map<Long, List<EventAttribute>> eventIdVsAttributes = getEventIdVsAttributeList(
                            sampleEvents, tempProjectId);
                    //skip sample status value in event attributes
                    String[] skipArrForEventAttribute = { "Sample Status" };

                    for (Event evt : sampleEvents) {
                        List<EventAttribute> eventAttributes = eventIdVsAttributes.get(evt.getEventId());
                        if (eventAttributes == null)
                            continue;

                        sampleAttrMap.putAll(CommonTool.getAttributeValueMap(eventAttributes, false,
                                skipArrForEventAttribute));
                    }
                }

                if (!sampleAttrMap.containsKey("Organism")) { //manually add Organism attribute if not exist for GCID projects
                    sampleAttrMap.put("Organism", "");
                }

                JSONObject sampleJsonObj = new JSONObject();
                for (String key : sampleAttrMap.keySet()) {
                    //this is custom decorating process for json data file only
                    //in status.shtml page, link on an organism should land to the project page rather than sample detail page
                    if (key.equals("Organism")) {
                        String organismVal = (String) sampleAttrMap.get(key);
                        if (organismVal == null) { //get different attribute value for GCID projects
                            organismVal = (String) sampleAttrMap.get("Species Source Common Name(CS4)");
                        }

                        sampleJsonObj.put("OrganismUrl", (PROD_SERVER_ADDRESS + Constants.SAMPLE_DETAIL_URL
                                + "iss=true" + "&projectName=" + project.getProjectName() + "&projectId="
                                + project.getProjectId() + "&sampleName=" + sampleAttrMap.get("Sample Name")
                                + "&sampleId=" + sampleAttrMap.get("sampleId")).replaceAll("\\\"", "\\\\\""));
                        if (domain != null && !"none".equals(domain)) {
                            String projectGroup = (String) sampleAttrMap.get("Project Group");
                            organismVal = convertIntoATag(String.format(Constants.PROJECT_SPECIFIC_PAGE, domain, //hostName != null && hostName.contains("spike") ? fileName + "-dev" : fileName,
                                    (projectGroup == null ? "" : projectGroup.toLowerCase()),
                                    project.getProjectName().replaceAll(" ", "_")), organismVal);
                        }
                        sampleJsonObj.put(key, organismVal);
                    } else {
                        sampleJsonObj.put(key, CommonTool.decorateAttribute(sampleAttrMap, key, project));
                    }
                }
                sampleList.add(sampleJsonObj);

                cellIndex = 0;
                singleRow = workSheet.createRow(rowIndex++);
                for (String tempAttribute : parameterizedAttributes) {
                    singleRow.createCell(cellIndex++)
                            .setCellValue(sampleAttrMap.get(tempAttribute) != null
                                    ? "" + sampleAttrMap.get(tempAttribute)
                                    : "");
                }
            }
            sumList.add(currSum);
        }

        JSONObject sumMap = new JSONObject();
        sumMap.put("s_l", statusList);
        sumMap.put("data", sumList);
        json.put("sums", sumMap);

        json.put("samples", sampleList);
        //bufferedWriter.write("]");
        bufferedWriter.write(json.toString());
        bufferedWriter.close();

        if (tempFile.exists() && tempFile.length() > 0) {
            File dataFile = new File(filePath + File.separator + fileName + ".json");
            tempFile.renameTo(dataFile);

            FileOutputStream fileOut = new FileOutputStream(filePath + File.separator + fileName + ".xls");
            workBook.write(fileOut);
            fileOut.close();
        } else
            throw new Exception("Failure in retrieving data for " + fileName
                    + ". File does not exist or file size is zero.");

        logger.info("[JsonProducer-MBean] JsonProducer process succeeded for " + projectNames);
    } catch (Exception ex) {
        logger.info("[JsonProducer-MBean] JsonProducer failed for " + projectNames);
        ex.printStackTrace();

        /*if( hostName.contains( "dmzweb" ) ) { //Send error notification for DMZs only
        new EmailSender().send(
                "json",
                "[PST]Failure in generating Json Data file on : " + hostName,
                ex.toString()
        );
        }*/
    }
}

From source file:org.kuali.test.runner.output.PoiHelper.java

License:Educational Community License

private void createPoiCellStyles(Workbook workbook) {

    // create bold cell style
    Font font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 10);
    cellStyleBold = workbook.createCellStyle();
    cellStyleBold.setFont(font);/*  ww w  .j av  a  2  s  . c  o m*/

    // create standard cell style
    font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
    font.setFontHeightInPoints((short) 10);
    cellStyleNormal = workbook.createCellStyle();
    cellStyleNormal.setFont(font);
    cellStyleNormal.setVerticalAlignment(CellStyle.VERTICAL_TOP);

    // create test header cell style
    font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 10);
    cellStyleTestHeader = workbook.createCellStyle();
    cellStyleTestHeader.setFont(font);
    cellStyleTestHeader.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.index);
    cellStyleTestHeader.setFillPattern(CellStyle.SOLID_FOREGROUND);

    // create timestamp cell style
    font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
    font.setFontHeightInPoints((short) 10);
    cellStyleTimestamp = workbook.createCellStyle();
    cellStyleTimestamp.setFont(font);
    cellStyleTimestamp.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
    cellStyleTimestamp.setVerticalAlignment(CellStyle.VERTICAL_TOP);

    // create timestamp cell style
    font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
    font.setFontHeightInPoints((short) 10);
    cellStyleTime = workbook.createCellStyle();
    cellStyleTime.setFont(font);
    cellStyleTime.setDataFormat(workbook.createDataFormat().getFormat("hh:mm:ss"));
    cellStyleTime.setVerticalAlignment(CellStyle.VERTICAL_TOP);

    // create success cell style
    font = workbook.createFont();
    font.setColor(IndexedColors.DARK_GREEN.index);
    font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
    font.setFontHeightInPoints((short) 10);
    cellStyleSuccess = workbook.createCellStyle();
    cellStyleSuccess.setFont(font);
    cellStyleSuccess.setVerticalAlignment(CellStyle.VERTICAL_TOP);

    // create ignore cell style
    font = workbook.createFont();
    font.setColor(IndexedColors.BROWN.index);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 10);
    cellStyleIgnore = workbook.createCellStyle();
    cellStyleIgnore.setFont(font);
    cellStyleIgnore.setVerticalAlignment(CellStyle.VERTICAL_TOP);

    // create warning cell style
    font = workbook.createFont();
    font.setColor(IndexedColors.DARK_YELLOW.index);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 10);
    cellStyleWarning = workbook.createCellStyle();
    cellStyleWarning.setFont(font);
    cellStyleWarning.setVerticalAlignment(CellStyle.VERTICAL_TOP);

    // create error cell style
    font = workbook.createFont();
    font.setColor(IndexedColors.DARK_RED.index);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 10);
    cellStyleError = workbook.createCellStyle();
    cellStyleError.setFont(font);
    cellStyleError.setVerticalAlignment(CellStyle.VERTICAL_TOP);

    // create header cell style
    font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 10);
    cellStyleHeader = (XSSFCellStyle) workbook.createCellStyle();
    cellStyleHeader.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.index);
    cellStyleHeader.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cellStyleHeader.setFont(font);
}

From source file:org.netxilia.impexp.impl.PoiUtils.java

License:Open Source License

public static CellStyle netxiliaStyle2Poi(Styles nxStyle, Workbook workbook) {
    return netxiliaStyle2Poi(nxStyle, workbook, workbook.createCellStyle());
}

From source file:org.nuclos.server.report.export.ExcelExport.java

License:Open Source License

private NuclosFile export(Workbook wb, String sheetname, ResultVO result, List<ReportFieldDefinition> fields,
        String name) throws NuclosReportException {
    sheetname = sheetname != null ? sheetname
            : SpringLocaleDelegate.getInstance().getMessage("XLSExport.2", "Daten aus Nucleus");
    Sheet s = wb.getSheet(sheetname);/*from  ww  w.  ja  v  a2 s .  c  o  m*/
    if (s == null) {
        s = wb.createSheet(sheetname);
    }

    int iRowNum = 0;
    int iColumnNum = 0;
    CreationHelper createHelper = wb.getCreationHelper();

    Row row = getRow(s, 0);

    Map<Integer, CellStyle> styles = new HashMap<Integer, CellStyle>();

    for (Iterator<ResultColumnVO> i = result.getColumns().iterator(); i.hasNext(); iColumnNum++) {
        i.next();
        Cell cell = getCell(row, iColumnNum);
        cell.setCellValue(fields.get(iColumnNum).getLabel());

        CellStyle style = wb.createCellStyle();
        String f = getFormat(fields.get(iColumnNum));
        if (f != null) {
            style.setDataFormat(createHelper.createDataFormat().getFormat(f));
        }
        styles.put(iColumnNum, style);
    }
    iRowNum++;

    // export data
    for (int i = 0; i < result.getRows().size(); i++, iRowNum++) {
        iColumnNum = 0;
        Object[] dataRow = result.getRows().get(i);
        row = getRow(s, iRowNum);
        for (int j = 0; j < result.getColumns().size(); j++, iColumnNum++) {
            Object value = dataRow[j];
            Cell c = getCell(row, iColumnNum);
            ReportFieldDefinition def = fields.get(j);

            if (value != null) {
                if (value instanceof List) {
                    final StringBuilder sb = new StringBuilder();
                    for (Iterator<?> it = ((List<?>) value).iterator(); it.hasNext();) {
                        final Object v = it.next();
                        sb.append(CollectableFieldFormat.getInstance(def.getJavaClass())
                                .format(def.getOutputformat(), v));
                        if (it.hasNext()) {
                            sb.append(", ");
                        }
                    }
                    c.setCellValue(sb.toString());
                } else {
                    if (Date.class.isAssignableFrom(def.getJavaClass())) {
                        c.setCellStyle(styles.get(iColumnNum));
                        c.setCellValue((Date) value);
                    } else if (Integer.class.isAssignableFrom(def.getJavaClass())) {
                        c.setCellStyle(styles.get(iColumnNum));
                        c.setCellValue((Integer) value);
                    } else if (Double.class.isAssignableFrom(def.getJavaClass())) {
                        c.setCellStyle(styles.get(iColumnNum));
                        c.setCellValue((Double) value);
                    } else {
                        c.setCellValue(String.valueOf(value));
                    }
                }
            } else {
                c.setCellValue("");
            }
        }
    }

    try {
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = wb.getSheetAt(sheetNum);
            for (Row r : sheet) {
                for (Cell c : r) {
                    if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                        evaluator.evaluateFormulaCell(c);
                    }
                }
            }
        }
    } catch (Exception e) {
    } // ignore any Exception

    ByteArrayOutputStream baos = new ByteArrayOutputStream(1024);
    try {
        wb.write(baos);
        return new NuclosFile(name + format.getExtension(), baos.toByteArray());
    } catch (IOException e) {
        throw new NuclosReportException(e);
    } finally {
        try {
            baos.close();
        } catch (IOException e) {
        }
    }
}

From source file:org.olat.core.gui.components.form.flexible.impl.elements.table.XlsFlexiTableExporter.java

License:Apache License

public static CellStyle getHeaderCellStyle(final Workbook wb) {
    CellStyle cellStyle = wb.createCellStyle();
    Font boldFont = wb.createFont();
    boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    cellStyle.setFont(boldFont);//w  w  w. j  a  v a  2  s .  com
    return cellStyle;
}