Example usage for org.apache.poi.ss.usermodel Sheet setDisplayGridlines

List of usage examples for org.apache.poi.ss.usermodel Sheet setDisplayGridlines

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. setDisplayGridlines

Introduction

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

Prototype

void setDisplayGridlines(boolean show);

Source Link

Document

Sets whether the gridlines are shown in a viewer

Usage

From source file:itpreneurs.itp.report.archive.LoanCalculator.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;/*from   w w  w  .ja va 2  s . c o m*/

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);
    Sheet sheet = wb.createSheet("Loan Calculator");
    sheet.setPrintGridlines(false);
    sheet.setDisplayGridlines(false);

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    sheet.setColumnWidth(0, 3 * 256);
    sheet.setColumnWidth(1, 3 * 256);
    sheet.setColumnWidth(2, 11 * 256);
    sheet.setColumnWidth(3, 14 * 256);
    sheet.setColumnWidth(4, 14 * 256);
    sheet.setColumnWidth(5, 14 * 256);
    sheet.setColumnWidth(6, 14 * 256);

    createNames(wb);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(35);
    for (int i = 1; i <= 7; i++) {
        titleRow.createCell(i).setCellStyle(styles.get("title"));
    }
    Cell titleCell = titleRow.getCell(2);
    titleCell.setCellValue("Simple Loan Calculator");
    sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1"));

    Row row = sheet.createRow(2);
    Cell cell = row.createCell(4);
    cell.setCellValue("Enter values");
    cell.setCellStyle(styles.get("item_right"));

    row = sheet.createRow(3);
    cell = row.createCell(2);
    cell.setCellValue("Loan amount");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_$"));
    cell.setAsActiveCell();

    row = sheet.createRow(4);
    cell = row.createCell(2);
    cell.setCellValue("Annual interest rate");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_%"));

    row = sheet.createRow(5);
    cell = row.createCell(2);
    cell.setCellValue("Loan period in years");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_i"));

    row = sheet.createRow(6);
    cell = row.createCell(2);
    cell.setCellValue("Start date of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_d"));

    row = sheet.createRow(8);
    cell = row.createCell(2);
    cell.setCellValue("Monthly payment");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(9);
    cell = row.createCell(2);
    cell.setCellValue("Number of payments");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")");
    cell.setCellStyle(styles.get("formula_i"));

    row = sheet.createRow(10);
    cell = row.createCell(2);
    cell.setCellValue("Total interest");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(11);
    cell = row.createCell(2);
    cell.setCellValue("Total cost of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    // Write the output to a file
    String file = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/loan-calculator.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:jdbreport.model.io.xls.poi.Excel2003Writer.java

License:Apache License

private void saveSheet(Workbook wb, ReportModel model, ReportBook reportBook, String reportTitle)
        throws SaveReportException {

    CreationHelper createHelper = wb.getCreationHelper();

    Sheet sheet = wb.createSheet(reportTitle);
    sheet.setDisplayGridlines(reportBook.isShowGrid());
    sheet.setPrintGridlines(false);//from w  ww. j a v a 2  s .co  m
    sheet.setFitToPage(model.isStretchPage());
    sheet.setDisplayRowColHeadings(model.isShowHeader() || model.isShowRowHeader());
    ReportPage rp = model.getReportPage();
    sheet.setMargin(Sheet.TopMargin, rp.getTopMargin(Units.INCH));
    sheet.setMargin(Sheet.BottomMargin, rp.getBottomMargin(Units.INCH));
    sheet.setMargin(Sheet.LeftMargin, rp.getLeftMargin(Units.INCH));
    sheet.setMargin(Sheet.RightMargin, rp.getRightMargin(Units.INCH));
    sheet.getPrintSetup().setLandscape(rp.getOrientation() == ReportPage.LANDSCAPE);
    short paperSize = convertPaperSize(rp.getPaperSize());
    if (paperSize > 0) {
        sheet.getPrintSetup().setPaperSize(paperSize);
    }

    TableColumnModel cm = model.getColumnModel();

    for (int c = 0; c < model.getColumnCount(); c++) {
        if (model.isColumnBreak(c)) {
            sheet.setColumnBreak(c);
        }

        //char width in points
        float char_width = 5.5f;
        sheet.setColumnWidth(c,
                (int) ((((ReportColumn) cm.getColumn(c)).getNativeWidth() - 2) / char_width * 256));
    }

    fillStyles(wb, reportBook);

    createRows(model, sheet);

    drawing = sheet.createDrawingPatriarch();
    for (int row = 0; row < model.getRowCount(); row++) {
        saveRow(wb, sheet, reportBook, model, row, createHelper);
    }
    drawing = null;
}

From source file:om.edu.squ.squportal.portlet.tsurvey.dao.excel.TeachingSurveyExcelImpl.java

License:Open Source License

/**
 * //from  w  ww . j  ava2s  .c  om
 * method name  : getExcelSurveyReport
 * @param object
 * @param response
 * @param params
 * @param locale
 * @return
 * @throws DocumentException
 * @throws IOException
 * TeachingSurveyExcelImpl
 * return type  : OutputStream
 * 
 * purpose      : Get Streaming excel object for valid/invalid survey report
 *
 * Date          :   Mar 16, 2016 1:23:57 PM
 */
public OutputStream getExcelSurveyReport(String templateName, Object object, ResourceResponse response,
        Map<String, String> params, Locale locale) throws DocumentException, IOException {
    int colHead = 0;
    int rowNum = 0;
    String paramStaffRole = params.get(Constants.CONST_ROLE_STAFF);
    String paramTypeSurvey = params.get(Constants.CONST_PARAM_TYPE_SURVEY);
    String paramSemesterCode = params.get(Constants.CONST_PARAM_SEMESTER_CODE);
    String titleRegion = null;

    Workbook workbook = new HSSFWorkbook();
    CreationHelper creationHelper = workbook.getCreationHelper();
    Map<String, CellStyle> styles = createStyles(workbook);
    Sheet sheet = null;
    Cell cellSH = null;
    List<ReportSummary> reportSummaries = (List<ReportSummary>) object;

    if (templateName.equals(Constants.CONST_VALID_SURVEY_REPORT)) {
        sheet = workbook.createSheet(
                UtilProperty.getMessage("prop.course.teaching.survey.report.survey.valid", null, locale));
    }
    if (templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) {
        sheet = workbook.createSheet(
                UtilProperty.getMessage("prop.course.teaching.survey.report.survey.invalid", null, locale));
    }

    sheet.getPrintSetup().setLandscape(true);
    sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);

    /**  Header Footer **/
    Footer footer = sheet.getFooter();
    Header header = sheet.getHeader();
    footer.setRight("Page &P of &N");
    footer.setLeft("&D");
    header.setLeft(UtilProperty.getMessage("prop.course.teaching.survey.heading", null, locale));
    header.setCenter(UtilProperty.getMessage("prop.course.teaching.survey.title", null, locale));
    header.setRight(paramTypeSurvey + " - " + paramSemesterCode);

    sheet.setRepeatingRows(CellRangeAddress.valueOf("2:2"));
    sheet.setDisplayGridlines(true);
    sheet.setPrintGridlines(true);

    /**  Title **/
    Row titleRow = sheet.createRow(rowNum);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue(paramTypeSurvey + " - " + paramSemesterCode);
    titleCell.setCellStyle(styles.get(TITLE));

    ++rowNum;
    titleRegion = "$A$" + rowNum + ":$O$" + rowNum;
    sheet.addMergedRegion(CellRangeAddress.valueOf(titleRegion));

    /**  Header Row **/
    Row rowSubHeader = sheet.createRow(rowNum++);

    if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)
            && !templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) {

        cellSH = rowSubHeader.createCell(colHead++);
        cellSH.setCellValue(creationHelper.createRichTextString(
                UtilProperty.getMessage("prop.course.teaching.survey.rank.university", null, locale)));
        cellSH.setCellStyle(styles.get(SUB_HEADER));
        cellSH = rowSubHeader.createCell(colHead++);
        cellSH.setCellValue(creationHelper.createRichTextString(
                UtilProperty.getMessage("prop.course.teaching.survey.rank.college", null, locale)));
        cellSH.setCellStyle(styles.get(SUB_HEADER));
        cellSH = rowSubHeader.createCell(colHead++);
        cellSH.setCellValue(creationHelper.createRichTextString(
                UtilProperty.getMessage("prop.course.teaching.survey.rank.department", null, locale)));
        cellSH.setCellStyle(styles.get(SUB_HEADER));
    }
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.instructor.id", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.instructor.name", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.college", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)) {
        cellSH = rowSubHeader.createCell(colHead++);
        cellSH.setCellValue(creationHelper.createRichTextString(
                UtilProperty.getMessage("prop.course.teaching.survey.department", null, locale)));
        cellSH.setCellStyle(styles.get(SUB_HEADER));
    }
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.course.code", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.section", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.student.registered", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.response.number", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.analysis.mean", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.analysis.response.percentage", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.analysis.mean", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.analysis.response.percentage", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    /**  Report details **/
    for (ReportSummary reportSummary : reportSummaries) {
        int colNum = 0;
        Row row = sheet.createRow((short) rowNum);

        if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)
                && !templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) {
            row.createCell(colNum++).setCellValue(reportSummary.getUniversityRank());
            row.createCell(colNum++).setCellValue(reportSummary.getCollegeRank());
            row.createCell(colNum++).setCellValue(reportSummary.getDepartmentRank());
        }
        row.createCell(colNum++).setCellValue(Double.parseDouble(reportSummary.getEmpNumber()));
        row.createCell(colNum++).setCellValue(creationHelper.createRichTextString(reportSummary.getEmpName()));
        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(reportSummary.getCollegeCode()));
        if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)) {
            row.createCell(colNum++)
                    .setCellValue(creationHelper.createRichTextString(reportSummary.getDepartmentName()));
        }
        row.createCell(colNum++).setCellValue(reportSummary.getCourseCode());
        row.createCell(colNum++).setCellValue(Integer.parseInt(reportSummary.getSectionNo()));
        row.createCell(colNum++).setCellValue(reportSummary.getRegisteredStudent());

        Cell cellStudentNoResponse = row.createCell(colNum++);
        cellStudentNoResponse.setCellValue(reportSummary.getStudentNoResponse());
        cellStudentNoResponse.setCellStyle(styles.get(FORMULA_1));

        Cell cellTeachingMean = row.createCell(colNum++);
        cellTeachingMean.setCellValue(reportSummary.getTeachingMean());
        cellTeachingMean.setCellStyle(styles.get(FORMULA_1));

        Cell cellTeachingPercentageFavor = row.createCell(colNum++);
        cellTeachingPercentageFavor.setCellValue(reportSummary.getTeachingPercentageFavor());
        cellTeachingPercentageFavor.setCellStyle(styles.get(FORMULA_1));

        Cell cellQuestionMean = row.createCell(colNum++);
        cellQuestionMean.setCellValue(reportSummary.getQuestionMean());
        cellQuestionMean.setCellStyle(styles.get(FORMULA_1));

        Cell cellQuestionPercentageFavor = row.createCell(colNum++);
        cellQuestionPercentageFavor.setCellValue(reportSummary.getQuestionPercentageFavor());
        cellQuestionPercentageFavor.setCellStyle(styles.get(FORMULA_1));

        rowNum++;

    }

    response.setContentType("application/vnd.ms-excel");
    OutputStream outputStream = response.getPortletOutputStream();
    workbook.write(outputStream);
    outputStream.flush();
    outputStream.close();

    return null;
}

