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

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

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

Introduction

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

Prototype

@Override
public void setAutobreaks(boolean b)

Source Link

Document

show automatic page breaks or not

Usage

From source file:com.eryansky.core.excelTools.ExcelUtils.java

License:Apache License

public static void copySheetStyle(HSSFWorkbook destwb, HSSFSheet dest, HSSFWorkbook srcwb, HSSFSheet src) {
    if (src == null || dest == null)
        return;//from w w w  .  jav  a 2 s  .  com

    dest.setAlternativeExpression(src.getAlternateExpression());
    dest.setAlternativeFormula(src.getAlternateFormula());
    dest.setAutobreaks(src.getAutobreaks());
    dest.setDialog(src.getDialog());
    if (src.getColumnBreaks() != null) {
        for (int col : src.getColumnBreaks()) {
            dest.setColumnBreak(col);
        }
    }
    dest.setDefaultColumnWidth(src.getDefaultColumnWidth());
    dest.setDefaultRowHeight(src.getDefaultRowHeight());
    dest.setDefaultRowHeightInPoints(src.getDefaultRowHeightInPoints());
    dest.setDisplayGuts(src.getDisplayGuts());
    dest.setFitToPage(src.getFitToPage());
    dest.setHorizontallyCenter(src.getHorizontallyCenter());
    dest.setDisplayFormulas(src.isDisplayFormulas());
    dest.setDisplayGridlines(src.isDisplayGridlines());
    dest.setDisplayRowColHeadings(src.isDisplayRowColHeadings());
    dest.setGridsPrinted(src.isGridsPrinted());
    dest.setPrintGridlines(src.isPrintGridlines());

    for (int i = 0; i < src.getNumMergedRegions(); i++) {
        CellRangeAddress r = src.getMergedRegion(i);
        dest.addMergedRegion(r);
    }

    if (src.getRowBreaks() != null) {
        for (int row : src.getRowBreaks()) {
            dest.setRowBreak(row);
        }
    }
    dest.setRowSumsBelow(src.getRowSumsBelow());
    dest.setRowSumsRight(src.getRowSumsRight());

    int maxcol = 0;
    for (int i = 0; i <= src.getLastRowNum(); i++) {
        HSSFRow row = src.getRow(i);
        if (row != null) {
            if (maxcol < row.getLastCellNum())
                maxcol = row.getLastCellNum();
        }
    }
    for (int col = 0; col <= maxcol; col++) {
        if (src.getColumnWidth(col) != src.getDefaultColumnWidth())
            dest.setColumnWidth(col, src.getColumnWidth(col));
        dest.setColumnHidden(col, src.isColumnHidden(col));
    }
}

From source file:com.report.excel.ExcelToHtmlConverter.java

License:Apache License

protected void processSheet(HSSFSheet sheet) {
    //processSheetHeader( htmlDocumentFacade.getBody(), sheet );
    sheet.setAutobreaks(true);

    final int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
    if (physicalNumberOfRows <= 0)
        return;/*from   w  ww . j av a 2  s  .c  o  m*/

    Element table = htmlDocumentFacade.createTable();
    htmlDocumentFacade.addStyleClass(table, cssClassPrefixTable, "border-collapse:collapse;border-spacing:0;");

    Element tableBody = htmlDocumentFacade.createTableBody();

    final CellRangeAddress[][] mergedRanges = ExcelToHtmlUtils.buildMergedRangesMap(sheet);

    final List<Element> emptyRowElements = new ArrayList<Element>(physicalNumberOfRows);
    int maxSheetColumns = 1;
    for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
        HSSFRow row = sheet.getRow(r);

        if (row == null)
            continue;

        if (!isOutputHiddenRows() && row.getZeroHeight())
            continue;

        Element tableRowElement = htmlDocumentFacade.createTableRow();
        htmlDocumentFacade.addStyleClass(tableRowElement, cssClassPrefixRow,
                "height:" + (row.getHeight() / 20f) + "pt;");

        int maxRowColumnNumber = processRow(mergedRanges, row, tableRowElement);

        if (maxRowColumnNumber == 0) {
            emptyRowElements.add(tableRowElement);
        } else {
            if (!emptyRowElements.isEmpty()) {
                for (Element emptyRowElement : emptyRowElements) {
                    tableBody.appendChild(emptyRowElement);
                }
                emptyRowElements.clear();
            }

            tableBody.appendChild(tableRowElement);
        }
        maxSheetColumns = Math.max(maxSheetColumns, maxRowColumnNumber);
    }

    processColumnWidths(sheet, maxSheetColumns, table);

    if (isOutputColumnHeaders()) {
        //processColumnHeaders( sheet, maxSheetColumns, table );
    }

    table.appendChild(tableBody);

    htmlDocumentFacade.getBody().appendChild(table);
}

From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportExcelXLS.java

License:Open Source License

/**
 * //from w  w w. j a v a 2s  . c o  m
 * @throws FileNotFoundException
 * @throws IOException
 */

public void write() throws FileNotFoundException, IOException {
    FileOutputStream out = new FileOutputStream(new File(filename));
    HSSFWorkbook wb;

    wb = new HSSFWorkbook();

    Map<String, HSSFCellStyle> styles = createStyles(wb);
    HSSFSheet sheet = wb.createSheet(sheetName);

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

    sheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 1);

    HSSFRow headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);

    int[][] width = new int[titles.length][titles.length];

    for (int i = 0; i < titles.length; i++) {
        HSSFCell cell = headerRow.createCell(i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(styles.get("header"));

        width[i][0] = titles[i].length();
    }

    HSSFRow row;
    HSSFCell 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);
            if (data[i][j] == null)
                data[i][j] = "";

            cell.setCellValue(data[i][j].toString());

            if (data[i][j].toString().length() > width[j][0])
                width[j][0] = data[i][j].toString().length();
        }
    }

    for (int i = 0; i < titles.length; i++) {
        int widthShort = (256 * (width[i][0] + 3));

        sheet.setColumnWidth(i, widthShort);
    }

    int position = (titles.length / 2) - 1;

    row = sheet.createRow(rownum + 3);
    cell = row.createCell(position);

    if (footName == null) {
        SimpleDateFormat df = new SimpleDateFormat("dd.MM.yyyy HH:mm");
        cell.setCellValue("Export MaklerPoint vom " + df.format(new Date(System.currentTimeMillis()))
                + " - www.maklerpoint.de");
    } else {
        cell.setCellValue(footName);
    }

    sheet.setZoom(3, 4);

    wb.write(out);
    out.close();
}

From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportKalenderExcel.java

License:Open Source License

/**
 * //from  www  .j av a  2  s.c  o m
 * @throws FileNotFoundException
 * @throws IOException
 */

public void write() throws FileNotFoundException, IOException {
    Calendar calendar = Calendar.getInstance();
    int year = calendar.get(Calendar.YEAR);

    HSSFWorkbook wb = new HSSFWorkbook();
    Map<String, HSSFCellStyle> styles = createStyles(wb);

    for (int month = 0; month < 12; month++) {
        calendar.set(Calendar.MONTH, month);
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        //create a sheet for each month
        HSSFSheet sheet = wb.createSheet(months[month]);

        //turn off gridlines
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        HSSFPrintSetup 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
        HSSFRow headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(80);
        HSSFCell titleCell = headerRow.createCell(0);
        titleCell.setCellValue(months[month] + " " + year);
        titleCell.setCellStyle(styles.get("title"));
        //                sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

        //header with month titles
        HSSFRow monthRow = sheet.createRow(1);
        for (int i = 0; i < days.length; i++) {
            //set column widths, the width is measured in units of 1/256th of a character width
            sheet.setColumnWidth((i * 2), (5 * 256)); //the column is 5 characters wide
            sheet.setColumnWidth((i * 2 + 1), (13 * 256)); //the column is 13 characters wide
            //sheet.addMergedRegion(new Region(1, (short) 1, i*2, (short) (i * 2 + 1)));
            sheet.addMergedRegion(new CellRangeAddress(1, i * 2, 1, (i * 2 + 1))); // TODO Test
            HSSFCell monthCell = monthRow.createCell((i * 2));
            monthCell.setCellValue(days[i]);
            monthCell.setCellStyle(styles.get("month"));
        }

        int cnt = 1, day = 1;
        int rownum = 2;
        for (int j = 0; j < 6; j++) {
            HSSFRow row = sheet.createRow(rownum++);
            row.setHeightInPoints(100);
            for (int i = 0; i < days.length; i++) {
                HSSFCell dayCell_1 = row.createCell((i * 2));
                HSSFCell dayCell_2 = row.createCell((i * 2 + 1));

                int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);
                if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) {
                    dayCell_1.setCellValue(day);
                    calendar.set(Calendar.DAY_OF_MONTH, ++day);

                    if (i == 0 || i == days.length - 1) {
                        dayCell_1.setCellStyle(styles.get("weekend_left"));
                        dayCell_2.setCellStyle(styles.get("weekend_right"));
                    } else {
                        dayCell_1.setCellStyle(styles.get("workday_left"));
                        dayCell_2.setCellStyle(styles.get("workday_right"));
                    }
                } else {
                    dayCell_1.setCellStyle(styles.get("grey_left"));
                    dayCell_2.setCellStyle(styles.get("grey_right"));
                }
                cnt++;
            }
            if (calendar.get(Calendar.MONTH) > month)
                break;
        }
    }

    // Write the output to a file        

    FileOutputStream out = new FileOutputStream(this.filename);
    wb.write(out);
    out.close();
}

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);

    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);/* ww w  .j  a  va  2 s. c om*/
    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: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);/*from   w  w  w  .ja v  a2s  . co  m*/

    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:org.jxstar.report.util.ReportXlsUtil.java

