php上传excel,php实现Excel上传及下载

下载扩展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文件

}

~~~