nimmneun/onesheet

OneSheet is a simple single sheet excel/xlsx file writer for PHP 5.4+ & 7+ & 8

onesheet's Issues

Adding image to the spreadsheet

Is it possible to add an insert image to the document?

Write multiple sheets

In some cases, it would be helpful to be able to write multiple sheets. E.g. if the finance team wants to have a list of order_items and refund_items in one file for a YoY comparison/evaluation of sold products vs their refund quota.

• try to make this a non-breaking change (aim for v1.2 over v2.0)
• ensure auto-sizing feature works for individual sheets
• ensure repeatable print ranges work for individual sheets
• ensure tests for single and multiple sheets are added

PHP 8.1.3

Hi !
As I tried to use OneSheet with PHP 8.1.3 (having no problems with previous versions of PHP), I faced some errors that prevented the file to be generated. I was able to make it work with these 3 changes.

File : src/OneSheet/Sheet.php

Error : Deprecated: preg_match(): Passing null to parameter #2 ($subject) of type string is deprecated

Before :

public function getSheetViewsXml()
    {
        if (1 !== preg_match('~^[A-Z]+(\d+)$~', $this->freezePaneCellId, $m)) {
            return '';
        }

        return sprintf(SheetXml::SHEETVIEWS_XML, array_pop($m) - 1, $this->freezePaneCellId);
    }

After :

public function getSheetViewsXml()
    {
        if (1 !== preg_match('~^[A-Z]+(\d+)$~', $this->freezePaneCellId ?: '', $m)) {
            return '';
        }

        return sprintf(SheetXml::SHEETVIEWS_XML, array_pop($m) - 1, $this->freezePaneCellId);
    }	

File : src/OneSheet/Sheetfile.php

Error : Deprecated: uniqid(): Passing null to parameter #1 ($prefix) of type string is deprecated

Before :

public function __construct()
    {
        $this->filePath = sys_get_temp_dir() . DIRECTORY_SEPARATOR . uniqid(null, 1) . '.xml';
        if (!$this->filePointer = fopen($this->filePath, 'wb+')) {
            throw new \RuntimeException("Failed to create temporary sheet file {$this->filePath}!");
        }
    }

After :

public function __construct()
    {
        $this->filePath = sys_get_temp_dir() . DIRECTORY_SEPARATOR . uniqid('', 1) . '.xml';
        if (!$this->filePointer = fopen($this->filePath, 'wb+')) {
            throw new \RuntimeException("Failed to create temporary sheet file {$this->filePath}!");
        }
    }	

File : src/OneSheet/Size/SizeCalculator.php

Error : Deprecated: is_dir(): Passing null to parameter #1 ($filename) of type string is deprecated

Before :

public function __construct($fontsDirectory = null)
    {
        if (is_dir($fontsDirectory)) {
            $this->findFonts($fontsDirectory);
        }
    }

After :

public function __construct($fontsDirectory = null)
    {
        if ($fontsDirectory != null && is_dir($fontsDirectory)) {
            $this->findFonts($fontsDirectory);
        }
    }

It works for what I need to do but I'm not sure if it's the best way to correct those errors.

New lines not working

Hello,

I'm trying to output new lines in a cell. I've tried with \n, \r\n and PHP_EOL but none of them works, unless I give focus to the cell on Microsoft Excel and then validate it again, and in that case the new line will appear.

For instance :
$onesheet = new \OneSheet\Writer(); $onesheet->enableCellAutosizing(); $onesheet->addRow(["Apple \n Orange"]); $onesheet->writeToFile('sheet.xlsx');

... will just output "Apple Orange" on the same line.

I'm not sure if this is an Excel settings issue or if it's OneSheet not formatting new lines correctly...

Any advice?

Thanks

Repeat heading when printing

It would be a great thing if there is something like this:

https://stackoverflow.com/questions/35026930/set-rows-to-repeat-at-top-when-printing-phpexcel

So when someone prints the document, the heading is automatically printed on each sheet.

What do you think?

zoom scale sheet view

Please how to add zoom scale to xml files of sheet view ? i am trying to modify xml in my fork for testing .

Support \Traversable (e.g. generator) for Writer::addRows()

It would be nice if Writer::addRows() could accept a \Traversable (e.g. a generator) instead of only an array. Right now one either has to use iterator_to_array() or Writer::addRow() and foreach.

