Excel.php 5.36 KB
Newer Older
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
<?php
/**
 * Created by PhpStorm.
 * User: crose
 * Date: 8/7/18
 * Time: 11:32 AM
 *
 * Check: CODING.md > Download
 */

namespace qfq;

//use TYPO3\CMS\Core\Resource\Exception\FileDoesNotExistException;

require_once(__DIR__ . '/../Constants.php');
//require_once(__DIR__ . '/../store/Session.php');
//require_once(__DIR__ . '/../store/Store.php');
//require_once(__DIR__ . '/../helper/OnArray.php');
//require_once(__DIR__ . '/../helper/Logger.php');
//require_once(__DIR__ . '/../helper/Sanitize.php');
require_once(__DIR__ . '/../helper/HelperFile.php');
//require_once(__DIR__ . '/../report/Html2Pdf.php');
//require_once(__DIR__ . '/Thumbnail.php');
//require_once(__DIR__ . '/Monitor.php');
require_once(__DIR__ . '/../exceptions/DownloadException.php');
//require_once(__DIR__ . '/../Evaluate.php');
//require_once(__DIR__ . '/../helper/KeyValueStringParser.php');
//
require __DIR__ . '/../../../Resources/Private/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

const COLUMN = 0;
const ROW = 1;

/**
 * Class Excel
 * @package qfq
 */
class Excel {

    /**
     * @param array $files
     * @param array $data
     * @return string
     * @throws downloadException
     */
    public function process(array $files, array $data) {

        // Check that there are some sources
        if (empty($files) && empty($data)) {
            throw new downloadException("No source found to offer to download", ERROR_DOWNLOAD_NOTHING_TO_DO);
        }

        // Check if there are only File(s) given: return them.
        if (empty($data)) {
            if (count($files) == 1) {
                return $files[0]; // Return the given file.
            } else {
                throw new downloadException("Only one source file per Excel download supported", ERROR_DOWNLOAD_UNEXPECTED_NUMBER_OF_SOURCES);
            }
        }

        // Use Template or NEW
66
        if (empty($files[0])) {
67
68
            // Create new
            $spreadsheet = new Spreadsheet();
69
70
71
        } else {
            // Open template
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($files[0]);
72
73
74
        }

        // Iterate over all sources.
75
        foreach ($data as $page) {
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
            $spreadsheet = $this->fillSpreadsheet($spreadsheet, $page);
        }

        $filename = HelperFile::tempnam();
        $writer = new Xlsx($spreadsheet);
        $writer->save($filename);

        return $filename;
    }

    /**
     * @param Spreadsheet $spreadsheet
     * @param string $data
     * @return Spreadsheet
     * @throws downloadException
     */
    private function fillSpreadsheet(Spreadsheet $spreadsheet, $data) {

        $worksheet = $spreadsheet->getActiveSheet();
        $pos = 'A1';
96
97
        if (!OnString::splitExcelPos($pos, $posColumn, $posRow)) {
            throw new downloadException("Invalid cell coordinates: " . $pos, ERROR_EXCEL_INVALID_COORDINATES);
98
        }
99
100
101
102
103
104
105
106
107
108
        $arr = explode(PHP_EOL, $data);
        foreach ($arr as $line) {

            $line = trim($line);

            // Skip empty /comment lines
            if (empty($line) || $line[0] == TOKEN_COMMENT) {
                continue;
            }

109
            $token = explode('=', $line, 2);
110
111
            $key=$token[0];
            $value=$token[1];
112
113

            switch ($key) {
114
                case EXCEL_WORKSHEET:
115
                    throw new downloadException("Not implemented: $key", ERROR_NOT_IMPLEMENTED);
116
117

                case EXCEL_MODE:
118
                    throw new downloadException("Not implemented: $key", ERROR_NOT_IMPLEMENTED);
119
120

                case EXCEL_POSITION:
121

122
                    if (empty($value)) {
123
124
                        throw new downloadException("Position argument is empty", ERROR_EXCEL_POSITION_ARGUMENT_EMPTY);
                    }
125

126
127
                    if (!OnString::splitExcelPos($value, $posColumn, $posRow)) {
                        throw new downloadException("Invalid cell coordinates: " . $value, ERROR_EXCEL_INVALID_COORDINATES);
128
                    }
129
130
131
132
133
134
135
                    break;

                case EXCEL_ROW:
                    $posRow++;
                    break;

                case EXCEL_STRING:
136
137
138
139
140
141
142
143
144
145
                case EXCEL_STRING2:
                case EXCEL_FORMULA:
                case EXCEL_NUMERIC:
                case EXCEL_BOOL:
                case EXCEL_NULL:
                case EXCEL_INLINE:
                case EXCEL_ERROR:
                    $spreadsheet->getActiveSheet()
                        ->setCellValueExplicit(
                            $posColumn . $posRow,
146
147
                            $value,
                            $key
148
                        );
149
150
151
                    $posColumn = $this->nextColumn($posColumn);
                    break;

152
                default:
153
                    throw new downloadException("Excel Export: unknown token " . $key, ERROR_UNKNOWN_TOKEN);
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
            }
        }

        return $spreadsheet;
    }

    /**
     * Increment the alpha string. If 'Z' is reached on the last position, it appends 'A'.
     * 'A' > 'B'
     * 'Z' > 'ZA'
     * 'ZA' > 'ZB'
     *
     * @param $column
     * @return string - incremented column.
     */
    private function nextColumn($column) {
        $len = strlen($column);
        $c = $column[$len - 1];
        if ($c == 'Z') {
            $column .= 'A';
        } else {
            $c = ++$c;
            $column[$len - 1] = $c;
        }

        return $column;
    }
}