473,549 Members | 2,723 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

PHP to create CSV that Excel can read

I am using PHP 4.3.2 to create a CSV file, however, Excel constantly
views it as a single-column spreadsheet with everything in quotes,
whereas OpenOffice Calc views it as a legitimate spreadsheet in
separate columns/cells.

[PHP]
class ReportGenerator {

/**
* Generate content based upon type
*
* @access private
* @param mixed $type type of content (e.g. Excel, CSV, Word, etc)
* @return mixed formatted content
*/
function &generateConten t($type) { // STATIC STRING METHOD
$result = $this->getResult();

switch (strtolower($ty pe)) {
case 'excel':
$delimiter = '</td><td valign="top">';
$lb = "\n</tr>\n<tr>";
break;
case 'csv':
$delimiter = ',';
$lb = "\n";
break;
default: // DO NOTHING
break;
}

if ($result && strcmp(strtolow er($type), 'csv') != 0) $content =
'<table><tr>';
if ($result) $content .= $this->generateHeader s($type) . $lb;

$isFirstRow = true; $isEndOfFirstRo w = true;

for ($i = 0; $i < @sizeof($result ); $i++) {
$header = @array_keys(get _object_vars($r esult[$i]));
if (strcmp(strtolo wer($type), 'csv') != 0 && $isFirstRow) {
$isFirstRow = false;
$field = '<td valign="top">';
}
for ($j = 0; $j < @sizeof($header ); $j++) {
if (strcmp(strtolo wer($type), 'csv') == 0) {
$field .= '"' . nl2br(preg_repl ace('/\r/', "\n",
preg_replace('/[\t]/', ' ', str_replace('"' , '"',
$result[$i]->$header[$j])))) . '"';
} else {
$field .= nl2br(preg_repl ace('/\r/', "\n", preg_replace('/[\t]/',
' ', $result[$i]->$header[$j])));
}
$content .= $field . $delimiter;
$field = '';
}
if (strcmp(strtolo wer($type), 'csv') != 0 && $isEndOfFirstRo w) {
$isEndOfFirstRo w = false;
$content = preg_replace('/<td valign="top">$/', '', $content);
}
$content .= $lb;
$field = '';
$isFirstRow = true; $isEndOfFirstRo w = true;
}

if ($result && strcmp(strtolow er($type), 'csv') != 0) $content =
preg_replace('/<tr>$/', '', $content) . '</table>';

return $content;

}

}
[/PHP]

This class contains a method that will generate the CSV-formatted
content, however, it again fails to produce legitimate content in
Excel while producing just-fine content in OpenOffice.

Help!

Thanx
Phil
Jul 17 '05 #1
9 21814
Phil Powell wrote:
I am using PHP 4.3.2 to create a CSV file, however, Excel constantly
views it as a single-column spreadsheet with everything in quotes,
whereas OpenOffice Calc views it as a legitimate spreadsheet in
separate columns/cells.
*snip* This class contains a method that will generate the CSV-formatted
content, however, it again fails to produce legitimate content in
Excel while producing just-fine content in OpenOffice.

Help!

Thanx
Phil


Are you sending the appropriate http headers? I've used:

header ("Content-Type: application/msexcel");
header ("Content-Disposition: attachment; filename=\"file name.csv\"");

followed by a stream of comma separated text and it just works, in both
excel and oo.o

Be aware that you DONT want the first bytes in the csv to be "ID", Excel
treats the file as a .slk no matter what you tell it it really is. (THAT
took me a while to figure out! Doh!)

Sacs

Jul 17 '05 #2
.oO(Sacs)
Are you sending the appropriate http headers? I've used:

header ("Content-Type: application/msexcel");


Shouldn't that be application/vnd.ms-excel instead?

According to <http://www.iana.org/assignments/media-types/application/>
application/msexcel is not a registered MIME type.

Micha
Jul 17 '05 #3
Michael Fesser wrote:
.oO(Sacs)

Are you sending the appropriate http headers? I've used:

header ("Content-Type: application/msexcel");

Shouldn't that be application/vnd.ms-excel instead?

