Wallogit.com
2017 © Pedro Peláez
The preferred way to install this extension is through composer., (*1)
Either run, (*2)
$ php composer.phar require ahmadpriatama/xls-woles "*"
or add, (*3)
"ahmadpriatama/xls-woles": "*"
to the require section of your composer.json file., (*4)
see folder demo, (*5)
We have a list of children book in Book1.xlsx as shown below, (*6)
, (*7)
And your supervisor want clean data to be imported with specs:
1. Book name must stored in lower case string
2. Must split author name into first name and last name
3. ISBN must stored in format XXX-X-XX-XXXXXX-X
4. Language must stored in constant value ex: Book::LANG_EN = 1 or Book::LANG_NON_EN = 2, (*8)
and this is your script, (*9)
$config = json_decode(file_get_contents($fileConfig), true);
$data = \XLSWoles\Reader::load($fileInput)
->setSheetName('Sheet1')
->setColumnConfig($config['columns'])
->setDataRange('a2:e6')
->fetch();
column-config.json:, (*10)
{
"columns" : {
"book-name": {
"filters": [
"string-lowercase"
]
},
"author-name": {
"filters":[
"SplitNameFilter"
]
},
"publisher": {},
"isbn": {
"filters": [
"regex||-||",
"regex||(\\d{3})(\\d{1})(\\d{2})(\\d{6})(\\d{1})||$1-$2-$3-$4-$5"
]
},
"language": {
"filters": [
"string-lowercase",
"regex||english||1"
]
}
}
}
you see that author-name field has SplitNameFilter which is a custom filter which source code as shown below:, (*11)
<?php
/**
* Class SplitNameFilter
* @author Ahmad Priatama <ahmad.priatama@gmail.com>
* @since 2016.08.28
*/
class SplitNameFilter
{
/**
* @param string $input Input String
* @return array
*/
public function process($input)
{
$arr = explode(' ', $input);
$count = count($arr);
if ($count == 1) {
return [
'first-name' => $arr[0],
'last-name' => null,
];
} else {
return [
'first-name' => implode(' ', array_slice($arr, 0, $count-1)),
'last-name' => $arr[$count-1],
];
}
}
}
print_r($data); should give you, (*12)
Array
(
[0] => Array
(
[book-name] => where the wild things are
[author-name] => Array
(
[first-name] => Maurice
[last-name] => Sendak
)
[publisher] => HarperCollins
[isbn] => 978-0-06-443178-1
[language] => 1
)
[1] => Array
(
[book-name] => the snowy day
[author-name] => Array
(
[first-name] => Ezra Jack
[last-name] => Keats
)
[publisher] => Puffin Books
[isbn] => 978-0-14-050182-7
[language] => 1
)
[2] => Array
(
[book-name] => goodnight moon
[author-name] => Array
(
[first-name] => Margaret Wise
[last-name] => Brown
)
[publisher] => HarperCollins
[isbn] => 978-0-06-443017-3
[language] => 1
)
[3] => Array
(
[book-name] => owl moon
[author-name] => Array
(
[first-name] => Jane
[last-name] => Yolen
)
[publisher] => Philomel Books
[isbn] => 978-0-39-921457-8
[language] => 1
)
[4] => Array
(
[book-name] => the giving tree
[author-name] => Array
(
[first-name] => Shel
[last-name] => Silverstein
)
[publisher] => Harper & Row
[isbn] => 978-0-06-025665-4
[language] => 1
)
)