Search in sources :

Example 26 with HSSFCellStyle

use of org.apache.poi.hssf.usermodel.HSSFCellStyle in project poi by apache.

the class RepeatingRowsAndColumns method main.

public static void main(String[] args) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("first sheet");
    HSSFSheet sheet2 = wb.createSheet("second sheet");
    HSSFSheet sheet3 = wb.createSheet("third sheet");
    HSSFFont boldFont = wb.createFont();
    boldFont.setFontHeightInPoints((short) 22);
    boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    HSSFCellStyle boldStyle = wb.createCellStyle();
    boldStyle.setFont(boldFont);
    HSSFRow row = sheet1.createRow(1);
    HSSFCell cell = row.createCell(0);
    cell.setCellValue("This quick brown fox");
    cell.setCellStyle(boldStyle);
    // Set the columns to repeat from column 0 to 2 on the first sheet
    sheet1.setRepeatingColumns(CellRangeAddress.valueOf("A:C"));
    // Set the rows to repeat from row 0 to 2 on the second sheet.
    sheet2.setRepeatingRows(CellRangeAddress.valueOf("1:3"));
    // Set the the repeating rows and columns on the third sheet.
    CellRangeAddress cra = CellRangeAddress.valueOf("D1:E2");
    sheet3.setRepeatingColumns(cra);
    sheet3.setRepeatingRows(cra);
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}
Also used : HSSFCellStyle(org.apache.poi.hssf.usermodel.HSSFCellStyle) HSSFCell(org.apache.poi.hssf.usermodel.HSSFCell) FileOutputStream(java.io.FileOutputStream) HSSFRow(org.apache.poi.hssf.usermodel.HSSFRow) HSSFSheet(org.apache.poi.hssf.usermodel.HSSFSheet) HSSFFont(org.apache.poi.hssf.usermodel.HSSFFont) CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress) HSSFWorkbook(org.apache.poi.hssf.usermodel.HSSFWorkbook)

Example 27 with HSSFCellStyle

use of org.apache.poi.hssf.usermodel.HSSFCellStyle in project poi by apache.

the class InCellLists method listInCell.

/**
     * Inserts a list of plain items - that is items that are neither
     * numbered or bulleted - into a single cell.
     *
     * @param workbook A reference to the HSSFWorkbook that 'contains' the
     *                 cell.
     * @param listItems An ArrayList whose elements encapsulate the text for
     *                  the list's items.
     * @param cell An instance of the HSSFCell class that encapsulates a
     *             reference to the spreadsheet cell into which the list
     *             will be written.
     */
public void listInCell(HSSFWorkbook workbook, ArrayList<String> listItems, HSSFCell cell) {
    StringBuilder buffer = new StringBuilder();
    HSSFCellStyle wrapStyle = workbook.createCellStyle();
    wrapStyle.setWrapText(true);
    for (String listItem : listItems) {
        buffer.append(listItem);
        buffer.append("\n");
    }
    // The StringBuffer's contents are the source for the contents
    // of the cell.
    cell.setCellValue(new HSSFRichTextString(buffer.toString().trim()));
    cell.setCellStyle(wrapStyle);
}
Also used : HSSFCellStyle(org.apache.poi.hssf.usermodel.HSSFCellStyle) HSSFRichTextString(org.apache.poi.hssf.usermodel.HSSFRichTextString) HSSFRichTextString(org.apache.poi.hssf.usermodel.HSSFRichTextString)

Example 28 with HSSFCellStyle

use of org.apache.poi.hssf.usermodel.HSSFCellStyle in project poi by apache.

the class HSSFHtmlHelper method colorStyles.

@Override
public void colorStyles(CellStyle style, Formatter out) {
    HSSFCellStyle cs = (HSSFCellStyle) style;
    out.format("  /* fill pattern = %d */%n", cs.getFillPatternEnum().getCode());
    styleColor(out, "background-color", cs.getFillForegroundColor());
    styleColor(out, "color", cs.getFont(wb).getColor());
    styleColor(out, "border-left-color", cs.getLeftBorderColor());
    styleColor(out, "border-right-color", cs.getRightBorderColor());
    styleColor(out, "border-top-color", cs.getTopBorderColor());
    styleColor(out, "border-bottom-color", cs.getBottomBorderColor());
}
Also used : HSSFCellStyle(org.apache.poi.hssf.usermodel.HSSFCellStyle)