From source file:om.edu.squ.squportal.portlet.tsurvey.dao.excel.TeachingSurveyExcelImpl.java

License:Open Source License

/**
 * /*  w ww.j  a  v  a  2 s. c  o m*/
 * method name  : getExcelCollegeCoursesAsstDean
 * @param templateName
 * @param object
 * @param response
 * @param params
 * @param locale
 * @return
 * @throws DocumentException
 * @throws IOException
 * TeachingSurveyExcelImpl
 * return type  : OutputStream
 * 
 * purpose      : 
 *
 * Date          :   Jun 7, 2016 11:49:24 AM
 */
public OutputStream getExcelCollegeCoursesAsstDean(String templateName, Object object,
        ResourceResponse response, Map<String, String> params, Locale locale)
        throws DocumentException, IOException {
    int colHead = 0;
    int rowNum = 0;
    String paramTypeSurvey = params.get(Constants.CONST_PARAM_TYPE_SURVEY);
    String titleRegion = null;
    List<StudentResponse> studentResponses = (List<StudentResponse>) object;

    Workbook workbook = new HSSFWorkbook();
    CreationHelper creationHelper = workbook.getCreationHelper();
    Map<String, CellStyle> styles = createStyles(workbook);
    Sheet sheet = null;
    Cell cellSH = null;

    sheet = workbook
            .createSheet(UtilProperty.getMessage("prop.course.teaching.survey.courses.list", null, locale));

    sheet.getPrintSetup().setLandscape(true);
    sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);

    /**  Header Footer **/
    Footer footer = sheet.getFooter();
    Header header = sheet.getHeader();
    footer.setRight("Page &P of &N");
    footer.setLeft("&D");
    header.setLeft(UtilProperty.getMessage("prop.course.teaching.survey.heading", null, locale));
    header.setCenter(UtilProperty.getMessage("prop.course.teaching.survey.title", null, locale));
    header.setRight(paramTypeSurvey);

    sheet.setRepeatingRows(CellRangeAddress.valueOf("2:2"));
    sheet.setDisplayGridlines(true);
    sheet.setPrintGridlines(true);

    /**  Title **/
    Row titleRow = sheet.createRow(rowNum);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue(paramTypeSurvey);
    titleCell.setCellStyle(styles.get(TITLE));

    ++rowNum;
    titleRegion = "$A$" + rowNum + ":$O$" + rowNum;
    sheet.addMergedRegion(CellRangeAddress.valueOf(titleRegion));

    /**  Header Row **/
    Row rowSubHeader = sheet.createRow(rowNum++);

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.analysis.department", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper
            .createRichTextString(UtilProperty.getMessage("prop.course.teaching.survey.course", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.section", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.committee.member.number", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.committee.member.name", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.seats.taken", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.response.students", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.include.exclude", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    /**  Report details **/
    for (StudentResponse studentResponse : studentResponses) {
        int colNum = 0;
        Row row = sheet.createRow((short) rowNum);

        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(studentResponse.getDepartmentName()));
        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(studentResponse.getCourseCode()));
        row.createCell(colNum++).setCellValue(Integer.parseInt(studentResponse.getSectionNo()));
        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(studentResponse.getEmpNumber()));
        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(studentResponse.getEmpName()));
        row.createCell(colNum++).setCellValue(studentResponse.getSeatsTaken());
        row.createCell(colNum++).setCellValue(studentResponse.getStudentResponse());
        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(studentResponse.getIncludeExclude()));

        rowNum++;

    }

    response.setContentType("application/vnd.ms-excel");
    OutputStream outputStream = response.getPortletOutputStream();
    workbook.write(outputStream);
    outputStream.flush();
    outputStream.close();

    return null;
}

