Hướng dẫn import excel và export excel trong Drupal, Laravel với phpoffice/phpspreadsheet

Hướng dẫn import excel và export excel trong Drupal, Laravel với phpoffice/phpspreadsheet

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/phpspreadsheet

this 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.

excel

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('<b>The File</b>'),
    );
    
    $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 "<pre>";print_r($row);exit;
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(FALSE);
            $cells = [];
            foreach ($cellIterator as $cell) {
                $cells[] = $cell->getValue();
                    

            }
           $rows[] = $cells;
           
        }

Here out put of $rows array will be as shown below.

Array
(
    [0] => Array
        (
            [0] => Title
            [1] => Body text
        )

    [1] => Array
        (
            [0] => news1
            [1] => Baker1
        )

    [2] => Array
        (
            [0] => news2
            [1] => Baker2
        )

    [3] => Array
        (
            [0] => news3
            [1] => Baker3
        )

)

Here I used below sheet to import.

excel

Here first row is header row . this we don’t want to import to the news content .So for removing first row use below array_shift function.

array_shift($rows);

Next we are iterating through $rows array to insert rows to news content.

So first we will check wether node exists by comparing title fields.

Se below code to create and update node from excel data.

foreach($rows as $row){
            $values = \Drupal::entityQuery('node')->condition('title', $row[0])->execute();
            $node_not_exists = empty($values);
            if($node_not_exists){
                /*if node does not exist create new node*/
                $node = \Drupal::entityTypeManager()->getStorage('node')->create([
                  'type'       => 'news', //===here news is the content type mechine name
                  'title'      => $row[0],
                  'body'       => 'body content updated'
                ]);
                $node->save();
            }else{
                /*if node exist update the node*/
                $nid = reset($values);
                
                $node = \Drupal\node\Entity\Node::load($nid);
                $node->setTitle($row[0]);
                $node->set("body", $row[1]);
                //$node->set("field_name", 'New value');
                $node->save();
            }
        }
        
        \Drupal::messenger()->addMessage('imported successfully');

Now you are done with the implementation. Flush the cache you can see below form while accessing URL – http://your-site.com/admin/structure/digitalnadeem_importexcel/sheet/import

upload-form excel

succes-form

See imported contents in the admin content page.

content-imported

2) Export với phpoffice/phpspreadsheet

// Create new Spreadsheet object
  $spreadsheet = new Spreadsheet();
  $sheet = $spreadsheet->getActiveSheet();
// Set document properties
    $spreadsheet->getProperties()->setCreator('miraimedia.co.th')
      ->setLastModifiedBy('Cholcool')
      ->setTitle('how to export data to excel use phpspreadsheet in codeigniter')
      ->setSubject('Generate Excel use PhpSpreadsheet in CodeIgniter')
      ->setDescription('Export data to Excel Work for me!');
// add style to the header
    $styleArray = array(
      'font' => array(
        'bold' => true,
      ),
      'alignment' => array(
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
      ),
      'borders' => array(
          'bottom' => array(
              'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
              'color' => array('rgb' => '333333'),
          ),
      ),
      'fill' => array(
        'type'       => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
        'rotation'   => 90,
        'startcolor' => array('rgb' => '0d0d0d'),
        'endColor'   => array('rgb' => 'f2f2f2'),
      ),
    );
    $spreadsheet->getActiveSheet()->getStyle('A1:G1')->applyFromArray($styleArray);
    // auto fit column to content
foreach(range('A', 'G') as $columnID) {
      $spreadsheet->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
    }
// set the names of header cells
      $sheet->setCellValue('A1', 'ID');
      $sheet->setCellValue('B1', 'Name');
      $sheet->setCellValue('C1', 'Age');
      $sheet->setCellValue('D1', 'Sex');
      $sheet->setCellValue('E1', 'Address');
      $sheet->setCellValue('F1', 'Phone');
      $sheet->setCellValue('G1', 'Email');
$getdata = $this->welcome_message->get_sample();
    // Add some data
    $x = 2;
    foreach($getdata as $get){
        $sheet->setCellValue('A'.$x, $get->id);
        $sheet->setCellValue('B'.$x, $get->the_name);
        $sheet->setCellValue('C'.$x, $get->the_age);
        $sheet->setCellValue('D'.$x, $get->the_sex);
        $sheet->setCellValue('E'.$x, $get->the_address);
        $sheet->setCellValue('F'.$x, $get->the_phone);
        $sheet->setCellValue('G'.$x, $get->the_email);
      $x++;
    }

$filename = 'test';
$writer = new Xlsx($spreadsheet);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'. $filename .'.xlsx"');
header('Cache-Control: max-age=0');
$writer->save('php://output'); 
//End Function index
 }
//End Class Welcome

Conclusion

Here I explained how we can import a single excel/csv sheet to Drupal. Most of the third party system can have API ‘s which can place excel/csv data sheet in a directory in a regular interval of time. In that  case,  follow the same approach that I explained above and a create crone job to take the file from a specific location. So cron job will insert content from excel in regular intervals. This way you can automate the whole process.

Bạn thấy bài viết này như thế nào?: 
Average: 6.7 (13 votes)
Ảnh của Tommy Tran

Tommy owner Express Magazine

Drupal Developer having 9+ year experience, implementation and having strong knowledge of technical specifications, workflow development. Ability to perform effectively and efficiently in team and individually. Always enthusiastic and interseted to study new technologies

  • Skype ID: tthanhthuy

Bình luận (0)

 

Add Comment

Filtered HTML

  • Các địa chỉ web và email sẽ tự động được chuyển sang dạng liên kết.
  • Các thẻ HTML được chấp nhận: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Tự động ngắt dòng và đoạn văn.

Plain text

  • No HTML tags allowed.
  • Các địa chỉ web và email sẽ tự động được chuyển sang dạng liên kết.
  • Tự động ngắt dòng và đoạn văn.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.

Advertisement

 

jobsora

Dich vu khu trung tphcm

Dich vu diet chuot tphcm

Dich vu diet con trung

Quảng Cáo Bài Viết

 
Modules Migrate và Migrate D2D cho lập trình viên Drupal năm 2015

Modules Migrate và Migrate D2D cho lập trình viên Drupal năm 2015

This unorthodox decision was made to support substantial improvements in Drupal’s major version upgrade process

Máy tính bảng giá rẻ Kindle Fire cũng bị phàn nàn

Máy tính bảng giá rẻ Kindle Fire cũng bị phàn nàn

Mặc dù Kindle Fire đang bán khá chạy và có thể sẽ có một bản cập nhật phần cứng vào đầu năm 2012

Phần 2: Data Migration từ HTML Pages vào Drupal 7

Phần 2: Data Migration từ HTML Pages vào Drupal 7

This gives you access to the full Drupal 7 API, and in the case of data migration, easily allows you to programmatically create nodes.

Công ty diệt chuột T&C

 

Diet con trung