README

一个围绕 PhpSpreadsheet 库的 PHP 库封装器。

composer require dprmc/excel

用法：创建简单工作表

以下是一个展示如何使用此类的示例。

您可以看到我们创建了一些关联数组

• $rows
• $totals

此库将 $rows 数组的键作为列标题。

如果输出文件已存在，此方法将在末尾附加时间戳以尝试创建一个唯一的文件名。

$rows[]     = [
    'CUSIP'  => '123456789',
    'DATE'   => '2018-01-01',
    'ACTION' => 'BUY',
];
$totals     = [
    'CUSIP'  => '1',
    'DATE'   => '2',
    'ACTION' => '3',
];

$options = [
    'title'    => "Sample Title",
    'subject'  => "CUSIP List",
    'category' => "Back Office",
];

$pathToFile = Excel::simple( $rows, $totals, "Tab Label", '/outputFile.xlsx', $options );

用法：创建高级工作表

以下是一个展示如何使用此类的示例。

您可以看到我们创建了多个关联数组

• $rows
• $totals
• $options
• $columnDataTypes
• $columnsWithCustomNumberFormats
• $columnsWithCustomWidths
• $styles

'$columnDataTypes' 可选的关联数组参数将应用数组的值作为数据类型到对应数组的列单元格。

'$columnsWithCustomNumberFormats' 可选的关联数组参数将应用数组的数值格式到对应数组的列单元格。

'$columnsWithCustomWidths' 可选的关联数组参数将应用数组的宽度值到对应数组的列单元格。

'$styles' 可选的关联数组参数将应用 $styles 数组的样式值到相应的列标题、非标题单元格或特定单元格。

此外，可选的布尔参数 '$freezeHeader' 将确定是否冻结标题行。默认为 'TRUE'。

如果输出文件已存在，此方法将在末尾附加时间戳以尝试创建一个唯一的文件名。

$rows[]     = [
    'CUSIP'  => '123456789',
    'DATE'   => '2018-01-01',
    'PRICE'  => '123.45',   
    'ACTION' => 'BUY',
    'FORM'   => '=IFERROR(((E2-D2)/D2),"")'
];
$totals     = [
    'CUSIP'  => '1',
    'DATE'   => '2',
    'PRICE'  => '3',
    'ACTION' => '4',
    'FORM'   => '5'
];

$sheetName = 'Sheet Name';
$pathToFile = '/outputFile.xlsx';
$options = [];

$columnDataTypes = [
    'CUSIP' => DataType::TYPE_STRING,
    'DATE'  => DataType::TYPE_STRING,
    'PRICE' => DataType::TYPE_NUMERIC,
    'FORM'  => DataType::TYPE_FORMULA
];
$columnsWithCustomNumberFormats = [
    'PRICE' => Excel::FORMAT_NUMERIC,
    'FORM'  => NumberFormat::FORMAT_NUMBER
];
$columnsWithCustomWidths = [
    'CUSIO' => 50,
    'PRICE' => 75,
    'FORM' => 100
];
$styles = [
    'CUSIP'   => [ 'font' => [ 'bold' => TRUE ] ], // Apply style to column header
    'CUSIP:*' => [ 'borders' => [ 'top' => [ 'borderStyle' => 'thin'] ] ], // Apply style to all column rows except header row
    'DATE:4'  => [ 'fill' => [ 'fillType' => 'linear', 'rotation' => 90 ] ] // Apply style to cell in column and specified row 
];

$freezeHeader = TRUE;
$pathToFile = Excel::advanced( $rows, $totals, $sheetName, $pathToFile, $options, $columnDataTypes, $columnsWithCustomNumberFormats, $columnsWithCustomWidths, $styles, $freezeHeader );

用法：创建包含多个工作表的工作簿

使用多维关联数组创建包含多个工作表的工作簿。多维数组的每个键将在工作簿中代表一个新的工作表。多维数组的每个值都遵循上面示例中显示的高级工作表的格式。

$pathToFile = '/outputFile.xlsx';
$options = [];