From source file:org.dashbuilder.dataset.backend.DataSetBackendServicesImpl.java

License:Apache License

@Override
public String exportDataSetExcel(DataSet dataSet) {
    // TODO?: Excel 2010 limits: 1,048,576 rows by 16,384 columns; row width 255 characters
    if (dataSet == null)
        throw new IllegalArgumentException("Null dataSet specified!");
    int columnCount = dataSet.getColumns().size();
    int rowCount = dataSet.getRowCount() + 1; //Include header row;
    int row = 0;/*from  w w w .j a  v a  2  s  .c  o m*/

    SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
    Map<String, CellStyle> styles = createStyles(wb);
    Sheet sh = wb.createSheet("Sheet 1");

    // General setup
    sh.setDisplayGridlines(true);
    sh.setPrintGridlines(false);
    sh.setFitToPage(true);
    sh.setHorizontallyCenter(true);
    PrintSetup printSetup = sh.getPrintSetup();
    printSetup.setLandscape(true);

    // Create header
    Row header = sh.createRow(row++);
    header.setHeightInPoints(20f);
    for (int i = 0; i < columnCount; i++) {
        Cell cell = header.createCell(i);
        cell.setCellStyle(styles.get("header"));
        cell.setCellValue(dataSet.getColumnByIndex(i).getId());
    }

    // Create data rows
    for (; row < rowCount; row++) {
        Row _row = sh.createRow(row);
        for (int cellnum = 0; cellnum < columnCount; cellnum++) {
            Cell cell = _row.createCell(cellnum);
            Object value = dataSet.getValueAt(row - 1, cellnum);
            if (value instanceof Short || value instanceof Long || value instanceof Integer
                    || value instanceof BigInteger) {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellStyle(styles.get("integer_number_cell"));
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Float || value instanceof Double || value instanceof BigDecimal) {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellStyle(styles.get("decimal_number_cell"));
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellStyle(styles.get("date_cell"));
                cell.setCellValue((Date) value);
            } else if (value instanceof Interval) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellStyle(styles.get("text_cell"));
                cell.setCellValue(((Interval) value).getName());
            } else {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellStyle(styles.get("text_cell"));
                cell.setCellValue(value.toString());
            }
        }
    }

    // Adjust column size
    for (int i = 0; i < columnCount; i++) {
        sh.autoSizeColumn(i);
    }

    Path tempExcelFilePath = null;
    try {
        tempExcelFilePath = ioService.createTempFile("export", "xlsx", null);
        OutputStream os = Files.newOutputStream(tempExcelFilePath);
        wb.write(os);
        os.flush();
        os.close();
    } catch (Exception e) {
        log.error("Error in excel export: ", e);
    }

    // Dispose of temporary files backing this workbook on disk
    if (!wb.dispose())
        log.warn("Could not dispose of temporary file associated to data export!");

    return tempExcelFilePath.toString();
}

