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

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

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

Introduction

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

Prototype

@Override
public HSSFCellStyle createCellStyle()

Source Link

Document

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

Usage

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

License:Open Source License

/**
 * Generates Excel spreadsheet.//w  ww . j ava  2s .  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();//from   ww  w.ja v  a  2s  .c o  m
    font.setColor(HSSFColor.BLACK.index);
    font.setFontHeightInPoints((short) 8);
    HSSFCellStyle redStyle = wb.createCellStyle();
    redStyle.setFont(font);
    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.sourceforge.fenixedu.presentationTier.Action.administrativeOffice.scholarship.utl.report.ReportStudentsUTLCandidates.java

License:Open Source License

private CellStyle headerBackgroundStyle(final HSSFWorkbook wb) {
    CellStyle style = wb.createCellStyle();
    style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
    style.setFillPattern(CellStyle.BIG_SPOTS);

    return style;
}

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();
    }// ww w  .j a va  2 s . 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:net.vpc.app.vainruling.core.web.jsf.Vr.java

public void postProcessDataExporterXLS(Object document) {
    HSSFWorkbook book = (HSSFWorkbook) document;
    HSSFSheet sheet = book.getSheetAt(0);
    HSSFRow header = sheet.getRow(0);/*from   w  w w  .j  a v  a2 s.c om*/
    int rowCount = sheet.getPhysicalNumberOfRows();
    HSSFCellStyle headerCellStyle = book.createCellStyle();
    headerCellStyle.setFillForegroundColor(HSSFColor.AQUA.index);
    headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    HSSFCreationHelper creationHelper = book.getCreationHelper();

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);

        cell.setCellStyle(headerCellStyle);
    }

    HSSFCellStyle intStyle = book.createCellStyle();
    intStyle.setDataFormat((short) 1);

    HSSFCellStyle decStyle = book.createCellStyle();
    decStyle.setDataFormat((short) 2);

    HSSFCellStyle dollarStyle = book.createCellStyle();
    dollarStyle.setDataFormat((short) 5);

    int maxColumn = -1;
    Map<String, HSSFCellStyle> datFormats = new HashMap<>();
    for (int rowInd = 1; rowInd < rowCount; rowInd++) {
        HSSFRow row = sheet.getRow(rowInd);
        int colCount = row.getPhysicalNumberOfCells();
        if (maxColumn < colCount) {
            maxColumn = colCount;
        }
        for (int cellInd = 0; cellInd < colCount; cellInd++) {
            HSSFCell cell = row.getCell(cellInd);

            String strVal = cell.getStringCellValue();

            if (strVal.startsWith("$")) {
                //do nothing
            } else {
                if (strVal.startsWith("'")) {
                    strVal = strVal.substring(1);
                }
                if (PlatformUtils.isDouble(strVal)) {
                    cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    if (PlatformUtils.isInteger(strVal)) {
                        int intVal = Integer.valueOf(strVal.trim());
                        cell.setCellStyle(intStyle);
                        cell.setCellValue(intVal);
                    } else if (PlatformUtils.isDouble(strVal)) {
                        double dblVal = Double.valueOf(strVal.trim());
                        cell.setCellStyle(decStyle);
                        cell.setCellValue(dblVal);
                    }
                } else {
                    boolean isDate = false;
                    for (String dteFormat : new String[] { "yyyy-MM-dd HH:mm:ss.SSS", "yyyy-MM-dd HH:mm:ss",
                            "yyyy-MM-dd HH:mm", "yyyy-MM-dd", "HH:mm" }) {
                        if (PlatformUtils.isDate(strVal, dteFormat)) {
                            HSSFCellStyle dateStyle = datFormats.get(dteFormat.trim());
                            if (dateStyle == null) {
                                dateStyle = book.createCellStyle();
                                dateStyle.setDataFormat(creationHelper.createDataFormat().getFormat(dteFormat));
                                datFormats.put(dteFormat, dateStyle);
                            }
                            cell.setCellStyle(dateStyle);
                            try {
                                cell.setCellValue(new SimpleDateFormat(dteFormat).parse(strVal));
                            } catch (ParseException e) {
                                //
                            }
                            isDate = true;
                            break;
                        }
                    }

                }
            }
        }
    }
    if (maxColumn >= 0) {
        for (int cellInd = 0; cellInd < maxColumn; cellInd++) {
            sheet.autoSizeColumn(cellInd);
        }
    }

}

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);/*from ww w . j  a v  a 2 s. co m*/
            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 ww .j  a  va2  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  ava  2 s. co  m*/
 * 
 * @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.bbreak.excella.core.handler.DebugErrorHandler.java

