Example usage for org.apache.poi.hssf.usermodel HSSFSheet getPrintSetup

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getPrintSetup

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. getPrintSetup

Introduction

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

Prototype

@Override
public HSSFPrintSetup getPrintSetup()

Source Link

Document

Gets the print setup object.

Usage

From source file:module.siadap.domain.SiadapRootModule.java

License:Open Source License

private void populateSheet(HSSFSheet sheetToWriteTo, boolean considerQuotas, UnitSiadapWrapper unitToSearchIn,
        HSSFWorkbook wb, boolean shouldIncludeEndOfRole, boolean includeHarmonizationResponsibles,
        boolean shouldIncludeUniverse) {

    CreationHelper creationHelper = wb.getCreationHelper();

    // make the sheet fit the page
    PrintSetup ps = sheetToWriteTo.getPrintSetup();

    sheetToWriteTo.setAutobreaks(true);//from w ww .  jav a 2 s.c  om

    ps.setFitHeight((short) 1);
    ps.setFitWidth((short) 1);

    /* ** styles ** */

    // CostCenter style
    HSSFFont costCenterFont = wb.createFont();
    costCenterFont.setColor(HSSFColor.DARK_BLUE.index);
    costCenterFont.setFontHeightInPoints((short) 12);
    costCenterFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle costCenterStyle = wb.createCellStyle();
    costCenterStyle.setFont(costCenterFont);

    // make the Unit header style
    CellStyle unitHeaderStyle = wb.createCellStyle();
    unitHeaderStyle.setBorderBottom(CellStyle.BORDER_THIN);
    unitHeaderStyle.setBorderTop(CellStyle.BORDER_THIN);
    unitHeaderStyle.setBorderLeft(CellStyle.BORDER_THIN);
    unitHeaderStyle.setBorderRight(CellStyle.BORDER_THIN);
    unitHeaderStyle.setAlignment(CellStyle.ALIGN_CENTER);
    unitHeaderStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    HSSFFont headerFont = wb.createFont();
    headerFont.setFontHeightInPoints((short) 12);
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setItalic(true);
    unitHeaderStyle.setFont(headerFont);

    // make the default name style
    CellStyle defaultTextNameStyle = wb.createCellStyle();
    defaultTextNameStyle.setBorderLeft(CellStyle.BORDER_THIN);
    defaultTextNameStyle.setBorderRight(CellStyle.BORDER_THIN);
    defaultTextNameStyle.setBorderBottom(CellStyle.BORDER_NONE);
    defaultTextNameStyle.setBorderTop(CellStyle.BORDER_NONE);
    defaultTextNameStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    HSSFFont defaultFont = wb.createFont();
    defaultFont.setFontHeightInPoints((short) 11);
    defaultTextNameStyle.setFont(defaultFont);

    // make the last line name style
    CellStyle defaultTextNameLastStyle = wb.createCellStyle();
    defaultTextNameLastStyle.setBorderLeft(CellStyle.BORDER_THIN);
    defaultTextNameLastStyle.setBorderRight(CellStyle.BORDER_THIN);
    defaultTextNameLastStyle.setBorderBottom(CellStyle.BORDER_THIN);
    defaultTextNameLastStyle.setBorderTop(CellStyle.BORDER_NONE);
    defaultTextNameLastStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    defaultTextNameLastStyle.setFont(defaultFont);

    // make the default IST-ID last line style
    CellStyle defaultTextIstIdLastStyle = wb.createCellStyle();
    defaultTextIstIdLastStyle.setBorderLeft(CellStyle.BORDER_THIN);
    defaultTextIstIdLastStyle.setBorderBottom(CellStyle.BORDER_THIN);
    defaultTextIstIdLastStyle.setBorderTop(CellStyle.BORDER_NONE);
    defaultTextIstIdLastStyle.setBorderRight(CellStyle.BORDER_THIN);
    defaultTextIstIdLastStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    defaultTextIstIdLastStyle.setAlignment(CellStyle.ALIGN_CENTER);
    defaultTextIstIdLastStyle.setFont(defaultFont);

    // make the default IST-ID style
    CellStyle defaultTextIstIdStyle = wb.createCellStyle();
    defaultTextIstIdStyle.setBorderLeft(CellStyle.BORDER_THIN);
    defaultTextIstIdStyle.setBorderBottom(CellStyle.BORDER_NONE);
    defaultTextIstIdStyle.setBorderTop(CellStyle.BORDER_NONE);
    defaultTextIstIdStyle.setBorderRight(CellStyle.BORDER_THIN);
    defaultTextIstIdStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    defaultTextIstIdStyle.setAlignment(CellStyle.ALIGN_CENTER);
    defaultTextIstIdStyle.setFont(defaultFont);

    // header style

    // CellStyle headerStyle = wb.createCellStyle();
    // HSSFFont headerFont = wb.createFont();
    // headerFont.setFontName(HSSFFont.FONT_ARIAL);
    // headerFont.setFontHeightInPoints((short) 10);
    // headerStyle.setFont(headerFont);
    //

    // first line style
    CellStyle firstLineStyle = wb.createCellStyle();
    HSSFFont firstLineFont = wb.createFont();
    firstLineFont.setColor(HSSFColor.DARK_BLUE.index);
    firstLineFont.setFontHeightInPoints((short) 14);
    firstLineFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    firstLineStyle.setFont(firstLineFont);
    firstLineStyle.setAlignment(CellStyle.ALIGN_CENTER);
    firstLineStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

    // second line style
    CellStyle secondLineStyle = wb.createCellStyle();
    HSSFFont secondLineFont = wb.createFont();
    secondLineFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    secondLineFont.setFontHeightInPoints((short) 14);
    secondLineStyle.setFont(secondLineFont);
    secondLineStyle.setAlignment(CellStyle.ALIGN_CENTER);
    secondLineStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

    // the style for Unit Harmonization responsibles - title
    CellStyle unitHarmonizationTitleStyle = wb.createCellStyle();
    // the BLUE title font - is equal to 'firstLineFont'
    unitHarmonizationTitleStyle.setFont(firstLineFont);
    // now we just have to shade it
    unitHarmonizationTitleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    unitHarmonizationTitleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    unitHarmonizationTitleStyle.setAlignment(CellStyle.ALIGN_CENTER);

    // the style for Unit Harmonization responsibles - normal

    // let's create the BLUE Arial 14 font for the responsibles of
    // harmonization
    HSSFFont harmonizationResponsibleFont = wb.createFont();
    harmonizationResponsibleFont.setColor(HSSFColor.DARK_BLUE.index);
    harmonizationResponsibleFont.setFontHeightInPoints((short) 14);

    CellStyle unitHarmonizationResponsibleStyle = wb.createCellStyle();
    unitHarmonizationResponsibleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    unitHarmonizationResponsibleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    unitHarmonizationResponsibleStyle.setFont(harmonizationResponsibleFont);
    unitHarmonizationResponsibleStyle.setAlignment(CellStyle.ALIGN_CENTER);

    /* ** END of styles ** */

    /* ** Immutable IST header ** */

    HSSFHeader header = sheetToWriteTo.getHeader();
    header.setCenter(HSSFHeader.font("Arial", "Normal") + HSSFHeader.fontSize((short) 10));
    header.setCenter("Instituto Superior Tcnico");

    int rowIndex = START_ROW_INDEX;
    int cellIndex = START_CELL_INDEX;

    int firstLineIndex = rowIndex++;
    int secondLineIndex = rowIndex++;
    /* ** Write the first lines with the dates ** */
    HSSFRow row = sheetToWriteTo.createRow(firstLineIndex);
    HSSFCell cell = row.createCell(cellIndex);
    cell.setCellValue("SIADAP - LISTA DE AVALIADORES " + unitToSearchIn.getYear());
    cell.setCellStyle(firstLineStyle);
    sheetToWriteTo
            .addMergedRegion(new CellRangeAddress(firstLineIndex, firstLineIndex, cellIndex, cellIndex + 3));

    // second line
    if (!considerQuotas) {
        cellIndex = START_CELL_INDEX;
        row = sheetToWriteTo.createRow(secondLineIndex);
        cell = row.createCell(cellIndex);
        cell.setCellValue("PESSOAL CONTRATADO PELA ADIST");
        cell.setCellStyle(secondLineStyle);

    }

    /* ** write the IST logo ** */

    int pictureIdx = wb.addPicture(istLogoBytes, Workbook.PICTURE_TYPE_PNG);
    HSSFPatriarch drawingPatriarch = sheetToWriteTo.createDrawingPatriarch();
    ClientAnchor clientAnchor = creationHelper.createClientAnchor();
    clientAnchor.setCol1(cellIndex);
    clientAnchor.setRow1(rowIndex);
    HSSFPicture picture = drawingPatriarch.createPicture(clientAnchor, pictureIdx);

    // let's give the next item some space
    rowIndex += 6;

    /* ** Dynamic IST footer ** */

    HSSFFooter footer = sheetToWriteTo.getFooter();
    footer.setLeft("Lista gerada em: " + HSSFFooter.date() + " " + HSSFFooter.time());
    footer.setCenter(HSSFFooter.page());
    footer.setRight("SIADAP - Lista de avaliadores " + unitToSearchIn.getYear());

    for (UnitSiadapWrapper eachUnit : unitToSearchIn
            .getAllChildUnits(unitToSearchIn.getConfiguration().getUnitRelations())) {

        Collection<Person> harmonizationResponsibles = eachUnit.getHarmonizationResponsibles();
        if (includeHarmonizationResponsibles && !harmonizationResponsibles.isEmpty()) {
            // let's add the section stating the responsible for
            // Harmonization
            cellIndex = START_CELL_INDEX;
            row = sheetToWriteTo.createRow(++rowIndex);
            // let's merge the row
            sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3));
            cell = row.createCell(cellIndex);
            cell.setCellStyle(unitHarmonizationTitleStyle);
            cell.setCellValue("UNIDADE DE HARMONIZAO: " + eachUnit.getName());
            // a 'blank' styled line
            row = sheetToWriteTo.createRow(++rowIndex);
            // merge it
            sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3));
            row.createCell(cellIndex).setCellStyle(unitHarmonizationResponsibleStyle);
            // each responsible has one of the following lines
            for (Person harmonizationResponsible : harmonizationResponsibles) {
                cellIndex = START_CELL_INDEX;
                row = sheetToWriteTo.createRow(++rowIndex);
                // merge it
                sheetToWriteTo
                        .addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3));
                cell = row.createCell(cellIndex);
                cell.setCellStyle(unitHarmonizationResponsibleStyle);
                cell.setCellValue("RESPONS?VEL PELA HARMONIZAO: " + harmonizationResponsible.getName());
            }
            // and let's add an extra 'blank' styled line
            row = sheetToWriteTo.createRow(++rowIndex);
            sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3));
            row.createCell(cellIndex).setCellStyle(unitHarmonizationResponsibleStyle);
            // and a regular one! (skip one in the index)
            ++rowIndex;

        }
        if (eachUnit.getQuotaAwareTotalPeopleWorkingInUnit(false, considerQuotas) > 0) {

            row = sheetToWriteTo.createRow(++rowIndex);
            cellIndex = START_CELL_INDEX;
            // write the unit name and cost center
            String unitNameWithCC = eachUnit.getUnit().getPartyName().getContent();
            if (eachUnit.getUnit().getPartyTypesSet().contains(PartyType.readBy("CostCenter"))) {
                unitNameWithCC += " - " + eachUnit.getUnit().getAcronym();
            }
            cell = row.createCell(cellIndex++);
            sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, --cellIndex, ++cellIndex));
            cell.setCellValue(unitNameWithCC);
            cell.setCellStyle(costCenterStyle);

            /* **** write the Unit header ***** */

            // restart the cell's index
            cellIndex = START_CELL_INDEX;

            // IST id avaliado
            int firstLineAfterUnitNameIndex = ++rowIndex;
            int secondLineAfterUnitNameIndex = ++rowIndex;

            row = sheetToWriteTo.createRow(firstLineAfterUnitNameIndex);
            cell = row.createCell(cellIndex);
            cell.setCellStyle(unitHeaderStyle);
            cell.setCellValue("IST id.");

            row = sheetToWriteTo.createRow(secondLineAfterUnitNameIndex);
            cell = row.createCell(cellIndex);
            cell.setCellStyle(unitHeaderStyle);

            // merge the IST id
            sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex,
                    secondLineAfterUnitNameIndex, cellIndex, cellIndex));

            // Nome avaliado
            row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex);
            cell = row.createCell(++cellIndex);
            cell.setCellStyle(unitHeaderStyle);
            cell.setCellValue("Nome");

            row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex);
            cell = row.createCell(cellIndex);
            cell.setCellStyle(unitHeaderStyle);

            // merge
            sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex,
                    secondLineAfterUnitNameIndex, cellIndex, cellIndex));

            if (shouldIncludeUniverse) {

                // SIADAP do avaliado
                row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex);
                cell = row.createCell(++cellIndex);
                cell.setCellStyle(unitHeaderStyle);
                cell.setCellValue("SIADAP");

                row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex);
                cell = row.createCell(cellIndex);
                cell.setCellStyle(unitHeaderStyle);

                // merge
                sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex,
                        secondLineAfterUnitNameIndex, cellIndex, cellIndex));
            }

            // Ist id do avaliador
            row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex);
            cell = row.createCell(++cellIndex);
            cell.setCellStyle(unitHeaderStyle);
            cell.setCellValue("IST id.");

            row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex);
            cell = row.createCell(cellIndex);
            cell.setCellStyle(unitHeaderStyle);

            // merge
            sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex,
                    secondLineAfterUnitNameIndex, cellIndex, cellIndex));

            // avaliador
            row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex);
            cell = row.createCell(++cellIndex);
            cell.setCellStyle(unitHeaderStyle);
            cell.setCellValue("Avaliador");

            row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex);
            cell = row.createCell(cellIndex);
            cell.setCellStyle(unitHeaderStyle);

            // merge
            sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex,
                    secondLineAfterUnitNameIndex, cellIndex, cellIndex));

            List<PersonSiadapWrapper> listToUse = (considerQuotas) ? eachUnit.getUnitEmployeesWithQuotas(false)
                    : eachUnit.getUnitEmployeesWithoutQuotas(true);

            // now let's take care of exporting the persons
            for (PersonSiadapWrapper personWrapper : listToUse) {
                row = sheetToWriteTo.createRow(++rowIndex);
                // restart the cell's index
                cellIndex = START_CELL_INDEX;
                String istIdEvaluated = personWrapper.getPerson().getUser().getUsername();
                cell = row.createCell(cellIndex++);
                cell.setCellValue(istIdEvaluated);
                cell.setCellStyle(defaultTextIstIdStyle);

                String nameEvaluatedPerson = personWrapper.getPerson().getName();
                cell = row.createCell(cellIndex++);
                cell.setCellValue(nameEvaluatedPerson);
                cell.setCellStyle(defaultTextNameStyle);

                if (shouldIncludeUniverse) {

                    Siadap siadap = personWrapper.getSiadap();
                    String siadapUniverseToBeWritten = (siadap == null
                            || siadap.getDefaultSiadapUniverse() == null) ? "No definido"
                                    : siadap.getDefaultSiadapUniverse().getLocalizedName();
                    cell = row.createCell(cellIndex++);
                    cell.setCellValue(siadapUniverseToBeWritten);
                    cell.setCellStyle(defaultTextNameStyle);
                }

                PersonSiadapWrapper evaluatorWrapper = personWrapper.getEvaluator();
                String istIdEvaluator = evaluatorWrapper == null ? "-"
                        : evaluatorWrapper.getPerson().getUser().getUsername();
                cell = row.createCell(cellIndex++);
                cell.setCellValue(istIdEvaluator);
                cell.setCellStyle(defaultTextIstIdStyle);

                String nameEvaluatorWrapper = evaluatorWrapper == null ? "-" : evaluatorWrapper.getName();
                cell = row.createCell(cellIndex++);
                cell.setCellValue(nameEvaluatorWrapper);
                cell.setCellStyle(defaultTextNameStyle);

            }
            // let's make a bottom border on the last four cells
            for (int i = START_CELL_INDEX; i < START_CELL_INDEX + 4; i++) {
                cell = row.getCell(i);
                // let's diferentaitate between the IST-id and the name
                if (i == START_CELL_INDEX || i == START_CELL_INDEX + 2) // first
                // cell,
                // IST-ID
                // then.
                // or
                // third
                // cell
                // the
                // other
                // IST-ID
                {
                    cell.setCellStyle(defaultTextIstIdLastStyle);
                } else {
                    cell.setCellStyle(defaultTextNameLastStyle);
                }

            }
            row = sheetToWriteTo.createRow(++rowIndex);
            row = sheetToWriteTo.createRow(++rowIndex);

        }

    }

    sheetToWriteTo.autoSizeColumn(START_CELL_INDEX);
    sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 1);
    sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 2);
    sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 3);
    sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 4);

    // now let's resize the logo
    picture.resize();
}

