PHPExcel表格的导出,PHP_XLSXWriter导出和PHPExcel导入

冯德宇
2023-12-01

前言:网站自带的一个PHPExcel导入功能,现在需要加一个导出。
问题:导出数据太多,占脚本运行内存,容易内存不足
解决:
1/使用轻量级PHP_XLSXWriter导出
2/自己编写的导出代码
3/PHPExcel导出时,修改当前运行脚本内存ini_set(“memory_limit”, “1024M”);

一/PHPExcel导出

<?php
//***************导出筛选报表***************
if($_POST["act"]=="download")
{
	include 'PHPExcel/PHPExcel.php';
	//include 'PHPExcel/PHPExcel/Writer/Excel2007.php';//用于输出.xlsx的
	include 'PHPExcel/PHPExcel/Writer/Excel5.php'; //用于输出.xls的
	ini_set("memory_limit", "1024M");//修改当前运行脚本内存
	$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
	$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;
	$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
	$cacheSettings = array('memoryCacheSize'=>'16MB');
	PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);	
	$objPHPExcel = new PHPExcel(); //创建一个实例
	$phpColor = new PHPExcel_Style_Color(); //颜色
	//文件 => 属性 => 摘要
	$objPHPExcel->getProperties()->setCreator("SiWaTu Network");//创建人
	$objPHPExcel->getProperties()->setLastModifiedBy("SiWaTu Network");//最后修改人
	$objPHPExcel->getProperties()->setTitle("Office 2003 XLS Document");//标题
	$objPHPExcel->getProperties()->setSubject("Office 2003 XLS Document");//题目
	$objPHPExcel->getProperties()->setDescription("SiWaTu Network,Office 2003 XLS Document.");//描述
	$objPHPExcel->getProperties()->setKeywords("");//关键字
	$objPHPExcel->getProperties()->setCategory("");//种类
	//=========================================================================================================================================设置当前的工作簿
	//设置当前的工作簿(被操作的是当前工作簿)
	$objPHPExcel->setActiveSheetIndex(0);
	$objPHPExcel->getActiveSheet()->setTitle(iconv('gb2312','UTF-8','Sheet1'));  //设置工作簿的名称
	$objPHPExcel->getActiveSheet()->getTabColor()->setARGB( 'FFFFFFFF');     //设置工作簿的颜色
	//设置默认的字体和文字大小
	$objPHPExcel->getDefaultStyle()->getFont()->setName(iconv('gb2312','UTF-8','宋体'));
	$objPHPExcel->getDefaultStyle()->getFont()->setSize(12);
	//=========================================================================================================================================第一行属性
	//设置垂直居中
	$objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	//设置加粗
	$objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getFont()->setBold(true);
	$objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getFont()->setSize(16);
	//设置水平对齐方式(HORIZONTAL_RIGHT,HORIZONTAL_LEFT,HORIZONTAL_CENTER,HORIZONTAL_JUSTIFY)
	$objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	//设置单元格高度
	$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(25);
	//设置单元格的宽度
	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
	//添加文字
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue( 'A1', '标题一' );
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue( 'B1', '标题二' );
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue( 'C1', '标题三' );
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue( 'D1', '标题四' );
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue( 'E1', '标题五' );
	//文字换行
	//$objPHPExcel->getActiveSheet()->getStyle('F2')->getAlignment()->setWrapText(true);
	//=========================================================================================================================================
	$sql="select * from php_product where (1=1) and jia=1 order by px desc,id desc";
	$result = mysqli_query($conn, $sql);
	$numPl = mysqli_num_rows($result);
	$i=0;
	//循环
	do {$row = mysqli_fetch_array($result);
		$hid = $i+2;
		//高度
		$objPHPExcel->getActiveSheet()->getRowDimension($hid)->setRowHeight(20);
		$objPHPExcel->getActiveSheet()->getStyle($hid)->getFont()->setSize(11);
		//设置垂直居中
		$objPHPExcel->getActiveSheet()->getStyle('A'.$hid.':J'.$hid.'')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
		//设置水平对齐方式(HORIZONTAL_RIGHT,HORIZONTAL_LEFT,HORIZONTAL_CENTER,HORIZONTAL_JUSTIFY)
		$objPHPExcel->getActiveSheet()->getStyle('A'.$hid.'')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objPHPExcel->getActiveSheet()->getStyle('C'.$hid.':J'.$hid.'')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		//字体颜色
		$objPHPExcel->getActiveSheet()->getStyle('A'.$hid.':J'.$hid.'')->getFont()->setColor($phpColor->setRGB('666666'));
		//添加内容
		//编号
		$Bianhao = $row["bianhao"];
		//型号
		$Xinhao = $row["xinghao"];
		//会员价格
		$Hjiage = $row["Price"];
		//库存
		$Kun = $row["kucun"];
		//产品排序
		$Pai = $row["px"];
		/****-------------------------------------------*/
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue( 'A'.$hid, "\t".$Bianhao ."\t" );
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue( 'B'.$hid, "\t".$Xinhao ."\t" );
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue( 'C'.$hid, "\t".$Hjiage ."\t" );
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue( 'D'.$hid, "\t".$Kun."\t" );
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue( 'E'.$hid, "\t".$Pai ."\t" );
		/****-------------------------------------------*/
		//字体颜色
		if($JEfu=="-"){
			$objPHPExcel->getActiveSheet()->getStyle('D'.$hid.'')->getFont()->setColor($phpColor->setRGB('ff6520'));
		}else{
			$objPHPExcel->getActiveSheet()->getStyle('D'.$hid.'')->getFont()->setColor($phpColor->setRGB('55acef'));
		}
		//插入图片,有兴趣的自己看看
//		$touimg = $rzl["touimg"];
//		if($touimg){
//			if($swtWjj){$touimg = str_replace("" . $swtWjj . "/", "/", $touimg);}
//			if(file_exists("..".$touimg)){
//				$arr = explode('.',$touimg);
//				$touimg_new = $arr[0]."_100x100.".$arr[1];
//				if(!file_exists("..".$touimg_new)){
//					imgSmallFun_small("..".$touimg,"..".$touimg_new,100,100);
//				}
//				$objDrawing = new PHPExcel_Worksheet_Drawing();
//				$objDrawing->setPath("..".$touimg_new);//设置图片路径 切记:只能是本地图片
//				$objDrawing->setResizeProportional(false);//是否缩放
//				$objDrawing->setHeight(23);//照片高度
//				$objDrawing->setWidth(20); //照片宽度
//				$objDrawing->setCoordinates('B'.$hid);//要插入的单元格
//				$objDrawing->setOffsetX(2);//X轴
//				$objDrawing->setOffsetY(2);//Y轴
//				$objDrawing->setRotation(0);//旋转
//				$objDrawing->getShadow()->setVisible(true);//是否显示
//				$objDrawing->getShadow()->setDirection(50);
//				$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
//			}
//		}
		$i++;
	} while ($i < $numPl);
	//=========================================================================================================================================
	//调整到第一个工作簿
	$objPHPExcel->setActiveSheetIndex(0);	
	$savename='产品信息';
	$ua = $_SERVER["HTTP_USER_AGENT"];
	$datetime = date('Ymdhis', time());        
	if (preg_match("/MSIE/", $ua)) {
			$savename = urlencode($savename); //处理IE导出名称乱码
	} 
	// excel头参数  
	ob_end_clean(); // Added by me
	header('Content-Type: application/vnd.ms-excel;');  
	header('Content-Disposition: attachment;filename="'.$savename.$datetime.'.xls"');  //日期为文件名后缀  
	header('Cache-Control: max-age=0'); 
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  //excel5为xls格式,excel2007为xlsx格式  
	$objWriter->save('php://output');
	exit;
}
//******************************************
?>

