Simple Excel Export 簡(jiǎn)單的Excel導(dǎo)出
Basics 基礎(chǔ)
A new file can be created using thecreatemethod with the filename as first parameter.
創(chuàng)建一個(gè)文件，使用第一個(gè)參數(shù)作為文件名。
Excel::create('Filename');
To manipulate the creation of the file you can use the callback
可以使用回調(diào)函數(shù)操作創(chuàng)建的文件。
Excel::create('Filename', function($excel) {
// Call writer methods here
});
Changing properties
更改屬性
There are a couple of properties we can change inside the closure. Most of them are set to the config values by default. See ?app/config/packages/maatwebsite/excel/config.php.
有幾個(gè)屬性可以改變 ? ?大多數(shù)設(shè)置了默認(rèn)。
Excel::create('Filename', function($excel) {
?// Set the title ?設(shè)置標(biāo)題
? ? ? ? ? ? ? ? ?$excel->setTitle('Our new awesome title');
// Chain the setters ?設(shè)置創(chuàng)作人
? ? ? ? ? ? ? ? ? ? $excel->setCreator('Maatwebsite')
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ->setCompany('Maatwebsite');
// Call them separately ? 設(shè)置 介紹說(shuō)明
? ? ? ? ? ? ? ? ? ? ?$excel->setDescription('A demonstration to change the file properties');
});
Go to the reference guide to see a list of available properties.
Exporting導(dǎo)出
To download the created file, use->export($ext) or ->download($ext).
下載創(chuàng)建的excel
Export to Excel5 (xls)格式
Excel::create('Filename', function($excel) {
})->export('xls');
// or
->download('xls');
Export to Excel2007 (xlsx)格式
->export('xlsx');
// or
->download('xlsx');
Export to CSV格式
->export('csv');
// or
->download('csv');
You can set the default enclosure and delimiter inside the config
在配置文件里設(shè)置 范圍 和 定界符
Export to PDF 導(dǎo)出為PDF格式
To export files to pdf, you will have to include"dompdf/dompdf": "~0.6.1","mpdf/mpdf": "~5.7.3"or"tecnick.com/tcpdf": "~6.0.0"in yourcomposer.jsonand change theexport.pdf.driverconfig setting accordingly.
->export('pdf');
如果想要導(dǎo)出格式為PDF，則需要安裝擴(kuò)展
NewExcelFile injections
新的Excel文件 注入
Following the Laravel 5.0 philosophy with its new awesome FormRequest injections, we introduce you NewExcelFile injections.
NewExcelFile class
This NewExcelFile is a wrapper for a new Excel file. Inside thegetFilename()you can declare the wanted filename.
class UserListExport extends \Maatwebsite\Excel\Files\NewExcelFile {
public function getFilename()
? ? ? ? {
? ? ? ? ? ?return 'filename';
? ? ? ? ? }
}
Usage ?使用 用途
You can inject these NewExcelFiles inside the __constructor or inside the method (when using Laravel 5.0), in e.g. the controller.
class ExampleController extends Controller {
public function exportUserList(UserListExport $export)
{
// work on the export
return $export->sheet('sheetName', function($sheet)
{
})->export('xls');
}
}
Export Handlers
導(dǎo)出事件處理器
To decouple your Excel-export code completely from the controller, you can use the export handlers.
class ExampleController extends Controller {
public function exportUserList(UserListExport $export)
{
// Handle the export
$export->handleExport();
}
}
ThehandleExport()method will dynamically call a handler class which is your class name appended withHandler
class UserListExportHandler implements \Maatwebsite\Excel\Files\ExportHandler {
public function handle(UserListExport $export)
{
// work on the export
return $export->sheet('sheetName', function($sheet)
{
})->export('xls');
}
}
Store on server ? 把文件保存到服務(wù)器上
To store the created file on the server, use->store($ext, $path = false, $returnInfo = false)or->save().
Normal export to default storage path
By default the file will be stored inside theapp/storage/exportsfolder, which has been defined in theexport.phpconfig file.
Excel::create('Filename', function($excel) {
// Set sheets
})->store('xls');
Normal export to custom storage path ? 一般導(dǎo)出到自定義的路徑
If you want to use a custom storage path (e.g. to separate the files per client), you can set the folder as the second parameter.
->store('xls', storage_path('excel/exports'));
Store and export ?保存并導(dǎo)出
->store('xls')->export('xls');
Store and return storage info ?保存并顯示保存信息
If you want to return storage information, set the third paramter to true or change the config setting insideexport.php.
->store('xls', false, true);
KeyExplanation
fullFull path with filename
pathPath without filename
fileFilename
titleFile title
extFile extension
Make sure your storage folder iswritable!
Sheets ?工作表
Creating a sheet ? 創(chuàng)建一個(gè)工作表
To create a new sheet inside our newly created file, use->sheet('Sheetname').
Excel::create('Filename', function($excel) {
$excel->sheet('Sheetname', function($sheet) {
// Sheet manipulation
});
})->export('xls');
Creating multiple sheets ? 創(chuàng)建多個(gè)工作表
You can set as many sheets as you like inside the file:
Excel::create('Filename', function($excel) {
// Our first sheet
$excel->sheet('First sheet', function($sheet) {
});
// Our second sheet
$excel->sheet('Second sheet', function($sheet) {
});
})->export('xls');
Changing properties ? 修改屬性
There are a couple of properties we can change inside the closure. Most of them are set to the config values by default. Seeapp/config/packages/maatwebsite/excel/config.php.
Excel::create('Filename', function($excel) {
$excel->sheet('Sheetname', function($sheet) {
$sheet->setOrientation('landscape');
});
})->export('xls');
Go to the reference guide to see a list of available properties.
Default page margin ? ?頁(yè)邊距 ?頁(yè)邊空白
It's possible to set the default page margin inside the config fileexcel::export.sheets. It accepts boolean, single value or array.
To manually set the page margin you can use:->setPageMargin()
// Set top, right, bottom, left
$sheet->setPageMargin(array(
0.25, 0.30, 0.25, 0.30
));
// Set all margins
$sheet->setPageMargin(0.25);
Password protecting a sheet ? 使用密碼保護(hù)一個(gè)工作表
A sheet can be password protected with$sheet->protect():
// Default protect ? 默認(rèn)設(shè)置
? ? ? ? ? ? ? ? ?$sheet->protect('password');
// Advanced protect ? ? ? 更高級(jí)的設(shè)置
$sheet->protect('password', function(\PHPExcel_Worksheet_Protection $protection) {
? ? ? ? ? ? ? $protection->setSort(true);
});
Creating a sheet from an array ? ? ?用一個(gè)數(shù)組創(chuàng)建一個(gè)工作表
Array
To create a new file from an array use->fromArray($source, $nullValue, $startCell, $strictNullComparison, $headingGeneration)inside the sheet closure.
Excel::create('Filename', function($excel) { ?
? ? ? ? ? $excel->sheet('Sheetname', function($sheet) {
? ? ? ? ? ? ? ? ? ? ? ? $sheet->fromArray(array( ? 使用fromArray()
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? array('data1', 'data2'),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? array('data3', 'data4')
? ? ? ? ? ? ? ? ? ? ? ? ));
? ? ? ? ? ?});
})->export('xls');
Alternatively you can use->with(). ?或者使用 with()
$sheet->with(array(
? ? ? ? ? ? ? ? array('data1', 'data2'),
? ? ? ? ? ? ? ? array('data3', 'data4')
));
If you want to pass variables inside the closure, useuse($data)
$data = array(
? ? ? ? ? ? ?array('data1', 'data2'),
? ? ? ? ? ? ?array('data3', 'data4')
);
Excel::create('Filename', function($excel) use($data) {
? ? ? ? ? ? ? ? ? ? $excel->sheet('Sheetname', function($sheet) use($data) {
? ? ? ? ? ? ? ? ? ? $sheet->fromArray($data);
? ? ? ? ?});
})->export('xls');
Null comparision
By default 0 is shown as an empty cell. If you want to change this behaviour, you can pass true as 4th parameter:
// Will show 0 as 0
$sheet->fromArray($data, null, 'A1', true);
To change the default behaviour, you can useexcel::export.sheets.strictNullComparisonconfig setting.
Eloquent model
It's also possible to pass an Eloquent model and export it by using->fromModel($model). The method accepts the same parameters as fromArray
Auto heading generation
By default the export will use the keys of your array (or model attribute names) as first row (header column). To change this behaviour you can edit the default config setting (excel::export.generate_heading_by_indices) or passfalseas 5th parameter:
// Won't auto generate heading columns
$sheet->fromArray($data, null, 'A1', false, false);
Row manipulation ? 一行發(fā)操作
Manipulate certain row ? 操作某一行
Change cell values
// Manipulate first row ? ? ? ? ? 設(shè)置第一行
$sheet->row(1, array(
'test1', 'test2'
));
// Manipulate 2nd row ? ? ? ? ? ?設(shè)置第二行
$sheet->row(2, array(
'test3', 'test4'
));
Manipulate row cells ? ? ?操作行單元格
// Set black background ? ? ? ? 設(shè)置黑色背景
$sheet->row(1, function($row) {
? ? ? ? ? ? ? ?// call cell manipulation methods
? ? ? ? ? ? ? ?$row->setBackground('#000000');
});
Append row ?附加行
// Append row after row 2 ? 第二行后添加 附加行
$sheet->appendRow(2, array(
? ? ? ? ? ? ? 'appended', 'appended'
));
// Append row as very last ? 最后添加附加行
$sheet->appendRow(array(
? ? ? ? ? ? ? 'appended', 'appended'
));
Prepend row ? 前置行
// Add before first row
$sheet->prependRow(1, array(
? ? ? ? ? ? ? ?'prepended', 'prepended'
));
// Add as very first
$sheet->prependRow(array(
? ? ? ? ? ? ? ? ? ?'prepended', 'prepended'
));
Append multiple rows ? ?附加多行
// Append multiple rows
$sheet->rows(array(
? ? ? ? ? ? ? array('test1', 'test2'),
? ? ? ? ? ? ? array('test3', 'test4')
));
// Append multiple rows
$sheet->rows(array(
? ? ? ? ? ? ? ? array('test5', 'test6'),
? ? ? ? ? ? ? ? array('test7', 'test8')
));
Cell manipulation ?單元格操作
$sheet->cell('A1', function($cell) {
// manipulate the cell
});
$sheet->cells('A1:A5', function($cells) {
// manipulate the range of cells
});
Set background
To change the background of a range of cells we can use->setBackground($color, $type, $colorType)
// Set black background
$cells->setBackground('#000000');
Change fonts
// Set with font color
$cells->setFontColor('#ffffff');
// Set font family
$cells->setFontFamily('Calibri');
// Set font size
$cells->setFontSize(16);
// Set font weight to bold
$cells->setFontWeight('bold');
// Set font
$cells->setFont(array(
'family'? ? => 'Calibri',
'size'? ? ? => '16',
'bold'? ? ? =>? true
));
Set borders
// Set all borders (top, right, bottom, left)
$cells->setBorder('solid', 'none', 'none', 'solid');
// Set borders with array
$cells->setBorder(array(
'borders' => array(
'top'? => array(
'style' => 'solid'
),
)
));
Set horizontal alignment ? ?設(shè)置水平對(duì)齊方式
// Set alignment to center
$cells->setAlignment('center');
Set vertical alignment ? ?設(shè)置垂直對(duì)其方式
// Set vertical alignment to middle
$cells->setValignment('middle');
Sheet styling ? 工作表樣式
General styling
If you want to change the general styling of your sheet (not cell or range specific), you can use the->setStyle()method.
// Set font with ->setStyle()`
$sheet->setStyle(array(
? ? ? ? ? ? ?'font' => array(
? ? ? ? ? ? ? ? ? ? ? ? ? ?'name'? ? ? =>? 'Calibri',
? ? ? ? ? ? ? ? ? ? ? ? ? ? 'size'? ? ? =>? 15,
? ? ? ? ? ? ? ? ? ? ? ? ? ? 'bold'? ? ? =>? true
? ? ? ? ? ? ? ?)
));
Fonts
To change the font for the current sheet use->setFont($array):
$sheet->setFont(array(
'family'? ? => 'Calibri',
'size'? ? ? => '15',
'bold'? ? ? => true
));
Separate setters
// Font family
$sheet->setFontFamily('Comic Sans MS');
// Font size
$sheet->setFontSize(15);
// Font bold
$sheet->setFontBold(true);
Borders
You can set borders for the sheet, by using:
// Sets all borders
$sheet->setAllBorders('thin');
// Set border for cells
$sheet->setBorder('A1', 'thin');
// Set border for range
$sheet->setBorder('A1:F10', 'thin');
Go to the reference guide to see a list of available border styles
Freeze rows
If you want to freeze a cell, row or column, use:
// Freeze first row
$sheet->freezeFirstRow();
// Freeze the first column
$sheet->freezeFirstColumn();
// Freeze the first row and column
$sheet->freezeFirstRowAndColumn();
// Set freeze
$sheet->setFreeze('A2');
Auto filter
To enable the auto filter use->setAutoFilter($range = false).
// Auto filter for entire sheet
$sheet->setAutoFilter();
// Set auto filter for a range
$sheet->setAutoFilter('A1:E10');
Cell size
Set column width
To set the column width use->setWidth($cell, $width).
// Set width for a single column
$sheet->setWidth('A', 5);
// Set width for multiple cells
$sheet->setWidth(array(
'A'? ? =>? 5,
'B'? ? =>? 10
));
Set row height
To set the row height use->setHeight($row, $height).
// Set height for a single row
$sheet->setHeight(1, 50);
// Set height for multiple rows
$sheet->setHeight(array(
1? ? =>? 50,
2? ? =>? 25
));
Set cell size
To set the cell size use->setSize($cell, $width, $height).
// Set size for a single cell
$sheet->setSize('A1', 500, 50);
$sheet->setSize(array(
'A1' => array(
'width'? ? => 50
'height'? ? => 500,
)
));
Auto size
By default the exported file be automatically auto sized. To change this behaviour you can either change the config or use the setters:
// Set auto size for sheet
$sheet->setAutoSize(true);
// Disable auto size for sheet
$sheet->setAutoSize(false);
// Disable auto size for columns
$sheet->setAutoSize(array(
'A', 'C'
));
The default config setting can be found in:export.php.
Column merging
Merging cells
To merge a range of cells, use->mergeCells($range).
$sheet->mergeCells('A1:E1');
Merging columns and rows
To merge columns and rows, use->setMergeColumn($array).
$sheet->setMergeColumn(array(
'columns' => array('A','B','C','D'),
'rows' => array(
array(2,3),
array(5,11),
)
));
Column formatting
To tell Excel how it should interpret certain columns, you can use->setColumnFormat($array).
// Format column as percentage
$sheet->setColumnFormat(array(
'C' => '0%'
));
// Format a range with e.g. leading zeros
$sheet->setColumnFormat(array(
'A2:K2' => '0000'
));
// Set multiple column formats
$sheet->setColumnFormat(array(
'B' => '0',
'D' => '0.00',
'F' => '@',
'F' => 'yyyy-mm-dd',
));
Go to the reference guide to see a list of available formats.
Calling PHPExcel's native methods
It's possible to call all native PHPExcel methods on the$exceland$sheetobjects.
Calling Workbook methods
Example:
// Get default style for this workbook
$excel->getDefaultStyle();
Calling worksheet methods
Example:
// Protect cells
$sheet->protectCells('A1', $password);
Head over to PHPOffice to learn more about the native methods.
