PHPExcel
是用来操作Office Excel 文档的一个PHP类库,它基于微软的OpenXML标准和PHP语言。可以使用它来读取、写入不同格式的电子表格,能正常操作的文件保护Excel2007、Excel5等,(.xls, .xlsx, cvs…)
PHPExcel官网下载地址,目前最新版本为1.8.0, 选择一个版本下载,解压,把解压后的Classes里的文件传到你的项目。
使用PHPExcel
在使用PHPExcel前,必须先导入PHPExcel的核心文件
//加载 PHPExcel 核心
require_once(ROOT_COREAPPLIBS.'/thirdlb/excel/PHPExcel.php');
PS:若你只想导入Excel文件, 引入 PHPExcel_IOFactory
require_once(ROOT_COREAPPLIBS.'/thirdlb/excel/PHPExcel/PHPExcel_IOFactory.php');
开始使用
引入了PHPExcel核心后,后续就不在PHPExcel的加载问题了。
导入excel文件
public function excel2array(){
//文件名
$file = "a2.xls";
$file = "a.xlsx";
//获取文件类型 xls xlsx cvs等
$type = strtolower( pathinfo($file, PATHINFO_EXTENSION) );
//导入文件所在路径, 如我要导入的是 F:/a.xlsx 文件
$path = 'F:'.'/'.$file;
if (!file_exists($path)) {
die('no file!');
}
/**
* 根据不同类型分别操作
* csv格式单独处理,
*/
if( $type=='xlsx'||$type=='xls' ){
//导入文件到PHPExcel
$objPHPExcel = PHPExcel_IOFactory::load($path);
}else if( $type=='csv' ){
$objReader = PHPExcel_IOFactory::createReader('CSV')
->setDelimiter(',')
->setInputEncoding('GBK') //不设置将导致中文列内容返回boolean(false)或乱码
->setEnclosure('"')
->setLineEnding("\r\n")
->setSheetIndex(0);
$objPHPExcel = $objReader->load($path);
}else{
die('Not supported file types!');
}
//获取Excel文件中的第一个 sheet
$sheet = $objPHPExcel->getSheet(0);
//获取行数与列数,注意列数需要转换
$highestRowNum = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$highestColumnNum = PHPExcel_Cell::columnIndexFromString($highestColumn);
//取得字段,这里测试表格中的第一行为数据的字段,因此先取出用来作后面数组的键名
$filed = array();
for($i=0; $i<$highestColumnNum;$i++){
$cellName = PHPExcel_Cell::stringFromColumnIndex($i).'1';
$cellVal = $sheet->getCell($cellName)->getValue();//取得列内容
$filed []= $cellVal;
}
//开始取出数据并存入数组
$data = array();
for($i=2;$i<=$highestRowNum;$i++){//ignore row 1
$row = array();
for($j=0; $j<$highestColumnNum;$j++){
$cellName = PHPExcel_Cell::stringFromColumnIndex($j).$i;
$cellVal = $sheet->getCell($cellName)->getValue();
$row[ $filed[$j] ] = $cellVal;
}
$data []= $row;
}
//print_r($data);
return $data;
}
我要导入的文件(F:/a.xlsx)
运行结果
导出excel文件
导出excel前, 你需要知道你想导入的excel格式,这有助于理解。如,我想导出后的excel如下
public function outexcelAction()
{
$title_excel = '先写入在导出的Excel文件';
//把导入的excel数据导入
$list = $this->getData();
//只导出100条左右
$len = count($list);
if ( $len >= 100) {
$len = 100;
}
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("ctos");
/* ->setLastModifiedBy("ctos")
->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"); */
// 设置每一列宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
// 设置行高度
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
// 字体和样式
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);//字体size
$objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getFont()->setBold(true);//加粗
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
// 设置水平居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('I')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// 合并
$objPHPExcel->getActiveSheet()->mergeCells('A1:I1');
// 表头
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', '序号')
->setCellValue('A2', '序号')
->setCellValue('B2', '英文名')
->setCellValue('C2', '中文名')
->setCellValue('D2', 'DISTRICTEN')
->setCellValue('E2', 'DISTRICTCN')
->setCellValue('F2', 'PROVEN')
->setCellValue('G2', 'PROVCN')
->setCellValue('H2', 'NATIONEN')
->setCellValue('I2', 'NATIONCN');
// 内容
for ($i = 0; $i < $len; $i++) {
$objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($i + 3), $list[$i]['AREAID']);
$objPHPExcel->getActiveSheet(0)->setCellValue('B' . ($i + 3), $list[$i]['NAMEEN']);
$objPHPExcel->getActiveSheet(0)->setCellValue('C' . ($i + 3), $list[$i]['NAMECN']);
$objPHPExcel->getActiveSheet(0)->setCellValue('D' . ($i + 3), $list[$i]['DISTRICTEN']);
$objPHPExcel->getActiveSheet(0)->setCellValue('E' . ($i + 3), $list[$i]['DISTRICTCN']);
$objPHPExcel->getActiveSheet(0)->setCellValue('F' . ($i + 3), $list[$i]['PROVEN']);
$objPHPExcel->getActiveSheet(0)->setCellValue('G' . ($i + 3), $list[$i]['PROVCN']);
$objPHPExcel->getActiveSheet(0)->setCellValue('H' . ($i + 3), $list[$i]['NATIONEN']);
$objPHPExcel->getActiveSheet(0)->setCellValue('I' . ($i + 3), $list[$i]['NATIONCN']);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($i + 3) . ':I' . ($i + 3))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($i + 3) . ':I' . ($i + 3))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getRowDimension($i + 3)->setRowHeight(16);
}
// 设置 sheet 名字
$objPHPExcel->getActiveSheet()->setTitle($title_excel);
// 设置打开时选择 第一个 sheet
$objPHPExcel->setActiveSheetIndex(0);
// 输出
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $title_excel . '.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
直接访问该方法时,浏览器提示下载文件, 可下载后打开查看导出效果。
参考博客
www.godruoyi.com