I have given two concrete user cases from my post above...
Well, you are never better served than by yourself...
While waiting for a better answer, we can do this in a custom module.
Create a file modules/custom/my_module/src/Controller/ExcelFileMultiSheetsController.php with the content below:
namespace Drupal\my_module\Controller;
use Drupal\Core\Controller\ControllerBase;
use Drupal\Core\Url;
class ExcelFileMultiSheetsController extends ControllerBase {
public function startBatchExcelFile() {
$operations = [];
//Example with two sheets in the same Excel document
//You have to create the views first using a block display for example
$views_to_excel = [
'machine_name_of_a_view_1' => 'Sheet #1',
'machine_name_of_a_view_2' => 'Sheet #2',
];
foreach ( $views_to_excel as $view_id => $sheet_name ) {
$view = \Drupal\views\Views::getView( $view_id );
$view -> setDisplay( 'default' );
$view -> execute();
$total = count( $view -> result );
$chunks = ceil( $total / 100 );
for ( $i = 0; $i < $chunks; $i ++ ) {
$operations[] = [
'\Drupal\my_module\Service\ExcelFileBatch::processChunk',
[ $view_id, $sheet_name, $i * 100, 100 ]
];
}
}
$batch = [
'title' => $this -> t( 'Creating Excel File' ),
'operations' => $operations,
'finished' => '\Drupal\my_module\Service\ExcelFileBatch::finishBatch',
];
batch_set( $batch );
return batch_process( Url::fromRoute( 'system.admin_content' ) );
}
}
Then, create a file modules/custom/my_module/src/Service/ExcelFileBatch.php with the content below:
namespace Drupal\my_module\Service;
use Drupal\Core\File\FileSystemInterface;
use Drupal\views\Views;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class ExcelFileBatch {
protected static function getFilePath( $filename = 'my_file.xlsx' ) {
$stream_wrapper_manager = \Drupal::service( 'stream_wrapper_manager' );
$file_system = \Drupal::service( 'file_system' );
$user_dir = \Drupal::currentUser() -> id();
$date_dir = date( 'Y-m-d' );
$directory = $stream_wrapper_manager -> isValidScheme( 'private' )
? "private://views_data_export/$date_dir/$user_dir/"
: "public://views_data_export/$date_dir/$user_dir/";
$file_system -> prepareDirectory( $directory, FileSystemInterface::CREATE_DIRECTORY | FileSystemInterface::MODIFY_PERMISSIONS );
return $directory . $filename;
}
public static function processChunk( $view_id, $sheet_name, $offset, $limit, &$context ) {
$file_path = self::getFilePath();
if ( file_exists( \Drupal::service( 'file_system' ) -> realpath( $file_path ) ) ) {
$spreadsheet = IOFactory::load( \Drupal::service( 'file_system' ) -> realpath( $file_path ) );
} else {
$spreadsheet = new Spreadsheet();
$spreadsheet -> removeSheetByIndex( 0 );
}
$view = Views::getView( $view_id );
$view -> setDisplay( 'default' );
$view -> setItemsPerPage( $limit );
$view -> setOffset( $offset );
$view -> execute();
$data = [];
foreach ( $view -> result as $row ) {
$record = [];
foreach ( $view -> field as $field_name => $field ) {
$record[ $field_name ] = $field -> advancedRender( $row );
}
$data[] = $record;
}
if ( empty( $data ) ) {
return;
}
$sheet = null;
foreach ( $spreadsheet -> getSheetNames() as $index => $name ) {
if ( $name === $sheet_name ) {
$sheet = $spreadsheet -> getSheet( $index );
break;
}
}
if ( !$sheet ) {
$sheet = new Worksheet( $spreadsheet, $sheet_name );
$spreadsheet -> addSheet( $sheet );
$sheet -> fromArray( array_keys( $data[ 0 ] ), NULL, 'A1' );
$row_num = 2;
} else {
$row_num = $sheet -> getHighestRow() + 1;
}
foreach ( $data as $row ) {
$sheet -> fromArray( array_values( $row ), NULL, 'A' . $row_num ++ );
}
$temp_path = tempnam( sys_get_temp_dir(), 'phpss_' );
$writer = new Xlsx( $spreadsheet );
$writer -> save( $temp_path );
$data = file_get_contents( $temp_path );
unlink( $temp_path );
\Drupal::service( 'file.repository' ) -> writeData(
$data,
$file_path,
FileSystemInterface::EXISTS_REPLACE
);
}
public static function finishBatch( $success, $results, $operations ) {
if ( $success ) {
\Drupal::messenger() -> addMessage( 'Excel file has been created successfully.' );
$file_path = self::getFilePath();
$url = \Drupal::service( 'file_url_generator' ) -> generateString( $file_path );
\Drupal::messenger() -> addMessage( '<a href="' . $url . '">Download Excel File</a>' );
} else {
\Drupal::messenger() -> addError( 'Excel File creation failed.' );
}
}
}
Now, in the file modules/custom/my_module/my_module.module, you have to add this function:
function my_module_file_download( $uri ) {
if ( str_starts_with( $uri, 'private://views_data_export/' ) ) {
return [
'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
];
}
return -1;
}
And, in the file modules/custom/my_module/my_module.routing.yml, you have to add these lines:
excel_file_multi_sheets.create_file:
path: '/admin/excel_file_batch'
defaults:
_controller: '\Drupal\my_module\Controller\ExcelFileMultiSheetsController ::startBatchExcelFile'
_title: 'Create Excel File multi sheets'
requirements:
_permission: 'access content'From a browser, logged in with a user with appropriate rights, you have to go to /admin/excel_file_batch in order to create the Excel File with multiple sheets.
This is just a first draft, feel free to improve the code above.
Hi, I'm also very interested in this feature, I want to generate one excel document with several sheets, one containing for example data from content of an entity type and another sheet containing data from content of another entity type because some of data is cross-referenced between these two entity types. Or another case: In a sheet I want data from content of an entity type and in another sheet I want the complete list of the terms of a taxonomy vocabulary used in the entity type.
OK, I answer to my own question,
For those who are interested, here is some code to create a custom serializer to use within a REST Export display which shows spellcheck suggestions in a "spellcheck" metadata inside the JSON response.
modules/custom/my_custom_module/Plugin/views/style/CustomSerializer.php
<?php
namespace Drupal\my_custom_module\Plugin\views\style;
use Drupal\Core\Form\FormStateInterface;
use Drupal\rest\Plugin\views\style\Serializer;
use Symfony\Component\DependencyInjection\ContainerInterface;
use Symfony\Component\Serializer\SerializerInterface;
/**
* The style plugin for serialized output formats.
*
* @ingroup views_style_plugins
*
* @ViewsStyle(
* id = "my_custom_serializer",
* title = @Translation( "My Custom serializer" ),
* help = @Translation( "Serializes views row data using the Serializer component." ),
* display_types = { "data" }
* )
*/
class CustomSerializer extends Serializer {
public static function create( ContainerInterface $container, array $configuration, $plugin_id, $plugin_definition ) {
return new static(
$configuration,
$plugin_id,
$plugin_definition,
$container -> get( 'serializer' ),
$container -> getParameter( 'serializer.formats' ),
$container -> getParameter( 'serializer.format_providers' )
);
}
public function __construct( array $configuration, $plugin_id, $plugin_definition, SerializerInterface $serializer, array $serializer_formats, array $serializer_format_providers ) {
parent::__construct( $configuration, $plugin_id, $plugin_definition, $serializer, $serializer_formats, $serializer_format_providers );
}
protected function defineOptions() {
$options = parent::defineOptions();
$options[ 'spellcheck_count' ] = [ 'default' => 1 ];
return $options;
}
public function buildOptionsForm( &$form, FormStateInterface $form_state ) {
parent::buildOptionsForm( $form, $form_state );
$form[ 'spellcheck_count' ] = [
'#type' => 'number',
'#title' => $this -> t( 'Spellcheck, amount of results to show' ),
'#default_value' => $this -> options[ 'spellcheck_count' ],
];
}
public function render() {
$rows = [];
foreach ( $this -> view -> result as $row_index => $row ) {
$this -> view -> row_index = $row_index;
$row = $this -> view -> rowPlugin -> render( $row );
//YOUR OWN CODE IF YOU WANT TO MAKE SOME MODIFICATION ON EACH ROW
$rows[] = $row;
}
unset( $this -> view -> row_index) ;
if ( empty( $this -> view -> live_preview ) ) {
$content_type = $this -> displayHandler -> getContentType();
} else {
$content_type = !empty( $this -> options[ 'formats' ] ) ? reset( $this -> options[ 'formats' ] ) : 'json';
}
$fields = [];
foreach ( $this -> view -> field as $field_name => $field ) {
if ( $field -> options[ 'exclude' ] == 0 && $field -> label() ) {
$fields[ $field_name ] = $field -> label();
}
}
$current_page = 0;
$total_items = 0;
$total_pages = 0;
$items_per_page = 0;
$pager = $this -> view -> pager;
if ( $pager ) {
$class = get_class( $pager );
$current_page = $pager -> getCurrentPage() ?? 0;
$items_per_page = $pager -> getItemsPerPage();
$total_items = $pager -> getTotalItems();
if ( !in_array( $class, [
'Drupal\views\Plugin\views\pager\None',
'Drupal\views\Plugin\views\pager\Some',
] ) ) {
$total_pages = $pager -> getPagerTotal();
}
} else {
$current_page = intval( \Drupal::request() -> query -> get( 'page' ) );
$items_per_page = 1;
$total_pages = count( $rows );
$total_items = count( $rows );
}
$spellcheck_suggestions = [];
$query = $this -> view -> query;
if ( $query instanceof \Drupal\search_api\Plugin\views\query\SearchApiQuery && $query -> getIndex() -> getServerInstance()-> supportsFeature( 'search_api_spellcheck' ) ) {
$search_api_query = $query -> getSearchApiQuery();
$index = $search_api_query -> getIndex();
$backend = $index -> getServerInstance() -> getBackend();
$connector = $backend -> getSolrConnector();
$solarium_query = $connector -> getSelectQuery();
$spellcheck = $solarium_query -> getSpellcheck();
$schema_languages = $backend -> getSchemaLanguageStatistics();
$dictionaries = [];
foreach ( \Drupal\search_api_solr\Utility\Utility::ensureLanguageCondition( $search_api_query ) as $language_id ) {
if ( isset( $schema_languages[ $language_id ] ) && $schema_languages[ $language_id ] ) {
$dictionaries[] = $schema_languages[ $language_id ];
}
}
if ( $dictionaries ) {
$spellcheck -> setDictionary( $dictionaries );
} else {
$spellcheck -> setDictionary( \Drupal\Core\Language\LanguageInterface::LANGCODE_NOT_SPECIFIED );
}
$keys = $query -> getKeys();
if ( $keys && is_array( $keys ) ) {
$spellcheck -> setQuery( implode( ' ', array_filter( $keys, 'is_int', ARRAY_FILTER_USE_KEY ) ) );
}
$spellcheck -> setCount( $this -> options[ 'spellcheck_count' ] );
$spellcheck -> setBuild( TRUE );
$spellcheck -> setCollate( TRUE );
$spellcheck -> setExtendedResults( TRUE );
$spellcheck -> setCollateExtendedResults( TRUE );
$resultset = $connector -> execute( $solarium_query );
$spellcheck_results = $resultset -> getSpellcheck();
$spellcheck_suggestions = [];
if ( $spellcheck_results ) {
foreach ( $spellcheck_results -> getSuggestions() as $suggestion ) {
$spellcheck_suggestions[] = [
'word' => $suggestion -> getWord(),
'numFound' => $suggestion -> getNumFound(),
'startOffset' => $suggestion -> getStartOffset(),
'endOffset' => $suggestion -> getEndOffset(),
'suggestion' => $suggestion -> getWords()
];
}
}
}
return $this -> serializer -> serialize( [
'title' => $this -> view -> getTitle(),
'rows' => $rows,
'spellcheck' => $spellcheck_suggestions,
'fields' => $fields,
'pager' => [
'current_page' => $current_page,
'total_items' => $total_items,
'total_pages' => $total_pages,
'items_per_page' => $items_per_page,
]
], $content_type, [ 'views_style_plugin' => $this ] );
}
}
For example, if I set an exposed search_api_fulltext filter (be careful that this is not working for "direct parse") and if I search for the word "spac", I obtain the following JSON response:
{"title":"My view",rows":[],"spellcheck":[{"word":"space","numFound":10,"startOffset":0,"endOffset":4,"suggestion":[{"word":"space","freq":60},{"word":"spacex","freq":7},{"word":"spain","freq":7},{"word":"scaf","freq":6},{"word":"spare","freq":6},{"word":"sdas","freq":3},{"word":"spot","freq":3},{"word":"seal","freq":2},{"word":"seas","freq":2},{"word":"spaces","freq":2}]}],"fields":{"date":"Date","description":"Description","country":"Country","source":"Source","tag":"Tag(s)","created":"Edited on"},"pager":{"current_page":0,"total_items":0,"total_pages":0,"items_per_page":50}}
It's just a first shot, I know it can be improved
Two years and a half later I'm asking the same question: how to implement spellcheck in the context of a rest export?
For example, is it possible to include the "did you mean" suggestion inside a metadata as pager or facets do?
Strange indeed! I had a former patch ( issue 3316975 🐛 Contextual Link throws RouteNotFoundException: Route "view.this" does not exist Needs review ) that I removed from my composer.json file because it seems to have been merged in the 2.1.4 release
gaspounet → created an issue.
Hi, I managed to install Drush globally for all my Drupal websites installed on my server using the dev-master branch of Drush:
composer global require drush/drush:dev-master -W
If your server is running on a Linux system and you connect to your server using a bash shell, you can edit the "~/.bashrc" file to add the following line at the end of the file:
export PATH="$HOME/.config/composer/vendor/bin:$PATH"
Then reload the shell configuration file with the following command:
source ~/.bashrc
Even if Drush is installed globally, you need also to install Drush in the composer.json file located on the root folder of your Drupal websites (not the dev-master branch in this case) running the following command:
composer require drush/drush
Verify that all is working properly by running the following command inside the root folder of one of your Drupal website:
drush status
It should result something like this:
Drupal version : 10.2.6 Site URI : http://default DB driver : <YOUR DATABASE SYSTEM> DB port : <YOUR DATABASE SYSTEM PORT IF ANY> DB username : <YOUR DATABASE USERNAME IF ANY> DB name : <YOUR DATABASE NAME> Database : Connected Drupal bootstrap : Successful Default theme : <YOUR THEME> Admin theme : <YOUR ADMIN THEME> PHP binary : <PHP PATH> PHP config : <PHP CONFIG FILE PATH> PHP OS : <YOUR SERVER OS> PHP version : <YOUR PHP VERSION> Drush script : <YOUR DRUPAL ROOT FOLDER>/vendor/drush/drush/drush Drush version : 12.5.2.0 Drush temp : <YOUR SYSTEM TEMP FOLDER> Drush configs : <YOUR DRUPAL ROOT FOLDER>/vendor/drush/drush/drush.yml Install profile : minimal Drupal root : <YOUR DRUPAL WEBROOT FOLDER> Site path : sites/default Files, Public : sites/default/files Files, Temp : <YOUR SYSTEM TEMP FOLDER>
Hi! It worked for me too, thank you, but I had to add also drupal/jquery_ui_datepicker ^2.0 to the composer require command:
composer require 'drupal/better_exposed_filters:6.0.3' 'drupal/jquery_ui_slider:^2.0' drupal/jquery_ui_datepicker:^2.0 -W
Thank you very much @keshavv for this patch, I can confirm that the error messages have disappeared now and that I can use again the autocomplete feature!
gaspounet → created an issue.
And so, in order to obtain the results I'm looking for using taxonomy IDs exposed filters alongside fulltext fields using the direct parse mode and the "contains all words" operator, I need to rewrite the query thanks to a hook function:
function MYMODULE_views_query_alter( $view, $query ) {
switch( $view -> id() ) {
case 'my_solr_index_view':
//Get the fulltext search value when using direct parse mode
$cleanKeys = " " . $query -> getKeys();
//remove all quotes if there is an odd number of quotes
if ( substr_count( $cleanKeys, '"' ) % 2 !== 0 ) {
$cleanKeys = str_replace( "\"", " ", $cleanKeys );
}
//Remove all parentheses if the number of open parentheses does not match the number of closed parentheses
if ( substr_count( $cleanKeys, '(' ) != substr_count( $cleanKeys, ')' ) ) {
$cleanKeys = str_replace( "(", " ", $cleanKeys );
$cleanKeys = str_replace( ")", " ", $cleanKeys );
}
//Clean operator keywords
$cleanKeys = str_replace( "+", " AND ", $cleanKeys );
$cleanKeys = str_replace( "&&", " AND ", $cleanKeys );
$cleanKeys = str_replace( "||", " OR ", $cleanKeys );
$cleanKeys = str_replace( "\"", " \" ", $cleanKeys );
$cleanKeys = str_replace( " -", " NOT ", $cleanKeys );
$cleanKeys = trim( preg_replace( '/\s+/', ' ', $cleanKeys ) );
$cleanKeys = preg_replace( "/^(AND |OR |NOT )/", "* $1", $cleanKeys );
//Loop through the query in order to add the "AND" operator between words when the operator is not explicitly written and when we are not looking for exact match
$solrBooleans = [ "AND", "OR", "NOT" ];
$newKeys = [];
$previousKey = "";
$exactWord = false;
foreach ( explode( " ", $cleanKeys ) as $key ) {
if ( $key == "\"" ) {
$exactWord = !$exactWord;
$newKeys[] = $key;
} else {
if ( $exactWord || !$previousKey || in_array( $key, $solrBooleans ) || in_array( $previousKey, $solrBooleans ) ) {
$newKeys[] = $key;
} else {
$newKeys[] = "AND";
$newKeys[] = $key;
}
}
$previousKey = $key;
}
$query -> keys( implode( " ", $newKeys ) );
break;
}
}
Please let me know if there is a better way (sure there is!).
Well me, it's nice talking to myself
A credit to dementia
—Megadeth, Sweating Bullets
OK so the problem was coming from the modification I made in my solrconfig_extra.xml file:
<requestHandler name="/select" class="solr.SearchHandler">
<lst name="defaults">
<str name="defType">lucene</str>
...
<str name="q.op">AND</str>
</lst>
...
</requestHandler>If I remove the "q.op" parameter to let the operator by default for query expressions (operator "OR"), it is working as expected...
In the first place I set this parameter to "AND" to suit my needs for my fulltext filters: I want only results were we can find all the words in the search phrases typed by the user without the user having to separate them manually with the keywords "AND" or "&&" which is not working for my views where I use the direct parsing mode which ignores the operator "contains all words".
By digging a little more it turns out that the problem comes in fact from the quotation marks used in the query
fq=itm_test_id:("10"+"13") <== NOT WORKING
fq=itm_test_id:(10+13) <== OK !
Is there a way to check that the value is numeric and in this case treat it as such ?
gaspounet → created an issue.