Save-CoCo: 2010

2010年12月24日星期五

將excel的資料轉到mysql，不使用phpMyAdmin及修改phpExcelReader

上次使用phpMyAdmin將Excel的資料轉到mysql，但使用上，手動的部分還很多，如果一次要轉大量檔案，還是很麻煩。包括必須在mysql建立一個對應到excel的table，必須逐一輸入欄位名稱。在網頁輸出後還要複製到文字檔，最後再利用phpMyAdmin匯入。

我改寫 phpExcelReader ，使mysql的表格能自動建立，資料能自動轉入，不必再用到phpMyAdmin。您唯一要做的事就是指定好要轉入的excel檔，其他一切就會自動完成了。

必須注意的是：
1. mysql的資料庫必須自己建立，權限及帳號密碼要自行設定(要有drop 及 create table的權限)。
2. 資料庫及網頁輸入使用utf-8編碼，但excel的檔名是big5碼，所以必須先轉成big5碼再讀檔。
$file_big5 = mb_convert_encoding($file1, 'BIG5', 'UTF-8');
$data->read($file_big5);

3. excel表格的第一行將成為mysql的table的欄位名稱。excel的檔名將成為mysql的表格(table)名稱。如果格式不符就轉不進來了。
4. 所有欄位均是text格式。

先下載 phpExcelReader ，置換壓縮檔中的 example.php及excel\reader.php。

程式如下：
example.php
<?php
if (!empty($_POST["file1"])){
doProcess($_POST["file1"],$_POST['tb1']);
}

?>

<form id="form1" name="form1" enctype="multipart/form-data" method="post" action="">
<p>
<label>Excel 檔名(完整路徑)：
<input name="file1" type="text" id="file1" size="50" />
<br />
</label>
</p>
<p>
<input type="submit" name="submit1" id="submit1" value="送出" />
</p>
</form>

<?php

// Test CVS
function doProcess($file1){
require_once 'Excel/reader.php';

$mysqli = new mysqli("localhost", "root", "xxxxxxxx","db1") or die($mysqli->error);

// ExcelFile($filename, $encoding);
$data = new Spreadsheet_Excel_Reader();

// Set output Encoding.
$data->setOutputEncoding('utf-8');

/***
* if you want you can change 'iconv' to mb_convert_encoding:
* $data->setUTFEncoder('mb');
*
**/

/***
* By default rows & cols indeces start with 1
* For change initial index use:
* $data->setRowColOffset(0);
*
**/

/***
* Some function for formatting output.
* $data->setDefaultFormat('%.2f');
* setDefaultFormat - set format for columns with unknown formatting
*
* $data->setColumnFormat(4, '%.3f');
* setColumnFormat - set format for column (apply only to number fields)
*
**/

$file_big5 = mb_convert_encoding($file1, 'BIG5', 'UTF-8');
$data->read($file_big5);

$data->sheets[0]['numRows'] - count rows
$data->sheets[0]['numCols'] - count columns
$data->sheets[0]['cells'][$i][$j] - data from $i-row $j-column

$data->sheets[0]['cellsInfo'][$i][$j] - extended info about cell

$data->sheets[0]['cellsInfo'][$i][$j]['type'] = "date" | "number" | "unknown"
if 'type' == "unknown" - use 'raw' value, because cell contain value with format '0.00';
$data->sheets[0]['cellsInfo'][$i][$j]['raw'] = value if cell without format
$data->sheets[0]['cellsInfo'][$i][$j]['colspan']
$data->sheets[0]['cellsInfo'][$i][$j]['rowspan']
*/

error_reporting(E_ALL ^ E_NOTICE);
$pos=mb_strrpos($file1,"\\");
$pos2=mb_strrpos($file1,".");
$len=mb_strlen($file1);
$fileName=mb_substr($file1,$pos+1,($pos2-$pos-1));

for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
$a=trim($data->sheets[0]['cells'][1][$j]);
if (empty($a))
$fields="field".$j;
else
$fields= $data->sheets[0]['cells'][1][$j];

$fields.=" text";

if($j < $data->sheets[0]['numCols'])
$fields.=",";

$fs.=$fields;
}

$mysqli->query("SET NAMES 'utf8'");
$q="DROP TABLE IF EXISTS ".$fileName;

$mysqli->query($q) or die($mysqli->error);

$q="create table ".$fileName."(".$fs.")";
$mysqli->query($q) or die($mysqli->error);

for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
$q="insert ".$fileName." values(";
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
$q.="\"".addslashes($data->sheets[0]['cells'][$i][$j]);
if($j < $data->sheets[0]['numCols'])
$q.="\",";
else
$q.="\"";
}
$q.=")";
// echo $q;
$mysqli->query($q) or die($mysqli->error);
}

}

?>

excel\reader.php

<?php
/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */

/**
* A class for reading Microsoft Excel Spreadsheets.
*
* Originally developed by Vadim Tkachenko under the name PHPExcelReader.
* (http://sourceforge.net/projects/phpexcelreader)
* Based on the Java version by Andy Khan (http://www.andykhan.com). Now
* maintained by David Sanders. Reads only Biff 7 and Biff 8 formats.
*
* PHP versions 4 and 5
*
* LICENSE: This source file is subject to version 3.0 of the PHP license
* that is available through the world-wide-web at the following URI:
* http://www.php.net/license/3_0.txt. If you did not receive a copy of
* the PHP License and are unable to obtain it through the web, please
* send a note to license@php.net so we can mail you a copy immediately.
*
* @category Spreadsheet
* @package Spreadsheet_Excel_Reader
* @author Vadim Tkachenko <vt@apachephp.com>
* @license http://www.php.net/license/3_0.txt PHP License 3.0
* @version CVS: $Id: reader.php 19 2007-03-13 12:42:41Z shangxiao $
* @link http://pear.php.net/package/Spreadsheet_Excel_Reader
* @see OLE, Spreadsheet_Excel_Writer
*/

