读取
//读取Excel内容
function readExcel($strPath)
{
include ‘PHPExcel.php’;
include ‘PHPExcel/Writer/Excel2007.php’;
//或者include ‘PHPExcel/Writer/Excel5.php’; 用于输出.xls的
$arrTmp = explode(‘.’, $strPath);
switch ($arrTmp[count($arrTmp) – 1]) {
case ‘xlsx’:
$objReader = PHPExcel_IOFactory::createReader(‘Excel2007’);
break;
case ‘xls’:
$objReader = PHPExcel_IOFactory::createReader(‘Excel5’);
break;
case ‘svc’:
$objReader = PHPExcel_IOFactory::createReader(‘CSV’);
break;
default:
return [];
break;
}
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($strPath);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
$excelData = array();
for ($row = 1; $row <= $highestRow; $row++) {
for ($col = 0; $col < $highestColumnIndex; $col++) {
$excelData[$row][] = (string) $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
}
}
return $excelData;
}
生成
/**
* 生成Excel并导出下载
*
* @param [string] $title 表格第一行合并居中文字
* @param [array] $arrData 表格头部说明 如 [‘姓名’,’性别’]
* @param [array] $input 表格数据 如 [[‘张三’,’男’],[‘李四’,’女’]]
* @return void
*/
function foundExcel($title,$arrData,$input)
{
include ‘PHPExcel.php’;
include ‘PHPExcel/Writer/Excel2007.php’;
//或者include ‘PHPExcel/Writer/Excel5.php’; 用于输出.xls的
$objPHPExcel = new \PHPExcel();
$objPHPExcel->getProperties()->setCreator(“secondar”);
$objPHPExcel->getProperties()->settitle(“导出数据”);
$objPHPExcel->getActiveSheet()->setCellValue(“A1”, $title);
$arrExcel = [‘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’];
$arrFor[‘newi’] = -1;
$i = 0;
$di = 0;
$arrTable = [];
$end = “”;
while (true) {
if ($arrFor[‘newi’] > -1) {
// $str = ‘$str = $arrExcel[$arrFor[“newi”]].$arrExcel[$i];’;
// eval($str);
$str = $arrExcel[$arrFor[“newi”]].$arrExcel[$i];
} else {
// $str = ‘$str = $arrExcel[$i];’;
// eval($str);
$str = $arrExcel[$i];
}
$i++;
$objPHPExcel->getActiveSheet()->setCellValue($str . “2”, $arrData[$di], \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->getColumnDimension($str)->setWidth(18);
if (!empty($arrData[$di])) {
$end = $str;
}
$arrTable[] = $str;
$di++;
if ($i > 25) {
$i = 0;
$arrFor[‘newi’]++;
}
if ($arrFor[‘newi’] > -1) {
$sum = $arrFor[‘newi’] + 1;
$sum = $sum * 26;
$sum = $sum + $i;
if ($sum >= count($arrData)) {
break;
}
}
}
$objPHPExcel->getActiveSheet()->mergeCells(‘A1:’ . $end . ‘1’);
$objPHPExcel->getActiveSheet()->getRowDimension(‘1’)->setRowHeight(30);
$objPHPExcel->getActiveSheet()->getstyle(‘A1’)->getAlignment()->setVertical(\PHPExcel_style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getstyle(‘A1’)->getAlignment()->setHorizontal(\PHPExcel_style_Alignment::HORIZONTAL_CENTER);
$i = 3;
foreach ($input as $k => $v) {
$i1 = 0;
foreach ($v as $vk => $vv) {
$objPHPExcel->getActiveSheet()->setCellValueExplicit($arrTable[$i1] . $i, $vv, \PHPExcel_Cell_DataType::TYPE_STRING);
$i1++;
}
$i++;
}
ob_end_clean();
header(‘pragma:public’);
header(‘Cache-Control: max-age=0’);
header(‘Cache-Control: max-age=1’);
header(‘Expires: Mon, 26 Jul 1997 05:00:00 GMT’);
header(‘Last-Modified: ‘ . gmdate(‘D, d M Y H:i:s’) . ‘ GMT’);
header(‘Cache-Control: cache, must-revalidate’);
header(‘Content-type:application/vnd.ms-excel;charset=utf-8;name=’ . $title . ‘.xls’);
header(‘Content-Disposition:attachment;filename=’ . time() . ‘.xls’);
$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save(‘php://output’);
}
声明:
本文采用
BY-NC-SA
协议进行授权,如无注明均为原创,转载请注明转自
一颗大萝北
本文地址: php/PHPExcel 读取Excel与生成Excel,xls,xlsx,svc
本文地址: php/PHPExcel 读取Excel与生成Excel,xls,xlsx,svc