php调用python导出excel_php读取excel文件数据的导入和导出（仅学习）

一、Excel数据导入到数据库php代码：

header('Content-type: text/html; charset=utf-8'); //设置页面编码

require_once 'phpexcel.class.php'; //引入文件

require_once 'PHPExcel/IOFactory.php'; //引入文件

require_once 'PHPExcel/Reader/Excel2007.php'; //引入文件

$uploadfile = $_FILES['select_file']['tmp_name']; //获取上传文件

$auid = $_SESSION['auid'];

$date = date('Ymd');

$rand = rand(1,9999);

$_month=str_replace('-','',$date);

$file_name = str_pad($auid, 4, 0, STR_PAD_LEFT).$date.str_pad($rand, 4, 0, STR_PAD_LEFT).'.xlsx';

$path_file = '../data/upload/file/'.$file_name; //上传文件目录指定

move_uploaded_file($uploadfile, $path_file); //文件上传

$inputFileType = PHPExcel_IOFactory::identify($path_file);

$objReader = PHPExcel_IOFactory::createReader($inputFileType);

$objReader->setReadDataOnly(true);//只需要添加这个方法实现表格数据格式转换

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

$sheet_read_arr = array();

$sheet_read_arr["表1"] = array("B","C");

$sheet_read_arr["表2"] = array("B","C");

$sheet_read_arr["表3"] = array("B","C");

$list_aray=array();

foreach ($sheet_read_arr as $key => $val){

$currentSheet = $objPHPExcel->getSheetByName($key);

$row_num = $currentSheet->getHighestRow();

for ($i = 6; $i <= $row_num; $i++){

$cell_values = array();

foreach ($val as $cell_val){

$address = $cell_val . $i;// 单元格坐标

$cell_values[] = $currentSheet->getCell($address)->getFormattedValue();

}

$list_aray[]=$cell_values;

}

}

二、thinkphp5实现数据导出到Excel文件：

/*

* @description 超过5000行数据用此方法

* @param [array] $export_data [导出的]

* @param [int] $count [导出数据的总行数]

* @return [source] [文件流]

* 例：

* $export_data['filename'] = '测试';

$export_data['cellName'] = array('编号','姓名','性别');

$export_data['sql'] = "select name from user"

);

*/

public function batchExport($export_data, $count)

{

// echo 4444;die;

set_time_limit(0);

ini_set('memory_limit', '128M');

$fileName = $export_data['filename']; //文件名

$title = $export_data['cellName']; //标题名称

$sql = $export_data['sql']; //需要拼接的sql

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

header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');

// echo 667;die;;

//打开php标准输出流

//以写入追加的方式打开

$fp = fopen('php://output', 'a');

//一次取5000条数据

$step = ceil($count/5000);

foreach($title as $key => $item) {

$title[$key] = iconv('UTF-8', 'GBK', $item);

}

//将标题写到标准输出中

fputcsv($fp, $title);

$order = 'sum(ga.num) DESC';

for($s = 0; $s < $step; $s++) {

$page_count = $s;

$page_size = 5000;

if ($page_count == $step-1){

$page_size = $step*5000-$count;

}

// $data = Db::query($sql, [$page_count, $page_size]);

$data = Db::query($sql);

// var_dump($data);die;;

foreach ($data as $k => &$v) {

//避免科学计数法导出数据错误，给每个数据加上\t

array_walk($v,function (&$s, $k, $prefix = "\t") {

$s = str_pad($s, strlen($prefix) + strlen($s), $prefix, STR_PAD_LEFT);}

);

foreach ($v as $key => &$value) {

//这里必须转码，不然会乱码

$v[$key] = iconv('UTF-8', 'GBK', $value);

}

fputcsv($fp, $v);

}

//每5000条数据就刷新缓冲区

ob_flush();

flush();

}

return;

}

public function exportdatas(){

$export_data['filename'] = '测试';

$export_data['cellName'] = array('编号','姓名','性别');

$export_data['sql'] = "select * from tp_goods";

// echo 9909;die;;

$this->batchExport($export_data, 10);

}