Link to home
Start Free TrialLog in
Avatar of Richard Korts
Richard KortsFlag for United States of America

asked on

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:

<?php
/**
 * Detailed example for creating Excel XML docs
 * @package ExcelWriterXML
 * @subpackage examples
 * @filesource
 */

/**
 * Include the class for creating Excel XML docs
 */
include('ExcelWriterXML.php');

/**
 * 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');
$xml->docCompany('Greers.Org');
$xml->docManager('Wife');

/**
 * Choose to show any formatting/input errors on a seperate sheet
 */
$xml->showErrorSheet(true);

/**
 * Show the style options
 */
$format1 = $xml->addStyle('left_rotate60_big');
$format1->alignRotate(60);
$format1->alignHorizontal('Left');
$format1->fontSize('18');

$format2 = $xml->addStyle('verticaltext_left');
$format2->alignVerticaltext(45);
$format2->alignHorizontal('Left');

$format3 = $xml->addStyle('wraptext_top');
$format3->alignWraptext();
$format3->alignVertical('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,1,'left_rotate45',$format1);
$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->columnWidth(1,'100');
$sheet2->writeString(1,1,'Number');
$sheet2->writeNumber(1,2,50);
$sheet2->writeString(2,1,'Number');
$sheet2->writeNumber(2,2,30);
$sheet2->writeString(3,1,'Number');
$sheet2->writeNumber(3,2,20);
$sheet2->writeString(4,1,'=SUM(R[-3]C:R[-1]C)');
$sheet2->writeFormula('Number',4,2,'=SUM(R[-3]C:R[-1]C)');
$sheet2->addComment(4,2,'Here is my formula: =SUM(R[-3]C:R[-1]C)','My NAME');
$sheet2->writeString(5,1,'=SUM(R1C2:R3C2)');
$sheet2->writeFormula('Number',5,2,'=SUM(R1C1:R3C2)');
$sheet2->addComment(5,2,'Here is my formula: =SUM(R1C1:R3C2)');

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

/**
 * Send the headers, then output the data
 */
$xml->sendHeaders();
$xml->writeData();


?>

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.)

Thanks!
elementindex_ExcelWriterXML.html
ASKER CERTIFIED SOLUTION
Avatar of Brian Tao
Brian Tao
Flag of Taiwan, Province of China image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Richard Korts

ASKER

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)

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);

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

Thanks
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.