使用PHP导入Excel和导出数据为Excel文件
有时需要使用PHP操作Excel表格，对数据进行一个导入导出，我们使用PHP的一个开源类库PHPExcel可以轻松实现Excel的导入和导出。
PHPExcel类是php一个excel表格处理的类。 PHPExcel 是用来操作Office Excel 文档的一个PHP类库，它基于微软的OpenXML标准和PHP语言。可以使用它来读取、写入不同格式的电子表格，如 Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML等等。
官方地址：https://github.com/PHPOffice/PHPExcel，可以导出office2007格式，同时兼容2003
一、phpExcel的安装
composer require phpoffice/phpexcel
二、使用think PHP3.2框架引入 第三方上传类库和PHPExcel包。
UploadFile.class.php 文件放于 ThinkPHP\Library\Org\Util 对应文件夹下，并在第三方类库文件头增加命名空间
namespace Org\Util;
phpexcel excel包 放入目录 ThinkPHP\Library\Vendor\phpexcel，如图所示
三、编写核心代码。
1)、新增 Application\Home\Controller\IndexController.class.php 文件
<?php
namespace Home\Controller;
use Think\Controller;
class IndexController extends Controller {
public function export(){
$m=M('users');
$list=$m->select();
$this->assign('list',$list);
$this->display();
}
public function import(){
$this->display();
}
/**
*
* 导出Excel
*/
function expUser(){//导出Excel
$xlsName = "审核";
$xlsCell = array(
array('id','账号序列'),
array('name','名字'),
array('tel','电话'),
array('sex','性别'),
array('address','住址')
);
$m=M('users');
$xlsData=$m->select();
$this->exportExcel($xlsName,$xlsCell,$xlsData);
}
public function exportExcel($expTitle,$expCellName,$expTableData){
$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
$fileName = $xlsTitle.date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
$cellNum = count($expCellName);
$dataNum = count($expTableData);
Vendor('phpexcel.Classes.PHPExcel');
$objPHPExcel = new PHPExcel();
$cellName = array('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','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
// sheet命名
$objPHPExcel->getActiveSheet()->setTitle('联系人信息表');
//设置宽width
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(50);
$objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.' Export time:'.date('Y-m-d H:i:s'));
for($i=0;$i<$cellNum;$i++){ //设置表格标题
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]); //setCellValue()第一个参数从第几行开始
}
// Miscellaneous glyphs, UTF-8
for($i=0;$i<$dataNum;$i++){
for($j=0;$j<$cellNum;$j++){
$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), ' '.$expTableData[$i][$expCellName[$j][0]]);
}
}
header('pragma:public');
header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');//excel5为xls格式，excel2007为xlsx格式
$objWriter->save('php://output');
exit;
}
function impUser(){
if (!empty($_FILES)) {
$config=array(
'allowExts'=>array('xlsx','xls'),
'savePath'=>'./Public/upload/',
'saveRule'=>'time',
);
$upload = new \Org\Util\UploadFile($config);
if (!$upload->upload()) {
$this->error($upload->getErrorMsg());
} else {
$info = $upload->getUploadFileInfo();
}
Vendor('phpexcel.Classes.PHPExcel');
$file_name=$info[0]['savepath'].$info[0]['savename'];
$objReader = \PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load($file_name,$encode='utf-8');
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
for($i=3;$i<=$highestRow;$i++)
{
$data['name'] = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
$data['tel'] = $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();
$data['sex'] = $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue();
$data['address']= $objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue();
M('users')->add($data);
}
$this->success('导入成功！');
}else
{
$this->error("请选择上传的文件");
}
}
}注意：UploadFile.class.php 和 PHPExcel 各自实例化方式。
参考：think PHP3.2 自动加载： http://document.thinkphp.cn/manual_3_2/autoload.html
2)、Application\Home\View\index 目录新增 导入import.html、导出export.html 页面。
import.html
<!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Document</title> </head> <body> <a href="__URL__/export">导出数据</a> <form action="__URL__/impUser" method="post" enctype="multipart/form-data"> <input type="file" name="import"/> <input type="hidden" name="table" value="tablename"/> <input type="submit" value="导入"/> </form> </body> </html>
export.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Document</title>
</head>
<body>
<a href="__URL__/import">导入数据</a>
<a href="__URL__/export">导出数据</a>
<table border="1">
<tr>
<th>id</th>
<th>姓名</th>
<th>电话</th>
<th>住址</th>
</tr>
<foreach name="list" item="vo">
<tr>
<td>{$vo.id}</td>
<td>{$vo.name}</td>
<td>{$vo.tel}</td>
<td>{$vo.address}</td>
</tr>
</foreach>
</table>
<!--<input type="button" id="button" value="导出数据" />-->
</body>
<!--
<script src="__PUBLIC__/js/jquery-1.11.3.min.js"></script>
<script type="text/javascript">
$(function(){
$("#button").click(function(){
window.location.href="http://localhost/3.1.3/index.php/Index/expUser";
});
});
</script>
-->
</html>相关属性设定
//创建一个excel对象
$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"); //种类
//设置当前的sheet
$objPHPExcel->setActiveSheetIndex(0);
//设置sheet的name
$objPHPExcel->getActiveSheet()->setTitle('Simple');
//合并单元格
$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
//分离单元格
$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');
// Redirect output to a client’s web browser (Excel5)
ob_end_clean();//清除缓冲区,避免乱码
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="订单汇总表(' . date('Ymd-His') . ').xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
//保护cell
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
// Needs to be set to true in order to enable any worksheet protection!
$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');
//设置格式
// Set cell number formats
echo date('H:i:s') . " Set cell number formats\n";
$objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
$objPHPExcel->getActiveSheet()->
duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );
//设置宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
//设置行高度
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
//set font size bold
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);
//垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A2:J2')->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A2:J2')->getBorders()->getAllBorders()
->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//设置水平居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
//设置column的border
$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('B4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('C4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('D4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//设置border的color
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');
//设置填充颜色
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('FF808080');
//加图片
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./images/officelogo.jpg');
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Paid');
$objDrawing->setDescription('Paid');
$objDrawing->setPath('./images/paid.png');
$objDrawing->setCoordinates('B15');
$objDrawing->setOffsetX(110);
$objDrawing->setRotation(25);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
// set table header content
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', '订单数据汇总 时间:' . date('Y-m-d H:i:s'))
->setCellValue('A2', '订单ID')
->setCellValue('B2', '下单人')
->setCellValue('C2', '客户名称')
->setCellValue('D2', '下单时间')
->setCellValue('E2', '需求机型')
->setCellValue('F2', '需求数量')
->setCellValue('G2', '需求交期')
->setCellValue('H2', '确认BOM料号')
->setCellValue('I2', 'PMC确认交期')
->setCellValue('J2', 'PMC交货备注');
// Miscellaneous glyphs, UTF-8
for ($i = 0; $i < count($result) - 1; $i++) {
//设置单元格的值
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'String');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 12);
$objPHPExcel->getActiveSheet()->setCellValue('A3', true);
$objPHPExcel->getActiveSheet()->setCellValue('C5', '=SUM(C2:C4)');
$objPHPExcel->getActiveSheet()->setCellValue('B8', '=MIN(B2:C5)');
$objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($i + 3), $result[$i]['id']);
$objPHPExcel->getActiveSheet(0)->setCellValue('B' . ($i + 3), $result[$i]['realname']);
$objPHPExcel->getActiveSheet(0)->setCellValue('C' . ($i + 3), $result[$i]['customer_name']);
$objPHPExcel->getActiveSheet(0)->setCellValue('D' . ($i + 3), $OrdersData[$i]['create_time']);
$objPHPExcel->getActiveSheet(0)->setCellValue('E' . ($i + 3), $result[$i]['require_product']);
$objPHPExcel->getActiveSheet(0)->setCellValue('F' . ($i + 3), $result[$i]['require_count']);
$objPHPExcel->getActiveSheet(0)->setCellValue('G' . ($i + 3), $result[$i]['require_time']);
$objPHPExcel->getActiveSheet(0)->setCellValue('H' . ($i + 3), $result[$i]['product_bom_encoding']);
$objPHPExcel->getActiveSheet(0)->setCellValue('I' . ($i + 3), $result[$i]['delivery_time']);
$objPHPExcel->getActiveSheet(0)->setCellValue('J' . ($i + 3), $result[$i]['delivery_memo']);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($i + 3) . ':J' . ($i + 3))->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($i + 3) . ':J' . ($i + 3))->getBorders()
->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getRowDimension($i + 3)->setRowHeight(16);
}
------------------------------------------------------
//处理中文输出问题需要将字符串转化为UTF-8编码，才能正常输出，否则中文字符将输出为空白，如下处理：
$str = iconv('gb2312', 'utf-8', $str);
或者你可以写一个函数专门处理中文字符串：
function convertUTF8($str)
{
if(empty($str))
return '';
return iconv('gb2312', 'utf-8', $str);
}
//从数据库输出数据处理方式
从数据库读取数据如：
$db = new Mysql($dbconfig);
$sql = "SELECT * FROM 表名";
$row = $db->GetAll($sql); // $row 为二维数组
$count = count($row);
for ($i = 2; $i <= $count+1; $i++) {
$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, convertUTF8($row[$i-2][1]));
$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, convertUTF8($row[$i-2][2]));
$objPHPExcel->getActiveSheet()->setCellValue('C' . $i, convertUTF8($row[$i-2][3]));
$objPHPExcel->getActiveSheet()->setCellValue('D' . $i, convertUTF8($row[$i-2][4]));
$objPHPExcel->getActiveSheet()->setCellValue('E' . $i, convertUTF8(date("Y-m-d", $row[$i-2][5])));
$objPHPExcel->getActiveSheet()->setCellValue('F' . $i, convertUTF8($row[$i-2][6]));
$objPHPExcel->getActiveSheet()->setCellValue('G' . $i, convertUTF8($row[$i-2][7]));
$objPHPExcel->getActiveSheet()->setCellValue('H' . $i, convertUTF8($row[$i-2][8]));
}
在默认sheet后，创建一个worksheet
echo date('H:i:s') . " Create new Worksheet object\n";
$objPHPExcel->createSheet();
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
$objWriter-save('php://output');
------------------------------------------------------
保存excel—2007格式
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
//或者
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式
$objWriter->save("xxx.xlsx");
直接输出到浏览器
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
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="resume.xls"');
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');PhpExcel导出excel表格中长数字文本自动转为科学计数法的解决办法
方法一：前面加空格
$objPHPExcel->getActiveSheet(0)->setCellValue('E'.($i+2), ' '.$volist[$i]['num']);
方法二：【推荐】设置单元格为 文本格式
$objPHPExcel->getActiveSheet(0)->setCellValueExplicit('G'.($i+2), $volist[$i]['num'],PHPExcel_Cell_DataType::TYPE_STRING);
方法三：
$objPHPExcel->getActiveSheet(0)->setCellValue('F'.($i+2), $volist[$i]['num']);
$objPHPExcel->getActiveSheet(0)->getStyle('F'.($i+2))->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);本文为崔凯原创文章,转载无需和我联系,但请注明来自冷暖自知一抹茶ckhttp://www.cksite.cn
- 上一篇： 使用PHP-ExcelReader导入导出excel
- 下一篇： 使用PHPWord生成word文档
请先登录后发表评论
- 最新评论
- 总共0条评论
