PHP操作execl

PHP写execl文件

直接上代码.

require_once 'phpexcel.php';
require_once 'phpexcel/writer/excel5.php';
require_once 'phpexcel/writer/excel2007.php';

$obj_phpexcel = new PHPExcel();//创建类实例
$obj_phpexcel->setActiveSheetIndex(0);//设置当前页
$obj_phpexcel->getActiveSheet()->mergeCells('A1:D2');//合并单元格
$obj_phpexcel->getActiveSheet()->getStyle('A1:D2')->applyFromArray(
	[
		'font' 		=> ['bold' => true],
		'alignment' => [
			'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
		],
		'borders' 	=> [
			'top' => [
				'style' => PHPExcel_Style_Border::BORDER_THIN
			]
		],
		'fill' => [
			'type' 			=> PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
			'rotation' 		=> 90,
			'startcolor' 	=> ['rgb' => 'DCDCDC'],
			'endcolor' 		=> ['rgb' => 'FFFFFF']
		]
	]
);
//对合并的单元格，设置格式，应用array方式，font为字体加粗，
//alignment为对齐方式（居中），borders为边框，fill为填充方式

$obj_phpexcel
	->getActiveSheet()
	->getStyle('A1')
	->getFont()
	->setSize(12);  //设置合并单元格的字体大小
$obj_phpexcel
	->getActiveSheet()
	->setCellValue('A1', '虾米大王创建的PHPEXCELL测试'); //对合并的单元格填充文字
$obj_phpexcel
	->getActiveSheet()
	->getStyle('A1')
	->getFont()
	->getColor()
	->setARGB(PHPExcel_Style_Color::COLOR_BLUE); //设置字体颜色
$obj_phpexcel->getActiveSheet()->getStyle('A3:D3')->applyFromArray(
	[
		'fill' => [
			'type' 	=> PHPExcel_Style_Fill::FILL_SOLID,
			'color' => ['rgb' => 'D1EEEE']
		],
	]
);//对第三行设置fill格式，和单元格颜色
$obj_phpexcel->getActiveSheet()->setCellValue('A3', 'test1'); //对第三行1列，填充文字
$obj_phpexcel->getActiveSheet()->setCellValue('B3', 'test2');
$obj_phpexcel->getActiveSheet()->setCellValue('C3', 'test3');
$obj_phpexcel->getActiveSheet()->setCellValue('D3', 'test4');
$obj_phpexcel
	->getActiveSheet()
	->getColumnDimension('A')
	->setAutoSize(true); //列宽度改为自动宽度
$obj_phpexcel->setActiveSheetIndex(0); //设置当前活动页
$obj_writer = PHPExcel_IOFactory::createWriter($obj_phpexcel, 'Excel5');
$obj_writer->save('myexchel5.xls');

PHP 读 execl文件

require_once 'phpexcel.php';
require_once 'phpexcel/read/excel5.php';
require_once 'phpexcel/read/excel2007.php';

$objReader = new PHPExcel_Reader_Excel2007();
if(!$objReader->canRead($dir)){
  $objReader = new PHPExcel_Reader_Excel5();
  if(!$objReader->canRead($dir)){
    echo '无法识别的Excel文件！';
    return false;
  };
};
$objPHPExcel = $objReader->load($dir.$templateName);
$sheet = $objPHPExcel->getSheet(0); //获取第一个工作表
$highestRow = $sheet->getHighestRow(); //取得总行数
$highestColumn = $sheet->getHighestColumn(); //取得总列数
$table = array();
echo "<PRE>";
for ($row = 1; $row <= $highestRow; $row++){
  $rowData = $sheet->rangeToArray(
  	'A'. $row . ':' . $highestColumn . $row,
  	NULL, 
  	TRUE, 
  	FALSE
  );
  $table[]  = $rowData[0];
};