二/PHP_XLSXWriter导出

<?php
include_once("PHP_XLSXWriter/xlsxwriter.class.php");
if($_POST["act"]=="download")
//***************导出筛选报表***************
{
	$S_big = $_POST["S_big"];
	$S_small = $_POST["S_small"];
	$S_xiao = $_POST["S_xiao"];
	$S_xxxx = $_POST["S_xxxx"];
	$jia = $_POST["jia"];
	$mkey = $_POST["mkey"];
	ini_set('display_errors', 0);
	ini_set('log_errors', 1);
	error_reporting(E_ALL & ~E_NOTICE);
	//设置 header,用于浏览器下载
	$datetime = date('Ymdhis', time());        
	$filename = "".$datetime."产品信息表.xlsx";
	header('Content-disposition: attachment; filename="' . XLSXWriter::sanitize_filename($filename) . '"');
	header('Content-Type: application/vnd.ms-excel;');  
	header('Cache-Control: max-age=0'); 
	# 表格样式
	$styles1 = array(
		'font'         => 'Arial',
		'font-size'    => 12,
		'font-style'   => 'italic',  #bold, italic, underline, strikethrough or multiple ie: 'bold,italic'
		'color'        => '#333',
		'fill'         => '#fff',  # 背景填充
		'halign'       => 'center',  # 水平位置 general, left, right, justify, center
		'border'       => 'left,right,top,bottom', # 边界 left, right, top, bottom, or multiple ie: 'top,left'
		'border-style' => 'thin',  # 边框样式 thin, medium, thick, dashDot, dashDotDot, dashed, dotted, double, hair, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot
		'border-color' => '#333',  # 边框颜色 #RRGGBB, ie: #ff99cc or #f9c
		'valign'       => 'center', # 垂直位置 bottom, center, distributed
		'height'       => 30,  # 行高
		// 'collapsed'       => true,  # 未知
		// 'hidden'       => true,  # 隐藏行
	);
	# 每列标题头
	$header = array(
	//    'created'     => 'date',//时间格式
	//    'product_id'  => 'integer',//整数格式
	//    'quantity'    => '#,##0.00', #价格 #,##0.00表示小数位两个,减少或增加改变长度
	//    'amount'      => 'price',//价格格式
	//    'description' => 'string',//文字格式
	//    'tax'         => '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00',
	//选中对应的型号,否者导出会有乱码
		'上下架'     => 'integer',
		'编号'     => 'string',
		'型号'     => 'string',
		'一级类别'     => 'integer',
		'二级类别'     => 'integer',
		'三级类别'     => 'integer',
		'四级类别'     => 'integer',
		'会员价格'     => 'integer',
		'库存'     => 'integer',
		'产品排序'     => 'integer',
	);
	# 标题头样式
	$col_options = array(
		'widths'       => [20,20,20,20,20,20,20,20,20,20],  # 宽度
		'font-size'    => 14,
		'auto_filter'  => false,  # 筛选
		'font-style'   => 'bold',  #bold, italic, underline, strikethrough or multiple ie: 'bold,italic'
		'color'        => '#ff0000',
		'fill'         => '#fff',  # 背景填充
		'halign'       => 'center',  # 水平位置 general, left, right, justify, center
		'border'       => 'left,right,top,bottom', # 边界 left, right, top, bottom, or multiple ie: 'top,left'
		'border-style' => 'thin',  # 边框样式 thin, medium, thick, dashDot, dashDotDot, dashed, dotted, double, hair, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot
		'border-color' => '#333',  # 边框颜色 #RRGGBB, ie: #ff99cc or #f9c
		'valign'       => 'center', # 垂直位置 bottom, center, distributed
		// 'collapsed'       => true,  # 未知
		// 'hidden'       => true,  # 隐藏行
	);
	# 表数据
	$sql="select * from php_product where (1=1) and jia=1 order by px desc,id desc";
	$result = mysqli_query($conn, $sql);
	$num = mysqli_num_rows($result);
	if($num){
		$rows = array();
		$i = 0;
		//循环
		do{$rs = mysqli_fetch_array($result);$i++;
			//上下架
			$Jia = $rs["jia"];
			//编号
			$Bianhao = $rs["bianhao"];
			//型号
			$Xinhao = $rs["baozhiqi"];
			//库存
			$Lei_bg= $rs["kucun"];
			//产品排序
			$Lei_sm= $rs["px"];
			//表格整理
			$rows[$i]["A"]=$Jia;
			$rows[$i]["B"]=$Bianhao;
			$rows[$i]["C"]=$Xinhao;
			$rows[$i]["D"]=$Lei_bg;
			$rows[$i]["E"]=$Lei_sm;
		}while ($i<$num);
	}
	$writer = new XLSXWriter();
	//$writer->setTitle('标题');
	//$writer->setSubject('主题');
	//$writer->setAuthor('作者名字');
	//$writer->setCompany('公司名字');
	//$writer->setKeywords('关键字');
	//$writer->setDescription('描述');
	//$writer->setTempDir('临时目录');
	# 合并单元格,第一行的大标题
	//$writer->markMergedCell('Sheet1', $start_row = 0, $start_col = 0, $end_row = 0, $end_col = 5);
	# 每列标题头
	$writer->writeSheetHeader('Sheet1', $header, $col_options);
	# 表数据行插入
	foreach ($rows as $row) {
		$writer->writeSheetRow('Sheet1', $row, $styles1);
	}
	#统计行数 返回行数
	$writer->countSheetRows('Sheet1');
	# 输出文档
	$writer->writeToStdOut();
	// $writer->writeToFile('example.xlsx');
	// echo $writer->writeToString();  #没什么卵用
	// $writer->log('错误信息');  # 控制台输出错误信息  数据支持数组、字符串
	exit(0);
}
?>

