/** * Write CFRule Record * @param PHPExcel_Style_Conditional $conditional */ private function _writeCFRule(PHPExcel_Style_Conditional $conditional) { $record = 0x1b1; // Record identifier // $type : Type of the CF // $operatorType : Comparison operator if ($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_EXPRESSION) { $type = 0x2; $operatorType = 0x0; } else { if ($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CELLIS) { $type = 0x1; switch ($conditional->getOperatorType()) { case PHPExcel_Style_Conditional::OPERATOR_NONE: $operatorType = 0x0; break; case PHPExcel_Style_Conditional::OPERATOR_EQUAL: $operatorType = 0x3; break; case PHPExcel_Style_Conditional::OPERATOR_GREATERTHAN: $operatorType = 0x5; break; case PHPExcel_Style_Conditional::OPERATOR_GREATERTHANOREQUAL: $operatorType = 0x7; break; case PHPExcel_Style_Conditional::OPERATOR_LESSTHAN: $operatorType = 0x6; break; case PHPExcel_Style_Conditional::OPERATOR_LESSTHANOREQUAL: $operatorType = 0x8; break; case PHPExcel_Style_Conditional::OPERATOR_NOTEQUAL: $operatorType = 0x4; break; case PHPExcel_Style_Conditional::OPERATOR_BETWEEN: $operatorType = 0x1; break; // not OPERATOR_NOTBETWEEN 0x02 } } } // $szValue1 : size of the formula data for first value or formula // $szValue2 : size of the formula data for second value or formula $arrConditions = $conditional->getConditions(); $numConditions = sizeof($arrConditions); if ($numConditions == 1) { $szValue1 = $arrConditions[0] <= 65535 ? 3 : 0x0; $szValue2 = 0x0; $operand1 = pack('Cv', 0x1e, $arrConditions[0]); $operand2 = null; } else { if ($numConditions == 2 && $conditional->getOperatorType() == PHPExcel_Style_Conditional::OPERATOR_BETWEEN) { $szValue1 = $arrConditions[0] <= 65535 ? 3 : 0x0; $szValue2 = $arrConditions[1] <= 65535 ? 3 : 0x0; $operand1 = pack('Cv', 0x1e, $arrConditions[0]); $operand2 = pack('Cv', 0x1e, $arrConditions[1]); } else { $szValue1 = 0x0; $szValue2 = 0x0; $operand1 = null; $operand2 = null; } } // $flags : Option flags // Alignment $bAlignHz = $conditional->getStyle()->getAlignment()->getHorizontal() == null ? 1 : 0; $bAlignVt = $conditional->getStyle()->getAlignment()->getVertical() == null ? 1 : 0; $bAlignWrapTx = $conditional->getStyle()->getAlignment()->getWrapText() == false ? 1 : 0; $bTxRotation = $conditional->getStyle()->getAlignment()->getTextRotation() == null ? 1 : 0; $bIndent = $conditional->getStyle()->getAlignment()->getIndent() == 0 ? 1 : 0; $bShrinkToFit = $conditional->getStyle()->getAlignment()->getShrinkToFit() == false ? 1 : 0; if ($bAlignHz == 0 || $bAlignVt == 0 || $bAlignWrapTx == 0 || $bTxRotation == 0 || $bIndent == 0 || $bShrinkToFit == 0) { $bFormatAlign = 1; } else { $bFormatAlign = 0; } // Protection $bProtLocked = $conditional->getStyle()->getProtection()->getLocked() == null ? 1 : 0; $bProtHidden = $conditional->getStyle()->getProtection()->getHidden() == null ? 1 : 0; if ($bProtLocked == 0 || $bProtHidden == 0) { $bFormatProt = 1; } else { $bFormatProt = 0; } // Border $bBorderLeft = $conditional->getStyle()->getBorders()->getLeft()->getColor()->getARGB() == PHPExcel_Style_Color::COLOR_BLACK && $conditional->getStyle()->getBorders()->getLeft()->getBorderStyle() == PHPExcel_Style_Border::BORDER_NONE ? 1 : 0; $bBorderRight = $conditional->getStyle()->getBorders()->getRight()->getColor()->getARGB() == PHPExcel_Style_Color::COLOR_BLACK && $conditional->getStyle()->getBorders()->getRight()->getBorderStyle() == PHPExcel_Style_Border::BORDER_NONE ? 1 : 0; $bBorderTop = $conditional->getStyle()->getBorders()->getTop()->getColor()->getARGB() == PHPExcel_Style_Color::COLOR_BLACK && $conditional->getStyle()->getBorders()->getTop()->getBorderStyle() == PHPExcel_Style_Border::BORDER_NONE ? 1 : 0; $bBorderBottom = $conditional->getStyle()->getBorders()->getBottom()->getColor()->getARGB() == PHPExcel_Style_Color::COLOR_BLACK && $conditional->getStyle()->getBorders()->getBottom()->getBorderStyle() == PHPExcel_Style_Border::BORDER_NONE ? 1 : 0; if ($bBorderLeft == 0 || $bBorderRight == 0 || $bBorderTop == 0 || $bBorderBottom == 0) { $bFormatBorder = 1; } else { $bFormatBorder = 0; } // Pattern $bFillStyle = $conditional->getStyle()->getFill()->getFillType() == null ? 0 : 1; $bFillColor = $conditional->getStyle()->getFill()->getStartColor()->getARGB() == null ? 0 : 1; $bFillColorBg = $conditional->getStyle()->getFill()->getEndColor()->getARGB() == null ? 0 : 1; if ($bFillStyle == 0 || $bFillColor == 0 || $bFillColorBg == 0) { $bFormatFill = 1; } else { $bFormatFill = 0; } // Font if ($conditional->getStyle()->getFont()->getName() != null || $conditional->getStyle()->getFont()->getSize() != null || $conditional->getStyle()->getFont()->getBold() != null || $conditional->getStyle()->getFont()->getItalic() != null || $conditional->getStyle()->getFont()->getSuperScript() != null || $conditional->getStyle()->getFont()->getSubScript() != null || $conditional->getStyle()->getFont()->getUnderline() != null || $conditional->getStyle()->getFont()->getStrikethrough() != null || $conditional->getStyle()->getFont()->getColor()->getARGB() != null) { $bFormatFont = 1; } else { $bFormatFont = 0; } // Alignment $flags = 0; $flags |= 1 == $bAlignHz ? 0x1 : 0; $flags |= 1 == $bAlignVt ? 0x2 : 0; $flags |= 1 == $bAlignWrapTx ? 0x4 : 0; $flags |= 1 == $bTxRotation ? 0x8 : 0; // Justify last line flag $flags |= 1 == 1 ? 0x10 : 0; $flags |= 1 == $bIndent ? 0x20 : 0; $flags |= 1 == $bShrinkToFit ? 0x40 : 0; // Default $flags |= 1 == 1 ? 0x80 : 0; // Protection $flags |= 1 == $bProtLocked ? 0x100 : 0; $flags |= 1 == $bProtHidden ? 0x200 : 0; // Border $flags |= 1 == $bBorderLeft ? 0x400 : 0; $flags |= 1 == $bBorderRight ? 0x800 : 0; $flags |= 1 == $bBorderTop ? 0x1000 : 0; $flags |= 1 == $bBorderBottom ? 0x2000 : 0; $flags |= 1 == 1 ? 0x4000 : 0; // Top left to Bottom right border $flags |= 1 == 1 ? 0x8000 : 0; // Bottom left to Top right border // Pattern $flags |= 1 == $bFillStyle ? 0x10000 : 0; $flags |= 1 == $bFillColor ? 0x20000 : 0; $flags |= 1 == $bFillColorBg ? 0x40000 : 0; $flags |= 1 == 1 ? 0x380000 : 0; // Font $flags |= 1 == $bFormatFont ? 0x4000000 : 0; // Alignment : $flags |= 1 == $bFormatAlign ? 0x8000000 : 0; // Border $flags |= 1 == $bFormatBorder ? 0x10000000 : 0; // Pattern $flags |= 1 == $bFormatFill ? 0x20000000 : 0; // Protection $flags |= 1 == $bFormatProt ? 0x40000000 : 0; // Text direction $flags |= 1 == 0 ? 0x80000000 : 0; // Data Blocks if ($bFormatFont == 1) { // Font Name if ($conditional->getStyle()->getFont()->getName() == null) { $dataBlockFont = pack('VVVVVVVV', 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0); $dataBlockFont .= pack('VVVVVVVV', 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0); } else { $dataBlockFont = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($conditional->getStyle()->getFont()->getName()); } // Font Size if ($conditional->getStyle()->getFont()->getSize() == null) { $dataBlockFont .= pack('V', 20 * 11); } else { $dataBlockFont .= pack('V', 20 * $conditional->getStyle()->getFont()->getSize()); } // Font Options $dataBlockFont .= pack('V', 0); // Font weight if ($conditional->getStyle()->getFont()->getBold() == true) { $dataBlockFont .= pack('v', 0x2bc); } else { $dataBlockFont .= pack('v', 0x190); } // Escapement type if ($conditional->getStyle()->getFont()->getSubScript() == true) { $dataBlockFont .= pack('v', 0x2); $fontEscapement = 0; } else { if ($conditional->getStyle()->getFont()->getSuperScript() == true) { $dataBlockFont .= pack('v', 0x1); $fontEscapement = 0; } else { $dataBlockFont .= pack('v', 0x0); $fontEscapement = 1; } } // Underline type switch ($conditional->getStyle()->getFont()->getUnderline()) { case PHPExcel_Style_Font::UNDERLINE_NONE: $dataBlockFont .= pack('C', 0x0); $fontUnderline = 0; break; case PHPExcel_Style_Font::UNDERLINE_DOUBLE: $dataBlockFont .= pack('C', 0x2); $fontUnderline = 0; break; case PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING: $dataBlockFont .= pack('C', 0x22); $fontUnderline = 0; break; case PHPExcel_Style_Font::UNDERLINE_SINGLE: $dataBlockFont .= pack('C', 0x1); $fontUnderline = 0; break; case PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING: $dataBlockFont .= pack('C', 0x21); $fontUnderline = 0; break; default: $dataBlockFont .= pack('C', 0x0); $fontUnderline = 1; break; } // Not used (3) $dataBlockFont .= pack('vC', 0x0, 0x0); // Font color index switch ($conditional->getStyle()->getFont()->getColor()->getRGB()) { case '000000': $colorIdx = 0x8; break; case 'FFFFFF': $colorIdx = 0x9; break; case 'FF0000': $colorIdx = 0xa; break; case '00FF00': $colorIdx = 0xb; break; case '0000FF': $colorIdx = 0xc; break; case 'FFFF00': $colorIdx = 0xd; break; case 'FF00FF': $colorIdx = 0xe; break; case '00FFFF': $colorIdx = 0xf; break; case '800000': $colorIdx = 0x10; break; case '008000': $colorIdx = 0x11; break; case '000080': $colorIdx = 0x12; break; case '808000': $colorIdx = 0x13; break; case '800080': $colorIdx = 0x14; break; case '008080': $colorIdx = 0x15; break; case 'C0C0C0': $colorIdx = 0x16; break; case '808080': $colorIdx = 0x17; break; case '9999FF': $colorIdx = 0x18; break; case '993366': $colorIdx = 0x19; break; case 'FFFFCC': $colorIdx = 0x1a; break; case 'CCFFFF': $colorIdx = 0x1b; break; case '660066': $colorIdx = 0x1c; break; case 'FF8080': $colorIdx = 0x1d; break; case '0066CC': $colorIdx = 0x1e; break; case 'CCCCFF': $colorIdx = 0x1f; break; case '000080': $colorIdx = 0x20; break; case 'FF00FF': $colorIdx = 0x21; break; case 'FFFF00': $colorIdx = 0x22; break; case '00FFFF': $colorIdx = 0x23; break; case '800080': $colorIdx = 0x24; break; case '800000': $colorIdx = 0x25; break; case '008080': $colorIdx = 0x26; break; case '0000FF': $colorIdx = 0x27; break; case '00CCFF': $colorIdx = 0x28; break; case 'CCFFFF': $colorIdx = 0x29; break; case 'CCFFCC': $colorIdx = 0x2a; break; case 'FFFF99': $colorIdx = 0x2b; break; case '99CCFF': $colorIdx = 0x2c; break; case 'FF99CC': $colorIdx = 0x2d; break; case 'CC99FF': $colorIdx = 0x2e; break; case 'FFCC99': $colorIdx = 0x2f; break; case '3366FF': $colorIdx = 0x30; break; case '33CCCC': $colorIdx = 0x31; break; case '99CC00': $colorIdx = 0x32; break; case 'FFCC00': $colorIdx = 0x33; break; case 'FF9900': $colorIdx = 0x34; break; case 'FF6600': $colorIdx = 0x35; break; case '666699': $colorIdx = 0x36; break; case '969696': $colorIdx = 0x37; break; case '003366': $colorIdx = 0x38; break; case '339966': $colorIdx = 0x39; break; case '003300': $colorIdx = 0x3a; break; case '333300': $colorIdx = 0x3b; break; case '993300': $colorIdx = 0x3c; break; case '993366': $colorIdx = 0x3d; break; case '333399': $colorIdx = 0x3e; break; case '333333': $colorIdx = 0x3f; break; default: $colorIdx = 0x0; break; } $dataBlockFont .= pack('V', $colorIdx); // Not used (4) $dataBlockFont .= pack('V', 0x0); // Options flags for modified font attributes $optionsFlags = 0; $optionsFlagsBold = $conditional->getStyle()->getFont()->getBold() == null ? 1 : 0; $optionsFlags |= 1 == $optionsFlagsBold ? 0x2 : 0; $optionsFlags |= 1 == 1 ? 0x8 : 0; $optionsFlags |= 1 == 1 ? 0x10 : 0; $optionsFlags |= 1 == 0 ? 0x20 : 0; $optionsFlags |= 1 == 1 ? 0x80 : 0; $dataBlockFont .= pack('V', $optionsFlags); // Escapement type $dataBlockFont .= pack('V', $fontEscapement); // Underline type $dataBlockFont .= pack('V', $fontUnderline); // Always $dataBlockFont .= pack('V', 0x0); // Always $dataBlockFont .= pack('V', 0x0); // Not used (8) $dataBlockFont .= pack('VV', 0x0, 0x0); // Always $dataBlockFont .= pack('v', 0x1); } if ($bFormatAlign == 1) { $blockAlign = 0; // Alignment and text break switch ($conditional->getStyle()->getAlignment()->getHorizontal()) { case PHPExcel_Style_Alignment::HORIZONTAL_GENERAL: $blockAlign = 0; break; case PHPExcel_Style_Alignment::HORIZONTAL_LEFT: $blockAlign = 1; break; case PHPExcel_Style_Alignment::HORIZONTAL_RIGHT: $blockAlign = 3; break; case PHPExcel_Style_Alignment::HORIZONTAL_CENTER: $blockAlign = 2; break; case PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS: $blockAlign = 6; break; case PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY: $blockAlign = 5; break; } if ($conditional->getStyle()->getAlignment()->getWrapText() == true) { $blockAlign |= 1 << 3; } else { $blockAlign |= 0 << 3; } switch ($conditional->getStyle()->getAlignment()->getVertical()) { case PHPExcel_Style_Alignment::VERTICAL_BOTTOM: $blockAlign = 2 << 4; break; case PHPExcel_Style_Alignment::VERTICAL_TOP: $blockAlign = 0 << 4; break; case PHPExcel_Style_Alignment::VERTICAL_CENTER: $blockAlign = 1 << 4; break; case PHPExcel_Style_Alignment::VERTICAL_JUSTIFY: $blockAlign = 3 << 4; break; } $blockAlign |= 0 << 7; // Text rotation angle $blockRotation = $conditional->getStyle()->getAlignment()->getTextRotation(); // Indentation $blockIndent = $conditional->getStyle()->getAlignment()->getIndent(); if ($conditional->getStyle()->getAlignment()->getShrinkToFit() == true) { $blockIndent |= 1 << 4; } else { $blockIndent |= 0 << 4; } $blockIndent |= 0 << 6; // Relative indentation $blockIndentRelative = 255; $dataBlockAlign = pack('CCvvv', $blockAlign, $blockRotation, $blockIndent, $blockIndentRelative, 0x0); } if ($bFormatBorder == 1) { $blockLineStyle = 0; switch ($conditional->getStyle()->getBorders()->getLeft()->getBorderStyle()) { case PHPExcel_Style_Border::BORDER_NONE: $blockLineStyle |= 0x0; break; case PHPExcel_Style_Border::BORDER_THIN: $blockLineStyle |= 0x1; break; case PHPExcel_Style_Border::BORDER_MEDIUM: $blockLineStyle |= 0x2; break; case PHPExcel_Style_Border::BORDER_DASHED: $blockLineStyle |= 0x3; break; case PHPExcel_Style_Border::BORDER_DOTTED: $blockLineStyle |= 0x4; break; case PHPExcel_Style_Border::BORDER_THICK: $blockLineStyle |= 0x5; break; case PHPExcel_Style_Border::BORDER_DOUBLE: $blockLineStyle |= 0x6; break; case PHPExcel_Style_Border::BORDER_HAIR: $blockLineStyle |= 0x7; break; case PHPExcel_Style_Border::BORDER_MEDIUMDASHED: $blockLineStyle |= 0x8; break; case PHPExcel_Style_Border::BORDER_DASHDOT: $blockLineStyle |= 0x9; break; case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT: $blockLineStyle |= 0xa; break; case PHPExcel_Style_Border::BORDER_DASHDOTDOT: $blockLineStyle |= 0xb; break; case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT: $blockLineStyle |= 0xc; break; case PHPExcel_Style_Border::BORDER_SLANTDASHDOT: $blockLineStyle |= 0xd; break; } switch ($conditional->getStyle()->getBorders()->getRight()->getBorderStyle()) { case PHPExcel_Style_Border::BORDER_NONE: $blockLineStyle |= 0x0 << 4; break; case PHPExcel_Style_Border::BORDER_THIN: $blockLineStyle |= 0x1 << 4; break; case PHPExcel_Style_Border::BORDER_MEDIUM: $blockLineStyle |= 0x2 << 4; break; case PHPExcel_Style_Border::BORDER_DASHED: $blockLineStyle |= 0x3 << 4; break; case PHPExcel_Style_Border::BORDER_DOTTED: $blockLineStyle |= 0x4 << 4; break; case PHPExcel_Style_Border::BORDER_THICK: $blockLineStyle |= 0x5 << 4; break; case PHPExcel_Style_Border::BORDER_DOUBLE: $blockLineStyle |= 0x6 << 4; break; case PHPExcel_Style_Border::BORDER_HAIR: $blockLineStyle |= 0x7 << 4; break; case PHPExcel_Style_Border::BORDER_MEDIUMDASHED: $blockLineStyle |= 0x8 << 4; break; case PHPExcel_Style_Border::BORDER_DASHDOT: $blockLineStyle |= 0x9 << 4; break; case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT: $blockLineStyle |= 0xa << 4; break; case PHPExcel_Style_Border::BORDER_DASHDOTDOT: $blockLineStyle |= 0xb << 4; break; case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT: $blockLineStyle |= 0xc << 4; break; case PHPExcel_Style_Border::BORDER_SLANTDASHDOT: $blockLineStyle |= 0xd << 4; break; } switch ($conditional->getStyle()->getBorders()->getTop()->getBorderStyle()) { case PHPExcel_Style_Border::BORDER_NONE: $blockLineStyle |= 0x0 << 8; break; case PHPExcel_Style_Border::BORDER_THIN: $blockLineStyle |= 0x1 << 8; break; case PHPExcel_Style_Border::BORDER_MEDIUM: $blockLineStyle |= 0x2 << 8; break; case PHPExcel_Style_Border::BORDER_DASHED: $blockLineStyle |= 0x3 << 8; break; case PHPExcel_Style_Border::BORDER_DOTTED: $blockLineStyle |= 0x4 << 8; break; case PHPExcel_Style_Border::BORDER_THICK: $blockLineStyle |= 0x5 << 8; break; case PHPExcel_Style_Border::BORDER_DOUBLE: $blockLineStyle |= 0x6 << 8; break; case PHPExcel_Style_Border::BORDER_HAIR: $blockLineStyle |= 0x7 << 8; break; case PHPExcel_Style_Border::BORDER_MEDIUMDASHED: $blockLineStyle |= 0x8 << 8; break; case PHPExcel_Style_Border::BORDER_DASHDOT: $blockLineStyle |= 0x9 << 8; break; case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT: $blockLineStyle |= 0xa << 8; break; case PHPExcel_Style_Border::BORDER_DASHDOTDOT: $blockLineStyle |= 0xb << 8; break; case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT: $blockLineStyle |= 0xc << 8; break; case PHPExcel_Style_Border::BORDER_SLANTDASHDOT: $blockLineStyle |= 0xd << 8; break; } switch ($conditional->getStyle()->getBorders()->getBottom()->getBorderStyle()) { case PHPExcel_Style_Border::BORDER_NONE: $blockLineStyle |= 0x0 << 12; break; case PHPExcel_Style_Border::BORDER_THIN: $blockLineStyle |= 0x1 << 12; break; case PHPExcel_Style_Border::BORDER_MEDIUM: $blockLineStyle |= 0x2 << 12; break; case PHPExcel_Style_Border::BORDER_DASHED: $blockLineStyle |= 0x3 << 12; break; case PHPExcel_Style_Border::BORDER_DOTTED: $blockLineStyle |= 0x4 << 12; break; case PHPExcel_Style_Border::BORDER_THICK: $blockLineStyle |= 0x5 << 12; break; case PHPExcel_Style_Border::BORDER_DOUBLE: $blockLineStyle |= 0x6 << 12; break; case PHPExcel_Style_Border::BORDER_HAIR: $blockLineStyle |= 0x7 << 12; break; case PHPExcel_Style_Border::BORDER_MEDIUMDASHED: $blockLineStyle |= 0x8 << 12; break; case PHPExcel_Style_Border::BORDER_DASHDOT: $blockLineStyle |= 0x9 << 12; break; case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT: $blockLineStyle |= 0xa << 12; break; case PHPExcel_Style_Border::BORDER_DASHDOTDOT: $blockLineStyle |= 0xb << 12; break; case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT: $blockLineStyle |= 0xc << 12; break; case PHPExcel_Style_Border::BORDER_SLANTDASHDOT: $blockLineStyle |= 0xd << 12; break; } //@todo _writeCFRule() => $blockLineStyle => Index Color for left line //@todo _writeCFRule() => $blockLineStyle => Index Color for right line //@todo _writeCFRule() => $blockLineStyle => Top-left to bottom-right on/off //@todo _writeCFRule() => $blockLineStyle => Bottom-left to top-right on/off $blockColor = 0; //@todo _writeCFRule() => $blockColor => Index Color for top line //@todo _writeCFRule() => $blockColor => Index Color for bottom line //@todo _writeCFRule() => $blockColor => Index Color for diagonal line switch ($conditional->getStyle()->getBorders()->getDiagonal()->getBorderStyle()) { case PHPExcel_Style_Border::BORDER_NONE: $blockColor |= 0x0 << 21; break; case PHPExcel_Style_Border::BORDER_THIN: $blockColor |= 0x1 << 21; break; case PHPExcel_Style_Border::BORDER_MEDIUM: $blockColor |= 0x2 << 21; break; case PHPExcel_Style_Border::BORDER_DASHED: $blockColor |= 0x3 << 21; break; case PHPExcel_Style_Border::BORDER_DOTTED: $blockColor |= 0x4 << 21; break; case PHPExcel_Style_Border::BORDER_THICK: $blockColor |= 0x5 << 21; break; case PHPExcel_Style_Border::BORDER_DOUBLE: $blockColor |= 0x6 << 21; break; case PHPExcel_Style_Border::BORDER_HAIR: $blockColor |= 0x7 << 21; break; case PHPExcel_Style_Border::BORDER_MEDIUMDASHED: $blockColor |= 0x8 << 21; break; case PHPExcel_Style_Border::BORDER_DASHDOT: $blockColor |= 0x9 << 21; break; case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT: $blockColor |= 0xa << 21; break; case PHPExcel_Style_Border::BORDER_DASHDOTDOT: $blockColor |= 0xb << 21; break; case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT: $blockColor |= 0xc << 21; break; case PHPExcel_Style_Border::BORDER_SLANTDASHDOT: $blockColor |= 0xd << 21; break; } $dataBlockBorder = pack('vv', $blockLineStyle, $blockColor); } if ($bFormatFill == 1) { // Fill Patern Style $blockFillPatternStyle = 0; switch ($conditional->getStyle()->getFill()->getFillType()) { case PHPExcel_Style_Fill::FILL_NONE: $blockFillPatternStyle = 0x0; break; case PHPExcel_Style_Fill::FILL_SOLID: $blockFillPatternStyle = 0x1; break; case PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY: $blockFillPatternStyle = 0x2; break; case PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY: $blockFillPatternStyle = 0x3; break; case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY: $blockFillPatternStyle = 0x4; break; case PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL: $blockFillPatternStyle = 0x5; break; case PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL: $blockFillPatternStyle = 0x6; break; case PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN: $blockFillPatternStyle = 0x7; break; case PHPExcel_Style_Fill::FILL_PATTERN_DARKUP: $blockFillPatternStyle = 0x8; break; case PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID: $blockFillPatternStyle = 0x9; break; case PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS: $blockFillPatternStyle = 0xa; break; case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL: $blockFillPatternStyle = 0xb; break; case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL: $blockFillPatternStyle = 0xc; break; case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN: $blockFillPatternStyle = 0xd; break; case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP: $blockFillPatternStyle = 0xe; break; case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID: $blockFillPatternStyle = 0xf; break; case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS: $blockFillPatternStyle = 0x10; break; case PHPExcel_Style_Fill::FILL_PATTERN_GRAY125: $blockFillPatternStyle = 0x11; break; case PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625: $blockFillPatternStyle = 0x12; break; case PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR: $blockFillPatternStyle = 0x0; break; // does not exist in BIFF8 // does not exist in BIFF8 case PHPExcel_Style_Fill::FILL_GRADIENT_PATH: $blockFillPatternStyle = 0x0; break; // does not exist in BIFF8 // does not exist in BIFF8 default: $blockFillPatternStyle = 0x0; break; } // Color switch ($conditional->getStyle()->getFill()->getStartColor()->getRGB()) { case '000000': $colorIdxBg = 0x8; break; case 'FFFFFF': $colorIdxBg = 0x9; break; case 'FF0000': $colorIdxBg = 0xa; break; case '00FF00': $colorIdxBg = 0xb; break; case '0000FF': $colorIdxBg = 0xc; break; case 'FFFF00': $colorIdxBg = 0xd; break; case 'FF00FF': $colorIdxBg = 0xe; break; case '00FFFF': $colorIdxBg = 0xf; break; case '800000': $colorIdxBg = 0x10; break; case '008000': $colorIdxBg = 0x11; break; case '000080': $colorIdxBg = 0x12; break; case '808000': $colorIdxBg = 0x13; break; case '800080': $colorIdxBg = 0x14; break; case '008080': $colorIdxBg = 0x15; break; case 'C0C0C0': $colorIdxBg = 0x16; break; case '808080': $colorIdxBg = 0x17; break; case '9999FF': $colorIdxBg = 0x18; break; case '993366': $colorIdxBg = 0x19; break; case 'FFFFCC': $colorIdxBg = 0x1a; break; case 'CCFFFF': $colorIdxBg = 0x1b; break; case '660066': $colorIdxBg = 0x1c; break; case 'FF8080': $colorIdxBg = 0x1d; break; case '0066CC': $colorIdxBg = 0x1e; break; case 'CCCCFF': $colorIdxBg = 0x1f; break; case '000080': $colorIdxBg = 0x20; break; case 'FF00FF': $colorIdxBg = 0x21; break; case 'FFFF00': $colorIdxBg = 0x22; break; case '00FFFF': $colorIdxBg = 0x23; break; case '800080': $colorIdxBg = 0x24; break; case '800000': $colorIdxBg = 0x25; break; case '008080': $colorIdxBg = 0x26; break; case '0000FF': $colorIdxBg = 0x27; break; case '00CCFF': $colorIdxBg = 0x28; break; case 'CCFFFF': $colorIdxBg = 0x29; break; case 'CCFFCC': $colorIdxBg = 0x2a; break; case 'FFFF99': $colorIdxBg = 0x2b; break; case '99CCFF': $colorIdxBg = 0x2c; break; case 'FF99CC': $colorIdxBg = 0x2d; break; case 'CC99FF': $colorIdxBg = 0x2e; break; case 'FFCC99': $colorIdxBg = 0x2f; break; case '3366FF': $colorIdxBg = 0x30; break; case '33CCCC': $colorIdxBg = 0x31; break; case '99CC00': $colorIdxBg = 0x32; break; case 'FFCC00': $colorIdxBg = 0x33; break; case 'FF9900': $colorIdxBg = 0x34; break; case 'FF6600': $colorIdxBg = 0x35; break; case '666699': $colorIdxBg = 0x36; break; case '969696': $colorIdxBg = 0x37; break; case '003366': $colorIdxBg = 0x38; break; case '339966': $colorIdxBg = 0x39; break; case '003300': $colorIdxBg = 0x3a; break; case '333300': $colorIdxBg = 0x3b; break; case '993300': $colorIdxBg = 0x3c; break; case '993366': $colorIdxBg = 0x3d; break; case '333399': $colorIdxBg = 0x3e; break; case '333333': $colorIdxBg = 0x3f; break; default: $colorIdxBg = 0x41; break; } // Fg Color switch ($conditional->getStyle()->getFill()->getEndColor()->getRGB()) { case '000000': $colorIdxFg = 0x8; break; case 'FFFFFF': $colorIdxFg = 0x9; break; case 'FF0000': $colorIdxFg = 0xa; break; case '00FF00': $colorIdxFg = 0xb; break; case '0000FF': $colorIdxFg = 0xc; break; case 'FFFF00': $colorIdxFg = 0xd; break; case 'FF00FF': $colorIdxFg = 0xe; break; case '00FFFF': $colorIdxFg = 0xf; break; case '800000': $colorIdxFg = 0x10; break; case '008000': $colorIdxFg = 0x11; break; case '000080': $colorIdxFg = 0x12; break; case '808000': $colorIdxFg = 0x13; break; case '800080': $colorIdxFg = 0x14; break; case '008080': $colorIdxFg = 0x15; break; case 'C0C0C0': $colorIdxFg = 0x16; break; case '808080': $colorIdxFg = 0x17; break; case '9999FF': $colorIdxFg = 0x18; break; case '993366': $colorIdxFg = 0x19; break; case 'FFFFCC': $colorIdxFg = 0x1a; break; case 'CCFFFF': $colorIdxFg = 0x1b; break; case '660066': $colorIdxFg = 0x1c; break; case 'FF8080': $colorIdxFg = 0x1d; break; case '0066CC': $colorIdxFg = 0x1e; break; case 'CCCCFF': $colorIdxFg = 0x1f; break; case '000080': $colorIdxFg = 0x20; break; case 'FF00FF': $colorIdxFg = 0x21; break; case 'FFFF00': $colorIdxFg = 0x22; break; case '00FFFF': $colorIdxFg = 0x23; break; case '800080': $colorIdxFg = 0x24; break; case '800000': $colorIdxFg = 0x25; break; case '008080': $colorIdxFg = 0x26; break; case '0000FF': $colorIdxFg = 0x27; break; case '00CCFF': $colorIdxFg = 0x28; break; case 'CCFFFF': $colorIdxFg = 0x29; break; case 'CCFFCC': $colorIdxFg = 0x2a; break; case 'FFFF99': $colorIdxFg = 0x2b; break; case '99CCFF': $colorIdxFg = 0x2c; break; case 'FF99CC': $colorIdxFg = 0x2d; break; case 'CC99FF': $colorIdxFg = 0x2e; break; case 'FFCC99': $colorIdxFg = 0x2f; break; case '3366FF': $colorIdxFg = 0x30; break; case '33CCCC': $colorIdxFg = 0x31; break; case '99CC00': $colorIdxFg = 0x32; break; case 'FFCC00': $colorIdxFg = 0x33; break; case 'FF9900': $colorIdxFg = 0x34; break; case 'FF6600': $colorIdxFg = 0x35; break; case '666699': $colorIdxFg = 0x36; break; case '969696': $colorIdxFg = 0x37; break; case '003366': $colorIdxFg = 0x38; break; case '339966': $colorIdxFg = 0x39; break; case '003300': $colorIdxFg = 0x3a; break; case '333300': $colorIdxFg = 0x3b; break; case '993300': $colorIdxFg = 0x3c; break; case '993366': $colorIdxFg = 0x3d; break; case '333399': $colorIdxFg = 0x3e; break; case '333333': $colorIdxFg = 0x3f; break; default: $colorIdxFg = 0x40; break; } $dataBlockFill = pack('v', $blockFillPatternStyle); $dataBlockFill .= pack('v', $colorIdxFg | $colorIdxBg << 7); } if ($bFormatProt == 1) { $dataBlockProtection = 0; if ($conditional->getStyle()->getProtection()->getLocked() == PHPExcel_Style_Protection::PROTECTION_PROTECTED) { $dataBlockProtection = 1; } if ($conditional->getStyle()->getProtection()->getHidden() == PHPExcel_Style_Protection::PROTECTION_PROTECTED) { $dataBlockProtection = 1 << 1; } } $data = pack('CCvvVv', $type, $operatorType, $szValue1, $szValue2, $flags, 0x0); if ($bFormatFont == 1) { // Block Formatting : OK $data .= $dataBlockFont; } if ($bFormatAlign == 1) { $data .= $dataBlockAlign; } if ($bFormatBorder == 1) { $data .= $dataBlockBorder; } if ($bFormatFill == 1) { // Block Formatting : OK $data .= $dataBlockFill; } if ($bFormatProt == 1) { $data .= $dataBlockProtection; } if (!is_null($operand1)) { $data .= $operand1; } if (!is_null($operand2)) { $data .= $operand2; } $header = pack('vv', $record, strlen($data)); $this->_append($header . $data); }
/** * Writes Excel FORMAT record for non "built-in" numerical formats. * * @param string $format Custom format string * @param integer $ifmt Format index code */ private function writeNumberFormat($format, $ifmt) { $record = 0x41e; // Record identifier $numberFormatString = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($format); $length = 2 + strlen($numberFormatString); // Number of bytes to follow $header = pack("vv", $record, $length); $data = pack("v", $ifmt) . $numberFormatString; $this->append($header . $data); }
/** * Store the DATAVALIDATIONS and DATAVALIDATION records. */ private function _writeDataValidity() { // Datavalidation collection $dataValidationCollection = $this->_phpSheet->getDataValidationCollection(); // Write data validations? if (!empty($dataValidationCollection)) { // DATAVALIDATIONS record $record = 0x1b2; // Record identifier $length = 0x12; // Bytes to follow $grbit = 0x0; // Prompt box at cell, no cached validity data at DV records $horPos = 0x0; // Horizontal position of prompt box, if fixed position $verPos = 0x0; // Vertical position of prompt box, if fixed position $objId = 0xffffffff; // Object identifier of drop down arrow object, or -1 if not visible $header = pack('vv', $record, $length); $data = pack('vVVVV', $grbit, $horPos, $verPos, $objId, count($dataValidationCollection)); $this->_append($header . $data); // DATAVALIDATION records $record = 0x1be; // Record identifier foreach ($dataValidationCollection as $cellCoordinate => $dataValidation) { // initialize record data $data = ''; // options $options = 0x0; // data type $type = $dataValidation->getType(); switch ($type) { case PHPExcel_Cell_DataValidation::TYPE_NONE: $type = 0x0; break; case PHPExcel_Cell_DataValidation::TYPE_WHOLE: $type = 0x1; break; case PHPExcel_Cell_DataValidation::TYPE_DECIMAL: $type = 0x2; break; case PHPExcel_Cell_DataValidation::TYPE_LIST: $type = 0x3; break; case PHPExcel_Cell_DataValidation::TYPE_DATE: $type = 0x4; break; case PHPExcel_Cell_DataValidation::TYPE_TIME: $type = 0x5; break; case PHPExcel_Cell_DataValidation::TYPE_TEXTLENGTH: $type = 0x6; break; case PHPExcel_Cell_DataValidation::TYPE_CUSTOM: $type = 0x7; break; } $options |= $type << 0; // error style $errorStyle = $dataValidation->getType(); switch ($errorStyle) { case PHPExcel_Cell_DataValidation::STYLE_STOP: $errorStyle = 0x0; break; case PHPExcel_Cell_DataValidation::STYLE_WARNING: $errorStyle = 0x1; break; case PHPExcel_Cell_DataValidation::STYLE_INFORMATION: $errorStyle = 0x2; break; } $options |= $errorStyle << 4; // explicit formula? if ($type == 0x3 && preg_match('/^\\".*\\"$/', $dataValidation->getFormula1())) { $options |= 0x1 << 7; } // empty cells allowed $options |= $dataValidation->getAllowBlank() << 8; // show drop down $options |= !$dataValidation->getShowDropDown() << 9; // show input message $options |= $dataValidation->getShowInputMessage() << 18; // show error message $options |= $dataValidation->getShowErrorMessage() << 19; // condition operator $operator = $dataValidation->getOperator(); switch ($operator) { case PHPExcel_Cell_DataValidation::OPERATOR_BETWEEN: $operator = 0x0; break; case PHPExcel_Cell_DataValidation::OPERATOR_NOTBETWEEN: $operator = 0x1; break; case PHPExcel_Cell_DataValidation::OPERATOR_EQUAL: $operator = 0x2; break; case PHPExcel_Cell_DataValidation::OPERATOR_NOTEQUAL: $operator = 0x3; break; case PHPExcel_Cell_DataValidation::OPERATOR_GREATERTHAN: $operator = 0x4; break; case PHPExcel_Cell_DataValidation::OPERATOR_LESSTHAN: $operator = 0x5; break; case PHPExcel_Cell_DataValidation::OPERATOR_GREATERTHANOREQUAL: $operator = 0x6; break; case PHPExcel_Cell_DataValidation::OPERATOR_LESSTHANOREQUAL: $operator = 0x7; break; } $options |= $operator << 20; $data = pack('V', $options); // prompt title $promptTitle = $dataValidation->getPromptTitle() !== '' ? $dataValidation->getPromptTitle() : chr(0); $data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($promptTitle); // error title $errorTitle = $dataValidation->getErrorTitle() !== '' ? $dataValidation->getErrorTitle() : chr(0); $data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($errorTitle); // prompt text $prompt = $dataValidation->getPrompt() !== '' ? $dataValidation->getPrompt() : chr(0); $data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($prompt); // error text $error = $dataValidation->getError() !== '' ? $dataValidation->getError() : chr(0); $data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($error); // formula 1 try { $formula1 = $dataValidation->getFormula1(); if ($type == 0x3) { // list type $formula1 = str_replace(',', chr(0), $formula1); } $this->_parser->parse($formula1); $formula1 = $this->_parser->toReversePolish(); $sz1 = strlen($formula1); } catch (Exception $e) { $sz1 = 0; $formula1 = ''; } $data .= pack('vv', $sz1, 0x0); $data .= $formula1; // formula 2 try { $formula2 = $dataValidation->getFormula2(); if ($formula2 === '') { throw new Exception('No formula2'); } $this->_parser->parse($formula2); $formula2 = $this->_parser->toReversePolish(); $sz2 = strlen($formula2); } catch (Exception $e) { $sz2 = 0; $formula2 = ''; } $data .= pack('vv', $sz2, 0x0); $data .= $formula2; // cell range address list $data .= pack('v', 0x1); $data .= $this->_writeBIFF8CellRangeAddressFixed($cellCoordinate); $length = strlen($data); $header = pack("vv", $record, $length); $this->_append($header . $data); } } }
/** * Store the footer caption BIFF record. * * @access private */ function _storeFooter() { $record = 0x15; // Record identifier /* removing for now // need to fix character count (multibyte!) if (strlen($this->_phpSheet->getHeaderFooter()->getOddFooter()) <= 255) { $str = $this->_phpSheet->getHeaderFooter()->getOddFooter(); } else { $str = ''; } */ if ($this->_BIFF_version == 0x600) { $recordData = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($this->_phpSheet->getHeaderFooter()->getOddFooter()); $length = strlen($recordData); } else { $cch = strlen($str); // Length of footer string $length = 1 + $cch; $data = pack("C", $cch); $recordData = $data . $str; } $header = pack("vv", $record, $length); $this->_prepend($header . $recordData); }
/** * Writes Excel FORMAT record for non "built-in" numerical formats. * * @param string $format Custom format string * @param integer $ifmt Format index code */ private function _writeNumFormat($format, $ifmt) { $record = 0x41e; // Record identifier if ($this->_BIFF_version == 0x600) { $numberFormatString = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($format); $length = 2 + strlen($numberFormatString); // Number of bytes to follow } elseif ($this->_BIFF_version == 0x500) { $length = 3 + strlen($format); // Number of bytes to follow } $header = pack("vv", $record, $length); if ($this->_BIFF_version == 0x600) { $data = pack("v", $ifmt) . $numberFormatString; $this->_append($header . $data); } elseif ($this->_BIFF_version == 0x500) { $cch = strlen($format); // Length of format string $data = pack("vC", $ifmt, $cch); $this->_append($header . $data . $format); } }
/** * Write a DEFINEDNAME record for BIFF8 using explicit binary formula data * * @param string $name The name in UTF-8 * @param string $formulaData The binary formula data * @param string $sheetIndex 1-based sheet index the defined name applies to. 0 = global * @param boolean $isBuiltIn Built-in name? * * @return string Complete binary record data */ private function _writeDefinedNameBiff8($name, $formulaData, $sheetIndex = 0, $isBuiltIn = false) { $record = 0x18; // option flags $options = $isBuiltIn ? 0x20 : 0x0; // length of the name, character count $nlen = PHPExcel_Shared_String::CountCharacters($name); // name with stripped length field $name = substr(PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($name), 2); // size of the formula (in bytes) $sz = strlen($formulaData); // combine the parts $data = pack('vCCvvvCCCC', $options, 0, $nlen, $sz, 0, $sheetIndex, 0, 0, 0, 0) . $name . $formulaData; $length = strlen($data); $header = pack('vv', $record, $length); return $header . $data; }