使用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,如图所示

冷暖自知一抹茶ck

        三、编写核心代码。

        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);




冷暖自知一抹茶ck
请先登录后发表评论
  • 最新评论
  • 总共0条评论