From source file:org.dashbuilder.dataset.backend.DataSetExportServicesImpl.java

License:Apache License

@Override
public org.uberfire.backend.vfs.Path exportDataSetExcel(DataSet dataSet) {
    try {//from   www .j a v a  2s  . c  o m
        // TODO?: Excel 2010 limits: 1,048,576 rows by 16,384 columns; row width 255 characters
        if (dataSet == null)
            throw new IllegalArgumentException("Null dataSet specified!");
        int columnCount = dataSet.getColumns().size();
        int rowCount = dataSet.getRowCount() + 1; //Include header row;
        int row = 0;

        SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
        Map<String, CellStyle> styles = createStyles(wb);
        Sheet sh = wb.createSheet("Sheet 1");

        // General setup
        sh.setDisplayGridlines(true);
        sh.setPrintGridlines(false);
        sh.setFitToPage(true);
        sh.setHorizontallyCenter(true);
        PrintSetup printSetup = sh.getPrintSetup();
        printSetup.setLandscape(true);

        // Create header
        Row header = sh.createRow(row++);
        header.setHeightInPoints(20f);
        for (int i = 0; i < columnCount; i++) {
            Cell cell = header.createCell(i);
            cell.setCellStyle(styles.get("header"));
            cell.setCellValue(dataSet.getColumnByIndex(i).getId());
        }

        // Create data rows
        for (; row < rowCount; row++) {
            Row _row = sh.createRow(row);
            for (int cellnum = 0; cellnum < columnCount; cellnum++) {
                Cell cell = _row.createCell(cellnum);
                Object value = dataSet.getValueAt(row - 1, cellnum);
                if (value instanceof Short || value instanceof Long || value instanceof Integer
                        || value instanceof BigInteger) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellStyle(styles.get("integer_number_cell"));
                    cell.setCellValue(((Number) value).doubleValue());
                } else if (value instanceof Float || value instanceof Double || value instanceof BigDecimal) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellStyle(styles.get("decimal_number_cell"));
                    cell.setCellValue(((Number) value).doubleValue());
                } else if (value instanceof Date) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellStyle(styles.get("date_cell"));
                    cell.setCellValue((Date) value);
                } else if (value instanceof Interval) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellStyle(styles.get("text_cell"));
                    cell.setCellValue(((Interval) value).getName());
                } else {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellStyle(styles.get("text_cell"));
                    cell.setCellValue(value.toString());
                }
            }
        }

        // Adjust column size
        for (int i = 0; i < columnCount; i++) {
            sh.autoSizeColumn(i);
        }

        String tempXlsFile = uuidGenerator.newUuid() + ".xlsx";
        Path tempXlsPath = gitStorage.createTempFile(tempXlsFile);
        OutputStream os = Files.newOutputStream(tempXlsPath);
        wb.write(os);
        os.flush();
        os.close();

        // Dispose of temporary files backing this workbook on disk
        if (!wb.dispose()) {
            log.warn("Could not dispose of temporary file associated to data export!");
        }
        return Paths.convert(tempXlsPath);
    } catch (Exception e) {
        throw exceptionManager.handleException(e);
    }
}

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

