How to solve slow movement and scrolling in an Excel worksheet that contains Hebrew text?

Question

I'm exporting a list of some resource into a xlsx file, where each resource has both English and Hebrew fields. The problem is that scrolling the worksheet is utterly slow.

The slowness has been experienced using Microsoft Excel in multiple PCs, with both Microsoft and Mac operating systems.

What I've tried that didn't help:

Exporting the xlsx file using both PHPOffice/PhpSpreadsheet and mk-j/PHP_XLSXWriter packages.
Trimming and removing special chars from the texts.

What I've tried that did help:

Writing the file in CSV format with UTF-8 encoding.

This solution isn't good enough for me, as I want to be able to export a file with stylings.

(Removing the Hebrew columns also helped speeding up the scrolling)

A reproducible example using PHPOffice/PhpSpreadsheet (assuming a database table with both English and Hebrew fields):

<?php

$nominees = \App\Entities\Nominee::all();

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Headings
$sheet->setCellValue('A1', 'שם');
$sheet->setCellValue('B1', 'Name');
$sheet->setCellValue('C1', 'ביוגרפיה');
$sheet->setCellValue('D1', 'Biography');

$row = 2;

// Rows
foreach ($nominees as $nominee) {
    $sheet->setCellValue('A' . '$row', $nominee->he_name);
    $sheet->setCellValue('B' . '$row', $nominee->en_name);
    $sheet->setCellValue('C' . '$row', $nominee->he_biography);
    $sheet->setCellValue('D' . '$row', $nominee->en_biography);

    $row++;
}

$sheet->setRightToLeft(true); // Example for styling

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save('some/path');

score 0 · Answer 1 · answered May 17 '19 at 15:47

I suggest investigating the scorlling use case. Maybe you can replace scrolling with 'find' or sort functionality.

The slowness is function of recalculating fields on each layout/screen and rendering.

So I suggest also trying to translate the calculated fields to static values.

Hope you got directions I am taking, and follow it.

How to solve slow movement and scrolling in an Excel worksheet that contains Hebrew text?

1 Answers1