ThinkPHP5 PHPExcel 导入和导出
1.必须要先下载一个插件,下载地址：https://github.com/PHPOffice/PHPExcel
也可以直接使用composer安装安装
composer require phpoffice/phpexcel
页面引入
use PHPExcel_IOFactory; use PHPExcel;
2.解压后,里面有个classes文件夹,我们需要把classes文件夹名修改为为PHPExcel
3.然后把他复制到根目录中的extend目录下.
4.然后,在控制器中这样引入
然后,在控制器中这样引入
引入方法1
import('phpexcel.PHPExcel', EXTEND_PATH);引入方法2
require(Env::get('root_path') . 'extend/'."PHPExcel/PHPExcel.php");也可以放在根目录中的/vendor/目录中
引入方法为
vendor("PHPExcel.PHPExcel");导入
//示例
//TODO 先上传 保存文件
/*设置上传路径*/
$savePath = $this->up_path;
$tmp_file = $_FILES ['file'] ['tmp_name'];
$file_types = explode ( ".", $_FILES ['file'] ['name'] );
$file_type = $file_types [count ( $file_types ) - 1];
/*以时间来命名上传的文件*/
$str = '商保商品黑名单'.$pack_id.date ( 'Ymdhis' );
$file_name = $str . "." . $file_type;
$file = $savePath . $file_name;
/*是否上传成功*/
if (!copy ($tmp_file, $file))
{
return ['code' => $params['error'], 'message' => '上传失败', 'data' => ''];
}
$extension = $file_type;
if ($extension =='xlsx') {
$objReader = new \PHPExcel_Reader_Excel2007();
$objExcel = $objReader ->load($file);
} else if ($extension =='xls') {
$objReader = new \PHPExcel_Reader_Excel5();
$objExcel = $objReader ->load($file);
} else if ($extension=='csv') {
$objReader = new \PHPExcel_Reader_CSV();
//默认输入字符集
$objReader->setInputEncoding('GBK');
//默认的分隔符
$objReader->setDelimiter(',');
//载入文件
$objExcel = $objReader->load($file);
}
$sheet = $objExcel->getSheet(); // 获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
$allColumn =$sheet->getHighestColumn(); // 获取总列数
$allRow = $sheet->getHighestRow();// 获取总行数
$ColumnNum = \PHPExcel_Cell::columnIndexFromString($allColumn); // 列号 转 列数
for ($rowIndex = 2; $rowIndex <= $allRow; $rowIndex++) { //循环读取每个单元格的内容。注意行从1开始，列从A开始
for ($colIndex = 0; $colIndex < $ColumnNum; $colIndex++) {
$goods_info[$rowIndex - 2][] = (string)$sheet->getCellByColumnAndRow($colIndex, $rowIndex)->getValue();
}
}
//处理完数据删除文件
unlink($file);导出
//示例
//TODO 超时设置
ignore_user_abort(true);
set_time_limit(0);
ini_set("memory_limit", "-1");
$obpe = new \PHPExcel();
//TODO 设置外边框
$styleThinBlackBorderOutline = array(
'borders' => array(
'outline' => array(
'style' => 'thin', //设置border样式
'color' => array('rgb' => 'D9E0EC'), //设置border颜色
),
),
'alignment' => array(
'horizontal' => 'left',
),
);
//TODO 设置表格宽度
$obpe->getactivesheet()->getColumnDimension('A')->setWidth(20);
$obpe->getactivesheet()->getColumnDimension('B')->setWidth(25);
$obpe->getactivesheet()->getColumnDimension('C')->setWidth(30);
$obpe->getactivesheet()->getColumnDimension('D')->setWidth(10);
$obpe->getactivesheet()->getColumnDimension('E')->setWidth(10);
$obpe->getactivesheet()->getColumnDimension('F')->setWidth(15);
$obpe->getactivesheet()->getColumnDimension('G')->setWidth(10);
$obpe->getactivesheet()->getColumnDimension('H')->setWidth(20);
$obpe->getactivesheet()->getColumnDimension('I')->setWidth(40);
//TODO 设置表头
$obpe->getactivesheet()->setcellvalue('A1', '支付时间')->getStyle('A1')->applyFromArray($styleThinBlackBorderOutline);;
$obpe->getactivesheet()->setcellvalue('B1', '订单号')->getStyle('B1')->applyFromArray($styleThinBlackBorderOutline);;
$obpe->getactivesheet()->setcellvalue('C1', '第三方单号')->getStyle('C1')->applyFromArray($styleThinBlackBorderOutline);;
$obpe->getactivesheet()->setcellvalue('D1', '支付类型')->getStyle('D1')->applyFromArray($styleThinBlackBorderOutline);;
$obpe->getactivesheet()->setcellvalue('E1', '支付金额')->getStyle('E1')->applyFromArray($styleThinBlackBorderOutline);;
$obpe->getactivesheet()->setcellvalue('F1', '支付状态')->getStyle('F1')->applyFromArray($styleThinBlackBorderOutline);;
$obpe->getactivesheet()->setcellvalue('G1', '退款金额')->getStyle('G1')->applyFromArray($styleThinBlackBorderOutline);;
$obpe->getactivesheet()->setcellvalue('H1', '退款时间')->getStyle('H1')->applyFromArray($styleThinBlackBorderOutline);;
$obpe->getactivesheet()->setcellvalue('I1', '退款原因')->getStyle('I1')->applyFromArray($styleThinBlackBorderOutline);;
//TODO 设置行高
$obpe->getActiveSheet()->getRowDimension('1')->setRowHeight(17);
$num = 0;
$i = 1;
$all_totalfee = 0;
$all_refund = 0;
$all_count = count($list);
$all_refund_count = 0;
//$list 需要导出的数据
foreach ($list as $key => $val) {
$num++;
if ($num == 1000) {//清空内存防止溢出
ob_flush();
flush();
$num = 0;
}
$i = $i + 1;
$status = '';
if ($val['status'] == 1) {
$status = '支付成功';
} else if ($val['status'] == 2) {
$status = '退款中';
} else if ($val['status'] == 3) {
$status = '退款成功';
}
$obpe->getActiveSheet()->getRowDimension($i)->setRowHeight(17);
$obpe->getactivesheet()->setcellvalue('A' . $i, '`' . $val['paytime'])->getStyle('A' . $i)->applyFromArray($styleThinBlackBorderOutline);
$obpe->getactivesheet()->setcellvalue('B' . $i, '`' . $val['ordernum'])->getStyle('B' . $i)->applyFromArray($styleThinBlackBorderOutline);
$obpe->getactivesheet()->setcellvalue('C' . $i, '`' . $val['transaction_id'])->getStyle('C' . $i)->applyFromArray($styleThinBlackBorderOutline);
$obpe->getactivesheet()->setcellvalue('D' . $i, $val['channel'] == 1 ? "支付宝" : "微信")->getStyle('D' . $i)->applyFromArray($styleThinBlackBorderOutline);
$obpe->getactivesheet()->setcellvalue('E' . $i, '`' . $val['totalfee'])->getStyle('E' . $i)->applyFromArray($styleThinBlackBorderOutline);
$obpe->getactivesheet()->setcellvalue('F' . $i, $status)->getStyle('F' . $i)->applyFromArray($styleThinBlackBorderOutline);
$obpe->getactivesheet()->setcellvalue('G' . $i, $val['status'] == 3 ? '`' . $val['totalfee'] : '')->getStyle('G' . $i)->applyFromArray($styleThinBlackBorderOutline);
$obpe->getactivesheet()->setcellvalue('H' . $i, $val['refund_time'] ? '`' . date('Y-m-d H:i:s', $val['refund_time']) : '')->getStyle('H' . $i)->applyFromArray($styleThinBlackBorderOutline);
$obpe->getactivesheet()->setcellvalue('I' . $i, $val['refund_reason'])->getStyle('I' . $i)->applyFromArray($styleThinBlackBorderOutline);
$all_totalfee = $val['totalfee']+$all_totalfee;
if($val['status'] == 3){
$all_refund = $val['totalfee']+$all_refund;
$all_refund_count = $all_refund_count + 1;
}
}
$i = $i+1;
$obpe->getactivesheet()->setcellvalue('A' . $i, '')->getStyle('A' . $i)->applyFromArray($styleThinBlackBorderOutline);
$i = $i+1;
$obpe->getactivesheet()->setcellvalue('D' . $i, '总计')->getStyle('A' . $i)->applyFromArray($styleThinBlackBorderOutline);
$obpe->getactivesheet()->setcellvalue('E' . $i, (string)$all_totalfee)->getStyle('C' . $i)->applyFromArray($styleThinBlackBorderOutline);
$obpe->getactivesheet()->setcellvalue('G' . $i, (string)$all_refund)->getStyle('E' . $i)->applyFromArray($styleThinBlackBorderOutline);
$obpe->setactivesheetindex(0); //工作副本1
$obpe->getActiveSheet(0)->setTitle('商保普惠明细');
$fileName = $other['drugstoresInfo']['name'] . '商保普惠明细';
if ($other['postdata']['start_time'] == $other['postdata']['end_time']) {
$fileName .= "_{$other['postdata']['start_time']}.xls";
} else {
$fileName .= "_{$other['postdata']['start_time']}到{$other['postdata']['start_time']}.xls";
}
$obpe->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=\"$fileName\"");
header('Cache-Control: max-age=0');
//写入类容
$obwrite = \PHPExcel_IOFactory::createWriter($obpe, 'Excel5');
//保存文件
ob_start();
$obwrite->save('php://output'); //文件通过浏览器下载
$xlsData = ob_get_contents();
ob_end_clean();
$data = [
'file' => "data:application/vnd.ms-excel;base64," . base64_encode($xlsData),
'file_name' => $fileName
];
return ['code' => $this->successCode, 'message' => '操作成功', 'data' => $data];require(Env::get('root_path') . 'extend/'."PHPExcel/PHPExcel.php");
Thinkphp5使用PHPExcel包操作excel示例
