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; }