License:Apache License

public InputStream exportExcel(Table table) {
    // TODO?: Excel 2010 limits: 1,048,576 rows by 16,384 columns; row width 255 characters
    if (table == null)
        throw new IllegalArgumentException("Null table specified!");
    int columnCount = table.getColumnCount();
    int rowCount = table.getRowCount() + 1; //Include header row
    int row = 0;//w  w w.j  a va2 s .co m

    SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
    Map<String, CellStyle> styles = createStyles(wb);
    Sheet sh = wb.createSheet("Sheet 1");

    // General setup
    sh.setDisplayGridlines(true);
    sh.setPrintGridlines(false);
    sh.setFitToPage(true);
    sh.setHorizontallyCenter(true);
    PrintSetup printSetup = sh.getPrintSetup();
    printSetup.setLandscape(true);

    // Create header
    Row header = sh.createRow(row++);
    header.setHeightInPoints(20f);
    for (int i = 0; i < columnCount; i++) {
        Cell cell = header.createCell(i);
        cell.setCellStyle(styles.get("header"));
        cell.setCellValue(table.getColumnName(i));
    }

    // Create data rows
    for (; row < rowCount; row++) {
        Row _row = sh.createRow(row);
        for (int cellnum = 0; cellnum < columnCount; cellnum++) {
            Cell cell = _row.createCell(cellnum);
            Object value = table.getValueAt(row - 1, cellnum);
            if (value instanceof Short || value instanceof Long || value instanceof Integer
                    || value instanceof BigInteger) {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellStyle(styles.get("integer_number_cell"));
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Float || value instanceof Double || value instanceof BigDecimal) {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellStyle(styles.get("decimal_number_cell"));
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellStyle(styles.get("date_cell"));
                cell.setCellValue((Date) value);
            } else if (value instanceof Interval) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellStyle(styles.get("text_cell"));
                cell.setCellValue(((Interval) value).getDescription(LocaleManager.currentLocale()));
            } else if (value == null) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellStyle(styles.get("text_cell"));
                cell.setCellValue("");
            } else {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellStyle(styles.get("text_cell"));
                cell.setCellValue(value.toString());
            }
        }
    }

    // Adjust column size
    for (int i = 0; i < columnCount; i++) {
        sh.autoSizeColumn(i);
    }

    ByteArrayInputStream bis = null;
    try {
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        wb.write(bos);
        bis = new ByteArrayInputStream(bos.toByteArray());
        bos.close();
    } catch (IOException e) {
        log.error("Data export error: ", e);
    }

    // Dispose of temporary files backing this workbook on disk
    if (!wb.dispose())
        log.warn("Could not dispose of temporary file associated to data export!");

    return bis;
}

