reporting/includes/ExcelWriterXML.php

   1 <?php
   2 /**
   3  * ExcelWriterXML Package
   4  * Used the schema documentation from Microsoft
   5  * @link http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx
   6  * @package ExcelWriterXML
   7  */
   8
   9 /**
  10  * Includes the other class file to create Sheets
  11  */
  12 include('ExcelWriterXML_Sheet.php');
  13 /**
  14  * Includes the other class file to create Styles
  15  */
  16 include('ExcelWriterXML_Style.php');
  17
  18 /**
  19  * Class for generating the initial Excel XML document
  20  * <code>
  21  * <?php
  22  * $xml = new ExcelWriterXML;
  23  * $format = $xml->addStyle('StyleHeader');
  24  * $format->fontBold();
  25  * $sheet = $xml->addSheet('Test Sheet');
  26  * $sheet->writeString (1,1,'Header1','StyleHeader');
  27  * $sheet->writeString(2,1,'My String');
  28  * $xml->sendHeaders();
  29  * $xml->writeData();
  30  * ?>
  31  * </code>
  32  * @link http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx
  33  * @author Robert F Greer
  34  * @version 1.0
  35  * @package ExcelWriterXML
  36  */
  37 class ExcelWriterXML
  38 {
  39         // Private Variables //
  40         var $styles = array();
  41         var $formatErrors = array();
  42         var $sheets = array();
  43         var $showErrorSheet = false;
  44         var $overwriteFile = false;
  45         var $docFileName;
  46         var $docTitle;
  47         var $docSubject;
  48         var $docAuthor;
  49         var $docCreated;
  50         var $docManager;
  51         var $docCompany;
  52         var $docVersion = 11.9999;
  53         var $charSet = "utf-8"; // 2009-02-28 Added by Joe Hunt, FA
  54         ///////////////////////
  55
  56         /**
  57      * Constructor for the ExcelWriterXML class.
  58      * A default style is created, a filename is generated (if not supplied) and
  59      * the create time of the document is stored.
  60      * @param string $fileName This is the filename that will be passed to the
  61      * browser.  If not present it will default to "file.xml"
  62      * @return ExcelWriterXML Instance of the class
  63      */
  64         function ExcelWriterXML($fileName = 'file.xml')
  65         {
  66                 // Add default style
  67                 $style = $this->addStyle('Default');
  68                 $style->name('Normal');
  69                 $style->alignVertical('Bottom');
  70
  71                 if ($fileName == '')
  72                 {
  73                         $fileName = 'file.xml';
  74                         $this->_addError(__FUNCTION__,'File name was blank, default to "file.xml"');
  75                 }
  76
  77                 $this->docFileName = $fileName;
  78                 $this->docCreated = date('Y-m-d').'T'.date('H:i:s').'Z';
  79         }
  80
  81         /**
  82          * Whether or not to overwrite a file (when writing to disk)
  83          * @param boolean $overwrite True or False
  84          */
  85         function overwriteFile($overwrite = true)
  86         {
  87                 if (!is_bool($overwrite))
  88                 {
  89                         $this->overwriteFile = false;
  90                         return;
  91                 }
  92                 else
  93                 {
  94                         $this->overwriteFile = $overwrite;
  95                 }
  96         }
  97
  98         /**
  99          * Whether or not to show the sheet containing the Formatting Errors
 100          * @param boolean $show
 101          */
 102         function showErrorSheet($show = true)
 103         {
 104                 if (!is_bool($show))
 105                 {
 106                         $this->showErrorSheet = true;
 107                         return;
 108                 }
 109                 else{
 110                         $this->showErrorSheet = $show;
 111                 }
 112         }
 113
 114         /**
 115          * Adds a format error.  When the document is generated if there are any
 116          * errors they will be listed on a seperate sheet.
 117          * @param string $function The name of the function that was called
 118          * @param string $message Details of the error
 119          */
 120         function _addError($function, $message)
 121         {
 122                 $tmp = array(
 123                         'function'      => $function,
 124                         'message'       => $message,
 125                 );
 126                 $this->formatErrors[] = $tmp;
 127         }
 128
 129         /**
 130      * Sends the HTML headers to the client.
 131      * This is only necessary if the XML doc is to be delivered from the server
 132      * to the browser.
 133      */
 134         function sendHeaders()
 135         {
 136                 header('content-type: text/xml');
 137                 header('Content-Disposition: attachment; filename="'.$this->docFileName.'"');
 138                 header('Expires: 0');
 139                 header('Cache-Control: must-revalidate, post-check=0,pre-check=0');
 140                 header('Pragma: public');
 141         }
 142
 143         /**
 144      * Gets the default style that was created by the contructor.
 145      * This is used when modifications to the default style are required.
 146      * @return ExcelWriterXML_Style Reference to a style class
 147      */
 148         function getDefaultStyle()
 149         {
 150                 return($this->styles[0]);
 151         }
 152
 153         /**
 154      * Creates a new style for the spreadsheet
 155      * @param string $id The name of the style to be referenced within the
 156      * spreadsheet
 157      * @return ExcelWriterXML_Style Reference to a new style class
 158      * @todo Parameter validation for styles, can't have duplicates
 159      */
 160         /*
 161         function addStyle($id)
 162         {
 163                 $style =& new ExcelWriterXML_Style($id);
 164                 $this->styles[] = $style;
 165                 return ($style);
 166         }
 167         */
 168         /**
 169      * Creates a new style within the spreadsheet.
 170      * Styles cannot have the same name as any other style. If a style has the
 171      * same name as another style then it will follow the default naming
 172      * convention as if $id was null
 173      * @param string $id The name of the style.  If left blank then the style
 174      * will default to "CustomStyle" + n (e.g. "CustomStyle1")
 175      * @return ExcelWriterXML_Style Reference to a new style class
 176      */
 177         function addStyle($id = null)
 178         {
 179                 static $styleNum = 1;
 180                 if (trim($id) == '')
 181                         $id = null;
 182
 183                 if ($id == null)
 184                 {
 185                         $id = 'CustomStyle'.$styleNum;
 186                         $styleNum++;
 187                         //$this->_addError(__FUNCTION__,'Style name was blank, renamed to "'.$id.'"');
 188                 }
 189
 190                 while (!$this->_checkStyleID($id))
 191                 {
 192                         $old_id = $id;
 193                         $id = 'CustomStyle'.$styleNum;
 194                         $this->_addError(__FUNCTION__,'Style name was duplicate ("'.$old_id.'"), renamed to "'.$id.'"');
 195                         $styleNum++;
 196                 }
 197
 198                 $style =& new ExcelWriterXML_Style($id);
 199                 $this->styles[] = $style;
 200                 return ($style);
 201         }
 202
 203         /**
 204      * Creates a new sheet within the spreadsheet
 205      * At least one sheet is required.
 206      * Additional sheets cannot have the same name as any other sheet.
 207      * If a sheet has the same name as another sheet then it will follow the
 208      * default naming convention as if $id was null
 209      * @param string $id The name of the sheet.  If left blank then the sheet
 210      * will default to "Sheet" + n (e.g. "Sheet1")
 211      * @return ExcelWriterXML_Sheet Reference to a new sheet class
 212      */
 213         function addSheet($id = null)
 214         {
 215                 static $sheetNum = 1;
 216                 if (trim($id) == '')
 217                         $id = null;
 218
 219                 if ($id == null)
 220                 {
 221                         $id = 'Sheet'.$sheetNum;
 222                         $sheetNum++;
 223                         $this->_addError(__FUNCTION__,'Sheet name was blank, renamed to "'.$id.'"');
 224                 }
 225
 226                 while (!$this->_checkSheetID($id))
 227                 {
 228                         $old_id = $id;
 229                         $id = 'Sheet'.$sheetNum;
 230                         $this->_addError(__FUNCTION__,'Sheet name was duplicate ("'.$old_id.'"), renamed to "'.$id.'"');
 231                         $sheetNum++;
 232                 }
 233
 234                 $sheet =& new ExcelWriterXML_Sheet($id);
 235                 $this->sheets[] = $sheet;
 236                 return ($sheet);
 237         }
 238
 239         /**
 240          * Checks whether a proposed Sheet ID has already been used
 241          * @param string $id The sheet id to be checked
 242          * @return boolean True if the id is unique, false otherwise
 243          */
 244         function _checkSheetID($id){
 245                 foreach($this->sheets as $sheet)
 246                 {
 247                         $sheetID = $sheet->getID();
 248                         if ($id == $sheetID)
 249                         {
 250                                 return false;
 251                         }
 252                 }
 253                 return true;
 254         }
 255
 256         /**
 257          * Checks whether a proposed Style ID has already been used
 258          * @param string $id The style id to be checked
 259          * @return boolean True if the id is unique, false otherwise
 260          */
 261         function _checkStyleID($id)
 262         {
 263                 foreach($this->styles as $style)
 264                 {
 265                         $styleID = $style->getID();
 266                         if ($id == $styleID)
 267                         {
 268                                 return false;
 269                         }
 270                 }
 271                 return true;
 272         }
 273
 274         // 2009-02-28 Added by Joe Hunt, FA
 275     function setCharSet($charset)
 276     {
 277                 $this->charSet = $charset;
 278         }
 279         /**
 280      * Writes the XML data
 281      * @param string $target If left null the function will output to STD OUT
 282      * (e. g. browser or console)
 283      */
 284         function writeData($target = null)
 285         {
 286                 $docTitle = '';
 287                 $docSubject = '';
 288                 $docAuthor = '';
 289                 $docCreated = '';
 290                 $docManager = '';
 291                 $docCompany = '';
 292                 $docVersion = 12;
 293
 294                 $errors = false;
 295
 296                 if ($this->showErrorSheet == true)
 297                 {
 298                         $format = $this->addStyle('formatErrorsHeader');
 299                         $format->fontBold();
 300                         $format->bgColor('red');
 301                 }
 302
 303                 if (!empty($this->docTitle))
 304                         $docTitle = '<Title>'.htmlspecialchars($this->docTitle).'</Title>'."\r";
 305                 if (!empty($this->docSubject))
 306                         $docSubject = '<Subject>'.htmlspecialchars($this->docSubject).'</Subject>'."\r";
 307                 if (!empty($this->docAuthor))
 308                         $docAuthor = '<Author>'.htmlspecialchars($this->docAuthor).'</Author>'."\r";
 309                 if (!empty($this->docCreated))
 310                         $docCreated = '<Created>'.htmlspecialchars($this->docCreated).'</Created>'."\r";
 311                 if (!empty($this->docManager))
 312                         $docManager = '<Manager>'.htmlspecialchars($this->docManager).'</Manager>'."\r";
 313                 if (!empty($this->docCompany))
 314                         $docCompany = '<Company>'.htmlspecialchars($this->docCompany).'</Company>'."\r";
 315                         //$docCompany = '<Company>'.$this->docCompany.'</Company>'."\r";
 316
 317                 $xml = '<?xml version="1.0" encoding="'.$this->charSet.'"?>'."\r";  // 2009-02-28 Added by Joe Hunt, FA
 318                 $xml .= '<?mso-application progid="Excel.Sheet"?>'."\r";
 319                 $xml .= '<Workbook
 320                         xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 321                         xmlns:o="urn:schemas-microsoft-com:office:office"
 322                         xmlns:x="urn:schemas-microsoft-com:office:excel"
 323                         xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 324                         xmlns:html="http://www.w3.org/TR/REC-html40">'."\r";
 325                 $xml .= '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">'."\r";
 326                 if (!empty($this->docTitle))
 327                         $xml .= '       '.$docTitle;
 328                 if (!empty($this->docSubject))
 329                         $xml .= '       '.$docSubject;
 330                 if (!empty($this->docAuthor))
 331                         $xml .= '       '.$docAuthor;
 332                 if (!empty($this->docCreated))
 333                         $xml .= '       '.$docCreated;
 334                 if (!empty($this->docManager))
 335                         $xml .= '       '.$docManager;
 336                 if (!empty($this->docCompany))
 337                         $xml .= '       '.$docCompany;
 338                 $xml .= '       <Version>'.$this->docVersion.'</Version>'."\r";
 339                 $xml .= '</DocumentProperties>'."\r";
 340                 $xml .= '<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel" />'."\r";
 341                 $xml .= '<Styles>'."\r";
 342                 foreach($this->styles as $style)
 343                 {
 344                         $xml .= $style->getStyleXML();
 345                         if (count($style->getErrors()) > 0)
 346                         {
 347                                 $errors = true;
 348                         }
 349                 }
 350                 $xml .= '</Styles>'."\r";
 351                 if (count($this->sheets) == 0)
 352                 {
 353                         $this->addSheet();
 354                 }
 355                 foreach($this->sheets as $sheet)
 356                 {
 357                         $xml .= $sheet->getSheetXML();
 358                         if (count($sheet->getErrors()) > 0)
 359                         {
 360                                 $errors = true;
 361                         }
 362                 }
 363                 if (count($this->formatErrors) > 0)
 364                 {
 365                         $errors = true;
 366                 }
 367
 368                 if ($errors == true && $this->showErrorSheet == true)
 369                 {
 370                         $sheet = $this->addSheet('formatErrors');
 371                         $sheet->cellMerge(1,1,3,0);     // Merge the first three cells across in row 1
 372                         $sheet->writeString(1,1,'Formatting Errors');
 373                         $sheet->writeString(2,1,'Type','formatErrorsHeader');
 374                         $sheet->writeString(2,2,'Function','formatErrorsHeader');
 375                         $sheet->cellWidth(2,1,200);
 376                         $sheet->cellWidth(2,2,200);
 377                         $sheet->cellWidth(2,3,400);
 378                         $sheet->writeString(2,3,'Error Message','formatErrorsHeader');
 379                         $row = 3;
 380                         foreach($this->formatErrors as $error)
 381                         {
 382                                 $function = $error['function'];
 383                                 $message = $error['message'];
 384                                 $sheet->writeString($row,1,'Document');
 385                                 $sheet->writeString($row,2,$function);
 386                                 $sheet->writeString($row,3,$message);
 387                                 $row++;
 388                         }
 389                         foreach($this->styles as $styleObject)
 390                         {
 391                                 $formatErrors = $styleObject->getErrors();
 392                                 $styleID = 'Style='.$styleObject->getID();
 393                                 foreach($formatErrors as $error)
 394                                 {
 395                                         $function = $error['function'];
 396                                         $message = $error['message'];
 397                                         $sheet->writeString($row,1,$styleID);
 398                                         $sheet->writeString($row,2,$function);
 399                                         $sheet->writeString($row,3,$message);
 400                                         $row++;
 401                                 }
 402                         }
 403                         foreach($this->sheets as $sheetObject)
 404                         {
 405                                 $formatErrors = $sheetObject->getErrors();
 406                                 $sheetID = 'Sheet='.$sheetObject->getID();
 407                                 foreach($formatErrors as $error)
 408                                 {
 409                                         $function = $error['function'];
 410                                         $message = $error['message'];
 411                                         $sheet->writeString($row,1,$sheetID);
 412                                         $sheet->writeString($row,2,$function);
 413                                         $sheet->writeString($row,3,$message);
 414                                         $row++;
 415                                 }
 416                         }
 417                         $xml .= $sheet->getSheetXML();
 418                 }
 419
 420
 421                 $xml .= '</Workbook>';
 422
 423                 if ($target == null)
 424                 {
 425                         // We aren't writing this file to disk, so echo back to the client.
 426                         echo $xml;
 427                         return true;
 428                 }
 429                 else
 430                 {
 431                         $fileExists = file_exists($target);
 432                         if ($fileExists == true && $this->overwriteFile == false)
 433                         {
 434                                 die('"'.$target.'" exists and "overwriteFile" is set to "false"');
 435                         }
 436                         $handle = fopen($target, 'w');
 437                         if ($handle)
 438                         {
 439                                 fwrite($handle,$xml);
 440                                 fclose($handle);
 441                                 return true;
 442                         }
 443                         else
 444                         {
 445                                 echo('<br/>Not able to open "'.$target.'" for writing');
 446                                 return false;
 447                         }
 448                 }
 449         }
 450
 451         /**
 452      * Sets the Title of the document
 453      * @param string $title Part of the properties of the document.
 454      */
 455         function docTitle($title = '') { $this->docTitle = $title; }
 456
 457         /**
 458      * Sets the Subject of the document
 459      * @param string $subject Part of the properties of the document.
 460      */
 461         function docSubject($subject = '') { $this->docSubject = $subject; }
 462
 463         /**
 464      * Sets the Author of the document
 465      * @param string $author Part of the properties of the document.
 466      */
 467         function docAuthor($author = '') { $this->docAuthor = $author; }
 468
 469         /**
 470      * Sets the Manager of the document
 471      * @param string $manager Part of the properties of the document.
 472      */
 473         function docManager($manager = '') { $this->docManager = $manager; }
 474
 475         /**
 476      * Sets the Company of the document
 477      * @param string $company Part of the properties of the document.
 478      */
 479         function docCompany($company = '') { $this->docCompany = $company; }
 480 }
 481
 482
 483 ?>