PHPExcel

We recently started using PHPExcel where I work to generate excel reports, some of these reports have a tendancy to get very large. We have an issue when creating large reports where PHPExcel takes a long time to generate and then runs out of memory ... [More] before completion. I've looked at similar issues on this site but haven't been able to find a solution.For what it's worth our old system, which ran php4 and used the PEAR Spreadsheet_Excel_Writer library, was able to run the report much quicker and didn't run out of memory.We recently upgraded our codebase to php5.3 and and PHPExcel because the PEAR library is no longer maintained (it also helped that PHPExcel uses Excel 2007 format which we hoped would allow us to run some of our bigger reports in one file rather than multiple files due to the row limitation in older versions of Excel). However the long processing time and out of memory issues are something we didn't anticipate. The large report in question is over 80k rows with up to 99 columns in each row. On the old system we could run this report in about an hour (up to the Excel row limit of 65k+ rows), on PHPExcel we don't even get to the 65k-th row before running out of memory and it takes roughly 6 hours before we run out of memory.The System we are now on is a Zend Server 6.0.1 on RedHat with 6GB RAM and 4GB of Swap space (this is one of our test environments). The old System was a RedHat server with vanilla Apache, PHP, Mysql installed. It has 3GB of RAM and 6GB of Swap Space.The questions at hand are:1. how can we reduce the amount of processing time?2. how can we resolve the out of memory issue?Obviously we can add more memory but I don't see that significantly reducing the amount of processing time.Here is a test script I wrote to test the Excel generation performance.```define('MAX_ROWS', 80249);//80249define('MAX_COLS', 99);// define('NEW_FILE', 'C:\logs\large_excel_test.xlsx');define('NEW_FILE', '/tmp/large_excel_test.xlsx');$data = array();echo "creating data array\n";for($i = 0; $i < MAX_ROWS; $i++) { $data[$i] = array(); for($j = 0; $j < MAX_COLS; $j++) { $data[$i][$j] = $i . ':' . $j; }}//cached methodecho "starting phpexcel (sans adapter)\n";$export = new PHPExcel();echo "setting caching method\n";PHPExcel_Settings::setCacheStorageMethod(PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp, array('memoryCacheSize' => '1024MB'));echo "setting active sheet to 0\n";$sheet = $export->setActiveSheetIndex(0);echo "setting value binder to leading zero\n";PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_LeadingZeroBinder());echo "generating rows and columns\n";$sheet->fromArray($data);echo "writing excel file\n";$write = PHPExcel_IOFactory::createWriter($export, 'Excel2007');$write->save(NEW_FILE);``` [Less]

I am using Centos OS and i have to populate values dynamically for their corresponding cell. Values for text items are populating well but it's not working for checkbox. please help.

Do not bother with my question, I tried, it does not work (because the file is already open). I'll try another way

