Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createFont

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createFont

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. createFont

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createFont.

Prototype


@Override
public HSSFFont createFont()

Source Link

Document

create a new Font and add it to the workbook's font table

Usage

From source file:net.mindengine.oculus.frontend.service.report.ExcelTestSearchReportGenerator.java

License:Open Source License

/**
 * Generates Excel spreadsheet.//www .  ja v a 2 s  .c  o m
 *
 * @param searchResult Search result for test or document (test-case)
 * @throws Exception 
 */
public void writeExcelReports(BrowseResult<?> searchResult, Long projectId, HttpServletRequest request,
        HttpServletResponse response) throws Exception {

    int cellOffset = 5;
    /*
     * Customizations which will be exported to XLS spreadsheet.
     */
    Collection<Customization> customizationsExport = new LinkedList<Customization>();

    /*
     * Here will be cell ids stored for each customization. This is needed because there might be a lot of merged cells for list customizations
     */
    Map<Long, Integer> customizationCells = new HashMap<Long, Integer>();

    if (projectId != null && projectId > 0) {
        Collection<Customization> customizations = customizationDAO.getCustomizations(projectId, unit);
        for (Customization customization : customizations) {

            //Checking if the user has selected this specific customization for exporting
            if ("on".equals(request.getParameter("cexport" + customization.getId()))) {

                customizationCells.put(customization.getId(), cellOffset);

                //Checking if the customization contains possible values and fetching them all
                if (customization.getType().equals(Customization.TYPE_CHECKLIST)
                        || customization.getType().equals(Customization.TYPE_LIST)) {
                    customization.setPossibleValues(
                            customizationDAO.getCustomizationPossibleValues(customization.getId()));
                    cellOffset += customization.getPossibleValues().size();
                } else {
                    cellOffset += 1;
                }

                customizationsExport.add(customization);
            }
        }
    }

    /*
     * Generating the Excel spreadsheet
     */

    OutputStream outputStream = response.getOutputStream();
    response.setContentType("application/ms-excel");

    HSSFWorkbook workBook = new HSSFWorkbook();

    HSSFSheet sheet = workBook.createSheet();

    HSSFFont fontHeader = workBook.createFont();
    fontHeader.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    fontHeader.setColor(HSSFColor.WHITE.index);

    HSSFCellStyle columnHeaderStyle = workBook.createCellStyle();
    columnHeaderStyle.setBorderTop((short) 2);
    columnHeaderStyle.setBorderLeft((short) 2);
    columnHeaderStyle.setBorderRight((short) 2);
    columnHeaderStyle.setBorderBottom((short) 2);
    columnHeaderStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
    columnHeaderStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    columnHeaderStyle.setFont(fontHeader);

    HSSFCellStyle columnRootHeaderStyle = workBook.createCellStyle();

    //columnRootHeaderStyle.cloneStyleFrom(columnHeaderStyle);
    columnRootHeaderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFCell cell;

    HSSFRow headerRow = sheet.createRow(0);
    HSSFRow header2Row = sheet.createRow(1);

    sheet.addMergedRegion(new Region((short) 0, (short) 0, (short) 0, (short) 4));
    cell = headerRow.createCell((short) 0);
    cell.setCellValue("Common");
    cell.setCellStyle(columnRootHeaderStyle);

    cell = header2Row.createCell((short) 0);
    cell.setCellValue("Test");
    cell.setCellStyle(columnHeaderStyle);

    cell = header2Row.createCell((short) 1);
    cell.setCellValue("Project");
    cell.setCellStyle(columnHeaderStyle);

    cell = header2Row.createCell((short) 2);
    cell.setCellValue("Sub-Project");
    cell.setCellStyle(columnHeaderStyle);

    cell = header2Row.createCell((short) 3);
    cell.setCellValue("Author");
    cell.setCellStyle(columnHeaderStyle);

    cell = header2Row.createCell((short) 4);
    cell.setCellValue("Created");
    cell.setCellStyle(columnHeaderStyle);

    for (Customization customization : customizationsExport) {
        int cellId = customizationCells.get(customization.getId());
        if (customization.getPossibleValues() != null) {
            int size = customization.getPossibleValues().size();
            if (size > 1) {
                sheet.addMergedRegion(
                        new Region((short) 0, (short) 0, (short) cellId, (short) (cellId + size - 1)));
            }

            /*
             * Exporting possible values 
             */
            int offset = 0;
            for (CustomizationPossibleValue cpv : customization.getPossibleValues()) {
                cell = header2Row.createCell((short) (cellId + offset));
                cell.setCellValue(cpv.getPossibleValue());
                cell.setCellStyle(columnHeaderStyle);
                offset++;
            }
        } else {
            cell = header2Row.createCell((short) cellId);
            cell.setCellStyle(columnHeaderStyle);
        }
        cell = headerRow.createCell((short) cellId);
        cell.setCellValue(customization.getName());
        cell.setCellStyle(columnRootHeaderStyle);
    }

    HSSFCellStyle cellStyle = workBook.createCellStyle();

    HSSFCellStyle checkboxStyle = workBook.createCellStyle();
    checkboxStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
    checkboxStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    checkboxStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFFont fontCheckbox = workBook.createFont();
    fontCheckbox.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    fontCheckbox.setColor(HSSFColor.BLACK.index);
    checkboxStyle.setFont(fontCheckbox);

    HSSFCellStyle boolYesStyle = workBook.createCellStyle();
    boolYesStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
    boolYesStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    HSSFCellStyle boolNoStyle = workBook.createCellStyle();
    boolNoStyle.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
    boolNoStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    /*
     * Used in order to cache user names. Key = ID, Value = User name
     */
    Map<Long, String> usersCache = new HashMap<Long, String>();

    int j = 2;
    for (Object object : searchResult.getResults()) {
        HSSFRow row = sheet.createRow(j);

        String name, parentProjectName, projectName, authorName;
        Date date;
        Long objectId;

        if (object instanceof Test) {
            Test test = (Test) object;
            name = test.getName();
            parentProjectName = test.getParentProjectName();
            projectName = test.getProjectName();
            authorName = test.getAuthorName();
            objectId = test.getId();
            date = test.getDate();
        } else
            throw new IllegalArgumentException(object.getClass().getName());

        cell = row.createCell((short) 0);
        cell.setCellValue(name);
        cell.setCellStyle(cellStyle);

        cell = row.createCell((short) 1);
        cell.setCellValue(parentProjectName);
        cell.setCellStyle(cellStyle);

        cell = row.createCell((short) 2);
        cell.setCellValue(projectName);
        cell.setCellStyle(cellStyle);

        cell = row.createCell((short) 3);
        cell.setCellValue(authorName);
        cell.setCellStyle(cellStyle);

        cell = row.createCell((short) 4);
        SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");
        cell.setCellValue(sdf.format(date));
        cell.setCellStyle(cellStyle);

        int offset = 5;

        for (Customization customization : customizationsExport) {
            UnitCustomizationValue ucv = customizationDAO.getUnitCustomizationValue(customization.getId(),
                    objectId);

            if (customization.getType().equals(Customization.TYPE_CHECKLIST)
                    || customization.getType().equals(Customization.TYPE_LIST)) {
                for (CustomizationPossibleValue cpv : customization.getPossibleValues()) {

                    boolean matches = false;

                    if (customization.getType().equals(Customization.TYPE_LIST)) {
                        if (ucv != null && ucv.getValue() != null && ucv.getValue().equals("" + cpv.getId())) {
                            matches = true;
                        }
                    } else {
                        String s = "(" + cpv.getId() + ")";
                        if (ucv != null && ucv.getValue() != null && ucv.getValue().contains(s)) {
                            matches = true;
                        }
                    }

                    if (matches) {
                        cell = row.createCell((short) offset);
                        cell.setCellValue("X");
                        cell.setCellStyle(checkboxStyle);
                    }
                    offset++;
                }
            } else {
                if (ucv != null) {
                    cell = row.createCell((short) offset);
                    cell.setCellStyle(cellStyle);

                    if (customization.getType().equals(Customization.TYPE_ASSIGNEE)) {
                        if (ucv.getValue() != null && !ucv.getValue().isEmpty()) {
                            try {
                                Long userId = Long.parseLong(ucv.getValue());
                                /*
                                 * Chaching user names by their ids
                                 */
                                String userName = null;
                                if (!usersCache.containsKey(userId)) {
                                    User user = userDAO.getUserById(userId);
                                    if (user != null) {
                                        userName = user.getName();
                                    } else
                                        userName = "";

                                    usersCache.put(userId, userName);
                                } else
                                    userName = usersCache.get(userId);

                                cell.setCellValue(userName);
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        }
                    } else if (customization.getType().equals(Customization.TYPE_CHECKBOX)) {
                        if (ucv.getValue() != null) {
                            if (ucv.getValue().equals("true")) {
                                cell.setCellValue("Yes");
                                cell.setCellStyle(boolYesStyle);
                            } else {
                                cell.setCellValue("No");
                                cell.setCellStyle(boolNoStyle);
                            }
                        }
                    } else {
                        cell.setCellValue(ucv.getValue());
                    }

                }
                offset++;
            }
        }
        j++;
    }

    /*
     * Making the text to fit in all cells 
     */
    for (short i = 0; i < (short) cellOffset + 1; i++) {
        sheet.autoSizeColumn(i);
    }

    workBook.write(outputStream);
    outputStream.flush();
    outputStream.close();
}

From source file:net.sourceforge.fenixedu.presentationTier.Action.academicAdministration.executionCourseManagement.CourseLoadOverviewBean.java

License:Open Source License

public StyledExcelSpreadsheet getInconsistencySpreadsheet() {
    final StyledExcelSpreadsheet spreadsheet = new StyledExcelSpreadsheet(
            BundleUtil.getString(Bundle.ACADEMIC, "label.course.load.inconsistency.filename") + "_"
                    + executionSemester.getExecutionYear().getYear().replace('/', '_') + "_"
                    + executionSemester.getSemester());
    HSSFCellStyle normalStyle = spreadsheet.getExcelStyle().getValueStyle();
    normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFWorkbook wb = spreadsheet.getWorkbook();
    HSSFFont font = wb.createFont();
    font.setColor(HSSFColor.BLACK.index);
    font.setFontHeightInPoints((short) 8);
    HSSFCellStyle redStyle = wb.createCellStyle();
    redStyle.setFont(font);/*www. ja  v a  2s.  c om*/
    redStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    redStyle.setFillForegroundColor(HSSFColor.ORANGE.index);
    redStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    HSSFCellStyle yellowStyle = wb.createCellStyle();
    yellowStyle.setFont(font);
    yellowStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
    yellowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    spreadsheet.newHeaderRow();
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.department"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.degree"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.executionCourse"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.shift"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.shiftType"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.competenceCourse"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.curricularCourse"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.executionCourse"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lessonInstances"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lesson.count"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lessonInstances.count"));

    for (final ExecutionCourse executionCourse : executionSemester.getAssociatedExecutionCoursesSet()) {
        for (final CourseLoad courseLoad : executionCourse.getCourseLoadsSet()) {
            for (final Shift shift : courseLoad.getShiftsSet()) {
                spreadsheet.newRow();
                spreadsheet.addCell(getDepartmentString(executionCourse));
                spreadsheet.addCell(executionCourse.getDegreePresentationString());
                spreadsheet.addCell(executionCourse.getName());
                spreadsheet.addCell(shift.getNome());
                spreadsheet.addCell(courseLoad.getType().getFullNameTipoAula());
                final BigDecimal competenceCourseLoad = new BigDecimal(getCompetenceCourseLoad(courseLoad))
                        .setScale(2, RoundingMode.HALF_EVEN);
                final BigDecimal curricularCourseLoad = new BigDecimal(getCurricularCourseLoad(courseLoad))
                        .setScale(2, RoundingMode.HALF_EVEN);
                final BigDecimal executionLoad = courseLoad.getTotalQuantity().setScale(2,
                        RoundingMode.HALF_EVEN);
                final BigDecimal shiftCourseLoad = getShiftCourseLoad(shift).setScale(2,
                        RoundingMode.HALF_EVEN);
                if (competenceCourseLoad.signum() < 0) {
                    spreadsheet.addCell(getCompetenceCourseLoadStrings(courseLoad), redStyle);
                } else {
                    spreadsheet.addCell(competenceCourseLoad);
                }
                if (!competenceCourseLoad.equals(curricularCourseLoad) || curricularCourseLoad.signum() < 0) {
                    spreadsheet.addCell(getCurricularCourseLoadString(courseLoad), redStyle);
                } else {
                    spreadsheet.addCell(curricularCourseLoad);
                }
                if (!executionLoad.equals(curricularCourseLoad)) {
                    spreadsheet.addCell(executionLoad, redStyle);
                } else {
                    spreadsheet.addCell(executionLoad);
                }
                if (!shiftCourseLoad.equals(executionLoad)) {
                    if (isLargeDifference(shiftCourseLoad, executionLoad,
                            competenceCourseLoad.divide(new BigDecimal(14), 2, RoundingMode.HALF_EVEN))) {
                        spreadsheet.addCell(shiftCourseLoad, redStyle);
                    } else {
                        spreadsheet.addCell(shiftCourseLoad, yellowStyle);
                    }
                } else {
                    spreadsheet.addCell(shiftCourseLoad);
                }
                spreadsheet.addCell(shift.getAssociatedLessonsSet().size());
                spreadsheet.addCell(getLessonInstanceCount(shift));
            }
        }
    }

    final HSSFSheet sheet = wb.getSheetAt(0);
    sheet.createFreezePane(0, 1, 0, 1);
    sheet.autoSizeColumn(1, true);
    sheet.autoSizeColumn(2, true);
    sheet.autoSizeColumn(3, true);
    sheet.autoSizeColumn(4, true);
    sheet.autoSizeColumn(5, true);
    sheet.autoSizeColumn(6, true);
    sheet.autoSizeColumn(7, true);
    sheet.autoSizeColumn(8, true);
    sheet.autoSizeColumn(9, true);

    return spreadsheet;
}

From source file:net.triptech.buildulator.view.ExcelTemplateView.java

License:Open Source License

@Override
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {

    DataGrid dataGrid = (DataGrid) model.get("dataGrid");

    String sheetName = "Sheet 1";
    if (StringUtils.isNotBlank(dataGrid.getTitle())) {
        sheetName = dataGrid.getTitle();
    }/*  w  w w .java2s  .c  o m*/

    HSSFSheet sheet = workbook.createSheet(sheetName);

    Font font = workbook.createFont();
    font.setColor(HSSFColor.WHITE.index);

    HSSFCellStyle style = workbook.createCellStyle();
    style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setFont(font);

    int rowNum = 0;
    int maxColumnCount = 0;

    if (dataGrid.getHeaderFields().size() > 0) {
        HSSFRow header = sheet.createRow(rowNum);
        rowNum++;

        maxColumnCount = dataGrid.getHeaderFields().size();

        int i = 0;
        for (String field : dataGrid.getHeaderFields()) {

            HSSFCell cell = header.createCell(i);
            cell.setCellValue(field);
            cell.setCellStyle(style);
            i++;
        }
    }

    for (int y = 0; y < dataGrid.getRowCount(); y++) {
        HSSFRow row = sheet.createRow(rowNum++);

        List<String> rowData = dataGrid.getRowFields(y);

        if (rowData.size() > maxColumnCount) {
            maxColumnCount = rowData.size();
        }

        int x = 0;

        for (String data : rowData) {
            HSSFCell cell = row.createCell(x);

            try {
                double dbValue = Double.parseDouble(data);
                cell.setCellValue(dbValue);
            } catch (NumberFormatException nfe) {
                cell.setCellValue(data);
            }
            x++;
        }
    }

    for (int i = 0; i < maxColumnCount; i++) {
        sheet.autoSizeColumn(i);
    }
}

From source file:opisiame.controller.gestion_resultat.Choix_exportController.java

@FXML
public void excel_export() {

    File excel_file = choix_chemin_enregistrement("Excel files (*.xls)", "*.xls");

    if (onglet_actif.equals("questions")) {
        if (excel_file != null) {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("Resultat par question");
            sheet.autoSizeColumn(5);/*w  w w. j  av  a  2 s .  com*/
            create_data1(sheet, 0, "Question", "Pourcentage reponse A", "Pourcentage reponse B",
                    "Pourcentage reponse C", "Pourcentage reponse D", "Pourcentage bonne rponse");

            Row row = sheet.getRow(0);
            HSSFCellStyle cellStyle = null;
            HSSFFont font = wb.createFont();
            font.setBold(true);
            cellStyle = wb.createCellStyle();
            cellStyle.setFont(font);
            row.setRowStyle(cellStyle);

            for (int i = 0; i < reponse_questions.size(); i++) {
                Reponse_question rq = reponse_questions.get(i);
                create_data1(sheet, i + 1, rq.getQuestion(), rq.getStr_pourcentage_rep_a(),
                        rq.getStr_pourcentage_rep_b(), rq.getStr_pourcentage_rep_c(),
                        rq.getStr_pourcentage_rep_d(), rq.getStr_pourcentage());
            }

            FileOutputStream fileOut;
            try {
                fileOut = new FileOutputStream(excel_file);
                wb.write(fileOut);
                fileOut.close();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    } else if (onglet_actif.equals("eleves")) {
        if (excel_file != null) {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("Resultat des tudiants");
            sheet.autoSizeColumn(5);
            create_data2(sheet, 0, "Nom", "Prnom", "N tudiant", "Note", "Pourcentage");

            Row row = sheet.getRow(0);
            HSSFCellStyle cellStyle = null;
            HSSFFont font = wb.createFont();
            font.setBold(true);
            cellStyle = wb.createCellStyle();
            cellStyle.setFont(font);
            row.setRowStyle(cellStyle);

            for (int i = 0; i < resultats_eleves.size(); i++) {
                Rep_eleves_quiz re = resultats_eleves.get(i);
                create_data2(sheet, i + 1, re.getNom_eleve(), re.getPrenom_eleve(),
                        re.getNum_eleve().toString(), re.getNote_eleve().toString(),
                        re.getPourcent_eleve().toString());
            }

            FileOutputStream fileOut;
            try {
                fileOut = new FileOutputStream(excel_file);
                wb.write(fileOut);
                fileOut.close();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    } else if (onglet_actif.equals("eleves_pas_num")) {
        if (excel_file != null) {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("Resultat des tudiants");
            sheet.autoSizeColumn(4);
            create_data3(sheet, 0, "Nom", "Prnom", "Note", "Pourcentage");

            Row row = sheet.getRow(0);
            HSSFCellStyle cellStyle = null;
            HSSFFont font = wb.createFont();
            font.setBold(true);
            cellStyle = wb.createCellStyle();
            cellStyle.setFont(font);
            row.setRowStyle(cellStyle);

            for (int i = 0; i < resultats_eleves.size(); i++) {
                Rep_eleves_quiz re = resultats_eleves.get(i);
                create_data3(sheet, i + 1, re.getNom_eleve(), re.getPrenom_eleve(),
                        re.getNote_eleve().toString(), re.getPourcent_eleve().toString());
            }

            FileOutputStream fileOut;
            try {
                fileOut = new FileOutputStream(excel_file);
                wb.write(fileOut);
                fileOut.close();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    close_window();
}

From source file:opn.greenwebs.Hyperlinks.java

public static void main(String[] args) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();

    //cell style for hyperlinks
    //by default hyperlinks are blue and underlined
    HSSFCellStyle hlink_style = wb.createCellStyle();
    HSSFFont hlink_font = wb.createFont();
    hlink_font.setUnderline(HSSFFont.U_SINGLE);
    hlink_font.setColor(HSSFColor.BLUE.index);
    hlink_style.setFont(hlink_font);//from w w  w .j a  v a2 s  . c  o  m

    HSSFCell cell;
    HSSFSheet sheet = wb.createSheet("Hyperlinks");

    //URL
    cell = sheet.createRow(0).createCell(0);
    cell.setCellValue("URL Link");
    HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
    link.setAddress("http://poi.apache.org/");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    //link to a file in the current directory
    cell = sheet.createRow(1).createCell(0);
    cell.setCellValue("File Link");
    link = new HSSFHyperlink(HSSFHyperlink.LINK_FILE);
    link.setAddress("link1.xls");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    //e-mail link
    cell = sheet.createRow(2).createCell(0);
    cell.setCellValue("Email Link");
    link = new HSSFHyperlink(HSSFHyperlink.LINK_EMAIL);
    //note, if subject contains white spaces, make sure they are url-encoded
    link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    //link to a place in this workbook

    //create a target sheet and cell
    HSSFSheet sheet2 = wb.createSheet("Target Sheet");
    sheet2.createRow(0).createCell(0).setCellValue("Target Cell");

    cell = sheet.createRow(3).createCell(0);
    cell.setCellValue("Worksheet Link");
    link = new HSSFHyperlink(HSSFHyperlink.LINK_DOCUMENT);
    link.setAddress("'Target Sheet'!A1");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    FileOutputStream out = new FileOutputStream("hssf-links.xls");
    wb.write(out);
    out.close();
}

From source file:org.anyframe.logmanager.web.LogManagerController.java

License:Apache License

/**
 * log data export for excel file type//  www  . j  a va 2s.  c  om
 * 
 * @param searchCondition
 * @param model
 * @param request
 * @return
 * @throws Exception
 */
@RequestMapping(params = "method=xlsExport")
public void xlsExport(LogSearchCondition searchCondition, Model model, HttpServletRequest request,
        HttpServletResponse response) throws Exception {

    searchCondition.setPageIndex(-1);
    searchCondition.setCollection(searchCondition.getRepositoryName());

    String fileName = null;
    String sDate = null;
    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss", new Locale("ko_KR"));
    sDate = sdf.format(new Date());
    StringBuffer sb = new StringBuffer();
    sb.append(searchCondition.getAppName().substring(searchCondition.getAppName().lastIndexOf("/") + 1));
    sb.append("_").append(searchCondition.getCollection()).append("_").append(sDate).append(".xls");
    fileName = sb.toString();

    SimpleDateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-ddHHmm");
    logger.debug("from:{}",
            searchCondition.getFromDate() + searchCondition.getFromHour() + searchCondition.getFromMinute());
    logger.debug("to:{}",
            searchCondition.getToDate() + searchCondition.getToHour() + searchCondition.getToMinute());
    if (searchCondition.isUseFromDate())
        searchCondition.setFromDateTime(dateTimeFormat.parse(searchCondition.getFromDate()
                + searchCondition.getFromHour() + searchCondition.getFromMinute()));
    if (searchCondition.isUseToDate())
        searchCondition.setToDateTime(dateTimeFormat.parse(
                searchCondition.getToDate() + searchCondition.getToHour() + searchCondition.getToMinute()));

    List<LogDataMap> resultList = service.searchAnalysisLog(searchCondition);

    response.reset();
    response.setContentType("application/x-msexcel;charset=MS949");
    // response.setContentType("application/octet-stream");
    String userAgent = request.getHeader("User-Agent");

    if (userAgent.indexOf("MSIE 5.5") > -1) {
        response.setHeader("Content-Disposition", "filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\";");
    } else if (userAgent.indexOf("MSIE") > -1) {
        response.setHeader("Content-Disposition",
                "attachment; filename=\"" + java.net.URLEncoder.encode(fileName, "UTF-8") + "\";");
    } else {
        response.setHeader("Content-Disposition",
                "attachment; filename=\"" + new String(fileName.getBytes("euc-kr"), "latin1") + "\";");
    }
    response.setHeader("Content-Description", "JSP Generated Data");
    response.setHeader("Content-Transfer-Encoding", "binary;");
    response.setHeader("Pragma", "no-cache;");
    response.setHeader("Expires", "-1;");

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet(fileName);

    OutputStream fileOut = null;
    try {
        fileOut = response.getOutputStream();
        HSSFRow row = null;
        HSSFRow headerRow = null;

        HSSFDataFormat df = workbook.createDataFormat();

        HSSFCellStyle headerStyle = workbook.createCellStyle();
        HSSFFont boldFont = workbook.createFont();
        boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(boldFont);
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFCellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat(df.getFormat("yyyy-mm-dd h:mm:ss.000"));
        dateStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        dateStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFCellStyle messageStyle = workbook.createCellStyle();
        messageStyle.setWrapText(true);
        messageStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        messageStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFCell cell;
        HSSFCell headerCell;

        short width = 265;

        Iterator<String> j = null;
        String key = null;
        int cellIndex = 0;
        int listSize = 0;

        String level = null;
        Date timestamp = null;
        String message = null;

        if (resultList != null) {
            listSize = resultList.size();
            for (int i = 0; i < listSize; i++) {
                LogDataMap log = (LogDataMap) resultList.get(i);
                if (i == 0) {
                    headerRow = sheet.createRow(i); // level header
                    sheet.setColumnWidth(0, 7 * width);
                    headerCell = headerRow.createCell(0);
                    HSSFRichTextString headerValue = new HSSFRichTextString("level");
                    headerCell.setCellValue(headerValue);
                    headerCell.setCellStyle(headerStyle);

                    headerCell = headerRow.createCell(1); // time stamp header
                    sheet.setColumnWidth(1, 24 * width);
                    headerValue = new HSSFRichTextString("timestamp");
                    headerCell.setCellValue(headerValue);
                    headerCell.setCellStyle(headerStyle);

                    headerCell = headerRow.createCell(2); // message header
                    sheet.setColumnWidth(2, 70 * width);
                    headerValue = new HSSFRichTextString("message");
                    headerCell.setCellValue(headerValue);
                    headerCell.setCellStyle(headerStyle);
                }

                row = sheet.createRow(i + 1);

                // level
                level = (String) log.get("level");
                cell = row.createCell(0);
                cell.setCellStyle(style);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(level);

                // timestamp
                timestamp = (Date) log.get("timestamp");
                cell = row.createCell(1);
                cell.setCellStyle(dateStyle);
                cell.setCellValue(timestamp);

                // message
                message = (String) log.get("message");
                HSSFRichTextString messageValue = new HSSFRichTextString(message);
                cell = row.createCell(2);
                cell.setCellStyle(messageStyle);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(messageValue);

                cellIndex = 3;
                j = log.keySet().iterator();
                while (j.hasNext()) {
                    key = j.next();
                    if ("_id".equals(key) || "message".equals(key) || "timestamp".equals(key)
                            || "level".equals(key)) {
                        continue;
                    }
                    //logger.debug("key=" + key);
                    if (i == 0) {
                        sheet.setColumnWidth(cellIndex, 20 * width);

                        headerCell = headerRow.createCell(cellIndex);
                        HSSFRichTextString headerValue = new HSSFRichTextString(key);
                        headerCell.setCellValue(headerValue);
                        headerCell.setCellStyle(headerStyle);
                    }
                    cell = row.createCell(cellIndex);
                    Object value = log.get(key);
                    if (value instanceof Date) {
                        cell.setCellStyle(dateStyle);
                        cell.setCellValue((Date) value);
                    } else {
                        cell.setCellStyle(style);
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellValue((String) log.get(key));
                    }

                    cellIndex++;
                }
            }
            workbook.write(fileOut);
        }
    } catch (Exception e) {
        throw e;
    } finally {
        try {
            if (fileOut != null) {
                fileOut.flush();
                fileOut.close();
            }
        } catch (IOException ex) {
            logger.warn(ex.getMessage(), ex);
        }
    }
}

From source file:org.clickframes.renderer.RequirementsGenerator.java

License:Open Source License

public static HSSFCellStyle createHeaderColumnStyle(HSSFWorkbook wb) {
    HSSFCellStyle cellStyle = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cellStyle.setFont(font);/*from  w w  w .  j  ava 2  s  .com*/

    return cellStyle;
}

From source file:org.cyberoam.iview.servlets.ExcelFileGenerator.java

License:Open Source License

void getWorkBook(ResultSetWrapper rsw, ReportBean reportBean, HSSFWorkbook wb) {
    try {/*from  w w w . j  a va 2s  .c om*/
        ReportColumnBean[] reportColumns = (ReportColumnBean[]) ReportColumnBean
                .getReportColumnsByReportID(reportBean.getReportId()).toArray(new ReportColumnBean[0]);
        HSSFSheet newSheet = wb.createSheet(reportBean.getTitle());
        HSSFRow row;
        HSSFCell cell;

        HSSFCellStyle cellStyle = wb.createCellStyle();
        HSSFFont fontStyle = wb.createFont();

        fontStyle.setFontHeightInPoints((short) 10);
        fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        cellStyle.setFont(fontStyle);

        // getting number of records & fields for report
        rsw.last();
        int rowCount = rsw.getRow();
        int colCount = reportColumns.length;

        //For Freezing the first row
        newSheet.createFreezePane(0, 1, 0, 1);

        // Adding column headings to Excel
        row = newSheet.createRow((short) 0);
        for (int cCount = 0; cCount < colCount; cCount++) {
            cell = row.createCell(cCount);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(reportColumns[cCount].getColumnName());
        }
        //Adding data for each record to Excel
        rsw.first();
        for (int rcount = 1; rcount <= rowCount; rcount++) {
            row = newSheet.createRow(rcount);
            for (int cCount = 0; cCount < colCount; cCount++) {
                String data = rsw.getString(reportColumns[cCount].getDbColumnName());
                cell = row.createCell(cCount);
                newSheet.autoSizeColumn((short) cCount);
                if (reportColumns[cCount].getColumnFormat() == TabularReportConstants.BYTE_FORMATTING) {
                    data = ByteInUnit.getBytesInUnit(rsw.getLong(reportColumns[cCount].getDbColumnName()));
                } else if (reportColumns[cCount].getColumnFormat() == TabularReportConstants.PROTOCOL_FORMATTING
                        && data.indexOf(':') != -1) {
                    String xdata = ProtocolBean.getProtocolNameById(
                            Integer.parseInt(rsw.getString(reportColumns[cCount].getDbColumnName()).substring(0,
                                    data.indexOf(':'))));
                    data = xdata + rsw.getString(reportColumns[cCount].getDbColumnName())
                            .substring(data.indexOf(':'), data.length());
                }
                // Setting value to the cell
                if (cCount == 0 && (data == null || "".equalsIgnoreCase(data)))
                    data = "N/A";
                if (reportColumns[cCount].getColumnFormat() == TabularReportConstants.PERCENTAGE_FORMATTING) {
                    try {
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(Double.parseDouble(data));
                    } catch (NumberFormatException e) {
                        cell.setCellValue(data);
                    }
                } else if (rsw.getMetaData().getColumnTypeName(cCount + 1).equalsIgnoreCase("numeric")
                        && reportColumns[cCount].getColumnFormat() != TabularReportConstants.BYTE_FORMATTING) {
                    try {
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(Integer.parseInt(data));
                    } catch (NumberFormatException e) {
                        cell.setCellValue(data);
                    }
                } else {
                    cell.setCellValue(data);
                }
            }
            rsw.next();
        }

    } catch (Exception e) {
        CyberoamLogger.appLog.error("***Error in getWorkbook function***" + e, e);
    }
}

From source file:org.deployom.core.AuditService.java

License:Open Source License

public HSSFWorkbook saveAudit() {

    // Create book
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFCreationHelper creationHelper = workbook.getCreationHelper();

    // Default Style
    HSSFCellStyle style = workbook.createCellStyle();
    style.setWrapText(true);/*  w  ww  . j  a  va 2 s.c  om*/
    HSSFFont font = workbook.createFont();
    font.setFontName("Courier New");
    style.setFont(font);
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

    // Header Style
    HSSFCellStyle styleHeader = workbook.createCellStyle();
    styleHeader.cloneStyleFrom(style);
    styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    font = workbook.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.WHITE.index);
    styleHeader.setFillForegroundColor(IndexedColors.BLACK.getIndex());
    styleHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    styleHeader.setFont(font);

    // Error Style
    HSSFCellStyle styleError = workbook.createCellStyle();
    styleError.cloneStyleFrom(style);
    styleError.setFillForegroundColor(IndexedColors.CORAL.getIndex());
    styleError.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    styleError.setWrapText(true);

    // Link Style
    HSSFCellStyle styleLink = workbook.createCellStyle();
    styleLink.cloneStyleFrom(style);
    font = workbook.createFont();
    font.setUnderline(HSSFFont.U_SINGLE);
    font.setColor(IndexedColors.BLUE.getIndex());
    styleLink.setFont(font);

    // Create Summary
    HSSFSheet summarySheet = workbook.createSheet("Summary");
    int summaryRownum = 0;
    int summaryCellnum = 0;

    //Create a new row in current sheet
    Row summaryRow = summarySheet.createRow(summaryRownum++);

    // 0
    Cell summaryCell = summaryRow.createCell(summaryCellnum++);
    summaryCell.setCellValue("Job");
    summaryCell.setCellStyle(styleHeader);

    // 1
    summaryCell = summaryRow.createCell(summaryCellnum++);
    summaryCell.setCellValue("Finished");
    summaryCell.setCellStyle(styleHeader);

    // 2
    summaryCell = summaryRow.createCell(summaryCellnum++);
    summaryCell.setCellValue("Errors");
    summaryCell.setCellStyle(styleHeader);

    for (Job job : releaseService.getJobs()) {

        // Open Job
        JobService jobService = new JobService(siteService.getSiteName(), job.getJobName());

        // Create Sheet
        HSSFSheet sheet = workbook.createSheet(job.getJobName());

        int rownum = 0;
        int cellnum = 0;
        int errors = 0;

        //Create a new row in current sheet
        Row row = sheet.createRow(rownum++);

        // 0
        Cell cell = row.createCell(cellnum++);
        cell.setCellValue("Host");
        cell.setCellStyle(styleHeader);

        // 1
        cell = row.createCell(cellnum++);
        cell.setCellValue("Service");
        cell.setCellStyle(styleHeader);

        // 2
        cell = row.createCell(cellnum++);
        cell.setCellValue("Command");
        cell.setCellStyle(styleHeader);

        // 3
        cell = row.createCell(cellnum++);
        cell.setCellValue("Executable");
        cell.setCellStyle(styleHeader);

        // 4
        cell = row.createCell(cellnum++);
        cell.setCellValue("Error");
        cell.setCellStyle(styleHeader);

        // 5
        cell = row.createCell(cellnum++);
        cell.setCellValue("Output");
        cell.setCellStyle(styleHeader);

        // Check all hosts
        for (Host host : jobService.getHosts()) {

            // Check all services
            for (Service service : host.getServices()) {

                // Get a Commands
                for (Command command : service.getCommands()) {

                    //Create a new row in current sheet
                    row = sheet.createRow(rownum++);
                    cellnum = 0;

                    // 0
                    cell = row.createCell(cellnum++);
                    cell.setCellValue(host.getHostName());
                    cell.setCellStyle(style);

                    // 1
                    cell = row.createCell(cellnum++);
                    cell.setCellValue(service.getServiceName());
                    cell.setCellStyle(style);

                    // 2
                    cell = row.createCell(cellnum++);
                    cell.setCellValue(command.getTitle());
                    cell.setCellStyle(style);

                    // 3
                    cell = row.createCell(cellnum++);
                    cell.setCellValue(command.getExec());
                    cell.setCellStyle(style);

                    // 4
                    cell = row.createCell(cellnum++);
                    cell.setCellValue("N");
                    cell.setCellStyle(style);

                    // 5
                    cell = row.createCell(cellnum++);
                    if (command.getOut().length() > 1024) {
                        cell.setCellValue(command.getOut().substring(0, 1024) + "...");
                    } else {
                        cell.setCellValue(command.getOut());
                    }
                    cell.setCellStyle(style);

                    // Error
                    if (command.isError() == true) {
                        row.getCell(0).setCellStyle(styleError);
                        row.getCell(1).setCellStyle(styleError);
                        row.getCell(2).setCellStyle(styleError);
                        row.getCell(3).setCellStyle(styleError);
                        row.getCell(4).setCellStyle(styleError);
                        row.getCell(5).setCellStyle(styleError);
                        row.getCell(4).setCellValue("Y");
                        errors++;
                    }
                }
            }
        }

        // Set Size
        sheet.setColumnWidth(0, 6000);
        sheet.setColumnWidth(1, 4000);
        sheet.setColumnWidth(2, 8000);
        sheet.setColumnWidth(3, 14000);
        sheet.setColumnWidth(4, 3000);
        sheet.setColumnWidth(5, 20000);

        // Summary
        summaryRow = summarySheet.createRow(summaryRownum++);
        summaryCellnum = 0;

        // 0
        summaryCell = summaryRow.createCell(summaryCellnum++);
        summaryCell.setCellValue(job.getJobName());
        summaryCell.setCellStyle(style);

        // Set Link
        HSSFHyperlink link = creationHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
        link.setAddress("" + job.getJobName() + "!A1");
        summaryCell.setHyperlink(link);
        summaryCell.setCellStyle(styleLink);

        // 1
        summaryCell = summaryRow.createCell(summaryCellnum++);
        summaryCell.setCellValue(jobService.getJob().getFinished());
        summaryCell.setCellStyle(style);

        // 2
        summaryCell = summaryRow.createCell(summaryCellnum++);
        summaryCell.setCellValue(errors);
        summaryCell.setCellStyle(style);

        // If errors found
        if (errors > 0) {
            summaryRow.getCell(0).setCellStyle(styleError);
            summaryRow.getCell(1).setCellStyle(styleError);
            summaryRow.getCell(2).setCellStyle(styleError);
        }
    }

    // Set Summary Size
    summarySheet.setColumnWidth(0, 6000);
    summarySheet.setColumnWidth(1, 10000);
    summarySheet.setColumnWidth(2, 4000);

    // Save
    try {
        FileOutputStream out = new FileOutputStream(new File(getFileName()));
        workbook.write(out);
        out.close();
        logger.log(Level.INFO, "{0} generated successfully..", getFileName());

        return workbook;
    } catch (FileNotFoundException ex) {
        logger.log(Level.WARNING, "Audit: {0}", ex);
    } catch (IOException ex) {
        logger.log(Level.WARNING, "Audit: {0}", ex);
    }

    return null;
}

From source file:org.displaytag.export.excel.ExcelHssfView.java

License:Open Source License

/**
 * Templated method that is called for all header cells.
 *
 * @param wb the wb/*from   www.  ja va2s.co m*/
 * @param headerCell the header cell
 * @return the HSSF cell style
 */
public HSSFCellStyle createHeaderStyle(HSSFWorkbook wb, HeaderCell headerCell) {
    HSSFCellStyle headerStyle = getNewCellStyle();

    headerStyle.setFillPattern(CellStyle.FINE_DOTS);
    headerStyle.setFillBackgroundColor(HSSFColor.BLUE_GREY.index);
    HSSFFont bold = wb.createFont();
    bold.setBoldweight(Font.BOLDWEIGHT_BOLD);
    bold.setColor(HSSFColor.WHITE.index);
    headerStyle.setFont(bold);

    return headerStyle;
}