Several minor problems encountered in the use of Phpexcel

Several problems encountered in the use of Phpexcel

Recently done projects, contact several phpexcel, the process encountered a few problems, now some common usage of phpexcel and I encountered problems and solutions summarized as follows. (It is recommended to take a look at the phpexcel official website manual)

?

First, common usage

??? This part of the site has a lot of information, I am no longer one by one listed, just a few places I used to write a bit.

?

 GetProperties ()->setcreator ("Miss yang")->setlastmodifiedby ("Miss yang")->settitle ("Office 2003 XLS DOCU ment ")->setsubject (" Office 2003 XLS Document ")->setdescription (" Trunkbow ")->setkeywords (" Trunkbow ")-&gt        ; Setcategory ("Trunkbow");  Sets the current sheet index for subsequent content operations. By default, Phpexcel automatically creates the first sheet is set sheetindex=0 $objExcel->setactivesheetindex (0);    $objActSheet = $objExcel->getactivesheet ();  Sets the name of the currently active sheet $objActSheet->settitle (' Sheet1 ');  Set cell contents $objActSheet->setcellvalue (' A1 ', ' string contents ');            String content $objActSheet->setcellvalue (' A2 ', 26);          Numeric $objActSheet->setcellvalue (' A3 ', true); Boolean value $objActSheet->setcellvalue (' A4 ', ' =sum (A2:A2) '); Formula//explicitly specify content type $objActSheet->setcellvalueexplicit (' A5 ', ' 8757584 ', phpexcel_cell_datatype::type_string);// Format cell $objactsheet->getstyle (' A1 ')->getfont ()->getcolor ()->setargb (Phpexcel_style_color::color_red); $objActSheet->getstyle (' A1 ')->getfont ()->setbold (true); $objActSheet->getstyle (' A ')->getnumberformat ()->setformatcode (phpexcel_style_number Format::format_text); $objActSheet->getcolumndimension (' A ')->setwidth (20); $objActSheet->getcolumndimension (' B ')->setautosize (true); $activeSheet->getcolumndimension (' C ')->setvisible (false); Generate drop-down list box $list = "Item1,item2,item3"; $objValidation 1 = $activeSheet->getcell (' A1 ')->getdatavalidation (); $ Objvalidation1->settype (phpexcel_cell_datavalidation::type_list)->seterrorstyle (PHPExcel_Cell_DataV               Alidation::style_information)->setallowblank (False)->setshowinputmessage (true)                ->setshowerrormessage (True)->setshowdropdown (true)->seterrortitle (' incorrect value entered ') ->seterror (' The value you entered is not in the drop-down box list.') ->setprompttitle (' Dropdown selection box ')->setprompt (' Please select your desired value from the dropdown box! ')->setformula1 (' "'. $list.    '"'); Download output $filename = "Testphp.xls"; $filename = Iconv ("Utf-8", ' GBK ', $filename); $objWriter = Phpexcel_iofactory:: Createwriter ($objPHPExcel, ' Excel5 '); header (' Content-type:application/vnd.ms-excel '); Header (" Content-disposition:attachment;filename= $filename "); header (' cache-control:max-age=0 '); $objWriter->save (' PHP    ://output '); exit (0);

?

?

?

Ii. Summary of issues

1. The cell contents are long digits, and when output, it is always turned into scientific notation.

?

??? this question on the Internet also has a lot of information mentioned, mostly said to modify the source code of several places. But I have not found the corresponding source where, it may be the version of the different reasons. That's how I solved it:

?

??

2.PHPExcel too much memory is occupied.

?

Phpexcel is a very powerful PHP open source class that handles Excel , but the big problem is that it takes up too much memory, starting with 1.7.3 , which supports setting the cell 's cache. However, it is recommended to use the current stable version of 1.7.6, because the previous version will have a different degree of existence bug.

This is what the official web says:phpexcel averages the memory using 1k/ cells, so large documents can cause memory consumption too quickly. The cell cache mechanism allows phpexcel to cache small in-memory cell objects in disk or APC,memcache , or wincache . Although it takes some time to read the data, it can help you reduce the memory consumption. By default,Phpexcel still saves the cell object in memory, but you can customize it. You can use the phpexcel_settings::setcachestoragemethod () method to pass caching as a parameter to this method to set the caching mode.

??

?

?

Setcachestoragemethod () The method returns a BOOL variable to indicate whether the setting was successful (for example, if you set the APC cache when the APC is not available), it will return false. Each worksheet will have a separate cache, and when an worksheet is instantiated, it is automatically created based on how the settings or configurations are cached. Once you start reading a file or you have created the first worksheet, you cannot change the way the cache is changed. Currently, there are several ways to use the following caching methods:

1). phpexcel_cachedobjectstoragefactory::cache_in_memory;

By default, if you do not initialize any of the caching methods,Phpexcel will use the way the memory is cached.

