在使用的地方 使用Vendor() 引入
/**
* 数据导入
* @param string $file excel文件
* @param string $subtype 节点id
* @return string 返回解析数据
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
*/
function importLedgerExecl($filename = '', $subtype)
{
Vendor('PHPExcel.PHPExcel'); //引入PHP EXCEL类
}
Excel 2005 和 2007 使用不同的方法加载
$extension = strrchr($filename, '.');
if ($extension == '.xlsx') {
$objReader = new \PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load($filename);
} else if ($extension == '.xls') {
$objReader = new \PHPExcel_Reader_Excel5();
$objPHPExcel = $objReader->load($filename);
}
获取Excel 内工作表数量[ Sheet ]
$sheetCount = $objPHPExcel->getSheetCount();
for ($i = 0; $i < $sheetCount; $i++)
{
$objWorksheet = $objPHPExcel->getSheet($i);
}
// 如果只有一个工作表
// $objWorksheet = $objPHPExcel->getActiveSheet();
获取工作表名称
$sheetTitle = $objWorksheet->getTitle();
1
获取Excel 最大行数
$highestRow = $objWorksheet->getHighestRow();
1
获取Excel 最大列数
$highestColumn = $objWorksheet->getHighestColumn();
1
将列名称转换为数字 [ A->0 ,B->1]【如果列数超过Z列,自行百度处理方法】
$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
1
获取公式的值
getFormattedValue()
1
获取文本值
getValue()
1
循环行列获取单元格内的数据
// 第二行开始是数据
$excelData = array();
for ($row = 2; $row <= $highestRow; $row++) {
//根据第二行获取第0列的值
$indexColVal = (string)$objWorksheet->getCellByColumnAndRow(0, $row)->getValue();
//如果第二行第一列的值为空则结束遍历
if ($indexColVal == '') {
break;
}
for ($col = 0; $col < $highestColumnIndex; $col++) {
$excelData[$row][] = (string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
}
}
获取Excel 文档中所有图片
// 返回值为集合
//$objWorksheet->getDrawingCollection()
foreach ($objWorksheet->getDrawingCollection() as $drawing) {
}
获取图片所在行和列[ Excel 内图片和文本值是分开的,所以如果既要读取文本又要读取图片,必须分开的读取 ]
list($startColumn, $startRow) = \PHPExcel_Cell::coordinateFromString($drawing->getCoordinates());
1
获取图片的路径
$fileName = $drawing->getPath();
1
获取Excel 内图片的名称
// [eg:image1.png]
$imgName = $drawing->getIndexedFilename();
//后缀
$ext = strrchr($fileName, '.');
PHPExcel 上传 Excel 代码
上传Excel,读取 Excel 文本内容存入数据库
贴上Excel 文件格式
/**
* 数据导入
* @param string $file excel文件
* @param string $subtype 节点id
* @return string 返回解析数据
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
*/
function importLedgerExecl($filename = '', $subtype)
{
Vendor('PHPExcel.PHPExcel'); //引入PHP EXCEL类
//---------------读取 $newName 数据存入数据库
$extension = strrchr($filename, '.');
if ($extension == '.xlsx') {
$objReader = new \PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load($filename);
} else if ($extension == '.xls') {
$objReader = new \PHPExcel_Reader_Excel5();
$objPHPExcel = $objReader->load($filename);
}
$subject = M('subject');
//获取工作表的数目
$sheetCount = $objPHPExcel->getSheetCount();
for ($i = 0; $i < $sheetCount; $i++) {
$objWorksheet = $objPHPExcel->getSheet($i);//getActiveSheet()
$sheetTitle = $objWorksheet->getTitle();//sheet工作表名称
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
$excelData = array();
// xls [第一行为列名]
for ($row = 2; $row <= $highestRow; $row++) {
//getFormattedValue 获取 公式计算后 的值 ,getValue 获取文本 值
$indexColVal = (string)$objWorksheet->getCellByColumnAndRow(0, $row)->getValue();
if ($indexColVal == '') {
break;
}
for ($col = 0; $col < $highestColumnIndex; $col++) {
$excelData[$row][] = (string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();//getValue();
}
}
$this->excelData = $excelData;
$xlsData=$this->xlsHandle($subtype);
// 超过50 条数据则分批处理,防止sql 超长产生异常造成执行失败
if (count($xlsData) > 50) {
$chunkArr = array_chunk($xlsData, 50);
foreach ($chunkArr as $item) {
$subject->addAll($item);
}
} else {
$subject->addAll($xlsData);
}
}
}
#region xls 数据处理
function xlsHandle($subtype)
{
$newData = array();
foreach ($this->excelData as $k => $val) {
$item = array();
$item['subtype']=$subtype;
$item['category'] = $val[0];
$item['subtitle'] = $val[1];
$item['opts'] = $val[2];
$item['answer'] = $val[3];
$item['analysis']=$val[4];
$item['cdate'] = time();
$newData[] = $item;
}
return $newData;
}
#endregion
读取Excel 内的图片
将上传的 Excel 拷贝一份
读取拷贝的文件,获取文件内所有图片和图片所属的行列,将图片复制到项目某文件夹内,将图片路径与图片在Excel 内所属的行列值生成键值对
-eg: array(
'key' =>'A1'
'value' => '/Public/images/demo.png'
);
修改上传的Excel ,根据键值对修改单元格,将图片路径写入Excel
修改完成后,将修改后的Excel拷贝一份
读取最终拷贝的Excel 文件数据,录入数据库
删除拷贝文件,保留最终文件
贴上Excel 文件格式
public $excelData = null;
public $xlsAllImages = [];
/**
* 数据导入
* @param string $file excel文件
* @param string $sheet
* @return string 返回解析数据
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
*/
function importExecl($filename = '')
{
Vendor('PHPExcel.PHPExcel'); //引入PHP EXCEL类
//------------------------------------------------
//---------拷贝当前xls
$extension = strrchr($filename, '.');
$tempXlsName = C('XLSTEMP') . time() . mt_rand() . $extension;
$copyStatus = copy($filename, $tempXlsName);
if ($copyStatus) {
// 读取临时xls
if ($extension == '.xlsx') {
$objReader = new \PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load($tempXlsName);
} else if ($extension == '.xls') {
$objReader = new \PHPExcel_Reader_Excel5();
$objPHPExcel = $objReader->load($tempXlsName);
}
//获取工作表的数目
$sheetCount = $objPHPExcel->getSheetCount();
for ($i = 0; $i < $sheetCount; $i++) {
$objWorksheet = $objPHPExcel->getSheet($i);
$list = $this->getXlsImg($objWorksheet);
array_push($this->xlsAllImages, $list);
}
//释放对象
$list = null;
$objReader = null;
$objPHPExcel = null;
//删除临时文件
unlink($tempXlsName);
}
//------如果当前xls 包含图片,则读取上传的xls 进行修改
if (count($this->xlsAllImages) > 0) {
//将上传文件保存为新名称[因当前上传文件正在编辑使用中无法保存]
$newXlsName = C('XLSFLODER') . date('Ymd', time()) . time() . mt_rand();
//--------修改上传的xls
if ($extension == '.xlsx') {
$objReader = new \PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load($filename);
} else if ($extension == '.xls') {
$objReader = new \PHPExcel_Reader_Excel5();
$objPHPExcel = $objReader->load($filename);
}
// 创建写入[]
if ($extension == '.xlsx') {
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$newXlsName .= '.xlsx';
} else if ($extension == '.xls') {
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$newXlsName .= '.xls';
}
for ($i = 0; $i < $sheetCount; $i++) {
$objWorksheet = $objPHPExcel->getSheet($i);
foreach ($this->xlsAllImages[$i] as $key => $val) {
$objWorksheet->setCellValue($val['key'], $val['value']);
}
ob_end_clean();//清除缓存防止乱码
//另存为新文件
$objWriter->save($newXlsName);
//删除上传文件
unlink($filename);
}
//释放对象
$objReader=null;
$objPHPExcel=null;
}
//---------------读取 $newName 数据存入数据库
if ($extension == '.xlsx') {
$objReader = new \PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load($newXlsName);
} else if ($extension == '.xls') {
$objReader = new \PHPExcel_Reader_Excel5();
$objPHPExcel = $objReader->load($newXlsName);
}
$subject=M('subject');
//获取工作表的数目
//$sheetCount = $objPHPExcel->getSheetCount();
for ($i = 0; $i < $sheetCount; $i++) {
$objWorksheet = $objPHPExcel->getSheet($i);
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
$excelData = array();
// xls 从第一行开始计算[第一行为列名]
for ($row = 1; $row <= $highestRow; $row++) {
//getFormattedValue 获取 公式计算后 的值 ,getValue 获取文本 值
$indexColVal = (string)$objWorksheet->getCellByColumnAndRow(0, $row)->getValue();
if ($indexColVal == '') {
break;
}
for ($col = 0; $col < $highestColumnIndex; $col++) {
$excelData[$row][] = (string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();//getValue();
}
}
$this->excelData = $excelData;
$xlsData=$this->xlsHandle();
// 超过50 条数据则分批处理,防止sql 超长产生异常造成执行失败
if (count($xlsData) > 50) {
$chunkArr = array_chunk($xlsData, 50);
foreach ($chunkArr as $item) {
$subject->addAll($item);
}
} else {
$subject->addAll($xlsData);
}
}
}
/*获取所有xls 内的图片
**/
function getXlsImg($objWorksheet)
{
$path = C('XLSIMGPATH');
$imgList = array();
foreach ($objWorksheet->getDrawingCollection() as $drawing) {
$newFileName = date('Ymd', time()) . time() . mt_rand();
//获取图片所属单元格1
list($startColumn, $startRow) = \PHPExcel_Cell::coordinateFromString($drawing->getCoordinates());//获取图片所在行和列
if ($drawing instanceof \PHPExcel_Worksheet_Drawing) {
$filename = $drawing->getPath();
//xls 自定义图片名称[eg:image1.png]
$fileName = $drawing->getIndexedFilename();
$ext = strrchr($fileName, '.');
$isSuccess = copy($filename, $path . $newFileName . $ext);
if ($isSuccess) {
$temp = array(
'key' => $startColumn . $startRow,
'value' => $newFileName . $ext
);
array_push($imgList, $temp);
}
}
}
return $imgList;
}
#region xls 数据处理
public function xlsHandle()
{
$newData = array();
foreach ($this->excelData as $k => $val) {
$item = array();
//删除数据序号
array_shift($val);
$item['year'] = $val[0];
$item['subtype'] = $val[1];
$item['subtitle'] = $val[2];
$item['opts']=$val[3];
$item['answer']=$val[4];
$item['analysis']=$val[5];
$item['subimg']=$val[6];
$item['cdate'] = strtotime(date('Y-m-d'));
$newData[] = $item;
}
return $newData;
#endregion
原连接:https://blog.csdn.net/csdn_Info/article/details/84557938