According to <http://www.iana.org/assignments/media-types/application/>
application/msexcel is not a registered MIME type.

Micha


Err, yes, it probably should be vnd.ms-excel :-)

application/msexcel does work though, for oo.o too, and thats the only
requirement I had at the time...

Thanks for pointing that out!

Sacs

Jul 17 '05 #4
In article <df************ *************** *****@4ax.com>,
Michael Fesser <ne*****@gmx.ne t> wrote:
Are you sending the appropriate http headers? I've used:

header ("Content-Type: application/msexcel");


Shouldn't that be application/vnd.ms-excel instead?

According to <http://www.iana.org/assignments/media-types/application/>
application/msexcel is not a registered MIME type.


THANK YOU for this URL! It's a great mystery to me if I'm using the
right mime type when I download things. This cleared up a lot. I do
note that Macintosh IE tends to use "octetstrea m" while all the other
browsers use "octet-stream". Anyone know why?

--
DeeDee, don't press that button! DeeDee! NO! Dee...

Jul 17 '05 #5
ph************* *@gmail.com (Phil Powell) wrote in message news:<b5******* *************** ***@posting.goo gle.com>...
I am using PHP 4.3.2 to create a CSV file, however, Excel constantly
views it as a single-column spreadsheet with everything in quotes,
whereas OpenOffice Calc views it as a legitimate spreadsheet in
separate columns/cells.

