Verification: a143cc29221c9be0

Php append to file enter

Php append to file enter

XML vs CSV

CSV format for import is only good if Excel Table format is NOT required. You cannot have the alternate rows highlighting, column headers available during scroll, etc. in summary, for what i found you cannot have external data and table format at the same time.

The error you will get converting the data connection table to "Format as table" is this:

 Your selection overlaps one or more external data ranges.
 Do you want to convert the selection to a table and remove
 all external connection?

Of course, I do not want that.. but using xml format for import data connection... the table format can stay like this screenshot:

xml data imported from file to Excel

And, when you scroll past one screen full.. the screen will look like this.. you can see the column headers A, B, C is automatically converted to the data header (ISBN, date, Title, ...)

scrolling the connected xml data past one screenfull

So, we should go for an xml solution.. which we are figuring out how using php. But, using CSV for testing purposes with about 10 columns x 20,000 rows data.. it works very nicely. The refresh from web server also works very well.

Put All Piece Togeter

I have had an internal discussion with my team and we think this will really work for us. Now, we are brainstorming on the delivery and flow of user actions to deliver the Excel template reports with data which he generated from php.

Ok.. What we have in start?

  • An Excel Template (e.g. staff_analysis_template.xlsm). This will will have all the pivot table, excel macros, buttons, images, charts, everything an excel file can have in there.
  • In the Template file there will be one sheet named "data". This is where the xml or csv import will end up being... readily available for all the pivot tables and charts in the file.
  • A php webpage which will have list boxes and text fields for the user to filter on the data.. and he will click search... and this will provide a web page result and and a link to view the same data in the excel template file with all the resulting data in sheet: data.

Here is the Excel Template with sample data.. in this screenshot... you can see

  1. Sheet: data where the xml data generated from php will be connected and end up in.
  2. Sheet: summary. where all the formulas in Excel can be used... and presented to the user which is based on the data.
  3. Sheeet: Pivot Can have pivot tables and charts which is based on the data and can be made to automatically refresh when the file is opened..
  4. VBA is also available.. for example the button can be used to refresh the data or to do any other work.

We can basicially use everything Excel has available for us. The resulting xml file can be left on the webserver.. and also data connection can be an URL also or as a file.

sourceFile="http://xxxxx/test_data3.csv" or

sourceFile="C:\Users\Fiaz\Documents\Magic Briefcase\Learn\excel\test_data3.csv"

enter image description here