Several minor problems encountered during PHPExcel usage

Several problems encountered during PHPExcel usage recently I have been engaged in PHPExcel several times and encountered several problems during the process, the common usage of PHPExcel and the problems I encountered and solutions are summarized as follows. (I suggest you check the PHPExcel official manual )? I. common usage? PHPExcel problems

Recently, I have been engaged in PHPExcel several times and encountered several problems. some common usage of PHPExcel and my problems and solutions are summarized as follows. (We recommend you refer to the PHPExcel official manual)

?

I. common usage

???There is a lot of information on this part of the website. I will not list them here, but I just wrote some of the places I used.

?

 GetProperties ()-> setCreator ("Miss Yang")-> setLastModifiedBy ("Miss Yang")-> setTitle ("Office 2003 XLS Document ") -> setSubject ("Office 2003 XLS Document")-> setDescription ("TrunkBow")-> setKeywords ("TrunkBow")-> setCategory ("TrunkBow "); // set the current sheet index for subsequent content operations. // By default, PHPExcel will automatically create the first sheet with the SheetIndex set to 0 $ objExcel-> setActiveSheetIndex (0); $ objActSheet = $ objExcel-> getActiveSheet (); // Set the name of the current active sheet $ objActSheet-> setTitle ('sheet1'); // Set the cell content $ objActSheet-> setCellValue ('A1', 'string content '); // String content $ objActSheet-> setCellValue ('A2 ', 26); // value $ objActSheet-> setCellValue ('A3', true ); // boolean value $ objActSheet-> setCellValue ('A4 ',' = SUM (A2: A2) '); // formula // explicitly specify the content type $ objAc TSheet-> setCellValueExplicit ('a5 ', '000000', PHPExcel_Cell_DataType: TYPE_STRING); // Set the cell format $ objActSheet-> getStyle ('A1')-> getFont () -> getColor ()-> setARGB (PHPExcel_Style_Color: COLOR_RED); $ objActSheet-> getStyle ('A1')-> getFont ()-> setBold (true ); $ objActSheet-> getStyle ('A')-> getNumberFormat ()-> setFormatCode (PHPExcel_Style_NumberFormat: FORMAT_TEXT); $ objActSheet-> getColumnDimension ('A')-> setWidth (20); $ objActSheet-> getColumnDimension ('B')-> setAutoSize (true); $ activeSheet-> getColumnDimension ('C ') -> setVisible (false); // Generate the drop-down list box $ list = "item1, item2, item3"; $ objValidation1 = $ activeSheet-> getCell ('A1 ') -> getDataValidation (); $ objValidation1-> setType (PHPExcel_Cell_DataValidation: TYPE_LIST)-> setErrorStyle (Character: STYLE_INFORMATION)-> setAllowBlank (fals E)-> setShowInputMessage (true)-> setShowErrorMessage (true)-> setShowDropDown (true)-> setErrorTitle ('incorrect input value ') -> setError ('The value you entered is not in the drop-down list. ')-> setPromptTitle ('drop-down selection box')-> setPrompt ('select the value from the drop-down box! ')-> SetFormula1 ('"'. $ list. '"'); // download the 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. problem summary

1. when the cell content is long numbers, the output is always converted into scientific notation.

?

??? Many materials have been mentioned on the internet about this issue, most of which are about to modify the source code in several places. However, I have never found the corresponding source code, which may be caused by different versions. I solve it like this:

?

// Display the specified content type $ objActSheet-> setCellValueExplicit ('a5 ', '000000', PHPExcel_Cell_DataType: TYPE_STRING );

??

2. php excel occupies too much memory.

?

PHPExcel is a powerful PHP open-source class for processing Excel, but a major problem is that it occupies too much memory. from 1.7.3, PHPExcel supports cell caching, however, we recommend that you use the current stable version 1.7.6, because previous versions may have bugs to varying degrees.

The official website says this: PHPExcel uses 1 k/cell memory on average, so large files will cause rapid memory consumption. The cell Cache mechanism allows PHPExcel to cache small cell objects in memory in disks, APC, memcache, or Wincache, although it takes some time to read data, but it can help you reduce memory consumption. By default, PHPExcel still saves cell objects in memory, but you can customize them. You can use the PHPExcel_Settings: setCacheStorageMethod () method to pass the cache method as a parameter to this method to set the cache method.

??

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory; PHPExcel_Settings::setCacheStorageMethod($cacheMethod); 

