最近开发遇到了PHP读取Excel的问题,找了几个Excel的库,发现这个很是简单好用,这里就MARK一下,留着以后备用.
你可以在Google的https://code.google.com/p/php-excel-reader/ 找到这个库的项目主页,如果你打不开Google的话,我这里已经上传到百度网盘了,下载地址在页面最下面,这里是项目主页的一个预览图
下面是ExcelReader的文档:
默认创建一个带有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");
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')
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)
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)
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)
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]; }