public function actionBrowse()
{
$response = array("status" => "failed");
$filename = explode(".", $_FILES['myfile']['name']);
$fname = current($filename);
$extension = end($filename);
$phonenumbers = array();
if ($extension == "xls") {
// or $extension == "xlsx"
Yii::import('application.extensions.ExcelReader.*');
require_once 'excel_reader.php';
// include the class
$path = $_FILES['myfile']['tmp_name'];
// creates an object instance of the class, and read the excel file data
$excel = new PhpExcelReader();
$excel->read($path);
$nr_sheets = count($excel->sheets);
// gets the number of sheets
if ($nr_sheets > 0) {
// traverses the number of sheets and sets html table with each sheet data in $excel_data
$sheet = $excel->sheets[0];
$rows = $sheet['numRows'];
$cols = $sheet['numCols'];
if ($rows > 1) {
$fields = array();
$x = 1;
$y = 1;
while ($y <= $cols) {
$fields[$y - 1] = isset($sheet['cells'][$x][$y]) ? str_replace("\\s", "", $sheet['cells'][$x][$y]) : '';
$y++;
}
$nameindex = $this->array_search2d("name", $fields);
$nameindex = $nameindex === false ? false : $nameindex + 1;
$numberindex = $this->array_search2d("number", $fields);
$numberindex = $numberindex === false ? false : $numberindex + 1;
if ($numberindex === false) {
$response["message"] = "Excel file must have a field `number`";
} else {
$x++;
while ($x <= $rows) {
if ($nameindex !== false) {
$phonenumbers[$x - 1]["name"] = isset($sheet['cells'][$x][$nameindex]) ? $sheet['cells'][$x][$nameindex] : '';
}
$phonenumbers[$x - 1]["number"] = isset($sheet['cells'][$x][$numberindex]) ? $sheet['cells'][$x][$numberindex] : '';
$x++;
}
$response["status"] = "success";
$response["numbers"] = $phonenumbers;
}
} else {
$response["message"] = "No data found";
}
} else {
$response["message"] = "No data found";
}
} else {
if ($extension == "csv") {
$contents = file_get_contents($_FILES['myfile']['tmp_name']);
$datas = array_map("str_getcsv", preg_split('/\\r*\\n+|\\r+/', $contents));
$start = 0;
if (isset($datas[$start])) {
$nameindex = $this->array_search2d("name", $datas[$start]);
//array_search('name', str_replace("\s","",$datas[$start]));
$numberindex = $this->array_search2d("number", $datas[$start]);
//array_search('number', $datas[$start]);
//echo $numberindex;exit;
if ($numberindex === false) {
$response["message"] = "CSV file must have a field `number`";
} else {
$start++;
while ($start < count($datas)) {
if ($nameindex) {
$phonenumbers[$start - 1]["name"] = $datas[$start][$nameindex];
}
$phonenumbers[$start - 1]["number"] = $datas[$start][$numberindex];
$start++;
}
$response["status"] = "success";
$response["numbers"] = $phonenumbers;
}
} else {
$response["message"] = "No datas found";
}
} else {
$response["message"] = "Please upload a .csv OR .xls file";
}
}
echo json_encode($response);
Yii::app()->end();
}
<?php
include 'excel_reader.php';
// include the class
// creates an object instance of the class, and read the excel file data
$excel = new PhpExcelReader();
$excel->read('test.xls');
// Excel file data is stored in $sheets property, an Array of worksheets
/*
The data is stored in 'cells' and the meta-data is stored in an array called 'cellsInfo'
Example (firt_sheet - index 0, second_sheet - index 1, ...):
$sheets[0] --> 'cells' --> row --> column --> Interpreted value
--> 'cellsInfo' --> row --> column --> 'type' (Can be 'date', 'number', or 'unknown')
--> 'raw' (The raw data that Excel stores for that data cell)
*/
// this function creates and returns a HTML table with excel rows and columns data
// Parameter - array with excel worksheet data
function sheetData($sheet)
{
$re = '<table>';
// starts html table
$x = 1;
while ($x <= $sheet['numRows']) {
$re .= "<tr>\n";
$y = 1;
while ($y <= $sheet['numCols']) {
$cell = isset($sheet['cells'][$x][$y]) ? $sheet['cells'][$x][$y] : '';
$re .= " <td>{$cell}</td>\n";
$y++;
<?php
include 'excel_reader.php';
// include the class
$excel = new PhpExcelReader();
// creates object instance of the class
$excel->read('iCleanData.xls');
// reads and stores the excel file data
function getData($sheet)
{
$users[$sheet['numCols'] - 1][3] = array();
$user_task_counts[$sheet['numCols'] - 1][$sheet['numRows'] - 3] = array();
$x = 1;
while ($x <= $sheet['numRows']) {
$y = 1;
while ($y <= $sheet['numCols']) {
if ($x == 1) {
if ($y <= 3) {
$titles[] = $sheet['cells'][$x][$y];
} else {
$tasks[] = $sheet['cells'][$x][$y];
}
} else {
if ($y <= 3) {
$users[$x - 2][$y - 1] = $sheet['cells'][$x][$y];
} else {
$user_task_counts[$x - 2][$y - 4] = $sheet['cells'][$x][$y];
}
}
$y++;
}
// A complex example that shows excel worksheets data appropiate to excel file
$excel_file = "test.xls";
$excel_file = 'branch_import_8.xls';
$sheet_data = '';
// to store html tables with excel data, added in page
$table_output = array();
// store tables with worksheets data
$max_rows = 0;
// USE 0 for no max
$max_cols = 8;
// USE 0 for no max
$force_nobr = 0;
// USE 1 to Force the info in cells Not to wrap unless stated explicitly (newline)
require_once 'excel_reader.php';
// include the class
$excel = new PhpExcelReader();
$excel->{$excel}->setOutputEncoding('UTF-8');
// sets encoding UTF-8 for output data
$excel->read($excel_file);
// read excel file data
$nr_sheets = count($excel->sheets);
// gets the number of worksheets
// function used to add A, B, C, ... for columns (like in excel)
function make_alpha_from_numbers($number)
{
$numeric = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
if ($number < strlen($numeric)) {
return $numeric[$number];
} else {
$dev_by = floor($number / strlen($numeric));
return make_alpha_from_numbers($dev_by - 1) . make_alpha_from_numbers($number - $dev_by * strlen($numeric));
public function actionBrowse()
{
if ($_FILES['myfile']['name'] != "") {
$filename = explode(".", $_FILES['myfile']['name']);
$fname = current($filename);
$extension = end($filename);
$phonenumbers = array();
$allowedfileformats = array();
if (Contacts::model()->import_contacts_config()) {
$import_config = Contacts::model()->import_contacts_config();
if ($import_config['allowed_file_formats']) {
$allowedfileformats = $import_config['allowed_file_formats'];
}
}
if (!in_array($extension, $allowedfileformats)) {
$this->renderPartial('import/_error', array('error' => 1));
} else {
$datas = array();
if ($extension == "xls") {
//excel file
require_once '/../../../extensions/ExcelReader/excel_reader.php';
// include the class
$path = $_FILES['myfile']['tmp_name'];
// creates an object instance of the class, and read the excel file data
$excel = new PhpExcelReader();
$excel->read($path);
$nr_sheets = count($excel->sheets);
// gets the number of sheets
if ($nr_sheets > 0) {
// traverses the number of sheets and sets html table with each sheet data in $excel_data
$sheet = $excel->sheets[0];
$rows = $sheet['numRows'];
$cols = $sheet['numCols'];
$x = 1;
while ($x <= $rows) {
$y = 1;
while ($y <= $cols) {
$cell = isset($sheet['cells'][$x][$y]) ? $sheet['cells'][$x][$y] : '';
$datas[$x - 1][$y - 1] = $cell;
$y++;
}
$x++;
}
}
} else {
if ($extension == "csv") {
//csv file
$contents = file_get_contents($_FILES['myfile']['tmp_name']);
$datas = array_map("str_getcsv", preg_split('/\\r*\\n+|\\r+/', $contents));
}
}
$this->renderPartial('import/_step1', array('datas' => $datas));
}
} else {
$this->renderPartial('import/_error', array('error' => 0));
}
}
">Import into Database</a>
<?php
$excel_file = $_POST["file"];
$sheet_data = '';
// to store html tables with excel data, added in page
$table_output = array();
// store tables with worksheets data
$max_rows = 0;
// USE 0 for no max
$max_cols = 8;
// USE 0 for no max
$force_nobr = 0;
// USE 1 to Force the info in cells Not to wrap unless stated explicitly (newline)
require_once 'excel_reader.php';
// include the class
$excel = new PhpExcelReader();
$excel->setOutputEncoding('UTF-8');
// sets encoding UTF-8 for output data
$excel->read($excel_file);
// read excel file data
$nr_sheets = count($excel->sheets);
// gets the number of worksheets
// function used to add A, B, C, ... for columns (like in excel)
function make_alpha_from_numbers($number)
{
$numeric = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
if ($number < strlen($numeric)) {
return $numeric[$number];
} else {
$dev_by = floor($number / strlen($numeric));
return make_alpha_from_numbers($dev_by - 1) . make_alpha_from_numbers($number - $dev_by * strlen($numeric));
$postValues = $_POST;
$target_dir = "customer_excel/";
$filename = explode(".", basename($_FILES["fileToUpload"]["name"]));
$newfilename = time() . '.' . end($filename);
$target_file = $target_dir . $newfilename;
$uploadOk = 1;
// Check if image file is a actual image or fake image
if (move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target_file)) {
echo "The file has been uploaded.";
} else {
echo $_FILES["fileToUpload"]["name"] . " Upload Error";
}
//$conn = mysql_connect("localhost","root","rootwdp") or die("ERROR::Not connected to SERVER");
//$db = mysql_select_db("sareees_db",$conn) or die("ERROR::Not connected to DATABASE");
require_once 'excel_reader/excel_reader.php';
$excel = new PhpExcelReader();
$excel->read('customer_excel/' . $newfilename);
$excel_data = $excel->sheets;
echo "<pre>";
print_r($excel_data);
echo "</pre>";
$count = $excel_data[0]['numRows'];
$customer_data = array(2 => 'email', 6 => 'firstname', 7 => 'lastname', 8 => 'passwordhash');
$billing_address = array(9 => "billing_firstname", 11 => "billing_lastname", 13 => "billing_street_full", 27 => "billing_company", 25 => "billing_postcode", 22 => "billing_city", 23 => "billing_region", 24 => "billing_country");
$shipping_address = array(30 => "shipping_firstname", 32 => "shipping_lastname", 34 => "shipping_street_full", 48 => "shipping_company", 46 => "shipping_postcode", 43 => "shipping_city", 44 => "shipping_region", 45 => "shipping_country");
$j = 0;
for ($i = 2; $i <= $count; $i++) {
foreach ($customer_data as $key => $value) {
if ($excel_data[0]['cells'][$i][$key] !== '' || array_key_exists($key, $excel_data[0]['cells'][$i])) {
$datas[$j][$value] = $excel_data[0]['cells'][$i][$key];
}
<?php
include 'excel_reader.php';
// include the class
$excel_file = "test.xls";
$excel_file = 'branch_import_8.xls';
// creates an object instance of the class, and read the excel file data
$excel = new PhpExcelReader();
$excel->read($excel_file);
// Excel file data is stored in $sheets property, an Array of worksheets
/*
The data is stored in 'cells' and the meta-data is stored in an array called 'cellsInfo'
Example (firt_sheet - index 0, second_sheet - index 1, ...):
$sheets[0] --> 'cells' --> row --> column --> Interpreted value
--> 'cellsInfo' --> row --> column --> 'type' (Can be 'date', 'number', or 'unknown')
--> 'raw' (The raw data that Excel stores for that data cell)
*/
// this function creates and returns a HTML table with excel rows and columns data
// Parameter - array with excel worksheet data
echo '<pre>';
print_r($excel);
echo '</pre>';
function sheetData($sheet)
{
$re = '<table>';
// starts html table
$x = 1;
while ($x <= $sheet['numRows']) {
$re .= "<tr>\n";
<?php
include 'excel_reader.php';
// include the class
// creates an object instance of the class, and read the excel file data
$excel = new PhpExcelReader();
$excel->read('uploads/wishlist.xls');
// Excel file data is stored in $sheets property, an Array of worksheets
/*
The data is stored in 'cells' and the meta-data is stored in an array called 'cellsInfo'
Example (firt_sheet - index 0, second_sheet - index 1, ...):
$sheets[0] --> 'cells' --> row --> column --> Interpreted value
--> 'cellsInfo' --> row --> column --> 'type' (Can be 'date', 'number', or 'unknown')
--> 'raw' (The raw data that Excel stores for that data cell)
*/
// this function creates and returns a HTML table with excel rows and columns data
// Parameter - array with excel worksheet data
function sheetData($sheet)
{
$re = '<table>';
// starts html table
$x = 1;
while ($x <= $sheet['numRows']) {
$re .= "<tr>\n";
$y = 1;
while ($y <= $sheet['numCols']) {
$cell = isset($sheet['cells'][$x][$y]) ? $sheet['cells'][$x][$y] : '';
$re .= " <td>{$cell}</td>\n";
$y++;
<?php
require_once '../app/Mage.php';
Mage::app();
$conn = mysql_connect("localhost", "root", "rootwdp") or die("ERROR::Not connected to SERVER");
$db = mysql_select_db("sareees_db", $conn) or die("ERROR::Not connected to DATABASE");
require_once 'excel_reader/excel_reader.php';
$excel = new PhpExcelReader();
$excel->read('customer_excel/products.xls');
$excel_data = $excel->sheets;
/* echo "<pre>";
print_r($excel_data);
echo "</pre>"; */
foreach ($excel_data[0]['cells'] as $sareez_colors) {
//$inserted_colors = array();
$inserted_data = mysql_query("SELECT DISTINCT(a.value) from eav_attribute_option_value a INNER JOIN eav_attribute_option b ON a.option_id = b.option_id WHERE b.attribute_id = 182");
while ($colors = mysql_fetch_assoc($inserted_data)) {
$inserted_colors[] = $colors['value'];
}
if (!strrchr($sareez_colors[1], "and")) {
$split_color = $sareez_colors[1];
if (!in_array(trim($split_color), $inserted_colors)) {
$arg_attribute = 'color';
$arg_value = $split_color;
$attr_model = Mage::getModel('catalog/resource_eav_attribute');
$attr = $attr_model->loadByCode('catalog_product', $arg_attribute);
$attr_id = $attr->getAttributeId();
$option['attribute_id'] = $attr_id;
$option['value']['any_option_name'][0] = trim($arg_value);
$option['value']['any_option_name'][1] = trim($arg_value);
$setup = new Mage_Eav_Model_Entity_Setup('core_setup');
<?php
include 'excel_reader.php';
// include the class
// creates an object instance of the class, and read the excel file data
$excel = new PhpExcelReader();
$excel->read('../document/books.xls');
// Excel file data is stored in $sheets property, an Array of worksheets
/*
The data is stored in 'cells' and the meta-data is stored in an array called 'cellsInfo'
Example (firt_sheet - index 0, second_sheet - index 1, ...):
$sheets[0] --> 'cells' --> row --> column --> Interpreted value
--> 'cellsInfo' --> row --> column --> 'type' (Can be 'date', 'number', or 'unknown')
--> 'raw' (The raw data that Excel stores for that data cell)
*
*
* ALTER TABLE `classified_info` ADD `created_at` TIMESTAMP NULL ,
ADD `modified_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
*/
// this function creates and returns a HTML table with excel rows and columns data
// Parameter - array with excel worksheet data
function sheetData($sheet)
{
$re = htmlentities('<table id="example" class="table table-striped table-bordered" cellspacing="0" width="100%">') . '<br>';
// starts html table
$x = 1;
<?php
if ($_REQUEST['submit']) {
$postValues = $_POST;
$target_dir = "order_excel/";
$filename = explode(".", basename($_FILES["fileToUpload"]["name"]));
$newfilename = time() . '.' . end($filename);
$target_file = $target_dir . $newfilename;
$uploadOk = 1;
// Check if image file is a actual image or fake image
if (move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target_file)) {
echo "The file has been uploaded.";
} else {
echo $_FILES["fileToUpload"]["name"] . " Upload Error";
}
require_once 'excel_reader/excel_reader.php';
$excel = new PhpExcelReader();
$excel->read('order_excel/' . $newfilename);
//$excel->read('order_excel/1431079053.xls');
$excel_data = $excel->sheets;
$count = count($excel_data[0]['cells']);
echo "<pre>";
print_r($excel_data);
echo "</pre>";
//$countorder = 1;
$orders = array();
$j = 0;
for ($i = 2; $i <= 5; $i++) {
$order = Mage::getModel('sales/order')->loadByIncrementId($excel_data[0]['cells'][$i][1]);
$email = $order->getCustomerEmail();
$customer_name = $order->getCustomerName();
$customer_id = $order->getCustomerId();
<?php
include 'excel_reader.php';
include '../database.php';
$excel = new PhpExcelReader();
$excel->read($_FILES["xls"]['tmp_name']);
$sheet = $excel->sheets[0];
class ExcelToDatabase
{
private $excel_column;
private $database_column;
private $table;
private $type;
private $result;
public function __construct($excel_column, $database_column, $type, $table)
{
$this->excel_column = $excel_column;
$this->database_column = $database_column;
$this->table = $table;
$this->type = $type;
}
public function cellData($sht)
{
$sheet = $sht;
$x = 1;
while ($x <= $sheet['numRows']) {
$column_data = isset($sheet['cells'][$x][$this->excel_column]) ? $sheet['cells'][$x][$this->excel_column] : '';
$this->result .= $column_data . ",";
$x++;
}
return $this->result;
$query = "INSERT INTO T_PROJECTS (`PROJ_ID`, `PROJ_NAME`, `PROJ_DESC`, `PROJ_VISIT_DATE`, `PROJ_DUE_DATE`, `PROJ_WISH_COUNT`, `PROJ_WISH_GRANT`, `PROJ_WL_URL`, `PROJ_STATUS`) VALUES \n\t\t(NULL, '" . $proj_name . "', '" . $proj_desc . "', '" . $proj_visit_date . "', '" . $proj_due_date . "', 0, 0, '.{$fileName}.', '" . $proj_set_active . "')";
$row = mysqli_query($con, $query);
/*-- Get inserted project id--*/
$query = "SELECT PROJ_ID, PROJ_VISIT_DATE FROM T_PROJECTS WHERE PROJ_NAME = '" . $proj_name . "'";
$result = mysqli_query($con, $query);
while ($row = mysqli_fetch_array($result)) {
if ($row['PROJ_VISIT_DATE'] == $proj_visit_date) {
echo "Found";
$newProjId = $row['PROJ_ID'];
break;
}
}
echo "New Proj Id:" . $newProjId . "<br/>";
/* -- Reading Excel and Saving Details to WL --*/
$wishList = [];
$excel = new PhpExcelReader();
$excel->read($fileName);
$sheet = $excel->sheets[0];
$x = 2;
while ($x <= $sheet['numRows']) {
$item = new WishItem();
$item->wishItemName = isset($sheet['cells'][$x][2]) ? "'" . $sheet['cells'][$x][2] . "'" : 'NULL';
$item->wishItemQty = isset($sheet['cells'][$x][3]) ? $sheet['cells'][$x][3] : 'NULL';
$item->wishItemOther = isset($sheet['cells'][$x][4]) ? "'" . $sheet['cells'][$x][4] . "'" : 'NULL';
$item->wishItemUrl = isset($sheet['cells'][$x][5]) ? "'" . $sheet['cells'][$x][5] . "'" : 'NULL';
$items = "(" . $item->wishItemName . "," . $newProjId . "," . $item->wishItemQty . "," . $item->wishItemOther . "," . $item->wishItemUrl . ")";
array_push($wishList, $items);
$x++;
}
$wishItemCount = sizeof($wishList);
/* -- Query Construction for insertion into WL table --*/
public function ReadExcel($url_file)
{
$excel = new PhpExcelReader();
$excel->read($url_file);
$arr_temp = $excel->sheets;
$arr_data = array();
$arr_data = $arr_temp[0]['cells'];
array_shift($arr_data);
array_shift($arr_data);
foreach ($arr_data as $key => $value) {
$this->insertDatVeCT($value);
}
return $arr_data;
}