Fixed and estimated column widths

It should be possible to set fixed/custom column widths.

There should be an option to auto set widths based on cell values.
Unfortunately imagettfbbox() is rather sloooooow, so thats not an option (for now), so it'll probably be "estimated" widths.
todos:

• base multipliers per font, size (+ bold & italic)
• multiplier groups for ~100 ASCII characters

Initial tests with Calibri, Arial, Times are looking ok. Estimations are off by about 2-5% per 50 characters.

Multiple sheets with option setFreezePaneCellId are grouped

Hello Team
when creating multiple sheets with option setFreezePaneCellId=1 all sheets are grouped. Now if someone starts to edit the header all headers in all sheets are changed. Once you click on a sheet tab they are no longer grouped.
If you remove tabSelected="1" in onesheet/src/OneSheet/Xml/SheetXml.php:20 it works and has no effect on single sheet excel files.
Could please verify this and correct it if possible?
Thanks a lot for you help

best regards
Christian

SizeCalculator instantiation and cleanups

Just a reminder for myself to a) maybe instantiate only on demand and b) tidy things up a little

[Feature Request] Column formats

Hi,

I like this library, would love to use it, but for us the lack of column formatting is a deal breaker unfortunately. Would you consider adding simple Column Formatting for Dates and Numbers? Or would you consider a PR adding simple Date/Number Formatting?

edit: basicly the same as this: #9

Problem with enableCellAutosizing

I found an issue that caused all the columns that had data to be effectively displayed as 0 width when turning enableCellAutosizing on. This might be related to my system locale or something similar. The $cellWidth that the sizeCalculator->getCellWidth was returning turned out to be a number with a comma as the decimal separator, such as "2,142".

I fixed the problem with a bit of a hack, by changing
$this->columnWidths[$cellIndex] = $cellWidth;

to
$this->columnWidths[$cellIndex] = str_replace(',', '.', $cellWidth);

in the updateColumnWidths method of Sheet.php. I'm sure there's a better way to do it, though.

New free CI tooling

Since travic-ci.org discontinued their free CI, a replacement is needed. It should test PHP 5.4 (or at least 5.6), 7.3/7.4, and 8.1

PHP 8.0.2

Hi team, the condition in the composer json don't allow installation on php 8 over 8.0.0:

Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - Root composer.json requires nimmneun/onesheet ^1.2 -> satisfiable by nimmneun/onesheet[1.2.0].
    - nimmneun/onesheet 1.2.0 requires php >=5.4.4 <=8 -> your php version (8.0.2) does not satisfy that requirement.

Create empty file

Hi,

When I am trying to create a file without any lines I get a broken file. Is it possible to create an empty file?

$onesheet = new \OneSheet\Writer('/optional/fonts/directory'); $onesheet->writeToFile('hello_world.xlsx');

Number Formats/numFmt per column?

There should be an option to set/apply number formats ... not sure how I want to implement this yet.

On the one hand number formats are part of the style, but I need them on a per-column bases for pretty much all of my use cases and not per row ... of course, there are the built in ones, which have their id's ... hmmm

Insert hyperlink

Is it possible to add a hyperlink to a cell as a link and not just as text?

Text alignment

Implement a decent way to align text ... probably requires refac of Style class beforehand.
Maybe something along the lines of:

(new Style())->doStuff()->align()->centerCenter()

class Alignment(Style)
public function topLeft();
public function topCenter();
public function topRight();
public function centerLeft();
public function centerCenter();
public function centerRight();
public function bottomLeft();
public function bottomCenter();
public function bottomRight();

Linking/importing xlsx file with Access show a blank screen

Hello,

I try to import/link an XLSX file in Access 2013 made with onesheet. At the very first step of the wizard the content of the XLSX file is not displayed. I think Access is puzzled by the structure of the file. Could that be fixed?

Regards,

OfficeImportErrorDomain when downloading file on iOS

Hello,

We use OneSheet to generate exports in our app since a week or two (we were generating CSVs before). Lately our iOS users reported that an error was shown instead of the downloaded export : "OfficeImportErrorDomain".

I investigated and ended up in a Box/Spout issue : box/spout#85 with a MR that was solving the problem.

So I tested on OneSheet and it's working fine too. So I am submitting an MR to OneSheet to solve this problem.

Thanks for your work,

Romain.