How to export a web report to Microsoft Excel using PHP

Asked By Serena 460 points N/A Posted on - 05/17/2011

I am writing a PHP website that enables users to track time. I have a page that I want to download to Microsoft Excel.

The PHP page has a date range to select the start date and the end date. The user then can select a particular work or all workers and get the details. My hosting platform is Linux.

 

Is it possible to create a report that could be opened in Microsoft Excel?

• Status: Open
• Question Views: 1558
• Answer Count: 7
• Vote Up 24 Vote Down

• Answer Accepted: Yes
• Question Category: PHP

Best Answer by Ingrid

Go To Solution

Answered By Ingrid 0 points N/A #98523

How to export a web report to Microsoft Excel using PHP

 

You can create a file that can be opened in Microsoft Excel. On a Linux platform, it is not possible to create a native binary file which Microsoft Excel uses. Microsoft Excel file creation is not possible on a Linux platform as Linux cannot host or run a Microsoft Excel installation. There are no plugins released by Microsoft for creating a native Excel file in Linux.

 

The native Microsoft File format is not in the open domain. Therefore there are very few plugins that are written on the open source domain for Linux. You will need to use a third-party library distributed by such a software vendor and then interact with the PHP web program.

About Ingrid

Questions
0

Answers
17

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 05/17/2011
• Question Category: PHP

Answered By Edvard 0 points N/A #98525

How to export a web report to Microsoft Excel using PHP

 

You need to look for a third party program that could be executed inside Linux from the command line. The key thing to look for is, a batch program where you can pass parameters and leave it to generate the excel file. In PHP You can do this by using the EXEC command. According to the PHP documentation the EXEC command is as follows.

 

string exec ( string $command [, array &$output [, int &$return_var ]] )

 

The first parameter to pass the command to be executed inclusive of the parameters. The next parameter is optional. If you want to capture the output of the command, example messages printed to the console, you can pass a variable of type array.

 

The last parameter is the system code that is returned from the command. The third party command may return a code that you can interpret such as a status code. Similar to using the EXIT(21) in a PHP script, where the code you want to return to the operating system is specified.

About Edvard

Questions
0

Answers
16

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 05/17/2011
• Question Category: PHP

Answered By Mathias 0 points N/A #98526

How to export a web report to Microsoft Excel using PHP

 

A third party program can also be written in Java language and then installed in the Linux machine. You can then execute the java program to read and write Microsoft Excel files. Java is an open source programming language and you get many programs written on the public domain. Java has released a POI library. POI stands for Poor Obfuscation Implementation.

 

According to the Apache website the main mission of the POI project is to make Microsoft Office documents readable and writable using java. In the Apache-POI suite of libraries there is a class called HSSF. This (interestingly) stands for Horrible Spread Sheet File format.

 

Using the HSSF class you can create, open, write and manipulate Microsoft Excel spreadsheets. You can leverage a small java program and execute it to create a Microsoft Excel Spreadsheet.

About Mathias

Questions
0

Answers
16

Best Answers
2

• Vote Up 0 Vote Down

• Posted on - 05/17/2011
• Question Category: PHP

Answered By Serena 460 points N/A #98527

How to export a web report to Microsoft Excel using PHP

In a shared hosting environment, the PHP EXEC function is disabled. Furthermore, I am not allowed to use any third party utilities other than what is written based on PHP. Now I understand that it is impossible to create a native Microsoft Excel file in PHP.

 

Thank you experts for your valuable advice.

 

Is there any other file format that could generate that Microsoft Excel might open? Can I create a XML or a ASCII text file that could be opened by Microsoft Excel?

About Serena

Questions
2

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/17/2011
• Question Category: PHP

Answered By Edvard 0 points N/A #98528

How to export a web report to Microsoft Excel using PHP

 

The most popular file format that you can generate is a comma separated, ASCII file that is readable by Microsoft Excel. Microsoft Excel can read and import different file formats. You can create a ASCII file or a XML file that is readable by Microsoft Excel.

 

But you need to note that the file need to adhere to a certain standard so that it opens correctly. When I mention "opens correctly" I mean that the data need to fit in each cell and a particular column need to have the data values in a single column.

 

The CSV file, that is known as, contains the data in rows. Each field is separated by a comma. Each row is terminated by a new line character (Line Feed and a Carriage Return characters, Char code 10 and Char code 13). The specification states that if a comma is used as a data item, then the data item need to be enclosed in double quotes.

 

In event a double quote is used as a data item that is enclosed in a double quote, then that double quote need to be escaped by another additional double quote. The number of comma separators need to be consistent. This is what I meant by a certain standard.

 

Example of a CSV data is as follows.

 

My Name is Evard, the great, the greatest

"I love food, bananas and apples", grapes, wine

"he said, ""Good Bye"" to me", evening, morning

 

The above is a CSV file with three rows and three columns. Notice the three data lines above and notice how data is escaped in the last 2 lines. You then save the file and put a hyperlink to it.

About Edvard

Questions
0

Answers
16

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 05/17/2011
• Question Category: PHP

Best Answer

Best Answer

Answered By Ingrid 0 points N/A #98529

How to export a web report to Microsoft Excel using PHP

 

Comma Separated Values data file, is the most common standard used for exchanging information between systems that do not natively read Microsoft Excel spreadsheets. You can make Excel automatically open up, by giving the file extension as "CSV". This file extension is registered as a file format to be opened in Microsoft Excel (if installed) on Microsoft Windows based computers.

 

On your web report you can have a link that points to a PHP file that outputs or creates the comma separated text file. Its quite easy in PHP as you only need to do a loop and out the data and then a comma and so on and so forth.

 

//for loop for the data and then

 

$dataline = "$feild1,$feild2,$feild3n"

 

echo $dataline

 

When the user clicks on the link, your script can write the Excel sheet to disk, and then redirect the user to the file, using the header command. You will notice that a file download dialog box will open up, prompting you to open it with Microsoft Excel. This I believe will solve your problem.

About Ingrid

Questions
0

Answers
17

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 05/17/2011
• Question Category: PHP

Answered By Serena 460 points N/A #98530

How to export a web report to Microsoft Excel using PHP

 

The Comma Separated Values data file is the solution! I tried it out on my web report and it works beautifully!

 

Thank you all for your expert advice! I learnt that PHP cannot really create a Microsoft Excel Sheet, but you can create a different file format that is readable by Microsoft Excel. I learnt that out of the many file formats, the Comma Separated Values format is universally recognized and supported by many applications including and not limited to Microsoft Excel.

 

I also found out that you cannot insert a logo, charts, functions, in the comma separated text file but I can explain that to the client!

 

I hope Microsoft will release its Excel file format specification out to the world, so that integration and data extraction will be easy in the future of web computing!

About Serena

Questions
2

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/17/2011
• Question Category: PHP