Hướng dẫn phpspreadsheet
|
Tôi đã giới thiệu và hướng dẫn các bạn sử dụng thư viện PHPExcel tại địa chỉ http://gextend.net/threads/tao-bao-cao-excel-voi-thu-vien-phpexcel.17 để tạo các báo cáo Excel và bây giờ tôi tiếp tục hướng dẫn các bạn sử dụng thư viện mới nhất được chính nhà phát triển thư viện PHPExcel đề nghị tên là
PhpSpreadsheet. Đây là thư viện có chức năng tương tự như PHPExcel nhưng được cải tiến rất nhiều về kiến trúc mã nguồn cũng như cho hiệu năng tốt hơn. Thư viện PhpSpreadsheet hỗ trợ nhiều định dạng hơn PHPExcel. Các định dạng cho phép đọc: Các định dạng cho phép ghi: Thư viện PhpSpreadsheet yêu cầu PHP phiên bản 5.6+ và các thư viện php_zip, php_xml và php_gd2. Để sử dụng PhpSpreadsheet các bạn cần phải sử dụng công cụ composer. Các bạn tham khảo tại địa chỉ https://getcomposer.org. Để cài đặt, các bạn chạy lệnh composer sau: Code: Sau khi composer cài đặt thành công thư viện PhpSpreadsheet, các bạn có thể sử dụng thư viện như ví dụ sau: PHP: Các bạn có thể tham khảo thêm về thư viện PhpSpreadsheet tại địa chỉ https://github.com/PHPOffice/PhpSpreadsheet và https://phpspreadsheet.readthedocs.io. As a Drupal developer, you might have come with a situation where you have to integrate Drupal websites with other third-party system. So here third party systems provide data in a CSV or excel format that we have to show in the Drupal website. In this case, we have to import this data to one or more content type in Drupal. There are excel import modules available that will do the job. But in these modules, you can use for direct import to content types. But most of the cases you have to work on each row to clean the data and also various validations on each data and assign to various content type based on conditions in cell values. For these purpose you have to write your own custom code. So here I am going to explain how we can write our own excel import functionality in our custom module in Drupal 8. before starting project. Install php spreadsheet library using the below command in your project folder. composer require phpoffice/phpspreadsheet 1) Import với phpoffice/phpspreadsheetthis will place phpspreadsheet library in your vendors directory and adds below entry in your project composer.json. “phpoffice/phpspreadsheet”: “^1.8” You can see library in phpoffice folder inside your project vendors directory. Here I have content type called news and trying to upload below sheet into the news content. Now we are going to create form in our custom module to import csv file and create content programmatically using phpspreadsheet library. Here my custom module name is digitalnadeem_importexcel First, we are creating custom form to import CSV or excel sheet. For this, I have created a new entry in routing .yml file in my module. digitalnadeem_importexcel.import_excel:
path: '/admin/structure/digitalnadeem_importexcel/sheet/import'
defaults:
_title: 'Upload sheet'
_form: '\Drupal\digitalnadeem_importexcel\Form\ImportexcelForm'
requirements:
_permission: 'Import form'Next I have created a form ImportExcelForm.php in my module path. \src\Form\ImportexcelForm.php First, you have to include spread sheet library classes as shown below use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Style\Fill; use PhpOffice\PhpSpreadsheet\Cell\DataType; use PhpOffice\PhpSpreadsheet\Style\Alignment; use PhpOffice\PhpSpreadsheet\Style\Border; in buildForm function include the below code to create upload form. public function buildForm(array $form, FormStateInterface $form_state) {
$form = array(
'#attributes' => array('enctype' => 'multipart/form-data'),
);
$form['file_upload_details'] = array(
'#markup' => t('The File'),
);
$validators = array(
'file_validate_extensions' => array('csv'),
);
$form['excel_file'] = array(
'#type' => 'managed_file',
'#name' => 'excel_file',
'#title' => t('File *'),
'#size' => 20,
'#description' => t('Excel format only'),
'#upload_validators' => $validators,
'#upload_location' => 'public://content/excel_files/',
);
$form['actions']['#type'] = 'actions';
$form['actions']['submit'] = array(
'#type' => 'submit',
'#value' => $this->t('Save'),
'#button_type' => 'primary',
);
return $form;
}Below the upload location, you have to create in your project directory. ‘#upload_location’ => ‘public://content/excel_files/ In my case I have created in folder in below path. \sites\default\files\content\excel_files So uploaded files will be stored in this path for processing. As mentioned in form configuration I have provided csv format only allowed in validation. You can provide xlsx in an array if you are uploading a normal excel sheet. Also added below validation function. public function validateForm(array &$form, FormStateInterface $form_state) {
if ($form_state->getValue('excel_file') == NULL) {
$form_state->setErrorByName('excel_file', $this->t('upload proper File'));
}
}Now we are going to implement import functionality in our submit form handler. In submit first we are getting file name that uploaded. And generating path to file uploaded directory . $file = \Drupal::entityTypeManager()->getStorage('file')
->load($form_state->getValue('excel_file')[0]);
$full_path = $file->get('uri')->value;
$file_name = basename($full_path);Next we are using phpspreadsheet functions to get extract cell values from uploaded document. The below code will iterate through each row and cells and store values in an array say variable $rows. $inputFileName = \Drupal::service('file_system')->realpath('public://content/excel_files/'.$file_name);
$spreadsheet = IOFactory::load($inputFileName);
$sheetData = $spreadsheet->getActiveSheet();
$rows = array();
foreach ($sheetData->getRowIterator() as $row) {
//echo " |
