> >> -----Original Message----- > >> From: Ian Robertson [mailto:irobertson@xxxxxxxxxxxxxxxxxxx] > >> Sent: Friday, February 19, 2010 1:28 PM > >> To: php-general@xxxxxxxxxxxxx > >> Subject: 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.vincent@xxxxxxxxxxxxxx] * @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); -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