三/不使用插件导出

<?php
//检查sql防注入
$_POST = qwbSQL_v2 ( $_POST );
$_GET = qwbSQL_v2 ( $_GET );
if($_POST["act"]!="download"){
	echo "<script>alert('信息错误!');history.go(-1);</script>";exit();
}else{
	$S_big = $_POST["S_big"];
	$S_small = $_POST["S_small"];
	$S_xiao = $_POST["S_xiao"];
	$S_xxxx = $_POST["S_xxxx"];
}
?>
<!DOCTYPE html>
<html lang="zh-cn">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="referrer" content="origin" />
<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title></title>
</head>
<body>
<?php
ob_end_clean();//清除缓冲区,避免乱码
$datetime = date('Ymdhis', time());        
header('Content-type:application/vnd.ms-excel;charset=GBK');
header('Content-Disposition:attachment;filename='.$datetime.'产品信息表.xls');//文件名自己取
$THead = array('一级类别' ,'二级类别' ,'三级类别' ,'四级类别');//这里是要输出的列头
$sql="select * from php_product where (1=1) and jia=1 order by px desc,id desc";
$result = mysqli_query($conn, $sql);
$num = mysqli_num_rows($result);//当前信息总数
if($num){
$TBody = array();
	$i = 0;
	//循环
	do{$rs = mysqli_fetch_array($result);$i++;
		//获取当前产品的四级类别
		$Lei_xxxx = $rs["xxxx"];
		//获取当前产品的三级类别
		$Lei_xiao = $rs["xiao"];
		//获取当前产品的二级类别
		$Lei_sm = $rs["small"];
		//获取当前产品的一级类别
		$Lei_bg = $rs["big"];
		//表格整理
		$TBody[$i]["A"]=$Jia;
		$TBody[$i]["B"]=$Bianhao;
		$TBody[$i]["C"]=$Xinhao;
		$TBody[$i]["D"]=$Lei_bg;
	}while ($i<$num);
}
echo '<table>';//以表格的形式输出  也可以字符串拼接
//输出head
echo '<tr>';
foreach ( $THead as $h )
{
	echo '<th style=" color:#ff0000; font-size:20px;font-weight:bold;">' . $h . '</th>';//直接输出即可,    
}
echo '</tr>';
//输出body
foreach ( $TBody as $row )
{
	echo '<tr>';
		foreach ($row as $v) {
			echo '<td style="text-align:center;">' . $v . '</td>';
		}
	echo '</tr>';
}
echo '</table>';  
?>
</body>
</html>