Example 29 with HSSFCellStyle

use of org.apache.poi.hssf.usermodel.HSSFCellStyle in project poi by apache.

the class AbstractExcelConverter method isTextEmpty.

protected boolean isTextEmpty(HSSFCell cell) {
    final String value;
    switch(cell.getCellTypeEnum()) {
        case STRING:
            // XXX: enrich
            value = cell.getRichStringCellValue().getString();
            break;
        case FORMULA:
            switch(cell.getCachedFormulaResultTypeEnum()) {
                case STRING:
                    HSSFRichTextString str = cell.getRichStringCellValue();
                    if (str == null || str.length() <= 0)
                        return false;
                    value = str.toString();
                    break;
                case NUMERIC:
                    HSSFCellStyle style = cell.getCellStyle();
                    double nval = cell.getNumericCellValue();
                    short df = style.getDataFormat();
                    String dfs = style.getDataFormatString();
                    value = _formatter.formatRawCellContents(nval, df, dfs);
                    break;
                case BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case ERROR:
                    value = ErrorEval.getText(cell.getErrorCellValue());
                    break;
                default:
                    value = ExcelToHtmlUtils.EMPTY;
                    break;
            }
            break;
        case BLANK:
            value = ExcelToHtmlUtils.EMPTY;
            break;
        case NUMERIC:
            value = _formatter.formatCellValue(cell);
            break;
        case BOOLEAN:
            value = String.valueOf(cell.getBooleanCellValue());
            break;
        case ERROR:
            value = ErrorEval.getText(cell.getErrorCellValue());
            break;
        default:
            return true;
    }
    return ExcelToHtmlUtils.isEmpty(value);
}
Also used : HSSFCellStyle(org.apache.poi.hssf.usermodel.HSSFCellStyle) HSSFRichTextString(org.apache.poi.hssf.usermodel.HSSFRichTextString) HSSFRichTextString(org.apache.poi.hssf.usermodel.HSSFRichTextString)

Example 30 with HSSFCellStyle

use of org.apache.poi.hssf.usermodel.HSSFCellStyle in project poi by apache.

the class ExcelToHtmlConverter method processCell.