[PHP] <snip> if (strcmp(strtolo wer($type), 'csv') == 0) {
$field .= '"' . nl2br(preg_repl ace('/\r/', "\n",
preg_replace('/[\t]/', ' ', str_replace('"' , '"',
$result[$i]->$header[$j])))) . '"';

I have no clue, why you use nl2br() here. Also, what's the point in
str_replace('"' , '"',..) ??

Perhaps you may want to try <http://in2.php.net/fputcsv> (CVS?) or
<http://in2.php.net/fgetcsv#14788>. The later worked very well for me
in many cases.

--
| Just another PHP saint |
Email: rrjanbiah-at-Y!com
Jul 17 '05 #6
Michael Vilain wrote:
I do note that Macintosh IE tends to use "octetstrea m" while
all the other browsers use "octet-stream". Anyone know why?


'Octetstream' is an unregistered subtype of the application
top-level type, and should therefore be prefixed with 'x-';
'octet-stream' is the registered subtype. The former is
more likely to cause the browser to ask its user what to do.

Note that browsers use whatever MIME media type they're
given, unless they're trying to guess the media type of a
file to be uploaded.

--
Jock
Jul 17 '05 #7
ng**********@re diffmail.com (R. Rajesh Jeba Anbiah) wrote in message news:<ab******* *************** ****@posting.go ogle.com>...
ph************* *@gmail.com (Phil Powell) wrote in message news:<b5******* *************** ***@posting.goo gle.com>...
I am using PHP 4.3.2 to create a CSV file, however, Excel constantly
views it as a single-column spreadsheet with everything in quotes,
whereas OpenOffice Calc views it as a legitimate spreadsheet in
separate columns/cells.

[PHP]

<snip>
if (strcmp(strtolo wer($type), 'csv') == 0) {
$field .= '"' . nl2br(preg_repl ace('/\r/', "\n",
preg_replace('/[\t]/', ' ', str_replace('"' , '"',
$result[$i]->$header[$j])))) . '"';

I have no clue, why you use nl2br() here. Also, what's the point in
str_replace('"' , '"',..) ??

Perhaps you may want to try <http://in2.php.net/fputcsv> (CVS?) or
<http://in2.php.net/fgetcsv#14788>. The later worked very well for me
in many cases.


Because. according to http://www.php.net, "fputcsv" is still in CVS,
and fgetcsv is useless to me since I'm doing a conversion to csv.

This works just fine again when opened in OpenOffice, but comes across
formatted incorrectly in Excel XP. I was told, however, you could
import the CSV instead.

Phil
Jul 17 '05 #8
Just maybe a stupid remark, but if you go to:
start => settings => control panel => regional options => numbers =>
list eperator and add ; (semicolon)you get it right

"John Dunlop" <us*********@jo hn.dunlop.name> wrote in message
news:MP******** *************** *@News.Individu al.NET...
Michael Vilain wrote:
I do note that Macintosh IE tends to use "octetstrea m" while
all the other browsers use "octet-stream". Anyone know why?


'Octetstream' is an unregistered subtype of the application
top-level type, and should therefore be prefixed with 'x-';
'octet-stream' is the registered subtype. The former is
more likely to cause the browser to ask its user what to do.

Note that browsers use whatever MIME media type they're
given, unless they're trying to guess the media type of a
file to be uploaded.

--
Jock

Jul 17 '05 #9
ph************* *@gmail.com (Phil Powell) wrote in message news:<b5******* *************** ***@posting.goo gle.com>...
<snip>
I have no clue, why you use nl2br() here. Also, what's the point in
str_replace('"' , '"',..) ??

Perhaps you may want to try <http://in2.php.net/fputcsv> (CVS?) or
<http://in2.php.net/fgetcsv#14788>. The later worked very well for me
in many cases.


Because. according to http://www.php.net, "fputcsv" is still in CVS,
and fgetcsv is useless to me since I'm doing a conversion to csv.


Did you click both the links? *Read* the contents of the links?

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com
Jul 17 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
41157
by: I Decker | last post by:
Hi all, Hope this is the right group. I am writing a program in c# to open create an excel document, enter some data, save it and then email it as an attachment. I have successfully created an excel document which the user can see (at this stage of development) and passed some data to it. I then used the savas method to save the file....
6
9338
by: Steve Richter | last post by:
I am getting error in a vbscript: ActiveX component cant create object: Excel.Application. The vbscript code is: Dim objExcel Set objExcel = CreateObject("Excel.Application") I am pretty sure it is a permission issue because the script works when I point the browser directly at the .htm file on the c: drive:...
0
1002
by: Johnny Fugazzi | last post by:
Basic question. I am working on an app that needs to open an excel file an read out data. Have posted a little about this before. The excel file is secured and the user cannot acces it. I can successfully impersonate a user and reach the share where the file is stored, but I then need to open that file in Excel. I am creating an Excel...
27
27344
by: jeniffer | last post by:
I need to create an excel file through a C program and then to populate it.How can it be done?
4
8529
by: =?Utf-8?B?UHJpeWE=?= | last post by:
Hi, I'm developing a webservice whicg reads data from a database and exports to a excel sheet. This applciation works if i have MS Office installed in my system . Since i'm unable to install MS Office in teh server is there any alternate way to program this. Thanks in advance Priya
1
3364
by: TG | last post by:
Hi! I have an application in which I have some checkboxes and depending which ones are checked those columns will show in the datagridview from sql server or no. After that I have 2 buttons: 1) export to excel button exports the visible columns from the datagridview to excel (this works fine)
11
2924
by: =?Utf-8?B?UGV0ZXIgSw==?= | last post by:
I am working with Visual Studio or alternately with Expression Web. I need to create about 50 aspx pages with about 1200 thumbnali images, typically arranged in three to four groups per page, having hyperlinks to the corresponding full size images. Can anybody point me to locations in MSDN or elsewhere giving the references to attach, the...
4
15400
by: =?Utf-8?B?Sm9zaW4gSm9obg==?= | last post by:
I could create MS Excel sheet using ASP.NET 2.0 with C# but it is not being created in some systems, following error occurs when the program compiles : Microsoft Office Excel cannot open or save any more documents because there is not enough available memory or disk space. • To make more memory available, close workbooks or programs...
15
9417
by: patf | last post by:
Hi - experienced programmer but this is my first Python program. This URL will retrieve an excel spreadsheet containing (that day's) msci stock index returns. http://www.mscibarra.com/webapp/indexperf/excel?priceLevel=0&scope=0&currency=15&style=C&size=36&market=1897&asOf=Jul+25%2C+2008&export=Excel_IEIPerfRegional Want to write python...
0
7450
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7470
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7809
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6043
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5368
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5088
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3500
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3481
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1941
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.