• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

php读取excel文档内容(转载)

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

入到数据库的需要,php-excel-reader可以很轻松的使用它读取excel文件,本文将详细介绍,需要了解的朋友可以参考下

 

php开发中肯定会遇到将excel文件内容导入到数据库的需要,php-excel-reader是一个读取excel的类,可以很轻松的使用它读取excel文件非常方便。

php-excel-reader下载地址: http://www.ogeek.net/codes/67223.html

我下载的是php-excel-reader-2.21版本,使用的时候还遇到几个小问题,后面再细说,先奉上php实例:

我使用的excel如下图:

php代码如下:


 1 <?php
 2 /*by www.phpddt.com*/
 3 header("Content-Type:text/html;charset=utf-8");
 4 require_once 'excel_reader2.php';
 5 //创建对象
 6 $data = new Spreadsheet_Excel_Reader();
 7 //设置文本输出编码
 8 $data->setOutputEncoding('UTF-8');
 9 //读取Excel文件
10 $data->read("example.xls");
11 //$data->sheets[0]['numRows']为Excel行数
12 for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
13 //$data->sheets[0]['numCols']为Excel列数
14 for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
15 //显示每个单元格内容
16 echo $data->sheets[0]['cells'][$i][$j].' ';
17 }
18 echo '<br>';
19 }
20 ?> 

 


读取结果截图如下

再来说说这个类的小问题:

(1)出现Deprecated: Function split() is deprecated in 。。。错误

解决:将excel_reader2.php源码中split改为explode,详情点击php中explode与split的区别介绍

(2)出现Deprecated: Assigning the return value of new by reference is deprecated in错误

解决:将excel_reader2.php源码中$this->_ole =& new OLERead()中 &去掉,因为php5.3中废除了=& 符号直接用=引用

(3)乱码问题解决:

构造函数是function Spreadsheet_Excel_Reader($file='',$store_extended_info=true,$outputEncoding=''), 它默认的编码是utf-8,如果不指定,可能会出现乱码问题,可通过$data->setOutputEncoding('GBK');指定,还有 如果你使用dump()函数,dump()函数将excel内容一html格式输出,使用htmlentities将字符转化为html的,它默认使用 ISO8559-1编码的,所以你要将 excel_reader2.php源码中 htmlentities($val)函数改为 htmlentities($val,ENT_COMPAT,"GB2312");才行。

最后来说说,php-excel-reader操作excel中的两个重要的方法

1.dump(),它可以将excel内容以html格式输出:

echo $data->dump(true,true);

