说明
如果有不能看明白的请留言,我会定时修改文档 类BlockExcel里的readFromExcel方法是分块读取 只需要分块儿的可以只看这个方法
情景
思路说明
首先读取第一行 因为第一行一般是表头。根据表头判断自己需要的列 类BlockExcel
中getExcelFirstTable
方法获取需要的列 记做:ExlData 不要试图获取总行数和总列数 这样的话相当于全文件读取 一样会溢出 根据开始行数和结束行数使用for循环读取行- 循环行 嵌套循环行和列(ExlData)得到数据 将数据合并为数组 记做:数组A 如果数组A为空 则这个文件读取完成 详细步骤说明会在代码里做标记注释
类与方法说明
类-ExcelFile-方法从这里开始执行
namespace App\ Console\ Commands ;
use App\ Exceptions\ BlockHandle ;
use App\ Exceptions\ SaveUploadFile ;
use App\ Exceptions\ SpreadExcelCsv ;
use App\ Models\ YyImportRecord ;
use App\ Models\ YySongImport ;
use Illuminate\ Console\ Command ;
use Illuminate\ Support\ Facades\ Log ;
class ExcelFile extends Command
{
protected $signature = 'excelfile' ;
protected $description = 'This is a auction excelfile process' ;
public function __construct ( )
{
parent :: __construct ( ) ;
}
public function handle ( )
{
Log :: info ( 'excelfile任务:' . date ( 'Y-m-d H:i:s' ) ) ;
$this -> readBlockExcel ( ) ;
}
private function readBlockExcel ( )
{
$item = YyImportRecord :: where ( [ 'is_implement' => 2 ] ) -> first ( ) ;
if ( ! $item ) {
$item = YyImportRecord :: where ( [ 'is_implement' => 0 ] ) -> first ( ) ;
}
if ( $item ) {
$keyname = 'YyImportRecord_' . $item -> file_url_id ;
$rowSize = 18000 ;
$endRowPre = cache ( ) -> store ( 'redis' ) -> get ( $keyname ) ;
if ( $endRowPre ) {
$endRowPre = intval ( $endRowPre ) ;
$startRow = $endRowPre + 1 ;
$endRow = $endRowPre + $rowSize ;
} else {
$startRow = 2 ;
$endRow = $rowSize ;
}
BlockHandle :: readExcel ( $item , $startRow , $endRow ) ;
}
unset ( $item ) ;
}
类-BlockHandle-在这里做数据判断
namespace App\ Exceptions ;
use App\ Exceptions\ BlockExcel ;
use App\ Models\ YyFileUrl ;
use App\ Models\ YyImportRecord ;
use App\ Models\ YySongImport ;
use Illuminate\ Support\ Facades\ Log ;
use PhpOffice\ PhpSpreadsheet\ IOFactory ;
class BlockHandle {
public static function readExcel ( $item , $startRow , $endRow ) {
$str_msg = '季度报表信息' ;
$file_url_id = intval ( $item -> file_url_id ) ;
$info = YyFileUrl :: find ( $file_url_id ) ;
if ( empty ( $info ) ) {
$error_str = $str_msg . '录入:' . "文件信息不存在" ;
SaveUploadFile :: message ( $error_str ) ;
exit ( ) ;
}
$file_name = env ( 'COMMON_FILE_ADDRESS' ) . $info -> file_url ;
if ( ! file_exists ( $file_name ) )
{
$error_str = $str_msg . '录入:' . "文件" . $file_name . "不存在" ;
SaveUploadFile :: message ( $error_str ) ;
exit ( ) ;
}
$exl = new BlockExcel ( ) ;
$kyyname = 'TABLE_YyImportRecord_HEAD_' . $file_url_id ;
if ( $startRow == 2 ) {
$tableHead = $exl -> getExcelFirstTable ( $file_name , 1 , 1 ) ;
cache ( ) -> store ( 'redis' ) -> set ( $kyyname , $tableHead ) ;
} else {
$tableHead = cache ( ) -> store ( 'redis' ) -> get ( $kyyname ) ;
if ( ! $tableHead ) {
$tableHead = $exl -> getExcelFirstTable ( $file_name , 1 , 1 ) ;
cache ( ) -> store ( 'redis' ) -> set ( $kyyname , $tableHead ) ;
}
}
$excel_orders = $exl -> readFromExcel ( $file_name , $tableHead , $startRow , $endRow ) ;
if ( empty ( $excel_orders ) ) {
YyImportRecord :: where ( [ 'id' => $item -> id ] ) -> update ( [ 'is_implement' => 1 ] ) ;
SaveUploadFile :: message ( $str_msg . '录入完成 ' ) ;
} else {
if ( $startRow == 2 ) {
YyImportRecord :: where ( [ 'id' => $item -> id ] ) -> update ( [ 'is_implement' => 2 ] ) ;
SaveUploadFile :: message ( $str_msg . '录入中... ' ) ;
}
$keyname = 'YyImportRecord_' . $file_url_id ;
cache ( ) -> store ( 'redis' ) -> set ( $keyname , $endRow ) ;
}
}
}
类-BlockExcel-在这里分块(分页读取)
namespace App\ Exceptions ;
use App\ Models\ YySongCompany ;
use Illuminate\ Support\ Facades\ Log ;
use PHPExcel_IOFactory ;
class BlockExcel {
public function readFromExcel ( $excelFile , $tableHead , $startRow = 1 , $endRow = 100 ) {
$str_msg = '季度报表信息' ;
$ExlData = $tableHead [ 'ExlData' ] ;
$columns = $tableHead [ 'columns' ] ;
Log :: info ( '开始行:' . $startRow . '--结束行:' . $endRow ) ;
$excelType = PHPExcel_IOFactory :: identify ( $excelFile ) ;
$excelReader = \ PHPExcel_IOFactory:: createReader ( $excelType ) ;
if ( strtoupper ( $excelType ) == 'CSV' ) {
$excelReader -> setInputEncoding ( 'GBK' ) ;
}
if ( $startRow && $endRow ) {
$excelFilter = new PHPExcelReadFilter ( ) ;
$excelFilter -> startRow = $startRow ;
$excelFilter -> endRow = $endRow ;
$excelReader -> setReadFilter ( $excelFilter ) ;
}
$phpexcel = $excelReader -> load ( $excelFile ) ;
$activeSheet = $phpexcel -> getActiveSheet ( ) ;
$params = array ( ) ;
for ( $row = $startRow ; $row <= $endRow ; $row ++ ) {
$param = array ( ) ;
foreach ( $ExlData as $letter => $key ) {
$value = $activeSheet -> getCellByColumnAndRow ( $letter , $row ) -> getValue ( ) ;
$param [ ] = $value ;
}
if ( isset ( $param [ 0 ] ) && $param [ 0 ] ) {
$params [ ] = $param ;
}
}
if ( ! empty ( $params ) ) {
$result = SaveUploadFile :: addData ( $params , $columns ) ;
if ( ! $result ) {
}
}
$phpexcel -> disconnectWorksheets ( ) ;
return $params ;
}
public function getExcelFirstTable ( $excelFile , $startRow = 1 , $endRow = 1 , $highestColumnIndex = 18 ) {
$excelType = PHPExcel_IOFactory :: identify ( $excelFile ) ;
$excelReader = \ PHPExcel_IOFactory:: createReader ( $excelType ) ;
if ( strtoupper ( $excelType ) == 'CSV' ) {
$excelReader -> setInputEncoding ( 'GBK' ) ;
}
if ( $startRow && $endRow ) {
$excelFilter = new PHPExcelReadFilter ( ) ;
$excelFilter -> startRow = $startRow ;
$excelFilter -> endRow = $endRow ;
$excelReader -> setReadFilter ( $excelFilter ) ;
}
$phpexcel = $excelReader -> load ( $excelFile ) ;
$activeSheet = $phpexcel -> getActiveSheet ( ) ;
$nameArr = SaveUploadFile :: getColumsTitle ( ) ;
$nameArrKey = array_flip ( $nameArr ) ;
$ExlData = array ( ) ;
$columns = array ( ) ;
$rowd = 1 ;
for ( $col = 0 ; $col < $highestColumnIndex ; $col ++ ) {
$title = ( string ) $activeSheet -> getCellByColumnAndRow ( $col , $rowd ) -> getValue ( ) ;
if ( $title && in_array ( $title , $nameArr ) ) {
$name = $nameArrKey [ $title ] ;
$ExlData [ $col ] = $name ;
$columns [ ] = $name ;
}
}
$phpexcel -> disconnectWorksheets ( ) ;
$result = array (
'ExlData' => $ExlData ,
'columns' => $columns ,
) ;
return $result ;
}
}
类-SaveUploadFile-方便读代码给贴出
namespace App\ Exceptions ;
use App\ Models\ YyDatum ;
use App\ Models\ YyFileUrl ;
use Illuminate\ Support\ Facades\ DB ;
use Illuminate\ Support\ Facades\ Log ;
class SaveUploadFile
{
public static function getColumsTitle ( ) {
$data = array (
'seted' => '结算期间' ,
'plorm' => '平台' ,
'qreee' => '份额' ,
'adare' => '接权' ,
'sonus' => '付费状态' ,
'enuag' => '收入分成-使用量' ,
'baage' => '包月收入分成-使用量' ,
'rffue' => '打榜收入' ,
'aunre' => '广告收入分成' ,
'baare' => '包月收入分成' ,
'recfg' => '打榜收入分成' ,
'cbome' => 'CP分成收入' ,
) ;
return $data ;
}
public static function headExcel ( ) {
$arr = 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' ) ;
return $arr ;
}
public static function addData ( $params , $columns ) {
DB :: beginTransaction ( ) ;
try {
$datumInstance = new YyDatum ;
$batchSize = count ( $params ) ;
$collect = collect ( $params ) ;
foreach ( $collect -> chunk ( $batchSize ) as $chunk ) {
$chunk_arr = json_decode ( json_encode ( $chunk ) , true ) ;
\ batch( ) -> insert ( $datumInstance , $columns , $chunk_arr , $batchSize ) ;
}
DB :: commit ( ) ;
return true ;
} catch ( \ Exception $e ) {
Log :: info ( '错误信息-addData:' . print_r ( $e -> getMessage ( ) , true ) ) ;
DB :: rollBack ( ) ;
return false ;
}
}
}