Newbie - Creating Spreadsheet code from existing spreadsheet - possible?

Topics: Developer Forum, User Forum
Sep 2, 2009 at 12:44 AM

Hi,

I have quite a visually complicated (but no formulas or anything like that) spreadsheet that is currently used by a company and I need to write code to generate it so rather then they manually typing all the data in it can be generated.

Looking at the spreadsheet I'm thinking it will take me quite a long time to write the code that gets the generated Excel spreadsheet looking like the one they aleady have.

Is there anyway of taking an exisitng spreadsheet and from that generating some PHPExcel code that would create the same thing. I'm not expecting a yes to this but I thought I would ask and perhaps at least learn some tips on how to speed things up?

Thanks in advance.

 

Developer
Sep 2, 2009 at 12:53 AM

>> Is there anyway of taking an exisitng spreadsheet and from that generating some PHPExcel code that would create the same thing.

Not at the moment. But there is a feature request for this:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=7550

>> Looking at the spreadsheet I'm thinking it will take me quite a long time to write the code that gets the generated Excel spreadsheet looking like the one they aleady have.

Would it not be possible just to load the existing workbook as a template using for example PHPExcel_Reader_Excel2007? Or did you try that, but it was too slow?

Sep 2, 2009 at 1:00 AM

Wow that was a quick response. Good to see what I have chosen is well supported.

Sounds interesing when you say

>>>"Would it not be possible just to load the existing workbook as a template using for example PHPExcel_Reader_Excel2007? Or did you try that, but it was too slow?"

However too much of a newbie to know how at this point.

So using this method would I just load it into memory as a template first, then make amendments to it (therefore mostly the actual data) and then product my spreadsheet?

The only thing is when the data is produced there is a lot of looping so from one spreadsheet to the next the data might be different (e.g. sales items if producing an invoice for example - one order might have one item the next order might have 100 items - based on the same spreadsheet but many differences because of the quantity of data).

but I guess if I can use insert row or something.

 

Remember I've very new to using this library.

 

Thanks again.

 

Developer
Sep 2, 2009 at 1:13 AM

>> So using this method would I just load it into memory as a template first, then make amendments to it (therefore mostly the actual data) and then product my spreadsheet?

Yes, for example, if you need to create invoices, what you would do is to have an invoice template file (invoice-template.xlsx) containing all the common stuff such as your company address, headers, logos etc. Perhaps also some formulas.

Then when you need to generate the actual invoice you load your template using e.g. PHPExcel_Reader_Excel2007, then you fill in the specific stuff such as invoice address/delivery address, product items etc.

>> but I guess if I can use insert row or something

Yes that is the idea.

Sep 2, 2009 at 1:22 AM

OK makes sense. I guess I will consider this method. I guess if I can create my own code that creates a spreadsheet from scratch this is better (right??) as then I do not have to rely on the template. so other then the time and easy editing of the base format a tempalte might save, if I got down to it and created the code that can generate the tempalte from scratch that is better, right?

 

I'll use the excuse again that I'm quite new to this so maybe asking basic stuff, just want to verify my assumptions.

Thanks again!

Developer
Sep 2, 2009 at 1:47 AM

>> as then I do not have to rely on the template

There are advantages using a template. For example, it is much faster to make changes in the tempate if you are confident with Excel.

>> if I got down to it and created the code that can generate the tempalte from scratch that is better, right?

It is not necessarily better to create code from scratch than using a template. I would say it is largely a matter of taste.

If you are worrying about speed, I think there is not much difference anymore. No matter the approach, virtually the same thing is happening under the hood.