好的导出好了,导入呢?因为需要保证产品表格导出后,还可以通过导出的表导入。
这导入使用的是PHPExcel
做了下测试发现方法二和三导出的表再导入都不行,表格格式一样都是.xsl还是不行。那就只能使用PHPExcel导出
难道是因为插件本身的原因???有知道的大佬,麻烦提醒或者告知下

四/PHPExcel导出

导入比较麻烦,需要判定和调整下获取的数据类型

<?php
if($_POST["act"]=="dao"){
	$xlsurl = "..".$_POST["up1_picurl"];//获取上传文件
	$shuliang = $_POST["shuliang"]-1;//这里在前端做了一个输入表格的行数,减一是减去标题
  //------------------------------------判断文件------------------------------------------------
	if($xlsurl==".."){
	 echo"<script LANGUAGE='javascript'>alert('未选择文件!');window.top.location='?act=on';</script>";exit();
	}
	if(!file_exists($xlsurl)){
	 echo"<script LANGUAGE='javascript'>alert('文件不存在!');window.top.location='?act=on';</script>";exit();
	}
  //------------------------------------判断数量------------------------------------------------
	if(!$shuliang){
	 echo"<script LANGUAGE='javascript'>alert('数量格式错误1!');window.top.location='?act=on';</script>";exit();
	}
	if(!is_numeric($shuliang)){
	 echo"<script LANGUAGE='javascript'>alert('数量格式错误2!');window.top.location='?act=on';</script>";exit();
	}
	//exit($shuliang);
	set_time_limit(90);
	ini_set("memory_limit", "1024M");
  //------------------------------------------------------------------------------------
	require_once './PHPExcel/PHPExcel.php';
	require_once './PHPExcel/PHPExcel/IOFactory.php';
	require_once './PHPExcel/PHPExcel/Reader/Excel5.php';
	$objReader = PHPExcel_IOFactory::createReader('Excel5'); //这里是导入excel2007 的xlsx格式,如果是2003格式可以把“excel2007”换成“Excel5",$excelpath是你的excel路径。
	//$excelpath='./ExcelX/'.$p_id.'-1.xls';
	$excelpath=$xlsurl;
	$objPHPExcel = $objReader->load($excelpath); 
	$sheet = $objPHPExcel->getSheet(0); 
	//$highestRow = $sheet->getHighestRow();           //取得总行数
	$highestRow = $shuliang + 1;           //取得总行数
	$highestColumn = $sheet->getHighestColumn(); //取得总列数
	//商品
	$sqls = array();
	$pd_shuliang = 0;
	for($j=2;$j<=$highestRow;$j++) //从第二行开始读取数据
	{ 
		$str="";
		for($k='A';$k<=$highestColumn;$k++)  //从A列读取数据
		{ 
			$str .=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue().'|*|';//读取单元格
		} 
		$str=mb_convert_encoding($str,'GBK','auto');//根据自己编码修改
		$strs = explode("|*|",$str);
		//echo $str . "<br />";
		$jia = $strs[0];
		if(!is_numeric($jia)){$jia = 1;}
		$bianhao = $strs[1];
		$bianhao = str_replace("'","\'", $bianhao);
		$baozhiqi = preg_replace("/(\s|\&nbsp\;| |\xc2\xa0)/","",$strs[2]);
		$big = intval($strs[3]);
		$small =intval($strs[4]);
		$xiao =intval($strs[5]);
		$xxxx = intval($strs[6]);
		$Price = $strs[7];
		$kucun = intval($strs[8]);
		$px = intval($strs[9]);
		if(!is_numeric($big)){/*$big = 0;*/echo "<script>alert('上传失败![一级类别]必须为纯数字[".$big."]');parent.location.reload();</script >";exit;}
		if(!is_numeric($small)){/*$small = 0;*/echo "<script>alert('上传失败![二级类别]必须为纯数字');parent.location.reload();</script >";exit;}
		if(!is_numeric($xiao)){/*$xiao = 0;*/echo "<script>alert('上传失败!三级类别]必须为纯数字');parent.location.reload();</script >";exit;}
		if(!is_numeric($xxxx)){/*$xxxx = 0;*/echo "<script>alert('上传失败![四级类别]必须为纯数字');parent.location.reload();</script >";exit;}
		$date = $formdate;
		if ($big && $small && $xiao && $xxxx){
		//2020-07-07日添加
			$sqlrbig = "select * from php_product_big where id=".$big."";
			$resultrbig = mysqli_query($conn,$sqlrbig); 
			$numbig=mysqli_num_rows($resultrbig);
			if($numbig==""){
				echo "<script>alert('上传失败!一级类别信息不匹配!');</script >";
				echo "<script>parent.location.reload();</script >";
				exit;
			}
			$sqlrsmall = "select * from php_product_small where id=".$small." and big=".$big."";
			$resultrsmall = mysqli_query($conn,$sqlrsmall); 
			$numsmall=mysqli_num_rows($resultrsmall);
			if($numsmall==""){
				echo "<script>alert('上传失败!二级类别信息不匹配!');</script >";
				echo "<script>parent.location.reload();</script >";
				exit;
			}
			$sqlrxiao = "select * from php_product_xiao where id=".$xiao." and big=".$big." and small=".$small."";
			$resultrxiao = mysqli_query($conn,$sqlrxiao); 
			$numxiao=mysqli_num_rows($resultrxiao);
			if($numxiao==""){
				echo "<script>alert('上传失败!三级类别信息不匹配!');</script >";
				echo "<script>parent.location.reload();</script >";
				exit;
			}	
			$sqlrxxxx = "select * from php_product_xxxx where id=".$xxxx." and big=".$big." and small=".$small." and xiao=".$xiao."";
			$resultrxxxx = mysqli_query($conn,$sqlrxxxx); 
			$numxxxx=mysqli_num_rows($resultrxxxx);
			if($numxxxx==""){
				echo "<script>alert('上传失败!四级类别信息不匹配!');</script >";
				echo "<script>parent.location.reload();</script >";
				exit;
			}
			$sqlrxin = "select * from php_product where xxxx=".$xxxx." and big=".$big." and small=".$small." and xiao=".$xiao." and baozhiqi='".$baozhiqi."'";
			$resultrxin = mysqli_query($conn,$sqlrxin); 
			$numxin=mysqli_num_rows($resultrxin);
			if($numxin==""){echo "<script>alert('一级类别".$big."二级类别".$small."三级类别".$xiao."四级类别".$xxxx."');</script >";exit;}//看下获取的数据
			//上传数据么,会牵扯到是修改还是新增,加个判断
			if($numxin==""){
				$adddate = gmdate("Y-m-d H:i:s", PHPExcel_Shared_Date::ExcelToPHP($adddate));
				//让他成为数组,循环结束后,统一执行								
				$sqledit[] = "INSERT INTO php_product (
							member,productid,jia,bianhao,baozhiqi,big,small,xiao,xxxx,kucun,Price,px,date,
							TCjine,vdlax
							) VALUES (
							'3','{$productid}','{$jia}','{$bianhao}','{$baozhiqi}','{$big}','{$small}','{$xiao}','{$xxxx}','{$kucun}','{$Price}','{$px}','{$formdate}','1','2');"; 
			}else{
				$sqledit[]="update php_product set kucun='{$kucun}',jia='{$jia}',Price='{$Price}',px='{$px}',date='{$formdate}' where big=".$big." and small=".$small." and xiao=".$xiao." and xxxx=".$xxxx." and baozhiqi='".$baozhiqi."'";
			}
			$pd_shuliang = $pd_shuliang+1;
		}
		//跳出本循环,执行下面的代码(这里牵扯的,当客户填写的行数,大于当前表格的内容行数,按照实际行数循环,为空跳出循环)
		if(!$big || !$xiao|| !$xxxx|| !$small){
			break;
		}
	}
	
	if($pd_shuliang==0){//再加个判断,如读取数据没返回
			echo "<script>alert('上传失败!请认真阅读“注意事项”');</script >";
			echo "<script>parent.location.reload();</script >";
			exit;
	}
	if(count($sqledit)){
		for($ic=0;$ic<count($sqledit);$ic++){
		  $resultc = mysqli_query($conn,$sqledit[$ic]);
		}
		echo "<script>alert('上传完成".$j."!');</script >";
		echo "<script>parent.location.reload();</script >";
	}
	exit;
}
?>

就这些了测试发现方法二和三导出的表格再导入都不行,表格格式一样都是.xsl还是不行。那就只能使用PHPExcel导出
难道是因为插件本身的原因???有知道的大佬,麻烦提醒或者告知下

20201024
忘了给文件了…
https://pan.baidu.com/s/1tpmC9GILqPmxK2sCH8yKzg
提取码:xyxs

20201028
导入的时候遇到点问题,就是在解析表格数据时。不是整数数据会有空格‘口123456口’这样!
把数据进行下清除空格
像这样 KaTeX parse error: Undefined control sequence: \s at position 27: …reg_replace("/(\̲s̲|\&nbsp\;| |\xc…content);
参考文章

200221104
PHPExcel合并单元格: $objPHPExcel->getActiveSheet()->mergeCells(‘A1:H1’);

 类似资料: