Excel (CSV) managed Drupal site

Rating

There are two cases where you manage content on a Drupal site not with the Drupal interface.

1) You have created a website, but you leave the management of content to non-Drupallers.

2) You have large amounts of nodes to enter. Think of product stock lists which are updated daily.

In the first case, it is my experience that even saving an Excel file to a CSV UTF-8, for some, is a threshold. I have created an interface for computer dummies, independently of any Drupal knowledge.

In the second case, you may use this interface, with the difference that you know what happens behind the scenes and if necessary can make the necessary changes. Especially with large amounts of nodes this is a handy interface.

The principle behind this method is, of course, CVS, combined with Feeds Tamper. They work through a CSV file (best UTF-8 for European languages). Images must be present before in a folder on the server. However, they need not be known in the Drupal system (no ID required), when used in the appropriate reference Feeds (UUID). You must ,in your CSV file, specify the path to the image. 

I this example i use the well known " Article " content typeThere is a text field, an image field (I made it multiple) and a multiple label field.

Look at the configuration. Pay attention to the details. Full HTML to text, Auto Create the labels, pictures replace the images ... The title should be unique ...

Screen001655.png

Feeds with Tamper are configured like this.

For the text you make it possible as HTML entity (<b> </ br> <I> ...). For the images I first divide the multiple field (explode) whenever there is a comma. Possible spaces I delete (Trim). Finally, I'm going to replace  a  # (I use here #, matter of agreement) to convert the path of the picture folder. The user need to write only the name and extension to complete the file. The Excel file will show an image name, preceded by a hashtag (#).

Screen001656.png

A detail of the "Find and replace text '

Screen001659.png

 When we use the label field, use 'explode' and 'trim'. Some people put a space after a comma, as in any word processor! Trim it..


Screen001657.png

The Excel file looks like this:

Screen001658.png

The Feeds institutions now do the rest.

In the Feeds choose this type:

Screen001664.png

and you create a feed of this type configure the path.

Screen001662.png

I've also configured that the Feed type removes the already imported nodes when there is no more row in the Excel file (with the same title).

Screen001665.png

How do you get the images and the Excel file on the server?

Note that the I've used Excel and CSV interchangeably. The fact is that I foresee in the interface you're using an Excel file, which is automatically converted to a PHP UTF-8 CSV file. You can also integrate Drupal page below the php filter module, otherwise make your example in a notebook one page and bring them to the server.

This code works but it needs the php spreadsheet librarie to operate. This is done with composer:  composer require phpoffice / phpspreadsheet. After uploading, the Excel file is converted to a CSV file and always stored with the same name, regardless of the name that you upload.

Edited version based on this discussion, with thanks: https://phpspreadsheet.readthedocs.io/en/develop/topics/reading-and-wri...

<?php
   if(isset($_FILES['bestand'])){
      $errors= array();
      $file_name = $_FILES['bestand']['name'];
      $file_size =$_FILES['bestand']['size'];
      $file_tmp =$_FILES['bestand']['tmp_name'];
      $file_type=$_FILES['bestand']['type'];
      $file_ext=strtolower(end(explode('.',$_FILES['bestand']['name'])));
      
      $expensions= array("xlsx");
      
      if(in_array($file_ext,$expensions)=== false){
         $errors[]="Het bestand moet een xlsx zijn";
      }     
           
      if(empty($errors)==true){
         move_uploaded_file($file_tmp,"sites/default/files/upload/dummysite.xlsx");
         echo "Bestand succesvol opgeladen";
      }else{
         print_r($errors);
      }
/* eerst met composer deze bib installeren!!
composer require phpoffice/phpspreadsheet
*/

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile("sites/default/files/upload/dummysite.xlsx");

$spreadsheet= $reader->load("sites/default/files/upload/dummysite.xlsx");

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);

$writer->setUseBOM(true);
$writer->setDelimiter(';');
$writer->setEnclosure('');
$writer->setLineEnding("\r\n");
$writer->setSheetIndex(0);

$writer->save("sites/default/files/upload/dummysite.csv");
   }
?>
<html>
   <body>
      
      <form action="" method="POST" enctype="multipart/form-data">
         
<p> Het laatste Excelbestand (.xlsx) staat op deze <a href="/sites/default/files/upload/dummysite.xlsx">link</a> en vormt de basis van de inhoud van deze site. </br>Wijzig het naar believen, maar de kolomkoppen moeten dezelfde naam behouden. De namen van afbeeldingen worden voorafgegaan door een #. Meerdere items worden door een komma gescheiden.</br>Elk uur zal de inhoud zichzelf aanpassen aan de nieuwe gegevens. Als je wilt kan je ook met deze <a href="https://fotoalbum.freewebsite.be/cron/RcT9g8RmxRy0qwVT6iZ2CkBN-pHOV5pIh…">link</a> onmiddellijk de gegevens aanpassen.</p>

