Skip to content

Commit e9b11e5

Browse files
blizzzblizzz
authored
Merge pull request #1545 from nextcloud/fix/1518/import-date-handling
fix(Import): DateTime import fixes
2 parents 14c65c1 + 3f5fc03 commit e9b11e5

File tree

5 files changed

+126
-59
lines changed

5 files changed

+126
-59
lines changed

‎lib/Db/Column.php

+7
Original file line numberDiff line numberDiff line change
@@ -95,6 +95,13 @@ class Column extends Entity implements JsonSerializable {
9595
public const TYPE_DATETIME = 'datetime';
9696
public const TYPE_USERGROUP = 'usergroup';
9797

98+
public const SUBTYPE_DATETIME_DATE = 'date';
99+
public const SUBTYPE_DATETIME_TIME = 'time';
100+
101+
public const SUBTYPE_SELECTION_CHECK = 'check';
102+
103+
public const SUBTYPE_TEXT_LINE = 'line';
104+
98105
protected ?string $title = null;
99106
protected ?int $tableId = null;
100107
protected ?string $createdBy = null;

‎lib/Service/ImportService.php

+88-59
Original file line numberDiff line numberDiff line change
@@ -31,6 +31,9 @@
3131
use Psr\Container\ContainerExceptionInterface;
3232
use Psr\Container\NotFoundExceptionInterface;
3333
use Psr\Log\LoggerInterface;
34+
use function is_string;
35+
use function mb_strlen;
36+
use function preg_match;
3437

3538
class ImportService extends SuperService {
3639

@@ -160,25 +163,38 @@ private function getPreviewData(Worksheet $worksheet): array {
160163
$colIndex = $cellIterator->getCurrentColumnIndex() - 1;
161164
$column = $this->columns[$colIndex];
162165

163-
if (($column && $column->getType() === 'datetime') || (is_array($columns[$colIndex]) && $columns[$colIndex]['type'] === 'datetime')) {
164-
if (isset($columns[$colIndex]['subtype']) && $columns[$colIndex]['subtype'] === 'date') {
166+
if (
167+
($column && $column->getType() === Column::TYPE_DATETIME)
168+
|| (is_array($columns[$colIndex])
169+
&& $columns[$colIndex]['type'] === Column::TYPE_DATETIME)
170+
) {
171+
if (isset($columns[$colIndex]['subtype'])
172+
&& $columns[$colIndex]['subtype'] === Column::SUBTYPE_DATETIME_DATE
173+
) {
165174
$format = 'Y-m-d';
166-
} elseif (isset($columns[$colIndex]['subtype']) && $columns[$colIndex]['subtype'] === 'time') {
175+
} elseif (isset($columns[$colIndex]['subtype'])
176+
&& $columns[$colIndex]['subtype'] === Column::SUBTYPE_DATETIME_TIME
177+
) {
167178
$format = 'H:i';
168179
} else {
169180
$format = 'Y-m-d H:i';
170181
}
171-
172-
try {
173-
$value = Date::excelToDateTimeObject($value)->format($format);
174-
} catch (\TypeError) {
175-
$value = (new \DateTimeImmutable($value))->format($format);
176-
}
177-
} elseif (($column && $column->getType() === 'number' && $column->getNumberSuffix() === '%')
178-
|| (is_array($columns[$colIndex]) && $columns[$colIndex]['type'] === 'number' && $columns[$colIndex]['numberSuffix'] === '%')) {
182+
$value = $this->parseAndFormatDateTimeString($value, $format);
183+
} elseif (
184+
($column && $column->getType() === Column::TYPE_NUMBER
185+
&& $column->getNumberSuffix() === '%')
186+
|| (is_array($columns[$colIndex])
187+
&& $columns[$colIndex]['type'] === Column::TYPE_NUMBER
188+
&& $columns[$colIndex]['numberSuffix'] === '%')
189+
) {
179190
$value = $value * 100;
180-
} elseif (($column && $column->getType() === 'selection' && $column->getSubtype() === 'check')
181-
|| (is_array($columns[$colIndex]) && $columns[$colIndex]['type'] === 'selection' && $columns[$colIndex]['subtype'] === 'check')) {
191+
} elseif (
192+
($column && $column->getType() === Column::TYPE_SELECTION
193+
&& $column->getSubtype() === Column::SUBTYPE_SELECTION_CHECK)
194+
|| (is_array($columns[$colIndex])
195+
&& $columns[$colIndex]['type'] === Column::TYPE_SELECTION
196+
&& $columns[$colIndex]['subtype'] === Column::SUBTYPE_SELECTION_CHECK)
197+
) {
182198
$value = $cell->getFormattedValue() === 'TRUE' ? 'true' : 'false';
183199
}
184200

@@ -381,34 +397,22 @@ private function createRow(Row $row): void {
381397
$value = $cell->getValue();
382398
$hasData = $hasData || !empty($value);
383399

384-
if ($column->getType() === 'datetime') {
385-
if ($column->getType() === 'datetime' && $column->getSubtype() === 'date') {
400+
if ($column->getType() === Column::TYPE_DATETIME) {
401+
if ($column->getSubtype() === Column::SUBTYPE_DATETIME_DATE) {
386402
$format = 'Y-m-d';
387-
} elseif ($column->getType() === 'datetime' && $column->getSubtype() === 'time') {
403+
} elseif ($column->getSubtype() === Column::SUBTYPE_DATETIME_TIME) {
388404
$format = 'H:i';
389405
} else {
390406
$format = 'Y-m-d H:i';
391407
}
392-
try {
393-
$value = Date::excelToDateTimeObject($value)->format($format);
394-
} catch (\TypeError) {
395-
$value = (new \DateTimeImmutable($value))->format($format);
396-
}
397-
} elseif ($column->getType() === 'datetime' && $column->getSubtype() === 'date') {
398-
try {
399-
$value = Date::excelToDateTimeObject($value)->format('Y-m-d');
400-
} catch (\TypeError) {
401-
$value = (new \DateTimeImmutable($value))->format('Y-m-d');
402-
}
403-
} elseif ($column->getType() === 'datetime' && $column->getSubtype() === 'time') {
404-
try {
405-
$value = Date::excelToDateTimeObject($value)->format('H:i');
406-
} catch (\TypeError) {
407-
$value = (new \DateTimeImmutable($value))->format('H:i');
408-
}
409-
} elseif ($column->getType() === 'number' && $column->getNumberSuffix() === '%') {
408+
$value = $this->parseAndFormatDateTimeString($value, $format);
409+
} elseif ($column->getType() === Column::TYPE_NUMBER
410+
&& $column->getNumberSuffix() === '%'
411+
) {
410412
$value = $value * 100;
411-
} elseif ($column->getType() === 'selection' && $column->getSubtype() === 'check') {
413+
} elseif ($column->getType() === Column::TYPE_SELECTION
414+
&& $column->getSubtype() === Column::SUBTYPE_SELECTION_CHECK
415+
) {
412416
$value = $cell->getFormattedValue() === 'TRUE' ? 'true' : 'false';
413417
}
414418

@@ -440,6 +444,37 @@ private function createRow(Row $row): void {
440444

441445
}
442446

447+
private function valueToDateTimeImmutable(mixed $value): ?\DateTimeImmutable {
448+
if (
449+
$value === false
450+
|| $value === null
451+
|| (is_string($value)
452+
&& mb_strlen($value) < 3 // Let pass potential 3-letter month names
453+
&& preg_match('/\d/', $value) !== 1) // or anything containing a digit
454+
) {
455+
return null;
456+
}
457+
try {
458+
$dt = Date::excelToDateTimeObject($value);
459+
return \DateTimeImmutable::createFromMutable($dt);
460+
} catch (\TypeError) {
461+
try {
462+
return (new \DateTimeImmutable($value));
463+
} catch (\Exception $e) {
464+
$this->logger->debug('Could not parse string {value} as date time.', [
465+
'exception' => $e,
466+
'value' => $value,
467+
]);
468+
return null;
469+
}
470+
}
471+
}
472+
473+
private function parseAndFormatDateTimeString(?string $value, string $format): string {
474+
$dateTime = $this->valueToDateTimeImmutable($value);
475+
return $dateTime?->format($format) ?: '';
476+
}
477+
443478
/**
444479
* @param Row $firstRow
445480
* @param Row $secondRow
@@ -524,87 +559,81 @@ private function parseColumnDataType(Cell $cell): array {
524559
$value = $cell->getValue();
525560
$formattedValue = $cell->getFormattedValue();
526561
$dataType = [
527-
'type' => 'text',
528-
'subtype' => 'line',
562+
'type' => Column::TYPE_TEXT,
563+
'subtype' => Column::SUBTYPE_TEXT_LINE,
529564
];
530565

531-
try {
532-
if ($value === false) {
533-
throw new \Exception('We do not accept `false` here');
534-
}
535-
$dateValue = new \DateTimeImmutable($value);
536-
} catch (\Exception) {
537-
}
538-
539-
if (isset($dateValue)
566+
if (!is_numeric($formattedValue)
567+
&& ($this->valueToDateTimeImmutable($value) instanceof \DateTimeImmutable
540568
|| Date::isDateTime($cell)
541-
|| $originDataType === DataType::TYPE_ISO_DATE) {
569+
|| $originDataType === DataType::TYPE_ISO_DATE)
570+
) {
542571
// the formatted value stems from the office document and shows the original user intent
543572
$dateAnalysis = date_parse($formattedValue);
544573
$containsDate = $dateAnalysis['year'] !== false || $dateAnalysis['month'] !== false || $dateAnalysis['day'] !== false;
545574
$containsTime = $dateAnalysis['hour'] !== false || $dateAnalysis['minute'] !== false || $dateAnalysis['second'] !== false;
546575

547576
if ($containsDate && !$containsTime) {
548-
$subType = 'date';
577+
$subType = Column::SUBTYPE_DATETIME_DATE;
549578
} elseif (!$containsDate && $containsTime) {
550-
$subType = 'time';
579+
$subType = Column::SUBTYPE_DATETIME_TIME;
551580
} else {
552581
$subType = '';
553582
}
554583

555584
$dataType = [
556-
'type' => 'datetime',
585+
'type' => Column::TYPE_DATETIME,
557586
'subtype' => $subType,
558587
];
559588
} elseif ($originDataType === DataType::TYPE_NUMERIC) {
560589
if (str_contains($formattedValue, '%')) {
561590
$dataType = [
562-
'type' => 'number',
591+
'type' => Column::TYPE_NUMBER,
563592
'number_decimals' => 2,
564593
'number_suffix' => '%',
565594
];
566595
} elseif (str_contains($formattedValue, '€')) {
567596
$dataType = [
568-
'type' => 'number',
597+
'type' => Column::TYPE_NUMBER,
569598
'number_decimals' => 2,
570599
'number_suffix' => '€',
571600
];
572601
} elseif (str_contains($formattedValue, 'EUR')) {
573602
$dataType = [
574-
'type' => 'number',
603+
'type' => Column::TYPE_NUMBER,
575604
'number_decimals' => 2,
576605
'number_suffix' => 'EUR',
577606
];
578607
} elseif (str_contains($formattedValue, '$')) {
579608
$dataType = [
580-
'type' => 'number',
609+
'type' => Column::TYPE_NUMBER,
581610
'number_decimals' => 2,
582611
'number_prefix' => '$',
583612
];
584613
} elseif (str_contains($formattedValue, 'USD')) {
585614
$dataType = [
586-
'type' => 'number',
615+
'type' => Column::TYPE_NUMBER,
587616
'number_decimals' => 2,
588617
'number_suffix' => 'USD',
589618
];
590619
} elseif (is_float($value)) {
591620
$decimals = strlen(substr(strrchr((string)$value, '.'), 1));
592621
$dataType = [
593-
'type' => 'number',
622+
'type' => Column::TYPE_NUMBER,
594623
'number_decimals' => $decimals,
595624
];
596625
} else {
597626
$dataType = [
598-
'type' => 'number',
627+
'type' => Column::TYPE_NUMBER,
599628
];
600629
}
601630
} elseif ($originDataType === DataType::TYPE_BOOL
602631
|| ($originDataType === DataType::TYPE_FORMULA
603632
&& in_array($formattedValue, ['FALSE', 'TRUE'], true))
604633
) {
605634
$dataType = [
606-
'type' => 'selection',
607-
'subtype' => 'check',
635+
'type' => Column::TYPE_SELECTION,
636+
'subtype' => Column::SUBTYPE_SELECTION_CHECK,
608637
'selection_default' => 'false',
609638
];
610639
}

‎tests/integration/features/APIv1.feature

+26
Original file line numberDiff line numberDiff line change
@@ -218,6 +218,32 @@ Feature: APIv1
218218
| import-from-ms365.xlsx |
219219
| import-from-libreoffice.csv |
220220

221+
@api1 @import
222+
Scenario: Import a document with optional field
223+
Given user "participant1" uploads file "import-from-libreoffice-optional-fields.csv"
224+
And table "Import test" with emoji "👨🏻‍💻" exists for user "participant1" as "base1"
225+
When user imports file "/import-from-libreoffice-optional-fields.csv" into last created table
226+
Then import results have the following data
227+
| found_columns_count | 9 |
228+
| created_columns_count | 9 |
229+
| inserted_rows_count | 2 |
230+
| errors_count | 0 |
231+
# At the moment, we only take the first row into account, when determining the cell format
232+
# Hence, it is expected that all turn out to be text
233+
Then table has at least following typed columns
234+
| Case | text |
235+
| Col1 | text |
236+
| num | text |
237+
| emoji | text |
238+
| special | text |
239+
| date | text |
240+
| truth | text |
241+
# the library handles "true" as boolean and so is converted into the text representation "1"
242+
Then table contains at least following rows
243+
| Case | Date and Time | Col1 | num | emoji | special | date | truth | time |
244+
| A | | | | | | | | |
245+
| B | 2016-06-01 13:37 | great | 99 | ⚠ | Ö | 2016-06-01 | 1 | 01:23 |
246+
221247
@api1
222248
Scenario: Create, edit and delete views
223249
Given table "View test" with emoji "👨🏻‍💻" exists for user "participant1" as "view-test"

‎tests/integration/resources/import-from-libreoffice-optional-fields.csv

+3
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
Case,Date and Time,Col1,num,emoji,special,date,truth,time
2+
A,,,,,,,,
3+
B,2016-06-01 13:37,great,99,⚠,Ö,2016-06-01,true,01:23

‎tests/integration/resources/import-from-libreoffice-optional-fields.csv.license

+2
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
SPDX-FileCopyrightText: 2025 Nextcloud GmbH and Nextcloud contributors
2+
SPDX-License-Identifier: AGPL-3.0-or-later

0 commit comments

Comments
 (0)