☆ A M B ☆
☆ A M B ☆
☆ A M B ☆
MODX Staff
<?php
class calcimporter
{
function calcimporter($importfields){
$this->importfields=$importfields;
$this->errorfields = array ();
$this->tab_replacement="</td><td>";//should be something what is not in your inputstring
}
function tabs_to_array($inputstring)
{
//replace all tabs with end-cell, begin-cell
$input = preg_replace("/\t/", $this->tab_replacement, $inputstring);
//split the list on linebreaks
$rows = preg_split("/\r\n/", $input);
//make Array from lines
$output = "";
foreach ($rows as $index=>$row)
{
if ($index == 0)
{
$this->tablefields = explode($this->tab_replacement, $row);
} else
{
$tablerow = array ();
$rowarray = array ();
if (strlen($row) > 0)
{
$rowarray = explode($this->tab_replacement, $row);
foreach ($rowarray as $key=>$value)
{
if (in_array($this->tablefields[$key], $this->importfields))
{
$tablerow[$this->tablefields[$key]] = $value;
}
else
{
if (!in_array($this->tablefields[$key], $this->errorfields))
{
$this->errorfields[] = $this->tablefields[$key];
unset ($this->tablefields[$key]);
}
}
}
$tablerows[] = $tablerow;
}
}
}
//echo'<pre>'.print_r($tablerows,true).'</pre>';
return ($tablerows);
}
}
//print_r($_POST);
//$bloxdatas['formstatus'] = '1';
$importer = new calcimporter($importfields);//fields as array which you want to import
if ( isset ($_POST['csvimport']))
{
$rows = $importer->tabs_to_array($_POST['csvimport']);//don't forget to escape your imprted values
//do what you want with imported array, insert or update tables, output as html....
}
<?php
// Google spreadsheet must be published
// Looks like:
// id-fr id-en (name) knowledge experience prospective
72 112 wordpress 70 75 85
81 113 css 50 15 30
$myGoogleSpreadsheetKey = $modx->getOption('myGoogleSpreadsheetKey', $scriptProperties, '');
$gid = $modx->getOption('gid', $scriptProperties, '0');
$cultureKey = $modx->getOption('cultureKey', $scriptProperties, 'fr');
$gdocRow = $modx->getOption('gdocRow', $scriptProperties, '1');
$gdocColumns = $modx->getOption('gdocColumns', $scriptProperties, '');
$columns = explode(',',$gdocColumns);
$url = 'http://spreadsheets.google.com/feeds/list/' . $myGoogleSpreadsheetKey . '/'. $gid .'/public/values?alt=json';
$file= file_get_contents($url);
$json = json_decode($file);
$rows = $json->{'feed'}->{'entry'};
foreach($rows as $row) {
// define cultureKey : because I use 2 languages, when translating I need the data to load also for my translated language
if($cultureKey =='fr') $id = $row->{'gsx$id-fr'}->{'$t'};
if($cultureKey =='en') $id = $row->{'gsx$id-en'}->{'$t'};
if($id == $gdocRow){
foreach($columns as $idx=>$column) {
$modx->setPlaceholder('column.name.'.($idx+1),$column);
$modx->setPlaceholder('column.value.'.($idx+1),$row->{'gsx$'.$column}->{'$t'});
}
break;
}
}[[GoogleSpreadsheet? &key=`[[++myGoogleSpreadsheetKey]]` &gid=`[[++myGoogleSpreadsheetGid.myServices]]` &cultureKey=`[[++cultureKey]]` &gdocRow=`[[+id]]` &gdocColumns=`knowledge,experience,prospective`]] <div class="skillbar clearfix " data-percent="[[+column.value.1]]%"> <div class="skillbar-title"><span>[[%[[+column.name.1]]? &namespace=`my_site` &topic=`skillbars`]]</span></div> <div class="skillbar-bar"></div> <div class="skill-bar-percent">[[+column.value.1]]%</div> </div> <div class="skillbar clearfix " data-percent="[[+column.value.2]]%"> <div class="skillbar-title"><span>[[%[[+column.name.2]]? &namespace=`my_site` &topic=`skillbars`]]</span></div> <div class="skillbar-bar"></div> <div class="skill-bar-percent">[[+column.value.2]]%</div> </div> <div class="skillbar clearfix " data-percent="[[+column.value.3]]%"> <div class="skillbar-title"><span>[[%[[+column.name.3]]? &namespace=`my_site` &topic=`skillbars`]]</span></div> <div class="skillbar-bar"></div> <div class="skill-bar-percent">+[[+column.value.3]]%</div> </div>
[[$mySkillbars.myServices? &id=`[[*id]]`]]
You could, if you don't mind getting your hands dirty a little with PHP, output the spreadsheet as a CSV and then use PHP's native CSV functions to import the data into a database table and use a basic snippet to output the HTML table in whatever format you need on your site.
The example here gives you 80% of the CSV import code you'll need: http://uk3.php.net/manual/en/function.fgetcsv.php
All you have to do to that is, on reading each row, have a query to update the database. If you don't need to keep historical stats, you could have a query before the CSV loop to truncate the table. Then, use the $modx->db->insert() method to put each row in the database.
The snippet for the table is as simple as grabbing all the rows from the database table, looping through and outputting your HTML table row in the desired format.
Quote from: garryn at Mar 18, 2010, 12:03 PMYou could, if you don't mind getting your hands dirty a little with PHP, output the spreadsheet as a CSV and then use PHP's native CSV functions to import the data into a database table and use a basic snippet to output the HTML table in whatever format you need on your site.
The example here gives you 80% of the CSV import code you'll need: http://uk3.php.net/manual/en/function.fgetcsv.php
All you have to do to that is, on reading each row, have a query to update the database. If you don't need to keep historical stats, you could have a query before the CSV loop to truncate the table. Then, use the $modx->db->insert() method to put each row in the database.
The snippet for the table is as simple as grabbing all the rows from the database table, looping through and outputting your HTML table row in the desired format.
I recommend this method. I recently used it to import a user list and it went very smoothly. It's also nice that if you need to make minor changes to the data, you can edit the CSV file with a text editor.