shuchkin / simplexlsxgen Goto Github PK

When I make multiple sheets with hyperlink in cells,

• Excel drops an error but read the sheets
• Every sheet contains the hyperlinks of first sheet.
  Test code:

        $xls['b'][] = array('a', 'b', 					'<a href="https://example.hu/b1">b1</a>');
	$xls['b'][] = array('aa', 'bb', 				'<a href="https://example.hu/b2">b2</a>' );
	$xls['m'][] = array('ma', 'mb', 'mc', 'md','me','<a href="https://example.hu/m1">m1</a>');
	$xls['m'][] = array('maa', 'mbb', 'mcc', 'mdd','mee', '<a href="https://example.hu/m2">m2</a>');
	$xls['d'][] = array('da', 'db', 'dc', 			'<a href="https://example.hu/d1">d1</a>');
	$xls['d'][] = array('daa', 'dbb', 'dcc',		'<a href="https://example.hu/d2">d2</a>');
	include_once($_SERVER['DOCUMENT_ROOT']."/.tools/simplexlsxgen/src/SimpleXLSXGen.php");
	$filename = "tst_".date('Ymd').".xlsx";
		
	$xlsx = new SimpleXLSXGen();
	$xlsx -> addSheet( $xls['b'], "1st Sheet" );
	$xlsx -> addSheet( $xls['m'], "2nd Sheet" );
	$xlsx -> addSheet( $xls['d'], "3rd Sheet" );
	$xlsx->saveAs('tmp/'.$filename);

What I do wrong ?
Thanx 4 any help as now i've to make 3 file than put them together ... not the best solution :)

Hi,
i wanted to save time "23:06:11" as a string to a cell, but the lib crashed.
called
public function date2excel($year, $month, $day, $hours=0, $minutes=0, $seconds=0)
with params (0, 0, 0, 23, 6, 11)
variable $year is changed to -1 in the else statement, then

$century = substr($year,0,2);
$decade = substr($year,2,2);

$excelDate = floor((146097 * $century) / 4) + floor((1461 * $decade) / 4) + floor((153 * $month + 2) / 5) + $day + 1721119 - $myExcelBaseDate + $excel1900isLeapYear;

the line with $excelDate crashes because of non numeric value of century (the sign minus).

I tried to do a fix and set the $year back to 0 if negative and it stoped to crash, but the cell was kind of nonsense, it was datetime format with year 0001 or someting like that. I consider this as a bad behaviour and i solved the problem with commenting

/*elseif ( preg_match('/^(\d\d):(\d\d):(\d\d)$/', $v, $m ) ){
							$cv = $this->date2excel(0,0,0,$m[1],$m[2],$m[3]);
							$cs = 4; // [14] mm-dd-yy
						}*/

I think this pattern should not be considered as a datetime even pure string is much better solution, TimeSpan would be optimal, but I am not sure, if it is supported.

BG, Franceq

Dunno if its a bug or an undocumented feature, but if you write something like this:
$xlsx = SimpleXLSXGen::fromArray($data);
$xlsx->saveAs('data1.xlsx');
$xlsx->saveAs('data2.xlsx');

second saveAs() call will generate several warnings and data2.xlsx file will be corrupted:
PHP Warning: count(): Parameter must be an array or an object that implements Countable in SimpleXLSXGen.php on line 248
PHP Warning: count(): Parameter must be an array or an object that implements Countable in SimpleXLSXGen.php on line 251
PHP Warning: implode(): Invalid arguments passed in SimpleXLSXGen.php on line 252

Probably, at least SI and SI_KEYS should be set to their devault values (empty array) at the beginning of the _write() method, not in constructor.
Or at least mention it in documentation :)

Thanks in advance.

There's a way to merge cells?

I have the following code snippet:

`$books = [
['ISBN', 'title', 'author', 'publisher', 'ctry' ],
[618260307, 'The Hobbit', 'J. R. R. Tolkien', 'Houghton Mifflin', 'USA'],
[908606664, 'Slinky Malinki', 'Lynley Dodd', 'Mallinson Rendel', 'NZ']
];

$xlsx = SimpleXLSXGen::fromArray( $books );
$xlsx->saveAs('books.xlsx');
$xlsx->downloadAs('books.xlsx');
`

The file is downloaded and save to my hard drive just fine. When I go to open it in Excel 2016 I get the following error message from Excel: "We found a problem with some content in books.xlsx. Do you want us to try and recover as much as we can." I can click on Yes and all the content is there and OK. What do I need to do to download Excel files and not get this message? If I use FileZilla and copy it to my hard drive from my website, I do not get the error message.

hey there,
first wanna thank you for this amazing package, it is really helpfull.
i am trying to generate a xlsx file which contain decimal number such as (0.19) but instead i am getting (0,19).
is there a way to edit this before generating?
Thanks in advance

Hi Sergey,
at line 120 missing an open-close round bracket in case "name" is not defined
Line $this->sheets[$this->curSheet] = ['name' => $name ?: ('Sheet'.($this->curSheet+1)), 'hyperlinks' => []];

When opening the file in excel, does it give a message that needs to be repaired?

Have you seen any messages like that?

Initial message

Result

LOG

producao-x-vendas.xlsx

Only in this file that gave this. Others that I generated are normal!

Hi, sorry if this is the wrong place to mention this - I shall remove this if you like. In the Example, there is :

$data = [
['Integer', 123],
['Float', 12.35],
['Procent ', '12%'],
['Datetime', '2020-05-20 02:38:00'],
['Date','2020-05-20'],
['Time','02:38:00'],
['String', 'Long UTF-8 String in autoresized column'],
['Disable Type Detection', "\0".'2020-10-04 16:02:00']
];

Should Procent be Percent?

:-)

Hi,

I'm just starting with your brilliant library, and I'm wondering if there's any way I might force a cell to be recognised as text?

Specific example:
A value for a cell is a string: 13432718501879496790001 - without specifying this cell (or column) as 'text' type, Excel presumes 'general', and converts this long number to scientific notation; it appears in the cell as: 1.34327185018795E+22 - unfortunately changing the cell/column type in Excel 'after the fact' doesn't restore the 'plain' string.

At the moment, I'm wrapping quotes around the number in the array for output by SimpleXLSXGen like this:

foreach($sqlRow as $key => $value){
	if($key == "longnumber"){
		$rowArray[] = "\"".$value."\"";
	}else{
		$rowArray[] = $value;
	}
}

...this (I know, dirty!) method forces quotes around the long number, Excel parses it as text, displaying: "13432718501879496790001"

In an ideal world, I'd like to be able to specify the cell (or column) type to Excel as text so that the quotes would not be required (and not shown on screen in Excel).

I imagine there might be a way to count digits in a string which appears to be a number, and once the count is above 11 (which AFAIK is Excel's cut-off) forcing 'text' cell type? (I had a look in the library but I'm not yet that good at PHP!!!).

Thanks (for your excellent library, and any help you might be able to offer on this)

Rob.

Hello!
Can you help me with formating text?

