PHP实现读取Excel文件的记录(一)

<?php
error_reporting(E_ALL);//报告所有错误
date_default_timezone_set('PRC');   //设置时区为中国
 

require_once './Classes/PHPExcel/IOFactory.php';
$excelFilename="安装记录111.xlsx";
 
$ext=substr($excelFilename,strripos($excelFilename,'.'));
$dataSheet=[];
 
if($ext=='.xlsx'){
	//按Excel2007文件格式读取
	$reader = PHPExcel_IOFactory::createReader('Excel2007');
	$reader->setReadDataOnly(true);
	$spreadSheet=$reader->load($excelFilename);
	$workSheet=$spreadSheet->getActiveSheet();
	foreach($workSheet->getRowIterator() as $key=>$row){
		$cellIterator=$row->getCellIterator();
		$cellIterator->setIterateOnlyExistingCells(false);
		foreach($cellIterator as $k=>$cell){
			$dataSheet[$key][$k]=$cell->getValue();
		}
	}
}elseif($ext=='.xls'){
	//以Excel5(Excel97-2003工作簿)的格式读取
	$reader = PHPExcel_IOFactory::createReader('Excel5');
	//加载excel文件
	$PHPExcel = $reader->load("WPS安装记录.xls");
	//读取第一个工作表
	$sheet = $PHPExcel->getSheet(0);
	//获取总行数
	$highestRow = $sheet->getHighestRow();
	//获取总列数
	$highestColumm = $sheet->getHighestColumn();
 
	//读取单元格的数据（从第一行，A列开始先行后列）
	for ($row = 1; $row <= $highestRow; $row++){
		for ($column = 'A'; $column <= $highestColumm; $column++) {
			$dataSheet[] = $sheet->getCell($column.$row)->getValue();
		}
	}
}else{
	exit("不能识别{$excelFilename}.\n");
}
 
print_r($dataSheet);
 
?>

require_once 'Classes/PHPExcel/Reader/Excel2007.php';
require_once 'Classes/PHPExcel/Reader/Excel5.php';
include 'Classes/PHPExcel/IOFactory.php';
function arrayToExcel($data){
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('firstsheet');
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
//add data
$i = 2;
foreach ($data as $line){
$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $line['From']);
$objPHPExcel->getActiveSheet()->getCell('A'.$i)->setDataType('n');
$objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $line['To']);
$objPHPExcel->getActiveSheet()->getCell('B'.$i)->setDataType('n');
$i++;
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$file = 'excel.xls';
$objWriter->save($file);
}

header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header('Content-Disposition:attachment;filename="excel.xls"');
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');