From source file:net.chaosserver.timelord.data.ExcelDataReaderWriter.java

License:Open Source License

/**
 * Generates the actual workbook of data.
 *
 * @param timelordData the data to generate a workbook for
 * @return the workbook//from  w  w  w.  jav  a 2  s  .  c  o m
 */
protected HSSFWorkbook generateWorkbook(TimelordData timelordData) {
    HSSFWorkbook wb = new HSSFWorkbook();

    // Build the Map of the Styles that will be applied to cells
    // in the workbook
    Map<String, HSSFCellStyle> styleMap = buildStyleMap(wb);
    Map<String, List<String>> sheetToNotes = new TreeMap<String, List<String>>(new DateComparator());

    // Since there is an issue re-ordering sheets after they
    // have been created.  First create the book with all needed
    // sheets
    preCreateAllSheets(wb, timelordData, sheetToNotes, styleMap);

    // After all the sheets have been pre-created, iterate through all
    // the tasks to add them into the sheets.
    int rowNum = addAllTasks(wb, timelordData, sheetToNotes, styleMap);

    // This section applies all the styles, creates the footers and adds
    // the notes onto the sheet.
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        HSSFSheet sheet = wb.getSheetAt(i);
        String sheetName = wb.getSheetName(i);
        createFooterRows(sheet, rowNum, styleMap);

        // This will apply styles to the rows that had no task associated
        // for a given week.
        for (int j = 1; j < rowNum - 1; j++) {
            HSSFRow row = sheet.getRow(j);
            if (row == null) {
                row = sheet.createRow(j);
                row.setHeight((short) 0);
                HSSFCell cell = row.createCell((short) 0);
                cell.setCellStyle((HSSFCellStyle) styleMap.get("taskNameStyle"));
                cell.setCellValue("");

                cell = row.createCell(MAX_COLUMN);
                cell.setCellStyle((HSSFCellStyle) styleMap.get("totalColumnStyle"));
                cell.setCellFormula("SUM(B" + (j + 1) + ":H" + (j + 1) + ")");
            }
        }

        List<String> noteList = sheetToNotes.get(sheetName);
        createNotesRows(sheet, noteList);

        HSSFPrintSetup ps = sheet.getPrintSetup();
        ps.setLandscape(true);
    }

    // Finally order the sheets properly
    if (logger.isDebugEnabled()) {
        logger.debug("Re-ordering sheets under final order.");
    }

    return wb;
}

