Skip to content →

PHP Excel Master Posts

How to use formulas?

Setting a formula to a Cell

Setting a formula in a Cell

PhpSpreadsheet parses everything that starts with = as a formula, and to get the value of a cell that contains a formula you use the function getCalculatedValue()

If you want to use ‘=’ as part of a string and not to be parsed as a formula, you will need to escape
To do this, you need to “escape” the value by setting it as “quoted text”.\

Locale Settings for Formulae

You can also create a formula using the function names and argument separators appropriate to the defined locale; then translate it to English before setting the cell value:
Currently, formula translation only translates the function names, the constants TRUE and FALSE, and the function argument separators.

Formula pre-calculation

Performing formula calculations

Formulas involving numbers and text

Formulas involving numbers and text may produce unexpected results or even unreadable file contents. For example, the formula =3+”Hello ” is expected to produce an error in Excel (#VALUE!). Due to the fact that PHP converts “Hello ” to a numeric value (zero), the result of this formula is evaluated as 3 instead of evaluating as an error. This also causes the Excel document being generated as containing unreadable content.

Disable Cache

Will disable calculation caching, and flush the current calculation cache.

Flush Cache

If you want only to flush the cache, then you can call

Leave a Comment

Reading data from a spreadsheet

Reading data from a spreadsheet can be done in multiple ways:

  1. Read a file using automatic file type detection
  2. Read a file by specifying the file type
  3. Read a file and opening specific worksheets
  4. Read a file in read-only mode
  5. Read a file and open only specific cells

How to read a file, by automatically detecting the file type

You should use this when you don’t know what file format the user will load.

How to read a file by specifying the file type:

This is faster than using the automatically detecting file type.

How to load only specific worksheets

How to open a file in read-only mode

How to read specific cells:

The following code will only read row 1 and rows 20 – 30 of any sheet in the Excel file:

Leave a Comment

How to install PhpSpreadsheet?

The simplest way of installing PHPSpreadSheet is to use Composer.

Here are the requirements for running this library:

  • PHP 5.6 (min version)
  • PHP extension php_xml enabled
  • PHP extension php_gd2 enabled (if not compiled in)
  • PHP extension php_zip enabled

The PHP extensions should be already enabled, with a standard PHP installation(I didn’t have to enable any extension when I was installing it for the first time).

To install the library we will use Composer:

If you don’t have composer installed on your current machine, here is a how to install it.

After the process was finished, you can test if it’s working correctly using this simple code:

This should generate a new spreadsheet that looks like this:

Leave a Comment

Niching down as a PHP Developer

As a PHP developer, choosing a small subject to specialise in is a crazy idea. At my day job, I work with multiple simple languages and fix bugs in the frontend and backend and sometimes in the middle.

As a PHP developer, you have a lot of freedom in choosing how a feature should be architected and implemented. You can do it Object Oriented or NOT! You can have a clear separation between the code that gets the data(Model) and the code that contains the logic(Controller), or you can just write everything in a single file without a specific order or structure.

We are used to having a lot of options, we enjoy that feeling and we don’t want to lose that freedom. In Java or Swift, there are a lot more constraints that the dev needs to follow, just to get the application to compile:

“Compiler error: The function do_something($float) expected a Float to be passed as a parameter, Int given!”

In PHP there is none of that malarky. You can compare a string with an int ( ‘1’ == 1 ) and will work as expected.

At no point in my working experience did I feel like:
“You know I am working on too many things, I really should just pick one area and just work on the models of a project!”

As a developer, I always want to improve and get better at my job. And a common way of improving as a developer is learning a new language. I mean I already work with a couple of languages already, but I am sure that if I add Python to my programming toolbelt, that’s gonna make all the difference.

Learning Python(or any other language that you don’t know yet, but you have heard about) will totally take me from a common unknown developer to a rockstar developer that is hunted by companies.f

And the programming community is full of people who know 20 languages and yet are struggling to get a decent pay increase or to find a job that they are excited about.

Don’t get me wrong, I think that learning a new language will help any developer improve, but the timing is crucial. If you are average at 5 programming languages, adding a new one will not help you that much.

However if you dig deep into one language, and become better then 99% of the other dev in that single subject, you differentiate yourself from the crowd.

How I became a better developer in the past?

I used the same process to become a better developer. When I look back at my skills and how they evolved, I can tell you that when I worked with PHP at my day job on my side projects and I was coached and received code reviews on the PHP I was writing, I improved a lot. Yes, it was a little bit frustrating and difficult to take the code reviews, but I didn’t ignore them.

In a couple of months, the quality of the php code I was writing improved. After I got better at writing PHP, I easily improved at the other languages I was working with, Javascript and SASS. The process was a lot easier and faster because I was replicating what I learned in PHP. You may say that PHP and SASS, don’t have much in common, but the basic principles apply to all the languages.

This is why I want to niche down. I have seen the advantages of focusing on a single language, and how that helped me. So I want to go a step further and focus on a specific area of a language, and after I master that, I can apply the knowledge that I acquired, to whatever project I am working on.

Leave a Comment

Working after work!

It’s 17:12 and I just left the office. I have been thinking that I should start my blog now, or start writing an article now. I had this thought in my mind since yesterday. After lunch, I wasn’t so keen on writing, but I still thought that I would do it.

As the hour to leave work came closer and closer, I became more bored, angry at what code I was writing and tired of figuring out why half of the things I wrote produced only errors.

But this is nothing special, I feel like this every time I leave the office after 8-9 hours of writing code at my day job. You would think that I would get used to this process and not give it much importance.

I wanted to write something yesterday after I got home. But I did everything possible to avoid it. I took the trash out, cooked, washed some clothes and watched a movie. After all this procrastination, I went to sleep and I was thinking that tomorrow I will start writing this article, and that it wouldn’t take much effort and that I would enjoy it.

I am not sure if this feeling is experienced especially by programmers because we are paid to use our brains, and writing an article or working on a side project, you guessed it, it requires some concentration.

The last statement seems reasonable to me, but I don’t think it’s the full story. I have lighter days, but when I leave the office I still feel the same, as if I spent the last 9 hours extinguishing big fires.

I just left the office for today, so I should be feeling tired at this moment!

Based on this observation, I think that leaving the office gets us into a mental state where we don’t want to do anything productive. I mean it’s obvious, we just finished “work”, we are supposed to be tired and relax until tomorrow. To me this is an automatic response, that we learn by having a day job for a long period of time.

How did write this post? Even if I feel doing nothing?
SIMPLE! I haven’t left the office yet. After I finished my work for today, I just took my personal computer took a seat on a comfortable couch and started putting my thoughts of procrastination into this article.

Leave a Comment