From source file:org.pentaho.reporting.engine.classic.core.modules.output.table.xls.helper.ExcelPrinterBase.java

License:Open Source License

protected void configureSheetPaperSize(final Sheet sheet, final PhysicalPageBox page) {
    Configuration config = getConfig();
    final String paper = config
            .getConfigProperty("org.pentaho.reporting.engine.classic.core.modules.output.table.xls.Paper");
    final String orientation = config.getConfigProperty(
            "org.pentaho.reporting.engine.classic.core.modules.output.table.xls.PaperOrientation");
    final short scale = (short) ParserUtil.parseInt(
            config.getConfigProperty(//from   w w  w.  j  a  v  a2 s .co  m
                    "org.pentaho.reporting.engine.classic.core.modules.output.table.xls.PrintScaleFactor"),
            100);
    final short hres = (short) ParserUtil.parseInt(config.getConfigProperty(
            "org.pentaho.reporting.engine.classic.core.modules.output.table.xls.PrintHorizontalResolution"),
            -1);
    final short vres = (short) ParserUtil.parseInt(config.getConfigProperty(
            "org.pentaho.reporting.engine.classic.core.modules.output.table.xls.PrintVerticalResolution"), -1);
    final boolean noColors = "true".equals(config.getConfigProperty(
            "org.pentaho.reporting.engine.classic.core.modules.output.table.xls.PrintNoColors"));
    final boolean notes = "true".equals(config.getConfigProperty(
            "org.pentaho.reporting.engine.classic.core.modules.output.table.xls.PrintNotes"));
    final boolean usePage = "true".equals(config.getConfigProperty(
            "org.pentaho.reporting.engine.classic.core.modules.output.table.xls.PrintUsePage"));
    final boolean draft = "true".equals(config.getConfigProperty(
            "org.pentaho.reporting.engine.classic.core.modules.output.table.xls.PrintDraft"));

    final PrintSetup printSetup = sheet.getPrintSetup();
    ExcelPrintSetupFactory.performPageSetup(printSetup, page, paper, orientation);
    printSetup.setScale(scale);
    printSetup.setNoColor(noColors);
    printSetup.setNotes(notes);
    printSetup.setUsePage(usePage);
    if (hres > 0) {
        printSetup.setHResolution(hres);
    }
    if (vres > 0) {
        printSetup.setVResolution(vres);
    }
    printSetup.setDraft(draft);

    final boolean displayGridLines = "true".equals(config.getConfigProperty(
            "org.pentaho.reporting.engine.classic.core.modules.output.table.xls.GridLinesDisplayed"));
    final boolean printGridLines = "true".equals(config.getConfigProperty(
            "org.pentaho.reporting.engine.classic.core.modules.output.table.xls.GridLinesPrinted"));
    sheet.setDisplayGridlines(displayGridLines);
    sheet.setPrintGridlines(printGridLines);
}

From source file:output.ExcelM3Upgrad.java

private void writeMigration() {
    Sheet sheet = workbook.getSheetAt(0);
    workbook.setSheetName(0, "Migration");
    sheet.setDisplayGridlines(false);
    sheet.setPrintGridlines(false);/*from  w  ww.  j  a  v  a 2s  . com*/
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    styles = createStyles(workbook);

    int rownum = beginROW;
    int cellnum = beginCOL;
    Row row = sheet.createRow(rownum++);
    for (int k = 0; k < model.getListColumn().length; k++) {
        Cell cell = row.createCell(cellnum++);
        cell.setCellValue(i18n.Language.getLabel(model.getListColumn()[k].getIdLng()));
        cell.setCellStyle(styles.get("header"));
        sheet.setColumnHidden(cell.getColumnIndex(), model.getListColumn()[k].isHidden());
        sheet.autoSizeColumn(k);
        dialStatus();
    }
    ArrayList<Integer> listHeader = new ArrayList<>();
    for (int i = 0; i < M3UpdObjModel.header.length; i++) {
        listHeader.add(M3UpdObjModel.header[i]);
    }

    String[] listLevel = i18n.Language.traduce(Ressource.listLevel)
            .toArray(new String[Ressource.listLevel.length]);

    data = model.getData();
    for (int i = 0; i < data.length; i++) {
        busyDial.setText("Alimentation de la ligne " + (i + 1) + " sur " + data.length);
        row = sheet.createRow(rownum++);
        Object[] objArr = data[i];
        cellnum = beginCOL;
        boolean first = true;
        int j = 0;
        for (Object obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof Date) {
                cell.setCellValue((Date) obj);
            } else if (obj instanceof Boolean) {
                if (first) {
                    first = false;
                    if ((Boolean) obj) {
                        cell.setCellValue("Oui");
                    } else {
                        cell.setCellValue("Non");
                    }
                } else {
                    if ((Boolean) obj) {
                        cell.setCellValue("OK");
                    } else {
                        cell.setCellValue("KO");
                    }
                }
            } else if (obj instanceof String) {
                cell.setCellValue((String) obj);
            } else if (obj instanceof Double) {
                cell.setCellValue((Double) obj);
            }
            if (listHeader.indexOf(218) == j) {
                try {
                    int n = Integer.parseInt(obj.toString().trim());
                    if (n == -1) {
                        cell.setCellValue("ERROR");
                    } else {
                        cell.setCellValue(listLevel[n]);
                    }
                } catch (NumberFormatException ex) {
                    cell.setCellValue("");
                }

            }

            if (j < objArr.length - 3) {
                cell.setCellStyle(styles.get("cell_b_centered_locked"));
            } else {
                cell.setCellStyle(styles.get("cell_b_centered"));
            }
            j++;
            dialStatus();
        }
        dialStatus();
    }

    dialStatus();
    busyDial.setText("Formatage du document");
    CellRangeAddressList userList = new CellRangeAddressList(beginROW + 1, beginROW + data.length,
            beginCOL + data[0].length - 1, beginCOL + data[0].length - 1);
    DataValidationConstraint userConstraint;
    DataValidation userValidation;

    if (type == 0) {
        userConstraint = DVConstraint.createExplicitListConstraint((String[]) model.getM3UserModel()
                .getListUserSelect().toArray(new String[model.getM3UserModel().getListUserSelect().size()]));
        userValidation = new HSSFDataValidation(userList, userConstraint);
    } else {
        XSSFDataValidationHelper userHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        userConstraint = (XSSFDataValidationConstraint) userHelper
                .createExplicitListConstraint((String[]) model.getM3UserModel().getListUserSelect()
                        .toArray(new String[model.getM3UserModel().getListUserSelect().size()]));
        userValidation = (XSSFDataValidation) userHelper.createValidation(userConstraint, userList);
    }
    sheet.addValidationData(userValidation);

    CellRangeAddressList migList = new CellRangeAddressList(beginROW + 1, beginROW + data.length,
            beginCOL + data[0].length - 2, beginCOL + data[0].length - 2);
    DataValidationConstraint migConstraint;
    DataValidation migValidation;

    if (type == 0) {
        migConstraint = DVConstraint.createExplicitListConstraint(new String[] { "OK", "KO" });
        migValidation = new HSSFDataValidation(migList, migConstraint);
    } else {
        XSSFDataValidationHelper migHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        migConstraint = (XSSFDataValidationConstraint) migHelper
                .createExplicitListConstraint(new String[] { "OK", "KO" });
        migValidation = (XSSFDataValidation) migHelper.createValidation(migConstraint, migList);
    }
    sheet.addValidationData(migValidation);

    CellRangeAddressList levelList = new CellRangeAddressList(beginROW + 1, beginROW + data.length,
            beginCOL + data[0].length - 3, beginCOL + data[0].length - 3);
    DataValidationConstraint levelConstraint;
    DataValidation levelValidation;

    ArrayList<String> listNameLevel = new ArrayList<>();
    listNameLevel.add("ERROR");
    listNameLevel.addAll(i18n.Language.traduce(Ressource.listLevel));//.toArray(new String[Ressource.listLevel.length])
    if (type == 0) {
        levelConstraint = DVConstraint
                .createExplicitListConstraint(listNameLevel.toArray(new String[listNameLevel.size()]));
        levelValidation = new HSSFDataValidation(levelList, levelConstraint);
    } else {
        XSSFDataValidationHelper levelHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        levelConstraint = (XSSFDataValidationConstraint) levelHelper.createExplicitListConstraint(
                i18n.Language.traduce(Ressource.listLevel).toArray(new String[Ressource.listLevel.length]));
        levelValidation = (XSSFDataValidation) levelHelper.createValidation(levelConstraint, levelList);
    }
    sheet.addValidationData(levelValidation);

    int irow = beginROW;
    int icol = beginCOL + model.getListColumn().length + 2;
    row = sheet.getRow(irow);
    Cell cell = row.createCell(icol);
    sheet.addMergedRegion(new CellRangeAddress(irow, irow, icol, icol + 1));
    cell.setCellValue("Estimation de la charge");
    cell.setCellStyle(styles.get("header"));

    irow++;
    row = sheet.getRow(irow);

    int cpt = 0;
    ArrayList<String> listStringLevel = i18n.Language.traduce(Ressource.listLevel);
    for (String s : listStringLevel) {
        cell = row.createCell(icol);
        cell.setCellValue(s);
        cell.setCellStyle(styles.get("cell_b_centered_locked"));
        cell = row.createCell(icol + 1);
        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(218) + beginCOL);
        cell.setCellFormula(
                "COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter
                        + (beginROW + data.length + 1) + ",\"" + s + "\")*" + Ressource.listWeightLevel[cpt]);
        cell.setCellStyle(styles.get("cell_b_centered_locked"));
        irow++;
        row = sheet.getRow(irow);
        cpt++;
    }
    row = sheet.getRow(irow);
    cell = row.createCell(icol);
    cell.setCellValue("Total des charges");
    cell.setCellStyle(styles.get("cell_b_centered_locked"));
    cell = row.createCell(icol + 1);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(icol + 1));
    cell.setCellFormula("SUM(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":"
            + columnLetter + (beginROW + Ressource.listLevel.length + 1) + ")");
    cell.setCellStyle(styles.get("cell_b_centered_locked"));

    for (int k = 0; k < model.getListColumn().length + 3; k++) {
        sheet.autoSizeColumn(k);
    }

    sheet.protectSheet("3kles2014");
}

