• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

Excel writer in php that allows file save, NOT download

I am looking for a way to create an Excel Spreadsheet in php (from data in a MySQL database, I know how to do that part).

I have found several classes. But in all cases, they seem determined to force auto download of the generated Excel (xls or xml).

One is called Spreadsheet Excel Writer (no documentation I can find).

Another is called ExcelWriterXML. Seems great, example here:

 * Detailed example for creating Excel XML docs
 * @package ExcelWriterXML
 * @subpackage examples
 * @filesource

 * Include the class for creating Excel XML docs

 * Create a new instance of the Excel Writer
$xml = new ExcelWriterXML('my file.xml');

 * Add some general properties to the document
$xml->docTitle('My Demo Doc');
$xml->docAuthor('Robert F Greer');

 * Choose to show any formatting/input errors on a seperate sheet

 * Show the style options
$format1 = $xml->addStyle('left_rotate60_big');

$format2 = $xml->addStyle('verticaltext_left');

$format3 = $xml->addStyle('wraptext_top');

 * Create a new sheet with the XML document
$sheet1 = $xml->addSheet('Alignment');
 * Add three new cells of type String with difference alignment values.
 * Notice that the style of the each cell can be explicity named or the style
 * reference can be passed.
$sheet1->writeString(1,2,'vertical left','verticaltext_left');
$sheet1->writeString(1,3,'this text has been wrapped and is aligned at the top','wraptext_top');
$sheet1->writeString(1,4,'No style applied');

$sheet2 = $xml->addSheet('Formulas');
 * Wrote three numbers.
 * Rows 4 and 5 show the formulas in R1C1 notation using the writeFormula()
 * function.
 * Also see how comments are added.
$sheet2->addComment(4,2,'Here is my formula: =SUM(R[-3]C:R[-1]C)','My NAME');
$sheet2->addComment(5,2,'Here is my formula: =SUM(R1C1:R3C2)');

$sheet4 = $xml->addSheet('more formatting');
$format4 = $xml->addStyle('my style');
$mydate = $sheet4->convertMysqlDateTime('2008-02-14 19:30:00');
// Change the row1 height to 30 pixels
$sheet4->writeString(2,1,'formatted text + cell color + merged + underlined',$format4);
// Merge (2,1) with 4 columns to the right and 2 rows down

 * Send the headers, then output the data


Open in new window

But it forces download, I want to save the file.

Attached is docs (HTML) for the latter. Don't see how to SAVE FILE. Seems like such a simple & obvious thing to want to do.

Can someone point me to a package that is (a) simple to use and (b) allows saving the file ONTO the web server? I need to be able to format cells (text size, font family, bold, color, background color, underline, alignment, etc.)

Richard Korts
Richard Korts
  • 2
1 Solution
Brian TaoSenior Business Solutions ConsultantCommented:
Use PHPExcel (official site: https://github.com/PHPOffice/PHPExcel)
I've been using it for reports in my various customer projects, for more than 5 years.  It supports both saving and downloading, of both xls and xlsx formats.  It also supports exporting to various format, including csv, pdf and so on.
It supports almost all Excel built-in functions, including cell formatting, formula, print setup, page setup and many others.
Richard KortsAuthor Commented:
Brian Tao,

I am now using that & it works PERFECTLY. Little hard to learn with a zillion excel properties but VERY comprehensive.

One question. I am trying to format the column widths to make the initially opened spreadsheet look cleaner. Of course I recognize that the recipient can adjust column widths to their liking in Excel.

If this is used, what are the units (12 in this example)


Is this the same as in Excel setting column width to 12?

Brian TaoSenior Business Solutions ConsultantCommented:
The unit is so called "Character Units" which is the width of a '0' in the workbooks default font, so the 2 things are not the same.
$col->setWidth(12) is slightly narrower than in Excel setting column width to 12

Below is the technical explanation from one of PHPExcel's documentation.
The measure for column width in PHPExcel does not correspond exactly to the measure you may be used to in Microsoft Office Excel. Column widths are difficult to deal with in Excel, and there are several measures for the column width.

1) Inner width in character units (e.g. 8.43 this is probably what you are familiar with in Excel)
2) Full width in pixels (e.g. 64 pixels)
3) Full width in character units (e.g. 9.140625, value -1 indicates unset width)

PHPExcel always operates with 3) "Full width in character units" which is in fact the only value that is stored in any Excel file, hence the most reliable measure. Unfortunately, Microsoft Office Excel does not present you with this measure. Instead measures 1) and 2) are computed by the application when the file is opened and these values are presented in various dialogues and tool tips.

The character width unit is the width of a '0' (zero) glyph in the workbooks default font. Therefore column widths measured in character units in two different workbooks can only be compared if they have the same default workbook font.

If you have some Excel file and need to know the column widths in measure 3), you can read the Excel file with PHPExcel and echo the retrieved values.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now