FastExcelReader是 FastExcelPhp 项目的一部分,该项目包括
该库被设计为轻量级、超快并且需要最少的内存使用。
FastExcelReader可以读取 XLSX 格式 (Office 2007+) 的 Excel 兼容电子表格。它只读取数据,但速度非常快并且内存使用量最少。
特征
使用composer
将FastExcelReader安装到您的项目中:
composer require avadim/fast-excel-reader
跳转至:
您可以在/demo文件夹中找到更多示例
use avadim FastExcelReader Excel ;
$ file = __DIR__ . ' /files/demo-00-simple.xlsx ' ;
// Open XLSX-file
$ excel = Excel:: open ( $ file );
// Read all values as a flat array from current sheet
$ result = $ excel -> readCells ();
你将得到这个数组:
Array
(
[A1] => 'col1'
[B1] => 'col2'
[A2] => 111
[B2] => 'aaa'
[A3] => 222
[B3] => 'bbb'
)
// Read all rows in two-dimensional array (ROW x COL)
$ result = $ excel -> readRows ();
你将得到这个数组:
Array
(
[1] => Array
(
['A'] => 'col1'
['B'] => 'col2'
)
[2] => Array
(
['A'] => 111
['B'] => 'aaa'
)
[3] => Array
(
['A'] => 222
['B'] => 'bbb'
)
)
// Read all columns in two-dimensional array (COL x ROW)
$ result = $ excel -> readColumns ();
你将得到这个数组:
Array
(
[A] => Array
(
[1] => 'col1'
[2] => 111
[3] => 222
)
[B] => Array
(
[1] => 'col2'
[2] => 'aaa'
[3] => 'bbb'
)
)
$ sheet = $ excel -> sheet ();
foreach ( $ sheet -> nextRow () as $ rowNum => $ rowData ) {
// $rowData is array ['A' => ..., 'B' => ...]
$ addr = ' C ' . $ rowNum ;
if ( $ sheet -> hasImage ( $ addr )) {
$ sheet -> saveImageTo ( $ addr , $ fullDirectoryPath );
}
// handling of $rowData here
// ...
}
// OR
foreach ( $ sheet -> nextRow () as $ rowNum => $ rowData ) {
// handling of $rowData here
// ...
// get image list from current row
$ imageList = $ sheet -> getImageListByRow ();
foreach ( $ imageList as $ imageInfo ) {
$ imageBlob = $ sheet -> getImageBlob ( $ imageInfo [ ' address ' ]);
}
}
// OR
foreach ( $ sheet -> nextRow ([ ' A ' => ' One ' , ' B ' => ' Two ' ], Excel:: KEYS_FIRST_ROW ) as $ rowNum => $ rowData ) {
// $rowData is array ['One' => ..., 'Two' => ...]
// ...
}
逐行读取的另一种方法
// Init internal read generator
$ sheet -> reset ([ ' A ' => ' One ' , ' B ' => ' Two ' ], Excel:: KEYS_FIRST_ROW );
// read the first row
$ rowData = $ sheet -> readNextRow ();
var_dump ( $ rowData );
// read the next 3 rows
for ( $ i = 0 ; $ i < 3 ; $ i ++) {
$ rowData = $ sheet -> readNextRow ();
var_dump ( $ rowData );
}
// Reset internal generator and read all rows
$ sheet -> reset ([ ' A ' => ' One ' , ' B ' => ' Two ' ], Excel:: KEYS_FIRST_ROW );
$ result = [];
while ( $ rowData = $ sheet -> readNextRow ()) {
$ result [] = $ rowData ;
}
var_dump ( $ result );
// Read rows and use the first row as column keys
$ result = $ excel -> readRows ( true );
你会得到这样的结果:
Array
(
[2] => Array
(
['col1'] => 111
['col2'] => 'aaa'
)
[3] => Array
(
['col1'] => 222
['col2'] => 'bbb'
)
)
可选的第二个参数指定结果数组键
// Rows and cols start from zero
$ result = $ excel -> readRows ( false , Excel:: KEYS_ZERO_BASED );
你会得到这样的结果:
Array
(
[0] => Array
(
[0] => 'col1'
[1] => 'col2'
)
[1] => Array
(
[0] => 111
[1] => 'aaa'
)
[2] => Array
(
[0] => 222
[1] => 'bbb'
)
)
结果模式的允许值
模式选项 | 描述 |
---|---|
KEYS_ORIGINAL | 行从“1”开始,列从“A”开始(默认) |
KEYS_ROW_ZERO_BASED | 从 0 开始的行 |
KEYS_COL_ZERO_BASED | 从 0 开始的列 |
KEYS_ZERO_BASED | 行从 0 开始,列从 0 开始(与 KEYS_ROW_ZERO_BASED + KEYS_COL_ZERO_BASED 相同) |
KEYS_ROW_ONE_BASED | 从 1 开始的行 |
KEYS_COL_ONE_BASED | 列从 1 |
KEYS_ONE_BASED | 行从 1 开始,列从 1 开始(与 KEYS_ROW_ONE_BASED + KEYS_COL_ONE_BASED 相同) |
可以与结果模式结合使用的附加选项
选项 | 描述 |
---|---|
KEYS_FIRST_ROW | 与第一个参数中的true相同 |
KEYS_RELATIVE | 区域(非工作表)左上角单元格的索引 |
KEYS_SWAP | 交换行和列 |
例如
$ result = $ excel -> readRows ([ ' A ' => ' bee ' , ' B ' => ' honey ' ], Excel:: KEYS_FIRST_ROW | Excel:: KEYS_ROW_ZERO_BASED );
你会得到这样的结果:
Array
(
[0] => Array
(
[bee] => 111
[honey] => 'aaa'
)
[1] => Array
(
[bee] => 222
[honey] => 'bbb'
)
)
默认情况下,库已经跳过空单元格和空行。空单元格是没有写入任何内容的单元格,空行是所有单元格都是空的行。如果单元格包含空字符串,则不将其视为空。但您可以更改此行为并跳过带有空字符串的单元格。
$ sheet = $ excel -> sheet ();
// Skip empty cells and empty rows
foreach ( $ sheet -> nextRow () as $ rowNum => $ rowData ) {
// handle $rowData
}
// Skip empty cells and cells with empty strings
foreach ( $ sheet -> nextRow ([], Excel:: TREAT_EMPTY_STRING_AS_EMPTY_CELL ) as $ rowNum => $ rowData ) {
// handle $rowData
}
// Skip empty cells and empty rows (rows containing only whitespace characters are also considered empty)
foreach ( $ sheet -> nextRow ([], Excel:: TRIM_STRINGS | Excel:: TREAT_EMPTY_STRING_AS_EMPTY_CELL ) as $ rowNum => $ rowData ) {
// handle $rowData
}
其他方式
$ sheet -> reset ([], Excel:: TRIM_STRINGS | Excel:: TREAT_EMPTY_STRING_AS_EMPTY_CELL );
$ rowData = $ sheet -> readNextRow ();
// do something
$ rowData = $ sheet -> readNextRow ();
// handle next row
// ...
use avadim FastExcelReader Excel ;
$ file = __DIR__ . ' /files/demo-02-advanced.xlsx ' ;
$ excel = Excel:: open ( $ file );
$ result = [
' sheets ' => $ excel -> getSheetNames () // get all sheet names
];
$ result [ ' #1 ' ] = $ excel
// select sheet by name
-> selectSheet ( ' Demo1 ' )
// select area with data where the first row contains column keys
-> setReadArea ( ' B4:D11 ' , true )
// set date format
-> setDateFormat ( ' Y-m-d ' )
// set key for column 'C' to 'Birthday'
-> readRows ([ ' C ' => ' Birthday ' ]);
// read other arrays with custom column keys
// and in this case we define range by columns only
$ columnKeys = [ ' B ' => ' year ' , ' C ' => ' value1 ' , ' D ' => ' value2 ' ];
$ result [ ' #2 ' ] = $ excel
-> selectSheet ( ' Demo2 ' , ' B:D ' )
-> readRows ( $ columnKeys );
$ result [ ' #3 ' ] = $ excel
-> setReadArea ( ' F5:H13 ' )
-> readRows ( $ columnKeys );
您可以通过工作簿中定义的名称来设置读取区域。例如,如果工作簿已定义名称标题,范围为Demo1!$B$4:$D$4 ,则您可以按此名称读取单元格
$ excel -> setReadArea ( ' Values ' );
$ cells = $ excel -> readCells ();
请注意,由于该值包含工作表名称,因此该工作表将成为默认工作表。
您可以在工作表中设置读取区域
$ sheet = $ excel -> getSheet ( ' Demo1 ' )-> setReadArea ( ' Headers ' );
$ cells = $ sheet -> readCells ();
但是如果你尝试在另一张纸上使用这个名称,你会得到一个错误
$ sheet = $ excel -> getSheet ( ' Demo2 ' )-> setReadArea ( ' Headers ' );
// Exception: Wrong address or range "Values"
如有必要,您可以使用带有回调函数的readSheetCallback()
方法来完全控制读取过程
use avadim FastExcelReader Excel ;
$ excel = Excel:: open ( $ file );
$ result = [];
$ excel -> readCallback ( function ( $ row , $ col , $ val ) use (& $ result ) {
// Any manipulation here
$ result [ $ row ][ $ col ] = ( string ) $ val ;
// if the function returns true then data reading is interrupted
return false ;
});
var_dump ( $ result );
默认情况下,所有日期时间值均返回为时间戳。但是您可以使用dateFormatter()
更改此行为
$ excel = Excel:: open ( $ file );
$ sheet = $ excel -> sheet ()-> setReadArea ( ' B5:D7 ' );
$ cells = $ sheet -> readCells ();
echo $ cells [ ' C5 ' ]; // -2205187200
// If argument TRUE is passed, then all dates will be formatted as specified in cell styles
// IMPORTANT! The datetime format depends on the locale
$ excel -> dateFormatter ( true );
$ cells = $ sheet -> readCells ();
echo $ cells [ ' C5 ' ]; // '14.02.1900'
// You can specify date format pattern
$ excel -> dateFormatter ( ' Y-m-d ' );
$ cells = $ sheet -> readCells ();
echo $ cells [ ' C5 ' ]; // '1900-02-14'
// set date formatter function
$ excel -> dateFormatter ( fn ( $ value ) => gmdate ( ' m/d/Y ' , $ value ));
$ cells = $ sheet -> readCells ();
echo $ cells [ ' C5 ' ]; // '02/14/1900'
// returns DateTime instance
$ excel -> dateFormatter ( fn ( $ value ) => ( new DateTime ())-> setTimestamp ( $ value ));
$ cells = $ sheet -> readCells ();
echo get_class ( $ cells [ ' C5 ' ]); // 'DateTime'
// custom manipulations with datetime values
$ excel -> dateFormatter ( function ( $ value , $ format , $ styleIdx ) use ( $ excel ) {
// get Excel format of the cell, e.g. '[$-F400]h:mm:ss AM/PM'
$ excelFormat = $ excel -> getFormatPattern ( $ styleIdx );
// get format converted for use in php functions date(), gmdate(), etc
// for example the Excel pattern above would be converted to 'g:i:s A'
$ phpFormat = $ excel -> getDateFormatPattern ( $ styleIdx );
// and if you need you can get value of numFmtId for this cell
$ style = $ excel -> getCompleteStyleByIdx ( $ styleIdx , true );
$ numFmtId = $ style [ ' format-num-id ' ];
// do something and write to $result
$ result = gmdate ( $ phpFormat , $ value );
return $ result ;
});
有时,如果单元格的格式指定为日期但不包含日期,则库可能会误解该值。为了避免这种情况,您可以禁用日期格式
此处,单元格 B1 包含字符串“3.2”,单元格 B2 包含日期 2024-02-03,但这两个单元格都设置为日期格式
$ excel = Excel:: open ( $ file );
// default mode
$ cells = $ sheet -> readCells ();
echo $ cell [ ' B1 ' ]; // -2208798720 - the library tries to interpret the number 3.2 as a timestamp
echo $ cell [ ' B2 ' ]; // 1706918400 - timestamp of 2024-02-03
// date formatter is on
$ excel -> dateFormatter ( true );
$ cells = $ sheet -> readCells ();
echo $ cell [ ' B1 ' ]; // '03.01.1900'
echo $ cell [ ' B2 ' ]; // '3.2'
// date formatter is off
$ excel -> dateFormatter ( false );
$ cells = $ sheet -> readCells ();
echo $ cell [ ' B1 ' ]; // '3.2'
echo $ cell [ ' B2 ' ]; // 1706918400 - timestamp of 2024-02-03
// Returns count images on all sheets
$ excel -> countImages ();
// Returns count images on sheet
$ sheet -> countImages ();
// Returns image list of sheet
$ sheet -> getImageList ();
// Returns image list of specified row
$ sheet -> getImageListByRow ( $ rowNumber );
// Returns TRUE if the specified cell has an image
$ sheet -> hasImage ( $ cellAddress );
// Returns mime type of image in the specified cell (or NULL)
$ sheet -> getImageMimeType ( $ cellAddress );
// Returns inner name of image in the specified cell (or NULL)
$ sheet -> getImageName ( $ cellAddress );
// Returns an image from the cell as a blob (if exists) or NULL
$ sheet -> getImageBlob ( $ cellAddress );
// Writes an image from the cell to the specified filename
$ sheet -> saveImage ( $ cellAddress , $ fullFilenamePath );
// Writes an image from the cell to the specified directory
$ sheet -> saveImageTo ( $ cellAddress , $ fullDirectoryPath );
该库尝试确定单元格值的类型,并且在大多数情况下它做得正确。因此,您将获得数字或字符串值。默认情况下,日期值作为时间戳返回。但是您可以通过设置日期格式来更改此行为(请参阅 date() php 函数的格式选项)。
$ excel = Excel:: open ( $ file );
$ result = $ excel -> readCells ();
print_r ( $ result );
上面的例子将输出:
Array
(
[B2] => -2205187200
[B3] => 6614697600
[B4] => -6845212800
)
$ excel = Excel:: open ( $ file );
$ excel -> setDateFormat ( ' Y-m-d ' );
$ result = $ excel -> readCells ();
print_r ( $ result );
上面的例子将输出:
Array
(
[B2] => '1900-02-14'
[B3] => '2179-08-12'
[B4] => '1753-01-31'
)
通常读取函数仅返回单元格值,但您可以使用样式读取值。在这种情况下,对于每个单元格,不会返回标量值,而是返回一个数组,如 ['v' => scalar_value , 's' => style_array , 'f' => Formula ]
$ excel = Excel:: open ( $ file );
$ sheet = $ excel -> sheet ();
$ rows = $ sheet -> readRowsWithStyles ();
$ columns = $ sheet -> readColumnsWithStyles ();
$ cells = $ sheet -> readCellsWithStyles ();
$ cells = $ sheet -> readCellsWithStyles ();
或者您只能读取样式(没有值)
$ cells = $ sheet -> readCellStyles ();
/*
array (
'format' =>
array (
'format-num-id' => 0,
'format-pattern' => 'General',
),
'font' =>
array (
'font-size' => '10',
'font-name' => 'Arial',
'font-family' => '2',
'font-charset' => '1',
),
'fill' =>
array (
'fill-pattern' => 'solid',
'fill-color' => '#9FC63C',
),
'border' =>
array (
'border-left-style' => NULL,
'border-right-style' => NULL,
'border-top-style' => NULL,
'border-bottom-style' => NULL,
'border-diagonal-style' => NULL,
),
)
*/
$ cells = $ sheet -> readCellStyles ( true );
/*
array (
'format-num-id' => 0,
'format-pattern' => 'General',
'font-size' => '10',
'font-name' => 'Arial',
'font-family' => '2',
'font-charset' => '1',
'fill-pattern' => 'solid',
'fill-color' => '#9FC63C',
'border-left-style' => NULL,
'border-right-style' => NULL,
'border-top-style' => NULL,
'border-bottom-style' => NULL,
'border-diagonal-style' => NULL,
)
*/
但我们不建议对大文件使用这些方法
XLSX 文件中的每个工作表都可以包含一组数据验证规则。要检索它们,您可以暗示在工作表上调用getDataValidations
$ excel = Excel:: open ( $ file );
$ sheet = $ excel -> sheet ();
$ validations = $ sheet -> getDataValidations ();
/*
[
[
'type' => 'list',
'sqref' => 'E2:E527',
'formula1' => '"Berlin,Cape Town,Mexico City,Moscow,Sydney,Tokyo"',
'formula2' => null,
], [
'type' => 'decimal',
'sqref' => 'G2:G527',
'formula1' => '0.0',
'formula2' => '999999.0',
],
]
*/
检索工作表中特定列的宽度:
$ excel = Excel:: open ( $ file );
$ sheet = $ excel -> selectSheet ( ' SheetName ' );
// Get the width of column 1 (column 'A')
$ columnWidth = $ sheet -> getColumnWidth ( 1 );
echo $ columnWidth ; // Example: 11.85
检索工作表中特定行的高度:
$ excel = Excel:: open ( $ file );
$ sheet = $ excel -> selectSheet ( ' SheetName ' );
// Get the height of row 1
$ rowHeight = $ sheet -> getRowHeight ( 1 );
echo $ rowHeight ; // Example: 15
检索工作表的冻结窗格信息:
$ excel = Excel:: open ( $ file );
$ sheet = $ excel -> selectSheet ( ' SheetName ' );
// Get the freeze pane configuration
$ freezePaneConfig = $ sheet -> getFreezePaneInfo ();
print_r ( $ freezePaneConfig );
/*
Example Output:
Array
(
[xSplit] => 0
[ySplit] => 1
[topLeftCell] => 'A2'
)
*/
检索工作表的选项卡颜色信息:
Copy code
$ excel = Excel:: open ( $ file );
$ sheet = $ excel -> selectSheet ( ' SheetName ' );
// Get the tab color configuration
$ tabColorConfig = $ sheet -> getTabColorInfo ();
print_r ( $ tabColorConfig );
/*
Example Output:
Array
(
[theme] => '2'
[tint] => '-0.499984740745262'
)
*/
您可以使用以下方法:
Sheet::getMergedCells()
-- 返回所有合并范围Sheet::isMerged(string $cellAddress)
-- 检查单元格是否合并Sheet::mergedRange(string $cellAddress)
-- 返回指定单元格的合并范围例如
if ( $ sheet -> isMerged ( ' B3 ' )) {
$ range = $ sheet -> mergedRange ( ' B3 ' );
}
getSheetNames()
-- 返回所有工作表的名称数组sheet(?string $name = null)
-- 返回默认或指定的工作表getSheet(string $name, ?string $areaRange = null, ?bool $firstRowKeys = false)
-- 按名称获取工作表getSheetById(int $sheetId, ?string $areaRange = null, ?bool $firstRowKeys = false)
-- 通过 id 获取工作表getFirstSheet(?string $areaRange = null, ?bool $firstRowKeys = false)
-- 获取第一张工作表selectSheet(string $name, ?string $areaRange = null, ?bool $firstRowKeys = false)
-- 按名称选择默认工作表并返回它selectSheetById(int $sheetId, ?string $areaRange = null, ?bool $firstRowKeys = false)
-- 按 id 选择默认工作表并返回selectFirstSheet(?string $areaRange = null, ?bool $firstRowKeys = false)
-- 选择第一个工作表作为默认值并返回它getDefinedNames()
-- 返回工作簿的定义名称name()
-- 返回字符串的名称isActive()
-- 活动工作表isHidden()
-- 如果工作表隐藏isVisible()
-- 如果工作表可见state()
-- 返回工作表的字符串状态(在isHidden()
和isVisible()
中使用)dimension()
-- 从工作表属性返回默认工作区域的尺寸countRows()
-- 计算维度中的行数countColumns()
-- 计算维度中的列数firstRow()
-- 第一行号firstCol()
-- 第一列字母readFirstRow()
-- 以数组形式返回第一行单元格的值readFirstRowWithStyles()
-- 以数组形式返回第一行单元格的值和样式getColumnWidth(int)
-- 返回给定列号的宽度getFreezePaneConfig()
-- 返回包含冻结窗格配置的数组getTabColorConfiguration()
-- 返回包含选项卡颜色配置的数组如果你觉得这个包有用,你可以在 GitHub 上给我一个 star。
或者你可以捐给我:)