?

?

The setCacheStorageMethod () method returns a boolean variable to indicate whether the setting is successful. (for example, if APC cannot be used, if you set to use APC cache, false is returned ). Each worksheet has an independent cache. when a worksheet is instantiated, it is automatically created based on the configured or configured cache mode. Once you start to read a file or you have created the first worksheet, you cannot change the caching method. Currently, the following cache methods are available:

1). PHPExcel_CachedObjectStorageFactory: cache_in_memory;

By default, if you do not initialize any caching method, PHPExcel uses the memory cache method.

2). PHPExcel_CachedObjectStorageFactory: cache_in_memory_serialized ;??

Using this cache method, cells are stored in the memory in a serialized manner, which is a solution to reduce memory usage performance.

3). PHPExcel_CachedObjectStorageFactory: cache_in_memory_gzip;

Similar to the serialization method, this method is compressed by gzip and then put into the memory after serialization. this method further reduces memory usage, but reading and writing will be slow.

4). PHPExcel_CachedObjectStorageFactory: cache_to_discISAM;

When cache_to_discISAM is used, all cells will be saved in a temporary disk file, and only their location in the file will be saved in the PHP memory, this is slower than any caching method in the memory, but can significantly reduce the memory usage. Temporary disk files are automatically deleted after the script is run.

5). PHPExcel_CachedObjectStorageFactory: cache_to_phpTemp;

????? Similar to cache_to_discISAM, when cache_to_phpTemp is used, all cells will still exist in the php: // temp I/O stream and only store their locations in the PHP memory. The PHP: // memory package of php stores data in the memory. php: // temp performs similar operations, but when the size of the stored data exceeds the memory limit, the data will be stored in the temporary file. the default size is 1 MB, but you can modify it during initialization:

$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp; $cacheSettings = array( ' memoryCacheSize ' => '8MB' ); PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); 

?

?

Php: // The temp file will be automatically deleted after the script ends.

?

6). PHPExcel_CachedObjectStorageFactory: cache_to_apc;

When cach_to_apc is used, the cell is saved in APC and only the index is saved in memory. By default, the APC cache timeout time is 600 seconds, which is sufficient for most applications. you can also modify the value during initialization:

?

?

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_APC; $cacheSettings = array( 'cacheTime' => 600 ); PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); ?

?

?

When the script stops running, all the data will be clear from the APC (ignore cache time). this mechanism 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 only the index is saved in memory. By default, PHPExcel searches for the memcache service on localhost and port 11211. the timeout time is 600 seconds. if you run the memcache service on another server or port, you can modify it during initialization:

?

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache; $cacheSettings = array( 'memcacheServer'=> 'localhost',                                        'memcachePort' => 11211,                                        'cacheTime' => 600 );  PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); 

??

??????? In terms of initialization settings, MS does not support multiple memcache servers polling.

? When the script ends, all data is cleared from memcache (the cache time is ignored). this mechanism cannot be used for persistent storage.

?

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

?

?

??? Let's take a look at what the manual says:

???

It is important to remember that any string maid in an Excel formula is allowed to be maximum 255 characters (not bytes ).

?

???

???? When the data source in the drop-down list is too long (more than 255 characters), the drop-down list will show incorrect (I encountered the following situation: this drop-down list shows the data sources of other normal drop-down lists ).

Here are my solutions:

// Solve the problem that the source string length of the data in the drop-down box is too large: break 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);} $ objValidation2 = $ activeSheet-> getCell ("A1")-> getDat AValidation (); $ objValidation2-> setType (PHPExcel_Cell_DataValidation: TYPE_LIST)-> setErrorStyle (Rule: STYLE_INFORMATION)-> setAllowBlank (true)-> setShowInputMessage (true) -> setShowErrorMessage (true)-> setShowDropDown (true)-> setErrorTitle ('incorrect input value ')-> setError ('Your input value is not in the drop-down list. ')-> setPromptTitle ('drop-down selection box')-> setPrompt ('select the value from the drop-down box! '); If ($ str_len <255) $ objValidation2-> setFormula1 (' "'. $ str_list.'" '); else $ objValidation2-> setFormula1 ("sheet1! P1: {$ endcell }");

?

4. the local operation is normal, and PHPExcel cannot run on the server.

Official 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)

? In addition, check whether the iconv extension is normal. PHPExcel requires its support.

?

?

?