网站已经改版为Wordpress版本,这里是旧版本的快照,请不要在页面中留言.

php-excel-reader,PHP解析Excel文件


最近开发遇到了PHP读取Excel的问题,找了几个Excel的库,发现这个很是简单好用,这里就MARK一下,留着以后备用.

你可以在Google的https://code.google.com/p/php-excel-reader/ 找到这个库的项目主页,如果你打不开Google的话,我这里已经上传到百度网盘了,下载地址在页面最下面,这里是项目主页的一个预览图

下面是ExcelReader的文档:

Creating The Reader Object(创建一个Reader对象)

默认创建一个带有Cell颜色字体信息并且编码为UTF-8的对象

$data = new Spreadsheet_Excel_Reader("test.xls");

 

Or conserve memory for large worksheets by not storing the extended information about cells like fonts, colors, etc.(如果表比较大,不存储表的Cell字体、颜色等信息,就是光存储对应的数据信息)

$data = new Spreadsheet_Excel_Reader("test.xls",false);

 

To use a coding other than UTF-8 (default) you can pass it as the 3rd parameter.(如果是其他的编码,参数3是编码名称,注意参数2表示是否存储Cell的字体,颜色等信息的)

$data = new Spreadsheet_Excel_Reader("test.xls",true,"UTF-16");

 

 

Dumping Worksheet Contentsdump输出工作表)

The simplest way to interact with an XLS file is to just dump it to HTML for display in a browser. This method will generate a table with inline CSS and all available formatting.

这个方法直接把表格数据dump输出到浏览器,包含表格的字体颜色等信息

$data->dump($row_numbers=false,$col_letters=false,$sheet=0,$table_class='excel')

Accessing Cell Values(读取一个Cell的数据)

It is recommended that the public API functions be used to access data rather than relying on the underlying data structure, which may change between releases.

Retrieve the formatted value of a cell (what is displayed by Excel) on the first (or only) worksheet:

$data->val($row,$col)

You can also use column names rather than numbers:

$data->val(10,'AZ')

Access data on a different sheet:

$data->val($row,$col,$sheet_index)

Sheet Info

Get the count of how many rows/cols are on a sheet (default: first sheet):

获取到工作表的有多少行或者多少列,参数表示是哪一个sheet,默认是0号sheet

$data->rowcount($sheet_index=0) 

$data->colcount($sheet_index=0)

Cell InfoCell内容的类型)

The type of data in the cell: number|date|unknown

$data->type($row,$col,$sheet=0)

 

The raw data stored for the cell. For example, a cell may contain 123.456 but display as 123.5 because of the cell's format. Raw accesses the underlying value.

$data->raw($row,$col,$sheet=0)

If the cell has a hyperlink associated with it, the url can be retrieved.

$data->hyperlink($row,$col,$sheet=0)

Rowspan/Colspan of the cell.

$data->rowspan($row,$col,$sheet=0) $data->colspan($row,$col,$sheet=0)

Formatting Details(格式信息)

The style method retrieves all available formatting information and returns a CSS string with all attributes.

$data->style($row,$col,$sheet=0)

The format string for the cell.

$data->format($row,$col,$sheet=0)

Cell alignment. Either 'right', 'center', or '' (left)

$data->align($row,$col,$sheet=0)

Background color, in #FFFFFF format.

$data->bgColor($row,$col,$sheet=0)

Get border type for any of the cell's sides. Possible return values are:

  • Thin

  • Medium

  • Dashed

  • Dotted

  • Thick

  • Double

  • Hair

  • Medium dashed

  • Thin dash-dotted

  • Medium dash-dotted

  • Thin dash-dot-dotted

  • Medium dash-dot-dotted

  • Slanted medium dash-dotted

$data->borderLeft($row,$col,$sheet=0)

$data->borderRight($row,$col,$sheet=0)

$data->borderTop($row,$col,$sheet=0)

$data->borderBottom($row,$col,$sheet=0)

Get the color of each of the borders, in #FFFFFF format.

$data->borderLeftColor($row,$col,$sheet=0)

$data->borderRightColor($row,$col,$sheet=0)

$data->borderTopColor($row,$col,$sheet=0)

$data->borderBottomColor($row,$col,$sheet=0)

The font color, which may be determined either by cell properties or by format properties, in #FFFFFF format.

$data->color($row,$col,$sheet=0)

Other font properties:

$data->bold($row,$col,$sheet=0) // Boolean

$data->italic($row,$col,$sheet=0) // Boolean

$data->underline($row,$col,$sheet=0) // Boolean

$data->height($row,$col,$sheet=0) // Number in pixels

$data->font($row,$col,$sheet=0) // Font name

上面有很多的使用方法,这里我就列出一个我在ThinkPHP读取Excel的方法:

在ThinkPHP中,我把这个库放到了./Application/Ext/ 这个文件夹中,在我的函数中我是通过获取到上传的XLS文件进行操作的下面是我的代码

require_once("./Application/Ext/excel/reader.php");
$xls = new \Spreadsheet_Excel_Reader();
$xls->setOutputEncoding('utf-8');  //设置编码 
//这个filename是相对于ThinkPHP的安装目录的,例如: “./Public/temp/123456.xls”
$xls->read($file_name);  //解析文件
//循环读取
for($i=1; $i<=$xls->sheets[0]['numRows']; $i++) {
 //如果表格数据为空,我就停止读取了
 if(!isset($xls->sheets[0]['cells'][$i])){break;}
 //将数据获取到,注意下标是1开始的
 echo  $xls->sheets[0]['cells'][$i][1];
}


附上:php-excel-reader下载地址


  • 标签:
  • PHP读取Excel
  • excel-reader
网站已经改版为Wordpress版本,这里是旧版本的快照,请不要在页面中留言.