<input type="file" name="bestand" />
<input type="submit"/ value="Verzenden">
      </form>
      
   </body>
</html>

The form looks like this. A link has also been provided to obtain the latest Excel file. The Feeds I configured so that an hourly cron synchronisations happen.. There is also for those who want a link for the cron to execute immediately.

Screen001660.png

There remains the problem of the images .. How do you load them up to the server, without any Drupal tool .. The following code, loads and will also resize them as too large (> 960 by 720px). It is checked whether it is a portet or landscape picture. GIF files I do not resize because sometimes they use animations that will be lost.

Images are overwritten with the same name and extension. I hate Drupal -0, -1, -2 numbering. If you do not want overwriting images ... use a local folder on your computer where you store the images. So there can never be two identical names to an image.

Edited version of this source: https://itsolutionstuff.com/post/how-to-upload-and-resize-image-in-php-...

<?php 
if(isset($_POST['submit'])){
function fn_resize($image_resource_id,$width,$height) {
$verhouding= $width/$height;
$target_width=$width;
$target_height=$height;
if($width>960 || $height>720){
    if ($verhouding>0){
      $target_width =960;
     $target_height =960/$verhouding;
    }
    else{
     $target_width =720;
     $target_height =720/$verhouding;
}
}

echo "breedte ".$width." hoogte ".$height." nieuwe breedte ".$target_width."  nieuwe hoogte ".$target_height."</br>";
$target_layer=imagecreatetruecolor($target_width,$target_height);
imagecopyresampled($target_layer,$image_resource_id,0,0,0,0,$target_width,$target_height, $width,$height);
return $target_layer;
}
 
 // Count total files
 $countfiles = count($_FILES['file']['name']);

 // Looping all files
 for($i=0;$i<$countfiles;$i++){
  $filename = $_FILES['file']['name'][$i];
 
  // Upload file
  //move_uploaded_file($_FILES['file']['tmp_name'][$i],'sites/default/files/upload/'.$filename);
// echo "Bestand ". $filename." werd opgeladen</br>";

$file = $_FILES['file']['tmp_name'][$i]; 
$source_properties = getimagesize($file);
$image_type = $source_properties[2]; 
//echo "file ".$file. "imagetype ".$image_type;

if( $image_type == IMAGETYPE_JPEG ) {   
$image_resource_id = imagecreatefromjpeg($file);  
$target_layer = fn_resize($image_resource_id,$source_properties[0],$source_properties[1]);
imagejpeg($target_layer,'sites/default/files/upload/'.$_FILES['file']['name'][$i]);
}
elseif( $image_type == IMAGETYPE_GIF )  {  
//$image_resource_id = imagecreatefromgif($file);
//$target_layer = fn_resize($image_resource_id,$source_properties[0],$source_properties[1]);
//imagegif($target_layer,"sites/default/files/upload/".$_FILES['file']['name'][$i]);
move_uploaded_file($_FILES['file']['tmp_name'][$i],'sites/default/files/upload/'.$filename);
}
elseif( $image_type == IMAGETYPE_PNG ) {
$image_resource_id = imagecreatefrompng($file); 
$target_layer = fn_resize($image_resource_id,$source_properties[0],$source_properties[1]);
imagepng($target_layer,'sites/default/files/upload/'.$_FILES['file']['name'][$i]);
}

}  

?>
<form method='post' action='' enctype='multipart/form-data'>
 
<p>Met dit formulier kan je afbeeldingen uploaden die je wilt gebruiken op de site. Onthou de namen van deze afbeeldingen, want je gaat ze moeten gebruiken in het Excel bestand dat de inhoud beheert. vb mijnafbeelding.jpg, andereafbeelding.png,...</br>
Afbeeldingen (jpeg,png) worden beperkt tot 960 X 720 breedte-hoogte verhouding en automatisch geschaald als ze groter zijn. GIF bestanden worden niet geschaald.</br>

Je moet altijd <b>eerst je afbeeldingen uploaden</b> en pas daarna het Excel bestand.</p> 
<input type="file" name="file[]" id="file" multiple>
<input type='submit' name='submit' value='Upload'>
</form>

Screen001661.png

So. Now you have two forms. One to put on the server the images and resize them and one for the Excel file, converted to a CSV file.

You have now a fully Excel-driven web site (at least for this type of content, but can you expand). You can test this on  https://fotoalbum.freewebsite.be . This is open to anyone testing. Want to work safer, make sure the URLs of your pages contain complex strings? .Not secure enough? Google Drive always works like that...