2.将excel数据存入数组中,使用$data->sheets,打印下如下:

  1 Array
  2 (
  3   [0] => Array
  4     (
  5       maxrow] => 0
  6       [maxcol] => 0
  7       [numRows] => 5
  8       [numCols] => 4
  9       [cells] => Array
 10         (
 11           [1] => Array
 12             (
 13               [1] => 编号
 14               [2] => 姓名
 15               [3] => 年龄
 16               [4] => 学号
 17             )
 18           [2] => Array
 19             (
 20               [1] => 1
 21               [2] => 小红
 22               [3] => 22
 23               [4] => a1000
 24             )
 25           [3] => Array
 26             (
 27               [1] => 2
 28               [2] => 小王
 29               [3] => 33
 30               [4] => a1001
 31              )
 32           [4] => Array
 33             (
 34               [1] => 3
 35               [2] => 小黑
 36               [3] => 44
 37               [4] => a1002
 38             )
 39           ) 45   [cellsInfo] => Array
 46     (
 47       [1] => Array
 48         (
 49           [1] => Array
 50             (
 51               [xfIndex] => 15
 52              )
 53           [2] => Array
 54             (
 55               [xfIndex] => 15
 56             )
 57           [3] => Array
 58             (
 59               [xfIndex] => 15
 60             )
 61           [4] => Array
 62             (
 63               [xfIndex] => 15
 64             )
 65          )
 66       [2] => Array
 67         (
 68           [1] => Array
 69             (
 70               [string] => 1
 71               [raw] => 1
 72               [rectype] => unknown
 73               [format] => %s
 74               [formatIndex] => 0
 75               [fontIndex] => 0
 76               [formatColor] =>
 77               [xfIndex] => 15
 78             )
 79           [2] => Array
 80             (
 81               [xfIndex] => 15
 82             )
 83           [3] => Array
 84             (
 85               [string] => 22
 86               [raw] => 22
 87               [rectype] => unknown
 88               [format] => %s
 89               [formatIndex] => 0
 90               [fontIndex] => 0
 91               [formatColor] =>
 92               [xfIndex] => 15
 93             )
 94           [4] => Array
 95             (
 96               [xfIndex] => 15
 97             )
 98          )
 99       [3] => Array
100          (
101         ...  ...//剩下的部分可以自己打印出来看看

 

下面的read_php的类是改过上文提到过的问题的代码,可以直接使用,不再需要修改

<?php
/**
 * A class for reading Microsoft Excel (97/2003) Spreadsheets.
 *
 * Version 2.21
 *
 * Enhanced and maintained by Matt Kruse < http://mattkruse.com >
 * Maintained at http://code.google.com/p/php-excel-reader/
 *
 * Format parsing and MUCH more contributed by:
 *    Matt Roxburgh < http://www.roxburgh.me.uk >
 *
 * DOCUMENTATION
 * =============
 *   http://code.google.com/p/php-excel-reader/wiki/Documentation
 *
 * CHANGE LOG
 * ==========
 *   http://code.google.com/p/php-excel-reader/wiki/ChangeHistory
 *
 * DISCUSSION/SUPPORT
 * ==================
 *   http://groups.google.com/group/php-excel-reader-discuss/topics
 *
 * --------------------------------------------------------------------------
 *
 * 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 [email protected] so we can mail you a copy immediately.
 *
 * @category   Spreadsheet
 * @package    Spreadsheet_Excel_Reader
 * @author     Vadim Tkachenko <[email protected]>
 * @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
 * --------------------------------------------------------------------------
 */

define('NUM_BIG_BLOCK_DEPOT_BLOCKS_POS', 0x2c);
define('SMALL_BLOCK_DEPOT_BLOCK_POS', 0x3c);
define('ROOT_START_BLOCK_POS', 0x30);
define('BIG_BLOCK_SIZE', 0x200);
define('SMALL_BLOCK_SIZE', 0x40);
define('EXTENSION_BLOCK_POS', 0x44);
define('NUM_EXTENSION_BLOCK_POS', 0x48);
define('PROPERTY_STORAGE_BLOCK_SIZE', 0x80);
define('BIG_BLOCK_DEPOT_BLOCKS_POS', 0x4c);
define('SMALL_BLOCK_THRESHOLD', 0x1000);
// property storage offsets
define('SIZE_OF_NAME_POS', 0x40);
define('TYPE_POS', 0x42);
define('START_BLOCK_POS', 0x74);
define('SIZE_POS', 0x78);
define('IDENTIFIER_OLE', pack("CCCCCCCC",0xd0,0xcf,0x11,0xe0,0xa1,0xb1,0x1a,0xe1));


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;
}

// http://uk.php.net/manual/en/function.getdate.php
function gmgetdate($ts = null){
    $k = array('seconds','minutes','hours','mday','wday','mon','year','yday','weekday','month',0);
    return(array_comb($k,explode(":",gmdate('s:i:G:j:w:n:Y:z:l:F:U',is_null($ts)?time():$ts))));
    } 

// Added for PHP4 compatibility
function array_comb($array1, $array2) {
    $out = array();
    foreach ($array1 as $key => $value) {
        $out[$value] = $array2[$key];
    }
    return $out;
}

function v($data,$pos) {
    return ord($data[$pos]) | ord($data[$pos+1])<<8;
}

class OLERead {
    var $data = '';
    function OLERead(){    }

    function read($sFileName){
        // check if file exist and is readable (Darko Miljanovic)
        if(!is_readable($sFileName)) {
            $this->error = 1;
            return false;
        }
        $this->data = @file_get_contents($sFileName);
        if (!$this->data) {
            $this->error = 1;
            return false;
           }
           if (substr($this->data, 0, 8) != IDENTIFIER_OLE) {
            $this->error = 1;
            return false;
           }
        $this->numBigBlockDepotBlocks = GetInt4d($this->data, NUM_BIG_BLOCK_DEPOT_BLOCKS_POS);
        $this->sbdStartBlock = GetInt4d($this->data, SMALL_BLOCK_DEPOT_BLOCK_POS);
        $this->rootStartBlock = GetInt4d($this->data, ROOT_START_BLOCK_POS);
        $this->extensionBlock = GetInt4d($this->data, EXTENSION_BLOCK_POS);
        $this->numExtensionBlocks = GetInt4d($this->data, NUM_EXTENSION_BLOCK_POS);

        $bigBlockDepotBlocks = array();
        $pos = BIG_BLOCK_DEPOT_BLOCKS_POS;
        $bbdBlocks = $this->numBigBlockDepotBlocks;
        if ($this->numExtensionBlocks != 0) {
            $bbdBlocks = (BIG_BLOCK_SIZE - BIG_BLOCK_DEPOT_BLOCKS_POS)/4;
        }

        for ($i = 0; $i < $bbdBlocks; $i++) {
            $bigBlockDepotBlocks[$i] = GetInt4d($this->data, $pos);
            $pos += 4;
        }


        for ($j = 0; $j < $this->numExtensionBlocks; $j++) {
            $pos = ($this->extensionBlock + 1) * BIG_BLOCK_SIZE;
            $blocksToRead = min($this->numBigBlockDepotBlocks - $bbdBlocks, BIG_BLOCK_SIZE / 4 - 1);

            for ($i = $bbdBlocks; $i < $bbdBlocks + $blocksToRead; $i++) {
                $bigBlockDepotBlocks[$i] = GetInt4d($this->data, $pos);
                $pos += 4;
            }

            $bbdBlocks += $blocksToRead;
            if ($bbdBlocks < $this->numBigBlockDepotBlocks) {
                $this->extensionBlock = GetInt4d($this->data, $pos);
            }
        }

        // readBigBlockDepot
        $pos = 0;
        $index = 0;
        $this->bigBlockChain = array();

        for ($i = 0; $i < $this->numBigBlockDepotBlocks; $i++) {
            $pos = ($bigBlockDepotBlocks[$i] + 1) * BIG_BLOCK_SIZE;
            //echo "pos = $pos";
            for ($j = 0 ; $j < BIG_BLOCK_SIZE / 4; $j++) {
                $this->bigBlockChain[$index] = GetInt4d($this->data, $pos);
                $pos += 4 ;
                $index++;
            }
        }

        // readSmallBlockDepot();
        $pos = 0;
        $index = 0;
        $sbdBlock = $this->sbdStartBlock;
        $this->smallBlockChain = array();

        while ($sbdBlock != -2) {
          $pos = ($sbdBlock + 1) * BIG_BLOCK_SIZE;
          for ($j = 0; $j < BIG_BLOCK_SIZE / 4; $j++) {
            $this->smallBlockChain[$index] = GetInt4d($this->data, $pos);
            $pos += 4;
            $index++;
          }
          $sbdBlock = $this->bigBlockChain[$sbdBlock];
        }


        // readData(rootStartBlock)
        $block = $this->rootStartBlock;
        $pos = 0;
        $this->entry = $this->__readData($block);
        $this->__readPropertySets();
    }

    function __readData($bl) {
        $block = $bl;
        $pos = 0;
        $data = '';
        while ($block != -2)  {
            $pos = ($block + 1) * BIG_BLOCK_SIZE;
            $data = $data.substr($this->data, $pos, BIG_BLOCK_SIZE);
            $block = $this->bigBlockChain[$block];
        }
        return $data;
     }

    function __readPropertySets(){
        $offset = 0;
        while ($offset < strlen($this->entry)) {
            $d = substr($this->entry, $offset, PROPERTY_STORAGE_BLOCK_SIZE);
            $nameSize = ord($d[SIZE_OF_NAME_POS]) | (ord($d[SIZE_OF_NAME_POS+1]) << 8);
            $type = ord($d[TYPE_POS]);
            $startBlock = GetInt4d($d, START_BLOCK_POS);
            $size = GetInt4d($d, SIZE_POS);
            $name = '';
            for ($i = 0; $i < $nameSize ; $i++) {
                $name .= $d[$i];
            }
            $name = str_replace("\x00", "", $name);
            $this->props[] = array (
                'name' => $name,
                'type' => $type,
                'startBlock' => $startBlock,
                'size' => $size);
            if ((strtolower($name) == "workbook") || ( strtolower($name) == "book")) {
                $this->wrkbook = count($this->props) - 1;
            }
            if ($name == "Root Entry") {
                $this->rootentry = count($this->props) - 1;
            }
            $offset += PROPERTY_STORAGE_BLOCK_SIZE;
        }

    }


    function getWorkBook(){
        if ($this->props[$this->wrkbook]['size'] < SMALL_BLOCK_THRESHOLD){
            $rootdata = $this->__readData($this->props[$this->rootentry]['startBlock']);
            $streamData = '';
            $block = $this->props[$this->wrkbook]['startBlock'];
            $pos = 0;
            while ($block != -2) {
                    $pos = $block * SMALL_BLOCK_SIZE;
                  $streamData .= substr($rootdata, $pos, SMALL_BLOCK_SIZE);
                  $block = $this->smallBlockChain[$block];
            }
            return $streamData;
        }else{
            $numBlocks = $this->props[$this->wrkbook]['size'] / BIG_BLOCK_SIZE;
            if ($this->props[$this->wrkbook]['size'] % BIG_BLOCK_SIZE != 0) {
                $numBlocks++;
            }

            if ($numBlocks == 0) return '';
            $streamData = '';
            $block = $this->props[$this->wrkbook]['startBlock'];
            $pos = 0;
            while ($block != -2) {
              $pos = ($block + 1) * BIG_BLOCK_SIZE;
              $streamData .= substr($this->data, $pos, BIG_BLOCK_SIZE);
              $block = $this->bigBlockChain[$block];
            }
            return $streamData;
        }
    }

}

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_FONT',      0x0031);
define('SPREADSHEET_EXCEL_READER_TYPE_PALETTE',      0x0092);
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_TYPE_HYPER',         0x01b8);
define('SPREADSHEET_EXCEL_READER_TYPE_COLINFO',         0x7d);
define('SPREADSHEET_EXCEL_READER_TYPE_DEFCOLWIDTH',  0x55);
define('SPREADSHEET_EXCEL_READER_TYPE_STANDARDWIDTH', 0x99);
define('SPREADSHEET_EXCEL_READER_DEF_NUM_FORMAT',    "%s");


/*
* Main Class
*/
class Spreadsheet_Excel_Reader {

    // MK: Added to make data retrieval easier
    var $colnames = array();
    var $colindexes = array();
    var $standardColWidth = 0;
    var $defaultColWidth = 0;

    function myHex($d) {
        if ($d < 16) return "0" . dechex($d);
        return dechex($d);
    }
    
    function dumpHexData($data, $pos, $length) {
        $info = "";
        for ($i = 0; $i <= $length; $i++) {
            $info .= ($i==0?"":" ") . $this->myHex(ord($data[$pos + $i])) . (ord($data[$pos + $i])>31? "[" . $data[$pos + $i] . "]":'');
        }
        return $info;
    }

    function getCol($col) {
        if (is_string($col)) {
            $col = strtolower($col);
            if (array_key_exists($col,$this->colnames)) {
                $col = $this->colnames[$col];
            }
        }
        return $col;
    }

    // PUBLIC API FUNCTIONS
    // --------------------

    function val($row,$col,$sheet=0) {
        $col = $this->getCol($col);
        if (array_key_exists( 
                       
                    
                    

鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
phpintval()函数漏洞,is_numeric()漏洞,绕过回文判断发布时间:2022-07-12
下一篇:
{php数据类型}发布时间:2022-07-12
热门推荐
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap