Php Spreadsheet導入導出

4352 ワード

Php Spreadsheet
使用前に、iPhone Spreadsheetにcompserを導入します.
composer require phpoffice/phpspreadsheet
方法:
  • 回転配列
  • を導入する.
  • は、ファイル
  • として導出される.
  • ファイルを導入してデータベース
  • に預け入れます.
    getActiveSheet();
    
            $highestRow = $worksheet->getHighestRow(); //    
            $highestColumn = $worksheet->getHighestColumn(); //    
    
            $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);
    
            $data = [];
            for ($row = 2; $row <= $highestRow; ++$row) { //       
                $row_data = [];
                for ($column = 1; $column <= $highestColumnIndex; $column++) {
                    $row_data[] = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
                }
                $data[] = $row_data;
            }
    
            return $data;
        }
    
        /**
         * Array     
         * @param array $title      ['title1', 'title2']
         * @param array $data      ['A1' => '***', 'B2' => '***']
         * @param string $fileName       
         * @param array $options    [ 'fileType' => 'xls',              xls
         *                             'savePath' => '/web'           
         *                           ]
         * @return bool
         * @throws \PhpOffice\PhpSpreadsheet\Exception
         * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
         */
        public static function toFile($title, $data, $fileName, $options = [])
        {
    
            /**        */
            if (isset($options['fileType']) && !in_array($options['fileType'], ['xls', 'xlsx', 'ods', 'csv', 'html', 'tcpdf', 'dompdf', 'mpdf'])) {
                return false;
            }
    
            $spreadsheet = new Spreadsheet();
    
            $worksheet = $spreadsheet->getActiveSheet();
            //      
            $worksheet->setTitle($fileName);
    
            foreach ($title as $key => $value) {
                $worksheet->setCellValueByColumnAndRow($key + 1, 1, $value);
            }
    
            $row = 2; //     
            foreach ($data as $item) {
                $column = 1;
                foreach ($item as $value) {
                    $worksheet->setCellValueByColumnAndRow($column, $row, $value);
                    $column++;
                }
                $row++;
            }
    
            /**         */
            if (!isset($options['fileType'])) {
                $fileName = $fileName . '.xls';
            } else {
                $fileName = $fileName . '.' . $options['fileType'];
            }
    
            /**        */
            if (!isset($options['savePath'])) {
                $savePath = \Yii::$app->basePath . '/web/' . $fileName;
            } else {
                $savePath = $options['savePath'] . $fileName;
            }
    
            /** @var string $writerType    PhpSpreadsheet       */
            $writerType = ucfirst($options['fileType']);
    
            $writer = IOFactory::createWriter($spreadsheet, $writerType);
    
            $writer->save($savePath);
    
            /*      */
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
            ob_end_flush();
    
            return true;
        }
    
        /**
         *          
         * @param string $tableName   
         * @param array $filed     [ 'create_id', 'create_time' ]
         * @param string $filePath     
         * @return bool|int
         * @throws \PhpOffice\PhpSpreadsheet\Exception
         * @throws \yii\db\Exception
         */
        public static function toSql($tableName, $filed, $filePath)
        {
            $params = self::toArray($filePath);
    
            $result = \Yii::$app->db->createCommand()->batchInsert($tableName, $filed, $params)->execute();
    
            return $result;
        }
    }