php连接excel表格数据类型 TP5 中使用PHPExcel 导出导入数据到excel表格

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

导出数据代码如下

//导出数据表 开始

public function product(){

$list=db('product')->select();

vendor("PHPExcel18.PHPExcel");

$objPHPExcel = new \PHPExcel();

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

->setLastModifiedBy("ctos")

->setTitle("Office XLSX Test Document")

->setSubject("Office XLSX Test Document")

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

->setKeywords("office 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格式，excel为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;

}