Does this mean that a web server may be not shared by multiple users when using PHPExcel ? If so what wil happen with a code like the following : $file ="myfile.xls"; $fp = fopen($file", "r+"); flock($fp, LOCK_EX); // lock my file $objPHPExcel = ... [More] PHPExcel_IOFactory::load($file); $objWorksheet = $objPHPExcel->getActiveSheet(); $line = $objWorksheet->getHighestRow()+1; ... ... $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); try { $objWriter->save($file); flock($fp, LOCK_UN); } catch (Exception $e) { ob_get_clean(); ob_start(); die("An error was decteted"); } ``` [Less]

Hello, I have created a EXCEL file with many columns. A few of these columns have dropdown lists to provide the user with just a few choices. When I'm adding/updating a new row in this file, the dropdown box disappear for all columns cells of all next rows. Is-it normal, is there a solution ? Many thanks in advance Gégé

On 05/01/2015 13:56, GegeMartinelli wrote: From: GegeMartinelli Hello I'm pretty new to PHPEXCEL, and already I was able to create and modify xls files. It's a fantastic tool. However I'm wondering how things work concerning file ... [More] sharing and locking file (or rows) when updating the file. Let's assume 2 people creating one row each. How to lock the file when it is updated by the first guy ? Does phpExcel uses internally the php function : flock ? It there a good progrmming way to insure the file integrity ? THanks a lot for any advice or comment. Gégé PHPExcel doesn't provide any functionality to lock files in any way, or make any provision for concurrent access to files -- Mark Baker _________ |. \ \-3 |_J_/ PHP | || | __ | || |m| |m| I LOVE PHP [Less]

Hello I'm pretty new to PHPEXCEL, and already I was able to create and modify xls files. It's a fantastic tool. However I'm wondering how things work concerning file sharing and locking file (or rows) when updating the file. Let's assume 2 ... [More] people creating one row each. How to lock the file when it is updated by the first guy ? Does phpExcel use internally the php function : flock ? Is there a good progrmming way to insure the file integrity ? THanks a lot for any advice or comment. Gégé [Less]

The chances are that codeigniter is overriding the headers that you are setting manually with those for a standard html page.... there should be some feature/method in CI that lets you disable CI sending headers so that your own headers are respected instead

//load our new PHPExcel library $this->load->library('excel'); //activate worksheet number 1 $this->excel->setActiveSheetIndex(0); //name the worksheet $this->excel->getActiveSheet()->setTitle('test worksheet'); ... [More] //set cell A1 content with some text $this->excel->getActiveSheet()->setCellValue('A1', 'This'); $this->excel->getActiveSheet()->setCellValue('B1', 'Test'); //change the font size $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(10); //make the font become bold $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true); //merge cell A1 until D1 //set aligment to center for that merged cell (A1 to D1) $this->excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $filename = 'aa.xls'; //save our workbook as this file name header('Content-Type: application/vnd.ms-excel'); //mime type header('Content-Disposition: attachment;filename="' . $filename . '"'); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type) //if you want to save it as .XLSX Excel 2007 format $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5'); //force user to download the Excel file without writing it to server's HD $objWriter->save('php://output'); OUTPUT: ÐÏࡱá;þÿ þÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿþÿÿÿ þÿÿÿþÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿþÿà…ŸòùOh«‘ '³Ù0¸@Hh€˜ ¤°äUntitled SpreadsheetUnknown CreatorUnknown Creator@C!{Ã&Ð@C!{Ã&Ð » ÌÑB°=¼%r8X"1ܐCalibri1ȼCalibriàõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À à À à"È “€ÿ’â8ÿÿÿÿÿÿÿÿÿÿÿÿ€€€€€€€€€ÀÀÀ€€€™™ÿ™3fÿÿÌÌÿÿffÿ€€fÌÌÌÿ€ÿÿÿÿÿÿ€€€€€ÿÌÿÌÿÿÌÿÌÿÿ™™Ìÿÿ™ÌÌ™ÿÿÌ™3fÿ3ÌÌ™ÌÿÌÿ™ÿfff™–––3f3™f333™3™3f33™333…$ctest worksheetÁÁgæ®üTest » ÌÑ* ‚€Áƒ„&ffffffæ?'ffffffæ?(è?)è?¡"dXX333333Ó?333333Ó?U}$ }$ @ý >¶@d‹‹dggÿÿÿÿÿ þÿÕÍÕœ.“— ,ù®0¼HPX`hp xŽä WorksheetFeuilles de calculRoot Entryÿÿÿÿÿÿÿÿ ÀFC!{Ã&ÐC!{Ã&ÐÀSummaryInformation(ÿÿÿÿ ÀFèWorkbookÿÿÿÿÿÿÿÿÿÿÿÿ ÀF¿DocumentSummaryInformation8ÿÿÿÿÿÿÿÿÿÿÿÿ ÀFìþÿÿÿþÿÿÿþÿÿÿýÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ Whats Wrong in My code.. I m Beginner in Code... [Less]

Hi,I am using PhpExcel 1.8.0 on Php 5.5I am trying to get phpexcel to a whole bunch of heavy lifting when it comes to calculations. I am generating a sheet that contain named ranges that span a single column but multiple rows (RANGE_1 = A2:A6 ... [More] , RANGE_2 = B2:B6).I then have another set of cells (C2:C6) where the formula is RANGE_1+RANGE_2 in each cell.When you load the generated file in Excel, it correctly calculates the values in column C depending on what is in each row. THIS IS GREAT.But, when I getCalculatedValue of (C2) it return 0 and not the expected result of (A2+B2). Is there anything I am obviously and clearly doing wrong, or do I need to convert my named ranges in the formula to actual cell references (that would be really tedious as it works really well).I appreciate any help on this.Comments: ** Comment from web user: StuartAllsopp ** Thank you for your very quick response I really appreciate it, I have re-worked that code using direct cell references and it works fine.. I have unfortunately hit another snag with VLOOKUPS not using ranges... thought it be safer not to... I have posted it as a separate issue.Again thank you so much for your help.. [Less]