标签归档:excel

PHP Laravel Excel

PHP Laravel Excel官方网址:http://www.maatwebsite.nl/laravel-excel/docs;

GitHub: https://github.com/Maatwebsite/Laravel-Excel;
Packagist: https://packagist.org/packages/maatwebsite/excel

这个程序包依赖PHPOffice/PHPExcel,实际就是在这个工具包上做了一些易于用户操作的封装。需要说明的是,目录依赖的PHPOffice/PHPExcel版本是1.8.*(也是当前的稳定版),在1.9.*和2.0.*中开始引入PHP的命名空间,意味着PHP版本至少要5.3以上,这两个分支还在开发中,看起来这个包的作者非常的保守。

安装就按照Laravel套路来就好:

#往composer.json中添加"maatwebsite/excel": "~2.1.0",然后update

#添加ServiceProvider
vi config/app.php
'Maatwebsite\Excel\ExcelServiceProvider',

#添加Facade(可选)
'Excel' => 'Maatwebsite\Excel\Facades\Excel',

#配置(会添加excel.php配置文件)
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

#获取excel实例
$excel = App::make('excel');

Maatwebsite/excel本身有一个默认的配置文件,如果应用级别有配置文件,那么应用配置将覆盖默认配置配置,具体实现是在ExcelServiceProvider中:

    public function boot()
    {
        $this->publishes([
            __DIR__ . '/../../config/excel.php' => config_path('excel.php'),
        ]);

        $this->mergeConfigFrom(
            __DIR__ . '/../../config/excel.php', 'excel'
        );

        //Set the autosizing settings
        $this->setAutoSizingSettings();
    }

表格操作,主要涉及输入和输出。

Excel::load('file.xls', function($reader) {

    // Getting all results
    $results = $reader->get();

    // ->all() is a wrapper for ->get() and will work the same
    $results = $reader->all();

});

使用get和all方法获取结果,默认,如果表格只有一个sheet,那么直接返回行集合,如果有多个sheet,那么返回sheet的集合,每个sheet又是行的集合。为了统一操作,可以设置配置文件中的force_sheets_collection设置为true,这样都会返回sheet的集合。

表格sheet的第一行是头部,默认会被转换成slug(别名),可以设置import.heading为false表示不使用文件头,可用值true|false|slugged|slugged_with_count|ascii|numeric|hashed|trans|original,设置为original表示使用字面值作为key,这个比较常见。

Sheet/行/单元格都是集合,使用了get()之后,就可以使用集合的方法。

#
$reader->get()->groupBy('firstname');

#依赖force_sheets_collection,可能返回第一个sheet或第一个行
$reader->first();
// Get workbook title
$workbookTitle = $reader->getTitle();

foreach($reader as $sheet)
{
    // get sheet title
    $sheetTitle = $sheet->getTitle();
}

// You can either use ->take()
$reader->take(10);

// Or ->limit()
$reader->limit(10);

// Skip 10 results
$reader->skip(10);

// Skip 10 results with limit, but return all other rows
$reader->limit(false, 10);

// Skip and take
$reader->skip(10)->take(10);

// Limit with skip and take
$reader->($skip, $take);

$reader->toArray();

$reader->toObject();

// Dump the results
$reader->dump();

// Dump results and die
$reader->dd();

#也可以使用foreach
// Loop through all sheets
$reader->each(function($sheet) {

    // Loop through all rows
    $sheet->each(function($row) {

    });

});

选择Sheet和列

#仅加载sheet1
Excel::selectSheets('sheet1')->load("xx.xls", function($reader) {});
Excel::selectSheets('sheet1', 'sheet2')->load();

#通过下标选择比较靠谱
// First sheet
Excel::selectSheetsByIndex(0)->load();

// First and second sheet
Excel::selectSheetsByIndex(0, 1)->load();

#选择列,很熟悉的用法?
#All get methods (like all(), first(), dump(), toArray(), ...) accept an array of columns.
// Select
$reader->select(array('firstname', 'lastname'))->get();

// Or
$reader->get(array('firstname', 'lastname'));

日期:
By default the dates will be parsed as a Carbon object.

分批导入:

Excel::filter('chunk')->load('file.csv')->chunk(250, function($results)
{
        foreach($results as $row)
        {
            // do stuff
        }
});

