Blame view

lib/XlsxParser.php 6.76 KB
8e128526   Mihail   add xlsx parser
1
2
3
4
5
6
7
8
  <?php
  /**
   * Created by PhpStorm.
   * User: Tsurkanov
   * Date: 21.10.2015
   * Time: 15:44
   */
  
d0261fd1   Mihail   fixed namespace i...
9
  namespace yii\multiparser;
8e128526   Mihail   add xlsx parser
10
11
  
  
8e128526   Mihail   add xlsx parser
12
  
8e128526   Mihail   add xlsx parser
13
14
  /**
   * Class XlsxParser
d0261fd1   Mihail   fixed namespace i...
15
   * @package yii\multiparser
8e128526   Mihail   add xlsx parser
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
   */
  class XlsxParser extends TableParser {
  
      /**
       * @var string - путь куда будут распаковываться файлы, если не указанно - во временный каталог сервера
       */
      public $path_for_extract_files = '';
  
  
      /**
       * @var int - если указано то считывание будет производиться с этого листа, иначе со всех листов
       * при чтении со всех листов - выходной массив будет иметь номера листов первыми элементами
       */
      public $active_sheet = 0;
  
      protected $strings_arr = [];
      protected $sheets_arr = [];
  
      protected $current_node;
      protected $current_sheet;
  
      public function setup()
      {
  
          parent::setup();
  
          if ( $this->path_for_extract_files == '' ) {
              $this->path_for_extract_files = sys_get_temp_dir();
          }
      }
  
  
      public function read()
      {
42a252c2   Mihail   fixed issue with ...
50
          $this->extractFiles();
0c6fd004   Mihail   fixed issue with ...
51
  
8e128526   Mihail   add xlsx parser
52
53
54
55
56
          $this->readSheets();
          $this->readStrings();
  
          foreach ( $this->sheets_arr  as $sheet ) {
              //проходим по всем файлам из директории /xl/worksheets/
0c6fd004   Mihail   fixed issue with ...
57
              $this->current_sheet = $sheet;
8e128526   Mihail   add xlsx parser
58
59
60
61
62
63
              $sheet_path = $this->path_for_extract_files . '/xl/worksheets/' . $sheet . '.xml';
              if ( file_exists( $sheet_path ) && is_readable( $sheet_path ) ) {
  
                  $xml = simplexml_load_file( $sheet_path, "SimpleXMLIterator" );
                  $this->current_node = $xml->sheetData->row;
                  $this->current_node->rewind();
0c6fd004   Mihail   fixed issue with ...
64
                  if ( $this->current_node->valid() ) {
0c6fd004   Mihail   fixed issue with ...
65
                      parent::read();
0c6fd004   Mihail   fixed issue with ...
66
                  }
8e128526   Mihail   add xlsx parser
67
              }
8e128526   Mihail   add xlsx parser
68
          }
0c6fd004   Mihail   fixed issue with ...
69
  
cd8b9f70   Mihail   add cleanUp metho...
70
          $this->cleanUp();
0c6fd004   Mihail   fixed issue with ...
71
          if ( $this->active_sheet ) {
0c6fd004   Mihail   fixed issue with ...
72
73
74
75
76
77
              // в настройках указан конкретный лист с которогшо будем производить чтение, поэтому и возвращаем подмассив
              return $this->result[ $this->current_sheet ];
          }else{
              return $this->result;
          }
  
8e128526   Mihail   add xlsx parser
78
79
80
81
      }
  
      protected function extractFiles ()
      {
0c6fd004   Mihail   fixed issue with ...
82
83
84
85
86
87
          $this->path_for_extract_files = $this->path_for_extract_files . session_id();
          if ( !mkdir($this->path_for_extract_files) )
          {
              throw new \Exception( 'Ошибка создания временного каталога - ' . $this->path_for_extract_files );
              }
  
8e128526   Mihail   add xlsx parser
88
          $zip = new \ZipArchive;
221da14e   Mihail   change SplFileObj...
89
          if ( $zip->open( $this->file_path ) === TRUE ) {
0c6fd004   Mihail   fixed issue with ...
90
              $zip->extractTo( $this->path_for_extract_files . '/' );
8e128526   Mihail   add xlsx parser
91
92
93
94
              $zip->close();
          } else {
              throw new \Exception( 'Ошибка чтения xlsx файла' );
          }
221da14e   Mihail   change SplFileObj...
95
          unset($zip);
8e128526   Mihail   add xlsx parser
96
97
98
99
100
      }
  
      protected function readSheets ()
      {
          if ( $this->active_sheet ) {
0c6fd004   Mihail   fixed issue with ...
101
              $this->sheets_arr[ ] = 'Sheet' . $this->active_sheet;
8e128526   Mihail   add xlsx parser
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
              return;
          }
  
          $xml = simplexml_load_file( $this->path_for_extract_files . '/xl/workbook.xml' );
          foreach ( $xml->sheets->children() as $sheet ) {
              $sheet_name = '';
              $sheet_id = 0;
              $attr = $sheet->attributes();
              foreach ( $attr as $name => $value ) {
                  if ($name == 'name')
                      $sheet_name = (string)$value;
  
                  if ($name == 'sheetId')
                      $sheet_id = $value;
  
              }
              if ( $sheet_name && $sheet_id ) {
                  $this->sheets_arr[$sheet_name] = 'Sheet' . $sheet_id;
              }
  //
          }
      }
  
      protected function readStrings ()
      {
          $xml = simplexml_load_file( $this->path_for_extract_files . '/xl/sharedStrings.xml' );
          foreach ( $xml->children() as $item ) {
              $this->strings_arr[] = (string)$item->t;
          }
      }
  
  
0c6fd004   Mihail   fixed issue with ...
134
135
  
     // protected function readRow ( $item, $sheet , $current_row )
8e128526   Mihail   add xlsx parser
136
137
      protected function readRow ( )
      {
0c6fd004   Mihail   fixed issue with ...
138
          $this->row = [];
8e128526   Mihail   add xlsx parser
139
          $node = $this->current_node->getChildren();
0c6fd004   Mihail   fixed issue with ...
140
141
142
143
144
145
          if ($node === NULL) {
              return;
          }
          //foreach ( $node as $child ) {
          for ( $node->rewind(); $node->valid(); $node->next() ) {
              $child = $node->current();
8e128526   Mihail   add xlsx parser
146
147
148
149
150
151
152
153
              $attr = $child->attributes();
  
              if( isset($child->v) ) {
                  $value = (string)$child->v;
              }else{
                  $value = '';
              }
              if ( isset( $attr['t'] ) ) {
8e128526   Mihail   add xlsx parser
154
155
                  $this->row[] =  $this->strings_arr[ $value ];
              }else{
8e128526   Mihail   add xlsx parser
156
157
158
159
                  $this->row[] =  $value;
              }
  
          }
0c6fd004   Mihail   fixed issue with ...
160
161
162
163
164
165
166
          // дополним ряд пустыми значениями если у нас ключей больше чем значений
          if ( $this->has_header_row && ( count( $this->keys ) > count( $this->row ) ) ) {
              $extra_coloumn = count( $this->keys ) - count( $this->row );
              for ( $i = 1; $i <= $extra_coloumn; $i++ ) {
                  $this->row[] =  '';
              }
          }
8e128526   Mihail   add xlsx parser
167
          $this->current_node->next();
8e128526   Mihail   add xlsx parser
168
169
170
171
172
      }
  
      protected  function isEmptyRow(){
  
          $is_empty = false;
8e128526   Mihail   add xlsx parser
173
174
175
176
177
178
179
180
  
          if ( !count( $this->row ) || !$this->current_node->valid() ) {
              return true;
          }
  
          $j = 0;
          for ($i = 1; $i <= count( $this->row ); $i++) {
  
42a252c2   Mihail   fixed issue with ...
181
              if ( isset($this->row[$i - 1]) && $this->isEmptyColumn( $this->row[$i - 1] ) ) {
8e128526   Mihail   add xlsx parser
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
                  $j++;
              }
  
              if ( $j >= $this->min_column_quantity ) {
                  $is_empty = true;
                  break;
              }
          }
  
          return $is_empty;
      }
  
      protected  function isEmptyColumn( $val ){
          return $val == '';
      }
0c6fd004   Mihail   fixed issue with ...
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
  
      protected  function setResult(  ){
          $this->result[ $this->current_sheet ][] = $this->row;
      }
  
      protected function deleteExtractFiles ()
      {
              $this->removeDir( $this->path_for_extract_files );
  
      }
  
      protected function removeDir($dir) {
          if (is_dir($dir)) {
              $objects = scandir($dir);
              foreach ($objects as $object) {
                  if ($object != "." && $object != "..") {
                      if (filetype($dir."/".$object) == "dir")
                          $this->removeDir($dir."/".$object);
                      else
                          unlink($dir."/".$object);
                  }
              }
              reset($objects);
              rmdir($dir);
          }
      }
  
cd8b9f70   Mihail   add cleanUp metho...
224
225
  
      protected function cleanUp()
0c6fd004   Mihail   fixed issue with ...
226
      {
cd8b9f70   Mihail   add cleanUp metho...
227
228
229
230
231
          parent::cleanUp();
          unset($this->strings_arr);
          unset($this->sheets_arr);
          unset($this->current_node);
  
0c6fd004   Mihail   fixed issue with ...
232
          $this->deleteExtractFiles();
cd8b9f70   Mihail   add cleanUp metho...
233
  
0c6fd004   Mihail   fixed issue with ...
234
235
236
      }
  
  
8e128526   Mihail   add xlsx parser
237
  }