License:Open Source License

/**
 * ?/*ww w.  j a v  a 2s.co  m*/
 * 
 * @param workbook 
 * @param errorCell
 * @param exception
 */
protected void markupErrorCell(Workbook workbook, ParseException exception) {
    Cell errorCell = exception.getCell();
    if (errorCell == null) {
        return;
    }
    // ????
    workbook.setActiveSheet(workbook.getSheetIndex(errorCell.getSheet()));
    errorCell.setAsActiveCell();

    if (workbook instanceof XSSFWorkbook) {
        XSSFWorkbook xssfWorkbook = (XSSFWorkbook) workbook;

        CellStyle errorCellStyle = xssfWorkbook.createCellStyle();
        errorCellStyle.setFillForegroundColor(HSSFColorPredefined.ROSE.getIndex());
        errorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        errorCell.setCellStyle(errorCellStyle);

        // TODO:???????????????
        // XSSFComment xssfComment = ((XSSFSheet)sheet).createComment();
        // xssfComment.setRow( errorCell.getRowIndex());
        // xssfComment.setColumn( (short)errorCell.getColumnIndex());
        // XSSFRichTextString string = new XSSFRichTextString( ex.getMessage());
        // xssfComment.setString( ex.getMessage());
    } else {
        HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook;
        int sheetNum = hssfWorkbook.getNumberOfSheets();
        for (int cnt = 0; cnt < sheetNum; cnt++) {
            hssfWorkbook.getSheetAt(cnt).setSelected(false);
        }

        // ?
        CellStyle errorCellStyle = hssfWorkbook.createCellStyle();
        errorCellStyle.setFillForegroundColor(HSSFColorPredefined.ROSE.getIndex());
        errorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        errorCell.setCellStyle(errorCellStyle);

        // ?
        short commentColFrom = (short) (errorCell.getColumnIndex() + 1);
        short commentColTo = (short) (errorCell.getColumnIndex() + ERROR_COMENT_COL_SIZE);
        int commentRowFrom = errorCell.getRowIndex();
        int commentRowTo = errorCell.getRowIndex() + ERROR_COMENT_ROW_SIZE;

        HSSFSheet hssfSheet = (HSSFSheet) errorCell.getSheet();
        HSSFPatriarch patr = hssfSheet.createDrawingPatriarch();
        hssfSheet.setSelected(true);
        HSSFComment comment = patr.createComment(
                new HSSFClientAnchor(0, 0, 0, 0, commentColFrom, commentRowFrom, commentColTo, commentRowTo));
        comment.setVisible(true);
        comment.setString(new HSSFRichTextString(createCommentMessage(exception)));
        errorCell.setCellComment(comment);
    }
}

From source file:org.beangle.commons.transfer.excel.PoiTest.java

License:Open Source License

public static void main(String[] args) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");

    // Create a row and put some cells in it. Rows are 0 based.
    HSSFRow row = sheet.createRow(0);/*from  w ww  .  j a va2  s. c  om*/

    // Create a cell and put a date value in it. The first cell is not
    // styled as a date.
    HSSFCell cell = row.createCell(0);
    cell.setCellValue(new Date());

    // we style the second cell as a date (and time). It is important to
    // create a new cell style from the workbook
    // otherwise you can end up modifying the built in style and effecting
    // not only this cell but other cells.
    HSSFCellStyle cellStyle = wb.createCellStyle();
    DataFormat df = wb.createDataFormat();
    // cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
    cellStyle.setDataFormat(df.getFormat("YYYY-MM-DD HH:MM:SS"));
    cell = row.createCell(1);
    cell.setCellValue(new Date());
    cell.setCellStyle(cellStyle);
    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("src/test/resources/workbook.xls");
    wb.write(fileOut);
    fileOut.close();
}