$workbook['first sheet'] = [
    'rows'                           => [], // A multidimensional array with each item representing a row on the sheet
    'totals'                         => [], 
    'columnDataTypes'                => [],
    'columnsWithCustomNumberFormats' => [],
    'columnsWithCustomWidths'        => [],
    'styles'                         => [],
    'freezeHeader'                   => TRUE // A boolean value, defaults to true
];

$workbook['first sheet']['rows'][0] = [
    'CUSIP'     => '123456789',
    'DATE'      => '2024-01-01',
    'ACTION'    => 'BUY',
    'PRICE'     => '123.456',
    'QUANTITY'  => '1'
];

$workbook['first sheet']['rows'][1] = [
    'CUSIP'     => '123456789',
    'DATE'      => '2024-09-01',
    'ACTION'    => 'SELL',
    'PRICE'     => '123.456',
    'QUANTITY'  => '1'
];

$workbook['first sheet']['totals'] = [
    'CUSIP'     => '123456789',
    'DATE'      => '2024-09-17',
    'ACTION'    => '',
    'PRICE'     => '123.456',
    'QUANTITY'  => '0'
];

$workbook['first sheet']['columnDataTypes'] = [
    'CUSIP'     => DataType::TYPE_STRING,
    'ACTION'    => DataType::TYPE_STRING,
    'PRICE'     => DataType::TYPE_NUMERIC,
    'QUANTITY'  => DataType::TYPE_NUMERIC
];

$workbook['first sheet']['columnsWithCustomNumberFormats'] = [
    'PRICE'     => Excel::FORMAT_NUMERIC,
    'QUANTITY'  => Excel::FORMAT_NUMERIC
];

$workbook['first sheet']['columnsWithCustomWidths'] = [
    'CUSIP'    => 50,
    'PRICE'    => 50,
    'ACTION'   => 25,
    'QUANTITY' => 25
];

$workbook['first sheet']['styles'] = [
    'CUSIP' => [
        'font' => ['bold' => TRUE]
    ]            
];

$workbook['second sheet'] = [];
$workbook['second sheet']['rows'][0] = [
    'CUSIP' => '987654321',
    'NAV'   => '1234.56'
];
$workbook['second sheet']['rows'][1] = [
    'CUSIP' => 'ABCDEFGHI',
    'NAV'   => '6543.21'
];

$workbook['second sheet']['totals'] = [];
$workbook['second sheet']['columnDataTypes'] = [
    'CUSIP' => DataType::TYPE_STRING,
    'NAV'   => DataType::TYPE_NUMERIC
];
$workbook['second sheet']['columnsWithCustomNumberFormats'] = ['NAV' => Excel::FORMAT_NUMERIC];
$workbook['second sheet']['columnsWithCustomWidths'] = [];
$workbook['second sheet']['styles'] = [
    'CUSIP' => [
        'font' => ['bold' => TRUE]
    ],
    'NAV' => [
        'font' => ['italic' => TRUE]  
    ]
];                   
$workbook['second sheet']['freezeHeader'] = FALSE;
$workbook['third sheet'] = [];
$workbook['third sheet']['rows'][0] = [
    'CUSIP' => '000111222',
    'NAV'   => '56.78'
];
$workbook['third sheet']['rows'][1] = [
    'CUSIP' => 'AAABBBCCC',
    'NAV'   => '111'
];

$pathToFile = Excel::multiSheet( $pathToFile, $options, $workbook );

用法：将工作表读取到 PHP 数组中

传递 XLSX 工作表的路径和表名，此方法将返回一个关联数组。

/**  Define a Read Filter class implementing \PhpOffice\PhpSpreadsheet\Reader\IReadFilter  */
class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter
{
    public function readCell($column, $row, $worksheetName = '') {
        //  Read rows 1 to 7 and columns A to E only
        if ($row >= 1 && $row <= 7) {
            if (in_array($column,range('A','E'))) {
                return true;
            }
        }
        return false;
    }
}
/**  Create an Instance of our Read Filter  **/
$filterSubset = new MyReadFilter();


$pathToWorkbook = '/outputFile.xlsx';
$sheetName = 'Security_Pricing_Update';
$array = Excel::sheetToArray($pathToWorkbook, $sheetName, $filterSubset);
print_r($array);