/**
 * //  w ww  .  ja v a  2  s.  c  o  m
 * @param mainSheet -- ?
 * @param subSheet -- ?
 * @param tempRow -- ?????
 * @return
 */
public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet, int tempRow) {
    if (mainSheet == null || subSheet == null)
        return null;
    //??
    if (!isAllowOut(mainSheet))
        return mainSheet;
    //?
    int endRowNum = mainSheet.getPhysicalNumberOfRows();

    HSSFRow sourow = null, descrow = null;
    HSSFCell sourcell = null, descell = null, orgcell = null;
    int i = 0, offsetcnt = 0;

    //?
    copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook());

    //??
    CellRangeAddress range = null;
    int mergedNum = subSheet.getNumMergedRegions();
    for (i = 0; i < mergedNum; i++) {
        range = subSheet.getMergedRegion(i);
        range.setFirstRow(range.getFirstRow() + endRowNum);
        range.setLastRow(range.getLastRow() + endRowNum);
        mainSheet.addMergedRegion(range);
    }
    range = null;
    //int k = 0;

    //?
    mainSheet.setAlternativeExpression(subSheet.getAlternateExpression());
    mainSheet.setAlternativeFormula(subSheet.getAlternateFormula());
    mainSheet.setAutobreaks(subSheet.getAutobreaks());
    mainSheet.setDialog(subSheet.getDialog());
    mainSheet.setDisplayGuts(subSheet.getDisplayGuts());
    mainSheet.setFitToPage(subSheet.getFitToPage());

    for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext();) {
        sourow = (HSSFRow) iterow.next();
        offsetcnt = sourow.getRowNum() + endRowNum;
        descrow = mainSheet.createRow(offsetcnt);
        descrow.setHeight(sourow.getHeight());
        descrow.setHeightInPoints(sourow.getHeightInPoints());

        java.util.Iterator<Cell> iter = sourow.cellIterator();
        while (iter.hasNext()) {
            sourcell = (HSSFCell) iter.next();
            int column = sourcell.getColumnIndex();
            descell = descrow.createCell(column);

            /**
             * ??????orgcell = mainSheet.getRow(row).getCell(column);
             * ??
             * ??orgcell.getCellStyle()????sheet??
             * This Style does not belong to the supplied Workbook.
             * ?descell.getCellStyle().cloneStyleFrom(sourcell.getCellStyle());???excel
             * HSSFCellStyle cs = mainSheet.getWorkbook().createCellStyle();
             * cs.cloneStyleFrom(sourcell.getCellStyle());
             * descell.setCellStyle(cs);//excel?
             * tempRow????
             */

            //????????
            int row = sourcell.getRowIndex();
            if (tempRow > 0 && row > tempRow) {
                row = tempRow;
            }
            orgcell = mainSheet.getRow(row).getCell(column);
            if (orgcell != null) {
                //orgcell.getCellType()???0
                descell.setCellType(HSSFCell.CELL_TYPE_STRING);
                //???
                descell.setCellStyle(orgcell.getCellStyle());
            } else {
                _log.showWarn("module xls [{0}, {1}] cell is null!", row, column);
            }

            if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING)
                descell.setCellValue(sourcell.getStringCellValue());
            else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
                descell.setCellValue(sourcell.getNumericCellValue());
        }
        sourow = null;
        sourcell = null;
        descrow = null;
        orgcell = null;
    }

    return mainSheet;
}