protected boolean processCell(HSSFCell cell, Element tableCellElement, int normalWidthPx, int maxSpannedWidthPx, float normalHeightPt) {
    final HSSFCellStyle cellStyle = cell.getCellStyle();
    String value;
    switch(cell.getCellTypeEnum()) {
        case STRING:
            // XXX: enrich
            value = cell.getRichStringCellValue().getString();
            break;
        case FORMULA:
            switch(cell.getCachedFormulaResultTypeEnum()) {
                case STRING:
                    HSSFRichTextString str = cell.getRichStringCellValue();
                    if (str != null && str.length() > 0) {
                        value = (str.toString());
                    } else {
                        value = ExcelToHtmlUtils.EMPTY;
                    }
                    break;
                case NUMERIC:
                    double nValue = cell.getNumericCellValue();
                    short df = cellStyle.getDataFormat();
                    String dfs = cellStyle.getDataFormatString();
                    value = _formatter.formatRawCellContents(nValue, df, dfs);
                    break;
                case BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case ERROR:
                    value = ErrorEval.getText(cell.getErrorCellValue());
                    break;
                default:
                    logger.log(POILogger.WARN, "Unexpected cell cachedFormulaResultType (" + cell.getCachedFormulaResultTypeEnum() + ")");
                    value = ExcelToHtmlUtils.EMPTY;
                    break;
            }
            break;
        case BLANK:
            value = ExcelToHtmlUtils.EMPTY;
            break;
        case NUMERIC:
            value = _formatter.formatCellValue(cell);
            break;
        case BOOLEAN:
            value = String.valueOf(cell.getBooleanCellValue());
            break;
        case ERROR:
            value = ErrorEval.getText(cell.getErrorCellValue());
            break;
        default:
            logger.log(POILogger.WARN, "Unexpected cell type (" + cell.getCellTypeEnum() + ")");
            return true;
    }
    final boolean noText = ExcelToHtmlUtils.isEmpty(value);
    final boolean wrapInDivs = !noText && isUseDivsToSpan() && !cellStyle.getWrapText();
    if (cellStyle.getIndex() != 0) {
        @SuppressWarnings("resource") HSSFWorkbook workbook = cell.getRow().getSheet().getWorkbook();
        String mainCssClass = getStyleClassName(workbook, cellStyle);
        if (wrapInDivs) {
            tableCellElement.setAttribute("class", mainCssClass + " " + cssClassContainerCell);
        } else {
            tableCellElement.setAttribute("class", mainCssClass);
        }
        if (noText) {
            /*
                 * if cell style is defined (like borders, etc.) but cell text
                 * is empty, add "&nbsp;" to output, so browser won't collapse
                 * and ignore cell
                 */
            value = " ";
        }
    }
    if (isOutputLeadingSpacesAsNonBreaking() && value.startsWith(" ")) {
        StringBuilder builder = new StringBuilder();
        for (int c = 0; c < value.length(); c++) {
            if (value.charAt(c) != ' ')
                break;
            builder.append(' ');
        }
        if (value.length() != builder.length())
            builder.append(value.substring(builder.length()));
        value = builder.toString();
    }
    Text text = htmlDocumentFacade.createText(value);
    if (wrapInDivs) {
        Element outerDiv = htmlDocumentFacade.createBlock();
        outerDiv.setAttribute("class", this.cssClassContainerDiv);
        Element innerDiv = htmlDocumentFacade.createBlock();
        StringBuilder innerDivStyle = new StringBuilder();
        innerDivStyle.append("position:absolute;min-width:");
        innerDivStyle.append(normalWidthPx);
        innerDivStyle.append("px;");
        if (maxSpannedWidthPx != Integer.MAX_VALUE) {
            innerDivStyle.append("max-width:");
            innerDivStyle.append(maxSpannedWidthPx);
            innerDivStyle.append("px;");
        }
        innerDivStyle.append("overflow:hidden;max-height:");
        innerDivStyle.append(normalHeightPt);
        innerDivStyle.append("pt;white-space:nowrap;");
        ExcelToHtmlUtils.appendAlign(innerDivStyle, cellStyle.getAlignment());
        htmlDocumentFacade.addStyleClass(outerDiv, cssClassPrefixDiv, innerDivStyle.toString());
        innerDiv.appendChild(text);
        outerDiv.appendChild(innerDiv);
        tableCellElement.appendChild(outerDiv);
    } else {
        tableCellElement.appendChild(text);
    }
    return ExcelToHtmlUtils.isEmpty(value) && (cellStyle.getIndex() == 0);
}
Also used : HSSFCellStyle(org.apache.poi.hssf.usermodel.HSSFCellStyle) HSSFRichTextString(org.apache.poi.hssf.usermodel.HSSFRichTextString) Element(org.w3c.dom.Element) Text(org.w3c.dom.Text) HSSFRichTextString(org.apache.poi.hssf.usermodel.HSSFRichTextString) HSSFWorkbook(org.apache.poi.hssf.usermodel.HSSFWorkbook)

Aggregations

HSSFCellStyle (org.apache.poi.hssf.usermodel.HSSFCellStyle)35 HSSFRichTextString (org.apache.poi.hssf.usermodel.HSSFRichTextString)19 HSSFCell (org.apache.poi.hssf.usermodel.HSSFCell)18 HSSFFont (org.apache.poi.hssf.usermodel.HSSFFont)16 HSSFRow (org.apache.poi.hssf.usermodel.HSSFRow)16 HSSFSheet (org.apache.poi.hssf.usermodel.HSSFSheet)12 HSSFWorkbook (org.apache.poi.hssf.usermodel.HSSFWorkbook)12 FileOutputStream (java.io.FileOutputStream)9 Region (org.apache.poi.hssf.util.Region)2 CellRangeAddress (org.apache.poi.ss.util.CellRangeAddress)2 Element (org.w3c.dom.Element)2 Text (org.w3c.dom.Text)2 Color (java.awt.Color)1 Font (java.awt.Font)1 FileNotFoundException (java.io.FileNotFoundException)1 IOException (java.io.IOException)1 Timestamp (java.sql.Timestamp)1 DecimalFormat (java.text.DecimalFormat)1 Date (java.util.Date)1 HSSFClientAnchor (org.apache.poi.hssf.usermodel.HSSFClientAnchor)1