【PHPOffice/PhpSpreadsheet的基本使用】 PHPOffice/PhpSpreadsheet:?[https://github.com/PHPOffice/PhpSpreadsheet](https://github.com/PHPOffice/PhpSpreadsheet) document:?[https://phpspreadsheet.readthedocs.io/en/latest/](https://phpspreadsheet.readthedocs.io/en/latest/) ``` composer require phpoffice/phpspreadsheet ``` 1.1.Hello World ``` <?php require 'vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $sheet->setCellValue('A1', 'Hello World !'); $writer = new Xlsx($spreadsheet); $writer->save('hello world.xlsx'); ``` 1.2.上傳excel輸出整段php array form.php ``` <form method="post" enctype="multipart/form-data" action="readfile.php"> <div class="form-group"> <label for="exampleInputFile">File Upload</label> <input type="file" name="file" class="form-control" id="exampleInputFile"> </div> <button type="submit" class="btn btn-primary">Submit</button> </form> ``` readfile.php ``` <?php require 'vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Reader\Csv; use PhpOffice\PhpSpreadsheet\Reader\Xlsx; $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']); // start customize $sheetData = $spreadsheet->getActiveSheet()->ToArray(); echo '<pre>'; print_r($sheetData); // /end customize } ``` 遍歷所有內容： ``` // start customize $worksheet = $spreadsheet->getActiveSheet(); echo '<table>' . PHP_EOL; foreach ($worksheet->getRowIterator() as $row) { echo '<tr>' . PHP_EOL; $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells, // even if a cell value is not set. // By default, only cells that have a value // set will be iterated. foreach ($cellIterator as $cell) { echo '<td>' . $cell->getValue() . '</td>' . PHP_EOL; } echo '</tr>' . PHP_EOL; } echo '</table>' . PHP_EOL; // /end customize ``` 指定位置顯示內容： ``` // start customize $sheetData = $spreadsheet->getActiveSheet()->rangeToArray( 'C3:E5', // The worksheet range that we want to retrieve NULL, // Value that should be returned for empty cells TRUE, // Should formulas be calculated (the equivalent of getCalculatedValue() for each cell) TRUE, // Should values be formatted (the equivalent of getFormattedValue() for each cell) TRUE // Should the array be indexed by cell row and cell column ); // /end customize ``` 指定Excel列放入database： ``` // start customize $sheetData = $spreadsheet->getActiveSheet()->ToArray(); $connect = mysqli_connect("localhost", "root", "root", "test"); if(is_array($sheetData)) { foreach($sheetData as $row => $value) { $data1 = mysqli_real_escape_string($connect, $value[0]); $data2 = mysqli_real_escape_string($connect, $value[1]); $data3 = mysqli_real_escape_string($connect, $value[3]); $sql = "INSERT INTO demo(list1, list2, list3) VALUES ('".$data1."', '".$data2."', '".$data3."')"; mysqli_query($connect, $sql); } echo 'success'; } // /end customize ``` 將Excel指定內容用json格式輸出：