/**
* Property Begin Bind
*
* If no PHPExcel_Style_Alignment has been bound to PHPExcel_Style then bind this one. Return the actual bound one.
*
* @return PHPExcel_Style_Alignment
*/
private function propertyBeginBind()
{
if (!isset($this->_parent)) {
return $this;
}
// I am already bound
if ($this->_parent->propertyIsBound($this->_parentPropertyName)) {
return $this->_parent->getAlignment();
}
// Another one is already bound
$this->_parent->propertyCompleteBind($this, $this->_parentPropertyName);
// Bind myself
$this->_parent = null;
return $this;
}
/**
* Create CSS style
*
* @param PHPExcel_Style $pStyle PHPExcel_Style
* @return array
*/
private function _createCSSStyle(PHPExcel_Style $pStyle)
{
// Construct CSS
$css = '';
// Create CSS
$css = array_merge($this->_createCSSStyleAlignment($pStyle->getAlignment()), $this->_createCSSStyleBorders($pStyle->getBorders()), $this->_createCSSStyleFont($pStyle->getFont()), $this->_createCSSStyleFill($pStyle->getFill()));
// Return
return $css;
}
/**
* Write Cell Style Dxf
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_Style $pStyle Style
* @throws Exception
*/
private function _writeCellStyleDxf(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Style $pStyle = null)
{
// dxf
$objWriter->startElement('dxf');
// font
$this->_writeFont($objWriter, $pStyle->getFont());
// numFmt
$this->_writeNumFmt($objWriter, $pStyle->getNumberFormat());
// fill
$this->_writeFill($objWriter, $pStyle->getFill());
// alignment
$objWriter->startElement('alignment');
$objWriter->writeAttribute('horizontal', $pStyle->getAlignment()->getHorizontal());
$objWriter->writeAttribute('vertical', $pStyle->getAlignment()->getVertical());
$textRotation = 0;
if ($pStyle->getAlignment()->getTextRotation() >= 0) {
$textRotation = $pStyle->getAlignment()->getTextRotation();
} else {
if ($pStyle->getAlignment()->getTextRotation() < 0) {
$textRotation = 90 - $pStyle->getAlignment()->getTextRotation();
}
}
$objWriter->writeAttribute('textRotation', $textRotation);
$objWriter->endElement();
// border
$this->_writeBorder($objWriter, $pStyle->getBorders());
// protection
if ($pStyle->getProtection()->getLocked() != PHPExcel_Style_Protection::PROTECTION_INHERIT || $pStyle->getProtection()->getHidden() != PHPExcel_Style_Protection::PROTECTION_INHERIT) {
$objWriter->startElement('protection');
if ($pStyle->getProtection()->getLocked() != PHPExcel_Style_Protection::PROTECTION_INHERIT) {
$objWriter->writeAttribute('locked', $pStyle->getProtection()->getLocked() == PHPExcel_Style_Protection::PROTECTION_PROTECTED ? 'true' : 'false');
}
if ($pStyle->getProtection()->getHidden() != PHPExcel_Style_Protection::PROTECTION_INHERIT) {
$objWriter->writeAttribute('hidden', $pStyle->getProtection()->getHidden() == PHPExcel_Style_Protection::PROTECTION_PROTECTED ? 'true' : 'false');
}
$objWriter->endElement();
}
$objWriter->endElement();
}
/**
* Create CSS style
*
* @param PHPExcel_Style $pStyle PHPExcel_Style
* @return string
*/
private function _createCSSStyle(PHPExcel_Style $pStyle)
{
// Construct HTML
$html = '';
// Create CSS
$html .= ' .style' . $pStyle->getHashCode() . ' {' . "\r\n";
$html .= $this->_createCSSStyleAlignment($pStyle->getAlignment());
$html .= $this->_createCSSStyleFont($pStyle->getFont());
$html .= $this->_createCSSStyleBorders($pStyle->getBorders());
$html .= $this->_createCSSStyleFill($pStyle->getFill());
$html .= ' }' . "\r\n";
// Return
return $html;
}
/**
* Generates the sheet's workbook...
*
* @param String format extension
*/
function _generateSheets($format){
$sheets= array();
$sheets= $this->book->getSheets();
$i= 0;
if ($format=="ods"){
foreach($sheets as $sheet){
$cells= array();
$cells= $sheet->getCells();
foreach($cells as $cellarray){
foreach($cellarray as $cell){
$col= $cell->getDataColumn();
$row= $cell->getDataRow();
$data= $cell->getFormula();
$fontId= $cell->getFontStyleId();
$fontStyle= new FontStyle();
$fontStyle= $this->book->getFontStyle($fontId);
if (substr($data, 0, 1)== '=')
$this->objPHPOds->addCell($i,$row,$col,substr($data, 1),'float');
//TODO
else /*OJO CON ESTO DISCERNIR ENTRE LOS DIFERENTES TIPOS*/
$this->objPHPOds->addCell($i,$row,$col,$data,'string');
}
$this->objPHPOds->addStyle($fontStyle, $cell);
}
$i++;
}
}
else{
foreach($sheets as $sheet){
if ($i>0)
$this->objPHPExcel->createSheet();
$this->objPHPExcel->setActiveSheetIndex($i);
$j= $i + 1;
$this->objPHPExcel->getActiveSheet()->setTitle("Sheet $j");
$cells= array();
$cells= $sheet->getCells();
foreach($cells as $cellarray){
$cell= new Cell();
foreach ($cellarray as $cell){
$col= $cell->getDataColumn();
$row= $cell->getDataRow();
$row++;
$data= $cell->getFormula();
// $this->objPHPExcel= new PHPExcel();
$fontId= $cell->getFontStyleId();
$fontStyle= new FontStyle();
$fontStyle= $this->book->getFontStyle($fontId);
$fontName= $fontStyle->getFontName();
$fcolor= substr($fontStyle->getFontColor(),1);
if ($fcolor == "000000"){
//echo "$row $col $fcolor<hr>";
$ncolor= new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLACK);
$ncolor->setRGB($fcolor);
}
else{
//echo "$row $col $fcolor<hr>";
$ncolor= new PHPExcel_Style_Color();
$ncolor->setRGB($fcolor);
}
$style= new PHPExcel_Style();
$style->getFont()->setColor($ncolor);
$style->getFont()->setName($fontName);
$style->getFont()->setBold($fontStyle->getFontBold()== 1);
$style->getFont()->setItalic($fontStyle->getFontItalic()==1);
$style->getFont()->setSize($fontStyle->getFontSize());
$HzAlign= PHPExcel_Style_Alignment::HORIZONTAL_GENERAL;
switch ($fontStyle->fontHAlign){
case 0:
$HzAlign= PHPExcel_Style_Alignment::HORIZONTAL_GENERAL;
break;
case 1:
$HzAlign= PHPExcel_Style_Alignment::HORIZONTAL_LEFT;
break;
case 2:
$HzAlign= PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
break;
case 3:
$HzAlign= PHPExcel_Style_Alignment::HORIZONTAL_RIGHT;
break;
}
$VlAlign= PHPExcel_Style_Alignment::VERTICAL_BOTTOM;
switch ($fontStyle->fontVAlign){
case 0:
$VlAlign= PHPExcel_Style_Alignment::VERTICAL_BOTTOM;
break;
case 1:
$VlAlign= PHPExcel_Style_Alignment::VERTICAL_CENTER;
break;
case 2:
$VlAlign= PHPExcel_Style_Alignment::VERTICAL_TOP;
}
$style->getAlignment()->setHorizontal($HzAlign);
$style->getAlignment()->setVertical($VlAlign);
if ($fontStyle->getFontUnderline()!= 0){
$style->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
}
$this->objPHPExcel->getActiveSheet()->duplicateStyle($style, PHPExcel_Cell::stringFromColumnIndex($col) . $row);
$this->objPHPExcel->getActiveSheet()->getCellByColumnAndRow($col, $row)->setValueExplicit($data, PHPExcel_Cell_DataType::dataTypeForValue($data));
}
}
$i++;
}
}
}
/**
* Reads a general type of BIFF record. Does nothing except for moving stream pointer forward to next record.
*/
private function _readDefault()
{
$length = self::_GetInt2d($this->_data, $this->_pos + 2);
// $recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
}
/**
* The NOTE record specifies a comment associated with a particular cell. In Excel 95 (BIFF7) and earlier versions,
* this record stores a note (cell note). This feature was significantly enhanced in Excel 97.
*/
private function _readNote()
{
// echo '<b>Read Cell Annotation</b><br>';
$length = self::_GetInt2d($this->_data, $this->_pos + 2);
$recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
if ($this->_readDataOnly) {
return;
}
$cellAddress = $this->_readBIFF8CellAddress(substr($recordData, 0, 4));
if ($this->_version == self::XLS_BIFF8) {
$noteObjID = self::_GetInt2d($recordData, 6);
$noteAuthor = self::_readUnicodeStringLong(substr($recordData, 8));
$noteAuthor = $noteAuthor['value'];
// echo 'Note Address=',$cellAddress,'<br>';
// echo 'Note Object ID=',$noteObjID,'<br>';
// echo 'Note Author=',$noteAuthor,'<hr />';
//
$this->_cellNotes[$noteObjID] = array('cellRef' => $cellAddress, 'objectID' => $noteObjID, 'author' => $noteAuthor);
} else {
$extension = false;
if ($cellAddress == '$B$65536') {
// If the address row is -1 and the column is 0, (which translates as $B$65536) then this is a continuation
// note from the previous cell annotation. We're not yet handling this, so annotations longer than the
// max 2048 bytes will probably throw a wobbly.
$row = self::_GetInt2d($recordData, 0);
$extension = true;
$cellAddress = array_pop(array_keys($this->_phpSheet->getComments()));
}
// echo 'Note Address=',$cellAddress,'<br>';
$cellAddress = str_replace('$', '', $cellAddress);
$noteLength = self::_GetInt2d($recordData, 4);
$noteText = trim(substr($recordData, 6));
// echo 'Note Length=',$noteLength,'<br>';
// echo 'Note Text=',$noteText,'<br>';
if ($extension) {
// Concatenate this extension with the currently set comment for the cell
$comment = $this->_phpSheet->getComment($cellAddress);
$commentText = $comment->getText()->getPlainText();
$comment->setText($this->_parseRichText($commentText . $noteText));
} else {
// Set comment for the cell
$this->_phpSheet->getComment($cellAddress)->setText($this->_parseRichText($noteText));
}
}
}
/**
* The TEXT Object record contains the text associated with a cell annotation.
*/
private function _readTextObject()
{
$length = self::_GetInt2d($this->_data, $this->_pos + 2);
$recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
if ($this->_readDataOnly) {
return;
}
// recordData consists of an array of subrecords looking like this:
// grbit: 2 bytes; Option Flags
// rot: 2 bytes; rotation
// cchText: 2 bytes; length of the text (in the first continue record)
// cbRuns: 2 bytes; length of the formatting (in the second continue record)
// followed by the continuation records containing the actual text and formatting
$grbitOpts = self::_GetInt2d($recordData, 0);
$rot = self::_GetInt2d($recordData, 2);
$cchText = self::_GetInt2d($recordData, 10);
$cbRuns = self::_GetInt2d($recordData, 12);
$text = $this->_getSplicedRecordData();
$this->_textObjects[$this->textObjRef] = array('text' => substr($text["recordData"], $text["spliceOffsets"][0] + 1, $cchText), 'format' => substr($text["recordData"], $text["spliceOffsets"][1], $cbRuns), 'alignment' => $grbitOpts, 'rotation' => $rot);
// echo '<b>_readTextObject()</b><br>';
// var_dump($this->_textObjects[$this->textObjRef]);
// echo '<br>';
}
/**
* Read BOF
*/
private function _readBof()
{
$length = self::_GetInt2d($this->_data, $this->_pos + 2);
$recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
// offset: 2; size: 2; type of the following data
$substreamType = self::_GetInt2d($recordData, 2);
switch ($substreamType) {
case self::XLS_WorkbookGlobals:
$version = self::_GetInt2d($recordData, 0);
if ($version != self::XLS_BIFF8 && $version != self::XLS_BIFF7) {
throw new Exception('Cannot read this Excel file. Version is too old.');
}
$this->_version = $version;
break;
case self::XLS_Worksheet:
// do not use this version information for anything
// it is unreliable (OpenOffice doc, 5.8), use only version information from the global stream
break;
default:
// substream, e.g. chart
// just skip the entire substream
do {
$code = self::_GetInt2d($this->_data, $this->_pos);
$this->_readDefault();
} while ($code != self::XLS_Type_EOF && $this->_pos < $this->_dataSize);
break;
}
}
/**
* FILEPASS
*
* This record is part of the File Protection Block. It
* contains information about the read/write password of the
* file. All record contents following this record will be
* encrypted.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
private function _readFilepass()
{
$length = self::_GetInt2d($this->_data, $this->_pos + 2);
// $recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
throw new Exception('Cannot read encrypted file');
}
/**
* CODEPAGE
*
* This record stores the text encoding used to write byte
* strings, stored as MS Windows code page identifier.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
private function _readCodepage()
{
$length = self::_GetInt2d($this->_data, $this->_pos + 2);
$recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
// offset: 0; size: 2; code page identifier
$codepage = self::_GetInt2d($recordData, 0);
$this->_codepage = PHPExcel_Shared_CodePage::NumberToName($codepage);
}
/**
* DATEMODE
*
* This record specifies the base date for displaying date
* values. All dates are stored as count of days past this
* base date. In BIFF2-BIFF4 this record is part of the
* Calculation Settings Block. In BIFF5-BIFF8 it is
* stored in the Workbook Globals Substream.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
private function _readDateMode()
{
$length = self::_GetInt2d($this->_data, $this->_pos + 2);
$recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
// offset: 0; size: 2; 0 = base 1900, 1 = base 1904
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
if (ord($recordData[0]) == 1) {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
}
}
/**
* Read a FONT record
*/
private function _readFont()
{
$length = self::_GetInt2d($this->_data, $this->_pos + 2);
$recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
if (!$this->_readDataOnly) {
$objFont = new PHPExcel_Style_Font();
// offset: 0; size: 2; height of the font (in twips = 1/20 of a point)
$size = self::_GetInt2d($recordData, 0);
$objFont->setSize($size / 20);
// offset: 2; size: 2; option flags
// bit: 0; mask 0x0001; bold (redundant in BIFF5-BIFF8)
// bit: 1; mask 0x0002; italic
$isItalic = (0x2 & self::_GetInt2d($recordData, 2)) >> 1;
if ($isItalic) {
$objFont->setItalic(true);
}
// bit: 2; mask 0x0004; underlined (redundant in BIFF5-BIFF8)
// bit: 3; mask 0x0008; strike
$isStrike = (0x8 & self::_GetInt2d($recordData, 2)) >> 3;
if ($isStrike) {
$objFont->setStrikethrough(true);
}
// offset: 4; size: 2; colour index
$colorIndex = self::_GetInt2d($recordData, 4);
$objFont->colorIndex = $colorIndex;
// offset: 6; size: 2; font weight
$weight = self::_GetInt2d($recordData, 6);
switch ($weight) {
case 0x2bc:
$objFont->setBold(true);
break;
}
// offset: 8; size: 2; escapement type
$escapement = self::_GetInt2d($recordData, 8);
switch ($escapement) {
case 0x1:
$objFont->setSuperScript(true);
break;
case 0x2:
$objFont->setSubScript(true);
break;
}
// offset: 10; size: 1; underline type
$underlineType = ord($recordData[10]);
switch ($underlineType) {
case 0x0:
break;
// no underline
// no underline
case 0x1: