Re: [PHP] PHPExcel
Wish I had heard PHPExcel before. I'm using a class to read excel files. Ege. Alinti Floyd Resler fres...@adex-intl.com Does anyone have much experience with PHPExcel? I'm having difficulties in getting multiple sheet workbooks to work properly. I haven't been able to find any practical examples. Thanks! Floyd -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel
Hi, *Heres the documentation:* http://phpexcel.codeplex.com/releases/view/45412#DownloadId=212183 In user doc: *4.2 Reading Only Named WorkSheets from a File* * * and you can get all the sheet names: http://www.auditbureau.org.au/a/Documentation/API/PHPExcel/PHPExcel.html#methodgetSheetNames
[PHP] PHPExcel
Does anyone have much experience with PHPExcel? I'm having difficulties in getting multiple sheet workbooks to work properly. I haven't been able to find any practical examples. Thanks! Floyd -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHPExcel with large files (27,000+ rows)
I'm currently working on a project that requires the parsing of excel files. Basically the user uploads an excel file, and then a script needs to save a row in a Postgres database for each row in the excel file. The issue we are having is that when we task PHPExcel with parsing an excel file with, say 27k rows, it explodes with a memory error. I've read up on the PHPExcel forums and we've tried cell caching as well as ReadDataOnly, they do not seem to be sufficient. Does anyone here know of a way to do this? Surely there is a way to parse a large excel file with PHP. This is also NOT an on-demand service. That is, when someone uploads a file they get a task_id which allows them to check the status of their excel file. So the solution does not need to be a fast one! Thanks, Chris.
Re: [PHP] PHPExcel with large files (27,000+ rows)
chris h wrote: I'm currently working on a project that requires the parsing of excel files. Basically the user uploads an excel file, and then a script needs to save a row in a Postgres database for each row in the excel file. The issue we are having is that when we task PHPExcel with parsing an excel file with, say 27k rows, it explodes with a memory error. I've read up on the PHPExcel forums and we've tried cell caching as well as ReadDataOnly, they do not seem to be sufficient. Does anyone here know of a way to do this? Surely there is a way to parse a large excel file with PHP. If your excel file is or can be transformed to XML, I would just use XSLT. No PHP needed. -- Per Jessen, Zürich (19.1°C) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel with large files (27,000+ rows)
I use this: http://code.google.com/p/php-csv-parser/ No idea if it's any better than your current solution. I presume you've tried extending PHP's memory limit? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel with large files (27,000+ rows)
On Tue, Oct 5, 2010 at 12:39 AM, chris h chris...@gmail.com wrote: I'm currently working on a project that requires the parsing of excel files. Basically the user uploads an excel file, and then a script needs to save a row in a Postgres database for each row in the excel file. The issue we are having is that when we task PHPExcel with parsing an excel file with, say 27k rows, it explodes with a memory error. I've read up on the PHPExcel forums and we've tried cell caching as well as ReadDataOnly, they do not seem to be sufficient. Does anyone here know of a way to do this? Surely there is a way to parse a large excel file with PHP. This is also NOT an on-demand service. That is, when someone uploads a file they get a task_id which allows them to check the status of their excel file. So the solution does not need to be a fast one! Thanks, Chris. 1. Remove any variable that contains big object if its not necessary. 2. Use unset when applicable 3. Read chunk by chunk. 4. Profile it to find the exact place where you are wasting memory. Optimizing that little portion of code can improve memory performance. -- Shiplu Mokadd.im My talks, http://talk.cmyweb.net Follow me, http://twitter.com/shiplu SUST Programmers, http://groups.google.com/group/p2psust Innovation distinguishes bet ... ... (ask Steve Jobs the rest) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel with large files (27,000+ rows)
Thanks Jessen/Marc, though the user provided format can be in xls, xlsx, or csv. So i need a solution to support all formats. Thanks for the ideas shiplu I'll get with the team and see if there's anything there we aren't trying. Chris. On Mon, Oct 4, 2010 at 3:01 PM, shiplu shiplu@gmail.com wrote: On Tue, Oct 5, 2010 at 12:39 AM, chris h chris...@gmail.com wrote: I'm currently working on a project that requires the parsing of excel files. Basically the user uploads an excel file, and then a script needs to save a row in a Postgres database for each row in the excel file. The issue we are having is that when we task PHPExcel with parsing an excel file with, say 27k rows, it explodes with a memory error. I've read up on the PHPExcel forums and we've tried cell caching as well as ReadDataOnly, they do not seem to be sufficient. Does anyone here know of a way to do this? Surely there is a way to parse a large excel file with PHP. This is also NOT an on-demand service. That is, when someone uploads a file they get a task_id which allows them to check the status of their excel file. So the solution does not need to be a fast one! Thanks, Chris. 1. Remove any variable that contains big object if its not necessary. 2. Use unset when applicable 3. Read chunk by chunk. 4. Profile it to find the exact place where you are wasting memory. Optimizing that little portion of code can improve memory performance. -- Shiplu Mokadd.im My talks, http://talk.cmyweb.net Follow me, http://twitter.com/shiplu SUST Programmers, http://groups.google.com/group/p2psust Innovation distinguishes bet ... ... (ask Steve Jobs the rest)
[PHP] PHPExcel
Happy 4th of July! I am having a little bit of a problem using PHPExcel. I think I'm doing everything right but apparently not! I am basically trying to output results of a query to Excel using PHPExcel. My query works fine, my variables are being passed correctly, my database connection is fine...within my while () loop the echo This .$stat_year. .$stat_month.br /\n; properly returns all of the years and months for my query. But for whatever reason when I try and put those vaules in the Excel objects and export the spreadsheet, the values of my cells are always blank. Any ideas? /** PHPExcel */ include 'PHPExcel.php'; /** PHPExcel_Writer_Excel2007 */ include 'PHPExcel/Writer/Excel5.php'; // Create new PHPExcel object echo date('H:i:s') . Create new PHPExcel objectbr /\n; $objPHPExcel = new PHPExcel(); // Add some data, we will use printing features $name_code = $_GET['name']; // echo $name; $case_age = $_GET['case_age']; // echo $case_age; $case_cat = $_GET['case_cat']; // echo $case_cat; $case_status = $_GET['case_status']; // echo $case_status; include 'Conn/prpr_ifx.php'; if (!$connect_id = ifx_connect([EMAIL PROTECTED], $user, $pass)) { // THE ACTUAL CONNECTION echo Unable to connect to Informix Database\n; // DISPLAY IF CONNECTION FAILS exit(); } $sql = SELECT * FROM brev_pending_summary_detail WHERE name = '$name_code'; if (!empty($case_age)) { $sql.= AND case_age_group = '$case_age'; } if (!empty($case_cat)) { $sql.= AND case_category = '$case_cat'; } if (!empty($case_status)) { $sql.= AND case_status = '$case_status'; } // Start our query of the database $query = ifx_query($sql, $connect_id); echo date('H:i:s') . Add some databr /\n; if(!empty($query)) { while ($row = ifx_fetch_row($query)) { $stat_year = $row['stat_year']; $stat_month = $row['stat_month']; echo This .$stat_year. .$stat_month.br /\n; $objPHPExcel-getActiveSheet()-setCellValue('A' . $stat_year); $objPHPExcel-getActiveSheet()-setCellValue('B' . $stat_month); } } // Set header and footer. When no different headers for odd/even are used, odd header is assumed. echo date('H:i:s') . Set header/footerbr /\n; $objPHPExcel-getActiveSheet()-getHeaderFooter()-setOddHeader('CHPlease treat this document as confidential!'); $objPHPExcel-getActiveSheet()-getHeaderFooter()-setOddFooter('LB' . $objPHPExcel-getProperties()-getTitle() . 'RPage P of N'); // Set page orientation and size echo date('H:i:s') . Set page orientation and sizebr /\n; $objPHPExcel-getActiveSheet()-getPageSetup()-setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $objPHPExcel-getActiveSheet()-getPageSetup()-setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $objPHPExcel-getActiveSheet()-getColumnDimension('A')-setAutoSize(true); $objPHPExcel-getActiveSheet()-getColumnDimension('B')-setAutoSize(true); // Rename sheet echo date('H:i:s') . Rename sheetbr /\n; $objPHPExcel-getActiveSheet()-setTitle('Pending Summary'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel-setActiveSheetIndex(0); // Save Excel 5 file echo date('H:i:s') . Write to Excel2005 formatbr /\n; $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); $objWriter-save(str_replace('.php', '.xls', __FILE__)); // Echo done echo date('H:i:s') . Done writing file.\r\n;
Re: [PHP] PHPExcel
Dan Shirah wrote: Happy 4th of July! I am having a little bit of a problem using PHPExcel. I think I'm doing everything right but apparently not! I am basically trying to output results of a query to Excel using PHPExcel. My query works fine, my variables are being passed correctly, my database connection is fine...within my while () loop the echo This .$stat_year. .$stat_month.br /\n; properly returns all of the years and months for my query. But for whatever reason when I try and put those vaules in the Excel objects and export the spreadsheet, the values of my cells are always blank. Any ideas? /** PHPExcel */ include 'PHPExcel.php'; /** PHPExcel_Writer_Excel2007 */ include 'PHPExcel/Writer/Excel5.php'; // Create new PHPExcel object echo date('H:i:s') . Create new PHPExcel objectbr /\n; $objPHPExcel = new PHPExcel(); // Add some data, we will use printing features $name_code = $_GET['name']; // echo $name; $case_age = $_GET['case_age']; // echo $case_age; $case_cat = $_GET['case_cat']; // echo $case_cat; $case_status = $_GET['case_status']; // echo $case_status; include 'Conn/prpr_ifx.php'; if (!$connect_id = ifx_connect([EMAIL PROTECTED], $user, $pass)) { // THE ACTUAL CONNECTION echo Unable to connect to Informix Database\n; // DISPLAY IF CONNECTION FAILS exit(); } $sql = SELECT * FROM brev_pending_summary_detail WHERE name = '$name_code'; if (!empty($case_age)) { $sql.= AND case_age_group = '$case_age'; } if (!empty($case_cat)) { $sql.= AND case_category = '$case_cat'; } if (!empty($case_status)) { $sql.= AND case_status = '$case_status'; } // Start our query of the database $query = ifx_query($sql, $connect_id); echo date('H:i:s') . Add some databr /\n; if(!empty($query)) { while ($row = ifx_fetch_row($query)) { $stat_year = $row['stat_year']; $stat_month = $row['stat_month']; echo This .$stat_year. .$stat_month.br /\n; $objPHPExcel-getActiveSheet()-setCellValue('A' . $stat_year); $objPHPExcel-getActiveSheet()-setCellValue('B' . $stat_month); } } I see you calling the method getActiveSheet(). My guess is that it does exactly what it says it does. Problem is, is that you are not **setting** the active sheet until after this point. Maybe try setting all the paramaters before you start adding data to the sheet? That might fix it. // Set header and footer. When no different headers for odd/even are used, odd header is assumed. echo date('H:i:s') . Set header/footerbr /\n; $objPHPExcel-getActiveSheet()-getHeaderFooter()-setOddHeader('CHPlease treat this document as confidential!'); $objPHPExcel-getActiveSheet()-getHeaderFooter()-setOddFooter('LB' . $objPHPExcel-getProperties()-getTitle() . 'RPage P of N'); // Set page orientation and size echo date('H:i:s') . Set page orientation and sizebr /\n; $objPHPExcel-getActiveSheet()-getPageSetup()-setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $objPHPExcel-getActiveSheet()-getPageSetup()-setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $objPHPExcel-getActiveSheet()-getColumnDimension('A')-setAutoSize(true); $objPHPExcel-getActiveSheet()-getColumnDimension('B')-setAutoSize(true); // Rename sheet echo date('H:i:s') . Rename sheetbr /\n; $objPHPExcel-getActiveSheet()-setTitle('Pending Summary'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel-setActiveSheetIndex(0); // Save Excel 5 file echo date('H:i:s') . Write to Excel2005 formatbr /\n; $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); $objWriter-save(str_replace('.php', '.xls', __FILE__)); // Echo done echo date('H:i:s') . Done writing file.\r\n; -- Jim Lucas Some men are born to greatness, some achieve greatness, and some have greatness thrust upon them. Twelfth Night, Act II, Scene V by William Shakespeare -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel
Jim, I placed it above like you said, but i get the same result. I think I need to do something like the below: if(!empty($query)) { while ($row = ifx_fetch_row($query)) { for ($i = 3; $i = 50; $i++) { $stat_year = $row['stat_year']; $stat_month = $row['stat_month']; $name = $row['_name']; $case_number = $row['case_number']; echo This .$stat_year. .$stat_month. .$j_name. .$case_number.br /\n; $objPHPExcel-getActiveSheet()-setCellValue('A' . $i, $stat_year); $objPHPExcel-getActiveSheet()-setCellValue('B' . $i, $stat_month); $objPHPExcel-getActiveSheet()-setCellValue('C' . $i, $name); $objPHPExcel-getActiveSheet()-setCellValue('D' . $i, $case_number); } } } To get it to work I need to set the $i value to the starting cell I want. so, $i = 3 means that setCellValue('A' . $i) means the output will start populating the data in Excel cell A3. If I use the code above I get an output of the exact same record in rows 1-50. Maybe I need to put my query results into an array and use the $i value to select that row of data from the array also?
Re: [PHP] PHPExcel
Dan Shirah wrote: Jim, I placed it above like you said, but i get the same result. I think I need to do something like the below: if(!empty($query)) { while ($row = ifx_fetch_row($query)) { for ($i = 3; $i = 50; $i++) { $stat_year = $row['stat_year']; $stat_month = $row['stat_month']; $name = $row['_name']; $case_number = $row['case_number']; echo This .$stat_year. .$stat_month. .$j_name. .$case_number.br /\n; $objPHPExcel-getActiveSheet()-setCellValue('A' . $i, $stat_year); $objPHPExcel-getActiveSheet()-setCellValue('B' . $i, $stat_month); $objPHPExcel-getActiveSheet()-setCellValue('C' . $i, $name); $objPHPExcel-getActiveSheet()-setCellValue('D' . $i, $case_number); } } } To get it to work I need to set the $i value to the starting cell I want. so, $i = 3 means that setCellValue('A' . $i) means the output will start populating the data in Excel cell A3. If I use the code above I get an output of the exact same record in rows 1-50. Maybe I need to put my query results into an array and use the $i value to select that row of data from the array also? Looking at the example at the following URL http://www.codeplex.com/PHPExcel/Wiki/View.aspx?title=ExamplesreferringTitle=Home You can see that infact they do give the column/row combo in the setCellValue() method. -- Jim Lucas Some men are born to greatness, some achieve greatness, and some have greatness thrust upon them. Twelfth Night, Act II, Scene V by William Shakespeare -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel
On Thu, 2008-07-03 at 08:59 -0400, Dan Shirah wrote: Happy 4th of July! How did you know it was my sisters birthday today? Been an exciting week, first Canada's birthday was on Monday, now my sister's birthday today. What a thrill ride. :) Cheers, Rob. I am having a little bit of a problem using PHPExcel. I think I'm doing everything right but apparently not! I am basically trying to output results of a query to Excel using PHPExcel. My query works fine, my variables are being passed correctly, my database connection is fine...within my while () loop the echo This .$stat_year. .$stat_month.br /\n; properly returns all of the years and months for my query. But for whatever reason when I try and put those vaules in the Excel objects and export the spreadsheet, the values of my cells are always blank. Any ideas? /** PHPExcel */ include 'PHPExcel.php'; /** PHPExcel_Writer_Excel2007 */ include 'PHPExcel/Writer/Excel5.php'; // Create new PHPExcel object echo date('H:i:s') . Create new PHPExcel objectbr /\n; $objPHPExcel = new PHPExcel(); // Add some data, we will use printing features $name_code = $_GET['name']; // echo $name; $case_age = $_GET['case_age']; // echo $case_age; $case_cat = $_GET['case_cat']; // echo $case_cat; $case_status = $_GET['case_status']; // echo $case_status; include 'Conn/prpr_ifx.php'; if (!$connect_id = ifx_connect([EMAIL PROTECTED], $user, $pass)) { // THE ACTUAL CONNECTION echo Unable to connect to Informix Database\n; // DISPLAY IF CONNECTION FAILS exit(); } $sql = SELECT * FROM brev_pending_summary_detail WHERE name = '$name_code'; if (!empty($case_age)) { $sql.= AND case_age_group = '$case_age'; } if (!empty($case_cat)) { $sql.= AND case_category = '$case_cat'; } if (!empty($case_status)) { $sql.= AND case_status = '$case_status'; } // Start our query of the database $query = ifx_query($sql, $connect_id); echo date('H:i:s') . Add some databr /\n; if(!empty($query)) { while ($row = ifx_fetch_row($query)) { $stat_year = $row['stat_year']; $stat_month = $row['stat_month']; echo This .$stat_year. .$stat_month.br /\n; $objPHPExcel-getActiveSheet()-setCellValue('A' . $stat_year); $objPHPExcel-getActiveSheet()-setCellValue('B' . $stat_month); } } // Set header and footer. When no different headers for odd/even are used, odd header is assumed. echo date('H:i:s') . Set header/footerbr /\n; $objPHPExcel-getActiveSheet()-getHeaderFooter()-setOddHeader('CHPlease treat this document as confidential!'); $objPHPExcel-getActiveSheet()-getHeaderFooter()-setOddFooter('LB' . $objPHPExcel-getProperties()-getTitle() . 'RPage P of N'); // Set page orientation and size echo date('H:i:s') . Set page orientation and sizebr /\n; $objPHPExcel-getActiveSheet()-getPageSetup()-setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $objPHPExcel-getActiveSheet()-getPageSetup()-setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $objPHPExcel-getActiveSheet()-getColumnDimension('A')-setAutoSize(true); $objPHPExcel-getActiveSheet()-getColumnDimension('B')-setAutoSize(true); // Rename sheet echo date('H:i:s') . Rename sheetbr /\n; $objPHPExcel-getActiveSheet()-setTitle('Pending Summary'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel-setActiveSheetIndex(0); // Save Excel 5 file echo date('H:i:s') . Write to Excel2005 formatbr /\n; $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); $objWriter-save(str_replace('.php', '.xls', __FILE__)); // Echo done echo date('H:i:s') . Done writing file.\r\n; -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel
Happy 4th of July! How did you know it was my sisters birthday today? Been an exciting week, first Canada's birthday was on Monday, now my sister's birthday today. What a thrill ride. Isn't it the 3rd? -- Richard Heyes Employ me: http://www.phpguru.org/cv -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel
Richard Heyes wrote: Happy 4th of July! How did you know it was my sisters birthday today? Been an exciting week, first Canada's birthday was on Monday, now my sister's birthday today. What a thrill ride. Isn't it the 3rd? It is in the great Republic of Texas. -Shawn -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel
On 7/3/08, Shawn McKenzie [EMAIL PROTECTED] wrote: Richard Heyes wrote: Happy 4th of July! How did you know it was my sisters birthday today? Been an exciting week, first Canada's birthday was on Monday, now my sister's birthday today. What a thrill ride. Isn't it the 3rd? It is in the great Republic of Texas. -Shawn It is even in the sub-tropical part of Texas known as Houston. But this isn't a day of celebration for you guys, Richard, to be sure. Do you all just ignore the day, or do you hold some kind of anti-celebration? David
RE: [PHP] PHPExcel
-Original Message- From: David Giragosian [mailto:[EMAIL PROTECTED] Sent: Thursday, July 03, 2008 11:19 AM To: php-general@lists.php.net Subject: Re: [PHP] PHPExcel On 7/3/08, Shawn McKenzie [EMAIL PROTECTED] wrote: Richard Heyes wrote: Happy 4th of July! How did you know it was my sisters birthday today? Been an exciting week, first Canada's birthday was on Monday, now my sister's birthday today. What a thrill ride. Isn't it the 3rd? It is in the great Republic of Texas. It is even in the sub-tropical part of Texas known as Houston. But this isn't a day of celebration for you guys, Richard, to be sure. Do you all just ignore the day, or do you hold some kind of anti-celebration? Heh... almost got in trouble at work for laughing so loud at that one. Vive le revolucion! Todd Boyd Web Programmer
Re: [PHP] PHPExcel
So how would I go about getting each result from my query into the columns/rows? Should I do my query, and then assign the first column to an array, the second to another array, etc? And then try to loop it using $i to count each row? And just FYI, I put Happy 4th (even though today is the 3rd) because I won't be checking my email tomorrow :)
Re: [PHP] PHPExcel
On Thu, 2008-07-03 at 16:36 +0100, Richard Heyes wrote: Happy 4th of July! How did you know it was my sisters birthday today? Been an exciting week, first Canada's birthday was on Monday, now my sister's birthday today. What a thrill ride. Isn't it the 3rd? *doh* I took for granted someone else had the date right... I rarely know what day it is :) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel
GOT IT!!! I changed my code to the following: $sql = SELECT * FROM brev_pending_summary_detail WHERE name = '$name_code'; if (!empty($case_age)) { $sql.= AND case_age_group = '$case_age'; } if (!empty($case_cat)) { $sql.= AND case_category = '$case_cat'; } if (!empty($case_status)) { $sql.= AND case_status = '$case_status'; } // Start our query of the database $query = ifx_query($sql, $connect_id); $row = ifx_fetch_row($query); $results = array(); echo date('H:i:s') . Add some databr /\n; for ($i = 3, $j = 0; $i = $count; $i++, $j++) { $results[$j] = ifx_fetch_row($query); $objPHPExcel-getActiveSheet()-setCellValue('A' . $i, $results[$j]['stat_year']); $objPHPExcel-getActiveSheet()-setCellValue('B' . $i, $results[$j]['stat_month']); $objPHPExcel-getActiveSheet()-setCellValue('C' . $i, $results[$j]['name']); $objPHPExcel-getActiveSheet()-setCellValue('D' . $i, $results[$j]['case_age_group']); } I created a seperate query to get the row count...created an empty array ( $results = array(); ) I set $i = 3 so my results will start printing on line 3 of the spreadsheet I set $j = 0 so the row data will start at the first row of the results from my query Then I made the loop repeat until $i became equal to the total number of rows And I then set the value of my previsouly empty array equal to the current counted row of my query results And to round it all off I echo'd out my results by using the new $results($j) value appended with my database column names ['stat_year'] etc. AWESOME! I hope someone else learns from this or can use this!
Re: [PHP] PHPExcel
UGH! I am now constantly getting an error of PHP Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 936 bytes) on line 689 Is there a way to continuously write to the file and avoid getting this error? On 7/3/08, Dan Shirah [EMAIL PROTECTED] wrote: GOT IT!!! I changed my code to the following: $sql = SELECT * FROM brev_pending_summary_detail WHERE name = '$name_code'; if (!empty($case_age)) { $sql.= AND case_age_group = '$case_age'; } if (!empty($case_cat)) { $sql.= AND case_category = '$case_cat'; } if (!empty($case_status)) { $sql.= AND case_status = '$case_status'; } // Start our query of the database $query = ifx_query($sql, $connect_id); $row = ifx_fetch_row($query); $results = array(); echo date('H:i:s') . Add some databr /\n; for ($i = 3, $j = 0; $i = $count; $i++, $j++) { $results[$j] = ifx_fetch_row($query); $objPHPExcel-getActiveSheet()-setCellValue('A' . $i, $results[$j]['stat_year']); $objPHPExcel-getActiveSheet()-setCellValue('B' . $i, $results[$j]['stat_month']); $objPHPExcel-getActiveSheet()-setCellValue('C' . $i, $results[$j]['name']); $objPHPExcel-getActiveSheet()-setCellValue('D' . $i, $results[$j]['case_age_group']); } I created a seperate query to get the row count...created an empty array ( $results = array(); ) I set $i = 3 so my results will start printing on line 3 of the spreadsheet I set $j = 0 so the row data will start at the first row of the results from my query Then I made the loop repeat until $i became equal to the total number of rows And I then set the value of my previsouly empty array equal to the current counted row of my query results And to round it all off I echo'd out my results by using the new $results($j) value appended with my database column names ['stat_year'] etc. AWESOME! I hope someone else learns from this or can use this!
Re: [PHP] PHPExcel
But this isn't a day of celebration for you guys, Richard, to be sure. Depends how you look at it. My passing out parade (Navy not booze) was on the 4th July. Do you all just ignore the day, or do you hold some kind of anti-celebration? Well for me personally it's just another day. Can't speak for the rest of the populace. -- Richard Heyes Employ me: http://www.phpguru.org/cv -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel
*doh* I took for granted someone else had the date right... I rarely know what day it is :) It's not often I know what day it is... :-) -- Richard Heyes Employ me: http://www.phpguru.org/cv -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel
Richard Heyes wrote: *doh* I took for granted someone else had the date right... I rarely know what day it is :) It's not often I know what day it is... :-) Oops, sorry, I read your message wrong. -- Richard Heyes Employ me: http://www.phpguru.org/cv -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel
On Thu, Jul 03, 2008 at 02:36:18PM -0400, Dan Shirah wrote: UGH! I am now constantly getting an error of PHP Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 936 bytes) on line 689 Is there a way to continuously write to the file and avoid getting this error? Hi Dan... change the value of memory_limit in your php.ini to a greater value than 16M. Greetings Mario -- -BEGIN GEEK CODE BLOCK- Version: 3.12 GCS/CM d- s++: a+ C$ UBL*$ P++ L+++ E--- W+++ N+ o-- K- w O- M- V-- PS++ PE++ Y PGP+++ t--- 5 X R++ tv- b+++ DI D G++ e* h r+++ y --END GEEK CODE BLOCK-- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel
I changed the max memory limit to 512MB and the max timeput to 60... I'm trying to export a report that only has 3,600 rows and it still exceeds my memory size/timeout. any suggestions that I could make to my code to make it more efficient and not use up so much memory? On 7/3/08, Mario Guenterberg [EMAIL PROTECTED] wrote: On Thu, Jul 03, 2008 at 02:36:18PM -0400, Dan Shirah wrote: UGH! I am now constantly getting an error of PHP Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 936 bytes) on line 689 Is there a way to continuously write to the file and avoid getting this error? Hi Dan... change the value of memory_limit in your php.ini to a greater value than 16M. Greetings Mario -- -BEGIN GEEK CODE BLOCK- Version: 3.12 GCS/CM d- s++: a+ C$ UBL*$ P++ L+++ E--- W+++ N+ o-- K- w O- M- V-- PS++ PE++ Y PGP+++ t--- 5 X R++ tv- b+++ DI D G++ e* h r+++ y --END GEEK CODE BLOCK-- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel
Dan Shirah wrote: UGH! I am now constantly getting an error of PHP Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 936 bytes) on line 689 Is there a way to continuously write to the file and avoid getting this error? On 7/3/08, Dan Shirah [EMAIL PROTECTED] wrote: GOT IT!!! I changed my code to the following: $sql = SELECT * FROM brev_pending_summary_detail WHERE name = '$name_code'; if (!empty($case_age)) { $sql.= AND case_age_group = '$case_age'; } if (!empty($case_cat)) { $sql.= AND case_category = '$case_cat'; } if (!empty($case_status)) { $sql.= AND case_status = '$case_status'; } // Start our query of the database $query = ifx_query($sql, $connect_id); $row = ifx_fetch_row($query); $results = array(); echo date('H:i:s') . Add some databr /\n; for ($i = 3, $j = 0; $i = $count; $i++, $j++) { $results[$j] = ifx_fetch_row($query); $objPHPExcel-getActiveSheet()-setCellValue('A' . $i, $results[$j]['stat_year']); $objPHPExcel-getActiveSheet()-setCellValue('B' . $i, $results[$j]['stat_month']); $objPHPExcel-getActiveSheet()-setCellValue('C' . $i, $results[$j]['name']); $objPHPExcel-getActiveSheet()-setCellValue('D' . $i, $results[$j]['case_age_group']); } I created a seperate query to get the row count...created an empty array ( $results = array(); ) I set $i = 3 so my results will start printing on line 3 of the spreadsheet I set $j = 0 so the row data will start at the first row of the results from my query Then I made the loop repeat until $i became equal to the total number of rows And I then set the value of my previsouly empty array equal to the current counted row of my query results And to round it all off I echo'd out my results by using the new $results($j) value appended with my database column names ['stat_year'] etc. AWESOME! I hope someone else learns from this or can use this! What is on and around line 689? Unless you need the $results array later after the for loop you should overwrite it each time, so use $results = ifx_fetch_row($query); instead. Maybe this: $i = 3; while ($results = ifx_fetch_row($query)) { $objPHPExcel-getActiveSheet()-setCellValue('A' . $i, $results['stat_year']); $objPHPExcel-getActiveSheet()-setCellValue('B' . $i, $results['stat_month']); $objPHPExcel-getActiveSheet()-setCellValue('C' . $i, $results['name']); $objPHPExcel-getActiveSheet()-setCellValue('D' . $i, $results['case_age_group']); $i++; } -Shawn -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel
On 7/3/08, Dan Shirah [EMAIL PROTECTED] wrote: GOT IT!!! I changed my code to the following: $sql = SELECT * FROM brev_pending_summary_detail WHERE name = '$name_code'; if (!empty($case_age)) { $sql.= AND case_age_group = '$case_age'; } if (!empty($case_cat)) { $sql.= AND case_category = '$case_cat'; } if (!empty($case_status)) { $sql.= AND case_status = '$case_status'; } // Start our query of the database $query = ifx_query($sql, $connect_id); Also, why are you fetching a row here? Then in your loop you will have missed the first one. $row = ifx_fetch_row($query); $results = array(); echo date('H:i:s') . Add some databr /\n; for ($i = 3, $j = 0; $i = $count; $i++, $j++) { $results[$j] = ifx_fetch_row($query); $objPHPExcel-getActiveSheet()-setCellValue('A' . $i, $results[$j]['stat_year']); $objPHPExcel-getActiveSheet()-setCellValue('B' . $i, $results[$j]['stat_month']); $objPHPExcel-getActiveSheet()-setCellValue('C' . $i, $results[$j]['name']); $objPHPExcel-getActiveSheet()-setCellValue('D' . $i, $results[$j]['case_age_group']); } I created a seperate query to get the row count...created an empty array ( $results = array(); ) I set $i = 3 so my results will start printing on line 3 of the spreadsheet I set $j = 0 so the row data will start at the first row of the results from my query Then I made the loop repeat until $i became equal to the total number of rows And I then set the value of my previsouly empty array equal to the current counted row of my query results And to round it all off I echo'd out my results by using the new $results($j) value appended with my database column names ['stat_year'] etc. AWESOME! I hope someone else learns from this or can use this! What is on and around line 689? Unless you need the $results array later after the for loop you should overwrite it each time, so use $results = ifx_fetch_row($query); instead. Maybe this: $i = 3; while ($results = ifx_fetch_row($query)) { $objPHPExcel-getActiveSheet()-setCellValue('A' . $i, $results['stat_year']); $objPHPExcel-getActiveSheet()-setCellValue('B' . $i, $results['stat_month']); $objPHPExcel-getActiveSheet()-setCellValue('C' . $i, $results['name']); $objPHPExcel-getActiveSheet()-setCellValue('D' . $i, $results['case_age_group']); $i++; } -Shawn -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel
Dan Shirah wrote: GOT IT!!! I changed my code to the following: $sql = SELECT * FROM brev_pending_summary_detail WHERE name = '$name_code'; if (!empty($case_age)) { $sql.= AND case_age_group = '$case_age'; } if (!empty($case_cat)) { $sql.= AND case_category = '$case_cat'; } if (!empty($case_status)) { $sql.= AND case_status = '$case_status'; } // Start our query of the database $query = ifx_query($sql, $connect_id); $row = ifx_fetch_row($query); $results = array(); echo date('H:i:s') . Add some databr /\n; for ($i = 3, $j = 0; $i = $count; $i++, $j++) { $results[$j] = ifx_fetch_row($query); $objPHPExcel-getActiveSheet()-setCellValue('A' . $i, $results[$j]['stat_year']); $objPHPExcel-getActiveSheet()-setCellValue('B' . $i, $results[$j]['stat_month']); $objPHPExcel-getActiveSheet()-setCellValue('C' . $i, $results[$j]['name']); $objPHPExcel-getActiveSheet()-setCellValue('D' . $i, $results[$j]['case_age_group']); } Give this a try, should save you some memory! ?php $sql = SELECT * FROM brev_pending_summary_detail WHEREname = '{$name_code}'; if (!empty($case_age)) { $sql.= AND case_age_group = '{$case_age}'; } if (!empty($case_cat)) { $sql.= AND case_category = '{$case_cat}'; } if (!empty($case_status)) { $sql.= AND case_status = '{$case_status}'; } // Start our query of the database $query = ifx_query($sql, $connect_id); echo date('H:i:s') . Add some databr /\n; $offSet = 3; $row = ifx_fetch_row($query, NEXT); while ( is_array($row) ) { $objPHPExcel-getActiveSheet()-setCellValue('A' . $offSet, $row['stat_year']); $objPHPExcel-getActiveSheet()-setCellValue('B' . $offSet, $row['stat_month']); $objPHPExcel-getActiveSheet()-setCellValue('C' . $offSet, $row['name']); $objPHPExcel-getActiveSheet()-setCellValue('D' . $offSet, $row['case_age_group']); $offSet++; $row = ifx_fetch_row($query, NEXT); } ifx_free_result($query); ? -- Jim Lucas Some men are born to greatness, some achieve greatness, and some have greatness thrust upon them. Twelfth Night, Act II, Scene V by William Shakespeare -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHPExcel
Shawn McKenzie wrote: On 7/3/08, Dan Shirah [EMAIL PROTECTED] wrote: GOT IT!!! I changed my code to the following: $sql = SELECT * FROM brev_pending_summary_detail WHERE name = '$name_code'; if (!empty($case_age)) { $sql.= AND case_age_group = '$case_age'; } if (!empty($case_cat)) { $sql.= AND case_category = '$case_cat'; } if (!empty($case_status)) { $sql.= AND case_status = '$case_status'; } // Start our query of the database $query = ifx_query($sql, $connect_id); Also, why are you fetching a row here? Then in your loop you will have missed the first one. $row = ifx_fetch_row($query); $results = array(); It looks like he was following the example on the ifx_fetch_row() manual page. If you look at the arguments and examples, you will see that same type of concept. Problem is he didn't follow it in the for loop. Check my other email for the example that I submitted that he should try. -- Jim Lucas Some men are born to greatness, some achieve greatness, and some have greatness thrust upon them. Twelfth Night, Act II, Scene V by William Shakespeare -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php