* every cell is checked if it is an excel date (stored in number of days since 1990) * then, the date format from excel is reapplied in php * @param $spreadsheet * @param $values * @param $range * @return array * @throws \PhpOffice\PhpSpreadsheet\Exception */
($spreadsheet, $values, $range)
| 249 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
| 250 | */ |
| 251 | private function convertExcelDate($spreadsheet, $values, $range): array { |
| 252 | $map = [ |
| 253 | "yyyy" => "Y", // Four-digit year |
| 254 | "yy" => "y", // Two-digit year |
| 255 | "MM" => "m", // Two-digit month |
| 256 | "mmm" => "M", // Three-letter month abbreviation |
| 257 | "mm" => "i", // Two-digit minutes (lowercase) |
| 258 | "dd" => "d", // Two-digit day |
| 259 | "d" => "j", // Day without leading zeros |
| 260 | "hh" => "H", // 24-hour format |
| 261 | "h" => "G", // 12-hour format |
| 262 | "ss" => "s" // Seconds |
| 263 | ]; |
| 264 | |
| 265 | $start = str_getcsv($range, ':'); |
| 266 | $startCell = Coordinate::coordinateFromString($start[0]); |
| 267 | $startColumn = (int)Coordinate::columnIndexFromString($startCell[0]); |
| 268 | $startRow = (int)$startCell[1]; |
| 269 | |
| 270 | foreach ($values as $rowIndex => $row) { |
| 271 | foreach ($row as $columnIndex => $cellValue) { |
| 272 | $columnLetter = Coordinate::stringFromColumnIndex($columnIndex + $startColumn); |
| 273 | $rowNumber = $rowIndex + $startRow; |
| 274 | $coordinate = $columnLetter . $rowNumber; |
| 275 | $cell = $spreadsheet->getActiveSheet()->getCell($coordinate); |
| 276 | |
| 277 | // Check if it's part of a merged range |
| 278 | if ($cell->isInMergeRange()) { |
| 279 | // Overwrite the cell with the top-left cell of the merged range |
| 280 | $mergedRange = $cell->getMergeRange(); |
| 281 | [$startCell] = explode(':', $mergedRange); |
| 282 | $cell = $spreadsheet->getActiveSheet()->getCell($startCell); |
| 283 | $values[$rowIndex][$columnIndex] = $cell->getValue(); |
| 284 | } |
| 285 | |
| 286 | $excelFormat = $cell->getStyle()->getNumberFormat()->getFormatCode(); |
| 287 | |
| 288 | if (preg_match('/%/', $excelFormat)) { |
| 289 | // Convert percentage to decimal value |
| 290 | $cellValue = $cell->getCalculatedValue(); |
| 291 | $values[$rowIndex][$columnIndex] = round($cellValue, 2); |
| 292 | } elseif (Date::isDateTime($cell)) { |
| 293 | // handle date values |
| 294 | $excelFormat = rtrim($excelFormat, ";@"); |
| 295 | |
| 296 | // Check if it's a duration format (e.g., h:mm, [h]:mm, h:mm:ss) |
| 297 | if (preg_match('/[h]+:?[m]+:?[s]*/i', $excelFormat)) { |
| 298 | // Convert time duration to decimal |
| 299 | $excelTime = $cell->getCalculatedValue(); |
| 300 | $totalHours = Date::excelToDateTimeObject($excelTime)->format('G'); // Extract hours |
| 301 | $totalMinutes = Date::excelToDateTimeObject($excelTime)->format('i'); // Extract minutes |
| 302 | $totalSeconds = Date::excelToDateTimeObject($excelTime)->format('s'); // Extract seconds |
| 303 | |
| 304 | // Convert the time to decimal (minutes) |
| 305 | $totalMinutesValue = ($totalHours * 60) + $totalMinutes + ($totalSeconds / 60); |
| 306 | $values[$rowIndex][$columnIndex] = round($totalMinutesValue, 2); // Rounded to 2 decimal places |
| 307 | } else { |
| 308 | // Regular date formatting |