php-general Digest 20 Feb 2010 16:10:43 -0000 Issue 6600
Topics (messages 302231 through 302237):
Re: Excel Spreadsheets and PHP
302231 by: Daevid Vincent
302236 by: aschwin.wesseli.us
Re: Advice on maintaining public and private files
302232 by: clancy_1.cybec.com.au
302233 by: Kim Madsen
Re: array conversion
302234 by: clancy_1.cybec.com.au
Pre/Post inc (Was array conversion)
302235 by: Richard Quadling
302237 by: Nathan Rixham
Administrivia:
To subscribe to the digest, e-mail:
php-general-digest-subscr...@lists.php.net
To unsubscribe from the digest, e-mail:
php-general-digest-unsubscr...@lists.php.net
To post to the list, e-mail:
php-gene...@lists.php.net
----------------------------------------------------------------------
--- Begin Message ---
> >> -----Original Message-----
> >> From: Ian Robertson [mailto:irobert...@americantextile.com]
> >> Sent: Friday, February 19, 2010 1:28 PM
> >> To: php-gene...@lists.php.net
> >> Subject: [PHP] Excel Spreadsheets and PHP
> >>
> >> Hello, everyone.
> >>
> >> Just a quick question.
> >>
> >> What are you using, if anything, to create Excel spreadsheets
> >> with PHP?
> >>
> >> Thank you in advance.
> >>
> >
> > Pear Spreadsheet Excel Writer.
> >
> > http://pear.php.net/package/Spreadsheet_Excel_Writer
Related, here is a routine we use. Assuming you already have your data in a
multi-array.
/**
* Outputs an Excel .xls file
* Note: a row that starts with "---" will be considered a separator row
and output any text following the "---" as such.
*
* @param string $title_text The name of the title in the Excel .xls
document (gmdate('Y-m-d H:i') is auto appended)
* @param array $header_array an array of headers for each column
* @param array $data_array the data for each column and row
* @param string $file_name the name of the .xls file to save as
(gmdate('Y-m-d H:i') is auto appended), defaults to $title_text
* @author Daevid Vincent
* @date 10/29/2009
*/
function download_table_to_excel($title_text, &$header_array, &$data_array,
$file_name=null)
{
//require_once './includes/gui/gui_setup.inc.php';
if (!$file_name) $file_name = $title_text;
$file_name = str_replace( array('[', ']'), array('(',')'),
$file_name);
add_user_log('Action', 'Download "'.$file_name.'" Excel file');
set_include_path(get_include_path().PATH_SEPARATOR.ROOTPATH.'/includes/pear
');
require_once
ROOTPATH.'/includes/pear/Spreadsheet/Excel/Writer.php';
$excel_control_characters = array('@', '=');
$exceldoc = new Spreadsheet_Excel_Writer();
// Set version to 8 (BIFF8) so strings are not truncated to 255
chars
//$exceldoc->setVersion(8);
//http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-write
r.spreadsheet-excel-writer-workbook.setversion.php
//http://forum.openx.org/index.php?showtopic=503418353
//http://pear.php.net/bugs/bug.php?id=3384
$worksheet =& $exceldoc->addWorksheet('Sheet 1'); //sheet name can
only be < 31 chars, but we only use one sheet, so hard-code it
$format_data =& $exceldoc->addFormat();
$format_data->setTextWrap();
// Create an array to track the value length per column, the
default width is 8.11
$max_column = count($header_array) - 1;
$max_len_by_column = array();
for ($col = 0; $col <= $max_column; $col++)
$max_len_by_column[$col] = 8.11;
$row = -1;
// Optionally write table title
if ($title_text)
{
$format_title =& $exceldoc->addFormat();
$format_title->setAlign('center');
$format_title->setAlign('vcenter');
$format_title->setBold();
$format_title->setTextWrap();
$title_text .= ' (created on '.gmdate('Y-m-d @ H:i').'
UTC)';
// adjust the row height from the number of lines in the
table title
$lines = substr_count($title_text, '<br>') + 1;
$height = $lines * 14;
$row++;
$value =
html_entity_decode(trim(strip_tags(str_replace('<br>', "\n",
$title_text))));
if (is_string($value) && in_array(substr($value,0,1),
$excel_control_characters)) $value = ' '.$value; // Add a space before
Excel control characters
$worksheet->write($row, 0, $value, $format_title);
$worksheet->setRow($row, $height);
$worksheet->mergeCells($row, 0, $row, $max_column);
}
// Write column headers
$format_header =& $exceldoc->addFormat();
$format_header->setBold();
$format_header->setTextWrap();
$row++;
foreach ($header_array as $col => $header)
{
// remove html tags from values
$value =
html_entity_decode(trim(strip_tags(str_replace('<br>', "\n",
is_array($header) ? $header[0] : $header))));
if (is_string($value) and in_array(substr($value,0,1),
$excel_control_characters)) $value = " ".$value; // Add a space before
Excel control characters
$worksheet->write($row, $col, $value, $format_header);
if (is_array($header)) $worksheet->writeNote($row, $col,
$header[1]);
}
foreach ($data_array as $i => $data)
{
$row++;
$col = 0;
//check for magic separator rows
if ( substr($data,0,3) == '---' )
{
$separator_row = substr($data,3);
// adjust the row height from the number of lines
in the table title
$lines = substr_count($separator_row, '<br>') + 1;
$height = $lines * 14;
$row++;
$value =
html_entity_decode(trim(strip_tags(str_replace('<br>', "\n",
$separator_row))));
if (is_string($value) &&
in_array(substr($value,0,1), $excel_control_characters)) $value = '
'.$value; // Add a space before Excel control characters
$worksheet->write($row, 0, $value, $format_title);
$worksheet->setRow($row, $height);
$worksheet->mergeCells($row, 0, $row, $max_column);
continue;
}
foreach ($data as $key => $value)
{
$value =
html_entity_decode(trim(strip_tags(str_replace(array('<br>',"\t"),
array("\n",''), $value))));
if (is_string($value) &&
in_array(substr($value,0,1), $excel_control_characters)) $value = "
".$value; // Add a space before Excel control characters
$worksheet->write($row, $col, $value,
$format_data);
// find the maximum value len (up to 40) so an
appropriate column width can be set
$lines = explode("\n", $value);
foreach ($lines as $line)
{
$len = min(40, strlen($line) * 1.20);
//[dv] this 1.20 seems to be a fudge factor with no real basis AFAICT?
if ($len > $max_len_by_column[$col])
$max_len_by_column[$col] = $len;
}
$col++;
}
}
// Adjust column width based on column values
foreach ($max_len_by_column as $col => $len)
$worksheet->setColumn($col, $col, $len);
// Send the worksheet
$exceldoc_name = $file_name.' ('.gmdate('Y-m-d H:i').').xls';
$exceldoc_name = str_replace( array('[', ']',':'), array('(',
')','-'), $exceldoc_name); //IE6 chokes on some characters in filename
$exceldoc->send($exceldoc_name);
$exceldoc->close();
unset($header_array, $data_array);
}
/**
* Used as a supporting function for print_table() and the key to
download_table_to_excel()
* Returns an HTML anchor tag
*
* @param string $download_variable $_GET parameters that are parsed to
re-create the table in Excel rather than HTML
* @param string $table_name unique name of this table (useful for when
multiple tables are on the same page)
* @return string
* @author Daevid Vincent
* @date 2010-02-02
*/
function get_download_to_excel_link_html($download_variable, $table_name)
{
if ($_SESSION['mobile']) return;
$params = "{$download_variable}={$table_name}";
//append existing $_GET parameters automatically to the URL string
foreach ($_GET as $variable=>$value)
if (is_array($value))
foreach ($value as $array_value)
$params .= "&{$variable}[]={$array_value}";
else
$params .= "&{$variable}={$value}";
return '<a class="excel"
href="'.$_SERVER['PHP_SELF'].'?'.$params.'">Download table
"<b>'.$table_name.'</b>" to Excel</a><br/><br/>';
}
And here's a partial of the related function to output a table from an
array of data
/**
* A generic routine for displaying an HTML table
* Note: a row that starts with "---" will be considered a separator row
and output any text following the "---" as such.
*
* @access public
* @return an HTML formatted <table>
* @param string $title_text the title of the table
* @param array $header_array the column headers, ex:
array(array('Header 1 Title', 'Header 1 Description/Tip', 'nosort'), ...);
OR array('Header 1', 'Header 2', ...);
* @param array $data_array the data of the table
* @param array $td_attribute_array CSS attributes for the
$data_array values [do not count the detail column as an index] for
example, $attributes[1] = 'align="center"'; will center the second
$data_array column to the right
* @param string $table_name put a 'download to excel' link (huh?)
* @param boolean $portlet (true) toggle if you want this to be a
minimizeable portlet or not
* @param string $table_class any CSS class information for the table
tag (default is 'sortable')
* @param string $portlet_class any CSS class information for the
portlet tag (default is 'portlet')
* @param string $description a blob of text to display just above
the table
* @see print_array_table()
* @author Daevid Vincent [daevid.vinc...@panasonic.aero]
* @date 2009-01-14
*/
function print_table($title_text, $header_array, $data_array,
$td_attribute_array=NULL, $table_name=NULL, $portlet=true,
$table_class='sortable', $portlet_class='portlet', $description=null)
{
$download_variable = 'download_to_excel';
$num_rows = @intval(count($data_array));
<?php
if ($num_rows)
{
$header = array_shift($header_array);
if ($num_rows > 1000) notification_table('info', '<i>It is not
adviseable to sort these '.number_format($num_rows).' rows using the column
headers (as this may lock-up some browsers).<br/>Please narrow your results
to less than 1000.</i>');
if ($description) echo '<p>'.$description.'<p>';
if ($table_name)
{
echo get_download_to_excel_link_html($download_variable,
$table_name);
--- End Message ---
--- Begin Message ---
On 19-2-2010 21:28, Ian Robertson wrote:
Hello, everyone.
Just a quick question.
What are you using, if anything, to create Excel spreadsheets with PHP?
Thank you in advance.
Hello Ian,
You can use the PHPExcel class. It can read, write and convert between
Excel versions without the need of the COM. Depending on the version of
the file needs to be, it uses XML or bitbashing.
Or, like others said, you can use XML for writing Excel files in the new
xlsx format. This is actually a ZIP-file with a lot of XML-files in it.
This makes it possible to use other XML techniques as well like Schema,
XSL, XPath and DTD.
I prefer to make a XML based template and use XSL to transform and parse
the data. With PHP you can pass parameters to the XSL file and fill in
the blanks where needed.
Excel is quite complex due to the cells, the styles, the formulas and
the worksheets.
Kind regards,
Aschwin Wesselius
--- End Message ---
--- Begin Message ---
On Fri, 19 Feb 2010 13:19:50 -0500, st...@astroh.org (Michael Stroh) wrote:
>I have a site I'm working on with some data that I want to be readable by
>anyone, but some files that I want to keep hidden from outside users. Here is
>an example of my file structure.
>
>/products/data1/item_1/data.txt
>/products/data2/item_2/data.txt
>
>I would like everything in data1 to be available by anyone who visits the
>site, but I want to keep items in the data2 folder to only be accessible
>through certain web page which I hope to eventually require logins. Some of
>these items I'd like to not only display but also allow people to download.
>
>My main concern is that I don't want people to be able to guess the names of
>the files and then be able to access the information on them. Every 'item' has
>an entry in a MySQL database which holds some information. I was thinking I
>could have randomly generated folder names to take the place of the things
>like 'item_2' such as
>
>/products/data2/kl23j42i/data.txt
>
>and then link the folder name through a database entry. But I'm not sure if
>there are more elegant or easier ways to deal with this. Plus someone could
>still just try randomly querying the site until they get a match. I'd first
>like to just create a web page where you can go to access the hidden files but
>would later like to add more control for other users using logins and
>passwords.
>
>Most of my files are just text files and images. Any suggestions?
>
>Thanks in advance!
>
>Michael
I have been working on a website engine for some time, and have recently been
addressing
these problems. The website layout is specified by textbased data files, with a
separate
entry for each item on the page. These may be links to subdirectories or even
other
websites, links to further index pages or links to individual items.
Users are divided into groups, e.g. Guest, Admin, or Manager, and each data
file has a
field specifying who is allowed to use it. Each entry has a similar field, and
when a data
file is being loaded the loader checks that the current user has permission to
access it
before allowing the file to be loaded, and then as it processes each item in
the file it
checks if the user has permission to view this item, and if not skips it. This
means that
the user only sees the items he is entitled to see. There is nothing to
indicate that
anything is being hidden from him.
At present I only have one allowable group for each file or item, and permit
individual
users to belong to multiple groups (as set up by the administrator). On
reflection it
would probably be better to assign each user to a single group, and allow
multiple groups
to be given access to the file. At first I simply assigned each user a
privilege level; 0,
1, 2, .. , but this prevented giving some user groups access to some areas of
the website,
and other user groups access to others.
Each website has one area containing data, and a separate one containing the
engine (which
has all the code). The data area also contains a small file index.php, which
sets up site
dependent parameters, and then hands access to the engine. I have several
different
websites sharing the same engine, and this means both that the individual
websites can
specify different configuration files and security requirements. One website
can be fully
accessible, another only accessible after the user is logged in, and another
can have some
areas only accessible through a hidden log in.
I use parameters to specify which page to be loaded, but I've recently realised
that this
is a significant security hole, as the parameters are readily visible, and
convey a lot of
information about the structure of the site. On second thoughts it would have
been better
to specify the various directories and files by numbers.
The engine is in a separate directory which is not under the root, so it is not
readily
accessible, but I wanted the photos to be able to be bookmarked, which meant
that they had
to be under the root, and I put the data files with them for simplicity.
However this
means that they can also be downloaded, so I will have to move them to a
different
location, as some of them contain valuable information. Fortunately the way the
engine is
designed makes this reasonably simple to do.
You can see a very simple demonstration website at
http://www.cydalba.com/?new=1.
At present this is set up so that part of the website is only accessible by
hidden log in.
If you access it via
http://www.cydalba.com/?new=1&action=log_in
you will be asked to log in, which you can do as 'Guest', with password
'Mandy17'. Some
more of the website will then be accessible.
Clancy
--- End Message ---
--- Begin Message ---
Michael Stroh wrote on 19/02/2010 19:19:
I have a site I'm working on with some data that I want to be
readable by anyone, but some files that I want to keep hidden from
outside users. Here is an example of my file structure.
/products/data1/item_1/data.txt
> /products/data2/item_2/data.txt
since no one has suggested it then... if you're on an Apache webserver
use a .htaccess file in data2 which contains:
Deny from all
Allow from none
That will do the trick and PHP can still fetch the files in data2 and
serve it to the user.
--
Kind regards
Kim Emax - masterminds.dk
--- End Message ---
--- Begin Message ---
Or:
$a = array ('Cats', 'white', 'Dogs', 'black', 'Mice', 'grey', 'Camels',
'brown');
$b = ''; // Just in case it has some leftover
value
$k = 2* (int) (count ($a)/2); // ensure even no of terms
$i = 0; while ($i < $k)
{
$b[$a[$i++]] = $a[$i++]; // ***
}
And this works:
$i = 0; $k = array_keys($b);
while ($i < count($b)) { echo '<h5>'.$i.': '.$k[$i].' = '.
$b[$k[$i++]].'</h5>'; }
0: Cats = white
1: Dogs = black
2: Mice = grey
3: Camels = brown
( *** I have always been wary of using statements like this because I was
unsure when the
incrementing would occur, so I tried it.)
Clancy
On Fri, 19 Feb 2010 02:26:49 -0500, simples...@gmail.com (Adam Richardson)
wrote:
>Or,
>
>function new_arr(array $arr)
>{
> $count = count($arr);
> if ($count % 2 != 0) throw new Exception('The new_arr() function
>requires an even number of elements.');
> for ($i = 0; $i < $count; $i += 2)
> {
> $new_arr[$arr[$i]] = $arr[$i + 1];
> }
> return $new_arr;
>}
>
>$test = new_arr(array('k1', 'v1', 'k2', 'v2', 'k3', 'v3'));
>
>exit(var_dump($test));
>
>On Fri, Feb 19, 2010 at 1:19 AM, Larry Garfield <la...@garfieldtech.com>wrote:
>
>> On Thursday 18 February 2010 11:58:28 pm Paul M Foster wrote:
>> > On Fri, Feb 19, 2010 at 01:20:12PM +0800, Dasn wrote:
>> > > Hi guys. How to convert an array like:
>> > >
>> > > Array
>> > > (
>> > > [0] => key1
>> > > [1] => value1
>> > > [2] => key2
>> > > [3] => value2
>> > > )
>> > >
>> > > to
>> > >
>> > >
>> > > Array
>> > > (
>> > > [key1] => value1
>> > > [key2] => value2
>> > > )
>> > >
>> > > Is there a built-in function to do this?
>> > > Please Cc me. :)
>> > > Thank you in advance.
>> >
>> > I don't believe so, but rolling your own should not be too hard:
>> >
>> > $a = array($key1, $value1, $key2, $value2);
>> > $b = array();
>> > $numitems = count($a);
>> >
>> > for ($i = 0; $i < $numitems; $i++) {
>> > if ($i % 2 == 0) {
>> > $saved_key = $a[$i];
>> > }
>> > elseif ($i % 2 == 1) {
>> > $b[$saved_key] = $a[$i];
>> > }
>> > }
>> >
>> > Code is crude and untested, but you get the idea.
>> >
>> > Paul
>>
>> This would be even shorter, I think:
>>
>> foreach ($items as $i => $value) {
>> $temp[$i % 2][] = $value;
>> }
>> $done = array_combine($temp[0], $temp[1]);
>>
>> (Also untested, just off the cuff...)
>>
>> --Larry Garfield
>>
>> --
>> PHP General Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
--- End Message ---
--- Begin Message ---
On 20 February 2010 11:18, <clanc...@cybec.com.au> wrote:
> Or:
>
> $a = array ('Cats', 'white', 'Dogs', 'black', 'Mice', 'grey', 'Camels',
> 'brown');
> $b = ''; // Just in case it has some leftover
> value
> $k = 2* (int) (count ($a)/2); // ensure even no of terms
> $i = 0; while ($i < $k)
> {
> $b[$a[$i++]] = $a[$i++]; // ***
> }
>
> And this works:
> $i = 0; $k = array_keys($b);
> while ($i < count($b)) { echo '<h5>'.$i.': '.$k[$i].' = '.
> $b[$k[$i++]].'</h5>'; }
>
> 0: Cats = white
> 1: Dogs = black
> 2: Mice = grey
> 3: Camels = brown
>
> ( *** I have always been wary of using statements like this because I was
> unsure when the
> incrementing would occur, so I tried it.)
>
> Clancy
<?php
$i = 10;
echo $i++; // Shows 10 and $i becomes 11
echo ++$i; // $i becomes 12 and 12 is shown.
?>
Post increment and pre increment.
No need to be "wary" of them.
http://docs.php.net/manual/en/language.operators.increment.php
--
-----
Richard Quadling
"Standing on the shoulders of some very clever giants!"
EE : http://www.experts-exchange.com/M_248814.html
EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp
Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731
ZOPA : http://uk.zopa.com/member/RQuadling
--- End Message ---
--- Begin Message ---
Richard Quadling wrote:
> On 20 February 2010 11:18, <clanc...@cybec.com.au> wrote:
>> Or:
>>
>> $a = array ('Cats', 'white', 'Dogs', 'black', 'Mice', 'grey', 'Camels',
>> 'brown');
>> $b = ''; // Just in case it has some leftover
>> value
>> $k = 2* (int) (count ($a)/2); // ensure even no of terms
>> $i = 0; while ($i < $k)
>> {
>> $b[$a[$i++]] = $a[$i++]; // ***
>> }
>>
>> And this works:
>> $i = 0; $k = array_keys($b);
>> while ($i < count($b)) { echo '<h5>'.$i.': '.$k[$i].' = '.
>> $b[$k[$i++]].'</h5>'; }
>>
>> 0: Cats = white
>> 1: Dogs = black
>> 2: Mice = grey
>> 3: Camels = brown
>>
>> ( *** I have always been wary of using statements like this because I was
>> unsure when the
>> incrementing would occur, so I tried it.)
>>
>> Clancy
>
>
> <?php
> $i = 10;
> echo $i++; // Shows 10 and $i becomes 11
> echo ++$i; // $i becomes 12 and 12 is shown.
> ?>
>
> Post increment and pre increment.
>
> No need to be "wary" of them.
>
> http://docs.php.net/manual/en/language.operators.increment.php
>
Expanding on what Richard says; there does seem to be a growing number
of people who haven't stopped to learn the very basics of PHP (or
languages in general).
I'd strongly recommend that all those in doubt over the basics take a
few hours out to (re-)familiarise themselves; and there's no finer
resource to do this than the php manual [1]
You'll notice the manual goes as follows:
# Basic syntax
# Types
# Variables
# Constants
# Expressions
# Operators
# Control Structures
# Functions
... more
And that's the order in which you should learn; in short you can't
really program or script without knowing basics through control structures.
Do hope this mail doesn't sound condescending in any way; as it's meant
with the best intentions and really will make you're (working) life a
lot easier. I myself still refer back to these base sections
periodically, and every time I do - a new detail pops out that makes
something easier.
[1] http://docs.php.net/manual/en/langref.php
Many Regards,
Nathan
--- End Message ---
