/*****************************************************************************************************************
一、读取文件
$data = ExcelHelper::getExcelData('C:\Users\Downloads\x.xls');
var_dump($data);
二、生成文件
#标题
$title1 = array('产品SKU','产品主图','产品名称','产品英文名称','产品品类（代码）','重量','长','宽','高','海关申报代码','英文申报品名','中文申报品名','申报价值','申报币种','采购价','采购币种','默认供应商代码','销售价格','销售运费','建立原因','供应商产品地址','供应商品号','款式代码','成品','运营方式','贴标容易度','产品销售状态','销售负责人','开发负责人','自定义分类','是否需要质检','组织机构（代码）','申报说明','是否包含电池','是否为仿制品','样品数量'); #产品
$title2 = array('产品SKU','图片URL','是否主图(Y/N)'); #产品图片
$title3 = array('产品SKU','包材代码','包材数量','仓库代码'); #产品包材
$dataList = array(
"title"=>array('产品'=>$title1, '产品图片'=>$title2, '产品包材'=>$title3),
"imgIndex"=>array(0, 1), #第二列生成图片
"width"=>array(0),
"height"=>array(0)
);
$temp1 = array(array('A'=>'111', 'B'=>'./Tulips.jpg'));
$temp2 = array(array('A'=>'222', 'B'=>'E:\defeat.gif')); #不支持网络图片
$temp3 = array(array('A'=>'333'));
$dataList["data"] = array('产品'=>$temp1, '产品图片'=>$temp2, '产品包材'=>$temp3);
$obj = new ExcelHelper();
$obj->save($dataList);
*****************************************************************************************************************/
/**
* Excel文件的生成、读取、下载
*/
class ExcelHelper
{
private $objPHPExcel = null;
/**
* [读取Excel表格数据，支持多Sheet]
* @param boolean $sheet [是否读取多个工作表]
* @return [array]
*/
public static function getExcelData($filename, $key_map=[], $sheet=false)
{
#读取表格数据【自动识别文件类型】
$objExcel = PHPExcel_IOFactory::load($filename);
$sheetData = array();
#是否多Sheet
if($sheet === false){
// $sheetData = $objExcel->getSheet(0)->toArray(null, true, true, true);
$sheetData = self::getSheetByCell($objExcel, 0, $key_map);
}else{
$num = $objExcel->getSheetCount();
for($i=0; $i<$num; $i++){
// $sheetData[$i] = $objExcel->getSheet($i)->toArray(null, true, true, true);
$sheetData[$i] = self::getSheetByCell($objExcel, $i);
}
}
return $sheetData;
}
private static function getSheetByCell($objExcel, $sheet=0, $key_map)
{
$currentSheet = $objExcel->getSheet($sheet); #当前页
$row_num = $currentSheet->getHighestRow(); #当前页行数
$col_max = $currentSheet->getHighestDataColumn(); #当前页包含数据最大列号
$col_max = self::getCellNum($col_max);
$key_map = array_values($key_map);
$sheetData = array();
for($i=2; $i<=$row_num; $i++) #第一行是表头
{
$lineArr = array();
if(empty($key_map))
{
for($j=1; $j<=$col_max; $j++)
{
$col = self::getCellName($j);
$address = $col . $i; #单元格坐标
$lineArr[$col] = $currentSheet->getCell($address)->getFormattedValue();
}
}else{
foreach($key_map as $key=>$map)
{
$col = self::getCellName($key+1);
$address = $col . $i;
$lineArr[$map] = $currentSheet->getCell($address)->getFormattedValue();
}
}
$sheetData[] = $lineArr;
}
return $sheetData;
}
/**
* [数据保存到Excel表格，支持多Sheet]
* @param string $path [文件保存路径，为空时直接下载]
* @return string $filename [文件地址]
*/
public function save($data, $path='', $name='', $ext='xlsx')
{
# a）Create new PHPExcel object
if($this->objPHPExcel === null){
$this->objPHPExcel = new PHPExcel();
}
# b）Set document properties
$this->setDocumentProperties();
# c）Add data
if(!isset($data["width"])) $data["width"]=array();
if(!isset($data["height"])) $data["height"]=array();
if(!isset($data["imgIndex"])) $data["imgIndex"]=array();
$num = 0;
foreach($data['title'] as $key=>$title){
if($num != 0){
$this->objPHPExcel->createSheet();
}
$this->objPHPExcel->setActiveSheetIndex($num);
$this->objPHPExcel->getActiveSheet()->setTitle($key);
$this->setCellTitle($title, $data["width"]);
$this->setCellValue($data["data"][$key], $data["imgIndex"], $data["height"]);
$num++;
}
# d）Set active sheet index to the first sheet
$this->objPHPExcel->setActiveSheetIndex(0);
# e）
if(empty($name)) $name = date('YmdHis').rand(100, 999);
if($ext != 'xls') $ext = 'xlsx';
$type = $ext=='xls' ? 'Excel5' : 'Excel2007';
$filename = $name.'.'.$ext;
# f）
$objWriter = PHPExcel_IOFactory::createWriter($this->objPHPExcel, $type);
$newFilename = iconv('utf-8','gbk',$filename);
# g) download
if(empty($path))
{
if(PHP_SAPI == 'cli')
trigger_error('Download excel should only be run from a Web Browser', E_USER_ERROR);
$this->downloadExcel($newFilename, $objWriter);
}
$objWriter->save($path.$newFilename);
return $path.$filename;
}
#1.设置单元格Title
private function setCellTitle($title, $width)
{
$i=1;
foreach($title as $v){
$name=$this->getCellName($i);
$k=$i-1;
$objColumn = $this->objPHPExcel->getActiveSheet()->getColumnDimension($name);
if(isset($width[$k]) && $width[$k]){
$objColumn->setWidth($width[$k]);
}else{
$objColumn->setAutoSize(true);
}
$this->objPHPExcel->getActiveSheet()->setCellValue($name.'1', $v);
$i++;
}
return true;
}
#2.设置单元格的值
private function setCellValue($content, $imgIndex, $height)
{
$row=2;
foreach($content as $val){
$i=1;
foreach($val as $v){
$name=$this->getCellName($i).$row;
$k=$i-1;
# 1.指定图片在第几列
if(isset($imgIndex[$k]) && $imgIndex[$k]==1){
if(file_exists($v)){
$this->setCellImage($name, $v);
$objRow = $this->objPHPExcel->getActiveSheet()->getRowDimension($row);
if(isset($height[$k]) && $height[$k]){
$objRow->setRowHeight($height[$k]);
}else{
$objRow->setRowHeight(60);
}
}
# 2.文本内容
}else{
$this->objPHPExcel->getActiveSheet()->setCellValueExplicit($name, $v, PHPExcel_Cell_DataType::TYPE_STRING);
}
$i++;
}
$row++;
}
return true;
}
#3.设置文档属性
private function setDocumentProperties()
{
$this->objPHPExcel->getProperties()
->setCreator("Maarten Balliauw")
->setLastModifiedBy("Maarten Balliauw")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
return $this;
}
#4.获取单元格名
private static function getCellName($i)
{
$code = null;
while($i){
$i = $i-1;
$n = $i%26+1;
$code = chr($n+64).$code; #返回对应的字母
$i = floor($i/26); #舍去法取整
}
return $code;
}
private static function getCellNum($str)
{
$num = null;
$len = strlen($str) - 1;
for($i=$len; $i>=0; $i--)
{
$num += (ord($str[$i]) - 64) * (26**($len - $i));
}
return $num;
}
#5.设置单元格图片
private function setCellImage($cell, $img)
{
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('img');
$objDrawing->setDescription('img');
$objDrawing->setPath($img);
$objDrawing->setHeight(60);
$objDrawing->setCoordinates($cell);
$objDrawing->setOffsetX(10);
$objDrawing->setOffsetY(10);
$objDrawing->setRotation(0);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(36);
$objDrawing->setWorksheet($this->objPHPExcel->getActiveSheet());
return $this;
}
public function format($title, $header, $data, $extra = [])
{
$result = [
'title' => [$title => $header],
'data' => [$title => $data],
'imgIndex' => array(0),
'width' => array(0),
'height' => array(0)
];
return array_merge($result, $extra);
}
#6.直接下载表格文件
private function downloadExcel($filename, $objWriter)
{
if(ob_get_length() > 0)
{
ob_end_clean(); //清除缓冲区,避免乱码
}
header("Content-Type: application/vnd.ms-excel; charset=utf-8");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:inline;filename="'.$filename.'"');
header("Content-Transfer-Encoding: binary");
header("Last-Modified: ".gmdate("D, d M Y H:i:s")." GMT");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: no-cache");
$objWriter->save('php://output');
exit;
}
/**
* subject: 导出多个sheet
* @param array $sheetName = ['sheet名字1','sheet名字2','sheet名字3']
* @param array $title = [ ['标题1', '标题2', '标题3'], ['标题11', '标题22'], ['标题111', '标题222', '标题333', '标题444'] ]
* @param array $data = [ [['A'=>1, 'B'=>2, 'C'=>3],['A'=>1, 'B'=>2, 'C'=>3]], [['A'=>11, 'B'=>22]], [['A'=>11, 'B'=>22, 'C'=>33, 'D'=>44]] ]
*/
public function exportMultiSheet($sheetName=[], $title=[], $data=[], $fileName){
if(empty($sheetName) || empty($title) || empty($data)){
echo "参数不能为空";
exit;
}
if(count($sheetName) != count($title) || count($sheetName) != count($data)){
echo "参数数量不一致";
exit;
}
$titleData = $contentData = [];
foreach($sheetName as $k=>$s){
$titleData[$s] = $title[$k];
$contentData[$s] = $data[$k];
}
$dataList = [
'title' =>$titleData,
'data' =>$contentData,
];
$this->save($dataList, '', $fileName);
}
}
先引入 PHPExcel.php
需要的老铁们, 可以自行改进
|