From source file:org.adempiere.impexp.AbstractExcelExporter.java

License:Open Source License

protected void formatPage(HSSFSheet sheet) {
    sheet.setFitToPage(true);/*from www  .j  a v  a2  s . c  o m*/
    // Print Setup
    HSSFPrintSetup ps = sheet.getPrintSetup();
    ps.setFitWidth((short) 1);
    ps.setNoColor(true);
    ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
    ps.setLandscape(false);
}

From source file:org.adempiere.print.export.PrintDataExcelExporter.java

License:Open Source License

@Override
protected void formatPage(HSSFSheet sheet) {
    super.formatPage(sheet);
    MPrintPaper paper = MPrintPaper.get(this.m_printFormat.getAD_PrintPaper_ID());
    ////from   ww  w.j  a  v a2 s .  co  m
    // Set paper size:
    short paperSize = -1;
    MediaSizeName mediaSizeName = paper.getMediaSize().getMediaSizeName();
    if (MediaSizeName.NA_LETTER.equals(mediaSizeName)) {
        paperSize = HSSFPrintSetup.LETTER_PAPERSIZE;
    } else if (MediaSizeName.NA_LEGAL.equals(mediaSizeName)) {
        paperSize = HSSFPrintSetup.LEGAL_PAPERSIZE;
    } else if (MediaSizeName.EXECUTIVE.equals(mediaSizeName)) {
        paperSize = HSSFPrintSetup.EXECUTIVE_PAPERSIZE;
    } else if (MediaSizeName.ISO_A4.equals(mediaSizeName)) {
        paperSize = HSSFPrintSetup.A4_PAPERSIZE;
    } else if (MediaSizeName.ISO_A5.equals(mediaSizeName)) {
        paperSize = HSSFPrintSetup.A5_PAPERSIZE;
    } else if (MediaSizeName.NA_NUMBER_10_ENVELOPE.equals(mediaSizeName)) {
        paperSize = HSSFPrintSetup.ENVELOPE_10_PAPERSIZE;
    }
    //      else if (MediaSizeName..equals(mediaSizeName)) {
    //         paperSize = HSSFPrintSetup.ENVELOPE_DL_PAPERSIZE;
    //      }
    //      else if (MediaSizeName..equals(mediaSizeName)) {
    //         paperSize = HSSFPrintSetup.ENVELOPE_CS_PAPERSIZE;
    //      }
    else if (MediaSizeName.MONARCH_ENVELOPE.equals(mediaSizeName)) {
        paperSize = HSSFPrintSetup.ENVELOPE_MONARCH_PAPERSIZE;
    }
    if (paperSize != -1) {
        sheet.getPrintSetup().setPaperSize(paperSize);
    }
    //
    // Set Landscape/Portrait:
    sheet.getPrintSetup().setLandscape(paper.isLandscape());
    //
    // Set Paper Margin:
    sheet.setMargin(HSSFSheet.TopMargin, ((double) paper.getMarginTop()) / 72);
    sheet.setMargin(HSSFSheet.RightMargin, ((double) paper.getMarginRight()) / 72);
    sheet.setMargin(HSSFSheet.LeftMargin, ((double) paper.getMarginLeft()) / 72);
    sheet.setMargin(HSSFSheet.BottomMargin, ((double) paper.getMarginBottom()) / 72);
    //
}

From source file:org.eclipse.scada.ae.ui.views.export.excel.impl.ExportEventsImpl.java

License:Open Source License

private HSSFSheet createSheet(final List<Event> events, final HSSFWorkbook workbook,
        final List<Field> columns) {
    final HSSFSheet sheet = workbook.createSheet(Messages.ExportImpl_ExcelSheet_Name);

    final HSSFHeader header = sheet.getHeader();
    header.setLeft(Messages.ExportImpl_ExcelSheet_Header);
    header.setRight(HeaderFooter.date() + " " + HeaderFooter.time());//$NON-NLS-1$

    final HSSFFooter footer = sheet.getFooter();
    footer.setLeft(String.format(Messages.ExportImpl_ExcelSheet_Footer_1, events.size()));

    footer.setRight(Messages.ExportImpl_ExcelSheet_Footer_2 + HeaderFooter.page()
            + Messages.ExportImpl_ExcelSheet_Footer_3 + HeaderFooter.numPages());

    makeHeader(columns, sheet);//  w w w.j a va 2  s .  c  om

    final HSSFPrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    printSetup.setFitWidth((short) 1);
    printSetup.setFitHeight((short) 0);
    printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);

    sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, columns.size() - 1));
    sheet.createFreezePane(0, 1);
    sheet.setFitToPage(true);
    sheet.setAutobreaks(true);

    printSetup.setFooterMargin(0.25);

    sheet.setMargin(Sheet.LeftMargin, 0.25);
    sheet.setMargin(Sheet.RightMargin, 0.25);
    sheet.setMargin(Sheet.TopMargin, 0.25);
    sheet.setMargin(Sheet.BottomMargin, 0.5);

    return sheet;
}

