Read and write xlsm file in php

Asked

Viewed 647 times

2

I have a very big hand problem: I have an xlsm file with macros to read, write and finally download.

It is a 4 MB file with inline formatting and images.

I’ve been using Phpexcel only I haven’t been able to read the file completely because it gives me the following error: "Out of memory".

The goal is not to read and write all sheets in the file. I don’t need to mess with macros or anything.

Know the best API or best way to read and write this type of file?

Usability and use of a linux server.

php laravel excel macro

  • The answer solved the problem?

  • No. I solved my problem differently. I had to develop a mini Java application in order to fill the cells in the xlsm file.

1 answer

3

Generate files with PHPExcel or even simple things may cause excessive use of memory, this may be caused by the library or it may be caused by the way you wrote the codes or the file in the input can be very big.

Like loops "botched", not set NULL in references that will not use, is something too long to discuss.

Let’s go to the fastest solution, the error:

PHP Fatal error: Out of memory (allocated 49545216)

This occurs when you exceed the memory limit set by php.ini, one solution is to edit php.ini and restart the http server (apache, ngnix, lighttpd, etc.), open php.ini and look for this memory_limit, then edit to:

memory_limit = 128M

And restart the http server.

If the problem still exists it is because for some reason the script is consuming more than 128mb, if this occurs it means that there is a serious problem in your script, which can be simple or not to solve.

So you may have to resort to alternative solutions like apps that can run as exec php, or front-end solutions like Html5 (if the browser supports) or even does not use xlsm and start using a new format that is lighter and portable.

Using javascript

You can try to make use of the https://github.com/SheetJS/js-xlsx it supports XLSX / XLSM / XLSB / XLS / Spreadsheetml (Excel Spreadsheet) / ODS.

Using javascript you can make use of the client’s machine memory instead of the server, because the application will run on the front end.

Add this to your page:

<script src="dist/xlsx.core.min.js"></script>

I can’t tell if he has control over macros, or things like that, although even with phpexcel this would be difficult. So the idea is to use js-xlsx or change everything and completely stop using office files.

Reading an XLSM (with ajax):

var url = "pasta/formula_stress_test_ajax.xlsm";

var oReq = new XMLHttpRequest();

oReq.open("GET", url, true);

oReq.responseType = "arraybuffer";

oReq.onload = function(e) {
  var arraybuffer = oReq.response;

  /* convert data to binary string */
  var data = new Uint8Array(arraybuffer);
  var arr = new Array();
  for(var i = 0; i != data.length; ++i) arr[i] = String.fromCharCode(data[i]);
  var bstr = arr.join("");

  /* arquivo pra manipulação */
  var workbook = XLSX.read(bstr, {type:"binary"});
}

oReq.send();

Creating an XLSM:

var wopts = { bookType:'xlsm', bookSST:false, type:'binary' };

var wbout = XLSX.write(workbook,wopts);

Links about the lib

Browser other questions tagged php laravel excel macro

You are not signed in. Login or sign up in order to post.