每次读入250行,处理完毕在导入250行??

批量导入:

Excel::batch('app/storage/uploads', function($rows, $file) {

    // Explain the reader how it should interpret each row,
    // for every file inside the batch
    $rows->each(function($row) {

        // Example: dump the firstname
        dd($row->firstname);

    });

});

$files = array(
    'file1.xls',
    'file2.xls'
);

Excel::batch($files, function($rows, $file) {

});

Excel::batch('app/storage/uploads', function($sheets, $file) {

    $sheets->each(function($sheet) {

    });

});

导出也有很多定制化的操作,参考:http://www.maatwebsite.nl/laravel-excel/docs/export

例子:

// 关联数组,输出表头
$excel_array = [
    [
        "表头1" => "xxxx",
        "表头2" => "yyyy"
    ],
    [
        "表头1" => "xxxx2",
        "表头2" => "yyyy3"
    ]
];
// 直接数据输入
$excel_array2 = [
    [
        "表头1", "表头2"
    ],
    [
        "xxxx", "yyyy"
    ],
    [
        "xxxx2", "yyyy3"
    ]
];

        \Excel::create("test1", function ($excel) use($excel_array) {
            $excel->sheet('sheet1', function ($sheet) use($excel_array) {
                $sheet->fromArray($excel_array);
            });
        })->store("xls","d:/");
        
        \Excel::create("test2", function ($excel) use($excel_array2) {
            $excel->sheet('sheet1', function ($sheet) use($excel_array2) {
                $sheet->fromArray($excel_array2, null, 'A1', false, false);
            });
        })->save("xls");

默认,如果不指定导入的路径,会保存到storage_path(‘exports’),即app/storage/exports。可以修改配置文件export.store.path的值。

导出的sheet,默认第一行总是头部,这个可以修改配置文件的export.generate_heading_by_indices为false取消这个默认值。也可以指定fromArray的第5参数为false达到同样目的。

store()的第一参数是导入文件的类型,第二参数是路径(不需要包含文件名),第三参数控制是否返回保存文件的信息(比如保存的路径,扩展名等)。

PHPExcel 之 大表格导入

PHPExcel是PHP中用来处理Excel表格最流行的库,不过在导入大表格时,耗时比较长。以下测试程序:

<?php
///
$root_dir = realpath(__DIR__.'/..');
set_include_path(implode(PATH_SEPARATOR, array(
    $root_dir . '/library',
    get_include_path(),
)));

if(file_exists($root_dir.'/vendor/autoload.php')){
    $loader = include $root_dir.'/vendor/autoload.php';
}else{
    exit("Autoload Failed. ");
}
$loader->setUseIncludePath(true);

///
require_once realpath($root_dir. '/library/PHPExcel/Classes/PHPExcel.php');

///
$params = array(
    'host' => '127.0.0.1',
    'username' =>'root',
    'password' =>'',
    'dbname' =>'test',
    'charset' =>'utf8'
);
$db = Zend_Db::factory('MYSQLI', $params);
Zend_Db_Table_Abstract::setDefaultAdapter($db);

///
header("Content-type:text/html;charset=utf-8");

///
ini_set("display_errors","1");
ini_set("max_execution_time","7200");
ini_set("memory_limit","512M");

///
///导入任务是否完成
$today = date("Y-m-d");
$has = $db->fetchRow("SELECT * FROM job WHERE job_date='".$today."' ");
if(empty($has)) {
    $db->insert("job", array("job_date"=>$today));
}else{
    $job_product = (int)$has['job_product'];
    if($job_product > 0){
        exit;
    }
}

///
///预定文件是否存在
$file = $root_dir."/jobs/data/".$today."/product";
if(file_exists($file.".xls")){
    $file = $file.".xls";
}else if(file_exists($file.".xlsx")) {
    $file = $file.".xlsx";
} else {
    exit;
}