From source file:rocky.sizecounter.SizeCounterUtil.java

License:Apache License

/**
 * Count size of Excel file.// www.j  a v a  2 s  . c  om
 * 
 * @param filePath path of Excel file
 * @return SizeMetaData: Unit,Size: SHEET; Unit1,Size1:PAGE
 */
public static SizeMetaData countSpreadSheet(String filePath) {
    InputStream is = null;
    SizeMetaData sizeMD = new SizeMetaData();
    int nmPage = 0;
    int nmSheet;
    try {
        is = CommonUtil.loadResource(filePath);
        if (CommonUtil.getExtension(filePath).equals("xls")) {
            HSSFWorkbook wb = new HSSFWorkbook(is);
            wb.getDocumentSummaryInformation().getLineCount();
            nmSheet = wb.getNumberOfSheets();
            HSSFSheet sheet;

            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                sheet = wb.getSheetAt(i);

                // Count approximately number of page
                nmPage += sheet.getPrintSetup().getFitWidth() + sheet.getPrintSetup().getFitHeight();
            }

            sizeMD.setUnit(UnitType.SHEET);
            sizeMD.setSize(nmSheet);

            sizeMD.setUnit1(UnitType.PAGE);
            sizeMD.setSize1(nmPage);

        } else if (CommonUtil.getExtension(filePath).equals("xlsx")) {
            XSSFWorkbook xwb = new XSSFWorkbook(is);
            nmSheet = xwb.getNumberOfSheets();
            XSSFSheet sheet;

            for (int i = 0; i < xwb.getNumberOfSheets(); i++) {
                sheet = xwb.getSheetAt(i);

                // Count approximately number of page
                nmPage += sheet.getPrintSetup().getFitWidth() + sheet.getPrintSetup().getFitHeight();
            }

            sizeMD.setUnit(UnitType.SHEET);
            sizeMD.setSize(nmSheet);

            sizeMD.setUnit1(UnitType.PAGE);
            sizeMD.setSize1(nmPage);
        }
    } catch (FileNotFoundException ex) {
        LOG.warn("Invalid when reading file.", ex);
    } catch (IOException ex) {
        LOG.warn("Invalid when reading file.", ex);
    } catch (Exception e) {
        LOG.warn("Can not count file " + new File(filePath).getName(), e);
    } finally {
        if (is != null) {
            try {
                is.close();
            } catch (IOException ex) {
                LOG.warn("Close the file input stream", ex);
            }
        }
    }

    return sizeMD;
}

