excel 导出导入

最新推荐文章于 2025-04-20 09:25:42 发布

阳光终会撒在每个角落

最新推荐文章于 2025-04-20 09:25:42 发布

阅读量148

点赞数

文章标签： excel

本文链接：https://blog.youkuaiyun.com/q1609301436/article/details/117329449

版权

本文介绍了如何使用PHPExcel库在PHP中实现Excel数据的导出和导入功能。导出部分展示了如何设置表头、样式并写入数据库数据到Excel文件。导入部分则讲解了读取Excel文件内容，检查数据格式并将其写入或更新到数据库的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/**

导出
@param
@param
@return
*/

public function exportexcel()
{

set_time_limit(0);
ini_set(‘memory_limit’, ‘1024M’);
vendor(“PHPExcel.PHPExcel.PHPExcel”);
vendor(“PHPExcel.PHPExcel.Writer.IWriter”);
vendor(“PHPExcel.PHPExcel.Writer.Abstract”);
vendor(“PHPExcel.PHPExcel.Writer.Excel5”);
vendor(“PHPExcel.PHPExcel.Writer.Excel2007”);
vendor(“PHPExcel.PHPExcel.IOFactory”);
//$data = Db::table(‘enroll_activity_hotel’)->where(‘is_del’, ‘0’)->field(‘name,address,phone,departure_time,two_room,three_room’)->select();
$name = ‘酒店列表’;
$excel = new \PHPExcel(); //引用phpexcel
iconv(‘UTF-8’, ‘gb2312’, $name); //针对中文名转码
$header = [‘酒店名称’, ‘酒店地址’, ‘酒店电话’, ‘发车时间’, ‘两人间数’, ‘三人间数’]; //表头,名称可自定义
$excel->setActiveSheetIndex(0);
$e x c e l - > g e t A c t i v e S h e e t () - > s e t T i t l e ($ name); //设置表名
$excel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(18);
$excel->getActiveSheet()->getColumnDimension(‘A’)->setWidth(30);
$excel->getActiveSheet()->getColumnDimension(‘B’)->setWidth(30);
$excel->getActiveSheet()->getColumnDimension(‘C’)->setWidth(30);
$excel->getActiveSheet()->getColumnDimension(‘D’)->setWidth(30);
$excel->getActiveSheet()->getColumnDimension(‘E’)->setWidth(30);
$excel->getActiveSheet()->getColumnDimension(‘F’)->setWidth(30);
$l e t t e r = a r r a y (^{'} A^{'},^{'} B^{'},^{'} C^{'},^{'} D^{'},^{'} E^{'},^{'} F^{'},^{'} G^{'},^{'} H^{'},^{'} I^{'},^{'} J^{'},^{'} K^{'},^{'} L^{'},^{'} M^{'},^{'} N^{'},^{'} O^{'},^{'} P^{'},^{'} Q^{'},^{'} R^{'},^{'} S^{'},^{'} T^{'},^{'} U^{'},^{'} V^{'},^{'} W^{'},^{'} X^{'},^{'} Y^{'},^{'} Z^{'},^{'} A A^{'},^{'} A B^{'},^{'} A C^{'},^{'} A D^{'},^{'} A E^{'},^{'} A F^{'},^{'} A G^{'},^{'} A H^{'},^{'} A I^{'},^{'} A J^{'},^{'} A K^{'},^{'} A L^{'},^{'} A M^{'},^{'} A N^{'},^{'} A O^{'},^{'} A P^{'},^{'} A Q^{'},^{'} A R^{'},^{'} A S^{'},^{'} A T^{'},^{'} A U^{'},^{'} A V^{'},^{'} A W^{'},^{'} A X^{'},^{'} A Y^{'},^{'} A Z^{'}); / / 生成表头 f o r ($ i = 0; $i < c o u n t ($ header); $i++) {
//设置表头值
$e x c e l - > g e t A c t i v e S h e e t () - > s e t C e l l V a l u e ("$ letter[$i]1", $h e a d e r [$ i]);
//设置表头字体样式
$e x c e l - > g e t A c t i v e S h e e t () - > g e t S t y l e ("$ letter[$i]1")->getFont()->setName(‘宋体’);
//设置表头字体大小
$e x c e l - > g e t A c t i v e S h e e t () - > g e t S t y l e ("$ letter[$i]1")->getFont()->setSize(14);
//设置表头字体是否加粗
$e x c e l - > g e t A c t i v e S h e e t () - > g e t S t y l e ("$ letter[$i]1")->getFont()->setBold(true);
//设置表头文字水平居中
$e x c e l - > g e t A c t i v e S h e e t () - > g e t S t y l e ("$ letter[$i]1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置文字上下居中
$e x c e l - > g e t A c t i v e S h e e t () - > g e t S t y l e ($ letter[$i])->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//设置单元格背景色
$e x c e l - > g e t A c t i v e S h e e t () - > g e t S t y l e ("$ letter[$i]1")->getFill()->getStartColor()->setARGB(‘FFFFFFFF’);
$e x c e l - > g e t A c t i v e S h e e t () - > g e t S t y l e ("$ letter[$i]1")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
$e x c e l - > g e t A c t i v e S h e e t () - > g e t S t y l e ("$ letter[$i]1")->getFill()->getStartColor()->setARGB(‘FF6DBA43’);
//设置字体颜色
$e x c e l - > g e t A c t i v e S h e e t () - > g e t S t y l e ("$ letter[$i]1")->getFont()->getColor()->setARGB(‘FFFFFFFF’);
}
//写入数据
$count = Db::table(‘enroll_activity_hotel’)->where(‘is_del’, ‘0’)->count(‘id’);
$n u m s = c e i l ($ count / 2000);
for ($i = 0; $i < $n u m s; + +$ i) {
$data = Db::table('enroll_activity_hotel')->where('is_del', '0')->field('name,address,phone,departure_time,two_room,three_room')->limit(($ i * 2000) . ‘,’ . (2000))->select();
if (!empty($data)) {
$param = $i * 2000; / / 写入数据 f o r e a c h ($ data as $k => $v) {
$n = $param + $k + 2;
//列字体居中
$excel->getActiveSheet()->getStyle(‘A’ . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->getStyle(‘B’ . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->getStyle(‘C’ . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->getStyle(‘D’ . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->getStyle(‘E’ . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->getStyle(‘F’ . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->getStyle(‘D’)->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);

//从第二行开始写入数据（第一行为表头）
$excel->getActiveSheet()->setCellValue(‘A’ . $n, $v[‘name’]);
$excel->getActiveSheet()->setCellValue(‘B’ . $n, $v[‘address’]);
$excel->getActiveSheet()->setCellValue(‘C’ . $n, $v[‘phone’]);
$excel->getActiveSheet()->setCellValue(‘D’ . $n, $v[“departure_time”]);
$excel->getActiveSheet()->setCellValue(‘E’ . $n, $v[‘two_room’]);
$excel->getActiveSheet()->setCellValue(‘F’ . $n, $KaTeX parse error: Expected 'EOF', got '}' at position 19: …three_room']); }̲ } else { break…$ data);

//清理缓冲区，避免中文乱码
ob_end_clean();//清除缓冲区,避免乱码
header(‘Content-Type: application/vnd.ms-excel’);
header(‘Content-Disposition: attachment;filename="’ . $name . ‘.xlsx"’);
header(‘Cache-Control: max-age=0’);
//导出数据
$KaTeX parse error: Undefined control sequence: \PHPExcel at position 13: res_excel = \̲P̲H̲P̲E̲x̲c̲e̲l̲_IOFactory::cre…$ excel, ‘Excel2007’);
$res_excel->save(‘php://output’);
}
/**

daoru
@param
@param
@return
*/
public function implodexcel()
{

set_time_limit(0);
if (!empty( $KaTeX parse error: Expected '}', got 'EOF' at end of input: \dots传文件 if (!empty($ _FILES[‘import’][‘error’])) {
return array(‘status’ => 0, ‘msg’ => ‘文件上传失败’);
}

$tmp_file = $_FILES [‘import’] [‘tmp_name’];
$file_types = explode(".", $_FILES [‘import’] [‘name’]);
$file_type = $file_types [count($ file_types) - 1];
/判别是不是.xls文件，判别是不是excel文件/
if (strtolower( $KaTeX parse error: Expected 'EOF', got '&' at position 22: \dotsype) != "xlsx" &̲& strtolower($ file_type) != “xls”) {
$this->error(‘不是Excel文件，重新上传’);
}
/设置上传路径/
/百度有些文章写的上传路径经过编译之后斜杠不对。不对的时候用大写的DS代替，然后用连接符链接就可以拼凑路径了。/
$savePath = ROOT_PATH . ‘public’ . DS . ‘upload’ . DS;/以时间来命名上传的文件/
$str = date(‘Ymdhis’);
$file_name = $str . “.” . $file_type; /*是否上传成功*/ if (!copy($ tmp_file, $savePath . $file_name)) {
$KaTeX parse error: Expected 'EOF', got '}' at position 22: \dotserror('上传失败'); }̲ /* *注意：这里调用执行了\dots$ res,再进行数据库写入
*/
$res = $t h i s - > r e a d ($ savePath . $file_name, “UTF-8”, $file_type);//传参,判断office2007还是office2003

$uniques = array_filter(array_column($ res, 0));
$filpuni = array_flip(array_flip($ uniques));

if (count( $u n i q u e s)! = c o u n t ($ filpuni)) {
return array(‘status’ => 0, ‘msg’ => “酒店名称有重复项，请处理后再导入”);
}

$datas = Db::table(‘enroll_activity_hotel’)->where(‘is_del’, ‘0’)->field(‘id,name,address,phone,departure_time,two_room,three_room’)->select();
$anames = array_column($ datas, ‘name’);
$couludate = array_column($ datas, null, ‘name’);//print_r( $KaTeX parse error: Expected '}', got 'EOF' at end of input: \dots的写入*/ foreach ($ res as $k => $v) {

//如果导入数据为空，跳过
if (empty(array_filter($v))) {
continue;
}

if ($k > 1) {

$mblength = mb_strlen($ v[0], ‘UTF8’);
if ($mblength > 50 || $mblength < 1) {
Db::rollback();
return array(‘status’ => 0, ‘msg’ => ‘酒店名’ . $v[0] . ‘：字符长度不符请控制在2-50字符以内’);
}
$pholength = mb_strlen($ v[2], ‘UTF8’);
if ($pholength > 20 || $pholength < 1) {
Db::rollback();
return array(‘status’ => 0, ‘msg’ => ‘酒店’ . $KaTeX parse error: Expected 'EOF', got '}' at position 20: \dots . '：电话长度不符'); }̲ if(!is_numeric\dots$ v[2])) {
Db::rollback();
return array(‘status’ => 0, ‘msg’ => ‘酒店’ . $v[0] . ‘：电话必须为数字’);
}
$adreslength = mb_strlen($ v[1], ‘UTF8’);
if ($adreslength > 100 || $adreslength < 1) {
Db::rollback();
return array(‘status’ => 0, ‘msg’ => ‘酒店’ . $v[0] . ‘：地址长度不符’);
}

$timelength = mb_strlen($ v[3], ‘UTF8’);
if ($timelength > 20 || $timelength < 1) {
Db::rollback();
return array(‘status’ => 0, ‘msg’ => ‘酒店’ . $v[0] . ‘：发车时间长度不符’);
}

$toromlength = mb_strlen($ v[4], ‘UTF8’);
$treromlength = mb_strlen($ v[5], ‘UTF8’);
if ($toromlength < 1 || $treromlength < 1) {
Db::rollback();
return array(‘status’ => 0, ‘msg’ => ‘酒店’ . $KaTeX parse error: Expected 'EOF', got '}' at position 31: \dots写,没有请填写数字为0'); }̲ if(!is_numeric\dots$ v[4])) {
Db::rollback();
return array(‘status’ => 0, ‘msg’ => ‘酒店’ . $KaTeX parse error: Expected 'EOF', got '}' at position 22: \dots '：房间数必须为数字'); }̲ if(!is_numeric\dots$ v[5])) {
Db::rollback();
return array(‘status’ => 0, ‘msg’ => ‘酒店’ . $v[0] . ‘：房间数必须为数字’);
}
$isexit = array_search($ v[0], $anames);

if (false === $isexit) {
$d a t a [$ k][‘name’] = $v[0];
$d a t a [$ k][‘address’] = $v[1];
$d a t a [$ k][‘phone’] = $v[2];
$d a t a [$ k][‘departure_time’] = $v[3];
$d a t a [$ k][‘two_room’] = $v[4];
$d a t a [$ k][‘three_room’] = $v[5];
$d a t a [$ k][‘is_open’] = 1;
$d a t a [$ k][‘add_admin_id’] = session(‘user.id’);
$d a t a [$ k][‘add_time’] = time();
$d a t a [$ k][‘update_admin_id’] = session(‘user.id’);
$d a t a [$ k][‘update_time’] = time();
} else {
//如果数据表中有本账户，判断他最新导入的表格是否修改过信息
if ( $c o u l u d a t e [$ v[0]][‘address’] != $v[1] || $c o u l u d a t e [$ v[0]][‘phone’] != $v[2] || $c o u l u d a t e [$ v[0]][‘departure_time’] != $v[3] || $c o u l u d a t e [$ v[0]][‘two_room’] != $v[4] || $c o u l u d a t e [$ v[0]][‘three_room’] != $v[5]) {
$d a t a u p [$ k][‘id’] = $c o u l u d a t e [$ v[0]][‘id’];
$d a t a u p [$ k][‘address’] = $v[1];
$d a t a u p [$ k][‘phone’] = $v[2];
$d a t a u p [$ k][‘departure_time’] = $v[3];
$d a t a u p [$ k][‘two_room’] = $v[4];
$d a t a u p [$ k][‘three_room’] = $v[5];
$d a t a u p [$ k][‘update_admin_id’] = session(‘user.id’);
$d a t a u p [$ k][‘update_time’] = time();

}
}

}
}
if (isset( $d a t a u p) ∣ ∣ i s s e t ($ data)) {
//如果有更新的数据，批量更新
if (isset($dataup)) {

foreach ($dataup as $key => $value) {
$updas = Db::table(‘enroll_activity_hotel’)->where(‘id’, $v a l u e [^{'} i d^{'}]) - > u p d a t e ($ value);
if (!$updas) {
Db::rollback();
return array(‘status’ => 0, ‘msg’ => $e->getMessage());
}
}

}
if (isset($data)) {
//插入的操作最好放在循环外面
$result = Db::table('enroll_activity_hotel')->insertAll($ data);
if (!$result) {
Db::rollback();
return array(‘status’ => 0, ‘msg’ => $e->getMessage());
}
}
Db::commit();
return array(‘status’ => 1, ‘msg’ => “导入成功”);

} else {
Db::rollback();
return array(‘status’ => 0, ‘msg’ => “重复导入”);
}

} catch (\Exception $e) {
Db::rollback();
return array(‘status’ => 0, ‘msg’ => $e->getMessage());
}
}

}

/**

导入方法附属方法
@param
@param
@return
*/
public function read($filename, $encode, $file_type)
{

vendor(“PHPExcel.PHPExcel.PHPExcel”);
vendor(“PHPExcel.PHPExcel.Writer.IWriter”);
vendor(“PHPExcel.PHPExcel.Writer.Abstract”);
vendor(“PHPExcel.PHPExcel.Writer.Excel5”);
vendor(“PHPExcel.PHPExcel.Writer.Excel2007”);
vendor(“PHPExcel.PHPExcel.IOFactory”);

if (strtolower($file_type) == ‘xls’)//判断excel表类型为2003还是2007
{
Vendor(“Excel.PHPExcel.Reader.Excel5”);
$objReader = \PHPExcel_IOFactory::createReader(‘Excel5’);

} elseif (strtolower($file_type) == ‘xlsx’) {
Vendor(“Excel.PHPExcel.Reader.Excel2007”);
$objReader = \PHPExcel_IOFactory::createReader(‘Excel2007’);
}
$objReader->setReadDataOnly(true);
$objPHPExcel = $o b j R e a d e r - > l o a d ($ filename);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$KaTeX parse error: Undefined control sequence: \PHPExcel at position 22: …tColumnIndex = \̲P̲H̲P̲E̲x̲c̲e̲l̲_Cell::columnIn…$ highestColumn);
$e x c e l D a t a = a r r a y (); f o r ($ row = 1; $row <= $highestRow; $KaTeX parse error: Expected '}', got 'EOF' at end of input: row++) { for ($ col = 0; $col < $highestColumnIndex; $col++) {
$e x c e l D a t a [$ row][] = (string) $o b j W o r k s h e e t - > g e t C e l l B y C o l u m n A n d R o w ($ col, $row)->getValue();
}
}
return $excelData;
}