22d14827101d7f06456c0c53a406f55965620da9.svn-base 121 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130213121322133213421352136213721382139214021412142214321442145214621472148214921502151215221532154215521562157215821592160216121622163216421652166216721682169217021712172217321742175217621772178217921802181218221832184218521862187218821892190219121922193219421952196219721982199220022012202220322042205220622072208220922102211221222132214221522162217221822192220222122222223222422252226222722282229223022312232223322342235223622372238223922402241224222432244224522462247224822492250225122522253225422552256225722582259226022612262226322642265226622672268226922702271227222732274227522762277227822792280228122822283228422852286228722882289229022912292229322942295229622972298229923002301230223032304230523062307230823092310231123122313231423152316231723182319232023212322232323242325232623272328232923302331233223332334233523362337233823392340234123422343234423452346234723482349235023512352235323542355235623572358235923602361236223632364236523662367236823692370237123722373237423752376237723782379238023812382238323842385238623872388238923902391239223932394239523962397239823992400240124022403240424052406240724082409241024112412241324142415241624172418241924202421242224232424242524262427242824292430243124322433243424352436243724382439244024412442244324442445244624472448244924502451245224532454245524562457245824592460246124622463246424652466246724682469247024712472247324742475247624772478247924802481248224832484248524862487248824892490249124922493249424952496249724982499250025012502250325042505250625072508250925102511251225132514251525162517251825192520252125222523252425252526252725282529253025312532253325342535253625372538253925402541254225432544254525462547254825492550255125522553255425552556255725582559256025612562256325642565256625672568256925702571257225732574257525762577257825792580258125822583258425852586258725882589259025912592259325942595259625972598259926002601260226032604260526062607260826092610261126122613261426152616261726182619262026212622262326242625262626272628262926302631263226332634263526362637263826392640264126422643264426452646264726482649265026512652265326542655265626572658265926602661266226632664266526662667266826692670267126722673267426752676267726782679268026812682268326842685268626872688268926902691269226932694269526962697269826992700270127022703270427052706270727082709271027112712271327142715271627172718271927202721272227232724272527262727272827292730273127322733273427352736273727382739274027412742274327442745274627472748274927502751275227532754275527562757275827592760276127622763276427652766276727682769277027712772277327742775277627772778277927802781278227832784278527862787278827892790279127922793279427952796279727982799280028012802280328042805280628072808280928102811281228132814281528162817281828192820282128222823282428252826282728282829283028312832283328342835283628372838283928402841284228432844284528462847284828492850285128522853285428552856285728582859286028612862286328642865286628672868286928702871287228732874287528762877287828792880288128822883288428852886288728882889289028912892289328942895289628972898289929002901290229032904290529062907290829092910291129122913291429152916291729182919292029212922292329242925292629272928292929302931293229332934293529362937293829392940294129422943294429452946294729482949295029512952295329542955295629572958295929602961296229632964296529662967296829692970297129722973297429752976297729782979298029812982298329842985298629872988298929902991299229932994299529962997299829993000300130023003300430053006300730083009301030113012301330143015301630173018301930203021302230233024302530263027302830293030303130323033303430353036303730383039304030413042304330443045304630473048304930503051305230533054305530563057305830593060306130623063306430653066306730683069307030713072307330743075307630773078307930803081308230833084308530863087308830893090309130923093309430953096309730983099310031013102310331043105310631073108310931103111311231133114311531163117311831193120312131223123312431253126312731283129313031313132313331343135313631373138313931403141314231433144314531463147314831493150315131523153315431553156315731583159316031613162316331643165316631673168316931703171317231733174317531763177317831793180318131823183318431853186318731883189319031913192319331943195319631973198319932003201320232033204320532063207320832093210321132123213321432153216321732183219322032213222322332243225322632273228322932303231323232333234323532363237323832393240324132423243324432453246324732483249325032513252325332543255325632573258325932603261326232633264326532663267326832693270327132723273327432753276327732783279328032813282328332843285328632873288328932903291329232933294329532963297329832993300330133023303330433053306330733083309331033113312331333143315331633173318331933203321332233233324332533263327332833293330333133323333333433353336333733383339334033413342334333443345334633473348334933503351335233533354335533563357335833593360336133623363336433653366336733683369337033713372337333743375337633773378337933803381338233833384338533863387338833893390339133923393339433953396339733983399340034013402340334043405340634073408340934103411341234133414341534163417341834193420342134223423342434253426342734283429343034313432343334343435343634373438343934403441344234433444344534463447344834493450345134523453345434553456345734583459346034613462346334643465346634673468346934703471347234733474347534763477347834793480348134823483348434853486348734883489349034913492349334943495349634973498349935003501350235033504350535063507350835093510351135123513351435153516351735183519352035213522352335243525352635273528352935303531353235333534353535363537353835393540354135423543354435453546354735483549355035513552355335543555355635573558355935603561356235633564356535663567356835693570357135723573357435753576357735783579358035813582358335843585358635873588358935903591359235933594359535963597359835993600360136023603360436053606360736083609361036113612361336143615361636173618361936203621362236233624362536263627362836293630363136323633363436353636363736383639364036413642364336443645364636473648364936503651365236533654365536563657365836593660366136623663366436653666366736683669367036713672367336743675367636773678367936803681
  1. <?php
  2. /**
  3. * PHPExcel
  4. *
  5. * Copyright (c) 2006 - 2014 PHPExcel
  6. *
  7. * This library is free software; you can redistribute it and/or
  8. * modify it under the terms of the GNU Lesser General Public
  9. * License as published by the Free Software Foundation; either
  10. * version 2.1 of the License, or (at your option) any later version.
  11. *
  12. * This library is distributed in the hope that it will be useful,
  13. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  15. * Lesser General Public License for more details.
  16. *
  17. * You should have received a copy of the GNU Lesser General Public
  18. * License along with this library; if not, write to the Free Software
  19. * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
  20. *
  21. * @category PHPExcel
  22. * @package PHPExcel_Writer_Excel5
  23. * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  25. * @version ##VERSION##, ##DATE##
  26. */
  27. // Original file header of PEAR::Spreadsheet_Excel_Writer_Worksheet (used as the base for this class):
  28. // -----------------------------------------------------------------------------------------
  29. // /*
  30. // * Module written/ported by Xavier Noguer <xnoguer@rezebra.com>
  31. // *
  32. // * The majority of this is _NOT_ my code. I simply ported it from the
  33. // * PERL Spreadsheet::WriteExcel module.
  34. // *
  35. // * The author of the Spreadsheet::WriteExcel module is John McNamara
  36. // * <jmcnamara@cpan.org>
  37. // *
  38. // * I _DO_ maintain this code, and John McNamara has nothing to do with the
  39. // * porting of this code to PHP. Any questions directly related to this
  40. // * class library should be directed to me.
  41. // *
  42. // * License Information:
  43. // *
  44. // * Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets
  45. // * Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com
  46. // *
  47. // * This library is free software; you can redistribute it and/or
  48. // * modify it under the terms of the GNU Lesser General Public
  49. // * License as published by the Free Software Foundation; either
  50. // * version 2.1 of the License, or (at your option) any later version.
  51. // *
  52. // * This library is distributed in the hope that it will be useful,
  53. // * but WITHOUT ANY WARRANTY; without even the implied warranty of
  54. // * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  55. // * Lesser General Public License for more details.
  56. // *
  57. // * You should have received a copy of the GNU Lesser General Public
  58. // * License along with this library; if not, write to the Free Software
  59. // * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
  60. // */
  61. /**
  62. * PHPExcel_Writer_Excel5_Worksheet
  63. *
  64. * @category PHPExcel
  65. * @package PHPExcel_Writer_Excel5
  66. * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  67. */
  68. class PHPExcel_Writer_Excel5_Worksheet extends PHPExcel_Writer_Excel5_BIFFwriter
  69. {
  70. /**
  71. * Formula parser
  72. *
  73. * @var PHPExcel_Writer_Excel5_Parser
  74. */
  75. private $_parser;
  76. /**
  77. * Maximum number of characters for a string (LABEL record in BIFF5)
  78. * @var integer
  79. */
  80. public $_xls_strmax;
  81. /**
  82. * Array containing format information for columns
  83. * @var array
  84. */
  85. public $_colinfo;
  86. /**
  87. * Array containing the selected area for the worksheet
  88. * @var array
  89. */
  90. public $_selection;
  91. /**
  92. * The active pane for the worksheet
  93. * @var integer
  94. */
  95. public $_active_pane;
  96. /**
  97. * Whether to use outline.
  98. * @var integer
  99. */
  100. public $_outline_on;
  101. /**
  102. * Auto outline styles.
  103. * @var bool
  104. */
  105. public $_outline_style;
  106. /**
  107. * Whether to have outline summary below.
  108. * @var bool
  109. */
  110. public $_outline_below;
  111. /**
  112. * Whether to have outline summary at the right.
  113. * @var bool
  114. */
  115. public $_outline_right;
  116. /**
  117. * Reference to the total number of strings in the workbook
  118. * @var integer
  119. */
  120. public $_str_total;
  121. /**
  122. * Reference to the number of unique strings in the workbook
  123. * @var integer
  124. */
  125. public $_str_unique;
  126. /**
  127. * Reference to the array containing all the unique strings in the workbook
  128. * @var array
  129. */
  130. public $_str_table;
  131. /**
  132. * Color cache
  133. */
  134. private $_colors;
  135. /**
  136. * Index of first used row (at least 0)
  137. * @var int
  138. */
  139. private $_firstRowIndex;
  140. /**
  141. * Index of last used row. (no used rows means -1)
  142. * @var int
  143. */
  144. private $_lastRowIndex;
  145. /**
  146. * Index of first used column (at least 0)
  147. * @var int
  148. */
  149. private $_firstColumnIndex;
  150. /**
  151. * Index of last used column (no used columns means -1)
  152. * @var int
  153. */
  154. private $_lastColumnIndex;
  155. /**
  156. * Sheet object
  157. * @var PHPExcel_Worksheet
  158. */
  159. public $_phpSheet;
  160. /**
  161. * Count cell style Xfs
  162. *
  163. * @var int
  164. */
  165. private $_countCellStyleXfs;
  166. /**
  167. * Escher object corresponding to MSODRAWING
  168. *
  169. * @var PHPExcel_Shared_Escher
  170. */
  171. private $_escher;
  172. /**
  173. * Array of font hashes associated to FONT records index
  174. *
  175. * @var array
  176. */
  177. public $_fntHashIndex;
  178. /**
  179. * Constructor
  180. *
  181. * @param int &$str_total Total number of strings
  182. * @param int &$str_unique Total number of unique strings
  183. * @param array &$str_table String Table
  184. * @param array &$colors Colour Table
  185. * @param mixed $parser The formula parser created for the Workbook
  186. * @param boolean $preCalculateFormulas Flag indicating whether formulas should be calculated or just written
  187. * @param string $phpSheet The worksheet to write
  188. * @param PHPExcel_Worksheet $phpSheet
  189. */
  190. public function __construct(&$str_total, &$str_unique, &$str_table, &$colors,
  191. $parser, $preCalculateFormulas, $phpSheet)
  192. {
  193. // It needs to call its parent's constructor explicitly
  194. parent::__construct();
  195. // change BIFFwriter limit for CONTINUE records
  196. // $this->_limit = 8224;
  197. $this->_preCalculateFormulas = $preCalculateFormulas;
  198. $this->_str_total = &$str_total;
  199. $this->_str_unique = &$str_unique;
  200. $this->_str_table = &$str_table;
  201. $this->_colors = &$colors;
  202. $this->_parser = $parser;
  203. $this->_phpSheet = $phpSheet;
  204. //$this->ext_sheets = array();
  205. //$this->offset = 0;
  206. $this->_xls_strmax = 255;
  207. $this->_colinfo = array();
  208. $this->_selection = array(0,0,0,0);
  209. $this->_active_pane = 3;
  210. $this->_print_headers = 0;
  211. $this->_outline_style = 0;
  212. $this->_outline_below = 1;
  213. $this->_outline_right = 1;
  214. $this->_outline_on = 1;
  215. $this->_fntHashIndex = array();
  216. // calculate values for DIMENSIONS record
  217. $minR = 1;
  218. $minC = 'A';
  219. $maxR = $this->_phpSheet->getHighestRow();
  220. $maxC = $this->_phpSheet->getHighestColumn();
  221. // Determine lowest and highest column and row
  222. // $this->_firstRowIndex = ($minR > 65535) ? 65535 : $minR;
  223. $this->_lastRowIndex = ($maxR > 65535) ? 65535 : $maxR ;
  224. $this->_firstColumnIndex = PHPExcel_Cell::columnIndexFromString($minC);
  225. $this->_lastColumnIndex = PHPExcel_Cell::columnIndexFromString($maxC);
  226. // if ($this->_firstColumnIndex > 255) $this->_firstColumnIndex = 255;
  227. if ($this->_lastColumnIndex > 255) $this->_lastColumnIndex = 255;
  228. $this->_countCellStyleXfs = count($phpSheet->getParent()->getCellStyleXfCollection());
  229. }
  230. /**
  231. * Add data to the beginning of the workbook (note the reverse order)
  232. * and to the end of the workbook.
  233. *
  234. * @access public
  235. * @see PHPExcel_Writer_Excel5_Workbook::storeWorkbook()
  236. */
  237. function close()
  238. {
  239. $_phpSheet = $this->_phpSheet;
  240. $num_sheets = $_phpSheet->getParent()->getSheetCount();
  241. // Write BOF record
  242. $this->_storeBof(0x0010);
  243. // Write PRINTHEADERS
  244. $this->_writePrintHeaders();
  245. // Write PRINTGRIDLINES
  246. $this->_writePrintGridlines();
  247. // Write GRIDSET
  248. $this->_writeGridset();
  249. // Calculate column widths
  250. $_phpSheet->calculateColumnWidths();
  251. // Column dimensions
  252. if (($defaultWidth = $_phpSheet->getDefaultColumnDimension()->getWidth()) < 0) {
  253. $defaultWidth = PHPExcel_Shared_Font::getDefaultColumnWidthByFont($_phpSheet->getParent()->getDefaultStyle()->getFont());
  254. }
  255. $columnDimensions = $_phpSheet->getColumnDimensions();
  256. $maxCol = $this->_lastColumnIndex -1;
  257. for ($i = 0; $i <= $maxCol; ++$i) {
  258. $hidden = 0;
  259. $level = 0;
  260. $xfIndex = 15; // there are 15 cell style Xfs
  261. $width = $defaultWidth;
  262. $columnLetter = PHPExcel_Cell::stringFromColumnIndex($i);
  263. if (isset($columnDimensions[$columnLetter])) {
  264. $columnDimension = $columnDimensions[$columnLetter];
  265. if ($columnDimension->getWidth() >= 0) {
  266. $width = $columnDimension->getWidth();
  267. }
  268. $hidden = $columnDimension->getVisible() ? 0 : 1;
  269. $level = $columnDimension->getOutlineLevel();
  270. $xfIndex = $columnDimension->getXfIndex() + 15; // there are 15 cell style Xfs
  271. }
  272. // Components of _colinfo:
  273. // $firstcol first column on the range
  274. // $lastcol last column on the range
  275. // $width width to set
  276. // $xfIndex The optional cell style Xf index to apply to the columns
  277. // $hidden The optional hidden atribute
  278. // $level The optional outline level
  279. $this->_colinfo[] = array($i, $i, $width, $xfIndex, $hidden, $level);
  280. }
  281. // Write GUTS
  282. $this->_writeGuts();
  283. // Write DEFAULTROWHEIGHT
  284. $this->_writeDefaultRowHeight();
  285. // Write WSBOOL
  286. $this->_writeWsbool();
  287. // Write horizontal and vertical page breaks
  288. $this->_writeBreaks();
  289. // Write page header
  290. $this->_writeHeader();
  291. // Write page footer
  292. $this->_writeFooter();
  293. // Write page horizontal centering
  294. $this->_writeHcenter();
  295. // Write page vertical centering
  296. $this->_writeVcenter();
  297. // Write left margin
  298. $this->_writeMarginLeft();
  299. // Write right margin
  300. $this->_writeMarginRight();
  301. // Write top margin
  302. $this->_writeMarginTop();
  303. // Write bottom margin
  304. $this->_writeMarginBottom();
  305. // Write page setup
  306. $this->_writeSetup();
  307. // Write sheet protection
  308. $this->_writeProtect();
  309. // Write SCENPROTECT
  310. $this->_writeScenProtect();
  311. // Write OBJECTPROTECT
  312. $this->_writeObjectProtect();
  313. // Write sheet password
  314. $this->_writePassword();
  315. // Write DEFCOLWIDTH record
  316. $this->_writeDefcol();
  317. // Write the COLINFO records if they exist
  318. if (!empty($this->_colinfo)) {
  319. $colcount = count($this->_colinfo);
  320. for ($i = 0; $i < $colcount; ++$i) {
  321. $this->_writeColinfo($this->_colinfo[$i]);
  322. }
  323. }
  324. $autoFilterRange = $_phpSheet->getAutoFilter()->getRange();
  325. if (!empty($autoFilterRange)) {
  326. // Write AUTOFILTERINFO
  327. $this->_writeAutoFilterInfo();
  328. }
  329. // Write sheet dimensions
  330. $this->_writeDimensions();
  331. // Row dimensions
  332. foreach ($_phpSheet->getRowDimensions() as $rowDimension) {
  333. $xfIndex = $rowDimension->getXfIndex() + 15; // there are 15 cellXfs
  334. $this->_writeRow( $rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), $xfIndex, ($rowDimension->getVisible() ? '0' : '1'), $rowDimension->getOutlineLevel() );
  335. }
  336. // Write Cells
  337. foreach ($_phpSheet->getCellCollection() as $cellID) {
  338. $cell = $_phpSheet->getCell($cellID);
  339. $row = $cell->getRow() - 1;
  340. $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
  341. // Don't break Excel!
  342. // if ($row + 1 > 65536 or $column + 1 > 256) {
  343. if ($row > 65535 || $column > 255) {
  344. break;
  345. }
  346. // Write cell value
  347. $xfIndex = $cell->getXfIndex() + 15; // there are 15 cell style Xfs
  348. $cVal = $cell->getValue();
  349. if ($cVal instanceof PHPExcel_RichText) {
  350. // $this->_writeString($row, $column, $cVal->getPlainText(), $xfIndex);
  351. $arrcRun = array();
  352. $str_len = PHPExcel_Shared_String::CountCharacters($cVal->getPlainText(), 'UTF-8');
  353. $str_pos = 0;
  354. $elements = $cVal->getRichTextElements();
  355. foreach ($elements as $element) {
  356. // FONT Index
  357. if ($element instanceof PHPExcel_RichText_Run) {
  358. $str_fontidx = $this->_fntHashIndex[$element->getFont()->getHashCode()];
  359. }
  360. else {
  361. $str_fontidx = 0;
  362. }
  363. $arrcRun[] = array('strlen' => $str_pos, 'fontidx' => $str_fontidx);
  364. // Position FROM
  365. $str_pos += PHPExcel_Shared_String::CountCharacters($element->getText(), 'UTF-8');
  366. }
  367. $this->_writeRichTextString($row, $column, $cVal->getPlainText(), $xfIndex, $arrcRun);
  368. } else {
  369. switch ($cell->getDatatype()) {
  370. case PHPExcel_Cell_DataType::TYPE_STRING:
  371. case PHPExcel_Cell_DataType::TYPE_NULL:
  372. if ($cVal === '' || $cVal === null) {
  373. $this->_writeBlank($row, $column, $xfIndex);
  374. } else {
  375. $this->_writeString($row, $column, $cVal, $xfIndex);
  376. }
  377. break;
  378. case PHPExcel_Cell_DataType::TYPE_NUMERIC:
  379. $this->_writeNumber($row, $column, $cVal, $xfIndex);
  380. break;
  381. case PHPExcel_Cell_DataType::TYPE_FORMULA:
  382. $calculatedValue = $this->_preCalculateFormulas ?
  383. $cell->getCalculatedValue() : null;
  384. $this->_writeFormula($row, $column, $cVal, $xfIndex, $calculatedValue);
  385. break;
  386. case PHPExcel_Cell_DataType::TYPE_BOOL:
  387. $this->_writeBoolErr($row, $column, $cVal, 0, $xfIndex);
  388. break;
  389. case PHPExcel_Cell_DataType::TYPE_ERROR:
  390. $this->_writeBoolErr($row, $column, self::_mapErrorCode($cVal), 1, $xfIndex);
  391. break;
  392. }
  393. }
  394. }
  395. // Append
  396. $this->_writeMsoDrawing();
  397. // Write WINDOW2 record
  398. $this->_writeWindow2();
  399. // Write PLV record
  400. $this->_writePageLayoutView();
  401. // Write ZOOM record
  402. $this->_writeZoom();
  403. if ($_phpSheet->getFreezePane()) {
  404. $this->_writePanes();
  405. }
  406. // Write SELECTION record
  407. $this->_writeSelection();
  408. // Write MergedCellsTable Record
  409. $this->_writeMergedCells();
  410. // Hyperlinks
  411. foreach ($_phpSheet->getHyperLinkCollection() as $coordinate => $hyperlink) {
  412. list($column, $row) = PHPExcel_Cell::coordinateFromString($coordinate);
  413. $url = $hyperlink->getUrl();
  414. if ( strpos($url, 'sheet://') !== false ) {
  415. // internal to current workbook
  416. $url = str_replace('sheet://', 'internal:', $url);
  417. } else if ( preg_match('/^(http:|https:|ftp:|mailto:)/', $url) ) {
  418. // URL
  419. // $url = $url;
  420. } else {
  421. // external (local file)
  422. $url = 'external:' . $url;
  423. }
  424. $this->_writeUrl($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1, $url);
  425. }
  426. $this->_writeDataValidity();
  427. $this->_writeSheetLayout();
  428. // Write SHEETPROTECTION record
  429. $this->_writeSheetProtection();
  430. $this->_writeRangeProtection();
  431. $arrConditionalStyles = $_phpSheet->getConditionalStylesCollection();
  432. if(!empty($arrConditionalStyles)){
  433. $arrConditional = array();
  434. // @todo CFRule & CFHeader
  435. // Write CFHEADER record
  436. $this->_writeCFHeader();
  437. // Write ConditionalFormattingTable records
  438. foreach ($arrConditionalStyles as $cellCoordinate => $conditionalStyles) {
  439. foreach ($conditionalStyles as $conditional) {
  440. if($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_EXPRESSION
  441. || $conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CELLIS){
  442. if(!in_array($conditional->getHashCode(), $arrConditional)){
  443. $arrConditional[] = $conditional->getHashCode();
  444. // Write CFRULE record
  445. $this->_writeCFRule($conditional);
  446. }
  447. }
  448. }
  449. }
  450. }
  451. $this->_storeEof();
  452. }
  453. /**
  454. * Write a cell range address in BIFF8
  455. * always fixed range
  456. * See section 2.5.14 in OpenOffice.org's Documentation of the Microsoft Excel File Format
  457. *
  458. * @param string $range E.g. 'A1' or 'A1:B6'
  459. * @return string Binary data
  460. */
  461. private function _writeBIFF8CellRangeAddressFixed($range = 'A1')
  462. {
  463. $explodes = explode(':', $range);
  464. // extract first cell, e.g. 'A1'
  465. $firstCell = $explodes[0];
  466. // extract last cell, e.g. 'B6'
  467. if (count($explodes) == 1) {
  468. $lastCell = $firstCell;
  469. } else {
  470. $lastCell = $explodes[1];
  471. }
  472. $firstCellCoordinates = PHPExcel_Cell::coordinateFromString($firstCell); // e.g. array(0, 1)
  473. $lastCellCoordinates = PHPExcel_Cell::coordinateFromString($lastCell); // e.g. array(1, 6)
  474. return(pack('vvvv',
  475. $firstCellCoordinates[1] - 1,
  476. $lastCellCoordinates[1] - 1,
  477. PHPExcel_Cell::columnIndexFromString($firstCellCoordinates[0]) - 1,
  478. PHPExcel_Cell::columnIndexFromString($lastCellCoordinates[0]) - 1
  479. ));
  480. }
  481. /**
  482. * Retrieves data from memory in one chunk, or from disk in $buffer
  483. * sized chunks.
  484. *
  485. * @return string The data
  486. */
  487. function getData()
  488. {
  489. $buffer = 4096;
  490. // Return data stored in memory
  491. if (isset($this->_data)) {
  492. $tmp = $this->_data;
  493. unset($this->_data);
  494. return $tmp;
  495. }
  496. // No data to return
  497. return false;
  498. }
  499. /**
  500. * Set the option to print the row and column headers on the printed page.
  501. *
  502. * @access public
  503. * @param integer $print Whether to print the headers or not. Defaults to 1 (print).
  504. */
  505. function printRowColHeaders($print = 1)
  506. {
  507. $this->_print_headers = $print;
  508. }
  509. /**
  510. * This method sets the properties for outlining and grouping. The defaults
  511. * correspond to Excel's defaults.
  512. *
  513. * @param bool $visible
  514. * @param bool $symbols_below
  515. * @param bool $symbols_right
  516. * @param bool $auto_style
  517. */
  518. function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false)
  519. {
  520. $this->_outline_on = $visible;
  521. $this->_outline_below = $symbols_below;
  522. $this->_outline_right = $symbols_right;
  523. $this->_outline_style = $auto_style;
  524. // Ensure this is a boolean vale for Window2
  525. if ($this->_outline_on) {
  526. $this->_outline_on = 1;
  527. }
  528. }
  529. /**
  530. * Write a double to the specified row and column (zero indexed).
  531. * An integer can be written as a double. Excel will display an
  532. * integer. $format is optional.
  533. *
  534. * Returns 0 : normal termination
  535. * -2 : row or column out of range
  536. *
  537. * @param integer $row Zero indexed row
  538. * @param integer $col Zero indexed column
  539. * @param float $num The number to write
  540. * @param mixed $xfIndex The optional XF format
  541. * @return integer
  542. */
  543. private function _writeNumber($row, $col, $num, $xfIndex)
  544. {
  545. $record = 0x0203; // Record identifier
  546. $length = 0x000E; // Number of bytes to follow
  547. $header = pack("vv", $record, $length);
  548. $data = pack("vvv", $row, $col, $xfIndex);
  549. $xl_double = pack("d", $num);
  550. if (self::getByteOrder()) { // if it's Big Endian
  551. $xl_double = strrev($xl_double);
  552. }
  553. $this->_append($header.$data.$xl_double);
  554. return(0);
  555. }
  556. /**
  557. * Write a LABELSST record or a LABEL record. Which one depends on BIFF version
  558. *
  559. * @param int $row Row index (0-based)
  560. * @param int $col Column index (0-based)
  561. * @param string $str The string
  562. * @param int $xfIndex Index to XF record
  563. */
  564. private function _writeString($row, $col, $str, $xfIndex)
  565. {
  566. $this->_writeLabelSst($row, $col, $str, $xfIndex);
  567. }
  568. /**
  569. * Write a LABELSST record or a LABEL record. Which one depends on BIFF version
  570. * It differs from _writeString by the writing of rich text strings.
  571. * @param int $row Row index (0-based)
  572. * @param int $col Column index (0-based)
  573. * @param string $str The string
  574. * @param mixed $xfIndex The XF format index for the cell
  575. * @param array $arrcRun Index to Font record and characters beginning
  576. */
  577. private function _writeRichTextString($row, $col, $str, $xfIndex, $arrcRun){
  578. $record = 0x00FD; // Record identifier
  579. $length = 0x000A; // Bytes to follow
  580. $str = PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($str, $arrcRun);
  581. /* check if string is already present */
  582. if (!isset($this->_str_table[$str])) {
  583. $this->_str_table[$str] = $this->_str_unique++;
  584. }
  585. $this->_str_total++;
  586. $header = pack('vv', $record, $length);
  587. $data = pack('vvvV', $row, $col, $xfIndex, $this->_str_table[$str]);
  588. $this->_append($header.$data);
  589. }
  590. /**
  591. * Write a string to the specified row and column (zero indexed).
  592. * NOTE: there is an Excel 5 defined limit of 255 characters.
  593. * $format is optional.
  594. * Returns 0 : normal termination
  595. * -2 : row or column out of range
  596. * -3 : long string truncated to 255 chars
  597. *
  598. * @access public
  599. * @param integer $row Zero indexed row
  600. * @param integer $col Zero indexed column
  601. * @param string $str The string to write
  602. * @param mixed $xfIndex The XF format index for the cell
  603. * @return integer
  604. */
  605. private function _writeLabel($row, $col, $str, $xfIndex)
  606. {
  607. $strlen = strlen($str);
  608. $record = 0x0204; // Record identifier
  609. $length = 0x0008 + $strlen; // Bytes to follow
  610. $str_error = 0;
  611. if ($strlen > $this->_xls_strmax) { // LABEL must be < 255 chars
  612. $str = substr($str, 0, $this->_xls_strmax);
  613. $length = 0x0008 + $this->_xls_strmax;
  614. $strlen = $this->_xls_strmax;
  615. $str_error = -3;
  616. }
  617. $header = pack("vv", $record, $length);
  618. $data = pack("vvvv", $row, $col, $xfIndex, $strlen);
  619. $this->_append($header . $data . $str);
  620. return($str_error);
  621. }
  622. /**
  623. * Write a string to the specified row and column (zero indexed).
  624. * This is the BIFF8 version (no 255 chars limit).
  625. * $format is optional.
  626. * Returns 0 : normal termination
  627. * -2 : row or column out of range
  628. * -3 : long string truncated to 255 chars
  629. *
  630. * @access public
  631. * @param integer $row Zero indexed row
  632. * @param integer $col Zero indexed column
  633. * @param string $str The string to write
  634. * @param mixed $xfIndex The XF format index for the cell
  635. * @return integer
  636. */
  637. private function _writeLabelSst($row, $col, $str, $xfIndex)
  638. {
  639. $record = 0x00FD; // Record identifier
  640. $length = 0x000A; // Bytes to follow
  641. $str = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($str);
  642. /* check if string is already present */
  643. if (!isset($this->_str_table[$str])) {
  644. $this->_str_table[$str] = $this->_str_unique++;
  645. }
  646. $this->_str_total++;
  647. $header = pack('vv', $record, $length);
  648. $data = pack('vvvV', $row, $col, $xfIndex, $this->_str_table[$str]);
  649. $this->_append($header.$data);
  650. }
  651. /**
  652. * Writes a note associated with the cell given by the row and column.
  653. * NOTE records don't have a length limit.
  654. *
  655. * @param integer $row Zero indexed row
  656. * @param integer $col Zero indexed column
  657. * @param string $note The note to write
  658. */
  659. private function _writeNote($row, $col, $note)
  660. {
  661. $note_length = strlen($note);
  662. $record = 0x001C; // Record identifier
  663. $max_length = 2048; // Maximun length for a NOTE record
  664. // Length for this record is no more than 2048 + 6
  665. $length = 0x0006 + min($note_length, 2048);
  666. $header = pack("vv", $record, $length);
  667. $data = pack("vvv", $row, $col, $note_length);
  668. $this->_append($header . $data . substr($note, 0, 2048));
  669. for ($i = $max_length; $i < $note_length; $i += $max_length) {
  670. $chunk = substr($note, $i, $max_length);
  671. $length = 0x0006 + strlen($chunk);
  672. $header = pack("vv", $record, $length);
  673. $data = pack("vvv", -1, 0, strlen($chunk));
  674. $this->_append($header.$data.$chunk);
  675. }
  676. return(0);
  677. }
  678. /**
  679. * Write a blank cell to the specified row and column (zero indexed).
  680. * A blank cell is used to specify formatting without adding a string
  681. * or a number.
  682. *
  683. * A blank cell without a format serves no purpose. Therefore, we don't write
  684. * a BLANK record unless a format is specified.
  685. *
  686. * Returns 0 : normal termination (including no format)
  687. * -1 : insufficient number of arguments
  688. * -2 : row or column out of range
  689. *
  690. * @param integer $row Zero indexed row
  691. * @param integer $col Zero indexed column
  692. * @param mixed $xfIndex The XF format index
  693. */
  694. function _writeBlank($row, $col, $xfIndex)
  695. {
  696. $record = 0x0201; // Record identifier
  697. $length = 0x0006; // Number of bytes to follow
  698. $header = pack("vv", $record, $length);
  699. $data = pack("vvv", $row, $col, $xfIndex);
  700. $this->_append($header . $data);
  701. return 0;
  702. }
  703. /**
  704. * Write a boolean or an error type to the specified row and column (zero indexed)
  705. *
  706. * @param int $row Row index (0-based)
  707. * @param int $col Column index (0-based)
  708. * @param int $value
  709. * @param boolean $isError Error or Boolean?
  710. * @param int $xfIndex
  711. */
  712. private function _writeBoolErr($row, $col, $value, $isError, $xfIndex)
  713. {
  714. $record = 0x0205;
  715. $length = 8;
  716. $header = pack("vv", $record, $length);
  717. $data = pack("vvvCC", $row, $col, $xfIndex, $value, $isError);
  718. $this->_append($header . $data);
  719. return 0;
  720. }
  721. /**
  722. * Write a formula to the specified row and column (zero indexed).
  723. * The textual representation of the formula is passed to the parser in
  724. * Parser.php which returns a packed binary string.
  725. *
  726. * Returns 0 : normal termination
  727. * -1 : formula errors (bad formula)
  728. * -2 : row or column out of range
  729. *
  730. * @param integer $row Zero indexed row
  731. * @param integer $col Zero indexed column
  732. * @param string $formula The formula text string
  733. * @param mixed $xfIndex The XF format index
  734. * @param mixed $calculatedValue Calculated value
  735. * @return integer
  736. */
  737. private function _writeFormula($row, $col, $formula, $xfIndex, $calculatedValue)
  738. {
  739. $record = 0x0006; // Record identifier
  740. // Initialize possible additional value for STRING record that should be written after the FORMULA record?
  741. $stringValue = null;
  742. // calculated value
  743. if (isset($calculatedValue)) {
  744. // Since we can't yet get the data type of the calculated value,
  745. // we use best effort to determine data type
  746. if (is_bool($calculatedValue)) {
  747. // Boolean value
  748. $num = pack('CCCvCv', 0x01, 0x00, (int)$calculatedValue, 0x00, 0x00, 0xFFFF);
  749. } elseif (is_int($calculatedValue) || is_float($calculatedValue)) {
  750. // Numeric value
  751. $num = pack('d', $calculatedValue);
  752. } elseif (is_string($calculatedValue)) {
  753. if (array_key_exists($calculatedValue, PHPExcel_Cell_DataType::getErrorCodes())) {
  754. // Error value
  755. $num = pack('CCCvCv', 0x02, 0x00, self::_mapErrorCode($calculatedValue), 0x00, 0x00, 0xFFFF);
  756. } elseif ($calculatedValue === '') {
  757. // Empty string (and BIFF8)
  758. $num = pack('CCCvCv', 0x03, 0x00, 0x00, 0x00, 0x00, 0xFFFF);
  759. } else {
  760. // Non-empty string value (or empty string BIFF5)
  761. $stringValue = $calculatedValue;
  762. $num = pack('CCCvCv', 0x00, 0x00, 0x00, 0x00, 0x00, 0xFFFF);
  763. }
  764. } else {
  765. // We are really not supposed to reach here
  766. $num = pack('d', 0x00);
  767. }
  768. } else {
  769. $num = pack('d', 0x00);
  770. }
  771. $grbit = 0x03; // Option flags
  772. $unknown = 0x0000; // Must be zero
  773. // Strip the '=' or '@' sign at the beginning of the formula string
  774. if ($formula{0} == '=') {
  775. $formula = substr($formula,1);
  776. } else {
  777. // Error handling
  778. $this->_writeString($row, $col, 'Unrecognised character for formula');
  779. return -1;
  780. }
  781. // Parse the formula using the parser in Parser.php
  782. try {
  783. $error = $this->_parser->parse($formula);
  784. $formula = $this->_parser->toReversePolish();
  785. $formlen = strlen($formula); // Length of the binary string
  786. $length = 0x16 + $formlen; // Length of the record data
  787. $header = pack("vv", $record, $length);
  788. $data = pack("vvv", $row, $col, $xfIndex)
  789. . $num
  790. . pack("vVv", $grbit, $unknown, $formlen);
  791. $this->_append($header . $data . $formula);
  792. // Append also a STRING record if necessary
  793. if ($stringValue !== null) {
  794. $this->_writeStringRecord($stringValue);
  795. }
  796. return 0;
  797. } catch (PHPExcel_Exception $e) {
  798. // do nothing
  799. }
  800. }
  801. /**
  802. * Write a STRING record. This
  803. *
  804. * @param string $stringValue
  805. */
  806. private function _writeStringRecord($stringValue)
  807. {
  808. $record = 0x0207; // Record identifier
  809. $data = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($stringValue);
  810. $length = strlen($data);
  811. $header = pack('vv', $record, $length);
  812. $this->_append($header . $data);
  813. }
  814. /**
  815. * Write a hyperlink.
  816. * This is comprised of two elements: the visible label and
  817. * the invisible link. The visible label is the same as the link unless an
  818. * alternative string is specified. The label is written using the
  819. * _writeString() method. Therefore the 255 characters string limit applies.
  820. * $string and $format are optional.
  821. *
  822. * The hyperlink can be to a http, ftp, mail, internal sheet (not yet), or external
  823. * directory url.
  824. *
  825. * Returns 0 : normal termination
  826. * -2 : row or column out of range
  827. * -3 : long string truncated to 255 chars
  828. *
  829. * @param integer $row Row
  830. * @param integer $col Column
  831. * @param string $url URL string
  832. * @return integer
  833. */
  834. private function _writeUrl($row, $col, $url)
  835. {
  836. // Add start row and col to arg list
  837. return($this->_writeUrlRange($row, $col, $row, $col, $url));
  838. }
  839. /**
  840. * This is the more general form of _writeUrl(). It allows a hyperlink to be
  841. * written to a range of cells. This function also decides the type of hyperlink
  842. * to be written. These are either, Web (http, ftp, mailto), Internal
  843. * (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
  844. *
  845. * @access private
  846. * @see _writeUrl()
  847. * @param integer $row1 Start row
  848. * @param integer $col1 Start column
  849. * @param integer $row2 End row
  850. * @param integer $col2 End column
  851. * @param string $url URL string
  852. * @return integer
  853. */
  854. function _writeUrlRange($row1, $col1, $row2, $col2, $url)
  855. {
  856. // Check for internal/external sheet links or default to web link
  857. if (preg_match('[^internal:]', $url)) {
  858. return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url));
  859. }
  860. if (preg_match('[^external:]', $url)) {
  861. return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url));
  862. }
  863. return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url));
  864. }
  865. /**
  866. * Used to write http, ftp and mailto hyperlinks.
  867. * The link type ($options) is 0x03 is the same as absolute dir ref without
  868. * sheet. However it is differentiated by the $unknown2 data stream.
  869. *
  870. * @access private
  871. * @see _writeUrl()
  872. * @param integer $row1 Start row
  873. * @param integer $col1 Start column
  874. * @param integer $row2 End row
  875. * @param integer $col2 End column
  876. * @param string $url URL string
  877. * @return integer
  878. */
  879. function _writeUrlWeb($row1, $col1, $row2, $col2, $url)
  880. {
  881. $record = 0x01B8; // Record identifier
  882. $length = 0x00000; // Bytes to follow
  883. // Pack the undocumented parts of the hyperlink stream
  884. $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
  885. $unknown2 = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");
  886. // Pack the option flags
  887. $options = pack("V", 0x03);
  888. // Convert URL to a null terminated wchar string
  889. $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
  890. $url = $url . "\0\0\0";
  891. // Pack the length of the URL
  892. $url_len = pack("V", strlen($url));
  893. // Calculate the data length
  894. $length = 0x34 + strlen($url);
  895. // Pack the header data
  896. $header = pack("vv", $record, $length);
  897. $data = pack("vvvv", $row1, $row2, $col1, $col2);
  898. // Write the packed data
  899. $this->_append($header . $data .
  900. $unknown1 . $options .
  901. $unknown2 . $url_len . $url);
  902. return 0;
  903. }
  904. /**
  905. * Used to write internal reference hyperlinks such as "Sheet1!A1".
  906. *
  907. * @access private
  908. * @see _writeUrl()
  909. * @param integer $row1 Start row
  910. * @param integer $col1 Start column
  911. * @param integer $row2 End row
  912. * @param integer $col2 End column
  913. * @param string $url URL string
  914. * @return integer
  915. */
  916. function _writeUrlInternal($row1, $col1, $row2, $col2, $url)
  917. {
  918. $record = 0x01B8; // Record identifier
  919. $length = 0x00000; // Bytes to follow
  920. // Strip URL type
  921. $url = preg_replace('/^internal:/', '', $url);
  922. // Pack the undocumented parts of the hyperlink stream
  923. $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
  924. // Pack the option flags
  925. $options = pack("V", 0x08);
  926. // Convert the URL type and to a null terminated wchar string
  927. $url .= "\0";
  928. // character count
  929. $url_len = PHPExcel_Shared_String::CountCharacters($url);
  930. $url_len = pack('V', $url_len);
  931. $url = PHPExcel_Shared_String::ConvertEncoding($url, 'UTF-16LE', 'UTF-8');
  932. // Calculate the data length
  933. $length = 0x24 + strlen($url);
  934. // Pack the header data
  935. $header = pack("vv", $record, $length);
  936. $data = pack("vvvv", $row1, $row2, $col1, $col2);
  937. // Write the packed data
  938. $this->_append($header . $data .
  939. $unknown1 . $options .
  940. $url_len . $url);
  941. return 0;
  942. }
  943. /**
  944. * Write links to external directory names such as 'c:\foo.xls',
  945. * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
  946. *
  947. * Note: Excel writes some relative links with the $dir_long string. We ignore
  948. * these cases for the sake of simpler code.
  949. *
  950. * @access private
  951. * @see _writeUrl()
  952. * @param integer $row1 Start row
  953. * @param integer $col1 Start column
  954. * @param integer $row2 End row
  955. * @param integer $col2 End column
  956. * @param string $url URL string
  957. * @return integer
  958. */
  959. function _writeUrlExternal($row1, $col1, $row2, $col2, $url)
  960. {
  961. // Network drives are different. We will handle them separately
  962. // MS/Novell network drives and shares start with \\
  963. if (preg_match('[^external:\\\\]', $url)) {
  964. return; //($this->_writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format));
  965. }
  966. $record = 0x01B8; // Record identifier
  967. $length = 0x00000; // Bytes to follow
  968. // Strip URL type and change Unix dir separator to Dos style (if needed)
  969. //
  970. $url = preg_replace('/^external:/', '', $url);
  971. $url = preg_replace('/\//', "\\", $url);
  972. // Determine if the link is relative or absolute:
  973. // relative if link contains no dir separator, "somefile.xls"
  974. // relative if link starts with up-dir, "..\..\somefile.xls"
  975. // otherwise, absolute
  976. $absolute = 0x00; // relative path
  977. if ( preg_match('/^[A-Z]:/', $url) ) {
  978. $absolute = 0x02; // absolute path on Windows, e.g. C:\...
  979. }
  980. $link_type = 0x01 | $absolute;
  981. // Determine if the link contains a sheet reference and change some of the
  982. // parameters accordingly.
  983. // Split the dir name and sheet name (if it exists)
  984. $dir_long = $url;
  985. if (preg_match("/\#/", $url)) {
  986. $link_type |= 0x08;
  987. }
  988. // Pack the link type
  989. $link_type = pack("V", $link_type);
  990. // Calculate the up-level dir count e.g.. (..\..\..\ == 3)
  991. $up_count = preg_match_all("/\.\.\\\/", $dir_long, $useless);
  992. $up_count = pack("v", $up_count);
  993. // Store the short dos dir name (null terminated)
  994. $dir_short = preg_replace("/\.\.\\\/", '', $dir_long) . "\0";
  995. // Store the long dir name as a wchar string (non-null terminated)
  996. $dir_long = $dir_long . "\0";
  997. // Pack the lengths of the dir strings
  998. $dir_short_len = pack("V", strlen($dir_short) );
  999. $dir_long_len = pack("V", strlen($dir_long) );
  1000. $stream_len = pack("V", 0);//strlen($dir_long) + 0x06);
  1001. // Pack the undocumented parts of the hyperlink stream
  1002. $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000' );
  1003. $unknown2 = pack("H*",'0303000000000000C000000000000046' );
  1004. $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000');
  1005. $unknown4 = pack("v", 0x03 );
  1006. // Pack the main data stream
  1007. $data = pack("vvvv", $row1, $row2, $col1, $col2) .
  1008. $unknown1 .
  1009. $link_type .
  1010. $unknown2 .
  1011. $up_count .
  1012. $dir_short_len.
  1013. $dir_short .
  1014. $unknown3 .
  1015. $stream_len ;/*.
  1016. $dir_long_len .
  1017. $unknown4 .
  1018. $dir_long .
  1019. $sheet_len .
  1020. $sheet ;*/
  1021. // Pack the header data
  1022. $length = strlen($data);
  1023. $header = pack("vv", $record, $length);
  1024. // Write the packed data
  1025. $this->_append($header. $data);
  1026. return 0;
  1027. }
  1028. /**
  1029. * This method is used to set the height and format for a row.
  1030. *
  1031. * @param integer $row The row to set
  1032. * @param integer $height Height we are giving to the row.
  1033. * Use null to set XF without setting height
  1034. * @param integer $xfIndex The optional cell style Xf index to apply to the columns
  1035. * @param bool $hidden The optional hidden attribute
  1036. * @param integer $level The optional outline level for row, in range [0,7]
  1037. */
  1038. private function _writeRow($row, $height, $xfIndex, $hidden = false, $level = 0)
  1039. {
  1040. $record = 0x0208; // Record identifier
  1041. $length = 0x0010; // Number of bytes to follow
  1042. $colMic = 0x0000; // First defined column
  1043. $colMac = 0x0000; // Last defined column
  1044. $irwMac = 0x0000; // Used by Excel to optimise loading
  1045. $reserved = 0x0000; // Reserved
  1046. $grbit = 0x0000; // Option flags
  1047. $ixfe = $xfIndex;
  1048. if ( $height < 0 ){
  1049. $height = null;
  1050. }
  1051. // Use _writeRow($row, null, $XF) to set XF format without setting height
  1052. if ($height != null) {
  1053. $miyRw = $height * 20; // row height
  1054. } else {
  1055. $miyRw = 0xff; // default row height is 256
  1056. }
  1057. // Set the options flags. fUnsynced is used to show that the font and row
  1058. // heights are not compatible. This is usually the case for WriteExcel.
  1059. // The collapsed flag 0x10 doesn't seem to be used to indicate that a row
  1060. // is collapsed. Instead it is used to indicate that the previous row is
  1061. // collapsed. The zero height flag, 0x20, is used to collapse a row.
  1062. $grbit |= $level;
  1063. if ($hidden) {
  1064. $grbit |= 0x0030;
  1065. }
  1066. if ($height !== null) {
  1067. $grbit |= 0x0040; // fUnsynced
  1068. }
  1069. if ($xfIndex !== 0xF) {
  1070. $grbit |= 0x0080;
  1071. }
  1072. $grbit |= 0x0100;
  1073. $header = pack("vv", $record, $length);
  1074. $data = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw,
  1075. $irwMac,$reserved, $grbit, $ixfe);
  1076. $this->_append($header.$data);
  1077. }
  1078. /**
  1079. * Writes Excel DIMENSIONS to define the area in which there is data.
  1080. */
  1081. private function _writeDimensions()
  1082. {
  1083. $record = 0x0200; // Record identifier
  1084. $length = 0x000E;
  1085. $data = pack('VVvvv'
  1086. , $this->_firstRowIndex
  1087. , $this->_lastRowIndex + 1
  1088. , $this->_firstColumnIndex
  1089. , $this->_lastColumnIndex + 1
  1090. , 0x0000 // reserved
  1091. );
  1092. $header = pack("vv", $record, $length);
  1093. $this->_append($header.$data);
  1094. }
  1095. /**
  1096. * Write BIFF record Window2.
  1097. */
  1098. private function _writeWindow2()
  1099. {
  1100. $record = 0x023E; // Record identifier
  1101. $length = 0x0012;
  1102. $grbit = 0x00B6; // Option flags
  1103. $rwTop = 0x0000; // Top row visible in window
  1104. $colLeft = 0x0000; // Leftmost column visible in window
  1105. // The options flags that comprise $grbit
  1106. $fDspFmla = 0; // 0 - bit
  1107. $fDspGrid = $this->_phpSheet->getShowGridlines() ? 1 : 0; // 1
  1108. $fDspRwCol = $this->_phpSheet->getShowRowColHeaders() ? 1 : 0; // 2
  1109. $fFrozen = $this->_phpSheet->getFreezePane() ? 1 : 0; // 3
  1110. $fDspZeros = 1; // 4
  1111. $fDefaultHdr = 1; // 5
  1112. $fArabic = $this->_phpSheet->getRightToLeft() ? 1 : 0; // 6
  1113. $fDspGuts = $this->_outline_on; // 7
  1114. $fFrozenNoSplit = 0; // 0 - bit
  1115. // no support in PHPExcel for selected sheet, therefore sheet is only selected if it is the active sheet
  1116. $fSelected = ($this->_phpSheet === $this->_phpSheet->getParent()->getActiveSheet()) ? 1 : 0;
  1117. $fPaged = 1; // 2
  1118. $fPageBreakPreview = $this->_phpSheet->getSheetView()->getView() === PHPExcel_Worksheet_SheetView::SHEETVIEW_PAGE_BREAK_PREVIEW;
  1119. $grbit = $fDspFmla;
  1120. $grbit |= $fDspGrid << 1;
  1121. $grbit |= $fDspRwCol << 2;
  1122. $grbit |= $fFrozen << 3;
  1123. $grbit |= $fDspZeros << 4;
  1124. $grbit |= $fDefaultHdr << 5;
  1125. $grbit |= $fArabic << 6;
  1126. $grbit |= $fDspGuts << 7;
  1127. $grbit |= $fFrozenNoSplit << 8;
  1128. $grbit |= $fSelected << 9;
  1129. $grbit |= $fPaged << 10;
  1130. $grbit |= $fPageBreakPreview << 11;
  1131. $header = pack("vv", $record, $length);
  1132. $data = pack("vvv", $grbit, $rwTop, $colLeft);
  1133. // FIXME !!!
  1134. $rgbHdr = 0x0040; // Row/column heading and gridline color index
  1135. $zoom_factor_page_break = ($fPageBreakPreview? $this->_phpSheet->getSheetView()->getZoomScale() : 0x0000);
  1136. $zoom_factor_normal = $this->_phpSheet->getSheetView()->getZoomScaleNormal();
  1137. $data .= pack("vvvvV", $rgbHdr, 0x0000, $zoom_factor_page_break, $zoom_factor_normal, 0x00000000);
  1138. $this->_append($header.$data);
  1139. }
  1140. /**
  1141. * Write BIFF record DEFAULTROWHEIGHT.
  1142. */
  1143. private function _writeDefaultRowHeight()
  1144. {
  1145. $defaultRowHeight = $this->_phpSheet->getDefaultRowDimension()->getRowHeight();
  1146. if ($defaultRowHeight < 0) {
  1147. return;
  1148. }
  1149. // convert to twips
  1150. $defaultRowHeight = (int) 20 * $defaultRowHeight;
  1151. $record = 0x0225; // Record identifier
  1152. $length = 0x0004; // Number of bytes to follow
  1153. $header = pack("vv", $record, $length);
  1154. $data = pack("vv", 1, $defaultRowHeight);
  1155. $this->_append($header . $data);
  1156. }
  1157. /**
  1158. * Write BIFF record DEFCOLWIDTH if COLINFO records are in use.
  1159. */
  1160. private function _writeDefcol()
  1161. {
  1162. $defaultColWidth = 8;
  1163. $record = 0x0055; // Record identifier
  1164. $length = 0x0002; // Number of bytes to follow
  1165. $header = pack("vv", $record, $length);
  1166. $data = pack("v", $defaultColWidth);
  1167. $this->_append($header . $data);
  1168. }
  1169. /**
  1170. * Write BIFF record COLINFO to define column widths
  1171. *
  1172. * Note: The SDK says the record length is 0x0B but Excel writes a 0x0C
  1173. * length record.
  1174. *
  1175. * @param array $col_array This is the only parameter received and is composed of the following:
  1176. * 0 => First formatted column,
  1177. * 1 => Last formatted column,
  1178. * 2 => Col width (8.43 is Excel default),
  1179. * 3 => The optional XF format of the column,
  1180. * 4 => Option flags.
  1181. * 5 => Optional outline level
  1182. */
  1183. private function _writeColinfo($col_array)
  1184. {
  1185. if (isset($col_array[0])) {
  1186. $colFirst = $col_array[0];
  1187. }
  1188. if (isset($col_array[1])) {
  1189. $colLast = $col_array[1];
  1190. }
  1191. if (isset($col_array[2])) {
  1192. $coldx = $col_array[2];
  1193. } else {
  1194. $coldx = 8.43;
  1195. }
  1196. if (isset($col_array[3])) {
  1197. $xfIndex = $col_array[3];
  1198. } else {
  1199. $xfIndex = 15;
  1200. }
  1201. if (isset($col_array[4])) {
  1202. $grbit = $col_array[4];
  1203. } else {
  1204. $grbit = 0;
  1205. }
  1206. if (isset($col_array[5])) {
  1207. $level = $col_array[5];
  1208. } else {
  1209. $level = 0;
  1210. }
  1211. $record = 0x007D; // Record identifier
  1212. $length = 0x000C; // Number of bytes to follow
  1213. $coldx *= 256; // Convert to units of 1/256 of a char
  1214. $ixfe = $xfIndex;
  1215. $reserved = 0x0000; // Reserved
  1216. $level = max(0, min($level, 7));
  1217. $grbit |= $level << 8;
  1218. $header = pack("vv", $record, $length);
  1219. $data = pack("vvvvvv", $colFirst, $colLast, $coldx,
  1220. $ixfe, $grbit, $reserved);
  1221. $this->_append($header.$data);
  1222. }
  1223. /**
  1224. * Write BIFF record SELECTION.
  1225. */
  1226. private function _writeSelection()
  1227. {
  1228. // look up the selected cell range
  1229. $selectedCells = $this->_phpSheet->getSelectedCells();
  1230. $selectedCells = PHPExcel_Cell::splitRange($this->_phpSheet->getSelectedCells());
  1231. $selectedCells = $selectedCells[0];
  1232. if (count($selectedCells) == 2) {
  1233. list($first, $last) = $selectedCells;
  1234. } else {
  1235. $first = $selectedCells[0];
  1236. $last = $selectedCells[0];
  1237. }
  1238. list($colFirst, $rwFirst) = PHPExcel_Cell::coordinateFromString($first);
  1239. $colFirst = PHPExcel_Cell::columnIndexFromString($colFirst) - 1; // base 0 column index
  1240. --$rwFirst; // base 0 row index
  1241. list($colLast, $rwLast) = PHPExcel_Cell::coordinateFromString($last);
  1242. $colLast = PHPExcel_Cell::columnIndexFromString($colLast) - 1; // base 0 column index
  1243. --$rwLast; // base 0 row index
  1244. // make sure we are not out of bounds
  1245. $colFirst = min($colFirst, 255);
  1246. $colLast = min($colLast, 255);
  1247. $rwFirst = min($rwFirst, 65535);
  1248. $rwLast = min($rwLast, 65535);
  1249. $record = 0x001D; // Record identifier
  1250. $length = 0x000F; // Number of bytes to follow
  1251. $pnn = $this->_active_pane; // Pane position
  1252. $rwAct = $rwFirst; // Active row
  1253. $colAct = $colFirst; // Active column
  1254. $irefAct = 0; // Active cell ref
  1255. $cref = 1; // Number of refs
  1256. if (!isset($rwLast)) {
  1257. $rwLast = $rwFirst; // Last row in reference
  1258. }
  1259. if (!isset($colLast)) {
  1260. $colLast = $colFirst; // Last col in reference
  1261. }
  1262. // Swap last row/col for first row/col as necessary
  1263. if ($rwFirst > $rwLast) {
  1264. list($rwFirst, $rwLast) = array($rwLast, $rwFirst);
  1265. }
  1266. if ($colFirst > $colLast) {
  1267. list($colFirst, $colLast) = array($colLast, $colFirst);
  1268. }
  1269. $header = pack("vv", $record, $length);
  1270. $data = pack("CvvvvvvCC", $pnn, $rwAct, $colAct,
  1271. $irefAct, $cref,
  1272. $rwFirst, $rwLast,
  1273. $colFirst, $colLast);
  1274. $this->_append($header . $data);
  1275. }
  1276. /**
  1277. * Store the MERGEDCELLS records for all ranges of merged cells
  1278. */
  1279. private function _writeMergedCells()
  1280. {
  1281. $mergeCells = $this->_phpSheet->getMergeCells();
  1282. $countMergeCells = count($mergeCells);
  1283. if ($countMergeCells == 0) {
  1284. return;
  1285. }
  1286. // maximum allowed number of merged cells per record
  1287. $maxCountMergeCellsPerRecord = 1027;
  1288. // record identifier
  1289. $record = 0x00E5;
  1290. // counter for total number of merged cells treated so far by the writer
  1291. $i = 0;
  1292. // counter for number of merged cells written in record currently being written
  1293. $j = 0;
  1294. // initialize record data
  1295. $recordData = '';
  1296. // loop through the merged cells
  1297. foreach ($mergeCells as $mergeCell) {
  1298. ++$i;
  1299. ++$j;
  1300. // extract the row and column indexes
  1301. $range = PHPExcel_Cell::splitRange($mergeCell);
  1302. list($first, $last) = $range[0];
  1303. list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($first);
  1304. list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($last);
  1305. $recordData .= pack('vvvv', $firstRow - 1, $lastRow - 1, PHPExcel_Cell::columnIndexFromString($firstColumn) - 1, PHPExcel_Cell::columnIndexFromString($lastColumn) - 1);
  1306. // flush record if we have reached limit for number of merged cells, or reached final merged cell
  1307. if ($j == $maxCountMergeCellsPerRecord or $i == $countMergeCells) {
  1308. $recordData = pack('v', $j) . $recordData;
  1309. $length = strlen($recordData);
  1310. $header = pack('vv', $record, $length);
  1311. $this->_append($header . $recordData);
  1312. // initialize for next record, if any
  1313. $recordData = '';
  1314. $j = 0;
  1315. }
  1316. }
  1317. }
  1318. /**
  1319. * Write SHEETLAYOUT record
  1320. */
  1321. private function _writeSheetLayout()
  1322. {
  1323. if (!$this->_phpSheet->isTabColorSet()) {
  1324. return;
  1325. }
  1326. $recordData = pack(
  1327. 'vvVVVvv'
  1328. , 0x0862
  1329. , 0x0000 // unused
  1330. , 0x00000000 // unused
  1331. , 0x00000000 // unused
  1332. , 0x00000014 // size of record data
  1333. , $this->_colors[$this->_phpSheet->getTabColor()->getRGB()] // color index
  1334. , 0x0000 // unused
  1335. );
  1336. $length = strlen($recordData);
  1337. $record = 0x0862; // Record identifier
  1338. $header = pack('vv', $record, $length);
  1339. $this->_append($header . $recordData);
  1340. }
  1341. /**
  1342. * Write SHEETPROTECTION
  1343. */
  1344. private function _writeSheetProtection()
  1345. {
  1346. // record identifier
  1347. $record = 0x0867;
  1348. // prepare options
  1349. $options = (int) !$this->_phpSheet->getProtection()->getObjects()
  1350. | (int) !$this->_phpSheet->getProtection()->getScenarios() << 1
  1351. | (int) !$this->_phpSheet->getProtection()->getFormatCells() << 2
  1352. | (int) !$this->_phpSheet->getProtection()->getFormatColumns() << 3
  1353. | (int) !$this->_phpSheet->getProtection()->getFormatRows() << 4
  1354. | (int) !$this->_phpSheet->getProtection()->getInsertColumns() << 5
  1355. | (int) !$this->_phpSheet->getProtection()->getInsertRows() << 6
  1356. | (int) !$this->_phpSheet->getProtection()->getInsertHyperlinks() << 7
  1357. | (int) !$this->_phpSheet->getProtection()->getDeleteColumns() << 8
  1358. | (int) !$this->_phpSheet->getProtection()->getDeleteRows() << 9
  1359. | (int) !$this->_phpSheet->getProtection()->getSelectLockedCells() << 10
  1360. | (int) !$this->_phpSheet->getProtection()->getSort() << 11
  1361. | (int) !$this->_phpSheet->getProtection()->getAutoFilter() << 12
  1362. | (int) !$this->_phpSheet->getProtection()->getPivotTables() << 13
  1363. | (int) !$this->_phpSheet->getProtection()->getSelectUnlockedCells() << 14 ;
  1364. // record data
  1365. $recordData = pack(
  1366. 'vVVCVVvv'
  1367. , 0x0867 // repeated record identifier
  1368. , 0x0000 // not used
  1369. , 0x0000 // not used
  1370. , 0x00 // not used
  1371. , 0x01000200 // unknown data
  1372. , 0xFFFFFFFF // unknown data
  1373. , $options // options
  1374. , 0x0000 // not used
  1375. );
  1376. $length = strlen($recordData);
  1377. $header = pack('vv', $record, $length);
  1378. $this->_append($header . $recordData);
  1379. }
  1380. /**
  1381. * Write BIFF record RANGEPROTECTION
  1382. *
  1383. * Openoffice.org's Documentaion of the Microsoft Excel File Format uses term RANGEPROTECTION for these records
  1384. * Microsoft Office Excel 97-2007 Binary File Format Specification uses term FEAT for these records
  1385. */
  1386. private function _writeRangeProtection()
  1387. {
  1388. foreach ($this->_phpSheet->getProtectedCells() as $range => $password) {
  1389. // number of ranges, e.g. 'A1:B3 C20:D25'
  1390. $cellRanges = explode(' ', $range);
  1391. $cref = count($cellRanges);
  1392. $recordData = pack(
  1393. 'vvVVvCVvVv',
  1394. 0x0868,
  1395. 0x00,
  1396. 0x0000,
  1397. 0x0000,
  1398. 0x02,
  1399. 0x0,
  1400. 0x0000,
  1401. $cref,
  1402. 0x0000,
  1403. 0x00
  1404. );
  1405. foreach ($cellRanges as $cellRange) {
  1406. $recordData .= $this->_writeBIFF8CellRangeAddressFixed($cellRange);
  1407. }
  1408. // the rgbFeat structure
  1409. $recordData .= pack(
  1410. 'VV',
  1411. 0x0000,
  1412. hexdec($password)
  1413. );
  1414. $recordData .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong('p' . md5($recordData));
  1415. $length = strlen($recordData);
  1416. $record = 0x0868; // Record identifier
  1417. $header = pack("vv", $record, $length);
  1418. $this->_append($header . $recordData);
  1419. }
  1420. }
  1421. /**
  1422. * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
  1423. * references in a worksheet.
  1424. *
  1425. * Excel only stores references to external sheets that are used in formulas.
  1426. * For simplicity we store references to all the sheets in the workbook
  1427. * regardless of whether they are used or not. This reduces the overall
  1428. * complexity and eliminates the need for a two way dialogue between the formula
  1429. * parser the worksheet objects.
  1430. *
  1431. * @param integer $count The number of external sheet references in this worksheet
  1432. */
  1433. private function _writeExterncount($count)
  1434. {
  1435. $record = 0x0016; // Record identifier
  1436. $length = 0x0002; // Number of bytes to follow
  1437. $header = pack("vv", $record, $length);
  1438. $data = pack("v", $count);
  1439. $this->_append($header . $data);
  1440. }
  1441. /**
  1442. * Writes the Excel BIFF EXTERNSHEET record. These references are used by
  1443. * formulas. A formula references a sheet name via an index. Since we store a
  1444. * reference to all of the external worksheets the EXTERNSHEET index is the same
  1445. * as the worksheet index.
  1446. *
  1447. * @param string $sheetname The name of a external worksheet
  1448. */
  1449. private function _writeExternsheet($sheetname)
  1450. {
  1451. $record = 0x0017; // Record identifier
  1452. // References to the current sheet are encoded differently to references to
  1453. // external sheets.
  1454. //
  1455. if ($this->_phpSheet->getTitle() == $sheetname) {
  1456. $sheetname = '';
  1457. $length = 0x02; // The following 2 bytes
  1458. $cch = 1; // The following byte
  1459. $rgch = 0x02; // Self reference
  1460. } else {
  1461. $length = 0x02 + strlen($sheetname);
  1462. $cch = strlen($sheetname);
  1463. $rgch = 0x03; // Reference to a sheet in the current workbook
  1464. }
  1465. $header = pack("vv", $record, $length);
  1466. $data = pack("CC", $cch, $rgch);
  1467. $this->_append($header . $data . $sheetname);
  1468. }
  1469. /**
  1470. * Writes the Excel BIFF PANE record.
  1471. * The panes can either be frozen or thawed (unfrozen).
  1472. * Frozen panes are specified in terms of an integer number of rows and columns.
  1473. * Thawed panes are specified in terms of Excel's units for rows and columns.
  1474. */
  1475. private function _writePanes()
  1476. {
  1477. $panes = array();
  1478. if ($freezePane = $this->_phpSheet->getFreezePane()) {
  1479. list($column, $row) = PHPExcel_Cell::coordinateFromString($freezePane);
  1480. $panes[0] = $row - 1;
  1481. $panes[1] = PHPExcel_Cell::columnIndexFromString($column) - 1;
  1482. } else {
  1483. // thaw panes
  1484. return;
  1485. }
  1486. $y = isset($panes[0]) ? $panes[0] : null;
  1487. $x = isset($panes[1]) ? $panes[1] : null;
  1488. $rwTop = isset($panes[2]) ? $panes[2] : null;
  1489. $colLeft = isset($panes[3]) ? $panes[3] : null;
  1490. if (count($panes) > 4) { // if Active pane was received
  1491. $pnnAct = $panes[4];
  1492. } else {
  1493. $pnnAct = null;
  1494. }
  1495. $record = 0x0041; // Record identifier
  1496. $length = 0x000A; // Number of bytes to follow
  1497. // Code specific to frozen or thawed panes.
  1498. if ($this->_phpSheet->getFreezePane()) {
  1499. // Set default values for $rwTop and $colLeft
  1500. if (!isset($rwTop)) {
  1501. $rwTop = $y;
  1502. }
  1503. if (!isset($colLeft)) {
  1504. $colLeft = $x;
  1505. }
  1506. } else {
  1507. // Set default values for $rwTop and $colLeft
  1508. if (!isset($rwTop)) {
  1509. $rwTop = 0;
  1510. }
  1511. if (!isset($colLeft)) {
  1512. $colLeft = 0;
  1513. }
  1514. // Convert Excel's row and column units to the internal units.
  1515. // The default row height is 12.75
  1516. // The default column width is 8.43
  1517. // The following slope and intersection values were interpolated.
  1518. //
  1519. $y = 20*$y + 255;
  1520. $x = 113.879*$x + 390;
  1521. }
  1522. // Determine which pane should be active. There is also the undocumented
  1523. // option to override this should it be necessary: may be removed later.
  1524. //
  1525. if (!isset($pnnAct)) {
  1526. if ($x != 0 && $y != 0) {
  1527. $pnnAct = 0; // Bottom right
  1528. }
  1529. if ($x != 0 && $y == 0) {
  1530. $pnnAct = 1; // Top right
  1531. }
  1532. if ($x == 0 && $y != 0) {
  1533. $pnnAct = 2; // Bottom left
  1534. }
  1535. if ($x == 0 && $y == 0) {
  1536. $pnnAct = 3; // Top left
  1537. }
  1538. }
  1539. $this->_active_pane = $pnnAct; // Used in _writeSelection
  1540. $header = pack("vv", $record, $length);
  1541. $data = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct);
  1542. $this->_append($header . $data);
  1543. }
  1544. /**
  1545. * Store the page setup SETUP BIFF record.
  1546. */
  1547. private function _writeSetup()
  1548. {
  1549. $record = 0x00A1; // Record identifier
  1550. $length = 0x0022; // Number of bytes to follow
  1551. $iPaperSize = $this->_phpSheet->getPageSetup()->getPaperSize(); // Paper size
  1552. $iScale = $this->_phpSheet->getPageSetup()->getScale() ?
  1553. $this->_phpSheet->getPageSetup()->getScale() : 100; // Print scaling factor
  1554. $iPageStart = 0x01; // Starting page number
  1555. $iFitWidth = (int) $this->_phpSheet->getPageSetup()->getFitToWidth(); // Fit to number of pages wide
  1556. $iFitHeight = (int) $this->_phpSheet->getPageSetup()->getFitToHeight(); // Fit to number of pages high
  1557. $grbit = 0x00; // Option flags
  1558. $iRes = 0x0258; // Print resolution
  1559. $iVRes = 0x0258; // Vertical print resolution
  1560. $numHdr = $this->_phpSheet->getPageMargins()->getHeader(); // Header Margin
  1561. $numFtr = $this->_phpSheet->getPageMargins()->getFooter(); // Footer Margin
  1562. $iCopies = 0x01; // Number of copies
  1563. $fLeftToRight = 0x0; // Print over then down
  1564. // Page orientation
  1565. $fLandscape = ($this->_phpSheet->getPageSetup()->getOrientation() == PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE) ?
  1566. 0x0 : 0x1;
  1567. $fNoPls = 0x0; // Setup not read from printer
  1568. $fNoColor = 0x0; // Print black and white
  1569. $fDraft = 0x0; // Print draft quality
  1570. $fNotes = 0x0; // Print notes
  1571. $fNoOrient = 0x0; // Orientation not set
  1572. $fUsePage = 0x0; // Use custom starting page
  1573. $grbit = $fLeftToRight;
  1574. $grbit |= $fLandscape << 1;
  1575. $grbit |= $fNoPls << 2;
  1576. $grbit |= $fNoColor << 3;
  1577. $grbit |= $fDraft << 4;
  1578. $grbit |= $fNotes << 5;
  1579. $grbit |= $fNoOrient << 6;
  1580. $grbit |= $fUsePage << 7;
  1581. $numHdr = pack("d", $numHdr);
  1582. $numFtr = pack("d", $numFtr);
  1583. if (self::getByteOrder()) { // if it's Big Endian
  1584. $numHdr = strrev($numHdr);
  1585. $numFtr = strrev($numFtr);
  1586. }
  1587. $header = pack("vv", $record, $length);
  1588. $data1 = pack("vvvvvvvv", $iPaperSize,
  1589. $iScale,
  1590. $iPageStart,
  1591. $iFitWidth,
  1592. $iFitHeight,
  1593. $grbit,
  1594. $iRes,
  1595. $iVRes);
  1596. $data2 = $numHdr.$numFtr;
  1597. $data3 = pack("v", $iCopies);
  1598. $this->_append($header . $data1 . $data2 . $data3);
  1599. }
  1600. /**
  1601. * Store the header caption BIFF record.
  1602. */
  1603. private function _writeHeader()
  1604. {
  1605. $record = 0x0014; // Record identifier
  1606. /* removing for now
  1607. // need to fix character count (multibyte!)
  1608. if (strlen($this->_phpSheet->getHeaderFooter()->getOddHeader()) <= 255) {
  1609. $str = $this->_phpSheet->getHeaderFooter()->getOddHeader(); // header string
  1610. } else {
  1611. $str = '';
  1612. }
  1613. */
  1614. $recordData = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($this->_phpSheet->getHeaderFooter()->getOddHeader());
  1615. $length = strlen($recordData);
  1616. $header = pack("vv", $record, $length);
  1617. $this->_append($header . $recordData);
  1618. }
  1619. /**
  1620. * Store the footer caption BIFF record.
  1621. */
  1622. private function _writeFooter()
  1623. {
  1624. $record = 0x0015; // Record identifier
  1625. /* removing for now
  1626. // need to fix character count (multibyte!)
  1627. if (strlen($this->_phpSheet->getHeaderFooter()->getOddFooter()) <= 255) {
  1628. $str = $this->_phpSheet->getHeaderFooter()->getOddFooter();
  1629. } else {
  1630. $str = '';
  1631. }
  1632. */
  1633. $recordData = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($this->_phpSheet->getHeaderFooter()->getOddFooter());
  1634. $length = strlen($recordData);
  1635. $header = pack("vv", $record, $length);
  1636. $this->_append($header . $recordData);
  1637. }
  1638. /**
  1639. * Store the horizontal centering HCENTER BIFF record.
  1640. *
  1641. * @access private
  1642. */
  1643. private function _writeHcenter()
  1644. {
  1645. $record = 0x0083; // Record identifier
  1646. $length = 0x0002; // Bytes to follow
  1647. $fHCenter = $this->_phpSheet->getPageSetup()->getHorizontalCentered() ? 1 : 0; // Horizontal centering
  1648. $header = pack("vv", $record, $length);
  1649. $data = pack("v", $fHCenter);
  1650. $this->_append($header.$data);
  1651. }
  1652. /**
  1653. * Store the vertical centering VCENTER BIFF record.
  1654. */
  1655. private function _writeVcenter()
  1656. {
  1657. $record = 0x0084; // Record identifier
  1658. $length = 0x0002; // Bytes to follow
  1659. $fVCenter = $this->_phpSheet->getPageSetup()->getVerticalCentered() ? 1 : 0; // Horizontal centering
  1660. $header = pack("vv", $record, $length);
  1661. $data = pack("v", $fVCenter);
  1662. $this->_append($header . $data);
  1663. }
  1664. /**
  1665. * Store the LEFTMARGIN BIFF record.
  1666. */
  1667. private function _writeMarginLeft()
  1668. {
  1669. $record = 0x0026; // Record identifier
  1670. $length = 0x0008; // Bytes to follow
  1671. $margin = $this->_phpSheet->getPageMargins()->getLeft(); // Margin in inches
  1672. $header = pack("vv", $record, $length);
  1673. $data = pack("d", $margin);
  1674. if (self::getByteOrder()) { // if it's Big Endian
  1675. $data = strrev($data);
  1676. }
  1677. $this->_append($header . $data);
  1678. }
  1679. /**
  1680. * Store the RIGHTMARGIN BIFF record.
  1681. */
  1682. private function _writeMarginRight()
  1683. {
  1684. $record = 0x0027; // Record identifier
  1685. $length = 0x0008; // Bytes to follow
  1686. $margin = $this->_phpSheet->getPageMargins()->getRight(); // Margin in inches
  1687. $header = pack("vv", $record, $length);
  1688. $data = pack("d", $margin);
  1689. if (self::getByteOrder()) { // if it's Big Endian
  1690. $data = strrev($data);
  1691. }
  1692. $this->_append($header . $data);
  1693. }
  1694. /**
  1695. * Store the TOPMARGIN BIFF record.
  1696. */
  1697. private function _writeMarginTop()
  1698. {
  1699. $record = 0x0028; // Record identifier
  1700. $length = 0x0008; // Bytes to follow
  1701. $margin = $this->_phpSheet->getPageMargins()->getTop(); // Margin in inches
  1702. $header = pack("vv", $record, $length);
  1703. $data = pack("d", $margin);
  1704. if (self::getByteOrder()) { // if it's Big Endian
  1705. $data = strrev($data);
  1706. }
  1707. $this->_append($header . $data);
  1708. }
  1709. /**
  1710. * Store the BOTTOMMARGIN BIFF record.
  1711. */
  1712. private function _writeMarginBottom()
  1713. {
  1714. $record = 0x0029; // Record identifier
  1715. $length = 0x0008; // Bytes to follow
  1716. $margin = $this->_phpSheet->getPageMargins()->getBottom(); // Margin in inches
  1717. $header = pack("vv", $record, $length);
  1718. $data = pack("d", $margin);
  1719. if (self::getByteOrder()) { // if it's Big Endian
  1720. $data = strrev($data);
  1721. }
  1722. $this->_append($header . $data);
  1723. }
  1724. /**
  1725. * Write the PRINTHEADERS BIFF record.
  1726. */
  1727. private function _writePrintHeaders()
  1728. {
  1729. $record = 0x002a; // Record identifier
  1730. $length = 0x0002; // Bytes to follow
  1731. $fPrintRwCol = $this->_print_headers; // Boolean flag
  1732. $header = pack("vv", $record, $length);
  1733. $data = pack("v", $fPrintRwCol);
  1734. $this->_append($header . $data);
  1735. }
  1736. /**
  1737. * Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the
  1738. * GRIDSET record.
  1739. */
  1740. private function _writePrintGridlines()
  1741. {
  1742. $record = 0x002b; // Record identifier
  1743. $length = 0x0002; // Bytes to follow
  1744. $fPrintGrid = $this->_phpSheet->getPrintGridlines() ? 1 : 0; // Boolean flag
  1745. $header = pack("vv", $record, $length);
  1746. $data = pack("v", $fPrintGrid);
  1747. $this->_append($header . $data);
  1748. }
  1749. /**
  1750. * Write the GRIDSET BIFF record. Must be used in conjunction with the
  1751. * PRINTGRIDLINES record.
  1752. */
  1753. private function _writeGridset()
  1754. {
  1755. $record = 0x0082; // Record identifier
  1756. $length = 0x0002; // Bytes to follow
  1757. $fGridSet = !$this->_phpSheet->getPrintGridlines(); // Boolean flag
  1758. $header = pack("vv", $record, $length);
  1759. $data = pack("v", $fGridSet);
  1760. $this->_append($header . $data);
  1761. }
  1762. /**
  1763. * Write the AUTOFILTERINFO BIFF record. This is used to configure the number of autofilter select used in the sheet.
  1764. */
  1765. private function _writeAutoFilterInfo(){
  1766. $record = 0x009D; // Record identifier
  1767. $length = 0x0002; // Bytes to follow
  1768. $rangeBounds = PHPExcel_Cell::rangeBoundaries($this->_phpSheet->getAutoFilter()->getRange());
  1769. $iNumFilters = 1 + $rangeBounds[1][0] - $rangeBounds[0][0];
  1770. $header = pack("vv", $record, $length);
  1771. $data = pack("v", $iNumFilters);
  1772. $this->_append($header . $data);
  1773. }
  1774. /**
  1775. * Write the GUTS BIFF record. This is used to configure the gutter margins
  1776. * where Excel outline symbols are displayed. The visibility of the gutters is
  1777. * controlled by a flag in WSBOOL.
  1778. *
  1779. * @see _writeWsbool()
  1780. */
  1781. private function _writeGuts()
  1782. {
  1783. $record = 0x0080; // Record identifier
  1784. $length = 0x0008; // Bytes to follow
  1785. $dxRwGut = 0x0000; // Size of row gutter
  1786. $dxColGut = 0x0000; // Size of col gutter
  1787. // determine maximum row outline level
  1788. $maxRowOutlineLevel = 0;
  1789. foreach ($this->_phpSheet->getRowDimensions() as $rowDimension) {
  1790. $maxRowOutlineLevel = max($maxRowOutlineLevel, $rowDimension->getOutlineLevel());
  1791. }
  1792. $col_level = 0;
  1793. // Calculate the maximum column outline level. The equivalent calculation
  1794. // for the row outline level is carried out in _writeRow().
  1795. $colcount = count($this->_colinfo);
  1796. for ($i = 0; $i < $colcount; ++$i) {
  1797. $col_level = max($this->_colinfo[$i][5], $col_level);
  1798. }
  1799. // Set the limits for the outline levels (0 <= x <= 7).
  1800. $col_level = max(0, min($col_level, 7));
  1801. // The displayed level is one greater than the max outline levels
  1802. if ($maxRowOutlineLevel) {
  1803. ++$maxRowOutlineLevel;
  1804. }
  1805. if ($col_level) {
  1806. ++$col_level;
  1807. }
  1808. $header = pack("vv", $record, $length);
  1809. $data = pack("vvvv", $dxRwGut, $dxColGut, $maxRowOutlineLevel, $col_level);
  1810. $this->_append($header.$data);
  1811. }
  1812. /**
  1813. * Write the WSBOOL BIFF record, mainly for fit-to-page. Used in conjunction
  1814. * with the SETUP record.
  1815. */
  1816. private function _writeWsbool()
  1817. {
  1818. $record = 0x0081; // Record identifier
  1819. $length = 0x0002; // Bytes to follow
  1820. $grbit = 0x0000;
  1821. // The only option that is of interest is the flag for fit to page. So we
  1822. // set all the options in one go.
  1823. //
  1824. // Set the option flags
  1825. $grbit |= 0x0001; // Auto page breaks visible
  1826. if ($this->_outline_style) {
  1827. $grbit |= 0x0020; // Auto outline styles
  1828. }
  1829. if ($this->_phpSheet->getShowSummaryBelow()) {
  1830. $grbit |= 0x0040; // Outline summary below
  1831. }
  1832. if ($this->_phpSheet->getShowSummaryRight()) {
  1833. $grbit |= 0x0080; // Outline summary right
  1834. }
  1835. if ($this->_phpSheet->getPageSetup()->getFitToPage()) {
  1836. $grbit |= 0x0100; // Page setup fit to page
  1837. }
  1838. if ($this->_outline_on) {
  1839. $grbit |= 0x0400; // Outline symbols displayed
  1840. }
  1841. $header = pack("vv", $record, $length);
  1842. $data = pack("v", $grbit);
  1843. $this->_append($header . $data);
  1844. }
  1845. /**
  1846. * Write the HORIZONTALPAGEBREAKS and VERTICALPAGEBREAKS BIFF records.
  1847. */
  1848. private function _writeBreaks()
  1849. {
  1850. // initialize
  1851. $vbreaks = array();
  1852. $hbreaks = array();
  1853. foreach ($this->_phpSheet->getBreaks() as $cell => $breakType) {
  1854. // Fetch coordinates
  1855. $coordinates = PHPExcel_Cell::coordinateFromString($cell);
  1856. // Decide what to do by the type of break
  1857. switch ($breakType) {
  1858. case PHPExcel_Worksheet::BREAK_COLUMN:
  1859. // Add to list of vertical breaks
  1860. $vbreaks[] = PHPExcel_Cell::columnIndexFromString($coordinates[0]) - 1;
  1861. break;
  1862. case PHPExcel_Worksheet::BREAK_ROW:
  1863. // Add to list of horizontal breaks
  1864. $hbreaks[] = $coordinates[1];
  1865. break;
  1866. case PHPExcel_Worksheet::BREAK_NONE:
  1867. default:
  1868. // Nothing to do
  1869. break;
  1870. }
  1871. }
  1872. //horizontal page breaks
  1873. if (!empty($hbreaks)) {
  1874. // Sort and filter array of page breaks
  1875. sort($hbreaks, SORT_NUMERIC);
  1876. if ($hbreaks[0] == 0) { // don't use first break if it's 0
  1877. array_shift($hbreaks);
  1878. }
  1879. $record = 0x001b; // Record identifier
  1880. $cbrk = count($hbreaks); // Number of page breaks
  1881. $length = 2 + 6 * $cbrk; // Bytes to follow
  1882. $header = pack("vv", $record, $length);
  1883. $data = pack("v", $cbrk);
  1884. // Append each page break
  1885. foreach ($hbreaks as $hbreak) {
  1886. $data .= pack("vvv", $hbreak, 0x0000, 0x00ff);
  1887. }
  1888. $this->_append($header . $data);
  1889. }
  1890. // vertical page breaks
  1891. if (!empty($vbreaks)) {
  1892. // 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
  1893. // It is slightly higher in Excel 97/200, approx. 1026
  1894. $vbreaks = array_slice($vbreaks, 0, 1000);
  1895. // Sort and filter array of page breaks
  1896. sort($vbreaks, SORT_NUMERIC);
  1897. if ($vbreaks[0] == 0) { // don't use first break if it's 0
  1898. array_shift($vbreaks);
  1899. }
  1900. $record = 0x001a; // Record identifier
  1901. $cbrk = count($vbreaks); // Number of page breaks
  1902. $length = 2 + 6 * $cbrk; // Bytes to follow
  1903. $header = pack("vv", $record, $length);
  1904. $data = pack("v", $cbrk);
  1905. // Append each page break
  1906. foreach ($vbreaks as $vbreak) {
  1907. $data .= pack("vvv", $vbreak, 0x0000, 0xffff);
  1908. }
  1909. $this->_append($header . $data);
  1910. }
  1911. }
  1912. /**
  1913. * Set the Biff PROTECT record to indicate that the worksheet is protected.
  1914. */
  1915. private function _writeProtect()
  1916. {
  1917. // Exit unless sheet protection has been specified
  1918. if (!$this->_phpSheet->getProtection()->getSheet()) {
  1919. return;
  1920. }
  1921. $record = 0x0012; // Record identifier
  1922. $length = 0x0002; // Bytes to follow
  1923. $fLock = 1; // Worksheet is protected
  1924. $header = pack("vv", $record, $length);
  1925. $data = pack("v", $fLock);
  1926. $this->_append($header.$data);
  1927. }
  1928. /**
  1929. * Write SCENPROTECT
  1930. */
  1931. private function _writeScenProtect()
  1932. {
  1933. // Exit if sheet protection is not active
  1934. if (!$this->_phpSheet->getProtection()->getSheet()) {
  1935. return;
  1936. }
  1937. // Exit if scenarios are not protected
  1938. if (!$this->_phpSheet->getProtection()->getScenarios()) {
  1939. return;
  1940. }
  1941. $record = 0x00DD; // Record identifier
  1942. $length = 0x0002; // Bytes to follow
  1943. $header = pack('vv', $record, $length);
  1944. $data = pack('v', 1);
  1945. $this->_append($header . $data);
  1946. }
  1947. /**
  1948. * Write OBJECTPROTECT
  1949. */
  1950. private function _writeObjectProtect()
  1951. {
  1952. // Exit if sheet protection is not active
  1953. if (!$this->_phpSheet->getProtection()->getSheet()) {
  1954. return;
  1955. }
  1956. // Exit if objects are not protected
  1957. if (!$this->_phpSheet->getProtection()->getObjects()) {
  1958. return;
  1959. }
  1960. $record = 0x0063; // Record identifier
  1961. $length = 0x0002; // Bytes to follow
  1962. $header = pack('vv', $record, $length);
  1963. $data = pack('v', 1);
  1964. $this->_append($header . $data);
  1965. }
  1966. /**
  1967. * Write the worksheet PASSWORD record.
  1968. */
  1969. private function _writePassword()
  1970. {
  1971. // Exit unless sheet protection and password have been specified
  1972. if (!$this->_phpSheet->getProtection()->getSheet() || !$this->_phpSheet->getProtection()->getPassword()) {
  1973. return;
  1974. }
  1975. $record = 0x0013; // Record identifier
  1976. $length = 0x0002; // Bytes to follow
  1977. $wPassword = hexdec($this->_phpSheet->getProtection()->getPassword()); // Encoded password
  1978. $header = pack("vv", $record, $length);
  1979. $data = pack("v", $wPassword);
  1980. $this->_append($header . $data);
  1981. }
  1982. /**
  1983. * Insert a 24bit bitmap image in a worksheet.
  1984. *
  1985. * @access public
  1986. * @param integer $row The row we are going to insert the bitmap into
  1987. * @param integer $col The column we are going to insert the bitmap into
  1988. * @param mixed $bitmap The bitmap filename or GD-image resource
  1989. * @param integer $x The horizontal position (offset) of the image inside the cell.
  1990. * @param integer $y The vertical position (offset) of the image inside the cell.
  1991. * @param float $scale_x The horizontal scale
  1992. * @param float $scale_y The vertical scale
  1993. */
  1994. function insertBitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1)
  1995. {
  1996. $bitmap_array = (is_resource($bitmap) ? $this->_processBitmapGd($bitmap) : $this->_processBitmap($bitmap));
  1997. list($width, $height, $size, $data) = $bitmap_array; //$this->_processBitmap($bitmap);
  1998. // Scale the frame of the image.
  1999. $width *= $scale_x;
  2000. $height *= $scale_y;
  2001. // Calculate the vertices of the image and write the OBJ record
  2002. $this->_positionImage($col, $row, $x, $y, $width, $height);
  2003. // Write the IMDATA record to store the bitmap data
  2004. $record = 0x007f;
  2005. $length = 8 + $size;
  2006. $cf = 0x09;
  2007. $env = 0x01;
  2008. $lcb = $size;
  2009. $header = pack("vvvvV", $record, $length, $cf, $env, $lcb);
  2010. $this->_append($header.$data);
  2011. }
  2012. /**
  2013. * Calculate the vertices that define the position of the image as required by
  2014. * the OBJ record.
  2015. *
  2016. * +------------+------------+
  2017. * | A | B |
  2018. * +-----+------------+------------+
  2019. * | |(x1,y1) | |
  2020. * | 1 |(A1)._______|______ |
  2021. * | | | | |
  2022. * | | | | |
  2023. * +-----+----| BITMAP |-----+
  2024. * | | | | |
  2025. * | 2 | |______________. |
  2026. * | | | (B2)|
  2027. * | | | (x2,y2)|
  2028. * +---- +------------+------------+
  2029. *
  2030. * Example of a bitmap that covers some of the area from cell A1 to cell B2.
  2031. *
  2032. * Based on the width and height of the bitmap we need to calculate 8 vars:
  2033. * $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
  2034. * The width and height of the cells are also variable and have to be taken into
  2035. * account.
  2036. * The values of $col_start and $row_start are passed in from the calling
  2037. * function. The values of $col_end and $row_end are calculated by subtracting
  2038. * the width and height of the bitmap from the width and height of the
  2039. * underlying cells.
  2040. * The vertices are expressed as a percentage of the underlying cell width as
  2041. * follows (rhs values are in pixels):
  2042. *
  2043. * x1 = X / W *1024
  2044. * y1 = Y / H *256
  2045. * x2 = (X-1) / W *1024
  2046. * y2 = (Y-1) / H *256
  2047. *
  2048. * Where: X is distance from the left side of the underlying cell
  2049. * Y is distance from the top of the underlying cell
  2050. * W is the width of the cell
  2051. * H is the height of the cell
  2052. * The SDK incorrectly states that the height should be expressed as a
  2053. * percentage of 1024.
  2054. *
  2055. * @access private
  2056. * @param integer $col_start Col containing upper left corner of object
  2057. * @param integer $row_start Row containing top left corner of object
  2058. * @param integer $x1 Distance to left side of object
  2059. * @param integer $y1 Distance to top of object
  2060. * @param integer $width Width of image frame
  2061. * @param integer $height Height of image frame
  2062. */
  2063. function _positionImage($col_start, $row_start, $x1, $y1, $width, $height)
  2064. {
  2065. // Initialise end cell to the same as the start cell
  2066. $col_end = $col_start; // Col containing lower right corner of object
  2067. $row_end = $row_start; // Row containing bottom right corner of object
  2068. // Zero the specified offset if greater than the cell dimensions
  2069. if ($x1 >= PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_start))) {
  2070. $x1 = 0;
  2071. }
  2072. if ($y1 >= PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_start + 1)) {
  2073. $y1 = 0;
  2074. }
  2075. $width = $width + $x1 -1;
  2076. $height = $height + $y1 -1;
  2077. // Subtract the underlying cell widths to find the end cell of the image
  2078. while ($width >= PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end))) {
  2079. $width -= PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end));
  2080. ++$col_end;
  2081. }
  2082. // Subtract the underlying cell heights to find the end cell of the image
  2083. while ($height >= PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1)) {
  2084. $height -= PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1);
  2085. ++$row_end;
  2086. }
  2087. // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
  2088. // with zero eight or width.
  2089. //
  2090. if (PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) == 0) {
  2091. return;
  2092. }
  2093. if (PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) == 0) {
  2094. return;
  2095. }
  2096. if (PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_start + 1) == 0) {
  2097. return;
  2098. }
  2099. if (PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1) == 0) {
  2100. return;
  2101. }
  2102. // Convert the pixel values to the percentage value expected by Excel
  2103. $x1 = $x1 / PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) * 1024;
  2104. $y1 = $y1 / PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_start + 1) * 256;
  2105. $x2 = $width / PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) * 1024; // Distance to right side of object
  2106. $y2 = $height / PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1) * 256; // Distance to bottom of object
  2107. $this->_writeObjPicture($col_start, $x1,
  2108. $row_start, $y1,
  2109. $col_end, $x2,
  2110. $row_end, $y2);
  2111. }
  2112. /**
  2113. * Store the OBJ record that precedes an IMDATA record. This could be generalise
  2114. * to support other Excel objects.
  2115. *
  2116. * @param integer $colL Column containing upper left corner of object
  2117. * @param integer $dxL Distance from left side of cell
  2118. * @param integer $rwT Row containing top left corner of object
  2119. * @param integer $dyT Distance from top of cell
  2120. * @param integer $colR Column containing lower right corner of object
  2121. * @param integer $dxR Distance from right of cell
  2122. * @param integer $rwB Row containing bottom right corner of object
  2123. * @param integer $dyB Distance from bottom of cell
  2124. */
  2125. private function _writeObjPicture($colL,$dxL,$rwT,$dyT,$colR,$dxR,$rwB,$dyB)
  2126. {
  2127. $record = 0x005d; // Record identifier
  2128. $length = 0x003c; // Bytes to follow
  2129. $cObj = 0x0001; // Count of objects in file (set to 1)
  2130. $OT = 0x0008; // Object type. 8 = Picture
  2131. $id = 0x0001; // Object ID
  2132. $grbit = 0x0614; // Option flags
  2133. $cbMacro = 0x0000; // Length of FMLA structure
  2134. $Reserved1 = 0x0000; // Reserved
  2135. $Reserved2 = 0x0000; // Reserved
  2136. $icvBack = 0x09; // Background colour
  2137. $icvFore = 0x09; // Foreground colour
  2138. $fls = 0x00; // Fill pattern
  2139. $fAuto = 0x00; // Automatic fill
  2140. $icv = 0x08; // Line colour
  2141. $lns = 0xff; // Line style
  2142. $lnw = 0x01; // Line weight
  2143. $fAutoB = 0x00; // Automatic border
  2144. $frs = 0x0000; // Frame style
  2145. $cf = 0x0009; // Image format, 9 = bitmap
  2146. $Reserved3 = 0x0000; // Reserved
  2147. $cbPictFmla = 0x0000; // Length of FMLA structure
  2148. $Reserved4 = 0x0000; // Reserved
  2149. $grbit2 = 0x0001; // Option flags
  2150. $Reserved5 = 0x0000; // Reserved
  2151. $header = pack("vv", $record, $length);
  2152. $data = pack("V", $cObj);
  2153. $data .= pack("v", $OT);
  2154. $data .= pack("v", $id);
  2155. $data .= pack("v", $grbit);
  2156. $data .= pack("v", $colL);
  2157. $data .= pack("v", $dxL);
  2158. $data .= pack("v", $rwT);
  2159. $data .= pack("v", $dyT);
  2160. $data .= pack("v", $colR);
  2161. $data .= pack("v", $dxR);
  2162. $data .= pack("v", $rwB);
  2163. $data .= pack("v", $dyB);
  2164. $data .= pack("v", $cbMacro);
  2165. $data .= pack("V", $Reserved1);
  2166. $data .= pack("v", $Reserved2);
  2167. $data .= pack("C", $icvBack);
  2168. $data .= pack("C", $icvFore);
  2169. $data .= pack("C", $fls);
  2170. $data .= pack("C", $fAuto);
  2171. $data .= pack("C", $icv);
  2172. $data .= pack("C", $lns);
  2173. $data .= pack("C", $lnw);
  2174. $data .= pack("C", $fAutoB);
  2175. $data .= pack("v", $frs);
  2176. $data .= pack("V", $cf);
  2177. $data .= pack("v", $Reserved3);
  2178. $data .= pack("v", $cbPictFmla);
  2179. $data .= pack("v", $Reserved4);
  2180. $data .= pack("v", $grbit2);
  2181. $data .= pack("V", $Reserved5);
  2182. $this->_append($header . $data);
  2183. }
  2184. /**
  2185. * Convert a GD-image into the internal format.
  2186. *
  2187. * @access private
  2188. * @param resource $image The image to process
  2189. * @return array Array with data and properties of the bitmap
  2190. */
  2191. function _processBitmapGd($image) {
  2192. $width = imagesx($image);
  2193. $height = imagesy($image);
  2194. $data = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18);
  2195. for ($j=$height; $j--; ) {
  2196. for ($i=0; $i < $width; ++$i) {
  2197. $color = imagecolorsforindex($image, imagecolorat($image, $i, $j));
  2198. foreach (array("red", "green", "blue") as $key) {
  2199. $color[$key] = $color[$key] + round((255 - $color[$key]) * $color["alpha"] / 127);
  2200. }
  2201. $data .= chr($color["blue"]) . chr($color["green"]) . chr($color["red"]);
  2202. }
  2203. if (3*$width % 4) {
  2204. $data .= str_repeat("\x00", 4 - 3*$width % 4);
  2205. }
  2206. }
  2207. return array($width, $height, strlen($data), $data);
  2208. }
  2209. /**
  2210. * Convert a 24 bit bitmap into the modified internal format used by Windows.
  2211. * This is described in BITMAPCOREHEADER and BITMAPCOREINFO structures in the
  2212. * MSDN library.
  2213. *
  2214. * @access private
  2215. * @param string $bitmap The bitmap to process
  2216. * @return array Array with data and properties of the bitmap
  2217. */
  2218. function _processBitmap($bitmap)
  2219. {
  2220. // Open file.
  2221. $bmp_fd = @fopen($bitmap,"rb");
  2222. if (!$bmp_fd) {
  2223. throw new PHPExcel_Writer_Exception("Couldn't import $bitmap");
  2224. }
  2225. // Slurp the file into a string.
  2226. $data = fread($bmp_fd, filesize($bitmap));
  2227. // Check that the file is big enough to be a bitmap.
  2228. if (strlen($data) <= 0x36) {
  2229. throw new PHPExcel_Writer_Exception("$bitmap doesn't contain enough data.\n");
  2230. }
  2231. // The first 2 bytes are used to identify the bitmap.
  2232. $identity = unpack("A2ident", $data);
  2233. if ($identity['ident'] != "BM") {
  2234. throw new PHPExcel_Writer_Exception("$bitmap doesn't appear to be a valid bitmap image.\n");
  2235. }
  2236. // Remove bitmap data: ID.
  2237. $data = substr($data, 2);
  2238. // Read and remove the bitmap size. This is more reliable than reading
  2239. // the data size at offset 0x22.
  2240. //
  2241. $size_array = unpack("Vsa", substr($data, 0, 4));
  2242. $size = $size_array['sa'];
  2243. $data = substr($data, 4);
  2244. $size -= 0x36; // Subtract size of bitmap header.
  2245. $size += 0x0C; // Add size of BIFF header.
  2246. // Remove bitmap data: reserved, offset, header length.
  2247. $data = substr($data, 12);
  2248. // Read and remove the bitmap width and height. Verify the sizes.
  2249. $width_and_height = unpack("V2", substr($data, 0, 8));
  2250. $width = $width_and_height[1];
  2251. $height = $width_and_height[2];
  2252. $data = substr($data, 8);
  2253. if ($width > 0xFFFF) {
  2254. throw new PHPExcel_Writer_Exception("$bitmap: largest image width supported is 65k.\n");
  2255. }
  2256. if ($height > 0xFFFF) {
  2257. throw new PHPExcel_Writer_Exception("$bitmap: largest image height supported is 65k.\n");
  2258. }
  2259. // Read and remove the bitmap planes and bpp data. Verify them.
  2260. $planes_and_bitcount = unpack("v2", substr($data, 0, 4));
  2261. $data = substr($data, 4);
  2262. if ($planes_and_bitcount[2] != 24) { // Bitcount
  2263. throw new PHPExcel_Writer_Exception("$bitmap isn't a 24bit true color bitmap.\n");
  2264. }
  2265. if ($planes_and_bitcount[1] != 1) {
  2266. throw new PHPExcel_Writer_Exception("$bitmap: only 1 plane supported in bitmap image.\n");
  2267. }
  2268. // Read and remove the bitmap compression. Verify compression.
  2269. $compression = unpack("Vcomp", substr($data, 0, 4));
  2270. $data = substr($data, 4);
  2271. //$compression = 0;
  2272. if ($compression['comp'] != 0) {
  2273. throw new PHPExcel_Writer_Exception("$bitmap: compression not supported in bitmap image.\n");
  2274. }
  2275. // Remove bitmap data: data size, hres, vres, colours, imp. colours.
  2276. $data = substr($data, 20);
  2277. // Add the BITMAPCOREHEADER data
  2278. $header = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18);
  2279. $data = $header . $data;
  2280. return (array($width, $height, $size, $data));
  2281. }
  2282. /**
  2283. * Store the window zoom factor. This should be a reduced fraction but for
  2284. * simplicity we will store all fractions with a numerator of 100.
  2285. */
  2286. private function _writeZoom()
  2287. {
  2288. // If scale is 100 we don't need to write a record
  2289. if ($this->_phpSheet->getSheetView()->getZoomScale() == 100) {
  2290. return;
  2291. }
  2292. $record = 0x00A0; // Record identifier
  2293. $length = 0x0004; // Bytes to follow
  2294. $header = pack("vv", $record, $length);
  2295. $data = pack("vv", $this->_phpSheet->getSheetView()->getZoomScale(), 100);
  2296. $this->_append($header . $data);
  2297. }
  2298. /**
  2299. * Get Escher object
  2300. *
  2301. * @return PHPExcel_Shared_Escher
  2302. */
  2303. public function getEscher()
  2304. {
  2305. return $this->_escher;
  2306. }
  2307. /**
  2308. * Set Escher object
  2309. *
  2310. * @param PHPExcel_Shared_Escher $pValue
  2311. */
  2312. public function setEscher(PHPExcel_Shared_Escher $pValue = null)
  2313. {
  2314. $this->_escher = $pValue;
  2315. }
  2316. /**
  2317. * Write MSODRAWING record
  2318. */
  2319. private function _writeMsoDrawing()
  2320. {
  2321. // write the Escher stream if necessary
  2322. if (isset($this->_escher)) {
  2323. $writer = new PHPExcel_Writer_Excel5_Escher($this->_escher);
  2324. $data = $writer->close();
  2325. $spOffsets = $writer->getSpOffsets();
  2326. $spTypes = $writer->getSpTypes();
  2327. // write the neccesary MSODRAWING, OBJ records
  2328. // split the Escher stream
  2329. $spOffsets[0] = 0;
  2330. $nm = count($spOffsets) - 1; // number of shapes excluding first shape
  2331. for ($i = 1; $i <= $nm; ++$i) {
  2332. // MSODRAWING record
  2333. $record = 0x00EC; // Record identifier
  2334. // chunk of Escher stream for one shape
  2335. $dataChunk = substr($data, $spOffsets[$i -1], $spOffsets[$i] - $spOffsets[$i - 1]);
  2336. $length = strlen($dataChunk);
  2337. $header = pack("vv", $record, $length);
  2338. $this->_append($header . $dataChunk);
  2339. // OBJ record
  2340. $record = 0x005D; // record identifier
  2341. $objData = '';
  2342. // ftCmo
  2343. if($spTypes[$i] == 0x00C9){
  2344. // Add ftCmo (common object data) subobject
  2345. $objData .=
  2346. pack('vvvvvVVV'
  2347. , 0x0015 // 0x0015 = ftCmo
  2348. , 0x0012 // length of ftCmo data
  2349. , 0x0014 // object type, 0x0014 = filter
  2350. , $i // object id number, Excel seems to use 1-based index, local for the sheet
  2351. , 0x2101 // option flags, 0x2001 is what OpenOffice.org uses
  2352. , 0 // reserved
  2353. , 0 // reserved
  2354. , 0 // reserved
  2355. );
  2356. // Add ftSbs Scroll bar subobject
  2357. $objData .= pack('vv', 0x00C, 0x0014);
  2358. $objData .= pack('H*', '0000000000000000640001000A00000010000100');
  2359. // Add ftLbsData (List box data) subobject
  2360. $objData .= pack('vv', 0x0013, 0x1FEE);
  2361. $objData .= pack('H*', '00000000010001030000020008005700');
  2362. }
  2363. else {
  2364. // Add ftCmo (common object data) subobject
  2365. $objData .=
  2366. pack('vvvvvVVV'
  2367. , 0x0015 // 0x0015 = ftCmo
  2368. , 0x0012 // length of ftCmo data
  2369. , 0x0008 // object type, 0x0008 = picture
  2370. , $i // object id number, Excel seems to use 1-based index, local for the sheet
  2371. , 0x6011 // option flags, 0x6011 is what OpenOffice.org uses
  2372. , 0 // reserved
  2373. , 0 // reserved
  2374. , 0 // reserved
  2375. );
  2376. }
  2377. // ftEnd
  2378. $objData .=
  2379. pack('vv'
  2380. , 0x0000 // 0x0000 = ftEnd
  2381. , 0x0000 // length of ftEnd data
  2382. );
  2383. $length = strlen($objData);
  2384. $header = pack('vv', $record, $length);
  2385. $this->_append($header . $objData);
  2386. }
  2387. }
  2388. }
  2389. /**
  2390. * Store the DATAVALIDATIONS and DATAVALIDATION records.
  2391. */
  2392. private function _writeDataValidity()
  2393. {
  2394. // Datavalidation collection
  2395. $dataValidationCollection = $this->_phpSheet->getDataValidationCollection();
  2396. // Write data validations?
  2397. if (!empty($dataValidationCollection)) {
  2398. // DATAVALIDATIONS record
  2399. $record = 0x01B2; // Record identifier
  2400. $length = 0x0012; // Bytes to follow
  2401. $grbit = 0x0000; // Prompt box at cell, no cached validity data at DV records
  2402. $horPos = 0x00000000; // Horizontal position of prompt box, if fixed position
  2403. $verPos = 0x00000000; // Vertical position of prompt box, if fixed position
  2404. $objId = 0xFFFFFFFF; // Object identifier of drop down arrow object, or -1 if not visible
  2405. $header = pack('vv', $record, $length);
  2406. $data = pack('vVVVV', $grbit, $horPos, $verPos, $objId,
  2407. count($dataValidationCollection));
  2408. $this->_append($header.$data);
  2409. // DATAVALIDATION records
  2410. $record = 0x01BE; // Record identifier
  2411. foreach ($dataValidationCollection as $cellCoordinate => $dataValidation) {
  2412. // initialize record data
  2413. $data = '';
  2414. // options
  2415. $options = 0x00000000;
  2416. // data type
  2417. $type = $dataValidation->getType();
  2418. switch ($type) {
  2419. case PHPExcel_Cell_DataValidation::TYPE_NONE: $type = 0x00; break;
  2420. case PHPExcel_Cell_DataValidation::TYPE_WHOLE: $type = 0x01; break;
  2421. case PHPExcel_Cell_DataValidation::TYPE_DECIMAL: $type = 0x02; break;
  2422. case PHPExcel_Cell_DataValidation::TYPE_LIST: $type = 0x03; break;
  2423. case PHPExcel_Cell_DataValidation::TYPE_DATE: $type = 0x04; break;
  2424. case PHPExcel_Cell_DataValidation::TYPE_TIME: $type = 0x05; break;
  2425. case PHPExcel_Cell_DataValidation::TYPE_TEXTLENGTH: $type = 0x06; break;
  2426. case PHPExcel_Cell_DataValidation::TYPE_CUSTOM: $type = 0x07; break;
  2427. }
  2428. $options |= $type << 0;
  2429. // error style
  2430. $errorStyle = $dataValidation->getType();
  2431. switch ($errorStyle) {
  2432. case PHPExcel_Cell_DataValidation::STYLE_STOP: $errorStyle = 0x00; break;
  2433. case PHPExcel_Cell_DataValidation::STYLE_WARNING: $errorStyle = 0x01; break;
  2434. case PHPExcel_Cell_DataValidation::STYLE_INFORMATION: $errorStyle = 0x02; break;
  2435. }
  2436. $options |= $errorStyle << 4;
  2437. // explicit formula?
  2438. if ($type == 0x03 && preg_match('/^\".*\"$/', $dataValidation->getFormula1())) {
  2439. $options |= 0x01 << 7;
  2440. }
  2441. // empty cells allowed
  2442. $options |= $dataValidation->getAllowBlank() << 8;
  2443. // show drop down
  2444. $options |= (!$dataValidation->getShowDropDown()) << 9;
  2445. // show input message
  2446. $options |= $dataValidation->getShowInputMessage() << 18;
  2447. // show error message
  2448. $options |= $dataValidation->getShowErrorMessage() << 19;
  2449. // condition operator
  2450. $operator = $dataValidation->getOperator();
  2451. switch ($operator) {
  2452. case PHPExcel_Cell_DataValidation::OPERATOR_BETWEEN: $operator = 0x00 ; break;
  2453. case PHPExcel_Cell_DataValidation::OPERATOR_NOTBETWEEN: $operator = 0x01 ; break;
  2454. case PHPExcel_Cell_DataValidation::OPERATOR_EQUAL: $operator = 0x02 ; break;
  2455. case PHPExcel_Cell_DataValidation::OPERATOR_NOTEQUAL: $operator = 0x03 ; break;
  2456. case PHPExcel_Cell_DataValidation::OPERATOR_GREATERTHAN: $operator = 0x04 ; break;
  2457. case PHPExcel_Cell_DataValidation::OPERATOR_LESSTHAN: $operator = 0x05 ; break;
  2458. case PHPExcel_Cell_DataValidation::OPERATOR_GREATERTHANOREQUAL: $operator = 0x06; break;
  2459. case PHPExcel_Cell_DataValidation::OPERATOR_LESSTHANOREQUAL: $operator = 0x07 ; break;
  2460. }
  2461. $options |= $operator << 20;
  2462. $data = pack('V', $options);
  2463. // prompt title
  2464. $promptTitle = $dataValidation->getPromptTitle() !== '' ?
  2465. $dataValidation->getPromptTitle() : chr(0);
  2466. $data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($promptTitle);
  2467. // error title
  2468. $errorTitle = $dataValidation->getErrorTitle() !== '' ?
  2469. $dataValidation->getErrorTitle() : chr(0);
  2470. $data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($errorTitle);
  2471. // prompt text
  2472. $prompt = $dataValidation->getPrompt() !== '' ?
  2473. $dataValidation->getPrompt() : chr(0);
  2474. $data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($prompt);
  2475. // error text
  2476. $error = $dataValidation->getError() !== '' ?
  2477. $dataValidation->getError() : chr(0);
  2478. $data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($error);
  2479. // formula 1
  2480. try {
  2481. $formula1 = $dataValidation->getFormula1();
  2482. if ($type == 0x03) { // list type
  2483. $formula1 = str_replace(',', chr(0), $formula1);
  2484. }
  2485. $this->_parser->parse($formula1);
  2486. $formula1 = $this->_parser->toReversePolish();
  2487. $sz1 = strlen($formula1);
  2488. } catch(PHPExcel_Exception $e) {
  2489. $sz1 = 0;
  2490. $formula1 = '';
  2491. }
  2492. $data .= pack('vv', $sz1, 0x0000);
  2493. $data .= $formula1;
  2494. // formula 2
  2495. try {
  2496. $formula2 = $dataValidation->getFormula2();
  2497. if ($formula2 === '') {
  2498. throw new PHPExcel_Writer_Exception('No formula2');
  2499. }
  2500. $this->_parser->parse($formula2);
  2501. $formula2 = $this->_parser->toReversePolish();
  2502. $sz2 = strlen($formula2);
  2503. } catch(PHPExcel_Exception $e) {
  2504. $sz2 = 0;
  2505. $formula2 = '';
  2506. }
  2507. $data .= pack('vv', $sz2, 0x0000);
  2508. $data .= $formula2;
  2509. // cell range address list
  2510. $data .= pack('v', 0x0001);
  2511. $data .= $this->_writeBIFF8CellRangeAddressFixed($cellCoordinate);
  2512. $length = strlen($data);
  2513. $header = pack("vv", $record, $length);
  2514. $this->_append($header . $data);
  2515. }
  2516. }
  2517. }
  2518. /**
  2519. * Map Error code
  2520. *
  2521. * @param string $errorCode
  2522. * @return int
  2523. */
  2524. private static function _mapErrorCode($errorCode) {
  2525. switch ($errorCode) {
  2526. case '#NULL!': return 0x00;
  2527. case '#DIV/0!': return 0x07;
  2528. case '#VALUE!': return 0x0F;
  2529. case '#REF!': return 0x17;
  2530. case '#NAME?': return 0x1D;
  2531. case '#NUM!': return 0x24;
  2532. case '#N/A': return 0x2A;
  2533. }
  2534. return 0;
  2535. }
  2536. /**
  2537. * Write PLV Record
  2538. */
  2539. private function _writePageLayoutView(){
  2540. $record = 0x088B; // Record identifier
  2541. $length = 0x0010; // Bytes to follow
  2542. $rt = 0x088B; // 2
  2543. $grbitFrt = 0x0000; // 2
  2544. $reserved = 0x0000000000000000; // 8
  2545. $wScalvePLV = $this->_phpSheet->getSheetView()->getZoomScale(); // 2
  2546. // The options flags that comprise $grbit
  2547. if($this->_phpSheet->getSheetView()->getView() == PHPExcel_Worksheet_SheetView::SHEETVIEW_PAGE_LAYOUT){
  2548. $fPageLayoutView = 1;
  2549. } else {
  2550. $fPageLayoutView = 0;
  2551. }
  2552. $fRulerVisible = 0;
  2553. $fWhitespaceHidden = 0;
  2554. $grbit = $fPageLayoutView; // 2
  2555. $grbit |= $fRulerVisible << 1;
  2556. $grbit |= $fWhitespaceHidden << 3;
  2557. $header = pack("vv", $record, $length);
  2558. $data = pack("vvVVvv", $rt, $grbitFrt, 0x00000000, 0x00000000, $wScalvePLV, $grbit);
  2559. $this->_append($header . $data);
  2560. }
  2561. /**
  2562. * Write CFRule Record
  2563. * @param PHPExcel_Style_Conditional $conditional
  2564. */
  2565. private function _writeCFRule(PHPExcel_Style_Conditional $conditional){
  2566. $record = 0x01B1; // Record identifier
  2567. // $type : Type of the CF
  2568. // $operatorType : Comparison operator
  2569. if($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_EXPRESSION){
  2570. $type = 0x02;
  2571. $operatorType = 0x00;
  2572. } else if($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CELLIS){
  2573. $type = 0x01;
  2574. switch ($conditional->getOperatorType()){
  2575. case PHPExcel_Style_Conditional::OPERATOR_NONE:
  2576. $operatorType = 0x00;
  2577. break;
  2578. case PHPExcel_Style_Conditional::OPERATOR_EQUAL:
  2579. $operatorType = 0x03;
  2580. break;
  2581. case PHPExcel_Style_Conditional::OPERATOR_GREATERTHAN:
  2582. $operatorType = 0x05;
  2583. break;
  2584. case PHPExcel_Style_Conditional::OPERATOR_GREATERTHANOREQUAL:
  2585. $operatorType = 0x07;
  2586. break;
  2587. case PHPExcel_Style_Conditional::OPERATOR_LESSTHAN:
  2588. $operatorType = 0x06;
  2589. break;
  2590. case PHPExcel_Style_Conditional::OPERATOR_LESSTHANOREQUAL:
  2591. $operatorType = 0x08;
  2592. break;
  2593. case PHPExcel_Style_Conditional::OPERATOR_NOTEQUAL:
  2594. $operatorType = 0x04;
  2595. break;
  2596. case PHPExcel_Style_Conditional::OPERATOR_BETWEEN:
  2597. $operatorType = 0x01;
  2598. break;
  2599. // not OPERATOR_NOTBETWEEN 0x02
  2600. }
  2601. }
  2602. // $szValue1 : size of the formula data for first value or formula
  2603. // $szValue2 : size of the formula data for second value or formula
  2604. $arrConditions = $conditional->getConditions();
  2605. $numConditions = sizeof($arrConditions);
  2606. if($numConditions == 1){
  2607. $szValue1 = ($arrConditions[0] <= 65535 ? 3 : 0x0000);
  2608. $szValue2 = 0x0000;
  2609. $operand1 = pack('Cv', 0x1E, $arrConditions[0]);
  2610. $operand2 = null;
  2611. } else if($numConditions == 2 && ($conditional->getOperatorType() == PHPExcel_Style_Conditional::OPERATOR_BETWEEN)){
  2612. $szValue1 = ($arrConditions[0] <= 65535 ? 3 : 0x0000);
  2613. $szValue2 = ($arrConditions[1] <= 65535 ? 3 : 0x0000);
  2614. $operand1 = pack('Cv', 0x1E, $arrConditions[0]);
  2615. $operand2 = pack('Cv', 0x1E, $arrConditions[1]);
  2616. } else {
  2617. $szValue1 = 0x0000;
  2618. $szValue2 = 0x0000;
  2619. $operand1 = null;
  2620. $operand2 = null;
  2621. }
  2622. // $flags : Option flags
  2623. // Alignment
  2624. $bAlignHz = ($conditional->getStyle()->getAlignment()->getHorizontal() == null ? 1 : 0);
  2625. $bAlignVt = ($conditional->getStyle()->getAlignment()->getVertical() == null ? 1 : 0);
  2626. $bAlignWrapTx = ($conditional->getStyle()->getAlignment()->getWrapText() == false ? 1 : 0);
  2627. $bTxRotation = ($conditional->getStyle()->getAlignment()->getTextRotation() == null ? 1 : 0);
  2628. $bIndent = ($conditional->getStyle()->getAlignment()->getIndent() == 0 ? 1 : 0);
  2629. $bShrinkToFit = ($conditional->getStyle()->getAlignment()->getShrinkToFit() == false ? 1 : 0);
  2630. if($bAlignHz == 0 || $bAlignVt == 0 || $bAlignWrapTx == 0 || $bTxRotation == 0 || $bIndent == 0 || $bShrinkToFit == 0){
  2631. $bFormatAlign = 1;
  2632. } else {
  2633. $bFormatAlign = 0;
  2634. }
  2635. // Protection
  2636. $bProtLocked = ($conditional->getStyle()->getProtection()->getLocked() == null ? 1 : 0);
  2637. $bProtHidden = ($conditional->getStyle()->getProtection()->getHidden() == null ? 1 : 0);
  2638. if($bProtLocked == 0 || $bProtHidden == 0){
  2639. $bFormatProt = 1;
  2640. } else {
  2641. $bFormatProt = 0;
  2642. }
  2643. // Border
  2644. $bBorderLeft = ($conditional->getStyle()->getBorders()->getLeft()->getColor()->getARGB() == PHPExcel_Style_Color::COLOR_BLACK
  2645. && $conditional->getStyle()->getBorders()->getLeft()->getBorderStyle() == PHPExcel_Style_Border::BORDER_NONE ? 1 : 0);
  2646. $bBorderRight = ($conditional->getStyle()->getBorders()->getRight()->getColor()->getARGB() == PHPExcel_Style_Color::COLOR_BLACK
  2647. && $conditional->getStyle()->getBorders()->getRight()->getBorderStyle() == PHPExcel_Style_Border::BORDER_NONE ? 1 : 0);
  2648. $bBorderTop = ($conditional->getStyle()->getBorders()->getTop()->getColor()->getARGB() == PHPExcel_Style_Color::COLOR_BLACK
  2649. && $conditional->getStyle()->getBorders()->getTop()->getBorderStyle() == PHPExcel_Style_Border::BORDER_NONE ? 1 : 0);
  2650. $bBorderBottom = ($conditional->getStyle()->getBorders()->getBottom()->getColor()->getARGB() == PHPExcel_Style_Color::COLOR_BLACK
  2651. && $conditional->getStyle()->getBorders()->getBottom()->getBorderStyle() == PHPExcel_Style_Border::BORDER_NONE ? 1 : 0);
  2652. if($bBorderLeft == 0 || $bBorderRight == 0 || $bBorderTop == 0 || $bBorderBottom == 0){
  2653. $bFormatBorder = 1;
  2654. } else {
  2655. $bFormatBorder = 0;
  2656. }
  2657. // Pattern
  2658. $bFillStyle = ($conditional->getStyle()->getFill()->getFillType() == null ? 0 : 1);
  2659. $bFillColor = ($conditional->getStyle()->getFill()->getStartColor()->getARGB() == null ? 0 : 1);
  2660. $bFillColorBg = ($conditional->getStyle()->getFill()->getEndColor()->getARGB() == null ? 0 : 1);
  2661. if($bFillStyle == 0 || $bFillColor == 0 || $bFillColorBg == 0){
  2662. $bFormatFill = 1;
  2663. } else {
  2664. $bFormatFill = 0;
  2665. }
  2666. // Font
  2667. if($conditional->getStyle()->getFont()->getName() != null
  2668. || $conditional->getStyle()->getFont()->getSize() != null
  2669. || $conditional->getStyle()->getFont()->getBold() != null
  2670. || $conditional->getStyle()->getFont()->getItalic() != null
  2671. || $conditional->getStyle()->getFont()->getSuperScript() != null
  2672. || $conditional->getStyle()->getFont()->getSubScript() != null
  2673. || $conditional->getStyle()->getFont()->getUnderline() != null
  2674. || $conditional->getStyle()->getFont()->getStrikethrough() != null
  2675. || $conditional->getStyle()->getFont()->getColor()->getARGB() != null){
  2676. $bFormatFont = 1;
  2677. } else {
  2678. $bFormatFont = 0;
  2679. }
  2680. // Alignment
  2681. $flags = 0;
  2682. $flags |= (1 == $bAlignHz ? 0x00000001 : 0);
  2683. $flags |= (1 == $bAlignVt ? 0x00000002 : 0);
  2684. $flags |= (1 == $bAlignWrapTx ? 0x00000004 : 0);
  2685. $flags |= (1 == $bTxRotation ? 0x00000008 : 0);
  2686. // Justify last line flag
  2687. $flags |= (1 == 1 ? 0x00000010 : 0);
  2688. $flags |= (1 == $bIndent ? 0x00000020 : 0);
  2689. $flags |= (1 == $bShrinkToFit ? 0x00000040 : 0);
  2690. // Default
  2691. $flags |= (1 == 1 ? 0x00000080 : 0);
  2692. // Protection
  2693. $flags |= (1 == $bProtLocked ? 0x00000100 : 0);
  2694. $flags |= (1 == $bProtHidden ? 0x00000200 : 0);
  2695. // Border
  2696. $flags |= (1 == $bBorderLeft ? 0x00000400 : 0);
  2697. $flags |= (1 == $bBorderRight ? 0x00000800 : 0);
  2698. $flags |= (1 == $bBorderTop ? 0x00001000 : 0);
  2699. $flags |= (1 == $bBorderBottom ? 0x00002000 : 0);
  2700. $flags |= (1 == 1 ? 0x00004000 : 0); // Top left to Bottom right border
  2701. $flags |= (1 == 1 ? 0x00008000 : 0); // Bottom left to Top right border
  2702. // Pattern
  2703. $flags |= (1 == $bFillStyle ? 0x00010000 : 0);
  2704. $flags |= (1 == $bFillColor ? 0x00020000 : 0);
  2705. $flags |= (1 == $bFillColorBg ? 0x00040000 : 0);
  2706. $flags |= (1 == 1 ? 0x00380000 : 0);
  2707. // Font
  2708. $flags |= (1 == $bFormatFont ? 0x04000000 : 0);
  2709. // Alignment :
  2710. $flags |= (1 == $bFormatAlign ? 0x08000000 : 0);
  2711. // Border
  2712. $flags |= (1 == $bFormatBorder ? 0x10000000 : 0);
  2713. // Pattern
  2714. $flags |= (1 == $bFormatFill ? 0x20000000 : 0);
  2715. // Protection
  2716. $flags |= (1 == $bFormatProt ? 0x40000000 : 0);
  2717. // Text direction
  2718. $flags |= (1 == 0 ? 0x80000000 : 0);
  2719. // Data Blocks
  2720. if($bFormatFont == 1){
  2721. // Font Name
  2722. if($conditional->getStyle()->getFont()->getName() == null){
  2723. $dataBlockFont = pack('VVVVVVVV', 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000);
  2724. $dataBlockFont .= pack('VVVVVVVV', 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000);
  2725. } else {
  2726. $dataBlockFont = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($conditional->getStyle()->getFont()->getName());
  2727. }
  2728. // Font Size
  2729. if($conditional->getStyle()->getFont()->getSize() == null){
  2730. $dataBlockFont .= pack('V', 20 * 11);
  2731. } else {
  2732. $dataBlockFont .= pack('V', 20 * $conditional->getStyle()->getFont()->getSize());
  2733. }
  2734. // Font Options
  2735. $dataBlockFont .= pack('V', 0);
  2736. // Font weight
  2737. if($conditional->getStyle()->getFont()->getBold() == true){
  2738. $dataBlockFont .= pack('v', 0x02BC);
  2739. } else {
  2740. $dataBlockFont .= pack('v', 0x0190);
  2741. }
  2742. // Escapement type
  2743. if($conditional->getStyle()->getFont()->getSubScript() == true){
  2744. $dataBlockFont .= pack('v', 0x02);
  2745. $fontEscapement = 0;
  2746. } else if($conditional->getStyle()->getFont()->getSuperScript() == true){
  2747. $dataBlockFont .= pack('v', 0x01);
  2748. $fontEscapement = 0;
  2749. } else {
  2750. $dataBlockFont .= pack('v', 0x00);
  2751. $fontEscapement = 1;
  2752. }
  2753. // Underline type
  2754. switch ($conditional->getStyle()->getFont()->getUnderline()){
  2755. case PHPExcel_Style_Font::UNDERLINE_NONE : $dataBlockFont .= pack('C', 0x00); $fontUnderline = 0; break;
  2756. case PHPExcel_Style_Font::UNDERLINE_DOUBLE : $dataBlockFont .= pack('C', 0x02); $fontUnderline = 0; break;
  2757. case PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING : $dataBlockFont .= pack('C', 0x22); $fontUnderline = 0; break;
  2758. case PHPExcel_Style_Font::UNDERLINE_SINGLE : $dataBlockFont .= pack('C', 0x01); $fontUnderline = 0; break;
  2759. case PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING : $dataBlockFont .= pack('C', 0x21); $fontUnderline = 0; break;
  2760. default : $dataBlockFont .= pack('C', 0x00); $fontUnderline = 1; break;
  2761. }
  2762. // Not used (3)
  2763. $dataBlockFont .= pack('vC', 0x0000, 0x00);
  2764. // Font color index
  2765. switch ($conditional->getStyle()->getFont()->getColor()->getRGB()) {
  2766. case '000000': $colorIdx = 0x08; break;
  2767. case 'FFFFFF': $colorIdx = 0x09; break;
  2768. case 'FF0000': $colorIdx = 0x0A; break;
  2769. case '00FF00': $colorIdx = 0x0B; break;
  2770. case '0000FF': $colorIdx = 0x0C; break;
  2771. case 'FFFF00': $colorIdx = 0x0D; break;
  2772. case 'FF00FF': $colorIdx = 0x0E; break;
  2773. case '00FFFF': $colorIdx = 0x0F; break;
  2774. case '800000': $colorIdx = 0x10; break;
  2775. case '008000': $colorIdx = 0x11; break;
  2776. case '000080': $colorIdx = 0x12; break;
  2777. case '808000': $colorIdx = 0x13; break;
  2778. case '800080': $colorIdx = 0x14; break;
  2779. case '008080': $colorIdx = 0x15; break;
  2780. case 'C0C0C0': $colorIdx = 0x16; break;
  2781. case '808080': $colorIdx = 0x17; break;
  2782. case '9999FF': $colorIdx = 0x18; break;
  2783. case '993366': $colorIdx = 0x19; break;
  2784. case 'FFFFCC': $colorIdx = 0x1A; break;
  2785. case 'CCFFFF': $colorIdx = 0x1B; break;
  2786. case '660066': $colorIdx = 0x1C; break;
  2787. case 'FF8080': $colorIdx = 0x1D; break;
  2788. case '0066CC': $colorIdx = 0x1E; break;
  2789. case 'CCCCFF': $colorIdx = 0x1F; break;
  2790. case '000080': $colorIdx = 0x20; break;
  2791. case 'FF00FF': $colorIdx = 0x21; break;
  2792. case 'FFFF00': $colorIdx = 0x22; break;
  2793. case '00FFFF': $colorIdx = 0x23; break;
  2794. case '800080': $colorIdx = 0x24; break;
  2795. case '800000': $colorIdx = 0x25; break;
  2796. case '008080': $colorIdx = 0x26; break;
  2797. case '0000FF': $colorIdx = 0x27; break;
  2798. case '00CCFF': $colorIdx = 0x28; break;
  2799. case 'CCFFFF': $colorIdx = 0x29; break;
  2800. case 'CCFFCC': $colorIdx = 0x2A; break;
  2801. case 'FFFF99': $colorIdx = 0x2B; break;
  2802. case '99CCFF': $colorIdx = 0x2C; break;
  2803. case 'FF99CC': $colorIdx = 0x2D; break;
  2804. case 'CC99FF': $colorIdx = 0x2E; break;
  2805. case 'FFCC99': $colorIdx = 0x2F; break;
  2806. case '3366FF': $colorIdx = 0x30; break;
  2807. case '33CCCC': $colorIdx = 0x31; break;
  2808. case '99CC00': $colorIdx = 0x32; break;
  2809. case 'FFCC00': $colorIdx = 0x33; break;
  2810. case 'FF9900': $colorIdx = 0x34; break;
  2811. case 'FF6600': $colorIdx = 0x35; break;
  2812. case '666699': $colorIdx = 0x36; break;
  2813. case '969696': $colorIdx = 0x37; break;
  2814. case '003366': $colorIdx = 0x38; break;
  2815. case '339966': $colorIdx = 0x39; break;
  2816. case '003300': $colorIdx = 0x3A; break;
  2817. case '333300': $colorIdx = 0x3B; break;
  2818. case '993300': $colorIdx = 0x3C; break;
  2819. case '993366': $colorIdx = 0x3D; break;
  2820. case '333399': $colorIdx = 0x3E; break;
  2821. case '333333': $colorIdx = 0x3F; break;
  2822. default: $colorIdx = 0x00; break;
  2823. }
  2824. $dataBlockFont .= pack('V', $colorIdx);
  2825. // Not used (4)
  2826. $dataBlockFont .= pack('V', 0x00000000);
  2827. // Options flags for modified font attributes
  2828. $optionsFlags = 0;
  2829. $optionsFlagsBold = ($conditional->getStyle()->getFont()->getBold() == null ? 1 : 0);
  2830. $optionsFlags |= (1 == $optionsFlagsBold ? 0x00000002 : 0);
  2831. $optionsFlags |= (1 == 1 ? 0x00000008 : 0);
  2832. $optionsFlags |= (1 == 1 ? 0x00000010 : 0);
  2833. $optionsFlags |= (1 == 0 ? 0x00000020 : 0);
  2834. $optionsFlags |= (1 == 1 ? 0x00000080 : 0);
  2835. $dataBlockFont .= pack('V', $optionsFlags);
  2836. // Escapement type
  2837. $dataBlockFont .= pack('V', $fontEscapement);
  2838. // Underline type
  2839. $dataBlockFont .= pack('V', $fontUnderline);
  2840. // Always
  2841. $dataBlockFont .= pack('V', 0x00000000);
  2842. // Always
  2843. $dataBlockFont .= pack('V', 0x00000000);
  2844. // Not used (8)
  2845. $dataBlockFont .= pack('VV', 0x00000000, 0x00000000);
  2846. // Always
  2847. $dataBlockFont .= pack('v', 0x0001);
  2848. }
  2849. if($bFormatAlign == 1){
  2850. $blockAlign = 0;
  2851. // Alignment and text break
  2852. switch ($conditional->getStyle()->getAlignment()->getHorizontal()){
  2853. case PHPExcel_Style_Alignment::HORIZONTAL_GENERAL : $blockAlign = 0; break;
  2854. case PHPExcel_Style_Alignment::HORIZONTAL_LEFT : $blockAlign = 1; break;
  2855. case PHPExcel_Style_Alignment::HORIZONTAL_RIGHT : $blockAlign = 3; break;
  2856. case PHPExcel_Style_Alignment::HORIZONTAL_CENTER : $blockAlign = 2; break;
  2857. case PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS : $blockAlign = 6; break;
  2858. case PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY : $blockAlign = 5; break;
  2859. }
  2860. if($conditional->getStyle()->getAlignment()->getWrapText() == true){
  2861. $blockAlign |= 1 << 3;
  2862. } else {
  2863. $blockAlign |= 0 << 3;
  2864. }
  2865. switch ($conditional->getStyle()->getAlignment()->getVertical()){
  2866. case PHPExcel_Style_Alignment::VERTICAL_BOTTOM : $blockAlign = 2 << 4; break;
  2867. case PHPExcel_Style_Alignment::VERTICAL_TOP : $blockAlign = 0 << 4; break;
  2868. case PHPExcel_Style_Alignment::VERTICAL_CENTER : $blockAlign = 1 << 4; break;
  2869. case PHPExcel_Style_Alignment::VERTICAL_JUSTIFY : $blockAlign = 3 << 4; break;
  2870. }
  2871. $blockAlign |= 0 << 7;
  2872. // Text rotation angle
  2873. $blockRotation = $conditional->getStyle()->getAlignment()->getTextRotation();
  2874. // Indentation
  2875. $blockIndent = $conditional->getStyle()->getAlignment()->getIndent();
  2876. if($conditional->getStyle()->getAlignment()->getShrinkToFit() == true){
  2877. $blockIndent |= 1 << 4;
  2878. } else {
  2879. $blockIndent |= 0 << 4;
  2880. }
  2881. $blockIndent |= 0 << 6;
  2882. // Relative indentation
  2883. $blockIndentRelative = 255;
  2884. $dataBlockAlign = pack('CCvvv', $blockAlign, $blockRotation, $blockIndent, $blockIndentRelative, 0x0000);
  2885. }
  2886. if($bFormatBorder == 1){
  2887. $blockLineStyle = 0;
  2888. switch ($conditional->getStyle()->getBorders()->getLeft()->getBorderStyle()){
  2889. case PHPExcel_Style_Border::BORDER_NONE : $blockLineStyle |= 0x00; break;
  2890. case PHPExcel_Style_Border::BORDER_THIN : $blockLineStyle |= 0x01; break;
  2891. case PHPExcel_Style_Border::BORDER_MEDIUM : $blockLineStyle |= 0x02; break;
  2892. case PHPExcel_Style_Border::BORDER_DASHED : $blockLineStyle |= 0x03; break;
  2893. case PHPExcel_Style_Border::BORDER_DOTTED : $blockLineStyle |= 0x04; break;
  2894. case PHPExcel_Style_Border::BORDER_THICK : $blockLineStyle |= 0x05; break;
  2895. case PHPExcel_Style_Border::BORDER_DOUBLE : $blockLineStyle |= 0x06; break;
  2896. case PHPExcel_Style_Border::BORDER_HAIR : $blockLineStyle |= 0x07; break;
  2897. case PHPExcel_Style_Border::BORDER_MEDIUMDASHED : $blockLineStyle |= 0x08; break;
  2898. case PHPExcel_Style_Border::BORDER_DASHDOT : $blockLineStyle |= 0x09; break;
  2899. case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT : $blockLineStyle |= 0x0A; break;
  2900. case PHPExcel_Style_Border::BORDER_DASHDOTDOT : $blockLineStyle |= 0x0B; break;
  2901. case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT : $blockLineStyle |= 0x0C; break;
  2902. case PHPExcel_Style_Border::BORDER_SLANTDASHDOT : $blockLineStyle |= 0x0D; break;
  2903. }
  2904. switch ($conditional->getStyle()->getBorders()->getRight()->getBorderStyle()){
  2905. case PHPExcel_Style_Border::BORDER_NONE : $blockLineStyle |= 0x00 << 4; break;
  2906. case PHPExcel_Style_Border::BORDER_THIN : $blockLineStyle |= 0x01 << 4; break;
  2907. case PHPExcel_Style_Border::BORDER_MEDIUM : $blockLineStyle |= 0x02 << 4; break;
  2908. case PHPExcel_Style_Border::BORDER_DASHED : $blockLineStyle |= 0x03 << 4; break;
  2909. case PHPExcel_Style_Border::BORDER_DOTTED : $blockLineStyle |= 0x04 << 4; break;
  2910. case PHPExcel_Style_Border::BORDER_THICK : $blockLineStyle |= 0x05 << 4; break;
  2911. case PHPExcel_Style_Border::BORDER_DOUBLE : $blockLineStyle |= 0x06 << 4; break;
  2912. case PHPExcel_Style_Border::BORDER_HAIR : $blockLineStyle |= 0x07 << 4; break;
  2913. case PHPExcel_Style_Border::BORDER_MEDIUMDASHED : $blockLineStyle |= 0x08 << 4; break;
  2914. case PHPExcel_Style_Border::BORDER_DASHDOT : $blockLineStyle |= 0x09 << 4; break;
  2915. case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT : $blockLineStyle |= 0x0A << 4; break;
  2916. case PHPExcel_Style_Border::BORDER_DASHDOTDOT : $blockLineStyle |= 0x0B << 4; break;
  2917. case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT : $blockLineStyle |= 0x0C << 4; break;
  2918. case PHPExcel_Style_Border::BORDER_SLANTDASHDOT : $blockLineStyle |= 0x0D << 4; break;
  2919. }
  2920. switch ($conditional->getStyle()->getBorders()->getTop()->getBorderStyle()){
  2921. case PHPExcel_Style_Border::BORDER_NONE : $blockLineStyle |= 0x00 << 8; break;
  2922. case PHPExcel_Style_Border::BORDER_THIN : $blockLineStyle |= 0x01 << 8; break;
  2923. case PHPExcel_Style_Border::BORDER_MEDIUM : $blockLineStyle |= 0x02 << 8; break;
  2924. case PHPExcel_Style_Border::BORDER_DASHED : $blockLineStyle |= 0x03 << 8; break;
  2925. case PHPExcel_Style_Border::BORDER_DOTTED : $blockLineStyle |= 0x04 << 8; break;
  2926. case PHPExcel_Style_Border::BORDER_THICK : $blockLineStyle |= 0x05 << 8; break;
  2927. case PHPExcel_Style_Border::BORDER_DOUBLE : $blockLineStyle |= 0x06 << 8; break;
  2928. case PHPExcel_Style_Border::BORDER_HAIR : $blockLineStyle |= 0x07 << 8; break;
  2929. case PHPExcel_Style_Border::BORDER_MEDIUMDASHED : $blockLineStyle |= 0x08 << 8; break;
  2930. case PHPExcel_Style_Border::BORDER_DASHDOT : $blockLineStyle |= 0x09 << 8; break;
  2931. case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT : $blockLineStyle |= 0x0A << 8; break;
  2932. case PHPExcel_Style_Border::BORDER_DASHDOTDOT : $blockLineStyle |= 0x0B << 8; break;
  2933. case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT : $blockLineStyle |= 0x0C << 8; break;
  2934. case PHPExcel_Style_Border::BORDER_SLANTDASHDOT : $blockLineStyle |= 0x0D << 8; break;
  2935. }
  2936. switch ($conditional->getStyle()->getBorders()->getBottom()->getBorderStyle()){
  2937. case PHPExcel_Style_Border::BORDER_NONE : $blockLineStyle |= 0x00 << 12; break;
  2938. case PHPExcel_Style_Border::BORDER_THIN : $blockLineStyle |= 0x01 << 12; break;
  2939. case PHPExcel_Style_Border::BORDER_MEDIUM : $blockLineStyle |= 0x02 << 12; break;
  2940. case PHPExcel_Style_Border::BORDER_DASHED : $blockLineStyle |= 0x03 << 12; break;
  2941. case PHPExcel_Style_Border::BORDER_DOTTED : $blockLineStyle |= 0x04 << 12; break;
  2942. case PHPExcel_Style_Border::BORDER_THICK : $blockLineStyle |= 0x05 << 12; break;
  2943. case PHPExcel_Style_Border::BORDER_DOUBLE : $blockLineStyle |= 0x06 << 12; break;
  2944. case PHPExcel_Style_Border::BORDER_HAIR : $blockLineStyle |= 0x07 << 12; break;
  2945. case PHPExcel_Style_Border::BORDER_MEDIUMDASHED : $blockLineStyle |= 0x08 << 12; break;
  2946. case PHPExcel_Style_Border::BORDER_DASHDOT : $blockLineStyle |= 0x09 << 12; break;
  2947. case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT : $blockLineStyle |= 0x0A << 12; break;
  2948. case PHPExcel_Style_Border::BORDER_DASHDOTDOT : $blockLineStyle |= 0x0B << 12; break;
  2949. case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT : $blockLineStyle |= 0x0C << 12; break;
  2950. case PHPExcel_Style_Border::BORDER_SLANTDASHDOT : $blockLineStyle |= 0x0D << 12; break;
  2951. }
  2952. //@todo _writeCFRule() => $blockLineStyle => Index Color for left line
  2953. //@todo _writeCFRule() => $blockLineStyle => Index Color for right line
  2954. //@todo _writeCFRule() => $blockLineStyle => Top-left to bottom-right on/off
  2955. //@todo _writeCFRule() => $blockLineStyle => Bottom-left to top-right on/off
  2956. $blockColor = 0;
  2957. //@todo _writeCFRule() => $blockColor => Index Color for top line
  2958. //@todo _writeCFRule() => $blockColor => Index Color for bottom line
  2959. //@todo _writeCFRule() => $blockColor => Index Color for diagonal line
  2960. switch ($conditional->getStyle()->getBorders()->getDiagonal()->getBorderStyle()){
  2961. case PHPExcel_Style_Border::BORDER_NONE : $blockColor |= 0x00 << 21; break;
  2962. case PHPExcel_Style_Border::BORDER_THIN : $blockColor |= 0x01 << 21; break;
  2963. case PHPExcel_Style_Border::BORDER_MEDIUM : $blockColor |= 0x02 << 21; break;
  2964. case PHPExcel_Style_Border::BORDER_DASHED : $blockColor |= 0x03 << 21; break;
  2965. case PHPExcel_Style_Border::BORDER_DOTTED : $blockColor |= 0x04 << 21; break;
  2966. case PHPExcel_Style_Border::BORDER_THICK : $blockColor |= 0x05 << 21; break;
  2967. case PHPExcel_Style_Border::BORDER_DOUBLE : $blockColor |= 0x06 << 21; break;
  2968. case PHPExcel_Style_Border::BORDER_HAIR : $blockColor |= 0x07 << 21; break;
  2969. case PHPExcel_Style_Border::BORDER_MEDIUMDASHED : $blockColor |= 0x08 << 21; break;
  2970. case PHPExcel_Style_Border::BORDER_DASHDOT : $blockColor |= 0x09 << 21; break;
  2971. case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT : $blockColor |= 0x0A << 21; break;
  2972. case PHPExcel_Style_Border::BORDER_DASHDOTDOT : $blockColor |= 0x0B << 21; break;
  2973. case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT : $blockColor |= 0x0C << 21; break;
  2974. case PHPExcel_Style_Border::BORDER_SLANTDASHDOT : $blockColor |= 0x0D << 21; break;
  2975. }
  2976. $dataBlockBorder = pack('vv', $blockLineStyle, $blockColor);
  2977. }
  2978. if($bFormatFill == 1){
  2979. // Fill Patern Style
  2980. $blockFillPatternStyle = 0;
  2981. switch ($conditional->getStyle()->getFill()->getFillType()){
  2982. case PHPExcel_Style_Fill::FILL_NONE : $blockFillPatternStyle = 0x00; break;
  2983. case PHPExcel_Style_Fill::FILL_SOLID : $blockFillPatternStyle = 0x01; break;
  2984. case PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY : $blockFillPatternStyle = 0x02; break;
  2985. case PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY : $blockFillPatternStyle = 0x03; break;
  2986. case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY : $blockFillPatternStyle = 0x04; break;
  2987. case PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL : $blockFillPatternStyle = 0x05; break;
  2988. case PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL : $blockFillPatternStyle = 0x06; break;
  2989. case PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN : $blockFillPatternStyle = 0x07; break;
  2990. case PHPExcel_Style_Fill::FILL_PATTERN_DARKUP : $blockFillPatternStyle = 0x08; break;
  2991. case PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID : $blockFillPatternStyle = 0x09; break;
  2992. case PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS : $blockFillPatternStyle = 0x0A; break;
  2993. case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL : $blockFillPatternStyle = 0x0B; break;
  2994. case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL : $blockFillPatternStyle = 0x0C; break;
  2995. case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN : $blockFillPatternStyle = 0x0D; break;
  2996. case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP : $blockFillPatternStyle = 0x0E; break;
  2997. case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID : $blockFillPatternStyle = 0x0F; break;
  2998. case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS : $blockFillPatternStyle = 0x10; break;
  2999. case PHPExcel_Style_Fill::FILL_PATTERN_GRAY125 : $blockFillPatternStyle = 0x11; break;
  3000. case PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625 : $blockFillPatternStyle = 0x12; break;
  3001. case PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR : $blockFillPatternStyle = 0x00; break; // does not exist in BIFF8
  3002. case PHPExcel_Style_Fill::FILL_GRADIENT_PATH : $blockFillPatternStyle = 0x00; break; // does not exist in BIFF8
  3003. default : $blockFillPatternStyle = 0x00; break;
  3004. }
  3005. // Color
  3006. switch ($conditional->getStyle()->getFill()->getStartColor()->getRGB()) {
  3007. case '000000': $colorIdxBg = 0x08; break;
  3008. case 'FFFFFF': $colorIdxBg = 0x09; break;
  3009. case 'FF0000': $colorIdxBg = 0x0A; break;
  3010. case '00FF00': $colorIdxBg = 0x0B; break;
  3011. case '0000FF': $colorIdxBg = 0x0C; break;
  3012. case 'FFFF00': $colorIdxBg = 0x0D; break;
  3013. case 'FF00FF': $colorIdxBg = 0x0E; break;
  3014. case '00FFFF': $colorIdxBg = 0x0F; break;
  3015. case '800000': $colorIdxBg = 0x10; break;
  3016. case '008000': $colorIdxBg = 0x11; break;
  3017. case '000080': $colorIdxBg = 0x12; break;
  3018. case '808000': $colorIdxBg = 0x13; break;
  3019. case '800080': $colorIdxBg = 0x14; break;
  3020. case '008080': $colorIdxBg = 0x15; break;
  3021. case 'C0C0C0': $colorIdxBg = 0x16; break;
  3022. case '808080': $colorIdxBg = 0x17; break;
  3023. case '9999FF': $colorIdxBg = 0x18; break;
  3024. case '993366': $colorIdxBg = 0x19; break;
  3025. case 'FFFFCC': $colorIdxBg = 0x1A; break;
  3026. case 'CCFFFF': $colorIdxBg = 0x1B; break;
  3027. case '660066': $colorIdxBg = 0x1C; break;
  3028. case 'FF8080': $colorIdxBg = 0x1D; break;
  3029. case '0066CC': $colorIdxBg = 0x1E; break;
  3030. case 'CCCCFF': $colorIdxBg = 0x1F; break;
  3031. case '000080': $colorIdxBg = 0x20; break;
  3032. case 'FF00FF': $colorIdxBg = 0x21; break;
  3033. case 'FFFF00': $colorIdxBg = 0x22; break;
  3034. case '00FFFF': $colorIdxBg = 0x23; break;
  3035. case '800080': $colorIdxBg = 0x24; break;
  3036. case '800000': $colorIdxBg = 0x25; break;
  3037. case '008080': $colorIdxBg = 0x26; break;
  3038. case '0000FF': $colorIdxBg = 0x27; break;
  3039. case '00CCFF': $colorIdxBg = 0x28; break;
  3040. case 'CCFFFF': $colorIdxBg = 0x29; break;
  3041. case 'CCFFCC': $colorIdxBg = 0x2A; break;
  3042. case 'FFFF99': $colorIdxBg = 0x2B; break;
  3043. case '99CCFF': $colorIdxBg = 0x2C; break;
  3044. case 'FF99CC': $colorIdxBg = 0x2D; break;
  3045. case 'CC99FF': $colorIdxBg = 0x2E; break;
  3046. case 'FFCC99': $colorIdxBg = 0x2F; break;
  3047. case '3366FF': $colorIdxBg = 0x30; break;
  3048. case '33CCCC': $colorIdxBg = 0x31; break;
  3049. case '99CC00': $colorIdxBg = 0x32; break;
  3050. case 'FFCC00': $colorIdxBg = 0x33; break;
  3051. case 'FF9900': $colorIdxBg = 0x34; break;
  3052. case 'FF6600': $colorIdxBg = 0x35; break;
  3053. case '666699': $colorIdxBg = 0x36; break;
  3054. case '969696': $colorIdxBg = 0x37; break;
  3055. case '003366': $colorIdxBg = 0x38; break;
  3056. case '339966': $colorIdxBg = 0x39; break;
  3057. case '003300': $colorIdxBg = 0x3A; break;
  3058. case '333300': $colorIdxBg = 0x3B; break;
  3059. case '993300': $colorIdxBg = 0x3C; break;
  3060. case '993366': $colorIdxBg = 0x3D; break;
  3061. case '333399': $colorIdxBg = 0x3E; break;
  3062. case '333333': $colorIdxBg = 0x3F; break;
  3063. default: $colorIdxBg = 0x41; break;
  3064. }
  3065. // Fg Color
  3066. switch ($conditional->getStyle()->getFill()->getEndColor()->getRGB()) {
  3067. case '000000': $colorIdxFg = 0x08; break;
  3068. case 'FFFFFF': $colorIdxFg = 0x09; break;
  3069. case 'FF0000': $colorIdxFg = 0x0A; break;
  3070. case '00FF00': $colorIdxFg = 0x0B; break;
  3071. case '0000FF': $colorIdxFg = 0x0C; break;
  3072. case 'FFFF00': $colorIdxFg = 0x0D; break;
  3073. case 'FF00FF': $colorIdxFg = 0x0E; break;
  3074. case '00FFFF': $colorIdxFg = 0x0F; break;
  3075. case '800000': $colorIdxFg = 0x10; break;
  3076. case '008000': $colorIdxFg = 0x11; break;
  3077. case '000080': $colorIdxFg = 0x12; break;
  3078. case '808000': $colorIdxFg = 0x13; break;
  3079. case '800080': $colorIdxFg = 0x14; break;
  3080. case '008080': $colorIdxFg = 0x15; break;
  3081. case 'C0C0C0': $colorIdxFg = 0x16; break;
  3082. case '808080': $colorIdxFg = 0x17; break;
  3083. case '9999FF': $colorIdxFg = 0x18; break;
  3084. case '993366': $colorIdxFg = 0x19; break;
  3085. case 'FFFFCC': $colorIdxFg = 0x1A; break;
  3086. case 'CCFFFF': $colorIdxFg = 0x1B; break;
  3087. case '660066': $colorIdxFg = 0x1C; break;
  3088. case 'FF8080': $colorIdxFg = 0x1D; break;
  3089. case '0066CC': $colorIdxFg = 0x1E; break;
  3090. case 'CCCCFF': $colorIdxFg = 0x1F; break;
  3091. case '000080': $colorIdxFg = 0x20; break;
  3092. case 'FF00FF': $colorIdxFg = 0x21; break;
  3093. case 'FFFF00': $colorIdxFg = 0x22; break;
  3094. case '00FFFF': $colorIdxFg = 0x23; break;
  3095. case '800080': $colorIdxFg = 0x24; break;
  3096. case '800000': $colorIdxFg = 0x25; break;
  3097. case '008080': $colorIdxFg = 0x26; break;
  3098. case '0000FF': $colorIdxFg = 0x27; break;
  3099. case '00CCFF': $colorIdxFg = 0x28; break;
  3100. case 'CCFFFF': $colorIdxFg = 0x29; break;
  3101. case 'CCFFCC': $colorIdxFg = 0x2A; break;
  3102. case 'FFFF99': $colorIdxFg = 0x2B; break;
  3103. case '99CCFF': $colorIdxFg = 0x2C; break;
  3104. case 'FF99CC': $colorIdxFg = 0x2D; break;
  3105. case 'CC99FF': $colorIdxFg = 0x2E; break;
  3106. case 'FFCC99': $colorIdxFg = 0x2F; break;
  3107. case '3366FF': $colorIdxFg = 0x30; break;
  3108. case '33CCCC': $colorIdxFg = 0x31; break;
  3109. case '99CC00': $colorIdxFg = 0x32; break;
  3110. case 'FFCC00': $colorIdxFg = 0x33; break;
  3111. case 'FF9900': $colorIdxFg = 0x34; break;
  3112. case 'FF6600': $colorIdxFg = 0x35; break;
  3113. case '666699': $colorIdxFg = 0x36; break;
  3114. case '969696': $colorIdxFg = 0x37; break;
  3115. case '003366': $colorIdxFg = 0x38; break;
  3116. case '339966': $colorIdxFg = 0x39; break;
  3117. case '003300': $colorIdxFg = 0x3A; break;
  3118. case '333300': $colorIdxFg = 0x3B; break;
  3119. case '993300': $colorIdxFg = 0x3C; break;
  3120. case '993366': $colorIdxFg = 0x3D; break;
  3121. case '333399': $colorIdxFg = 0x3E; break;
  3122. case '333333': $colorIdxFg = 0x3F; break;
  3123. default: $colorIdxFg = 0x40; break;
  3124. }
  3125. $dataBlockFill = pack('v', $blockFillPatternStyle);
  3126. $dataBlockFill .= pack('v', $colorIdxFg | ($colorIdxBg << 7));
  3127. }
  3128. if($bFormatProt == 1){
  3129. $dataBlockProtection = 0;
  3130. if($conditional->getStyle()->getProtection()->getLocked() == PHPExcel_Style_Protection::PROTECTION_PROTECTED){
  3131. $dataBlockProtection = 1;
  3132. }
  3133. if($conditional->getStyle()->getProtection()->getHidden() == PHPExcel_Style_Protection::PROTECTION_PROTECTED){
  3134. $dataBlockProtection = 1 << 1;
  3135. }
  3136. }
  3137. $data = pack('CCvvVv', $type, $operatorType, $szValue1, $szValue2, $flags, 0x0000);
  3138. if($bFormatFont == 1){ // Block Formatting : OK
  3139. $data .= $dataBlockFont;
  3140. }
  3141. if($bFormatAlign == 1){
  3142. $data .= $dataBlockAlign;
  3143. }
  3144. if($bFormatBorder == 1){
  3145. $data .= $dataBlockBorder;
  3146. }
  3147. if($bFormatFill == 1){ // Block Formatting : OK
  3148. $data .= $dataBlockFill;
  3149. }
  3150. if($bFormatProt == 1){
  3151. $data .= $dataBlockProtection;
  3152. }
  3153. if(!is_null($operand1)){
  3154. $data .= $operand1;
  3155. }
  3156. if(!is_null($operand2)){
  3157. $data .= $operand2;
  3158. }
  3159. $header = pack('vv', $record, strlen($data));
  3160. $this->_append($header . $data);
  3161. }
  3162. /**
  3163. * Write CFHeader record
  3164. */
  3165. private function _writeCFHeader(){
  3166. $record = 0x01B0; // Record identifier
  3167. $length = 0x0016; // Bytes to follow
  3168. $numColumnMin = null;
  3169. $numColumnMax = null;
  3170. $numRowMin = null;
  3171. $numRowMax = null;
  3172. $arrConditional = array();
  3173. foreach ($this->_phpSheet->getConditionalStylesCollection() as $cellCoordinate => $conditionalStyles) {
  3174. foreach ($conditionalStyles as $conditional) {
  3175. if($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_EXPRESSION
  3176. || $conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CELLIS){
  3177. if(!in_array($conditional->getHashCode(), $arrConditional)){
  3178. $arrConditional[] = $conditional->getHashCode();
  3179. }
  3180. // Cells
  3181. $arrCoord = PHPExcel_Cell::coordinateFromString($cellCoordinate);
  3182. if(!is_numeric($arrCoord[0])){
  3183. $arrCoord[0] = PHPExcel_Cell::columnIndexFromString($arrCoord[0]);
  3184. }
  3185. if(is_null($numColumnMin) || ($numColumnMin > $arrCoord[0])){
  3186. $numColumnMin = $arrCoord[0];
  3187. }
  3188. if(is_null($numColumnMax) || ($numColumnMax < $arrCoord[0])){
  3189. $numColumnMax = $arrCoord[0];
  3190. }
  3191. if(is_null($numRowMin) || ($numRowMin > $arrCoord[1])){
  3192. $numRowMin = $arrCoord[1];
  3193. }
  3194. if(is_null($numRowMax) || ($numRowMax < $arrCoord[1])){
  3195. $numRowMax = $arrCoord[1];
  3196. }
  3197. }
  3198. }
  3199. }
  3200. $needRedraw = 1;
  3201. $cellRange = pack('vvvv', $numRowMin-1, $numRowMax-1, $numColumnMin-1, $numColumnMax-1);
  3202. $header = pack('vv', $record, $length);
  3203. $data = pack('vv', count($arrConditional), $needRedraw);
  3204. $data .= $cellRange;
  3205. $data .= pack('v', 0x0001);
  3206. $data .= $cellRange;
  3207. $this->_append($header . $data);
  3208. }
  3209. }