Read and write xlsm file in php

Question

Read and write xlsm file in php

Navigation

#1 by (3 votes)

2

I have a fairly large hand problem which is as follows: I have an xlsm file with macros to read, write, and then download.

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

I have used PHPExcel just because I have not 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 the sheets of the file. I do not need macros or anything.

Do you know the best API or best way to read and write this type of file?

Use laravel and use a linux server.

php macro excel laravel

asked by anonymous 13.04.2015 / 12:24

1 answer

Character count inside distinct paragraphs (jQuery) View control in Partial View conditionally

score 3 · Answer 1

Generating files with PHPExcel or even simple things can cause excessive memory usage, this can be caused by the library or it can be caused by the way you wrote the codes or the file in input can be very large .

As badly done loops , do not set NULL on references you will not use, it's a long question to discuss.

Let's take a quicker solution, the error:

PHP Fatal error: Out of memory (allocated 49545216)

This occurs when it exceeds the memory limit defined by php.ini, a solution is to edit php.ini and restart the http server (apache, ngnix, lighttpd, etc), open php.ini and look for memory_limit , then edit for:

memory_limit = 128M

And restart the http server.

If the problem still exists is because for some reason the script is consuming more than 128mb, if this does mean that there is a serious problem in your script, which may be simple or not.

Then you may have to resort to alternative solutions such as applications that can be run as exec f of php, or front-end solutions such as html5 (if the browser supports) or even do not use xlsm and start using a new format that is lighter and portable.

Using javascript

You can try using the link it supports XLSX / XLSM / XLSB / XLS / SpreadsheetML (Excel Spreadsheet) / ODS.

Using javascript you can make use of the memory of the client machine 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 not tell if he has control over macros, or the like, even though with phpexcel this would be difficult. So the idea is to use js-xlsx or change everything and totally 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 to lib

Other examples: link
Support details link