Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Issue creating excel from php

Posted on 2016-08-02
20
Medium Priority
?
53 Views
Last Modified: 2016-08-09
See attached php.

The purpose of this program is to query a table in a MySQL database & create an excel spreadsheet of the selected info for downloading. The technique used here I have been using for many years and worked here until yesterday or VERY recently.

When this runs, it opens the window for downloading the created file; if opened in Excel, it is empty.

To debug, I removed the top 3 php lines. It runs & generates an html table with the correct data, as it should.

It is possible I MIGHT have messed up the syntax of the top 3 lines.

Can someone tell me what is wrong?

Thanks
gen_excel_real.php
0
Comment
Question by:Richard Korts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 6
  • 2
20 Comments
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41739257
Can you post the downloaded file.
0
 

Author Comment

by:Richard Korts
ID: 41739337
Download File attached.
usage2016-08-01_03_41.xls
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 600 total points
ID: 41739456
It looks like this programming does not create an XLS spreadsheet.  Instead it appears to create an HTML document.  Is is possible for you to create the SSCCE, using a smaller test case so that you can isolate the failure?  If we can see the isolated failure we might have a better chance of offering a suggestion to fix it!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Richard Korts
ID: 41739480
Ray,

Do you mean less data or a shorter php program?

FYI, I have been using this EXACT technique for years. It works today for other clients & worked for this one as recently as July 21. To my knowledge, I made no changes since then.

The Host (Chihost) said there was a php upgrade on July 24 but it did not mention anything about this. Here is the change log.

http://php.net/ChangeLog-5.php#5.5.38

Richard
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 41739514
Less data.  This file has over 25,000 lines of code.  That's too much to wade through.  When I save the file with an XLS extension, it cannot be opened by Excel.

Can ChiHost give you a file extension that will run a backlevel version of PHP?  If so, you might be able to get before-and-after comparisons.  That might help isolate the changes that caused the issues.
0
 

Author Comment

by:Richard Korts
ID: 41739529
Ray,

I will generate a smaller file.

Great point about a backlevel version; I will ask them.

Thanks,

Richard
0
 

Author Comment

by:Richard Korts
ID: 41739566
Ray,

Terry at Chihost said this: There isn't a way to rollback to a previous "release" version.

I will generate a small output file & post it this PM.

Richard
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41739578
I suspect your problem is this - based on the sample file you uploaded.

That file has the following on the first line
qry = SELECT * from cvisitor where (SUBSTRING(last_time,1,10) >= '2016-05-01' and SUBSTRING(last_time,1,10) <= '2016-08-01') and vtype = 'R' order by last_time<br><!DOCTYPE html>

Open in new window

This is being output by the following line (line 47)
echo "qry = " . $qry . "<br>";

Open in new window


After removing this from the output - Excel opens the file.
0
 

Author Comment

by:Richard Korts
ID: 41739651
Julian,

Still does NOT work for me.

See attached.

Ray this is a small file, if not small enough, I can do just one day.
usage2016-08-02_02_11.xls
0
 

Author Comment

by:Richard Korts
ID: 41739825
I have another customer, also hosted in the same place where I used the IDENTICAL technique to generate Excel reports.

I just ran one, it failed in the same way. In that case, the program (php file) was last changed on 12/6/2014.

I think this is where I first found the method I am using: http://webcheatsheet.com/php/create_word_excel_csv_files_with_php.php

Richard
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41740154
Excel opens that file for me  - it gives a warning but if you say yes to the warning the file opens.

Is this not perhaps an issue with Excel?

Are you open to doing a minor re-write on the code? If so you might consider converting over to Excel XML format. Not too different from what you have right now so code change would be fairly small.
0
 

Author Comment

by:Richard Korts
ID: 41740645
Julian,

Yes, when it works, it gives me that warning too.

I have another customer, also hosted in the same place where I used the IDENTICAL technique to generate Excel reports.

I just ran one, it failed in the same way. In that case, the program (php file) was last changed on 12/6/2014.

Excel works for everything else.

I am willing to try a minor re-write. Can you provide details?

Of course I can't understand why I have to change something that worked 10 days ago. I am 95% certain that the php upgrade on the host is the cause.

Richard
0
 
LVL 59

Accepted Solution

by:
Julian Hansen earned 1400 total points
ID: 41741023
Ok before we deal with the rewrite - I am confused because the file you posted opens for me - are you saying that that same file does not open for you (or your clients)?

The XML Excel format.

Basically what I would do is save one of your existing files as a .XML file (Using the Save As .XML in Excel).

You can then edit the file and chop out the header bits - the bits that don't change - and paste these as the header and footer bits in your code.
You can then look at how the rows are generated and just adapt your HTML table code to output the equivalent XML code - the methodology is exactly the same - only slightly different markup.
If you get stuck post back and I will demonstrate.

First up though I am interested to know if the file you posted earlier works or fails - and if it fails how does it fail - as I mentioned before it opens fine for me.
0
 

Author Comment

by:Richard Korts
ID: 41741446
Julian,

The file does NOT open for me. It does open for you & the Web Host. I download it (rather than open directly in Excel), then open in excel from the download folder.

I just a few minutes ago emailed it to the customer & asked them to open it in Excel.

I'm beginning to think it's my Excel.

Richard
0
 

Author Comment

by:Richard Korts
ID: 41741461
Julian,

I emailed to my customer. He cannot open it either (in Excel).
0
 

Author Comment

by:Richard Korts
ID: 41741542
As another piece of data, the host suggested I download OpenOffice & try it there.

That works. But so what?

My customers use Microsoft Excel.
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41741766
So, if I understand correctly - this is something specific to the client in terms of their specific instance of Excel?

If so the question is what changed on their side. If it is not reversible then I recommend the XML route described above.
0
 

Author Comment

by:Richard Korts
ID: 41749024
The problem is not solved.

The EXACT same technique is used on another host for a variety of reports. I asked the customer to try one, it worked fine.

A DIFFERENT customer on the same host experiences the same problem.

I am converting the method to generate a csv file.

There is no reason that has been explained to me why this worked for years & now doesn't.

I'm awarding points for this question based on level of effort.

Thanks,

Richard
0
 

Author Closing Comment

by:Richard Korts
ID: 41749026
No solution, points awarded based on effort.
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41749063
Thanks Richard, but I believe this is something specific to Office at the client's location.

The fact that your code base has not changed and the generated files can be opened by others using Excel - it points to a problem local to the client.

I expect it has something to do with an Office update that was applied. I have turned updates off and have not updated office for some time - which might explain the issue.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question