From source file:ua.com.ecotep.unianalysis.export.XLSDataExport.java

@Override
public void exportData(String selectedFile, AnProperties props,
        ObservableList<ObservableList<Object>> exportData, List<String> columnTitles) throws Exception {
    if (selectedFile == null) {
        return;/*from w w w . j  a  va 2 s.c o m*/
    }
    System.setProperty("java.awt.headless", "true");
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFPrintSetup ps = sheet.getPrintSetup();
    ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
    ps.setLandscape(true);

    HSSFCellStyle cellStyleT = wb.createCellStyle();
    HSSFFont font1 = wb.createFont();
    font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font1.setFontHeightInPoints((short) 8);
    cellStyleT.setFont(font1);

    int rnumber = 0;
    HSSFRow row = sheet.createRow(0);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
    HSSFCell cell = row.createCell(0);

    cell.setCellValue(
            new HSSFRichTextString(" ???  ???  - "
                    + DateConverters.getDateToStr(LocalDate.now())));
    cell.setCellStyle(cellStyleT);
    rnumber++;

    if (props != null) {

        String val = " :  " + DateConverters.getDateToStr(props.getDateFrom())
                + "  " + DateConverters.getDateToStr(props.getDateTo());
        createHeaderCell(sheet, rnumber, val, cellStyleT);
        rnumber++;
        //----------
        if (props.isSalMode()) {
            String type = props.getSalSalerType() == AnProperties.SALER_TYPES.PROFILE
                    ? " "
                    : "  ";
            val = "? " + type + "| " + props.getSaler().getNameSaler();
            createHeaderCell(sheet, rnumber, val, cellStyleT);
            rnumber++;
            if (props.getSalSalerType() == AnProperties.SALER_TYPES.SALER) {
                type = props.isSalDirectSales() ? "? "
                        : " ";
                val = " : " + type;
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
                if (props.isSalFixedDepartment()) {
                    val = " : ";
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                }

            }
        }
        if (props.isGoodMode()) {
            if (props.getGoodClassLev0() != null) {
                val = "? 1: " + props.getGoodClassLev0().getName();
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
                if (props.getGoodClassLev1() != null) {
                    val = "? 2: " + props.getGoodClassLev1().getName();
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                    if (props.getGoodClassLev2() != null) {
                        val = "? 3: " + props.getGoodClassLev2().getName();
                        createHeaderCell(sheet, rnumber, val, cellStyleT);
                        rnumber++;
                        if (props.getGoodClassLev3() != null) {
                            val = "? 4: " + props.getGoodClassLev3().getName();
                            createHeaderCell(sheet, rnumber, val, cellStyleT);
                            rnumber++;
                        }
                    }
                }

                if (props.getGoodCustomSearch() != null && !props.getGoodCustomSearch().isEmpty()) {
                    val = " : " + props.getGoodCustomSearch();
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                }

                if (props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() == 2) {
                    val = " : " + DateConverters.getDateToStr(props.getGoodsIndateLst().get(0));
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                    val = " : "
                            + DateConverters.getDateToStr(props.getGoodsIndateLst().get(1));
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                }
                if (!props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() > 0) {
                    val = "+: ";
                    for (LocalDate ld : props.getGoodsIndateLst()) {
                        val += DateConverters.getDateToStr(ld) + "; ";
                    }
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                }

            }

        }

        if (!props.getClLst().isEmpty()) {
            if (props.isClIncluded()) {
                val = " : ";
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
            } else {
                val = " : ";
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
            }

            val = "+: ";
            for (ClientBean cb : props.getClLst()) {
                val += cb.getClientCl() + "; ";
            }
            createHeaderCell(sheet, rnumber, val, cellStyleT);
            rnumber++;
        }

        if (!props.getVLst().isEmpty()) {
            if (props.isVIncluded()) {
                val = " : ";
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
            } else {
                val = " : ";
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
            }
            val = "";
            for (String v : props.getVLst()) {
                val += v + "; ";
            }
            createHeaderCell(sheet, rnumber, val, cellStyleT);
            rnumber++;
        }
    }
    //----------                                    

    HSSFCellStyle cellStyleH = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cellStyleH.setFont(font);
    cellStyleH.setWrapText(true);
    cellStyleH.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyleH.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);

    cellStyleH.setBorderLeft((short) 1);
    cellStyleH.setBorderRight((short) 1);
    cellStyleH.setBorderTop((short) 1);
    cellStyleH.setBorderBottom((short) 1);

    HSSFCellStyle cellStyleHh = wb.createCellStyle();
    font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cellStyleHh.setFont(font);
    cellStyleHh.setWrapText(true);
    cellStyleHh.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    cellStyleHh.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);

    cellStyleHh.setBorderLeft((short) 1);
    cellStyleHh.setBorderRight((short) 1);
    cellStyleHh.setBorderTop((short) 1);
    cellStyleHh.setBorderBottom((short) 1);

    //filling table
    HSSFCellStyle cellStyleN = wb.createCellStyle();
    cellStyleN.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    cellStyleN.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyleN.setBorderLeft((short) 1);
    cellStyleN.setBorderRight((short) 1);
    cellStyleN.setBorderTop((short) 1);
    cellStyleN.setBorderBottom((short) 1);

    HSSFCellStyle cellStyleI = wb.createCellStyle();
    cellStyleI.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyleI.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyleI.setBorderLeft((short) 1);
    cellStyleI.setBorderRight((short) 1);
    cellStyleI.setBorderTop((short) 1);
    cellStyleI.setBorderBottom((short) 1);

    HSSFCellStyle cellStyleD = wb.createCellStyle();
    cellStyleD.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    cellStyleD.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT);
    HSSFDataFormat df = wb.createDataFormat();
    cellStyleD.setDataFormat(df.getFormat("#,##0.0"));
    cellStyleD.setBorderLeft((short) 1);
    cellStyleD.setBorderRight((short) 1);
    cellStyleD.setBorderTop((short) 1);
    cellStyleD.setBorderBottom((short) 1);

    HSSFCellStyle cellStyleP = wb.createCellStyle();
    cellStyleP.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    cellStyleP.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT);

    cellStyleP.setDataFormat(df.getFormat("0.0\\%"));
    cellStyleP.setBorderLeft((short) 1);
    cellStyleP.setBorderRight((short) 1);
    cellStyleP.setBorderTop((short) 1);
    cellStyleP.setBorderBottom((short) 1);

    // filling column headers
    row = sheet.createRow(rnumber);

    String rowTitle = null;

    row = sheet.createRow(rnumber);
    row.setHeightInPoints(40);
    cell = row.createCell(0);
    cell.setCellValue(new HSSFRichTextString("?"));
    cell.setCellStyle(cellStyleH);

    for (int t = 0; t < columnTitles.size(); t++) {
        cell = row.createCell(t + 1);
        cell.setCellValue(new HSSFRichTextString(columnTitles.get(t)));
        cell.setCellStyle(cellStyleH);
    }

    // filling table with data
    rnumber++;

    for (ObservableList<Object> line : exportData) {
        row = sheet.createRow(rnumber);
        cell = row.createCell(0);
        cell.setCellValue(new HSSFRichTextString((String) line.get(0)));
        cell.setCellStyle(cellStyleN);
        for (int i = 1; i < line.size(); i++) {
            Double val = (Double) line.get(i);
            cell = row.createCell(i);
            cell.setCellStyle(cellStyleD);
            cell.setCellValue(val);
        }
        rnumber++;
    }
    for (int t = 0; t < columnTitles.size(); t++) {
        sheet.autoSizeColumn((short) t);
    }
    saveWorkBook(wb, selectedFile);
    execute(selectedFile);
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.PageLayoutTest.java

