[Sommaire] [Imprimer]

   1  <?php
   2  
   3  /*
   4   * Copyleft 2002 Johann Hanne
   5   *
   6   * This is free software; you can redistribute it and/or
   7   * modify it under the terms of the GNU Lesser General Public
   8   * License as published by the Free Software Foundation; either
   9   * version 2.1 of the License, or (at your option) any later version.
  10   *
  11   * This software is distributed in the hope that it will be useful,
  12   * but WITHOUT ANY WARRANTY; without even the implied warranty of
  13   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  14   * Lesser General Public License for more details.
  15   *
  16   * You should have received a copy of the GNU Lesser General Public
  17   * License along with this software; if not, write to the
  18   * Free Software Foundation, Inc., 59 Temple Place,
  19   * Suite 330, Boston, MA  02111-1307 USA
  20   */
  21  
  22  /*
  23   * This is the Spreadsheet::WriteExcel Perl package ported to PHP
  24   * Spreadsheet::WriteExcel was written by John McNamara, jmcnamara@cpan.org
  25   */
  26  
  27  /*
  28   * Converts numeric $row/$col notation to an Excel cell reference string in
  29   * A1 notation.
  30   */
  31  function xl_rowcol_to_cell($row, $col, $row_abs=false, $col_abs=false) {
  32  
  33      $row_abs = $row_abs ? '$' : '';
  34      $col_abs = $col_abs ? '$' : '';
  35  
  36      $int  = floor($col / 26);
  37      $frac = $col % 26;
  38  
  39      $chr1 = ''; // Most significant character in AA1
  40  
  41      if ($int > 0) {
  42          $chr1 = chr(ord('A') + $int - 1);
  43      }
  44  
  45      $chr2 = chr(ord('A') + $frac);
  46  
  47      // Zero index to 1-index
  48      $row++;
  49  
  50      return $col_abs.$chr1.$chr2.$row_abs.$row;
  51  }
  52  
  53  /*
  54   * Converts an Excel cell reference string in A1 notation
  55   * to numeric $row/$col notation.
  56   *
  57   * Returns: array($row, $col, $row_absolute, $col_absolute)
  58   *
  59   * The $row_absolute and $col_absolute parameters aren't documented because
  60   * they are mainly used internally and aren't very useful to the user.
  61   */
  62  function xl_cell_to_rowcol($cell) {
  63  
  64      preg_match('/(\$?)([A-I]?[A-Z])(\$?)(\d+)/', $cell, $reg);
  65  
  66      $col_abs = ($reg[1] == "") ? 0 : 1;
  67      $col     = $reg[2];
  68      $row_abs = ($reg[3] == "") ? 0 : 1;
  69      $row     = $reg[4];
  70  
  71      // Convert base26 column string to number
  72      // All your Base are belong to us.
  73      $chars  = preg_split('//', $col, -1, PREG_SPLIT_NO_EMPTY);
  74      $expn   = 0;
  75      $col    = 0;
  76  
  77      while (sizeof($chars)>0) {
  78          $char = array_pop($chars); // Least significant character first
  79          $col += (ord($char) - ord('A') + 1) * pow(26, $expn);
  80          $expn++;
  81      }
  82  
  83      // Convert 1-index to zero-index
  84      $row--;
  85      $col--;
  86  
  87      return array($row, $col, $row_abs, $col_abs);
  88  }
  89  
  90  /*
  91   * Increments the row number of an Excel cell reference string
  92   * in A1 notation.
  93   * For example C4 to C5
  94   *
  95   * Returns: a cell reference string in A1 notation.
  96   */
  97  function xl_inc_row($cell) {
  98      list($row, $col, $row_abs, $col_abs) = xl_cell_to_rowcol($cell);
  99      return xl_rowcol_to_cell(++$row, $col, $row_abs, $col_abs);
 100  }
 101  
 102  /*
 103   * Decrements the row number of an Excel cell reference string
 104   * in A1 notation.
 105   * For example C4 to C3
 106   *
 107   * Returns: a cell reference string in A1 notation.
 108   */
 109  function xl_dec_row($cell) {
 110      list($row, $col, $row_abs, $col_abs) = xl_cell_to_rowcol($cell);
 111      return xl_rowcol_to_cell(--$row, $col, $row_abs, $col_abs);
 112  }
 113  
 114  /*
 115   * Increments the column number of an Excel cell reference string
 116   * in A1 notation.
 117   * For example C3 to D3
 118   *
 119   * Returns: a cell reference string in A1 notation.
 120   */
 121  function xl_inc_col($cell) {
 122      list($row, $col, $row_abs, $col_abs) = xl_cell_to_rowcol($cell);
 123      return xl_rowcol_to_cell($row, ++$col, $row_abs, $col_abs);
 124  }
 125  
 126  /*
 127   * Decrements the column number of an Excel cell reference string
 128   * in A1 notation.
 129   * For example C3 to B3
 130   *
 131   * Returns: a cell reference string in A1 notation.
 132   */
 133  function xl_dec_col($cell) {
 134      list($row, $col, $row_abs, $col_abs) = xl_cell_to_rowcol($cell);
 135      return xl_rowcol_to_cell($row, --$col, $row_abs, $col_abs);
 136  }
 137  
 138  function xl_date_list($year, $month=1, $day=1,
 139                        $hour=0, $minute=0, $second=0) {
 140  
 141      $monthdays=array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31);
 142  
 143      // Leap years since 1900 (year is dividable by 4)
 144      $leapyears=floor(($year-1900)/4);
 145  
 146      // Non-leap years since 1900 (year is dividable by 100)
 147      $nonleapyears=floor(($year-1900)/100);
 148  
 149      // Non-non-leap years since 1900 (year is dividable by 400)
 150      // (Yes, it MUST be "1600", not "1900")
 151      $nonnonleapyears=floor(($year-1600)/400);
 152  
 153      // Don't count the leap day of the specified year if it didn't
 154      // happen yet (i.e. before 1 March)
 155      //
 156      // Please note that $leapyears becomes -1 for dates before 1 March 1900;
 157      // this is not logical, but later we will add a day for Excel's
 158      // phantasie leap day in 1900 without checking if the date is actually
 159      // after 28 February 1900; so these two logic errors "neutralize"
 160      // each other
 161      if ($year%4==0 && $month<3) {
 162        $leapyears--;
 163      }
 164  
 165      $days=365*($year-1900)+$leapyears-$nonleapyears+$nonnonleapyears;
 166  
 167      for ($c=1;$c<$month;$c++) {
 168        $days+=$monthdays[$c-1];
 169      }
 170  
 171      // Excel actually wants the days since 31 December 1899, not since
 172      // 1 January 1900; this will also add this extra day
 173      $days+=$day;
 174  
 175      // Excel treats 1900 erroneously as a leap year, so we must
 176      // add one day
 177      //
 178      // Please note that we DON'T have to check if the date is after
 179      // 28 February 1900, because for such dates $leapyears is -1
 180      // (see above)
 181      $days++;
 182  
 183      return (float)($days+($hour*3600+$minute*60+$second)/86400);
 184  }
 185  
 186  function xl_parse_time($time) {
 187  
 188      if (preg_match('/(\d{1,2}):(\d\d):?((?:\d\d)(?:\.\d+)?)?(?:\s+)?(am|pm)?/i', $time, $reg)) {
 189  
 190          $hours       = $reg[1];
 191          $minutes     = $reg[2];
 192          $seconds     = $reg[3] || 0;
 193          $meridian    = strtolower($reg[4]) || '';
 194  
 195          // Normalise midnight and midday
 196          if ($hours == 12 && $meridian != '') {
 197              $hours = 0;
 198          }
 199  
 200          // Add 12 hours to the pm times. Note: 12.00 pm has been set to 0.00.
 201          if ($meridian == 'pm') {
 202              $hours += 12;
 203          }
 204  
 205          // Calculate the time as a fraction of 24 hours in seconds
 206          return (float)(($hours*3600+$minutes*60+$seconds)/86400);
 207  
 208      } else {
 209          return false; // Not a valid time string
 210      }
 211  }
 212  
 213  /*
 214   * Automagically converts almost any date/time string to an Excel date.
 215   * This function will always only be as good as strtotime() is.
 216   */
 217  function xl_parse_date($date) {
 218  
 219      $unixtime=strtotime($date);
 220  
 221      $year=date("Y", $unixtime);
 222      $month=date("m", $unixtime);
 223      $day=date("d", $unixtime);
 224      $hour=date("H", $unixtime);
 225      $minute=date("i", $unixtime);
 226      $second=date("s", $unixtime);
 227  
 228      // Convert to Excel date
 229      return xl_date_list($year, $month, $day, $hour, $minute, $second);
 230  }
 231  
 232  /*
 233   * Dummy function to be "compatible" to Spreadsheet::WriteExcel
 234   */
 235  function xl_parse_date_init() {
 236      // Erm... do nothing...
 237      // strtotime() doesn't require anything to be initialized
 238      // (do not ask me how to set the timezone...)
 239  }
 240  
 241  /*
 242   * xl_decode_date_EU() and xl_decode_date_US() are mapped
 243   * to xl_parse_date(); there seems to be no PHP function that
 244   * differentiates between EU and US dates; I've never seen
 245   * somebody using dd/mm/yyyy anyway, it always should be one of:
 246   * - yyyy-mm-dd (international)
 247   * - dd.mm.yyyy (european)
 248   * - mm/dd/yyyy (english/US/british?)
 249  */
 250  
 251  function xl_decode_date_EU($date) {
 252      return xl_parse_date($date);
 253  }
 254  
 255  function xl_decode_date_US($date) {
 256      return xl_parse_date($date);
 257  }
 258  
 259  function xl_date_1904($exceldate) {
 260  
 261      if ($exceldate < 1462) {
 262          // date is before 1904
 263          $exceldate = 0;
 264      } else {
 265          $exceldate -= 1462;
 266      }
 267  
 268      return $exceldate;
 269  }
 270  
 271  ?>