phpexcel导出、导入excel文件

来源：互联网发布：魔法王座翅膀进阶数据编辑：程序博客网时间：2024/04/25 07:11
1、下载PHPExcel
2、引入文件 PHPExcel/IOFactory.php
3、创建一个excel
$resultPHPExcel = new PHPExcel();
4、导出：
//设置当前sheet以及导出数据的标题行$resultPHPExcel->setActiveSheetIndex(0)    ->setCellValue('A1', '序号')    ->setCellValue('B1', '渠道')    ->setCellValue('C1', '展现量')    ->setCellValue('D1', '点击量')    ->setCellValue('E1', '咨询量')    ->setCellValue('F1', '消费金额')    ->setCellValue('G1', '平均点击价格')    ->setCellValue('h1', '推广总额')    ->setCellValue('I1', '咨询总量')    ->setCellValue('J1', '点击成本');//从第二行开始，设置单元格的数据值$i = 2;foreach ($result as $key => $val) {    $resultPHPExcel->getActiveSheet()->setCellValue('A' . $i, $result[$key]['id']);    $resultPHPExcel->getActiveSheet()->setCellValue('B' . $i, $result[$key]['channel']);    $resultPHPExcel->getActiveSheet()->setCellValue('C' . $i, $result[$key]['show_amout']);    $resultPHPExcel->getActiveSheet()->setCellValue('D' . $i, $result[$key]['click_amout']);    $resultPHPExcel->getActiveSheet()->setCellValue('E' . $i, $result[$key]['advisory_amout']);    $resultPHPExcel->getActiveSheet()->setCellValue('F' . $i, $result[$key]['money']);    $resultPHPExcel->getActiveSheet()->setCellValue('G' . $i, $result[$key]['aver_click_quota']);    $resultPHPExcel->getActiveSheet()->setCellValue('H' . $i, $result[$key]['promotion_total']);    $resultPHPExcel->getActiveSheet()->setCellValue('I' . $i, $result[$key]['advisory_total']);    $resultPHPExcel->getActiveSheet()->setCellValue('J' . $i, $result[$key]['click_cost']);    //设置垂直居中    $resultPHPExcel->getActiveSheet()        ->getStyle('A' . $i . ':J' . $i)        ->getAlignment()        ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);    //设置align    $resultPHPExcel->getActiveSheet()        ->getStyle('A' . $i . ':J' . $i)        ->getAlignment()        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);    //设置column的border    $resultPHPExcel->getActiveSheet()        ->getStyle('A' . $i . ':J' . $i)        ->getBorders()        ->getAllBorders()        ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);    //格式化数字、防止数字过大以科学计数方式显示    $resultPHPExcel->getActiveSheet()        ->getStyle('A' . $i . ':J' . $i)        ->getNumberFormat()        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);    $i++;}//设置sheet活动表标题 $resultPHPExcel->getActiveSheet()->setTitle($filename);$resultPHPExcel->setActiveSheetIndex(0);ob_end_clean();//清除缓冲区,避免乱码header("Content-type:application/vnd.ms-excel;charset=UTF-8");header('Content-Disposition:attachment;filename="' . $filename . '.xls"');header('Cache-Control:max-age=0');$xlsWriter = PHPExcel_IOFactory::createWriter($resultPHPExcel, 'Excel5');$xlsWriter->save("php://output");

5、导入：
   a)、获取上传文件的扩展名       
       $arr = $_FILES['upload_file'];
       $extension = strtolower(pathinfo($_FILES ['upload_file'] ['name'], PATHINFO_EXTENSION));//获取文件扩展名
   b)、根据需求判断你要上传的文件类型是否符合需求
   c)、使用 PHPExcel_IOFactory 来鉴别文件应该使用哪一个读取类       $inputFileType = PHPExcel_IOFactory::identify($_FILES['upload_file']['tmp_name']);       $xlsReader = PHPExcel_IOFactory::createReader($inputFileType);
   d)、载入excel文件     $Sheets = $xlsReader->load($_FILES['file_stu']['tmp_name']);     //开始读取上传到服务器中的Excel文件，返回一个二维数组     $dataArray = $Sheets->getSheet(0)->toArray();
   e)、组装获取到的数据进行后续处理即可
参考链接：http://www.cnblogs.com/freespider/p/3284828.html
http://blog.csdn.net/beyond__devil/article/details/53457849
0 0