I want to make my data bold, but can't understand, how can i do that :-(
And one more, i need to make a hyperlink :-(

When the content of the array has strings with accented letters (Á é í ã ....) or Ç the xlsx generated is malformed.

I notice when i tryed to generate a xlsx why the array:

Array ( [0] => Array (
[0] => Número do Contrato
[1] => Empenho
[2] => Objeto
[3] => Fornecedor
[4] => Data de Publicação
[5] => Data de Assinatura
[6] =>Data de Início de Vigência
[7] => Data de Fim De Vigência
[8] => Valor do Contrato ) )

I try to use utf8_encode and utf8_decode functions, but it does not worked.

Hello,
First of all thank you for the excellent class! Very easy to use. I have a curious problem. When I test my code locally, I can read the Excel file correctly. However, testing from my staging environment, I get an error message from Excel when I try to read the downloaded file. The data is all there, but Excel tells me that the file needs to be repaired.
SN482941614375363.xlsx

Here is an example file.

This is wonderfully simple! Thank you. I have a question, which is not a bug.

Is there anyway to avoid an ISO date format being turned into an excel date?

The application I have requires the XLS file to be uploaded automatically with this format in place.

Thank you for your work!

If you try to insert the utf8 char "\x03" as a shared string or inline string, microsoft excel wont be able to open the file because it will find a problem with some content in the xlsx file.

Hello how i can customized cell width? I guess not supported? Am i right?

Hello,

I'm reading my xlsx data file with shuchkin/simplexlsx and in this file are multiple sheets. But now I want to generate this file.
But unfortunately I'm not able to write an xlsx-file with multile sheets in it.

Thank you for your work!

If use these code (from read.me and replace saveAs () on downloadAs()):

$data = [
    ['Integer', 123],
    ['Float', 12.35],
    ['Percent', '12%'],
    ['Datetime', '2020-05-20 02:38:00'],
    ['Date','2020-05-20'],
    ['Time','02:38:00'],
    ['Datetime PHP', new DateTime('2021-02-06 21:07:00')],
    ['String', 'Long UTF-8 String in autoresized column'],
    ['Hyperlink', 'https://github.com/shuchkin/simplexlsxgen'],
    ['Hyperlink + Anchor', '<a href="https://github.com/shuchkin/simplexlsxgen">SimpleXLSXGen</a>'],
    ['RAW string', "\0".'2020-10-04 16:02:00']
];
SimpleXLSXGen::fromArray( $data )->downloadAs('datatypes.xlsx');

Excel File will be corrupted but showing the content.

Seems it's downloading page also.

Hello there is a Problem with date's which are smaller then 1900 like '1876-01-30'
and also Problem with year "0000-00-00".
Last one I fixed it by
if ( $year === 0 || (int) $year == 0 ) {
return $excelTime;
}

I am using a framework called cakephp. The installation for composer completed successfully, but the SimpleXLSXGen class does not load automatically. I am attaching my composer.json file
composer.json.txt
.

I really love this extension because of it's simple and clear interface, but I got stuck on naming my sheets.

What I do is adding sheets in a loop and naming them after a database field. Unfortunately some of those fields are in ASCII and some in UTF-8. Adding them leads always to a corrupted xlsx or rubbish names or Standard Names (e.g. "Sheet 1").

I also tried to change all ASCII to UTF-8, or UTF-16BE but without any luck. What character encoding does the sheet take in Excel?

Чувак, это супер!
Как насчет простейшего форматирования текста в ячейке? "Адрес ячейки", "размер шрифта" и "жирный / курсив" было бы достаточно.
Или использовать встроенный стиль экселя в такой-то ячейке.

Fatal error: Call to undefined function mb_strlen() in **/SimpleXLSXGen.php on line 345
what can i do?

Hi Everyone!,

I am having the following problem when generating an excel (xlsx) whose fields that I generate contain strings with accents, for example -> ("José Antonio Ciprés Giménez"), The document is generated without problems, but when viewing the content of the excel, the fields with accents appear as follows in the excel ("JOSÃ� ANTONIO CIPRÃ�S GIMÃ�NEZ").

I use php and encode the strings with utf8_encode() functionm, if I don't use this function, the excel is directly generated with errors. Is there any configuration parameter or any solution for this problem? I'm a bit of a newbie to PHP hehe. Thanks for everything.

Is that possible?

First of all, thanks for this library, it's what I really was looking for: a lightweight single file library for basic xlsx building in PHP.

There is a little feature that is missing and is a must IMHO: being able to set cell text and background colors.

There could be a bunch of predefined basic colors defined in the internal styles.xml. Then user could just type:
['My label', '<red>My value</red>']
while adding cells to the array.

Hello,
I need to indent some text, basically I've tried to put some whitespaces at beginning of the cell value but I have noticed that cell values seems to be trimmed and the whitespaces become ignored.

Is there anyway to fix this issue or a different approach to indent text?

Thanks

Hello! Could you implement something like this inside the class? Thanks!

public function disableTypeDetection() {
		foreach ($this->sheets as &$sheet)
			foreach ($sheet['rows'] as &$row)
				foreach ($row as &$cell)
					$cell = chr(0) . $cell;
		return $this;
	}

P.S. Can't do pull requests, sorry

Thank you for this awesome and simple library.

I have set the encoding to UTF- 8.

The xlsx file returns only random 1 field because there is a "£" sign.

I know this issue is not with the library but may be it can be improved to handle this issue.

Hi, when I insert a date in the array it is automatically changed to 'm/d/yy' format in the generated excel file. How can I configure the format for the date?
thanks, sorry for my broken English.

Greetings -
The application that consumes the excel file I output says: "This file is not an Excel file (application/octet-stream)". I checked with this service: https://htmlstrip.com/mime-file-type-checker .
However in the class, it saves the file with the following header.

header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

This seems to be best practice, according to:
https://stackoverflow.com/questions/4212861/what-is-a-correct-mime-type-for-docx-pptx-etc

Can you please help me understand why this is being interpreted as "application/octet-stream"?

Thank you!

In my testing with OpenOffice, simply inserting a formula string doesn't do it.
E.g. =AVERAGE(B2:H2) inserted into a cell, is not inserted as a usable formula.

hi

how i can add Hyperlink in excel tables . i usedthis code but dose not work

`include('SimpleXLSXGen.php');
$books = [
['ISBN', 'title', 'author', 'publisher', 'ctry' ,'link'],
[618260307, 'The Hobbit', 'J. R. R. Tolkien', 'Houghton Mifflin', 'USA','=HYPERLINK("https://google.com","Site")'],
[908606664, 'Slinky Malinki', 'Lynley Dodd', 'Mallinson Rendel', 'NZ','=HYPERLINK("https://google.com","site")']
];

$xlsx = SimpleXLSXGen::fromArray( $books );

$xlsx->downloadAs('table.xlsx');`

Even if variable is actually a string, not integer.

This can be fixed by adding
$ct = 'inlineStr';
on line 192, but it seems to be an ugly hack and possibly should be fixed somehow better.

Hi !
I would love to help you out with this implementation but once I started looking at the code I realize I know nothing about XLS formatting.
Is something you would like to add in the near future?

The reason I use your implementation over PhpSpreadsheet is that yours is lightweight, simple and also they require PHP >= 7.2

Thank you!

how to export to xls ?

Hello,

When I put a URL in as hyperlink simplexlsxgen wants to replace %2F with a forward slash /. I need the %2F to stay as is because that's the way the URL is.

Is it possible to ignore %2F?

Thank you

It's excellent. solves my big problem

Hello,

I see output like \�&�x�5��;�*D��3��A� on page while tring to download excelsheet. Am I missing anything?

If I try to save a string like this '\"https://test.rexx.com/de/vpos\"' the file will be broken.

I didn't debug deep, but the commenting SimpleXLSXGen.php file lines from 499 to 502 helps.

Files generates ok, but problem is when is downloaded. Downloaded file is corrupted and can not be opened. (MS Excel shows message about corrupted file and offers repairing, what doesn't help)

$xlsx = create_event_xlsx($event);
$xlsx->saveAs($event['title'] . '.xlsx');         // ok
$xlsx->downloadAs($event['title'] . '.xlsx');     // corrupted file

Hi ;)
Is good add test de performance in README.md
Example

Simil to report https://github.com/mk-j/PHP_XLSXWriter

Hello,

I'm trying to export data from database. If I don't have any hyperlinks in the data the export will be generated and can be opened successfully with Excel.

When the data has hyperlinks with parameters like mydomain.com/param1=xx&param2=ss... then xlsx is generating the data without problem, but when I try to open with Excel, getting an error, the file is corrupted, would you try to repair, if yes it tries but without success. The file can't be opened.

I tried almost everything, converting data ti UTF-8, some other encodings, didn't work.
If I'm using urlencode for the link, than Excel can open the file, but the link it looks like param1%2Fxx%3D%2Fss and so on, every special character will be encoded.

My clients are not accepting those caracters,
what would I do?

Hey, thanks for the awesome class. I'm having issues trying to open the generated file though for some reason :(

I can get the file to export my xlsx file with no problems on my localhost (XAMPP), but my production server gives me an error when trying to open the xlsx generated on there.

I am loading via composer, and I'm trying to open the file in the latest Excel. Any idea why it might not be letting me open it?

UPDATE: There was some code executing after that I didn't realise. I put exit; after my download and it worked fine. Thanks!

How to make borders?
Как сделать границы?

When i am doing it like described in your quickstart info Excel throws an error when try to open the generated xlsx file via download or downloadAs...
NOT OK:
$data = [['Integer', 123],['Float', 12.35]]; $xlsx = SimpleXLSXGen::fromArray( $data ); $status = $xlsx->downloadAs('export.xlsx');

Excel can "fix" the bug all data are visible...

But when i am doing it like this:
$data = [['Integer', 123],['Float', 12.35]]; $xlsx = SimpleXLSXGen::fromArray( $data ); $status = $xlsx->saveAs('export.xlsx');

and open / or download the file the error is not not thrown.
My Workaround now is to create the excel and then download it manually with a button.

This happens on my local environment (MAMP Pro and PHP 7.4)

How do I adjust the height of a row?

Hi,

I've encountered an issue with regards to DateTime objects out of a database.
It happens on line 354: ( $vl = mb_strlen( (string) $v ); )
This results in a fatal error.

By proof of concept I've added this code, and it stopped failing.
if ($v instanceof DateTime) {
$v = $v->format('Y-m-d H:i:s');
}

Could support for DateTime objects be added?
I rather have to update my composer packages then manually update the classe with the changes you've made.

Is there any way to format text inside a cell, or tags like <b> or <i> will always affect whole cell instead of one word?
I think that something like "['<b>Hello</b>' . 'world']," should be possible