엑셀 파일 읽어와서 mysql db 올리기

엑셀 파일 읽어와서 mysql db 올리기

require_once 'vendor/autoload.php' ;

require_once 'config.php' ;

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Reader\Csv;

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

if ( isset ( $_POST [ 'submit' ])) {

$file_mimes = array ( 'text/x-comma-separated-values' , 'text/comma-separated-values' , 'application/octet-stream' , 'application/vnd.ms-excel' , 'application/x-csv' , 'text/x-csv' , 'text/csv' , 'application/csv' , 'application/excel' , 'application/vnd.msexcel' , 'text/plain' , 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );

if ( isset ( $_FILES [ 'file' ][ 'name' ]) & & in_array( $_FILES [ 'file' ][ 'type' ], $file_mimes )) {

$arr_file = explode ( '.' , $_FILES [ 'file' ][ 'name' ]);

$extension = end( $arr_file );

if ( 'csv' = = $extension ) {

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();

} else {

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();

}

$spreadsheet = $reader - > load( $_FILES [ 'file' ][ 'tmp_name' ]);

$sheetData = $spreadsheet - > getActiveSheet() - > toArray();

if ( ! empty ( $sheetData )) {

for ( $i = 1 ; $i < count ( $sheetData ); $i + + ) { //skipping first row

$name = $sheetData [ $i ][ 0 ];

$email = $sheetData [ $i ][ 1 ];

$company = $sheetData [ $i ][ 2 ];

$db - > query( "INSERT INTO USERS(name, email, company) VALUES('$name', '$email', '$company')" );

}

}

echo "Records inserted successfully." ;

} else {

echo "Upload only CSV or Excel file." ;

}

}

from http://beaveryun.tistory.com/4 by ccl(A) rewrite - 2021-12-06 08:27:35