php excel引入tp,TP5引用PHPExcel实现导入导出功能

从php中文网下载PHPExcel

将下载好的PHPExcel文件夹跟PHPExcel放置在框架中的Vendor目录下，注意目录的结构，按照我的代码来基本上直接引用就能使用

前端代码

Advanced Tables

导出

导出功能控制器的代码$list = Db::table('product')->select();

vendor("PHPExcel176.PHPExcel");

$objPHPExcel = new \PHPExcel();

$objPHPExcel->getProperties()->setCreator("ctos")

->setLastModifiedBy("ctos")

->setTitle("Office 2007 XLSX Test Document")

->setSubject("Office 2007 XLSX Test Document")

->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")

->setKeywords("office 2007 openxml php")

->setCategory("Test result file");

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);

$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(50);

//设置行高度

$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);

$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);

//set font size bold

$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);

$objPHPExcel->getActiveSheet()->getStyle('A2:E2')->getFont()->setBold(true);

$objPHPExcel->getActiveSheet()->getStyle('A2:E2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('A2:E2')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);

//设置水平居中

$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//合并cell

$objPHPExcel->getActiveSheet()->mergeCells('A1:J1');

// set table header content

$objPHPExcel->setActiveSheetIndex(0)

->setCellValue('A1', '订单数据汇总 时间:'.date('Y-m-d H:i:s'))

->setCellValue('A2', '订单ID')

->setCellValue('B2', '商品名称')

->setCellValue('C2', '价格')

->setCellValue('D2', '库存')

->setCellValue('E2', '图片');

// Miscellaneous glyphs, UTF-8

for($i=0;$i

$objPHPExcel->getActiveSheet(0)->setCellValue('A'.($i+3), $list[$i]['id']);

$objPHPExcel->getActiveSheet(0)->setCellValue('B'.($i+3), $list[$i]['name']);

$objPHPExcel->getActiveSheet(0)->setCellValue('C'.($i+3), $list[$i]['price']);

$objPHPExcel->getActiveSheet(0)->setCellValue('D'.($i+3), $list[$i]['stock']);

$objPHPExcel->getActiveSheet(0)->setCellValue('E'.($i+3), $list[$i]['main_img_url']);

//$objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':J'.($i+3))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

//$objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':J'.($i+3))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);

$objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16);

}

// sheet命名

$objPHPExcel->getActiveSheet()->setTitle('订单汇总表');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet

$objPHPExcel->setActiveSheetIndex(0);

// excel头参数

header('Content-Type: application/vnd.ms-excel');

header('Content-Disposition: attachment;filename="商品表('.date('Ymd-His').').xls"'); //日期为文件名后缀

header('Cache-Control: max-age=0');

$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //excel5为xls格式，excel2007为xlsx格式

$objWriter->save('php://output');

导入功能控制器的代码public function daoru(){

$file = $_FILES['upload']['tmp_name'][0];

$data = $this->import_excel($file);

var_dump($data);

}

private function import_excel($file){

// 判断文件是什么格式

$type = pathinfo($file);

$type = strtolower($type["extension"]);

$type=$type==='csv' ? $type : 'Excel5';

ini_set('max_execution_time', '0');

Vendor('PHPExcel176.PHPExcel');

// 判断使用哪种格式

$objReader = \PHPExcel_IOFactory::createReader($type);

$objPHPExcel = $objReader->load($file);

$sheet = $objPHPExcel->getSheet(0);

// 取得总行数

$highestRow = $sheet->getHighestRow();

// 取得总列数

$highestColumn = $sheet->getHighestColumn();

//循环读取excel文件,读取一条,插入一条

$data=array();

//从第一行开始读取数据

for($j=3;$j<=$highestRow;$j++){

//从A列读取数据

for($k='A';$k<=$highestColumn;$k++){

// 读取单元格

$data[$j][]=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue();

}

}

return $data;

}

这里获得excel文件的数据内容，可以循环插入数据库中