Solved

PHP -> Excel

Posted on 2004-09-21
7
1,209 Views
Last Modified: 2008-02-01
I need a good class to build an XLS spreadsheet from PHP.
I now use the PEAR's class which does a great job, but its problem is that it still does not support unicode and I need to use chinese symbols in it.
I saw that this class is a port of a CPAN class that does support unicode.
The best solution would be if someone had already ported the unicode support into this PEAR class, but even if you could suggest another class with comparable functionality it would be fine.
0
Comment
Question by:alkalined
[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
7 Comments
 
LVL 15

Assisted Solution

by:nicholassolutions
nicholassolutions earned 20 total points
ID: 12124167
you could use a pipe to a Perl script that uses the CPAN module to create the XLS. It may not be ideal, but I think it would be the least work for you and allow you to do what you want fairly quickly. You're not the only person requesting unicode (and other charset) support, so they will probably provide it in an upcoming version.

Cheers,
Matt
0
 
LVL 1

Author Comment

by:alkalined
ID: 12130619
I hope they do release another version.. There has been more than 3 months since the last one.

What do you mean by using a pipe? Should I write a Perl script that connects to the database, retrieves the data and writes the XLS?
Because that would be really inconsistent and inconvenient :(

..or is there a way to just use the perl class from within the php code?
0
 
LVL 1

Assisted Solution

by:NchMch
NchMch earned 20 total points
ID: 12136824
The alternative solution would be to use CSV file format - it's pretty simple to generate and the Unicode support will depend only on application used for opening the generated file. Here's the example:
<?php

$foo = array
(
  'one'   => 'two',
  'three' => 'four',
  'five'  => 'six'
);

$csv_data = '';

foreach ($foo as $key => $val)
{
  $csv_data .= "$key;$val\r\n";
}

header('Content-type: application/ms-excel');
header('Content-Disposition: attachment; filename=data.csv');
echo($csv_data);

?>

This will generate file called "data.csv", which you may open in Excel like a regular spreadsheet.
0
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 
LVL 6

Accepted Solution

by:
peyox earned 40 total points
ID: 12139807
Hi Alkalined,

You can also try to save regular HTML table as .xls file. You can add formatting to it, but formulas would be difficult to implement (but possible). Such a "fake" excel files may be easily opened by excel with no difference for users. Another approach is to use XML to create excels, this approach is more powerful than HTML tables, however this still not a real excel file.

The advantage of methods described above, is that you can use unicode to populate your excel sheets.

Cheers.
0
 
LVL 2

Assisted Solution

by:harveykane
harveykane earned 20 total points
ID: 12173974
I have just been down this road of generating Excel from PHP.

I tried a few different methods (including PEAR) and ended up writing a Delphi dll (you can access via CGI or COM+ on windows) using the Flexcel component from www.tmssoftware.com (I think they have VB and C++ versions too).

This was a difficult solution, but it does have a couple of nice points. The main one being, you create your excel templates in excel itself (as opposed to coding each cell like in PEAR) and within the template define variables that will be replaced with actual data.

I have no idea if this solution supports various character sets, but I found it the most flexible method (and also most difficult to implement).
0
 
LVL 1

Author Comment

by:alkalined
ID: 12177296
Come on guys, so far nobody has offered an acceptable solution without so many cons..
- nicholassolutions: I need to retrieve data from database from a perl script,leading to inconsistency of code - unacceptable
- NchMch: yes, there is no unicode problem with this but i cannot add formatting and I need a beautiful report - unacceptable
- harveykane: this truly sounds like the most complicated solution that can exist. And it doesn't answer the real problem: entering chinese - hardly acceptable
- peyox: maybe the best solution so far. Although it's actually not an XLS :( . Also when I tried it all borders were gone but I guess it's just a matter of tricking it.. - probably acceptable

I actually found a solution myself, in pure php, and it did what I needed...
Even more, a friend of mine suggested me another solution, php again, so I can even use templates to fill in.
So I'll give you guys a couple more days to see if someone can fully deserve the points. Else I think I'll give the points to peyox or maybe split them somehow.
0
 
LVL 1

Author Comment

by:alkalined
ID: 12235065
Obviously no more input will appear..

What I did was to modify the beginning of function writeStringBIFF8 in Worksheet.php to:
     $str = iconv("UTF-8","UTF-16LE",$str);          
     $strlen = strlen($str) / 2;
     $encoding  = 0x1;

..and ported function writeUnicode from the CPAN class
Then added the following code to my script:

//not sure which one of the 3 lines below does the job
mb_internal_encoding("UTF-8");
iconv_set_encoding('output_encoding',"UTF-8");
iconv_set_encoding('internal_encoding',"UTF-8");            

$xls =& new Spreadsheet_Excel_Writer();
$xls->_codepage = '65001';
$xls->SetVersion(8);

It worked for me, although I'm not entirely sure how.. :)
However, until the next version appear, it seems like a satisfactory patch.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

630 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