Solved

Excel writer in php that allows file save, NOT download

Posted on 2016-09-30
3
35 Views
Last Modified: 2016-10-07
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
0
Comment
Question by:Richard Korts
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
Brian Tao earned 500 total points
ID: 41823898
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.
1
 

Author Comment

by:Richard Korts
ID: 41824337
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
0
 
LVL 9

Expert Comment

by:Brian Tao
ID: 41824445
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.
1

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now