Hannes Pries - Web/Shopware/JavaScript-Developer Blog
Weil es immer eine simple Lösung gibt!

<?php
include_once __DIR__ . '/vendor/autoload.php';

use Shuchkin\SimpleXLSX;

if ( $xlsx = SimpleXLSX::parse(__DIR__ . '/pets.xlsx') ) {
$json = [];
$head = $xlsx->rows()[0];

foreach ($xlsx->rows() as $idx => $row) {
if($idx > 0) {
$item = [];
foreach ($head as $idxKey => $key) {
if(strlen($key) > 0) {
$item[$key] = $row[$idxKey];
}
}
$json[] = $item;
}
}

echo json_encode($json);
} else {
echo SimpleXLSX::parseError();
}

[
{
"Name": "Miki",
"Rasse": "Hund",
"Farbe": "braun"
},
{
"Name": "Teddy",
"Rasse": "Hund",
"Farbe": "wei\\u00df"
},
{
"Name": "Merle",
"Rasse": "Katze",
"Farbe": "wei\\u00df|braun|schwarz"
},
{
"Name": "Finja",
"Rasse": "Katze",
"Farbe": "braun|schwarz"
},
{
"Name": "Ronja",
"Rasse": "Katze",
"Farbe": "wei\\u00df|schwarz"
},
{
"Name": "Molly",
"Rasse": "Katze",
"Farbe": "grau"
}
]

downloadCsv(item) {
const httpClient = Shopware.Application.getContainer('init').httpClient;
httpClient.get('/example-module/download/csv/{' + item.id).then(result => {

const pom = document.createElement('a');
pom.setAttribute('href', 'data:text/csv;charset=utf-8,' + encodeURIComponent(result.data));
pom.setAttribute('download', 'csv_' + item.id + '.csv');

if (document.createEvent) {
const event = document.createEvent('MouseEvents');
event.initEvent('click', true, true);
pom.dispatchEvent(event);
}
else {
pom.click();
}
});
}

^.*$

$

<?php
namespace HPrExportAttrExtend;

use Doctrine\DBAL\Connection;
use Shopware\Components\Plugin;

class HPrExportAttrExtend extends Plugin{

private $ignoreFields = [
'articleID',
'articledetailsID',
'attr1',
'attr2',
'attr3',
'attr4',
'attr5',
'attr6',
'attr7',
'attr8',
'attr9',
'attr10',
'attr11',
'attr12',
'attr13',
'attr14',
'attr15',
'attr16',
'attr17',
'attr18',
'attr19',
'attr20',
];

public static function getSubscribedEvents(){
return [
'sExport::sCreateSql::after' => 'extendExportArticleSql',
];
}

public function extendExportArticleSql(\Enlight_Hook_HookArgs $args){
$sql = $args->getReturn();

$fields = $this->getAllColumnNames();
if(count($fields) > 0){
$fields[] = 'at.attr20';

$sql = preg_replace("/at\.attr20/i", implode(', ', $fields), $sql);
}

$args->setReturn($sql);
}

private function getAllColumnNames(){
/** @var Connection $dbal */
$dbal = $this->container->get('dbal_connection');
$sql = 'SHOW columns FROM :tablename';
$stmt = $dbal->prepare($sql);
$stmt->execute(['tablename' => 's_articles_attributes']);
$rows = $stmt->fetchAll();
$fields = [];
foreach ($rows as $row){
$name = $row['field'];
if(!in_array($name, $this->ignoreFields)){
$fields[] = 'at.' . $name;
}
}
return $fields;
}
}

CREATE TABLE USERS(
USER_ID INT(11) UNSGINED NOT NULL,
USER_NAME VARCHAR(255) NOT NULL,
PRIMARY KEY (USER_ID)
);

CREATE TABLE MESSAGES(
MESSAGE_ID INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
MESSAGE_TITLE VARCHAR(255) NOT NULL,
FROM_ID INT(11) UNSIGNED NOT NULL,
TO_ID INT(11) UNSIGNED NOT NULL,
CC_ID INT(11) UNSIGNED NOT NULL,
PRIMARY KEY (MESSAGE_ID)
);

$sql="
SELECT 'user_id', 'user_name'
UNION
SELECT USER_ID,USERNAME
FROM USERS
INTO OUTFILE ".$exchangeFolder."/users.csv
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'
";

