PHPExcel
程序员文章站
2022-06-14 08:12:42
...
导入数据
$insertId = 0; //获取插入数据库的ID
function explodeXLSX($file_path) //$file_path 为 XLSX 的路径
{
require_once _LIBS . 'PHPExcel/Classes/PHPExcel.php'; //PHPExcel的路径
require_once _LIBS . 'PHPExcel/Classes/PHPExcel/IOFactory.php';
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load($file_path,'utf-8');
$sheet = $objPHPExcel->getSheet(0); // 取得第一个Sheet
$highestRow = $sheet->getHighestRow(); // 取得总行数
global $insertId;
//DB::Debug();
DB::tranBegin(); //事务开始
for($j=2; $j <= $highestRow; $j++) { //从第二行开始读取数据,第一行为标题
$row = array();
$level_1_id = null;
for($col = 'A'; $col != 'AA'; $col++) { //AA为数据列的后一个列号
$value = $objPHPExcel->getActiveSheet()->getCell("$col$j")->getValue();
$value = trim($value);
if('A' === $col) $row['name'] = $value; //name为数据库对应的字段
if('B' === $col) $row['alias'] = $value;
if('C' === $col) $row['degree'] = $value;
if('D' === $col) $row['type'] = $value;
if('E' === $col) $row['insurance'] = $value;
if('F' === $col){
if($value) $row['pic_url'] = $value;
}
......
}
if(!empty($row))
{
$id = Student::add($row); //保存该行数据到数据库
if($id < 1){
exit('<meta charset="utf-8"> 保存数据出错,请检查是否有空行。<br>所有数据被滚回。');
}
if($insertId != $id){
$insertId = $id;
}else{ //说明该行数据保存不成功
exit('<meta charset="utf-8"> 保存数据出错<br>所有数据被滚回。');
}
}
}
DB::tranEnd(); //事务结束
header("Location: /?a=import&msg=导入成功");exit;
}
导出数据
function excel()
{
$title = isset($_REQUEST['title']) ? $_REQUEST['title'] : '';
if(!$title) return false;
$rows = Student::getList("title='$title'");
$k = 1;
$data = array();
foreach ($rows as $row){
$data[$k]['考试名称'] = $title;
$data[$k]['姓名'] = $row['name'];
$data[$k]['时间'] = date('Y-m-d H:i:s', $row['create_at']);
$data[$k]['IP'] = $row['ip'];
$json = $row['data'];
$array = json_decode($json, true);
foreach ($array as $kk => $value){
if('others' === $kk) continue;
$data[$k][$kk] = $value;
}
$k++;
}
//获得标题,并赋值给 $data[0]
if(!empty($data)){
$dt = $data[1];
foreach ($dt as $t => $d){
$data[0][] = $t;
}
}
ksort($data);
//var_dump($data);
createXLSX($title, $data);
}
/**
$data = array(
array('111' => "QQQ", '222' => "EEE", '33aa' => "CCC", 200),
array("2010", "Q2", "www", 100),
......
);
*/
function createXLSX($title, &$data)
{
require_once _LIBS . 'PHPExcel/Classes/PHPExcel.php';
require_once _LIBS . 'PHPExcel/Classes/PHPExcel/IOFactory.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->fromArray($data, null, 'A1');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$title.'.xlsx"');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
$objPHPExcel->disconnectWorksheets();
}