2). phpexcel_cachedobjectstoragefactory::cache_in_memory_serialized;??

Using this caching method, cells are stored in memory in a serialized manner, which is a scenario that reduces memory utilization performance.

3). Phpexcel_cachedobjectstoragefactory::cache_in_memory_gzip;

Similar to serialization, this method, after serialization, is then gzip compressed and then put into memory, which further reduces the use of memory, but there are some slow reads and writes.

4). Phpexcel_cachedobjectstoragefactory::cache_to_discisam;

When using Cache_to_discisam this way, all the cells will be saved in a temporary disk file, only their location in the file in PHP memory, which is more than any kind of cache in memory way is slow, But it can significantly reduce the use of memory. Temporary disk files are automatically deleted at the end of the script run.

5). phpexcel_cachedobjectstoragefactory::cache_to_phptemp;

????? like Cache_to_discisam this way, when you use cache_to_phptemp , all cells still exist in the php://temp I/O stream, Just keep their location in PHP 's memory. PHP 's php://memory wrapper stores the data in memory,php://temp behaves similarly, but when the stored data size exceeds the memory limit, the data is saved in a temporary file, the default size is 1MB, but you can modify it at initialization time :

?

?

php://temp the file is automatically deleted at the end of the script.

?

6). PHPEXCEL_CACHEDOBJECTSTORAGEFACTORY::CACHE_TO_APC;

When CACH_TO_APC is used, the cell is saved in APC , and the index is saved only in memory. APC Caches the default time- out period of seconds, which is sufficient for most applications, and of course you can modify it at initialization time:

?

?

$cacheMethod = PHPEXCEL_CACHEDOBJECTSTORAGEFACTORY::CACHE_TO_APC; $cacheSettings = Array (' cacheTime ' = 600); Phpexcel_settings::setcachestoragemethod ($cacheMethod, $cacheSettings);?

?

?

When the script finishes running, all the data is clear from the APC (ignoring the cache time) and cannot be used as a persistent cache.

?

7). PHpexcel_cachedobjectstoragefactory::cache_to_memcache

??????? When cache_to_memory is used, the cell object is saved in Memcache , and the index is saved in memory only. By default,Phpexcel will look for the memcache service on localhost and port 11211 , with a time-out of If you are running the memcache service on a different server or other port, you can modify it at initialization time:

?

$cacheMethod = Phpexcel_cachedobjectstoragefactory::cache_to_memcache; $cacheSettings = Array (' memcacheserver ' = ' localhost ',                                        ' memcacheport ' = ' 11211 ',                                        ' cacheTime ' = 600);  

??

??????? from the initial setting of the formal view,MS also does not support multiple memcache Server Polling method, it is regrettable.

? When the script ends, all data is emptied from memcache (ignoring cache time) and cannot be persisted using this mechanism.

?

3. The data source of the drop-down list is too long

?

?

??? let's start by looking at what the handbook says:

???

It is important to remember the any string participating in an Excel formula are allowed to being maximum 255 characters (not bytes).

?

???

???? When the data source for the drop-down list is too long (morethan 255 characters), the drop-down list is not displayed correctly (I encountered a situation where the drop-down list shows the data source for another normal drop-down list).

Here's my workaround:

Resolve drop-down box data source string length is large: decompose each source string into an idle cell $str_list = "Item1,item2,item3,......";         $str _len = strlen ($str _list), if ($str _len>=255) {$str _list_arr = explode (', ', $str _list); if ($str _list_arr) foreach ($str _list_arr as $i = = $d) {$c = "P". (                     $i + 1);                $activeSheet->setcellvalue ($c, $d);         } $endcell = $c; $activeSheet->getcolumndimension (' P ')->setvisible (false); } $objValidation 2 = $activeSheet->getcell ("A1")->getdatavalidation (); $objValidation 2->settype (phpexcel_cell_datavalidation::type_list)->seterrorstyle (phpexcel_cell_d               Atavalidation::style_information)->setallowblank (True)->setshowinputmessage (True) ->setshowerrormessage (True)->setshowdropdown (true)->seterrortitle (' Input               Incorrect value ')->seterror (' The value you entered is not in the dropdown box list. ') ->setprompttitle (' Dropdown selection box ')               ->setprompt (' Please select your desired value from the drop-down box! if ($str _len<255) $objValidation 2->setformula1 (' "'. $str _list. '"'); else $objValidation 2->setformula1 ("sheet1!  p1:{$endcell} ");

?

4. Local operation is normal, phpexcel on the server cannot be run

Website:

????????? The following software is required to develop using Phpexcel:

?????????? ? PHP version 5.2.0 or newer

?????????? ? PHP extension php_zip enabled *)

???????????? PHP extension Php_xml enabled

???????????? PHP extension PHP_GD2 enabled (if not compiled in)

? Also, check to see if the iconv extension is normal andPhpexcel need his support

?

?

?