License:Open Source License

@Test
public void testRunReportXls() throws BirtException, IOException {

    InputStream inputStream = runAndRenderReport("PageLayout.rptdesign", "xls");
    assertNotNull(inputStream);/*from   w  ww .  j  a  va 2  s .  c o  m*/
    try {

        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(1, workbook.getNumberOfSheets());
        assertEquals("Page Layout Test", workbook.getSheetAt(0).getSheetName());

        HSSFSheet sheet0 = workbook.getSheetAt(0);
        HSSFPrintSetup printSetup = sheet0.getPrintSetup();
        assertEquals(HSSFPrintSetup.A4_PAPERSIZE, printSetup.getPaperSize());
        assertEquals(true, printSetup.getLandscape());
        assertEquals(1.0 / 2.54, printSetup.getHeaderMargin(), 0.01);
        assertEquals(1.0 / 2.54, printSetup.getFooterMargin(), 0.01);
        assertEquals(0.7 / 2.54, sheet0.getMargin(Sheet.LeftMargin), 0.01);
        assertEquals(0.7 / 2.54, sheet0.getMargin(Sheet.RightMargin), 0.01);
        assertEquals(1.7 / 2.54, sheet0.getMargin(Sheet.TopMargin), 0.01);
        assertEquals(1.7 / 2.54, sheet0.getMargin(Sheet.BottomMargin), 0.01);

    } finally {
        inputStream.close();
    }
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.PageLayoutTest.java

License:Open Source License

@Test
public void testRunReportPixelsXls() throws BirtException, IOException {

    InputStream inputStream = runAndRenderReport("PageLayoutPixels.rptdesign", "xls");
    assertNotNull(inputStream);/*from   w  w w  . ja v  a 2s . c  o  m*/
    try {

        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(1, workbook.getNumberOfSheets());
        assertEquals("Page Layout Test", workbook.getSheetAt(0).getSheetName());

        HSSFSheet sheet0 = workbook.getSheetAt(0);
        HSSFPrintSetup printSetup = sheet0.getPrintSetup();
        assertEquals(HSSFPrintSetup.A4_PAPERSIZE, printSetup.getPaperSize());
        assertEquals(true, printSetup.getLandscape());
        assertEquals(0.5, printSetup.getHeaderMargin(), 0.01);
        assertEquals(0.5, printSetup.getFooterMargin(), 0.01);
        assertEquals(0.75, sheet0.getMargin(Sheet.LeftMargin), 0.01);
        assertEquals(0.75, sheet0.getMargin(Sheet.RightMargin), 0.01);
        assertEquals(1.0, sheet0.getMargin(Sheet.TopMargin), 0.01);
        assertEquals(1.0, sheet0.getMargin(Sheet.BottomMargin), 0.01);

    } finally {
        inputStream.close();
    }
}