下载扩展PHPExcel
上传excel大概内容例子
~~~
public static function UploadExcel(){
require ('../extend/PHPExcel/Classes/PHPExcel.php');
require ('../extend/PHPExcel/Classes/PHPExcel/IOFactory.php');
$filename = $_FILES['file']['tmp_name'];
$ext = strtolower(pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION));
if ($ext == 'xlsx') {
$objReader = \PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load($filename, 'utf-8');
} elseif ($ext == 'xls') {
$objReader = \PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load($filename, 'utf-8');
}
$sheet = $objPHPExcel->getSheet(0);//选择读取页码
$highestRow = $sheet->getHighestRow(); // 取得总行数
for ($row = 2; $row <= $highestRow+1; $row++) {
$create_time = (string)$objPHPExcel->getActiveSheet()->getCell('A'.$row)->getValue();
$mpsid = (string)$objPHPExcel->getActiveSheet()->getCell('B'. $row)->getValue();
}
//插入数据库
}
~~~
下载excel大概例子
如果是前后端分离,前端用blob数据类型获取并解析
~~~
public static function createExcel(){
require ('../extend/PHPExcel/Classes/PHPExcel.php');
$cellName = array('A','B','C','D','E','F','G','H','I','J','K','L');
$objPHPExcel = new \PHPExcel();
$ids = request()->param('ids');
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', '时间')
->setCellValue('B1', '单号')
->setCellValue('C1', '唛头')
->setCellValue('D1', '购买货名')
->setCellValue('E1', '购买数量')
->setCellValue('F1', '购买重量')
->setCellValue('G1', '购买单价')
->setCellValue('H1', '购买总金额')
->setCellValue('I1', '供应商名称')
->setCellValue('J1', '备注')
->setCellValue('K1', '款项')
->setCellValue('L1', '付款时间');
for ($i = 0; $i < count($cellName); $i++) {
$objPHPExcel->getActiveSheet()->getColumnDimension($cellName[$i])->setWidth(20);
}
for($i=0;$i
$k = $i+2;
$result = Db::table('sz_expenditure_agent SZ')
->field('SZ.sz_expenditure_create_time, SZ.sz_expenditure_mpsid,
SZ.sz_expenditure_buy_name, SZ.sz_expenditure_shipping_mark, SZ.sz_expenditure_buy_number,
SZ.sz_expenditure_weight, SZ.sz_expenditure_buy_price, SZ.sz_expenditure_buy_amount,
C.name, SZ.sz_expenditure_remarks, SZ.sz_expenditure_payment, SZ.sz_expenditure_time')
->leftJoin('customer C', 'SZ.customer_id = C.customer_id')
->where("SZ.sz_expenditure_agent_id=$ids[$i]")
->find();
$objPHPExcel->getActiveSheet()
->setCellValue('A' . $k, date('Y-m-d',$result['sz_expenditure_create_time']))
->setCellValue('B' . $k, $result['sz_expenditure_mpsid'])
->setCellValue('C' . $k, $result['sz_expenditure_shipping_mark'])
->setCellValue('D' . $k, $result['sz_expenditure_buy_name'])
->setCellValue('E' . $k, $result['sz_expenditure_buy_number'])
->setCellValue('F' . $k, $result['sz_expenditure_weight'])
->setCellValue('G' . $k, $result['sz_expenditure_buy_price'])
->setCellValue('H' . $k, $result['sz_expenditure_buy_amount'])
->setCellValue('I' . $k, $result['name'])
->setCellValue('J' . $k, $result['sz_expenditure_remarks'])
->setCellValue('K' . $k, $result['sz_expenditure_payment']? '已付':'未付')
->setCellValue('L' . $k, date('Y-m-d',$result['sz_expenditure_time']));
}
// 设置垂直居中
$objPHPExcel->getActiveSheet()->getStyle("A1:L$k")->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
// 设置水平居中
$objPHPExcel->getActiveSheet()->getStyle("A1:L$k")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
ob_end_clean();// 清理(擦除)输出缓冲区并关闭输出缓冲区,解决导出excel无法打开及数据乱码问题。
header('Content-Type: applicsation/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//下面两句属于后期加入,未测试
header('Content-Disposition: attachment;filename="01simple.xls"');
header('Cache-Control: max-age=0');
$PHPWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
$PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
}
~~~