//require_once 'PEAR.php';
//require_once 'Spreadsheet/Excel/Reader/OLERead.php';
require_once 'oleread.inc';
//require_once 'OLE.php';

define('SPREADSHEET_EXCEL_READER_BIFF8', 0x600);
define('SPREADSHEET_EXCEL_READER_BIFF7', 0x500);
define('SPREADSHEET_EXCEL_READER_WORKBOOKGLOBALS', 0x5);
define('SPREADSHEET_EXCEL_READER_WORKSHEET', 0x10);

define('SPREADSHEET_EXCEL_READER_TYPE_BOF', 0x809);
define('SPREADSHEET_EXCEL_READER_TYPE_EOF', 0x0a);
define('SPREADSHEET_EXCEL_READER_TYPE_BOUNDSHEET', 0x85);
define('SPREADSHEET_EXCEL_READER_TYPE_DIMENSION', 0x200);
define('SPREADSHEET_EXCEL_READER_TYPE_ROW', 0x208);
define('SPREADSHEET_EXCEL_READER_TYPE_DBCELL', 0xd7);
define('SPREADSHEET_EXCEL_READER_TYPE_FILEPASS', 0x2f);
define('SPREADSHEET_EXCEL_READER_TYPE_NOTE', 0x1c);
define('SPREADSHEET_EXCEL_READER_TYPE_TXO', 0x1b6);
define('SPREADSHEET_EXCEL_READER_TYPE_RK', 0x7e);
define('SPREADSHEET_EXCEL_READER_TYPE_RK2', 0x27e);
define('SPREADSHEET_EXCEL_READER_TYPE_MULRK', 0xbd);
define('SPREADSHEET_EXCEL_READER_TYPE_MULBLANK', 0xbe);
define('SPREADSHEET_EXCEL_READER_TYPE_INDEX', 0x20b);
define('SPREADSHEET_EXCEL_READER_TYPE_SST', 0xfc);
define('SPREADSHEET_EXCEL_READER_TYPE_EXTSST', 0xff);
define('SPREADSHEET_EXCEL_READER_TYPE_CONTINUE', 0x3c);
define('SPREADSHEET_EXCEL_READER_TYPE_LABEL', 0x204);
define('SPREADSHEET_EXCEL_READER_TYPE_LABELSST', 0xfd);
define('SPREADSHEET_EXCEL_READER_TYPE_NUMBER', 0x203);
define('SPREADSHEET_EXCEL_READER_TYPE_NAME', 0x18);
define('SPREADSHEET_EXCEL_READER_TYPE_ARRAY', 0x221);
define('SPREADSHEET_EXCEL_READER_TYPE_STRING', 0x207);
define('SPREADSHEET_EXCEL_READER_TYPE_FORMULA', 0x406);
define('SPREADSHEET_EXCEL_READER_TYPE_FORMULA2', 0x6);
define('SPREADSHEET_EXCEL_READER_TYPE_FORMAT', 0x41e);
define('SPREADSHEET_EXCEL_READER_TYPE_XF', 0xe0);
define('SPREADSHEET_EXCEL_READER_TYPE_BOOLERR', 0x205);
define('SPREADSHEET_EXCEL_READER_TYPE_UNKNOWN', 0xffff);
define('SPREADSHEET_EXCEL_READER_TYPE_NINETEENFOUR', 0x22);
define('SPREADSHEET_EXCEL_READER_TYPE_MERGEDCELLS', 0xE5);

define('SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS' , 25569);
define('SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904', 24107);
define('SPREADSHEET_EXCEL_READER_MSINADAY', 86400);
//define('SPREADSHEET_EXCEL_READER_MSINADAY', 24 * 60 * 60);

//define('SPREADSHEET_EXCEL_READER_DEF_NUM_FORMAT', "%.2f");
define('SPREADSHEET_EXCEL_READER_DEF_NUM_FORMAT', "%s");

/*
* Place includes, constant defines and $_GLOBAL settings here.
* Make sure they have appropriate docblocks to avoid phpDocumentor
* construing they are documented by the page-level docblock.
*/

