Ưμ㼯A
PHP Excel Helper - Write and read Spreadsheet with easy way based on PhpSpreadsheet This library is a helper that encapsulate PhpSpreadsheet (Documentation) for simple usage.
Output an Excel file to browser for download:
\yidas\phpSpreadsheet\Helper::newSpreadsheet()
->addRow(['ID', 'Name', 'Email'])
->addRows([
['1', 'Nick','[email protected]'],
['2', 'Eric','eric@.....'],
])
->output('My Excel');Import above excel file and return two-dimensional array data contained rows > columns spread sheet:
$data = \yidas\phpSpreadsheet\Helper::newSpreadsheet('/tmp/My Excel.xlsx')
->getRows();
print_r($data);Output result:
Array
(
[0] => Array
(
[0] => ID
[1] => Name
[2] => Email
)
[1] => Array
(
[0] => 1
[1] => Nick
[2] => [email protected]
)
[2] => Array
(
[0] => 2
[1] => Eric
[2] => eric@.....
)
)This library requires the following:
Run Composer in your project:
composer require yidas/phpspreadsheet-helperThen you could call it after Composer is loaded depended on your PHP framework:
require __DIR__ . '/vendor/autoload.php';
\yidas\phpSpreadsheet\Helper::newSpreadsheet();Simpliy read an Excel file then output to browser:
\yidas\phpSpreadsheet\Helper::newSpreadsheet('/tmp/excel.xlsx')
->addRow(['Modified A1'])
->output();New or load an PhpSpreadsheet object
public static array newSpreadsheet(object|string $spreadSheet=null)Output file to browser
public static void output(string $filename='excel', string $format='Xlsx')$format list: Xlsx, Xls, Html, Csv, Ods
Save as file
public static string save(string $filename='excel', string $format='Xlsx')Example:
\yidas\phpSpreadsheet\Helper::newSpreadsheet()
->addRow(['Add A1'])
->save("/tmp/save");
// /tmp/save.xlsxGet data of a row from the actived sheet of PhpSpreadsheet
public static array getRow(boolean $toString=true, array $options=[], callable $callback=null)Example:
use \yidas\phpSpreadsheet\Helper;
$row1 = Helper::newSpreadsheet($filepath)
->getRow();
$row2 = Helper::getRow();
print_r($row1);
print_r($row2);Example of fetching content per each row (Example Code):
$helper = \yidas\phpSpreadsheet\Helper::newSpreadsheet($filepath);
while ($row = $helper->getRow()) {
// Each row data process
}Get rows from the actived sheet of PhpSpreadsheet
public static array getRows(boolean $toString=true, array $options=[], callable $callback=null)Add a row to the actived sheet of PhpSpreadsheet
public static self addRow(array $rowData, array $rowAttributes=null)$rowData value: An array contains string or array of attributes for each cell$rowAttributes value: string or array of attributes for each cell of a row
Example of setting attributes for each cell:
\yidas\phpSpreadsheet\Helper::newSpreadsheet()
->addRow([['value'=>'ID'], ['value'=>'Name'], ['value'=>'Email']])
->addRow(['ID', 'Name', 'Email']);Example of setting attributes for each row:
\yidas\phpSpreadsheet\Helper::newSpreadsheet()
// Set width as 25 to all cells of this row
->addRow([['value'=>'ID'], ['value'=>'Name'], ['value'=>'Email']], ['width'=>25]);Add rows to the actived sheet of PhpSpreadsheet
public static self addRows(array $data, array $rowAttributes=null)$data value: array of each $rowData from addRow()$rowAttributes value: string or array of attributes for each row
Attributes is a standard array for defining a cell or even a row, the keys are as follows:
key, value, col, row, skip, width, style
This helper is flexible that you could inject or extract original PhpSpreadsheet with it, when you need to manipulate some Phpspreadsheet methods integrated with Helper.
…use \yidas\phpSpreadsheet\Helper;
Helper::newSpreadsheet()
->setSheet(0, 'Sheet')
->addRow(['SN']);
// Get the PhpSpreadsheet object created by Helper
$objSpreadsheet = Helper::getSpreadsheet();
$objSpreadsheet->getProperties()
->setCreator("Nick Tsai")
->setTitle("Office 2007 XLSX Document");
// Get the actived sheet object created by Helper
$objSheet = Helper::getSheet();
$objSheet->setCellValue('A2', '1');
$objSheet->setCellValue('A3', '2');
Helper::output();It's easy to merge cells by defining each cell's span attributes:
row : Number of rowspan cells to merge with col : Number of colspan cells to merge with skip : Number of colspan cells to merge with\yidas\phpSpreadsheet\Helper::newSpreadsheet()
->addRows([
[['value'=>'SN', 'row'=>2], ['value'=>'Language', 'col'=>2], ['value'=>'Block', 'row'=>2, 'col'=>2]],
['','English','繁體中文',['skip'=>2]],
])
->addRows([
['1', 'Computer','電腦','#15'],
['2', 'Phone','手機','#4','#62'],
])
->output('Merged Excel');Set an active PhpSpreadsheet Sheet
public static self setSheet($sheet=0, $title=NULL, $normalizeTitle=false)Get PhpSpreadsheet Sheet object from cache
public static object getSheet($identity=null, $autoCreate=false)Example:
…getActiveSheetIndex(): Get active sheet indexgetSheetCount(): Get sheet count…The result could be:
Array
(
[sn] => A1
[lang] => B1
[block] => D1
[lang-en] => B2
[lang-zh] => C2
[block-skip] => D2
)
Array
(
[sn] => A1:A2
[lang] => B1:C1
[block] => D1:E2
[lang-en] => B2:B2
[lang-zh] => C2:C2
[block-skip] => D2:E2
)
sn start cell: A1
sn start column: A
sn start row: 1
sn range: A1:A2
All range: A1:E4The style attribute could be set on a single cell, a single row or even a range of cells.
style: a attribute refers to applyFromArray() for styling…Style array key/value/constant refers Valid array keys for style
applyFromArray()or Source Classes
The options for each cell data:
width: setWidth() for the column\yidas\phpSpreadsheet\Helper::newSpreadsheet()
->addRow([['value'=>'ID', 'width'=>10], ['value'=>'Name', 'width'=>25], ['value'=>'Email', 'width'=>50]])
->addRows([
['1', 'Nick','[email protected]'],
['2', 'Eric','eric@.....'],
])
->output('My Excel'); This section focuses on applying all actived cells or ranged cells on the sheet, not just effecting single cell, row or column.
Set Style for all actived cells or set by giving range to the actived sheet
public static self setStyle(array $styleArray, string $range=NULL)Example:
…Set WrapText for all actived cells or set by giving range to the actived sheet
public static self setWrapText(string $range=NULL, string $value=true)Set AutoSize for all actived cells or set by giving column range to the actived sheet
public static self setAutoSize(string $colAlphaStart=NULL, string $colAlphaEnd=NULL, boolean $value=true)This helper will automatically align the spreadsheet’s timezone with your PHP default timezone.
However, you can still customize the target timezone for the spreadsheet if needed.
public static self setTimezone(string $timezoneString=NULL)Example:
use \yidas\phpSpreadsheet\Helper;
Helper::setTimezone("Europe/London");
echo Helper::getTimezone();
$rows = $rowHelper::newSpreadsheet('/tmp/excel.xlsx')
->getRows();If you're building large cell data with XLSX, you may face performance issue with memory usage and execution time.
box/spout spreadsheet lirary supports building Excel file with high performance, you could use this library instead if you do not need more style and formatting requirements.
暂无开放 Issues,或尚未同步最近议题。