namespace app\excel;
use PHPExcel;
use PHPExcel_IOFactory;
class ExcelMultiSheetHelper
{
/**
* 多表单数据导出
* @param $data
* $data = [
* [
* 'title' => 'sheet1',
* 'list' =>
* [
* ['name' => 'sheet1_line1_name', 'item' => 'sheet1_line1_item'],
* ['name' => 'sheet1_line2_name', 'item' => 'sheet1_line1_item']
* ],
* 'column' =>
* [
* 'A' => ['title' => 'title_name', 'field' => 'name'],
* 'B' => ['title' => 'title_item', 'field' => 'item']
* ],
* ],
* [
* 'title' => 'sheet2',
* 'list' => [
* ['name' => 'sheet2_line1_name', 'item' => 'sheet2_line1_item'],
* ['name' => 'sheet2_line2_name', 'item' => 'sheet2_line1_item']
* ],
* 'column' =>
* [
* 'A' => ['title' => 'title_name', 'field' => 'name'],
* 'B' => ['title' => 'title_item', 'field' => 'item']
* ],
* ],
* ];
* @param string $fileName
*/
public static function exportToExcel($data, $fileName = '数据导出')
{
$objPHPExcel = new PHPExcel(); //实例化Excel类
foreach ($data as $key => $value) {
$objPHPExcel->createSheet($key);
$objPHPExcel->setActiveSheetIndex($key);
$activeSheet = $objPHPExcel->getactivesheet();
$activeSheet->setTitle($value['title']);
$column = $value['column'];
$list = $value['list'];
foreach ($list as $kk => $vv) {
$line = $kk + 2;
foreach ($column as $kkk => $vvv) {
$title = $vvv['title'];
$field = $vvv['field'];
$activeSheet->getColumnDimension($kkk)->setWidth(15); //设置单元格宽
$fieldData = !empty($vv[$field]) ? $vv[$field] : '';
$activeSheet->getStyle($kkk . 1)->getFont()->setName('宋体')->setBold(true); //字体加粗; //字体
$activeSheet->setcellvalue($kkk . 1, $title);//第A列 第1行,标题
$activeSheet->setcellvalue($kkk . $line, $fieldData);//第A列 第$k行
}
}
}
if ($objPHPExcel->getSheetCount() > 1) {
$objPHPExcel->removeSheetByIndex($objPHPExcel->getSheetCount() - 1); //删除模板sheet
}
//最后通过浏览器输出
static:: responseExcelFile($fileName, $objPHPExcel, 'Excel5');
exit;
}
/**
* 最后通过浏览器输出
* @param $fileName
* @param $objPHPExcel
* @param string $excelVersion
*/
public static function responseExcelFile($fileName, $objPHPExcel, $excelVersion = 'Excel5')
{
if (static:: isIE()) {//解决文件名含中文时下载乱码问题。测试浏览器:IE8\IE11\Chrome41\Firefox38
$fileName = urlencode($fileName);
}
$fileName .= $excelVersion === 'Excel5' ? '.xls' : '.xlsx';
ob_end_clean();
ob_start();
//添加fileDownload cookie便于jquery.fileDownload.js判断下载状态
header('Set-Cookie: fileDownload=true; path=/');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $fileName . '"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $excelVersion);
$objWriter->save('php://output');
}
/**
* 判断当前请求浏览器是否IE
* @return bool
*/
private static function isIE()
{
$u_agent = $_SERVER['HTTP_USER_AGENT'];
return (preg_match('/MSIE/i', $u_agent) || preg_match('/Trident/i', $u_agent)) && !preg_match('/Opera/i', $u_agent);
}
}
参考博客