libraries/export/xls.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Set of functions used to build XLS dumps of tables
   5  *
   6  * @version $Id$
   7  */
   8
   9 /**
  10  *
  11  */
  12 // Check if we have native MS Excel export using PEAR class Spreadsheet_Excel_Writer
  13 if (!empty($GLOBALS['cfg']['TempDir'])) {
  14     @include_once 'Spreadsheet/Excel/Writer.php';
  15     if (class_exists('Spreadsheet_Excel_Writer')) {
  16         $xls = TRUE;
  17     } else {
  18         $xls = FALSE;
  19     }
  20 } else {
  21     $xls = FALSE;
  22 }
  23
  24 if ($xls) {
  25
  26     if (isset($plugin_list)) {
  27         $plugin_list['xls'] = array(
  28             'text' => 'strStrucNativeExcel',
  29             'extension' => 'xls',
  30             'mime_type' => 'application/vnd.ms-excel',
  31             'force_file' => true,
  32             'options' => array(
  33                 array('type' => 'text', 'name' => 'null', 'text' => 'strReplaceNULLBy'),
  34                 array('type' => 'bool', 'name' => 'columns', 'text' => 'strPutColNames'),
  35                 array('type' => 'hidden', 'name' => 'data'),
  36                 ),
  37             'options_text' => 'strOptions',
  38             );
  39     } else {
  40
  41 /**
  42  * Set of functions used to build MS Excel dumps of tables
  43  */
  44
  45 /**
  46  * Outputs comment
  47  *
  48  * @param   string      Text of comment
  49  *
  50  * @return  bool        Whether it suceeded
  51  */
  52 function PMA_exportComment($text)
  53 {
  54     return TRUE;
  55 }
  56
  57 /**
  58  * Outputs export footer
  59  *
  60  * @return  bool        Whether it suceeded
  61  *
  62  * @access  public
  63  */
  64 function PMA_exportFooter()
  65 {
  66     global $workbook;
  67     global $tmp_filename;
  68
  69     $res = $workbook->close();
  70     if (PEAR::isError($res)) {
  71         echo $res->getMessage();
  72         return FALSE;
  73     }
  74     if (!PMA_exportOutputHandler(file_get_contents($tmp_filename))) {
  75         return FALSE;
  76     }
  77     unlink($tmp_filename);
  78
  79     return TRUE;
  80 }
  81
  82 /**
  83  * Outputs export header
  84  *
  85  * @return  bool        Whether it suceeded
  86  *
  87  * @access  public
  88  */
  89 function PMA_exportHeader()
  90 {
  91     global $workbook;
  92     global $tmp_filename;
  93
  94     if (empty($GLOBALS['cfg']['TempDir'])) {
  95         return FALSE;
  96     }
  97     $tmp_filename = tempnam(realpath($GLOBALS['cfg']['TempDir']), 'pma_xls_');
  98     $workbook = new Spreadsheet_Excel_Writer($tmp_filename);
  99
 100     return TRUE;
 101 }
 102
 103 /**
 104  * Outputs database header
 105  *
 106  * @param   string      Database name
 107  *
 108  * @return  bool        Whether it suceeded
 109  *
 110  * @access  public
 111  */
 112 function PMA_exportDBHeader($db)
 113 {
 114     return TRUE;
 115 }
 116
 117 /**
 118  * Outputs database footer
 119  *
 120  * @param   string      Database name
 121  *
 122  * @return  bool        Whether it suceeded
 123  *
 124  * @access  public
 125  */
 126 function PMA_exportDBFooter($db)
 127 {
 128     return TRUE;
 129 }
 130
 131 /**
 132  * Outputs create database database
 133  *
 134  * @param   string      Database name
 135  *
 136  * @return  bool        Whether it suceeded
 137  *
 138  * @access  public
 139  */
 140 function PMA_exportDBCreate($db)
 141 {
 142     return TRUE;
 143 }
 144
 145 /**
 146  * Outputs the content of a table in CSV format
 147  *
 148  * @param   string      the database name
 149  * @param   string      the table name
 150  * @param   string      the end of line sequence
 151  * @param   string      the url to go back in case of error
 152  * @param   string      SQL query for obtaining data
 153  *
 154  * @return  bool        Whether it suceeded
 155  *
 156  * @access  public
 157  */
 158 function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
 159 {
 160     global $what;
 161     global $workbook;
 162
 163     $workbook->setTempDir(realpath($GLOBALS['cfg']['TempDir']));
 164
 165     // Gets the data from the database
 166     $result      = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
 167     $fields_cnt  = PMA_DBI_num_fields($result);
 168
 169     $row = PMA_DBI_fetch_row($result);
 170     for ($sheetIndex = 0; ; $sheetIndex++) {
 171         // Maximum sheet name length is 31 chars - leave 2 for numeric index
 172         $sheetName = substr($table, 0, 29) . ($sheetIndex > 0 ? $sheetIndex : '');
 173         $worksheet =& $workbook->addWorksheet($sheetName);
 174         $rowIndex = 0;
 175
 176         // If required, get fields name at the first line
 177         if (isset($GLOBALS['xls_columns']) && $GLOBALS['xls_columns']) {
 178             for ($i = 0; $i < $fields_cnt; $i++) {
 179                 $worksheet->write(0, $i, stripslashes(PMA_DBI_field_name($result, $i)));
 180             } // end for
 181             $worksheet->repeatRows($rowIndex);
 182             $worksheet->freezePanes(array($rowIndex + 1, 0, $rowIndex + 1, 0));
 183             $rowIndex++;
 184         } // end if
 185
 186         // Format the data (max 65536 rows per worksheet)
 187         while ($rowIndex < 65536 && $row) {
 188             set_time_limit(0);
 189             for ($j = 0; $j < $fields_cnt; $j++) {
 190                 if (!isset($row[$j]) || is_null($row[$j])) {
 191                     $worksheet->write($rowIndex, $j, $GLOBALS['xls_null']);
 192                 } elseif ($row[$j] == '0' || $row[$j] != '') {
 193                     /**
 194                      * @todo we should somehow handle character set here!
 195                      */
 196                     $worksheet->write($rowIndex, $j, $row[$j]);
 197                 } else {
 198                     $worksheet->write($rowIndex, $j, '');
 199                 }
 200             } // end for
 201             $rowIndex++;
 202             $row = PMA_DBI_fetch_row($result);
 203         } // end while
 204         if (!$row) {
 205             break;
 206         }
 207     } // end for
 208     PMA_DBI_free_result($result);
 209
 210     return TRUE;
 211 }
 212
 213     }
 214 }
 215 ?>