Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel writer in php that allows file save, NOT download

Posted on 2016-09-30
3
Medium Priority
?
119 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
[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
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
Brian Tao earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses

636 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