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[$id] = &$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[$id] = &$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                         {
 246                 return !isset($this->sheets[$id]);
 247         }
 248
 249         /**
 250          * Checks whether a proposed Style ID has already been used
 251          * @param string $id The style id to be checked
 252          * @return boolean True if the id is unique, false otherwise
 253          */
 254         function _checkStyleID($id)
 255         {
 256                 return !isset($this->styles[$id]);
 257         }
 258
 259         // 2009-02-28 Added by Joe Hunt, FA
 260     function setCharSet($charset)
 261     {
 262                 $this->charSet = $charset;
 263         }
 264         /**
 265      * Writes the XML data
 266      * @param string $target If left null the function will output to STD OUT
 267      * (e. g. browser or console)
 268      */
 269         function writeData($target = null)
 270         {
 271                 $docTitle = '';
 272                 $docSubject = '';
 273                 $docAuthor = '';
 274                 $docCreated = '';
 275                 $docManager = '';
 276                 $docCompany = '';
 277                 $docVersion = 12;
 278
 279                 $errors = false;
 280
 281                 if ($this->showErrorSheet == true)
 282                 {
 283                         $format =& $this->addStyle('formatErrorsHeader');
 284                         $format->fontBold();
 285                         $format->bgColor('red');
 286                 }
 287                 if (!empty($this->docTitle))
 288                         $docTitle = '<Title>'.htmlspecialchars($this->docTitle).'</Title>'."\r";
 289                 if (!empty($this->docSubject))
 290                         $docSubject = '<Subject>'.htmlspecialchars($this->docSubject).'</Subject>'."\r";
 291                 if (!empty($this->docAuthor))
 292                         $docAuthor = '<Author>'.htmlspecialchars($this->docAuthor).'</Author>'."\r";
 293                 if (!empty($this->docCreated))
 294                         $docCreated = '<Created>'.htmlspecialchars($this->docCreated).'</Created>'."\r";
 295                 if (!empty($this->docManager))
 296                         $docManager = '<Manager>'.htmlspecialchars($this->docManager).'</Manager>'."\r";
 297                 if (!empty($this->docCompany))
 298                         $docCompany = '<Company>'.htmlspecialchars($this->docCompany).'</Company>'."\r";
 299                         //$docCompany = '<Company>'.$this->docCompany.'</Company>'."\r";
 300
 301                 $xml = '<?xml version="1.0" encoding="'.$this->charSet.'"?>'."\r";  // 2009-02-28 Added by Joe Hunt, FA
 302                 $xml .= '<?mso-application progid="Excel.Sheet"?>'."\r";
 303                 $xml .= '<Workbook
 304                         xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 305                         xmlns:o="urn:schemas-microsoft-com:office:office"
 306                         xmlns:x="urn:schemas-microsoft-com:office:excel"
 307                         xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 308                         xmlns:html="http://www.w3.org/TR/REC-html40">'."\r";
 309                 $xml .= '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">'."\r";
 310                 if (!empty($this->docTitle))
 311                         $xml .= '       '.$docTitle;
 312                 if (!empty($this->docSubject))
 313                         $xml .= '       '.$docSubject;
 314                 if (!empty($this->docAuthor))
 315                         $xml .= '       '.$docAuthor;
 316                 if (!empty($this->docCreated))
 317                         $xml .= '       '.$docCreated;
 318                 if (!empty($this->docManager))
 319                         $xml .= '       '.$docManager;
 320                 if (!empty($this->docCompany))
 321                         $xml .= '       '.$docCompany;
 322                 $xml .= '       <Version>'.$this->docVersion.'</Version>'."\r";
 323                 $xml .= '</DocumentProperties>'."\r";
 324                 $xml .= '<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel" />'."\r";
 325                 $xml .= '<Styles>'."\r";
 326                 foreach($this->styles as $style)
 327                 {
 328                         $xml .= $style->getStyleXML();
 329                         if (count($style->getErrors()) > 0)
 330                         {
 331                                 $errors = true;
 332                         }
 333                 }
 334                 $xml .= '</Styles>'."\r";
 335                 if (count($this->sheets) == 0)
 336                 {
 337                         $this->addSheet();
 338                 }
 339                 foreach($this->sheets as $sheet)
 340                 {
 341                         $xml .= $sheet->getSheetXML();
 342                         if (count($sheet->getErrors()) > 0)
 343                         {
 344                                 $errors = true;
 345                         }
 346                 }
 347                 if (count($this->formatErrors) > 0)
 348                 {
 349                         $errors = true;
 350                 }
 351
 352                 if ($errors == true && $this->showErrorSheet == true)
 353                 {
 354                         $sheet =& $this->addSheet('formatErrors');
 355                         $sheet->cellMerge(1,1,3,0);     // Merge the first three cells across in row 1
 356                         $sheet->writeString(1,1,'Formatting Errors');
 357                         $sheet->writeString(2,1,'Type','formatErrorsHeader');
 358                         $sheet->writeString(2,2,'Function','formatErrorsHeader');
 359                         $sheet->cellWidth(2,1,200);
 360                         $sheet->cellWidth(2,2,200);
 361                         $sheet->cellWidth(2,3,400);
 362                         $sheet->writeString(2,3,'Error Message','formatErrorsHeader');
 363                         $row = 3;
 364                         foreach($this->formatErrors as $error)
 365                         {
 366                                 $function = $error['function'];
 367                                 $message = $error['message'];
 368                                 $sheet->writeString($row,1,'Document');
 369                                 $sheet->writeString($row,2,$function);
 370                                 $sheet->writeString($row,3,$message);
 371                                 $row++;
 372                         }
 373                         foreach($this->styles as $styleObject)
 374                         {
 375                                 $formatErrors = $styleObject->getErrors();
 376                                 $styleID = 'Style='.$styleObject->getID();
 377                                 foreach($formatErrors as $error)
 378                                 {
 379                                         $function = $error['function'];
 380                                         $message = $error['message'];
 381                                         $sheet->writeString($row,1,$styleID);
 382                                         $sheet->writeString($row,2,$function);
 383                                         $sheet->writeString($row,3,$message);
 384                                         $row++;
 385                                 }
 386                         }
 387                         foreach($this->sheets as $sheetObject)
 388                         {
 389                                 $formatErrors = $sheetObject->getErrors();
 390                                 $sheetID = 'Sheet='.$sheetObject->getID();
 391                                 foreach($formatErrors as $error)
 392                                 {
 393                                         $function = $error['function'];
 394                                         $message = $error['message'];
 395                                         $sheet->writeString($row,1,$sheetID);
 396                                         $sheet->writeString($row,2,$function);
 397                                         $sheet->writeString($row,3,$message);
 398                                         $row++;
 399                                 }
 400                         }
 401                         $xml .= $sheet->getSheetXML();
 402                 }
 403
 404
 405                 $xml .= '</Workbook>';
 406
 407                 if ($target == null)
 408                 {
 409                         // We aren't writing this file to disk, so echo back to the client.
 410                         echo $xml;
 411                         return true;
 412                 }
 413                 else
 414                 {
 415                         $fileExists = file_exists($target);
 416                         if ($fileExists == true && $this->overwriteFile == false)
 417                         {
 418                                 die('"'.$target.'" exists and "overwriteFile" is set to "false"');
 419                         }
 420                         $handle = fopen($target, 'w');
 421                         if ($handle)
 422                         {
 423                                 fwrite($handle,$xml);
 424                                 fclose($handle);
 425                                 return true;
 426                         }
 427                         else
 428                         {
 429                                 echo('<br/>Not able to open "'.$target.'" for writing');
 430                                 return false;
 431                         }
 432                 }
 433         }
 434
 435         /**
 436      * Sets the Title of the document
 437      * @param string $title Part of the properties of the document.
 438      */
 439         function docTitle($title = '') { $this->docTitle = $title; }
 440
 441         /**
 442      * Sets the Subject of the document
 443      * @param string $subject Part of the properties of the document.
 444      */
 445         function docSubject($subject = '') { $this->docSubject = $subject; }
 446
 447         /**
 448      * Sets the Author of the document
 449      * @param string $author Part of the properties of the document.
 450      */
 451         function docAuthor($author = '') { $this->docAuthor = $author; }
 452
 453         /**
 454      * Sets the Manager of the document
 455      * @param string $manager Part of the properties of the document.
 456      */
 457         function docManager($manager = '') { $this->docManager = $manager; }
 458
 459         /**
 460      * Sets the Company of the document
 461      * @param string $company Part of the properties of the document.
 462      */
 463         function docCompany($company = '') { $this->docCompany = $company; }
 464 }
 465
 466
 467 ?>