/**
* A class for reading Microsoft Excel Spreadsheets.
*
* Originally developed by Vadim Tkachenko under the name PHPExcelReader.
* (http://sourceforge.net/projects/phpexcelreader)
* Based on the Java version by Andy Khan (http://www.andykhan.com). Now
* maintained by David Sanders. Reads only Biff 7 and Biff 8 formats.
*
* @category Spreadsheet
* @package Spreadsheet_Excel_Reader
* @author Vadim Tkachenko <vt@phpapache.com>
* @copyright 1997-2005 The PHP Group
* @license http://www.php.net/license/3_0.txt PHP License 3.0
* @version Release: @package_version@
* @link http://pear.php.net/package/PackageName
* @see OLE, Spreadsheet_Excel_Writer
*/
class Spreadsheet_Excel_Reader
{
/**
* Array of worksheets found
*
* @var array
* @access public
*/
var $boundsheets = array();

/**
* Array of format records found
*
* @var array
* @access public
*/
var $formatRecords = array();

/**
* todo
*
* @var array
* @access public
*/
var $sst = array();

/**
* Array of worksheets
*
* The data is stored in 'cells' and the meta-data is stored in an array
* called 'cellsInfo'
*
* Example:
*
* $sheets --> 'cells' --> row --> column --> Interpreted value
* --> 'cellsInfo' --> row --> column --> 'type' - Can be 'date', 'number', or 'unknown'
* --> 'raw' - The raw data that Excel stores for that data cell
*
* @var array
* @access public
*/
var $sheets = array();

/**
* The data returned by OLE
*
* @var string
* @access public
*/
var $data;

/**
* OLE object for reading the file
*
* @var OLE object
* @access private
*/
var $_ole;

/**
* Default encoding
*
* @var string
* @access private
*/
var $_defaultEncoding;

/**
* Default number format
*
* @var integer
* @access private
*/
var $_defaultFormat = SPREADSHEET_EXCEL_READER_DEF_NUM_FORMAT;

/**
* todo
* List of formats to use for each column
*
* @var array
* @access private
*/
var $_columnsFormat = array();

/**
* todo
*
* @var integer
* @access private
*/
var $_rowoffset = 1;

/**
* todo
*
* @var integer
* @access private
*/
var $_coloffset = 1;

/**
* List of default date formats used by Excel
*
* @var array
* @access public
*/
var $dateFormats = array (
// 0xe => "d/m/Y",
0xe => "Y/m/d",
0xf => "d-M-Y",
0x10 => "d-M",
0x11 => "M-Y",
0x12 => "h:i a",
0x13 => "h:i:s a",
0x14 => "H:i",
0x15 => "H:i:s",
0x16 => "d/m/Y H:i",
0x2d => "i:s",
0x2e => "H:i:s",
0x2f => "i:s.S");

/**
* Default number formats used by Excel
*
* @var array
* @access public
*/
var $numberFormats = array(
0x1 => "%1.0f", // "0"
0x2 => "%1.2f", // "0.00",
0x3 => "%1.0f", //"#,##0",
0x4 => "%1.2f", //"#,##0.00",
0x5 => "%1.0f", /*"$#,##0;($#,##0)",*/
0x6 => '$%1.0f', /*"$#,##0;($#,##0)",*/
0x7 => '$%1.2f', //"$#,##0.00;($#,##0.00)",
0x8 => '$%1.2f', //"$#,##0.00;($#,##0.00)",
0x9 => '%1.0f%%', // "0%"
0xa => '%1.2f%%', // "0.00%"
0xb => '%1.2f', // 0.00E00",
0x25 => '%1.0f', // "#,##0;(#,##0)",
0x26 => '%1.0f', //"#,##0;(#,##0)",
0x27 => '%1.2f', //"#,##0.00;(#,##0.00)",
0x28 => '%1.2f', //"#,##0.00;(#,##0.00)",
0x29 => '%1.0f', //"#,##0;(#,##0)",
0x2a => '$%1.0f', //"$#,##0;($#,##0)",
0x2b => '%1.2f', //"#,##0.00;(#,##0.00)",
0x2c => '$%1.2f', //"$#,##0.00;($#,##0.00)",
0x30 => '%1.0f'); //"##0.0E0";

// }}}
// {{{ Spreadsheet_Excel_Reader()

/**
* Constructor
*
* Some basic initialisation
*/
function Spreadsheet_Excel_Reader()
{
$this->_ole =& new OLERead();
$this->setUTFEncoder('iconv');
}

// }}}
// {{{ setOutputEncoding()

/**
* Set the encoding method
*
* @param string Encoding to use
* @access public
*/
function setOutputEncoding($encoding)
{
$this->_defaultEncoding = $encoding;
}

// }}}
// {{{ setUTFEncoder()

/**
* $encoder = 'iconv' or 'mb'
* set iconv if you would like use 'iconv' for encode UTF-16LE to your encoding
* set mb if you would like use 'mb_convert_encoding' for encode UTF-16LE to your encoding
*
* @access public
* @param string Encoding type to use. Either 'iconv' or 'mb'
*/
function setUTFEncoder($encoder = 'iconv')
{
$this->_encoderFunction = '';

if ($encoder == 'iconv') {
$this->_encoderFunction = function_exists('iconv') ? 'iconv' : '';
} elseif ($encoder == 'mb') {
$this->_encoderFunction = function_exists('mb_convert_encoding') ?
'mb_convert_encoding' :
'';
}
}

// }}}
// {{{ setRowColOffset()

/**
* todo
*
* @access public
* @param offset
*/
function setRowColOffset($iOffset)
{
$this->_rowoffset = $iOffset;
$this->_coloffset = $iOffset;
}

// }}}
// {{{ setDefaultFormat()

/**
* Set the default number format
*
* @access public
* @param Default format
*/
function setDefaultFormat($sFormat)
{
$this->_defaultFormat = $sFormat;
}

// }}}
// {{{ setColumnFormat()

/**
* Force a column to use a certain format
*
* @access public
* @param integer Column number
* @param string Format
*/
function setColumnFormat($column, $sFormat)
{
$this->_columnsFormat[$column] = $sFormat;
}

// }}}
// {{{ read()

/**
* Read the spreadsheet file using OLE, then parse
*
* @access public
* @param filename
* @todo return a valid value
*/
function read($sFileName)
{
$res = $this->_ole->read($sFileName);

// oops, something goes wrong (Darko Miljanovic)
if($res === false) {
// check error code
if($this->_ole->error == 1) {
// bad file
die('The filename ' . $sFileName . ' is not readable');
}
// check other error codes here (eg bad fileformat, etc...)
}

$this->data = $this->_ole->getWorkBook();

$this->_parse();
}

// }}}
// {{{ _parse()

/**
* Parse a workbook
*
* @access private
* @return bool
*/
function _parse()
{
$pos = 0;

$code = ord($this->data[$pos]) | ord($this->data[$pos+1])<<8;
$length = ord($this->data[$pos+2]) | ord($this->data[$pos+3])<<8;

$version = ord($this->data[$pos + 4]) | ord($this->data[$pos + 5])<<8;
$substreamType = ord($this->data[$pos + 6]) | ord($this->data[$pos + 7])<<8;
//echo "Start parse code=".base_convert($code,10,16)." version=".base_convert($version,10,16)." substreamType=".base_convert($substreamType,10,16).""."\n";

if (($version != SPREADSHEET_EXCEL_READER_BIFF8) &&
($version != SPREADSHEET_EXCEL_READER_BIFF7)) {
return false;
}

if ($substreamType != SPREADSHEET_EXCEL_READER_WORKBOOKGLOBALS){
return false;
}

//print_r($rec);
$pos += $length + 4;

$code = ord($this->data[$pos]) | ord($this->data[$pos+1])<<8;
$length = ord($this->data[$pos+2]) | ord($this->data[$pos+3])<<8;

while ($code != SPREADSHEET_EXCEL_READER_TYPE_EOF) {
/*$code:儲存格的格式;這裡不是parse儲存格的地方,而是parse整體變數的地方。
224 0xe0 SPREADSHEET_EXCEL_READER_TYPE_XF
*/
// echo "<br> parse_code:$code";
switch ($code) {
case SPREADSHEET_EXCEL_READER_TYPE_SST:
//echo "Type_SST\n";
$spos = $pos + 4;
$limitpos = $spos + $length;
$uniqueStrings = $this->_GetInt4d($this->data, $spos+4);
$spos += 8;
for ($i = 0; $i < $uniqueStrings; $i++) {
// Read in the number of characters
if ($spos == $limitpos) {
$opcode = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
$conlength = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
if ($opcode != 0x3c) {
return -1;
}
$spos += 4;
$limitpos = $spos + $conlength;
}
$numChars = ord($this->data[$spos]) | (ord($this->data[$spos+1]) << 8);
//echo "i = $i pos = $pos numChars = $numChars ";
$spos += 2;
$optionFlags = ord($this->data[$spos]);
$spos++;
$asciiEncoding = (($optionFlags & 0x01) == 0) ;
$extendedString = ( ($optionFlags & 0x04) != 0);

// See if string contains formatting information
$richString = ( ($optionFlags & 0x08) != 0);

if ($richString) {
// Read in the crun
$formattingRuns = ord($this->data[$spos]) | (ord($this->data[$spos+1]) << 8);
$spos += 2;
}

if ($extendedString) {
// Read in cchExtRst
$extendedRunLength = $this->_GetInt4d($this->data, $spos);
$spos += 4;
}

$len = ($asciiEncoding)? $numChars : $numChars*2;
if ($spos + $len < $limitpos) {
$retstr = substr($this->data, $spos, $len);
$spos += $len;
}else{
// found countinue
$retstr = substr($this->data, $spos, $limitpos - $spos);
$bytesRead = $limitpos - $spos;
$charsLeft = $numChars - (($asciiEncoding) ? $bytesRead : ($bytesRead / 2));
$spos = $limitpos;

while ($charsLeft > 0){
$opcode = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
$conlength = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
if ($opcode != 0x3c) {
return -1;
}
$spos += 4;
$limitpos = $spos + $conlength;
$option = ord($this->data[$spos]);
$spos += 1;
if ($asciiEncoding && ($option == 0)) {
$len = min($charsLeft, $limitpos - $spos); // min($charsLeft, $conlength);
$retstr .= substr($this->data, $spos, $len);
$charsLeft -= $len;
$asciiEncoding = true;
}elseif (!$asciiEncoding && ($option != 0)){
$len = min($charsLeft * 2, $limitpos - $spos); // min($charsLeft, $conlength);
$retstr .= substr($this->data, $spos, $len);
$charsLeft -= $len/2;
$asciiEncoding = false;
}elseif (!$asciiEncoding && ($option == 0)) {
// Bummer - the string starts off as Unicode, but after the
// continuation it is in straightforward ASCII encoding
$len = min($charsLeft, $limitpos - $spos); // min($charsLeft, $conlength);
for ($j = 0; $j < $len; $j++) {
$retstr .= $this->data[$spos + $j].chr(0);
}
$charsLeft -= $len;
$asciiEncoding = false;
}else{
$newstr = '';
for ($j = 0; $j < strlen($retstr); $j++) {
$newstr = $retstr[$j].chr(0);
}
$retstr = $newstr;
$len = min($charsLeft * 2, $limitpos - $spos); // min($charsLeft, $conlength);
$retstr .= substr($this->data, $spos, $len);
$charsLeft -= $len/2;
$asciiEncoding = false;
//echo "Izavrat\n";
}
$spos += $len;

}
}
$retstr = ($asciiEncoding) ? $retstr : $this->_encodeUTF16($retstr);
// echo "Str $i = $retstr\n";
if ($richString){
$spos += 4 * $formattingRuns;
}

// For extended strings, skip over the extended string data
if ($extendedString) {
$spos += $extendedRunLength;
}
//if ($retstr == 'Derby'){
// echo "bb\n";
//}
$this->sst[]=$retstr;
}
/*$continueRecords = array();
while ($this->getNextCode() == Type_CONTINUE) {
$continueRecords[] = &$this->nextRecord();
}
//echo " 1 Type_SST\n";
$this->shareStrings = new SSTRecord($r, $continueRecords);
//print_r($this->shareStrings->strings);
*/
// echo 'SST read: '.($time_end-$time_start)."\n";
break;

case SPREADSHEET_EXCEL_READER_TYPE_FILEPASS:
return false;
break;
case SPREADSHEET_EXCEL_READER_TYPE_NAME:
//echo "Type_NAME\n";
break;
case SPREADSHEET_EXCEL_READER_TYPE_FORMAT:
$indexCode = ord($this->data[$pos+4]) | ord($this->data[$pos+5]) << 8;

if ($version == SPREADSHEET_EXCEL_READER_BIFF8) {
$numchars = ord($this->data[$pos+6]) | ord($this->data[$pos+7]) << 8;
if (ord($this->data[$pos+8]) == 0){
$formatString = substr($this->data, $pos+9, $numchars);
} else {
$formatString = substr($this->data, $pos+9, $numchars*2);
}
} else {
$numchars = ord($this->data[$pos+6]);
$formatString = substr($this->data, $pos+7, $numchars*2);
}

$this->formatRecords[$indexCode] = $formatString;
// echo "Type.FORMAT\n";
break;
case SPREADSHEET_EXCEL_READER_TYPE_XF:
//global $dateFormats, $numberFormats;
$indexCode = ord($this->data[$pos+6]) | ord($this->data[$pos+7]) << 8;

// echo "<br>Type.XF ".count($this->formatRecords['xfrecords'])."code: $indexCode ";

if (array_key_exists($indexCode, $this->dateFormats)) {
// echo "<br>Type.XF ".count($this->formatRecords['xfrecords'])."code: $indexCode ";
// echo "<br>isdate ".$this->dateFormats[$indexCode];
$this->formatRecords['xfrecords'][] = array(
'type' => 'date',
'format' => $this->dateFormats[$indexCode]
);
}elseif (array_key_exists($indexCode, $this->numberFormats)) {
// echo "<br>isnumber ".$this->numberFormats[$indexCode];
$this->formatRecords['xfrecords'][] = array(
'type' => 'number',
'format' => $this->numberFormats[$indexCode]
);
}else{
$isdate = FALSE;
if ($indexCode > 0){
if (isset($this->formatRecords[$indexCode]))
$formatstr = $this->formatRecords[$indexCode];
//echo '.other.';
//echo "\ndate-time=$formatstr=\n";
if ($formatstr)
if (preg_match("/[^hmsday\/\-:\s]/i", $formatstr) == 0) { // found day and time format
$isdate = TRUE;
$formatstr = str_replace('mm', 'i', $formatstr);
$formatstr = str_replace('h', 'H', $formatstr);
//echo "\ndate-time $formatstr \n";
}
}

if ($isdate){
$this->formatRecords['xfrecords'][] = array(
'type' => 'date',
'format' => $formatstr,
);
}else{
$this->formatRecords['xfrecords'][] = array(
'type' => 'other',
'format' => '',
'code' => $indexCode
);
}
}
//echo "\n";
// echo "<br>type1:".$this->formatRecords['xfrecords'][count($this->formatRecords['xfrecords'])-1]['type'];
//$this->formatRecords['xfrecords'][$xfindex]['type'] == 'number'
break;
case SPREADSHEET_EXCEL_READER_TYPE_NINETEENFOUR:
//echo "Type.NINETEENFOUR\n";
$this->nineteenFour = (ord($this->data[$pos+4]) == 1);
break;
case SPREADSHEET_EXCEL_READER_TYPE_BOUNDSHEET:
//echo "Type.BOUNDSHEET\n";
$rec_offset = $this->_GetInt4d($this->data, $pos+4);
$rec_typeFlag = ord($this->data[$pos+8]);
$rec_visibilityFlag = ord($this->data[$pos+9]);
$rec_length = ord($this->data[$pos+10]);

if ($version == SPREADSHEET_EXCEL_READER_BIFF8){
$chartype = ord($this->data[$pos+11]);
if ($chartype == 0){
$rec_name = substr($this->data, $pos+12, $rec_length);
} else {
$rec_name = $this->_encodeUTF16(substr($this->data, $pos+12, $rec_length*2));
}
}elseif ($version == SPREADSHEET_EXCEL_READER_BIFF7){
$rec_name = substr($this->data, $pos+11, $rec_length);
}
$this->boundsheets[] = array('name'=>$rec_name,
'offset'=>$rec_offset);

break;

}

//echo "Code = ".base_convert($r['code'],10,16)."\n";
$pos += $length + 4;
$code = ord($this->data[$pos]) | ord($this->data[$pos+1])<<8;
$length = ord($this->data[$pos+2]) | ord($this->data[$pos+3])<<8;

//$r = &$this->nextRecord();
//echo "1 Code = ".base_convert($r['code'],10,16)."\n";
}

foreach ($this->boundsheets as $key=>$val){
$this->sn = $key;
$this->_parsesheet($val['offset']);
}
return true;

}

/**
* Parse a worksheet
*
* @access private
* @param todo
* @todo fix return codes
*/
function _parsesheet($spos)
{
$cont = true;
// read BOF
$code = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
$length = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;

$version = ord($this->data[$spos + 4]) | ord($this->data[$spos + 5])<<8;
$substreamType = ord($this->data[$spos + 6]) | ord($this->data[$spos + 7])<<8;

if (($version != SPREADSHEET_EXCEL_READER_BIFF8) && ($version != SPREADSHEET_EXCEL_READER_BIFF7)) {
return -1;
}

if ($substreamType != SPREADSHEET_EXCEL_READER_WORKSHEET){
return -2;
}
//echo "Start parse code=".base_convert($code,10,16)." version=".base_convert($version,10,16)." substreamType=".base_convert($substreamType,10,16).""."\n";
$spos += $length + 4;
//var_dump($this->formatRecords);
//echo "code $code $length";
while($cont) {
//echo "mem= ".memory_get_usage()."\n";
// $r = &$this->file->nextRecord();
$lowcode = ord($this->data[$spos]);
if ($lowcode == SPREADSHEET_EXCEL_READER_TYPE_EOF) break;
$code = $lowcode | ord($this->data[$spos+1])<<8;
$length = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
$spos += 4;
$this->sheets[$this->sn]['maxrow'] = $this->_rowoffset - 1;
$this->sheets[$this->sn]['maxcol'] = $this->_coloffset - 1;
//echo "Code=".base_convert($code,10,16)." $code\n";
unset($this->rectype);
$this->multiplier = 1; // need for format with %
// echo "<br>parsesheet_code:$code ";
/*
$code:
253 0xfd SPREADSHEET_EXCEL_READER_TYPE_LABELSST G/通用格式
638 0x27e SPREADSHEET_EXCEL_READER_TYPE_RK2 日期或unknow格式
*/

switch ($code) {
case SPREADSHEET_EXCEL_READER_TYPE_DIMENSION:
//echo 'Type_DIMENSION ';
if (!isset($this->numRows)) {
if (($length == 10) || ($version == SPREADSHEET_EXCEL_READER_BIFF7)){
$this->sheets[$this->sn]['numRows'] = ord($this->data[$spos+2]) | ord($this->data[$spos+3]) << 8;
$this->sheets[$this->sn]['numCols'] = ord($this->data[$spos+6]) | ord($this->data[$spos+7]) << 8;
} else {
$this->sheets[$this->sn]['numRows'] = ord($this->data[$spos+4]) | ord($this->data[$spos+5]) << 8;
$this->sheets[$this->sn]['numCols'] = ord($this->data[$spos+10]) | ord($this->data[$spos+11]) << 8;
}
}
//echo 'numRows '.$this->numRows.' '.$this->numCols."\n";
break;
case SPREADSHEET_EXCEL_READER_TYPE_MERGEDCELLS:
$cellRanges = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
for ($i = 0; $i < $cellRanges; $i++) {
$fr = ord($this->data[$spos + 8*$i + 2]) | ord($this->data[$spos + 8*$i + 3])<<8;
$lr = ord($this->data[$spos + 8*$i + 4]) | ord($this->data[$spos + 8*$i + 5])<<8;
$fc = ord($this->data[$spos + 8*$i + 6]) | ord($this->data[$spos + 8*$i + 7])<<8;
$lc = ord($this->data[$spos + 8*$i + 8]) | ord($this->data[$spos + 8*$i + 9])<<8;
//$this->sheets[$this->sn]['mergedCells'][] = array($fr + 1, $fc + 1, $lr + 1, $lc + 1);
if ($lr - $fr > 0) {
$this->sheets[$this->sn]['cellsInfo'][$fr+1][$fc+1]['rowspan'] = $lr - $fr + 1;
}
if ($lc - $fc > 0) {
$this->sheets[$this->sn]['cellsInfo'][$fr+1][$fc+1]['colspan'] = $lc - $fc + 1;
}
}
//echo "Merged Cells $cellRanges $lr $fr $lc $fc\n";
break;
case SPREADSHEET_EXCEL_READER_TYPE_RK:
case SPREADSHEET_EXCEL_READER_TYPE_RK2:
//echo 'SPREADSHEET_EXCEL_READER_TYPE_RK'."\n";
$row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
$column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
$rknum = $this->_GetInt4d($this->data, $spos + 6);
$numValue = $this->_GetIEEE754($rknum);
//echo $numValue." ";
if ($this->isDate($spos)) {
list($string, $raw) = $this->createDate($numValue);
}else{
$raw = $numValue;
if (isset($this->_columnsFormat[$column + 1])){
$this->curformat = $this->_columnsFormat[$column + 1];
}
$string = sprintf($this->curformat, $numValue * $this->multiplier);
//$this->addcell(RKRecord($r));
}
//hwjane
$this->addcell($row, $column, $string, $raw);
//echo "Type_RK $row $column $string $raw {$this->curformat}\n";
break;
case SPREADSHEET_EXCEL_READER_TYPE_LABELSST:
$row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
$column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
$xfindex = ord($this->data[$spos+4]) | ord($this->data[$spos+5])<<8;
$index = $this->_GetInt4d($this->data, $spos + 6);
//var_dump($this->sst);
$this->addcell($row, $column, $this->sst[$index]);
//echo "LabelSST $row $column $string\n";
break;
case SPREADSHEET_EXCEL_READER_TYPE_MULRK:
$row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
$colFirst = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
$colLast = ord($this->data[$spos + $length - 2]) | ord($this->data[$spos + $length - 1])<<8;
$columns = $colLast - $colFirst + 1;
$tmppos = $spos+4;
for ($i = 0; $i < $columns; $i++) {
$numValue = $this->_GetIEEE754($this->_GetInt4d($this->data, $tmppos + 2));
if ($this->isDate($tmppos-4)) {
list($string, $raw) = $this->createDate($numValue);
}else{
$raw = $numValue;
if (isset($this->_columnsFormat[$colFirst + $i + 1])){
$this->curformat = $this->_columnsFormat[$colFirst + $i + 1];
}
$string = sprintf($this->curformat, $numValue * $this->multiplier);
}
//hwjane
//$rec['rknumbers'][$i]['xfindex'] = ord($rec['data'][$pos]) | ord($rec['data'][$pos+1]) << 8;
$tmppos += 6;
$this->addcell($row, $colFirst + $i, $string, $raw);
//echo "MULRK $row ".($colFirst + $i)." $string\n";
}
//MulRKRecord($r);
// Get the individual cell records from the multiple record
//$num = ;

break;
case SPREADSHEET_EXCEL_READER_TYPE_NUMBER:
$row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
$column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
$tmp = unpack("ddouble", substr($this->data, $spos + 6, 8)); // It machine machine dependent
if ($this->isDate($spos)) {
list($string, $raw) = $this->createDate($tmp['double']);
// $this->addcell(DateRecord($r, 1));
}else{
//$raw = $tmp[''];
if (isset($this->_columnsFormat[$column + 1])){
$this->curformat = $this->_columnsFormat[$column + 1];
}
$raw = $this->createNumber($spos);
$string = sprintf($this->curformat, $raw * $this->multiplier);
}
// echo "number exist?";
$this->addcell($row, $column, $string, $raw);
//echo "Number $row $column $string\n";
break;
case SPREADSHEET_EXCEL_READER_TYPE_FORMULA:
case SPREADSHEET_EXCEL_READER_TYPE_FORMULA2:
$row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
$column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
if ((ord($this->data[$spos+6])==0) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {
//String formula. Result follows in a STRING record
//echo "FORMULA $row $column Formula with a string<br>\n";
} elseif ((ord($this->data[$spos+6])==1) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {
//Boolean formula. Result is in +2; 0=false,1=true
} elseif ((ord($this->data[$spos+6])==2) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {
//Error formula. Error code is in +2;
} elseif ((ord($this->data[$spos+6])==3) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {
//Formula result is a null string.
} else {
// result is a number, so first 14 bytes are just like a _NUMBER record
$tmp = unpack("ddouble", substr($this->data, $spos + 6, 8)); // It machine machine dependent
if ($this->isDate($spos)) {
list($string, $raw) = $this->createDate($tmp['double']);
// $this->addcell(DateRecord($r, 1));
}else{
//$raw = $tmp[''];
if (isset($this->_columnsFormat[$column + 1])){
$this->curformat = $this->_columnsFormat[$column + 1];
}
$raw = $this->createNumber($spos);
$string = sprintf($this->curformat, $raw * $this->multiplier);

// $this->addcell(NumberRecord($r));
}
//hwjane
$this->addcell($row, $column, $string, $raw);
//echo "Number $row $column $string\n";
}
break;
case SPREADSHEET_EXCEL_READER_TYPE_BOOLERR:
$row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
$column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
$string = ord($this->data[$spos+6]);
$this->addcell($row, $column, $string);
//echo 'Type_BOOLERR '."\n";
break;
case SPREADSHEET_EXCEL_READER_TYPE_ROW:
case SPREADSHEET_EXCEL_READER_TYPE_DBCELL:
case SPREADSHEET_EXCEL_READER_TYPE_MULBLANK:
break;
case SPREADSHEET_EXCEL_READER_TYPE_LABEL:
$row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
$column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
$this->addcell($row, $column, substr($this->data, $spos + 8, ord($this->data[$spos + 6]) | ord($this->data[$spos + 7])<<8));

// $this->addcell(LabelRecord($r));
break;

case SPREADSHEET_EXCEL_READER_TYPE_EOF:
$cont = false;
break;
default:
//echo ' unknown :'.base_convert($r['code'],10,16)."\n";
break;

}
$spos += $length;
}

if (!isset($this->sheets[$this->sn]['numRows']))
$this->sheets[$this->sn]['numRows'] = $this->sheets[$this->sn]['maxrow'];
if (!isset($this->sheets[$this->sn]['numCols']))
$this->sheets[$this->sn]['numCols'] = $this->sheets[$this->sn]['maxcol'];

}

/**
* Check whether the current record read is a date
*
* @param todo
* @return boolean True if date, false otherwise
*/
function isDate($spos)
{
//$xfindex = GetInt2d(, 4);

$a=ord($this->data[$spos+4]);
$b=ord($this->data[$spos+5]);
// echo "spos:$spos data1:$a data2:$b " ;
$xfindex = ord($this->data[$spos+4]) | ord($this->data[$spos+5]) << 8;
//echo 'check is date '.$xfindex.' '.$this->formatRecords['xfrecords'][$xfindex]['type']."\n";
//var_dump($this->formatRecords['xfrecords'][$xfindex]);
// echo "<br>xfindex:$xfindex ";
if ($this->formatRecords['xfrecords'][$xfindex]['type'] == 'date') {
$this->curformat = $this->formatRecords['xfrecords'][$xfindex]['format'];
$this->rectype = 'date '.$xfindex;
// echo "rectype-date:$this->rectype ";
return true;
} else {
if ($this->formatRecords['xfrecords'][$xfindex]['type'] == 'number') {
$this->curformat = $this->formatRecords['xfrecords'][$xfindex]['format'];
$this->rectype = 'number '.$xfindex;
// echo "rectype-number:$this->rectype ";
if (($xfindex == 0x9) || ($xfindex == 0xa)){
$this->multiplier = 100;
}
}else{
$this->curformat = $this->_defaultFormat;
$this->rectype = 'unknown '.$xfindex;
// echo "rectype-unknown:$this->rectype ";
}
return false;
}
}

//}}}
//{{{ createDate()

/**
* Convert the raw Excel date into a human readable format
*
* Dates in Excel are stored as number of seconds from an epoch. On
* Windows, the epoch is 30/12/1899 and on Mac it's 01/01/1904
*
* @access private
* @param integer The raw Excel value to convert
* @return array First element is the converted date, the second element is number a unix timestamp
*/
function createDate($numValue)
{
if ($numValue > 1) {
$utcDays = $numValue - ($this->nineteenFour ? SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904 : SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS);
$utcValue = round(($utcDays+1) * SPREADSHEET_EXCEL_READER_MSINADAY);
// $string = date ($this->curformat, $utcValue);
$string = date ($this->curformat, $utcValue - 1 * 24 * 60 * 60);
$raw = $utcValue;
} else {
$raw = $numValue;
$hours = floor($numValue * 24);
$mins = floor($numValue * 24 * 60) - $hours * 60;
$secs = floor($numValue * SPREADSHEET_EXCEL_READER_MSINADAY) - $hours * 60 * 60 - $mins * 60;
$string = date ($this->curformat, mktime($hours, $mins, $secs));
}

return array($string, $raw);
}

function addcell($row, $col, $string, $raw = '')
{
//echo "ADD cel $row-$col $string\n";
$this->sheets[$this->sn]['maxrow'] = max($this->sheets[$this->sn]['maxrow'], $row + $this->_rowoffset);
$this->sheets[$this->sn]['maxcol'] = max($this->sheets[$this->sn]['maxcol'], $col + $this->_coloffset);
$this->sheets[$this->sn]['cells'][$row + $this->_rowoffset][$col + $this->_coloffset] = $string;
if ($raw)
$this->sheets[$this->sn]['cellsInfo'][$row + $this->_rowoffset][$col + $this->_coloffset]['raw'] = $raw;
if (isset($this->rectype))
$this->sheets[$this->sn]['cellsInfo'][$row + $this->_rowoffset][$col + $this->_coloffset]['type'] = $this->rectype;
// echo "type:".$this->rectype."<br>";
}

function _GetIEEE754($rknum)
{
if (($rknum & 0x02) != 0) {
$value = $rknum >> 2;
} else {
//mmp
// first comment out the previously existing 7 lines of code here
// $tmp = unpack("d", pack("VV", 0, ($rknum & 0xfffffffc)));
// //$value = $tmp[''];
// if (array_key_exists(1, $tmp)) {
// $value = $tmp[1];
// } else {
// $value = $tmp[''];
// }
// I got my info on IEEE754 encoding from
// http://research.microsoft.com/~hollasch/cgindex/coding/ieeefloat.html
// The RK format calls for using only the most significant 30 bits of the
// 64 bit floating point value. The other 34 bits are assumed to be 0
// So, we use the upper 30 bits of $rknum as follows...
$sign = ($rknum & 0x80000000) >> 31;
$exp = ($rknum & 0x7ff00000) >> 20;
$mantissa = (0x100000 | ($rknum & 0x000ffffc));
$value = $mantissa / pow( 2 , (20- ($exp - 1023)));
if ($sign) {$value = -1 * $value;}
//end of changes by mmp

}

if (($rknum & 0x01) != 0) {
$value /= 100;
}
return $value;
}

function _GetInt4d($data, $pos)
{
$value = ord($data[$pos]) | (ord($data[$pos+1]) << 8) | (ord($data[$pos+2]) << 16) | (ord($data[$pos+3]) << 24);
if ($value>=4294967294)
{
$value=-2;
}
return $value;
}

}

/*
* Local variables:
* tab-width: 4
* c-basic-offset: 4
* c-hanging-comment-ender-p: nil
* End:
*/

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	a	ref	accSeq,bNo	bNo	4	const	1	Using where
1	SIMPLE	b	eq_ref	PRIMARY	PRIMARY	4	cook.a.prodSeq	1

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1
1	PRIMARY	b	const	PRIMARY	PRIMARY	4	const	1
2	DERIVED	batch_detail	ref	accSeq,bNo	bNo	4		1	Using where

2010年12月24日 星期五

2010年12月23日 星期四

2010年12月5日 星期日

2010年6月15日 星期二

MPlayerCE安裝步驟

使用MPlayerCE

2010年6月7日 星期一

什麼是IOS

什麼是cIOS

wii的漏洞(cIOS的原理)

IOS的版本

如何查看wii裡面有那些ios,cios

幾個常見的cIOS

升級

深入了解

關於IOS和SysMenu的關係

什麼是cIOSCORP

常用的IOS列表

2010年5月30日 星期日

投影機的新選擇-微型投影機

LED光源技術

亮度

USB裝置

直接播放檔案

輸入裝置

解析度

耗電

相關參考文章

2010年5月24日 星期一

流明(光通量)

峰值流明

ANSI流明

參考

2010年5月10日 星期一

基本瘦身法

刪除不用的檔案

令人討厭的Winsxs資料夾

2010年4月23日 星期五

2010年4月14日 星期三

2010年4月9日 星期五

2010年4月3日 星期六

2010年3月27日 星期六

2010年3月19日 星期五

2010年3月7日 星期日

軟碟機的開機片製作

開機光碟製作

usb開機碟製作

結論

2010年3月3日 星期三

2010年2月26日 星期五

2010年2月19日 星期五

2010年2月10日 星期三

2010年12月24日星期五

2010年12月23日星期四

2010年12月5日星期日

2010年6月15日星期二

2010年6月7日星期一

2010年5月30日星期日

2010年5月24日星期一

2010年5月10日星期一

2010年4月23日星期五

2010年4月14日星期三

2010年4月9日星期五

2010年4月3日星期六

2010年3月27日星期六

2010年3月19日星期五

2010年3月7日星期日

2010年3月3日星期三

2010年2月26日星期五

2010年2月19日星期五

2010年2月10日星期三