1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
|
class xlsbParserMan{
// l'array de donnes a remplacer
private $dataReplace;
// le nom de fichier qui va bien
private $fileName;
// L'objet excel correspondant au template
private $objPHPExcel;
public function __construct($file, $dataReplace){
/** Error reporting */
error_reporting(E_ALL);
/** PHPExcel */
require_once './Classes/PHPExcel.php';
/** PHPExcel_IOFactory */
require_once './Classes/PHPExcel/IOFactory.php';
$this->fileName=$file;
$this->dataReplace=$dataReplace;
$this->keys=array_keys($dataReplace);
}
public function parseXlsb(){
}
public function parseXlsb2($fileName2){
$path=realpath('./');
$path=preg_replace("/\//", "\\", $path);
$this->fileName=preg_split("/\//", $this->fileName);
$this->fileName=$this->fileName[3];
$placedItem=-1;
$excel_app=new COM("Excel.application");
$excel_app->Workbooks->Close();
// PLANTAGE ICI:
$Workbook = $excel_app->Workbooks->Open($path.'\\documents\\xlsTemplates\\'.$this->fileName) or Die('Did not open filename');
//
$Worksheet = $Workbook->Worksheets('Labcollector');
$Worksheet->activate;
for($i=1;$i<100; $i++){
for($j=65;$j<=90;$j++){
$excel_cell=$Worksheet->Range(chr($j).$i);
$excel_cell->activate;
$cellValue=$excel_cell->value;
if( in_array($cellValue, $this->keys)){
if(preg_match("/##chem_ref##/",$cellValue)){
$placedItem+=1;
}
if(preg_match("/##chem_ref##/",$cellValue) or preg_match("/##chem_name##/",$cellValue) or preg_match("/##qty##/",$cellValue) or preg_match("/##chem_seller_price##/",$cellValue) or preg_match("/##sequence##/",$cellValue) or preg_match("/##discount##/",$cellValue)){
if($placedItem >= count($this->dataReplace[$cellValue])){
$excel_cell->value="";
}else{
$excel_cell->value=$this->dataReplace[$cellValue][$placedItem];
}
}else{
//echo "REPLACING WITH ".$this->dataReplace[$cellValue];
$excel_cell->value=$this->dataReplace[$cellValue];
}
}
}
}
$fileName=$fileName2;
if(file_exists($path."\\documents\\xlsForms\\".$fileName.".xls"))
{
unlink($path."\\documents\\xlsForms\\".$fileName.".xls");
}
$Workbook->saveas($path."\\documents\\xlsForms\\".$fileName.".xls");
$Workbook->Saved = true;
$Workbook->Close;
unset($Worksheet);
unset($Workbook);
$excel_app->Workbooks->Close();
$excel_app->Quit();
unset($excel_app);
}
public function parseXlsbNoVBA($sheetNumber, $sheetNumber2){
$this->objPHPExcel = PHPExcel_IOFactory::load($this->fileName);
$this->objPHPExcel->setActiveSheetIndex($sheetNumber2);
$placedItem=-1;
for($i=0;$i<100; $i++){
for($j=65;$j<=90;$j++){
$cellValue= $this->objPHPExcel->getSheetByName('Labcollector')->getCell(chr($j).$i)->getValue();
if( in_array($cellValue, $this->keys)){
$this->objPHPExcel->getSheetByName('Labcollector')->setCellValue(chr($j).$i,"");
if(preg_match("/##chem_ref##/",$cellValue)){
$placedItem+=1;
}
if(preg_match("/##chem_ref##/",$cellValue) or preg_match("/##chem_name##/",$cellValue) or preg_match("/##qty##/",$cellValue) or preg_match("/##chem_seller_price##/",$cellValue) or preg_match("/##sequence##/",$cellValue) or preg_match("/##discount##/",$cellValue)){
if(!($placedItem >= count($this->dataReplace[$cellValue]))){
$this->objPHPExcel->getSheetByName('Labcollector')->setCellValue(chr($j).$i,"");
if(isset($this->dataReplace[$cellValue][$placedItem])){
$this->objPHPExcel->getSheetByName('Labcollector')->setCellValue(chr($j).$i,$this->dataReplace[$cellValue][$placedItem]);
}
}
}else{
$this->objPHPExcel->getSheetByName('Labcollector')->setCellValue(chr($j).$i,"");
}
}
}
}
}
public function makeFileNoVBA($fileName){
$this->objWriter = PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel2007');
$this->objWriter->save("./documents/xlsForms/".$fileName.".xls");
}
public function makeFile($fileName){
$this->parseXlsb2($fileName);
}
public function __destruct(){
}
} |