///
///导入产品表
try {
    $s = microtime(true);
    
    $objPHPExcel = PHPExcel_IOFactory::load($file);

    $currentSheet = $objPHPExcel->getSheet(0);
    $highestRow = $currentSheet->getHighestRow();
    $highestColumm = $currentSheet->getHighestColumn();
    $highestColumm= PHPExcel_Cell::columnIndexFromString($highestColumm);
    
    //读取表头
    $header = array(
        'A'=>'SKU',
        'B'=>'产品名称',
        'F'=>'采购单价',
        'I'=>'仓库名称',
        'O'=>'供应商名称',
        'Q'=>'采购链接',
        'AF'=>'采购员'
    );
    
    // 列是从0开始的,行是以1开始的  验证表头
    $validHeader = array();
    for ($column = 0; $column < $highestColumm; $column++) {
        $columnName = PHPExcel_Cell::stringFromColumnIndex($column);
        $cellValue = $currentSheet->getCellByColumnAndRow($column, 1)->getValue();
        $validHeader[strtoupper($columnName)] = trim($cellValue);
    }
    foreach($header as $hi=>$hv) {
        if(!isset($validHeader[$hi]) || ($validHeader[$hi] !== $hv)) {
            echo '表格格式错误......';
            exit;
        }
    }
    
    $e = microtime(true);
    echo "Read Excel:".(float)($e-$s)."\n";
    
    //清空表格
    $db->query("TRUNCATE TABLE product");
    
    //开始导入
    $db->update('job', array('job_product'=>1), "job_date='".$today."'");
   
    //使用原生API批量插入 
    $mysqli = $db->getConnection();

    // 循环读取每个单元格的数据  计算填充
    for ($row = 2; $row <= $highestRow; $row++) {
        $rd = array();
        $sku = '';
        foreach($header as $key=>$vvvv) {
            $column = PHPExcel_Cell::columnIndexFromString($key)-1;
            if($key === 'A') {
                $table_key = 'sku';
                $sku = trim($currentSheet->getCellByColumnAndRow($column, $row)->getValue());
            } else if($key === 'B') {
                $table_key = 'name';
            } else if($key === 'F') {
                $table_key = 'purchase_price';
                $rd[$table_key] = round(trim($currentSheet->getCellByColumnAndRow($column, $row)->getValue()),2);
                continue;
            }else if($key === 'I') {
                $table_key = 'warehouse_name';
            }else if($key === 'O') {
                $table_key = 'supplier_name';
            }else if($key === 'Q') {
                $table_key = 'purchase_link';
            }else if($key === 'AF') {
                $table_key = 'who_purchase';
            }
            
	    $rd[$table_key] = $mysqli->real_escape_string(trim($currentSheet->getCellByColumnAndRow($column, $row)->getValue()));
            
        }
        if(empty($sku)) {
            continue;
        }
        $valuess[$sku] = "('".$rd['sku']."', '".$rd['name']."', ".$rd['purchase_price'].", '".$rd['warehouse_name']."', '".$rd['supplier_name']."', '".$rd['purchase_link']."', '".$rd['who_purchase']."')";
    }
    
    $ee = microtime(true);
    echo "Get Excel Rows:".(float)($ee-$e)."\n";
    
    //批量插入
    $chunks = array_chunk($valuess, 5000);
    foreach($chunks as $chunk) {
	   $mysqli->query("INSERT INTO product (`sku`, `name`, `purchase_price`, `warehouse_name`, `supplier_name`, `purchase_link`, `who_purchase`) values".implode(",", $chunk));
    }
    
    $eee = microtime(true);
    echo "Insert to Db:".(float)($eee-$ee)."\n";

    //完成导入或更新
    $db->update('job', array('job_product'=>2), "job_date='".$today."'");
} catch (Exception $e) {
    $db->update('job', array('job_stock'=>0), "job_date='".$today."'");
}

输出:
php import_products.php
Read Excel:49.7071270942688
Get Excel Rows:3.034592866897583
Insert to Db:0.4471859931945801
这个输出是CentOS 6.5,1G内存下的输出,如果放在Window下,Read Excel是以上数据的6倍。实际的Excel有多大呢,大概是9兆,1万5千数据,所以这个Excel读入确实差强人意,如果表格再大,PHP估计就要崩溃了(需要调大PHP每个进程所能用的内存数)。

为何读入表格那么耗时,我想大概是因为需要在内存中建立一个完整的映射,类似于浏览器解析HTML建立一个DOM数的过程一样,浏览器在遇到大数据时(比如服务端返回10M HTML代码),那么它也常常出现僵死的情况…

不过用Office Excel打开看起来就不会那么耗时。