From source file:ro.dabuno.office.integration.BusinessPlan.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;/* w  ww  .  ja  va2 s. com*/

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Business Plan");

    //turn off gridlines
    sheet.setDisplayGridlines(false);
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    //the following three statements are required only for HSSF
    sheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 1);

    //the header row: centered text in 48pt font
    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);
    for (int i = 0; i < titles.length; i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(styles.get("header"));
    }
    //columns for 11 weeks starting from 9-Jul
    Calendar calendar = Calendar.getInstance();
    int year = calendar.get(Calendar.YEAR);

    calendar.setTime(fmt.parse("9-Jul"));
    calendar.set(Calendar.YEAR, year);
    for (int i = 0; i < 11; i++) {
        Cell cell = headerRow.createCell(titles.length + i);
        cell.setCellValue(calendar);
        cell.setCellStyle(styles.get("header_date"));
        calendar.roll(Calendar.WEEK_OF_YEAR, true);
    }
    //freeze the first row
    sheet.createFreezePane(0, 1);

    Row row;
    Cell cell;
    int rownum = 1;
    for (int i = 0; i < data.length; i++, rownum++) {
        row = sheet.createRow(rownum);
        if (data[i] == null)
            continue;

        for (int j = 0; j < data[i].length; j++) {
            cell = row.createCell(j);
            String styleName;
            boolean isHeader = i == 0 || data[i - 1] == null;
            switch (j) {
            case 0:
                if (isHeader) {
                    styleName = "cell_b";
                    cell.setCellValue(Double.parseDouble(data[i][j]));
                } else {
                    styleName = "cell_normal";
                    cell.setCellValue(data[i][j]);
                }
                break;
            case 1:
                if (isHeader) {
                    styleName = i == 0 ? "cell_h" : "cell_bb";
                } else {
                    styleName = "cell_indented";
                }
                cell.setCellValue(data[i][j]);
                break;
            case 2:
                styleName = isHeader ? "cell_b" : "cell_normal";
                cell.setCellValue(data[i][j]);
                break;
            case 3:
                styleName = isHeader ? "cell_b_centered" : "cell_normal_centered";
                cell.setCellValue(Integer.parseInt(data[i][j]));
                break;
            case 4: {
                calendar.setTime(fmt.parse(data[i][j]));
                calendar.set(Calendar.YEAR, year);
                cell.setCellValue(calendar);
                styleName = isHeader ? "cell_b_date" : "cell_normal_date";
                break;
            }
            case 5: {
                int r = rownum + 1;
                String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")";
                cell.setCellFormula(fmla);
                styleName = isHeader ? "cell_bg" : "cell_g";
                break;
            }
            default:
                styleName = data[i][j] != null ? "cell_blue" : "cell_normal";
            }

            cell.setCellStyle(styles.get(styleName));
        }
    }

    //group rows for each phase, row numbers are 0-based
    sheet.groupRow(4, 6);
    sheet.groupRow(9, 13);
    sheet.groupRow(16, 18);

    //set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 256 * 6);
    sheet.setColumnWidth(1, 256 * 33);
    sheet.setColumnWidth(2, 256 * 20);
    sheet.setZoom(75); //75% scale

    // Write the output to a file
    String file = "businessplan.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();

    wb.close();
}