$cyp="USING PERIODIC COMMIT 10000
LOAD CSV WITH HEADERS FROM "file:///".$exchangeFolder."/messages.csv" AS row
MERGE (m:message{mid:row.msg_id,title:row.msg_title});";

function cleanFolder($folder){
$files=scandir($folder);
foreach($files as $file){
if(preg_match("/\.csv$/i", $file)){
unlink($folder."/".$file);
}
}
}

include_once("PDBC/PDBCDBFactory.php");
PDBCDBFactory::init("PDBC/dbclasses/","PDBC/conffiles/");
$db=PDBCCache::getInstance()->getDB("embdoop");

use Everyman\Neo4j\Client;
use Everyman\Neo4j\Cypher\Query;

$client = new Everyman\Neo4j\Client();
$client->getTransport()->setAuth("neo4j","blubb");

function executeCypher($query){
global $client;
$query=new Query($client, $query);
$query->getResultSet();
}

//clear for export (if a previous import failed)
cleanFolder($exchangeFolder);

//export nodes
echo "create users.csv\n";
$sql=" SELECT 'user_id', 'user_name' UNION
SELECT USER_ID,USER_NAME
FROM USERS
INTO OUTFILE '".$exchangeFolder."/users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'";
$db->execute($sql);

echo "create messages.csv\n";
$sql=" SELECT 'msg_id', 'msg_title' UNION
SELECT MESSAGE_ID, MESSAGE_TITLE
FROM MESSAGES
INTO OUTFILE '".$exchangeFolder."/messages.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'";
$db->execute($sql);

//export relations
echo "create relations_etc.csv\n";
$sql=" SELECT 'user_id', 'msg_id', 'type' UNION
SELECT TO_ID, MESSAGE_ID, 'TO'
FROM MESSAGES
UNION
SELECT CC_ID, MESSAGE_ID, 'CC'
FROM MESSAGES

INTO OUTFILE '".$exchangeFolder."/relations_etc.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'";
$db->execute($sql);

echo "create relations_from.csv\n";
$sql=" SELECT 'user_id', 'msg_id', 'type' UNION
SELECT FROM_ID, MESSAGE_ID, 'FROM'
FROM MESSAGES

INTO OUTFILE '".$exchangeFolder."/relations_from.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'";
$db->execute($sql);

//create indexes for fast import
echo "create index's in neo4j\n";
$cyp="CREATE INDEX ON :user(uid);";
executeCypher($cyp);
$cyp="CREATE INDEX ON :message(mid);";
executeCypher($cyp);

//import nodes
echo "import users.csv\n";
$cyp="USING PERIODIC COMMIT 10000\n
LOAD CSV WITH HEADERS FROM "file:///".$exchangeFolder."/users.csv" AS row\n
MERGE (u:user{uid:row.user_id,name:row.user_name});";
executeCypher($cyp);

echo "import messages.csv\n";
$cyp="USING PERIODIC COMMIT 10000
LOAD CSV WITH HEADERS FROM "file:///".$exchangeFolder."/messages.csv" AS row
MERGE (m:message{mid:row.msg_id,title:row.msg_title});";
executeCypher($cyp);

//import relations
echo "import relations_from.csv\n";
$cyp="USING PERIODIC COMMIT 10000
LOAD CSV WITH HEADERS FROM "file:///".$exchangeFolder."/relations_from.csv" AS row
MATCH(u:user{uid:row.user_id})
MATCH(m:message{mid:row.msg_id})
MERGE (u)-[r:send]->(m);";
executeCypher($cyp);

echo "import relations_etc.csv\n";
$cyp="USING PERIODIC COMMIT 10000
LOAD CSV WITH HEADERS FROM "file:///".$exchangeFolder."/relations_etc.csv" AS row
MATCH(u:user{uid:row.user_id})
MATCH(m:message{mid:row.msg_id})
MERGE (m)-[r:receive]->(u)
SET r.type=row.type;";
executeCypher($cyp);

//clear after import
cleanFolder($exchangeFolder);

MATCH (n)
DETACH DELETE n

SELECT ID,NAME
FROM TEST
WHERE ID>5
INTO OUTFILE '/var/www/app/data/export/out_test.csv'
FIELDS TERMINATED BY ','

sudo aa-status

sudo /etc/init.d/apparmor reload