/**
* Creates an Excel spreadsheet given the search terms stored in Spreadsheet model class
* spreadsheet generation happens as background process, user redirected to status page
* where they can refresh page to check on status and download when finished
*/
public function xlsAction()
{
$spreadsheet = new Spreadsheet();
$spreadsheet->terms = serialize($_GET);
$spreadsheet->save();
$args = escapeshellarg("-u {$spreadsheet->user_id}") . ' ' . escapeshellarg("-i {$spreadsheet->id}");
$php_path = get_option('spreadsheet_php_path');
$script_path = PLUGIN_DIR . "/Spreadsheet/background_scripts/export.php";
exec('nice ' . $php_path . ' ' . $script_path . ' ' . $args . ' > /dev/null 2>&1 &');
$this->redirect->goto('status');
}
public function addHotelBookingToDocs($lead)
{
$leadArr = array("Transaction ID" => $lead['txnId'], "First Name" => $lead['fname'], "Last Name" => $lead['lname'], "Email" => $lead['email'], "Contact" => $lead['contact'], "Booking Date" => $lead['date_added'], "Check-in Date" => $lead['check_in'], "Check-out Date" => $lead['check_out'], "Room Booked" => $lead['booked_room'], "Max Guests" => $lead['max'], "Total Payment" => $lead['revenue'], "Status" => 0);
$spr = new Spreadsheet("*****@*****.**", "breakBoundaries\$\$##123");
$spr->setSpreadsheet("TNF100 2015 Registration")->setWorksheet("HotelBookings")->add($leadArr);
if (isset($spr) && !empty($spr->token)) {
return true;
} else {
return false;
}
}
function execute(&$form, $action_id)
{
$config = $form->actions_config[$action_id];
$config = new \GCore\Libs\Parameter($config);
$doc = new Spreadsheet();
$doc->authenticate($config->get('username'), $config->get('password'));
$doc->setSpreadsheet($config->get('spreadsheet'));
$doc->setWorksheet($config->get('worksheet'));
$path = $config->get('data_path', 'GSheet') ? explode('.', $config->get('data_path', 'GSheet')) : array();
$data = \GCore\Libs\Arr::getVal($form->data, $path, array());
//pr($data);
$doc->add($data);
$form->debug[$action_id][self::$title] = $doc->debug;
}
/**
* Create array from a range of cells
*
* @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
* @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
* @param boolean $calculateFormulas Should formulas be calculated?
* @param boolean $formatData Should formatting be applied to cell values?
* @param boolean $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
* True - Return rows and columns indexed by their actual row and column IDs
* @return array
*/
public function rangeToArray($pRange = 'A1', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false)
{
// Returnvalue
$returnValue = array();
// Identify the range that we need to extract from the worksheet
list($rangeStart, $rangeEnd) = Cell::rangeBoundaries($pRange);
$minCol = Cell::stringFromColumnIndex($rangeStart[0] - 1);
$minRow = $rangeStart[1];
$maxCol = Cell::stringFromColumnIndex($rangeEnd[0] - 1);
$maxRow = $rangeEnd[1];
$maxCol++;
// Loop through rows
$r = -1;
for ($row = $minRow; $row <= $maxRow; ++$row) {
$rRef = $returnCellRef ? $row : ++$r;
$c = -1;
// Loop through columns in the current row
for ($col = $minCol; $col != $maxCol; ++$col) {
$cRef = $returnCellRef ? $col : ++$c;
// Using getCell() will create a new cell if it doesn't already exist. We don't want that to happen
// so we test and retrieve directly against cellCollection
if ($this->cellCollection->isDataSet($col . $row)) {
// Cell exists
$cell = $this->cellCollection->getCacheData($col . $row);
if ($cell->getValue() !== null) {
if ($cell->getValue() instanceof RichText) {
$returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText();
} else {
if ($calculateFormulas) {
$returnValue[$rRef][$cRef] = $cell->getCalculatedValue();
} else {
$returnValue[$rRef][$cRef] = $cell->getValue();
}
}
if ($formatData) {
$style = $this->parent->getCellXfByIndex($cell->getXfIndex());
$returnValue[$rRef][$cRef] = Style\NumberFormat::toFormattedString($returnValue[$rRef][$cRef], $style && $style->getNumberFormat() ? $style->getNumberFormat()->getFormatCode() : Style\NumberFormat::FORMAT_GENERAL);
}
} else {
// Cell holds a NULL
$returnValue[$rRef][$cRef] = $nullValue;
}
} else {
// Cell doesn't exist
$returnValue[$rRef][$cRef] = $nullValue;
}
}
}
// Return
return $returnValue;
}
public function action_index()
{
$model = new $this->model();
$this->setMaxId($model);
$spreadsheet = \Spreadsheet::factory(array('filename' => 'Excel/base.xlsx'), FALSE)->load();
$spreadsheet->set_active_worksheet($this->sheetnumber);
$return = $spreadsheet->read();
// Переносим таблицу порциями
$count = count($return);
$connection = $model->getDbConnection();
$transaction = $connection->beginTransaction();
try {
for ($offset = $this->ignored; $offset < $count; $offset = $offset + $this->per_limit) {
if ($offset >= $this->limit) {
break;
}
// Порция
$spreed = array_slice($return, $offset, $this->per_limit);
// Заносим во все таблицы
foreach ($this->table_to as $handler => $table) {
// Show SQL Logger
if ($table == $this->show_table) {
var_dump($this->source_tables[$table]);
echo "<hr><strong>Table::</strong> " . $table . "<hr>";
}
// Получаем SQL запрос
$insert = $this->{$handler}($table, $spreed);
// Выполнение запроса
if (!$this->testMode) {
$this->commandSQL($insert, $connection);
}
}
// Next Max Element
$this->setMaxId($model);
//$this->maxId += $this->per_limit;
}
$transaction->commit();
} catch (Exception $e) {
echo "Transaction Error!<br>";
$transaction->rollback();
}
return;
}
/**
* Get an instance of this class
*
* @access public
* @param Spreadsheet $spreadsheet Injected spreadsheet for working with a PHPExcel Spreadsheet object,
* or NULL to create a standalone claculation engine
* @return Calculation
*/
public static function getInstance(Spreadsheet $spreadsheet = null)
{
if ($spreadsheet !== null) {
$instance = $spreadsheet->getCalculationEngine();
if (isset($instance)) {
return $instance;
}
}
if (!isset(self::$instance) || self::$instance === null) {
self::$instance = new \PHPExcel\Calculation();
}
return self::$instance;
}
/**
* Update named formulas (i.e. containing worksheet references / named ranges)
*
* @param PHPExcel $pPhpExcel Object to update
* @param string $oldName Old name (name to replace)
* @param string $newName New name
*/
public function updateNamedFormulas(Spreadsheet $pPhpExcel, $oldName = '', $newName = '')
{
if ($oldName == '') {
return;
}
foreach ($pPhpExcel->getWorksheetIterator() as $sheet) {
foreach ($sheet->getCellCollection(false) as $cellID) {
$cell = $sheet->getCell($cellID);
if ($cell !== null && $cell->getDataType() == Cell\DataType::TYPE_FORMULA) {
$formula = $cell->getValue();
if (strpos($formula, $oldName) !== false) {
$formula = str_replace("'" . $oldName . "'!", "'" . $newName . "'!", $formula);
$formula = str_replace($oldName . "!", $newName . "!", $formula);
$cell->setValueExplicit($formula, Cell\DataType::TYPE_FORMULA);
}
}
}
}
}
