function rapporteurspreadsheet($attr)
{
$attr = shortcode_atts(array('posts_per_page' => -1, 'type' => 'spreadsheets', 'tax' => 'spreadsheets_category', 'metabox' => 'spreadsheets_uploads'), $attr);
extract($attr);
$paged = get_query_var('paged') ? get_query_var('paged') : 1;
$options = array('posts_per_page' => $posts_per_page, 'post_type' => $type, 'orderby' => 'post_date', 'order' => 'DESC', 'paged' => $paged);
//$loop = new WP_Query( $options);
$loop = query_posts($options);
$tabs = null;
$tabs_content = null;
$j = 0;
if (have_posts()) {
while (have_posts()) {
the_post();
$id = get_the_id();
$the_title = get_the_title();
$tab_id = preg_replace('/[^a-z0-9\\_]/i', '_', $the_title);
$spreadsheet = get_post_meta($id, $metabox . '_spreadsheet', true);
$upload_dir = wp_upload_dir();
$file_path = preg_replace("#" . preg_quote($upload_dir['baseurl']) . "#i", $upload_dir['basedir'], $spreadsheet);
$xlsx = new SimpleXLSX($file_path);
$active = $j == 0 ? 'active' : '';
$tabs[] = "<li class='{$active}'><a data-toggle='tab' href='#{$tab_id}'>{$the_title}</a></li>";
$tabs_content[] = "<div class='tab-pane {$active}' id='{$tab_id}'>";
$tabs_content[] = '<table class="table table-bordered table-striped">';
$i = 0;
foreach ($xlsx->rows() as $index => $rows) {
$class = $i % 3 == 0 ? 'class="alternate"' : '';
$cells = $i == 0 ? 'th' : 'td';
$tabs_content[] = "<tr {$class}>";
foreach ($rows as $column => $text) {
$tabs_content[] = "<{$cells}>" . (!empty($text) ? $text : ' ') . "</ {$cells}>";
}
$tabs_content[] = '</tr>';
$i++;
}
$tabs_content[] = "</table></div>";
$j++;
}
wp_reset_query();
}
return "<div class='tabbable tabs-left'><ul class='nav nav-tabs' id='myTab'>" . join("", $tabs) . "</ul> <div class='tab-content'>" . join("", $tabs_content) . "</div> </div> <script> \$('#myTab a:first').tab('show'); </script>";
}
public function showFormat()
{
//get the link of the file
$file = new Files($this->formatfile_id);
$path = $file->fileWithPath();
if (preg_match("/xlsx\$/", $file->originalname) != 0) {
Yii::import('application.vendors.simplexlsx.*');
$xlsx = new SimpleXLSX($excelfile);
return $xlsx->dump();
} else {
if (preg_match("/xls\$/", $file->originalname) != 0) {
Yii::import('application.vendors.php-excel-reader.*');
$xls = new Spreadsheet_Excel_Reader($excelfile);
return $xls->dump();
}
}
}
function create_json_file($file_name)
{
if (empty($file_name)) {
return false;
}
if (empty($_FILES['file']) || empty($_FILES["file"]['name'])) {
return false;
}
require_once plugin_dir_path(__FILE__) . '../includes/simplexlsx.class.php';
$sw = get_option('ggkp_software', '1');
$xlsx = new SimpleXLSX($_FILES['file']['tmp_name']);
$sheetNames = $xlsx->sheetNames();
if (is_array($sheetNames)) {
$sheets = array();
foreach ($sheetNames as $sheetId => $sheetName) {
if ($sheetId == $sw) {
$filteredData = $this->filterData($xlsx, $sw);
$filteredJsonData = json_encode($filteredData);
$uploadResult = $this->createJsonFilteredDataFile($filteredJsonData, $file_name);
}
$sheets[] = $sheetId . ' -> ' . $sheetName;
}
$this->sheets = '<p style="float: left; width: 100%">Werblad nummers ';
$this->sheets .= '<br />' . $uploadResult . '</p>';
$this->sheets .= '<br />' . implode('<br />', $sheets) . '</p>';
$this->sheets .= '<p>Wanneer het Klompenpad werkblad wordt geopend en vervolgens wordt opgeslagen met LibreOffice kloppen de werkbladnummers niet meer. Alle werkbladnummers worden dan met 1 opgehoogd.</p><p>Wanneer het bestand met MSExcel wordt opgeslagen zijn de werkbladnummers oplopend vanaf 1.</p><p>Dit kan worden gecorrigeerd op de <a href="' . admin_url('options-general.php?page=' . $this->plugin_slug) . '">settings pagina</a> van deze plugin, dus de waarde 2 geven als het bestand met LibreOffice is opgeslagen, anders 1</p>';
}
return $uploadResult;
}
include $_SERVER['DOCUMENT_ROOT'] . "/lib/php/simplexlsx.class.php";
$skulist = array();
if (!array_key_exists("SKU", $_SESSION)) {
$_SESSION["SKU"] = array();
}
$_SESSION["SKU"] = count($_SESSION["SKU"]) == 0 ? array() : $_SESSION["SKU"];
$error_message = "";
if (isset($_FILES['csvupload']) && is_uploaded_file($_FILES['csvupload']['tmp_name'])) {
$allowed = array("xlsx");
$filename = $_FILES["csvupload"]["name"];
$ext = pathinfo($filename, PATHINFO_EXTENSION);
if (!in_array($ext, $allowed)) {
$error_message = "Please upload only <b>XLXS</b> file";
} else {
$_SESSION["SKU"] = array();
$xlsx = new SimpleXLSX($_FILES['csvupload']['tmp_name']);
$rows = $xlsx->rows();
$skureq = array();
foreach ($rows as $data) {
$sku = trim($data[0]);
$skuconf = explode("-", $sku)[0];
array_push($skureq, $skuconf);
if (count($skureq) >= 50) {
ajaxGetSkus($skureq);
$skureq = array();
}
}
ajaxGetSkus($skureq);
foreach ($rows as $data) {
$cat = empty($data[1]) ? "-" : $data[1];
if (!array_key_exists($cat, $_SESSION["SKU"])) {
}
}
if (!empty($_FILES['upload_sql'])) {
if (!empty($_FILES['upload_sql']['name'])) {
$path = $config['path']['Storage'] . date('d_m_Y_H_i_s');
$storage = $_SERVER['DOCUMENT_ROOT'] . $path;
$file = $_FILES['upload_sql']['tmp_name'];
$upload = $_FILES['upload_sql']['name'];
$truemime = $parse->CheckMime($file, $config['mimes']['Allow']);
if ($truemime) {
$xlsx = new SimpleXLSX($file);
$array = $xlsx->rows();
$chunk = array_chunk($array, '500');
if (empty($array)) goto die_a;
$result = $parse->sqlXls($chunk, $storage, $path, $config['vars']['DocName'], $config['vars']['AddHeader']);
goto die_b;
} else {
goto die_c;
}
} elseif (empty($_FILES['upload_sql']['name'])) {
goto die_d;
}
}
//Блок с выводом сообщений
die_a : die(json_encode(array('msg' => $config['messages']['EmptyXLS'])));
<?php
error_reporting(0);
include '../include/koneksi.php';
?>
<?php
if (isset($_FILES['userfile'])) {
require_once "library/xlsx_reader/simplexlsx.class.php";
$xlsx = new SimpleXLSX($_FILES['userfile']['tmp_name']);
//initial
//----------------------------------------
$jumlahrowimport = 0;
$gagal = 0;
list($cols, ) = $xlsx->dimension();
foreach ($xlsx->rows() as $b => $r) {
if ($b <= 1) {
continue;
}
// skip row by row array;
//membaca column 1 NIM di excel
$nim = $r[1];
//column Nama 2
$nama_mhs = $r[2];
//column number 3 hp di excel
$jurusan = $r[3];
$username = $nim;
$data = md5($nim);
$password = $data;
$foto = 'avatar.png';
$tanggal = gmdate("Y-m-d H:i:s", time() + 60 * 60 * 7);
function extract_text($file, $file0, $source_type, $url, $chrSet)
{
global $db_con, $tmp_dir, $pdftotext_path, $catdoc_path, $xls2csv_path, $op_system, $mb, $debug;
global $catppt_path, $home_charset, $command_line, $no_log, $clear, $converter_dir, $cl, $index_xmeta;
$result = array();
$home_charset1 = str_ireplace('iso-', '', $home_charset);
$charset_int = str_ireplace('iso', '', $home_charset1);
$temp_file = "tmp_file";
$filename = $tmp_dir . "/" . $temp_file;
if ($source_type == 'ods') {
$filename .= "." . $source_type . "";
}
if ($source_type == 'doc') {
$filename .= "." . $source_type . "";
}
if ($source_type == 'docx') {
$filename .= "." . $source_type . "";
}
if ($source_type == 'xlsx') {
$filename .= "." . $source_type . "";
}
if (!($handle = fopen($filename, 'w'))) {
die("Cannot open file {$filename} in temp folder");
}
mysqltest();
if (fwrite($handle, $file) === FALSE) {
die("Cannot write to file {$filename} in temp folder");
}
fclose($handle);
mysqltest();
// for PDF documents enter here
if ($source_type == 'pdf') {
/*
include('../converter/pdf2text.php');
$a = new PDF2Text();
$a->setFilename($filename);
$a->decodePDF();
echo $a->output();
*/
if (!($handle = fopen($pdftotext_path, 'rb'))) {
printStandardReport('errorNoPDFConv', $command_line);
$result[] = 'ERROR';
} else {
// prepare command line for PDF converter
if ($op_system != 'win') {
$command = "" . $pdftotext_path . " -enc UTF-8 " . $filename . "";
} else {
$command = "" . $pdftotext_path . " -cfg xpdfrc " . $filename . " -";
}
$a = exec($command, $result, $retval);
// convert the PDF document
if ($retval != '0') {
// error handler for PDF file converter
if ($retval == '1' || $retval == '3' || $retval == '127') {
if ($retval == '1') {
printStandardReport('errorOpenPDF', $command_line);
}
if ($retval == '3') {
printStandardReport('permissionError', $command_line);
}
if ($retval == '127') {
printStandardReport('noConverter', $command_line);
}
} else {
printStandardReport('ufoError', $command_line);
}
$result[] = 'ERROR';
}
$result = implode(' ', $result);
}
// for DOC and RTF files enter here
} else {
if ($source_type == 'doc' || $source_type == 'rtf') {
/*
echo "\r\n\r\n<br /> op_system: '$op_system'<br />\r\n";
echo "\r\n\r\n<br /> catdoc_path: '$catdoc_path'<br />\r\n";
echo "\r\n\r\n<br /> charset_int: '$charset_int'<br />\r\n";
echo "\r\n\r\n<br /> filename: '$filename'<br />\r\n";
*/
if ($op_system == 'win') {
$command = "" . $catdoc_path . " -s " . $charset_int . " -d utf-8 -x " . $filename . "";
$a = exec($command, $result, $retval);
if (stristr($result[0], "catdoc.exe")) {
printDocReport($result[0], $cl);
}
} else {
$message = " Indexing of .doc and .rtf documents is currently not supported on LINUX OS.";
printDocReport($message, $cl);
/*
$retval = '';
$catdoc_path = str_ireplace("catdoc.exe", "catdoc.lin", $catdoc_path);
//$command = "".$catdoc_path." -cfg xpdfrc ".$filename." -";
$command = "".$catdoc_path." -s ".$charset_int." -d utf-8 -w -x ".$filename."";
$a = exec($command, $result, $retval); // convert the DOC document
//echo "\r\n\r\n<br /> retval: '$retval'<br />\r\n";
//echo "\r\n\r\n<br>result Array:<br><pre>";print_r($result);echo "</pre>\r\n";
if ($retval) {
$result = 'ERROR';
//echo "\r\n\r\n<br /> retval: '$retval'<br />\r\n";
if($retval == '2') {
$message = " File to be converted not found";
printDocReport($message, $cl);
}
else if($retval == '3') {
$message = " Path to file not found.";
printDocReport($message, $cl);
}
else if($retval == '11') {
$message = " The executable is corrupted.";
printDocReport($message, $cl);
}
else if($retval == '12') {
$message = " Out of memory execution.";
printDocReport($message, $cl);
}
else if($retval == '22') {
$message = " dll error";
printDocReport($message, $cl);
}
else if($retval == '31') {
$message = " The association is missing, use Shell to try the OpenWith dialog.";
printDocReport($message, $cl);
}
else if($retval == '32') {
$message = " File could not be opened.";
printDocReport($message, $cl);
}
else if($retval == '126') {
$message = " Command invoked cannot execute (Permission problem or command is not an executable).";
printDocReport($message, $cl);
}
else if($retval == 127) {
$message = " Command not found.";
printDocReport($message, $cl);
}
else if($retval == 128) {
$message = " Invalid argument to exit. Exit takes only integer range 0 – 255.";
printDocReport($message, $cl);
}
else if($retval > 128 && $retval < 255) {
$message = " Fatal error code $retval";
printDocReport($message, $cl);
}
else if($retval == 255) {
$message = " Exit status out of range. Exit takes only integer range 0 – 255.";
printDocReport($message, $cl);
} else {
$message = " Unknown error code $retval.";
printDocReport($message, $cl);
}
}
*/
}
// for PPT files enter here
} else {
if ($source_type == 'ppt') {
// currently unsupported,as a failure was encountered for large PowerPoint presentations
$a = '';
/*
$command = $catppt_path." -s $charset_int -d utf-8 $filename";
$a = exec($command, $result, $retval);
*/
// for XLS spreadsheets enter here
} else {
if ($source_type == 'xls') {
$error = '';
require_once "" . $converter_dir . "/xls_reader.php";
$data = new Spreadsheet_Excel_Reader();
if ($mb == '1') {
// if extention exists, change 'iconv' to mb_convert_encoding:
$data->setUTFEncoder('mb');
}
// set output encoding.
$data->setOutputEncoding('UTF-8');
// read this document
$data->read($filename);
$error = $data->_ole->error;
if ($error == '1') {
printStandardReport('xlsError', $command_line, $no_log);
$result = 'ERROR';
} else {
$result = '';
$boundsheets = array();
$sheets = array();
$boundsheets = $data->boundsheets;
// get all tables in this file
$sheets = $data->sheets;
// get content of all sheets in all tables
if ($boundsheets) {
foreach ($boundsheets as &$bs) {
$result .= "" . $bs['name'] . ", ";
// collect all table names in this file
}
if ($sheets) {
foreach ($sheets as &$sheet) {
$cells = $sheet['cells'];
if ($cells) {
// ignore all empty cells
foreach ($cells as &$cell) {
foreach ($cell as &$content) {
$result .= "" . $content . ", ";
// collect content of all cells
}
}
}
}
}
if (strtoupper($home_charset) == 'ISO-8859-1') {
$result = utf8_encode($result);
}
}
}
// for ODS spreadsheets enter here
} else {
if ($source_type == 'ods') {
require_once "" . $converter_dir . "/ods_reader.php";
$reader = ods_reader::reader($filename);
$sheets = $reader->read($filename);
if ($sheets) {
$result = '';
foreach ($sheets as &$sheet) {
if ($sheet) {
foreach ($sheet as &$cell) {
if ($cell) {
// ignore all empty cells
foreach ($cell as &$content) {
$result .= "" . $content . " ";
// collect content of all cells
}
}
}
}
}
} else {
$result = 'ERROR';
}
// for ODT documents enter here
} else {
if ($source_type == 'odt') {
require_once "" . $converter_dir . "/odt_reader.php";
$x = new odt_reader();
// Unzip the document
$u = $x->odt_unzip($filename, false);
// read the document
$result = $x->odt_read($u[0], 2);
// create some blanks around the <div> tags
$result = str_replace("<", " <", $result);
$result = str_replace(">", "> ", $result);
//echo "\r\n\r\n<br /> odt result: $result<br />\r\n";
// for DOCX files enter here
} else {
if ($source_type == 'docx') {
// converter class supplied by http://www.phpdocx.com
$options = array('paragraph' => false, 'list' => false, 'table' => false, 'footnote' => false, 'endnote' => false, 'chart' => 0);
$docx_file = "docx.txt";
$result = '';
require_once "" . $converter_dir . "/docx/CreateDocx.inc";
CreateDocx::DOCX2TXT($filename, $tmp_dir . "/" . $docx_file, $options);
if ($file = @file_get_contents($tmp_dir . "/" . $docx_file)) {
$result = "{$file} ";
}
if ($index_xmeta) {
require_once "" . $converter_dir . "/xmeta_converter.php";
$docxmeta = new x_metadata();
$docxmeta->setDocument($filename);
/*
echo "Title : " . $docxmeta->getTitle() . "<br>";
echo "Subject : " . $docxmeta->getSubject() . "<br>";
echo "Creator : " . $docxmeta->getCreator() . "<br>";
echo "Keywords : " . $docxmeta->getKeywords() . "<br>";
echo "Description : " . $docxmeta->getDescription() . "<br>";
echo "Last Modified By : " . $docxmeta->getLastModifiedBy() . "<br>";
echo "Revision : " . $docxmeta->getRevision() . "<br>";
echo "Date Created : " . $docxmeta->getDateCreated() . "<br>";
echo "Date Modified : " . $docxmeta->getDateModified() . "<br>";
*/
$result .= $docxmeta->getTitle() . $docxmeta->getSubject() . $docxmeta->getCreator() . $docxmeta->getKeywords() . $docxmeta->getDescription() . $docxmeta->getLastModifiedBy() . $docxmeta->getRevision() . $docxmeta->getDateCreated() . $docxmeta->getDateModified();
}
@unlink($tmp_dir . "/" . $docx_file);
/*
if($result && $chrSet != "UTF-8") {
$result = @mb_convert_encoding($result, "UTF-8", $chrSet);
}
*/
// for XLSX spreadsheets enter here
} else {
if ($source_type == 'xlsx') {
$result = '';
$i = 1;
$name = '';
$finished = false;
$names = array();
require_once "" . $converter_dir . "/xlsx_reader.php";
$xlsx = new SimpleXLSX($filename);
$names = $xlsx->sheetNames();
//echo "\r\n\r\n<br>names array:<br><pre>";print_r($names);echo "</pre>\r\n";
if ($debug == 2 && $names) {
printXLSXreport(count($names), $cl);
}
foreach ($names as $my_name) {
$result .= $my_name . " ";
if ($debug == 2) {
printActKeyword($my_name);
}
}
while (!$finished) {
// get all sheets
if ($rows = $xlsx->rows($i)) {
foreach ($rows as $key) {
foreach ($key as $val) {
if ($val) {
$result .= " " . $val;
// add value of each cell
}
}
}
} else {
$finished = true;
// no more sheets found
}
//$my_name = $xlsx->sheetName($i);
//echo "\r\n\r\n<br /> sheet name $i: '$my_name'<br />\r\n";
$i++;
// try to get next sheet
}
if ($index_xmeta) {
require_once "" . $converter_dir . "/xmeta_converter.php";
$xlscxmeta = new x_metadata();
$xlscxmeta->setDocument($filename);
/*
echo "Title : " . $xlscxmeta->getTitle() . "<br>";
echo "Subject : " . $xlscxmeta->getSubject() . "<br>";
echo "Creator : " . $xlscxmeta->getCreator() . "<br>";
echo "Keywords : " . $xlscxmeta->getKeywords() . "<br>";
echo "Description : " . $xlscxmeta->getDescription() . "<br>";
echo "Last Modified By : " . $xlscxmeta->getLastModifiedBy() . "<br>";
echo "Revision : " . $xlscxmeta->getRevision() . "<br>";
echo "Date Created : " . $xlscxmeta->getDateCreated() . "<br>";
echo "Date Modified : " . $xlscxmeta->getDateModified() . "<br>";
*/
$result .= $xlscxmeta->getTitle() . $xlscxmeta->getSubject() . $xlscxmeta->getCreator() . $xlscxmeta->getKeywords() . $xlscxmeta->getDescription() . $xlscxmeta->getLastModifiedBy() . $xlscxmeta->getRevision() . $xlscxmeta->getDateCreated() . $xlscxmeta->getDateModified();
}
/*
if($result && $chrSet != "UTF-8") {
$result = @mb_convert_encoding($result, "UTF-8", $chrSet);
}
*/
// for JavaScript enter here
} else {
if ($source_type == 'js') {
$result = extract_js($file);
}
}
}
}
}
}
}
}
}
if ($result != 'ERROR') {
if (is_array($result)) {
$result = implode(" ", $result);
}
$count = strlen($result);
if ($count == '0') {
// if there was not one word found, print warning message
if ($source_type == 'js') {
printStandardReport('jsEmpty', $command_line, $no_log);
} else {
printStandardReport('nothingFound', $command_line, $no_log);
}
$result = 'ERROR';
}
}
unlink($filename);
mysqltest();
if ($clear == 1) {
unset($command, $retval, $a, $file, $count);
}
return $result;
}
public function callback_metabox($post, $metabox)
{
global $post;
// Nonce field for some validation
wp_nonce_field(plugin_basename(__FILE__), 'jw_post_type');
// Get all inputs from $data
$custom_fields = $metabox['args'][0];
// Get the saved values
$meta = get_post_custom($post->ID);
// Check the array and loop through it
if (!empty($custom_fields)) {
echo '<style> ul.admin-checkboxes { border: 1px solid #DFDFDF; background-color: #FFFFFF; margin:0px; 5px; padding: 5px; list-style-type:none; max-height: 80px; overflow-y: auto; } </style>';
// Loop through $custom_fields
foreach ($custom_fields as $label => $type) {
$field_id_name = self::uglify($this->post_type_name . '_' . $metabox['id'] . '_' . $label);
$select = '';
$checkbox = '';
if (is_array($type)) {
if (strtolower($type['type']) == 'select') {
// filter through them, and create options
$select .= "<select name='{$field_id_name}' class='widefat'>";
foreach ($type['options'] as $key => $option) {
$set_selected = isset($meta[$field_id_name]) && $meta[$field_id_name][0] == $key ? "selected = 'selected'" : '';
$select .= "<option value='{$key}' {$set_selected}> {$option} </option>";
}
$select .= "</select>";
array_push($_SESSION['taxonomy_data'], $field_id_name);
}
if (strtolower($type['type']) == 'checkbox') {
// filter through them, and create options
$checkbox .= "<ul class='admin-checkboxes'>";
foreach ($type['options'] as $key => $option) {
$answer_value = isset($meta[$field_id_name]) ? $meta[$field_id_name][0] : '';
$answer_value = explode(':::', $answer_value);
$checked = in_array($key, $answer_value) ? "checked = 'checked'" : '';
$checkbox .= "<li><input type='checkbox' name='{$field_id_name}[]' value='{$key}' {$checked} /> {$option}</li>";
}
$checkbox .= "</ul>";
array_push($_SESSION['taxonomy_data'], $field_id_name);
}
} else {
$value = isset($meta[$field_id_name][0]) ? $meta[$field_id_name][0] : '';
$checked = $type == 'checkbox' && !empty($value) ? 'checked' : '';
$checkbox .= "<input type='checkbox' name='{$field_id_name}' value='{$value}' {$checked} />";
array_push($_SESSION['taxonomy_data'], $field_id_name);
}
$lookup = array("text" => "<input type='text' name='{$field_id_name}' value='{$value}' class='widefat' />", "textarea" => "<textarea name='{$field_id_name}' class='widefat' rows='10'>{$value}</textarea>", "checkbox" => isset($checkbox) && !empty($checkbox) ? $checkbox : $checkbox, "select" => isset($select) && !empty($select) ? $select : '', "file" => "<input type='file' class='widefat' name='{$field_id_name}' id='{$field_id_name}' />", "spreadsheets" => "<input type='file' class='widefat' name='{$field_id_name}' id='{$field_id_name}' />");
?>
<p>
<label><?php
echo ucwords($label) . ':';
?>
</label>
<?php
echo $lookup[is_array($type) ? $type['type'] : $type];
?>
</p>
<?php
// If a file was uploaded, display it below the input.
if (in_array($type, array('file', 'spreadsheets'))) {
// display the image
$file = get_post_meta($post->ID, $field_id_name, true);
$file_type = wp_check_filetype($file);
$image_types = array('jpeg', 'jpg', 'bmp', 'gif', 'png');
$excel_types = array('xls', 'xlsx', 'csv');
if (isset($file)) {
if (in_array($file_type['ext'], $image_types)) {
echo " <p><img src='{$file}' alt='' style='max-width: 400px;' /> </p>";
} elseif (in_array($file_type['ext'], $excel_types)) {
$upload_dir = wp_upload_dir();
$file_path = preg_replace("#" . preg_quote($upload_dir['url']) . "#i", $upload_dir['path'], $file);
$xlsx = new SimpleXLSX($file_path);
echo '<table class="widefat fixed" cellspacing="0">';
$i = 0;
foreach ($xlsx->rows() as $index => $rows) {
$class = $i % 3 == 0 ? 'class="alternate"' : '';
$cells = $i == 0 ? 'th' : 'td';
echo "<tr {$class}>";
foreach ($rows as $column => $text) {
echo "<{$cells}>" . (!empty($text) ? $text : ' ') . "</ {$cells}>";
}
echo '</tr>';
$i++;
}
echo '</table>';
} else {
echo " <p><a href='{$file}'>{$file}</a></p> ";
}
}
}
?>
<?php
}
}
}
/**
* Import Microsoft Excel 2007-2013 data
*
* @since 1.1.0
*/
protected function import_xlsx()
{
TablePress::load_file('simplexlsx.class.php', 'libraries');
$simplexlsx = new SimpleXLSX($this->import_data, true);
if ($simplexlsx->success() && 0 < $simplexlsx->sheetsCount()) {
// Get Worksheet ID of the first Worksheet (not necessarily "1", which is the default in SimpleXLSX)
$sheet_ids = array_keys($simplexlsx->sheetNames());
$worksheet_id = $sheet_ids[0];
$this->imported_table = array('data' => $this->pad_array_to_max_cols($simplexlsx->rows($worksheet_id)));
} else {
$output = '<strong>' . __('The imported file contains errors:', 'tablepress') . '</strong><br /><br />' . $simplexlsx->error() . '<br />';
wp_die($output, 'Import Error', array('response' => 200, 'back_link' => true));
}
}
function create_excel_file($file_name)
{
$new_file_name = $file_name;
if (empty($new_file_name)) {
return false;
}
if (empty($_FILES['file']) || empty($_FILES["file"]['name'])) {
return false;
}
require_once plugin_dir_path(__FILE__) . '../includes/simplexlsx.class.php';
$xlsx = new SimpleXLSX($_FILES['file']['tmp_name']);
$sheetNames = $xlsx->sheetNames();
if (is_array($sheetNames)) {
foreach ($sheetNames as $sheetId => $sheetName) {
if ($sheetId != 1) {
$the_name = $new_file_name . '_sheet_' . $sheetId;
} else {
$the_name = $new_file_name;
}
$jsonData[$sheetId] = json_encode($xlsx->rows($sheetId));
$this->createJsonDataFile($jsonData[$sheetId], $the_name);
}
}
$file_ext = end(explode(".", $_FILES["file"]['name']));
$options = array('slug' => $new_file_name, 'filename' => $new_file_name . '.' . $file_ext, 'json_file' => $new_file_name . '.json', 'options_file' => $new_file_name . '.options.json', 'file_ext' => $file_ext, 'filesize' => filesize($_FILES["file"]["tmp_name"]), 'upload_time' => time(), 'sheet_names' => $sheetNames);
$file_name = $this->upload_dir . '/' . $new_file_name . '.options.json';
$fp = fopen($file_name, "wb");
fwrite($fp, json_encode($options));
fclose($fp);
return array('jsonData' => $jsonData, 'options' => $options);
}
<?php
/*
$db_name = Yii::app()->db->connectionString;
$value = substr(strstr(strstr($db_name, 'dbname='),'='),1);
echo $value;
*/
include 'simplexlsx.class.php';
$date = $_REQUEST['date'];
$xlsx = new SimpleXLSX('college_data/csv_docs/sheet'.$date.'.xlsx');
$data1 =$xlsx->rows();
$i=1;
$count = 0;
foreach($data1 as $data)
{
$var1 = null;
$eid = null;
if($i==1)
{
function unixstamp( $excelDateTime )
{
$d = floor( $excelDateTime ); // seconds since 1900
$t = $excelDateTime - $d;
return ($d > 0) ? ( $d - 25569 ) * 86400 + $t * 86400 : $t * 86400;
}
$unixDateVal = unixstamp($data[2]);
//var_dump($unixDateVal);
$exdate=date('Y-m-d',$unixDateVal);
//echo $exdate; exit;
$filename = "Book".$exdate.".xlsx";
/**
* Read ExcelX file
*
* @param string $file_name
* @return array
*/
public static function readXlsxFile($file_name)
{
$xlsx = new SimpleXLSX(self::TEMP_FOLDER . $file_name);
return $xlsx->dimension();
}
function student_bulk_add($param1 = '')
{
if ($this->session->userdata('admin_login') != 1) {
redirect(base_url(), 'refresh');
}
if ($param1 == 'import_excel') {
move_uploaded_file($_FILES['userfile']['tmp_name'], 'uploads/student_import.xlsx');
// Importing excel sheet for bulk student uploads
include 'simplexlsx.class.php';
$xlsx = new SimpleXLSX('uploads/student_import.xlsx');
list($num_cols, $num_rows) = $xlsx->dimension();
$f = 0;
foreach ($xlsx->rows() as $r) {
// Ignore the inital name row of excel file
if ($f == 0) {
$f++;
continue;
}
for ($i = 0; $i < $num_cols; $i++) {
if ($i == 0) {
$data['name'] = $r[$i];
} else {
if ($i == 1) {
$data['birthday'] = $r[$i];
} else {
if ($i == 2) {
$data['sex'] = $r[$i];
} else {
if ($i == 3) {
$data['address'] = $r[$i];
} else {
if ($i == 4) {
$data['phone'] = $r[$i];
} else {
if ($i == 5) {
$data['email'] = $r[$i];
} else {
if ($i == 6) {
$data['password'] = $r[$i];
} else {
if ($i == 7) {
$data['roll'] = $r[$i];
}
}
}
}
}
}
}
}
}
$data['class_id'] = $this->input->post('class_id');
$this->db->insert('student', $data);
//print_r($data);
}
redirect(base_url() . 'index.php?admin/student_information/' . $this->input->post('class_id'), 'refresh');
}
$page_data['page_name'] = 'student_bulk_add';
$page_data['page_title'] = get_phrase('add_bulk_student');
$this->load->view('backend/index', $page_data);
}
$sep = new sepa();
$sep->status_excelsession();
$sep->form_excel_ds($_SESSION['umsatz_id_temp']);
$bu = new buchen();
$bu->buchungsjournal_auszug($_SESSION['geldkonto_id'], $_SESSION['temp_kontoauszugsnummer']);
break;
case "excel_buchen_ALT":
// print_r($_SESSION);
if (!$_FILES) {
echo '<h1>Upload</h1>
<form method="post" enctype="multipart/form-data">
*.XLSX <input type="file" name="file" /> <input type="submit" value="Parse" />
</form>';
} else {
require_once "classes/simplexlsx.class.php";
$xlsx = new SimpleXLSX($_FILES['file']['tmp_name']);
echo '<pre>';
// print_r($xlsx->rows(5));
echo '<h1>Parsing Result</h1>';
echo "<table border=\"1\" cellpadding=\"3\" style=\"border-collapse: collapse\">";
$arr = $xlsx->rows(5);
$anz = count($arr);
for ($a = 2; $a < $anz; $a++) {
if (empty($arr[$a][3])) {
// Kontoauszug
// echo $arr[$a][0]."<br>";
} else {
echo "<tr><td>";
echo $arr[$a][0] . "<br>";
// Kontobez
$gk = new gk();
function gravaImportar()
{
global $db, $TPLV, $geral, $usuario, $urls, $log;
include GLOBAL_PATH . 'import/simplexlsx.class.php';
$xlsx = new SimpleXLSX(GLOBAL_PATH . 'import/online.xlsx');
$cont = 0;
//LIMPA DADOS TABELA
$sql = 'DELETE FROM onde_comprar_online';
$db->db_query($sql);
foreach ($xlsx->rows() as $registro) {
$cont++;
if ($cont > 1) {
$nome = $registro[0];
$link = $registro[1];
$atuacao = $registro[2];
$cnpj = $registro[3];
$responsavel = $registro[4];
$email = $registro[5];
$fone = $registro[6];
//CADASTRA REGISTRO
$sql = 'INSERT INTO onde_comprar_online (nome,atuacao,link,cnpj,responsavel,email,fone,ativo) VALUES ("' . $nome . '","' . $atuacao . '","' . $link . '","' . $cnpj . '","' . $responsavel . '","' . $email . '","' . $fone . '","1")';
$db->db_query($sql);
}
}
$TPLV->newBlock('importar');
$TPLV->assignGlobal('msg', 'Dados Atualizados com sucesso!');
$TPLV->printToScreen();
}
if (count($csvline) > 1) {
$search = $csvline[0];
$replace = $csvline[1];
$search = trim($search);
$replace = trim($replace);
$search = $search == '' ? null : $search;
$replace = $replace == '' ? null : $replace;
if ($search !== null && $replace !== null) {
$search_replaces[$search] = $replace;
}
}
}
fclose($fp);
} else {
if ($extension_xlsx == '.xlsx') {
$xlsx = new SimpleXLSX($srch_file);
foreach ($xlsx->rows() as $row) {
$search = $row[0];
$replace = $row[1];
$search = trim($search);
$replace = trim($replace);
$search = $search == '' ? null : $search;
$replace = $replace == '' ? null : $replace;
if ($search !== null && $replace !== null) {
$search_replaces[$search] = $replace;
}
}
}
}
foreach ($search_replaces as $search => $replace) {
$reports[] = array_merge(array('search' => $search, 'replace' => $replace), icit_srdb_replacer($connection, $search, $replace, $tables));
<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
<link href="css/bootstrap.min.css" rel="stylesheet">
<link href="css/style.css" rel="stylesheet">
<script type='text/javascript' src='script.js'></script>
<?php
session_start();
$root = "./";
require "includes.php";
?>
</head>
<body>
<?php
include 'nav.php';
include 'simplexlsx.class.php';
$xlsx = new SimpleXLSX($_FILES['avatar']['tmp_name']);
$name = $_FILES['avatar']['name'];
$names = explode('.', $name);
$nameFinal = "";
$i = 0;
for ($i = 0; $i < count($names) - 1; $i++) {
$nameFinal .= $names[$i];
}
$email_owner = $_SESSION['email'];
$url_txt = print_r($xlsx->rows(), true);
// echo $url_txt;
echo '<br/>';
$_type = $_SESSION['type'];
$contentDao = new ContentDAO(DynamoDbClientBuilder::get());
$content = new Content();
$content->setName($nameFinal);
public function fileupload($file_name)
{
// $file_name = $result['filename'];
// for header columns checking
$headers_array = array('1' => array('0' => 'Vendor(FILTER)', '1' => 'Plant', '2' => 'Products(FILTER)', '3' => 'Capabilities(FILTER)', '4' => 'Equipment(FILTER)', '5' => 'Street', '6' => 'City', '7' => 'State', '8' => 'Country', '9' => 'Zipcode'), '2' => array('0' => 'Client(FILTER)', '1' => 'Division(FILTER)', '2' => 'LocationType(FILTER)', '3' => 'Street1', '4' => 'City', '5' => 'State', '6' => 'Country', '7' => 'Zipcode'), '3' => array('0' => 'Distributor(FILTER)', '1' => 'PlantType(FILTER)', '2' => 'Plant', '3' => 'PlantID', '4' => 'DistributionType(FILTER)', '5' => 'Performance(DynamicMapPinColor)', '6' => 'Street', '7' => 'City', '8' => 'State', '9' => 'Country', '10' => 'Zipcode'));
if (file_exists($file_name)) {
require_once APPPATH . 'third_party/upload.php';
$xlsx = new SimpleXLSX($file_name);
$err_flag = false;
if (count($xlsx->sheets) > 2) {
// checking number of sheets
$excel_data = array();
for ($i = 1; $i < 4; $i++) {
$excel_data[$i] = $xlsx->rows($i);
}
foreach ($excel_data as $s => $sheet) {
// looping sheets
if (count($sheet[0])) {
foreach ($sheet[0] as $k => $v) {
$vv = preg_replace('/\\s+/', '', $v);
if ($headers_array[$s][$k] != $vv) {
// checking file headers
$err_flag = true;
}
}
}
}
} else {
$err_flag = true;
}
if ($err_flag) {
// return error if not match
echo json_encode(array('fail' => 'Columns Headers not matched'));
die;
} else {
set_time_limit(900);
// store file detail in log table
$log_sql = 'insert into log_files(`file_name`,`temp_name`,`date_uploaded`,`ipaddress`) values ("' . $_REQUEST['filename'] . '","' . $file_name . '","' . gmdate('Y-m-d H:i:s') . '","' . $_SERVER['REMOTE_ADDR'] . '")';
$this->db->query($log_sql);
// delete existing data
$arr_del_sql = array();
array_push($arr_del_sql, 'SET FOREIGN_KEY_CHECKS = 0;');
array_push($arr_del_sql, 'truncate user_capabilities;');
array_push($arr_del_sql, 'truncate user_distribution_types;');
array_push($arr_del_sql, 'truncate user_equipments;');
array_push($arr_del_sql, 'truncate user_products;');
array_push($arr_del_sql, 'truncate user_details;');
array_push($arr_del_sql, 'truncate users;');
array_push($arr_del_sql, 'truncate capabilities;');
array_push($arr_del_sql, 'truncate distribution_types;');
array_push($arr_del_sql, 'truncate division;');
array_push($arr_del_sql, 'truncate equipments;');
array_push($arr_del_sql, 'truncate location_type;');
array_push($arr_del_sql, 'truncate plant_type;');
array_push($arr_del_sql, 'truncate products;');
foreach ($arr_del_sql as $del_sql) {
$this->db->query($del_sql);
}
// store new excel data
// table names
$tables = array('Capabilities' => 'capabilities', 'Vendor' => 'users', 'Client' => 'users', 'Distributor' => 'users', 'Products' => 'products', 'Equipment' => 'equipments', 'Division' => 'division', 'LocationType' => 'location_type', 'PlantType' => 'plant_type', 'DistributionType' => 'distribution_types');
$bridge_tables = array('Capabilities' => 'user_capabilities', 'Products' => 'user_products', 'Equipment' => 'user_equipments', 'DistributionType' => 'user_distribution_types');
// reading excell
$excel_data = array();
for ($s = 1; $s < 4; $s++) {
// extract values from excel
$excel_data[$s] = $xlsx->rows($s);
}
$filter_index = array();
$headings = array();
$user_details_vals = '';
// framing inser values
$master_array = array();
foreach ($excel_data as $s => $sheet) {
// looping sheets
$row_cnt = count($sheet);
foreach ($sheet[0] as $k => $v) {
// identifiyin filter columns
if (strpos($v, '(FILTER)')) {
$filter_index[$s][$k] = trim(str_replace('(FILTER)', '', $v));
$flt = preg_replace('/\\s+/', '', $filter_index[$s][$k]);
// select master data
$qry = 'select id,name from ' . $tables[$flt];
$ds = $this->db->query($qry);
// while ($row = mysql_fetch_array($ds)) {
foreach ($ds->result_array() as $row) {
$master_array[$s][$k][$row['id']] = $row['name'];
}
}
$txt = preg_replace('/\\s+/', '', str_replace('(FILTER)', '', $v));
//remove spaces
$headings[$s][$k] = $txt;
}
for ($r = 1; $r < $row_cnt; $r++) {
//looping records in each sheet
$record_data = array();
foreach ($sheet[$r] as $k => $val) {
// insert values into master tables
if (!empty($filter_index[$s][$k])) {
$name = preg_replace('/\\s+/', '', trim($filter_index[$s][$k]));
//remove spaces
if ($k == 0) {
$val = trim($val);
if ($val != '' && (!isset($master_array[$s][$k]) || !in_array($val, $master_array[$s][$k]))) {
//if(!isset($master_array[$s][$k]) || !in_array($val,$master_array[$s][$k])){
//$val = ($val==''?'Blank':$val);
$ins = 'INSERT INTO ' . $tables[$name] . ' (`name`,`ipaddress`) VALUES ("' . addslashes($val) . '","' . $_SERVER['REMOTE_ADDR'] . '")';
$res = $this->db->query($ins);
$insert_id = $this->db->insert_id();
$master_array[$s][$k][$insert_id] = $val;
}
} else {
$arrvals = explode(', ', $val);
foreach ($arrvals as $v) {
$v = trim($v);
if (!isset($master_array[$s][$k]) || !in_array($v, $master_array[$s][$k])) {
if ($v != '') {
//$v = ($v==''?'Blank':$v);
$ins = 'INSERT INTO ' . $tables[$name] . ' (`name`,`ipaddress`) VALUES ("' . addslashes($v) . '","' . $_SERVER['REMOTE_ADDR'] . '")';
$res = $this->db->query($ins);
$insert_id = $this->db->insert_id();
$master_array[$s][$k][$insert_id] = $v;
}
}
}
}
$record_data[$s][$k] = !empty($master_array[$s][$k]) ? array_search(trim($val), $master_array[$s][$k]) : '';
}
}
// insert values into main details table
$street_val = '';
if (array_search('Street', $headings[$s])) {
$street_val = $sheet[$r][array_search('Street', $headings[$s])];
}
if (array_search('Street1', $headings[$s])) {
$street_val = $sheet[$r][array_search('Street1', $headings[$s])];
}
$zip_code = '';
if (array_search('Zipcode', $headings[$s])) {
$zip_code = preg_replace('/\\s+/', '', trim($sheet[$r][array_search('Zipcode', $headings[$s])]));
// remove space in zipcode
if (is_numeric($zip_code)) {
if (strlen($zip_code) == 4) {
$zip_code = '0' . $zip_code;
} else {
if (strlen($zip_code) == 3) {
$zip_code = '00' . $zip_code;
}
}
} else {
if (strpos($zip_code, '-')) {
// consider first value if two zip codes are given
$zip = explode('-', $zip_code);
$zip_code = $zip[0];
}
}
}
if ($record_data[$s][0] != '') {
$ins = 'INSERT INTO user_details (user_type_id,users_id,street,city,state,country,zip_code,plant,plant_id,division_id,location_type_id,plant_type_id,performance,ipaddress) VALUES(' . $s . ',' . $record_data[$s][0] . ',' . '"' . $xlsx->addString($street_val) . '",' . '"' . (array_search('City', $headings[$s]) ? $sheet[$r][array_search('City', $headings[$s])] : '""') . '",' . '"' . (array_search('State', $headings[$s]) ? $sheet[$r][array_search('State', $headings[$s])] : '""') . '",' . '"' . (array_search('Country', $headings[$s]) ? $sheet[$r][array_search('Country', $headings[$s])] : '""') . '",' . '"' . (array_search('Zipcode', $headings[$s]) ? $zip_code : '""') . '",' . '"' . (array_search('Plant', $headings[$s]) ? $sheet[$r][array_search('Plant', $headings[$s])] : '') . '",' . '"' . (array_search('PlantID', $headings[$s]) ? $sheet[$r][array_search('PlantID', $headings[$s])] : '') . '",' . (array_search('Division', $headings[$s]) && $record_data[$s][array_search('Division', $headings[$s])] != '' ? $record_data[$s][array_search('Division', $headings[$s])] : 'null') . ',' . (array_search('LocationType', $headings[$s]) && $record_data[$s][array_search('LocationType', $headings[$s])] != '' ? $record_data[$s][array_search('LocationType', $headings[$s])] : 'null') . ',' . (array_search('PlantType', $headings[$s]) && $record_data[$s][array_search('PlantType', $headings[$s])] != '' ? $record_data[$s][array_search('PlantType', $headings[$s])] : 'null') . ',' . '"' . (array_search('Performance(DynamicMapPinColor)', $headings[$s]) ? $sheet[$r][array_search('Performance(DynamicMapPinColor)', $headings[$s])] : '') . '","' . $_SERVER['REMOTE_ADDR'] . '"' . ');';
$res = $this->db->query($ins);
$row_id = $this->db->insert_id();
// insert values into bridge tables
if ($row_id) {
foreach ($filter_index[$s] as $k => $v) {
if (trim($sheet[$r][$k])) {
$name = preg_replace('/\\s+/', '', trim($filter_index[$s][$k]));
//remove spaces
if (isset($bridge_tables[$name])) {
$ch_qry = '';
$arrvals = explode(', ', $sheet[$r][$k]);
foreach ($arrvals as $v) {
if (trim($v) != '') {
$ch_qry .= '(' . $row_id . ',' . array_search(trim($v), $master_array[$s][$k]) . '),';
}
}
$qry = 'insert into ' . $bridge_tables[$name] . ' values ' . trim($ch_qry, ',') . ';';
$this->db->query($qry);
}
}
}
}
}
}
}
// update latitude and longitude values from available zipcodes
//Commented to on 22 MAY 2014
/* $latlong_sql = 'UPDATE user_details AS d
SET d.longitude= (SELECT u.Longitude FROM usa_zips u WHERE u.ZIPCode=d.zip_code LIMIT 1),
d.latitude=(SELECT u.Latitude FROM usa_zips u WHERE u.ZIPCode=d.zip_code LIMIT 1);';
$ds = $this->db->query($latlong_sql);*/
// update latitude and longitude values from available address library
$qry = 'SELECT id,street,city,state,country,zip_code FROM user_details WHERE is_updated="0"';
$ds = $this->db->query($qry);
foreach ($ds->result_array() as $row) {
$addr = '';
if (trim($row['street']) != '') {
$addr .= $row['street'];
if ($row['street'] != '') {
$addr .= ' ' . $row['city'];
if ($row['state'] != '') {
$addr .= ' ' . $row['state'];
if ($row['state'] != $row['country'] && $row['country'] != '') {
$addr .= ' ' . $row['country'];
}
}
}
}
// check in address library
$chk_sql = 'SELECT latitude,longitude FROM address_lib
WHERE street="' . $row['street'] . '" AND city="' . $row['city'] . '" AND state="' . $row['state'] . '" AND country="' . $row['country'] . '";';
$chk_ds = mysql_query($chk_sql);
$rec = mysql_fetch_row($chk_ds);
if (!empty($rec)) {
// if exist, update to user details table
$latlong_sql = 'UPDATE user_details SET longitude= "' . $rec[1] . '",latitude="' . $rec[0] . '",is_updated="1" where id=' . $row['id'];
$res = mysql_query($latlong_sql);
} else {
$this->getgooglelatlang($row['id']);
}
}
// fetching errored records
$sql = 'SELECT ud.id,ud.user_type_id,ud.street,ud.city,ud.state,ud.country,ud.zip_code,ud.latitude,ud.longitude,u.name
FROM user_details ud
LEFT JOIN users u ON ud.users_id=u.id
WHERE latitude IS NULL';
$res = $this->db->query($sql);
$err = array();
foreach ($res->result_array() as $row) {
$err[$row['user_type_id']][] = array('id' => $row['id'], 'street' => $row['street'], 'city' => $row['city'], 'user_type_id' => $row['user_type_id'], 'name' => $row['name'], 'country' => $row['country'], 'zip_code' => $row['zip_code'], 'latitude' => $row['latitude'], 'longitude' => $row['longitude']);
}
$sql = 'SELECT COUNT(ud.id) as cnt,ud.user_type_id FROM user_details ud
WHERE ud.latitude IS NOT NULL GROUP BY ud.user_type_id';
$res = $this->db->query($sql);
$succ_arr = array();
foreach ($res->result_array() as $row) {
$succ_arr[$row['user_type_id']] = $row['cnt'];
}
$rs = array('success' => true, 'err_data' => $err, 'succ_data' => $succ_arr);
echo json_encode($rs);
}
} else {
echo json_encode(array('fail' => 'unable to get file'));
}
}
public function create()
{
$data = array();
$this->load->model('uploader_model');
$this->load->helper('url');
$this->load->helper('form');
$this->load->library('form_validation');
$this->form_validation->set_error_delimiters('<span class="help-block">', '</span>');
$rules = array(array('field' => 'table', 'label' => 'Table Name', 'rules' => 'trim|required'));
$this->form_validation->set_rules($rules);
if ($this->form_validation->run() === TRUE) {
$table = $this->input->post('table');
/*
$content = $this->input->post('content');
$html = new simple_html_dom();
$html->load($content);
$rows = array();
foreach ($html->find('table') as $tbl) {
foreach ($tbl->find('tr') as $tr) {
$row = array();
//Loop over tds
foreach ($tr->find('td') as $td) {
$row[] = $td->innertext;
}
$rows[] = $row;
}
}
*/
move_uploaded_file($_FILES['userfile']['tmp_name'], 'uploads/student_import.xlsx');
// Importing excel sheet for bulk student uploads
include 'simplexlsx.class.php';
$xlsx = new SimpleXLSX('uploads/student_import.xlsx');
//list($num_cols, $num_rows) = $xlsx->dimension();
$f = 0;
$items = array();
var_dump($xlsx->rows());
exit;
//Build Db insert
foreach ($xlsx->rows() as $rws) {
//if ($rws[0] != '') {
// Ignore the inital name row of excel file
if ($f == 0) {
$f++;
continue;
}
//Get the item structure
$item = $this->getItemStructure($table);
$keys = array_keys($item);
for ($i = 0; $i < count($rws); $i++) {
if ($keys[$i] == "password") {
$item[$keys[$i]] = $rws[$i] != '' ? md5($rws[$i]) : md5(trim($item[$keys[$i]]));
} else {
$item[$keys[$i]] = $rws[$i] != '' ? $rws[$i] : trim($item[$keys[$i]]);
}
}
$items[] = $item;
//}
}
$this->uploader_model->create_multiple($items, $table);
}
//$this->data['siteinfos'] = NULL;
//$this->data['alert'] = NULL;
$this->data["subview"] = "uploader/create";
$this->load->view('_layout_main', $this->data);
//$this->load->view('uploader/create', $data);
}
function load_excelxlsx($xlsx_datei)
{
$xlsx = new SimpleXLSX($xlsx_datei);
$records = $xlsx->rows();
foreach ($records as $i => $row) {
if ($row[0] and $i > 0) {
$records[$i][0] = $this->ExcelToPHPObject($row[0])->format('Y-m-d H:i:s');
}
}
return $records;
}
<?php
include 'simplexlsx.class.php';
$xlsx = new SimpleXLSX('countries_and_population.xlsx');
echo '<h1>$xlsx->rows()</h1>';
echo '<pre>';
print_r($xlsx->rows());
echo '</pre>';
echo '<h1>$xlsx->rowsEx()</h1>';
echo '<pre>';
print_r($xlsx->rowsEx());
echo '</pre>';
<?php
include 'simplexlsx.class.php';
$xlsx = new SimpleXLSX('countries_and_population.xlsx');
echo '<table cellpadding="10">
<tr><td valign="top">';
// output worsheet 1
list($num_cols, $num_rows) = $xlsx->dimension();
echo '<h1>Sheet 1</h1>';
echo '<table>';
foreach ($xlsx->rows() as $r) {
echo '<tr>';
for ($i = 0; $i < $num_cols; $i++) {
echo '<td>' . (!empty($r[$i]) ? $r[$i] : ' ') . '</td>';
}
echo '</tr>';
}
echo '</table>';
echo '</td><td valign="top">';
// output worsheet 2
list($num_cols, $num_rows) = $xlsx->dimension(2);
echo '<h1>Sheet 2</h1>';
echo '<table>';
foreach ($xlsx->rows(2) as $r) {
echo '<tr>';
for ($i = 0; $i < $num_cols; $i++) {
echo '<td>' . (!empty($r[$i]) ? $r[$i] : ' ') . '</td>';
}
echo '</tr>';
}
echo '</table>';
public static function excelToModel($modelclass, $excelfile, $rowbegin, $rowend, $map)
{
Yii::import('application.vendors.simplexlsx.*');
$output = array();
//get path of excelfile
$file = Files::model()->findByPk($excelfile);
$excelfile = Yii::getPathOfAlias(Yii::app()->params['filesAlias']) . DIRECTORY_SEPARATOR . $file->id . DIRECTORY_SEPARATOR . $file->originalname;
if (preg_match("/xlsx\$/", $file->originalname) != 0) {
//require_once(Yii::getPathOfAlias('application.vendors.simplexlsx.simplexlsx\..class\..php'));
//Yii::import('application.vendors.simplexlsx.simplexlsx\.class.php');
$xlsx = new SimpleXLSX($excelfile);
$rows = $xlsx->rows();
//var_dump($rows);
//exit;
for ($row = $rowbegin; $row <= $rowend; $row++) {
$model = new $modelclass();
$str = "";
try {
foreach ($map as $field => $col) {
if (is_array($col)) {
$colnew = $col['col'];
$_model = $col['model'];
$_attr = $col['attr'];
$_field = $col['field'];
$model->{$field} = $_model::model()->findByAttributes(array($_attr => $rows[$row - $rowbegin + 1][$colnew]))->{$_field};
$str .= $model->{$field};
$str .= ",";
} else {
//print_r($rows[$row-$rowbegin+1][$col]);
//print_r($model);
//echo $field;
//exit;
$stemp = $rows[$row - $rowbegin + 1][$col];
//echo print_r($rows);
//echo print_r($map);
//exit;
//$stemp="hi";
$model->{$field} = (string) $stemp;
$str .= $model->{$field} . ",";
}
}
} catch (Exception $ex) {
//echo ($ex->getMessage()."\$field");
}
$output[$row - $rowbegin][0] = $str;
if ($model->save()) {
$output[$row - $rowbegin][1] = "Saved without errors";
} else {
$output[$row - $rowbegin][1] = "Not saved due to error(s) " . $model->getError('name');
}
}
} else {
if (preg_match("/xls\$/", $file->originalname) != 0) {
//require_once('php-excel-reader/excel_reader2.php');
Yii::import('application.vendors.php-excel-reader.*');
$xls = new Spreadsheet_Excel_Reader($excelfile);
for ($row = $rowbegin; $row <= $rowend; $row++) {
$model = new $modelclass();
$str = "";
foreach ($map as $field => $col) {
if (is_array($col)) {
$colnew = $col['col'];
$_model = $col['model'];
$_attr = $col['attr'];
$_field = $col['field'];
$str .= $model->{$field} = $_model::model()->findByAttributes(array($_attr => $xls->val($row, $colnew)))->{$_field};
$str .= ",";
} else {
$str .= $model->{$field} = $xls->val($row, $col);
$str .= ",";
}
$output[$row][0] = $str;
if ($model->save()) {
$output[$row][1] = "Saved without errors";
} else {
$output[$row][1] = "Not saved due to Errors";
}
}
}
} else {
//echo "error in excel file\n"; exit;
}
}
$str1 = "<table border=1><th>Row</th><th>Data</th><th>Result</th>";
foreach ($output as $row => $arr) {
$str1 .= "<tr><td>" . $row . "</td>" . "<td>" . $output[$row][0] . "</td>" . "<td>" . $output[$row][1] . "</td>" . "</tr>" . "";
}
$str1 .= "</table>";
return $str1;
//return CJSON::encode(array('html'=>$str1));
}
