public function run_import($file_upload)
{
$file_path = './upload/files/excel/' . $file_upload['file_name'];
//load the excel library
$this->load->library('excel');
//read file from path
$inputFileType = PHPExcel_IOFactory::identify($file_path);
//die(print_r($inputFileType));
/** Create a new Reader of the type defined in $inputFileType **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($file_path);
//die(print_r($objPHPExcel));
//get only the Cell Collection
$cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection();
//extract to a PHP readable array format
foreach ($cell_collection as $cell) {
$column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn();
$row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
//header will/should be in row 1 only. of course this can be modified to suit your need.
if ($row == 1) {
$header[$row][$column] = $data_value;
} else {
$arr_data[$row][$column] = $data_value;
}
}
//send the data in an array format
$data['header'] = $header;
$data['values'] = $arr_data;
return $arr_data;
}
public static function excelParsing($fileExcel)
{
// $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_sqlite3; /* here i added */
// $cacheEnabled = \PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
// if (!$cacheEnabled) {
// echo "### WARNING - Sqlite3 not enabled ###" . PHP_EOL;
// }
$objPHPExcel = new \PHPExcel();
//$fileExcel = Yii::getAlias('@webroot/templates/operator.xls');
$inputFileType = \PHPExcel_IOFactory::identify($fileExcel);
$objReader = \PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
/** Load $inputFileName to a PHPExcel Object * */
$objPHPExcel = $objReader->load($fileExcel);
$total_sheets = $objPHPExcel->getSheetCount();
$allSheetName = $objPHPExcel->getSheetNames();
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
for ($row = 1; $row <= $highestRow; ++$row) {
for ($col = 0; $col < $highestColumnIndex; ++$col) {
$value = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
$arraydata[$row - 1][$col] = $value;
}
}
return $arraydata;
}
public function convertCsvToExcel($loadPath, $outputPath)
{
$objReader = \PHPExcel_IOFactory::createReader('CSV');
$objPHPExcel = $objReader->load($loadPath);
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save($outputPath);
}
function parseFile($file, $type)
{
$sql = new MySQL();
$sql->connect('127.0.0.1', 'root', 'root');
$objReader = PHPExcel_IOFactory::createReader($type);
$chunkSize = 200;
$i = 1;
$sql->clear('price_liga');
$r = array();
for ($startRow = 0; $startRow <= 5000; $startRow += $chunkSize + 1) {
$chunkFilter = new chunkReadFilter($startRow, $chunkSize);
$objReader->setReadFilter($chunkFilter);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($file);
$data = $objPHPExcel->getActiveSheet()->toArray();
foreach ($data as $k => $v) {
if ($data[$k][0] == '') {
unset($data[$k]);
} else {
$sql->insert('price_liga', array('id' => $i, 'cat_num' => $data[$k][0], 'brand' => ucwords(strtolower($data[$k][1])), 'article' => $data[$k][2], 'descr' => str_replace("'", "\\'", $data[$k][3]), 'model' => str_replace("'", "\\'", $data[$k][4]), 'size' => $data[$k][5], 'price' => $data[$k][6], 'amount' => $data[$k][8]), true);
$i++;
}
}
}
//print_r($r);
$sql->close();
return array('counter' => $i);
}
public function actionExcel($id)
{
$po = $this->loadModel($id);
$poItemCriteria = new CDbCriteria();
$poItemCriteria->with = array('material');
$poItemCriteria->compare('po_number', $id);
$poItemDataProvider = new CActiveDataProvider('PoItems', array('criteria' => $poItemCriteria, 'pagination' => false));
Yii::import('ext.phpexcel.XPHPExcel');
$objPHPExcel = XPHPExcel::createPHPExcel();
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load(Yii::app()->basePath . DIRECTORY_SEPARATOR . 'data' . DIRECTORY_SEPARATOR . "templates" . DIRECTORY_SEPARATOR . "PO_Template.xls");
$objPHPExcel->getProperties()->setCreator(Yii::app()->user->name)->setLastModifiedBy(Yii::app()->user->name)->setTitle("PO Order-" . $id);
//->setSubject("Office 2007 XLSX Test Document")
//->setDescription("Sales Order#")
//->setKeywords("office 2007 openxml php")
//->setCategory("Test result file");
// Add the data
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C2', $po->po_number)->setCellValue('C3', Yii::app()->dateFormatter->formatDateTime($po->maturity_date, "short", null))->setCellValue('C4', Yii::app()->dateFormatter->formatDateTime($po->created, "short", null))->setCellValue('C5', Yii::app()->dateFormatter->formatDateTime($po->updated, "short", null))->setCellValue('E2', $po->comp->name)->setCellValue('E3', $po->contact)->setCellValue('E4', $po->contact_telephone)->setCellValue('C6', $po->paymentTerm->description)->setCellValue('C7', $po->is_open ? "Open" : "Closed")->setCellValue('A8', "Comments:\r" . $po->comments);
$row = 13;
//$i = 1;
// Write the sale items now
$items = $poItemDataProvider->getData();
foreach ($items as $item) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("B" . $row, $item->material->cat->description . "-" . $item->material->description)->setCellValue("D" . $row, $item->qty)->setCellValue("E" . $row, $item->qty_units)->setCellValue("F" . $row, $item->unit_price)->setCellValue("G" . $row, $item->price_units)->setCellValue("H" . $row, $item->qty_recieved)->setCellValue("I" . $row, $item->qty_diff);
//$i++;
$row++;
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
// Redirect output to a client’s web browser
//header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=' . '"PurchaseOrder-' . $id . '.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
}
private function readExcel($key = 0)
{
error_reporting(E_ALL);
date_default_timezone_set('Asia/ShangHai');
require_once 'Classes/PHPExcel/IOFactory.php';
$reader = PHPExcel_IOFactory::createReader('Excel2007');
//设置格式
foreach ($GLOBALS['excelfile'] as $key => $value) {
$value = EXCEL_PATH . $value;
if (!file_exists($value)) {
exit("not found {$value}.\n");
}
$PHPExcel = $reader->load($value);
// 载入excel文件
$sheet = $PHPExcel->getSheet(0);
// 读取第一個工作表
$sheetName = $sheet->getTitle();
$allRow = $sheet->getHighestRow();
// 取得总行数
$allColumm = $sheet->getHighestColumn();
// 取得总列数
if ($key == 1) {
$this->outputLuaByMap($sheet, $sheetName, $allRow, $allColumm);
} else {
$this->outputLuaByList($sheet, $sheetName, $allRow, $allColumm);
}
$this->outputXml($sheet, $sheetName, $allRow, $allColumm, $key);
}
}
public function importExcel($fileID)
{
$filename = $this->storeExcel($fileID);
if ($filename != false) {
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = PHPExcel_IOFactory::load($this->path . $filename);
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
//取得行数
$highestColumn = $sheet->getHighestColumn();
// 取得列数
$result = array();
for ($j = 1; $j <= $highestRow; $j++) {
for ($k = 'A'; $k <= $highestColumn; $k++) {
$str = $objPHPExcel->getActiveSheet()->getCell("{$k}{$j}")->getValue();
//这个地方的字符处理有问题，当把array传递到createAccount_run页面的时候会出现乱码
// $str = iconv('gbk','utf-8',$str);
$result[$j][$k] = $str;
}
}
unlink($this->path . $filename);
//删除文件
return $result;
} else {
return false;
}
}
/**
* Write statistics to Excel table
*
* @param string $file File name
* @return void
*/
public function Write($file)
{
if (file_exists($file) && NULL !== $this->session->userdata('Write_statistics') && $this->session->userdata('Write_statistics')) {
$this->session->unset_userdata('Write_statistics');
$this->load->library('Excel');
// Open file
$fileType = 'Excel2007';
$objReader = PHPExcel_IOFactory::createReader($fileType);
$objPHPExcel = $objReader->load($file);
$objPHPExcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$dateFound = FALSE;
$sum = 0;
$currentDate = date('Y.m.d');
for ($row = 1; $row <= $highestRow; ++$row) {
$value = $objWorksheet->getCellByColumnAndRow(0, $row)->getValue();
$sum = max($sum, $objWorksheet->getCellByColumnAndRow(1, $row)->getValue());
if ($value == $currentDate) {
$dateFound = TRUE;
$objPHPExcel = $this->Update($objPHPExcel, $row, $sum, $currentDate);
}
}
if (!$dateFound) {
$objPHPExcel = $this->Update($objPHPExcel, $highestRow + 1, $sum, $currentDate);
}
// Write the file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $fileType);
$objWriter->save($file);
}
}
function excelToArray($file)
{
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($file);
//读取文件
$objWorksheet = $objPHPExcel->getActiveSheet(0);
//读取excel文件中的第一个工作表
$highestRow = $objWorksheet->getHighestRow();
//计算总行数
$highestColumn = $objWorksheet->getHighestColumn();
//取得列数中最大的字母。如(J)
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
//通过字母计算总列数
$excelData = array();
//存放读取的数据
for ($row = 2; $row <= $highestRow; ++$row) {
//从第二行开始读取数据
for ($col = 0; $col <= $highestColumnIndex; ++$col) {
//读取每行中的各列
//把读取的数据放入数组中
$excelData[$row - 2][] = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
}
}
return $excelData;
}
public function customerimport()
{
require_once APPPATH . 'third_party/phpexcel/Classes/PHPExcel/IOFactory.php';
$path = "C://inetpub/ftproot/retailersurvey/";
$filename = 'NAV Active Customer and Ship-to Address List.xlsx';
if (!file_exists($path . $filename)) {
exit("file not found" . EOL);
}
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load($path . $filename);
$sheetNames = $objPHPExcel->getSheetNames();
$sheetindex = '0';
$sheet = $objPHPExcel->getSheet($sheetindex);
$startRow = 2;
$highestRow = $sheet->getHighestRow();
$comma = "";
$customeridlist = '';
$data = array();
for ($row = $startRow; $row <= $highestRow; $row++) {
if (trim($sheet->getCell('A' . $row)->getValue()) != '') {
$temp = $this->cronjob_model->updatecustomer(trim($sheet->getCell('A' . $row)->getValue()), trim($sheet->getCell('B' . $row)->getValue()));
if (!$temp) {
} else {
$customeridlist .= $comma . $temp;
$comma = ', ';
}
//echo trim($sheet->getCell('A'.$row)->getValue())." | ".trim($sheet->getCell('B'.$row)->getValue())."\n";
}
}
$this->cronjob_model->updatecustomerlist($customeridlist);
}
public function index()
{
//load library excel
$this->load->library('excel');
//Here i used microsoft excel 2007
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
//Set to read only
$objReader->setReadDataOnly(true);
//Load excel file
$objPHPExcel = $objReader->load('assets/uploads/files/data.xlsx');
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
//load model
$this->load->model('excelModel');
//loop from first data untill last data
for ($i = 2; $i <= 4; $i++) {
$n_nota = $objWorksheet->getCellByColumnAndRow(0, $i)->getValue();
$nit_cc = $objWorksheet->getCellByColumnAndRow(1, $i)->getValue();
$nombre = $objWorksheet->getCellByColumnAndRow(2, $i)->getValue();
$direccion = $objWorksheet->getCellByColumnAndRow(3, $i)->getValue();
$barrio = $objWorksheet->getCellByColumnAndRow(4, $i)->getValue();
$telefono = $objWorksheet->getCellByColumnAndRow(5, $i)->getValue();
$descripcion = $objWorksheet->getCellByColumnAndRow(6, $i)->getValue();
$cantidad = $objWorksheet->getCellByColumnAndRow(7, $i)->getValue();
$fecha = $objWorksheet->getCellByColumnAndRow(8, $i)->getValue();
$hora = $objWorksheet->getCellByColumnAndRow(9, $i)->getValue();
$data_user = array('n_nota' => $n_nota, 'nit_cc' => $nit_cc, 'nombre' => $nombre, 'direccion' => $direccion, 'barrio' => $barrio, 'telefono' => $telefono, 'descripcion' => $descripcion, 'cantidad' => $cantidad, 'fecha' => $fecha, 'hora_servicio' => $hora, 'cod_zona' => $i, 'cod_estado' => 2);
$this->excelModel->add_data($data_user);
}
echo 'listo';
}
function insertarExcel($array)
{
$uploadOk = 1;
$time = time();
$fecha = date("Y-m-d", $time);
$target_dir = "../documents/";
$target_file = $target_dir . basename($_FILES["archivoExcel"]["name"]);
move_uploaded_file($array["archivoExcel"]["tmp_name"], $target_file);
set_include_path(get_include_path() . PATH_SEPARATOR . '../complements/PHPExcel-1.8/Classes/');
$inputFileType = 'Excel2007';
include 'PHPExcel/IOFactory.php';
$inputFileName = $target_file;
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
require_once "../db/conexiones.php";
$consulta = new Conexion();
foreach ($sheetData as $datos) {
$nombreSinAcentos = sanear_string($datos['B']);
$nombre = strtoupper(trim($nombreSinAcentos));
$datosEmpleado = $consulta->Conectar("postgres", "SELECT * FROM userinfo WHERE UPPER(name)='" . $nombre . "'");
if ($datosEmpleado) {
$sqlInsert = $this->invoco->Conectar("postgres", "INSERT INTO horario_personal (user_id, banda_id, fecha) VALUES (" . $datosEmpleado[0]['userid'] . "," . $datos['C'] . ", '" . $fecha . "')");
}
}
return "Se insertaron los datos Exitosamente!";
}
public function __construct($filename = null, $sheet = 0)
{
$this->filename = $filename;
if (file_exists($filename)) {
/* on charge le contenu du fichier dans l'objet phpExcel */
$inputFileType = PHPExcel_IOFactory::identify($filename);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$this->phpExcel = $objReader->load($filename);
/* on implémente dans le tableau */
// Get worksheet dimensions
$sheet = $this->phpExcel->getSheet($sheet);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
// Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++) {
// Read a row of data into an array
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
$this->container[] = $rowData[0];
}
} else {
$this->phpExcel = new PHPExcel();
$this->phpExcel->setActiveSheetIndex($sheet);
}
parent::__construct($this->container);
}
public function actionImport()
{
$field = ['fileImport' => 'File Import'];
$modelImport = DynamicModel::validateData($field, [[['fileImport'], 'required'], [['fileImport'], 'file', 'extensions' => 'xls,xlsx', 'maxSize' => 1024 * 1024]]);
if (Yii::$app->request->post()) {
$modelImport->fileImport = \yii\web\UploadedFile::getInstance($modelImport, 'fileImport');
if ($modelImport->fileImport && $modelImport->validate()) {
$inputFileType = \PHPExcel_IOFactory::identify($modelImport->fileImport->tempName);
$objReader = \PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($modelImport->fileImport->tempName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$baseRow = 2;
while (!empty($sheetData[$baseRow]['A'])) {
$model = new Mahasiswa();
$model->nama = (string) $sheetData[$baseRow]['B'];
$model->nim = (string) $sheetData[$baseRow]['C'];
$model->save();
//die(print_r($model->errors));
$baseRow++;
}
Yii::$app->getSession()->setFlash('success', 'Success');
} else {
Yii::$app->getSession()->setFlash('error', 'Error');
}
}
return $this->redirect(['index']);
}
public function import_dt($inputFileName)
{
try {
//$inputFileType = 'Excel2007';
$inputFileType = 'Excel5';
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
$data = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$result = '';
$session = new Zend_Session_Namespace('import_dt');
$deTais = array();
$thanhViens = array();
$count = -1;
for ($row = 10; $row <= count($data); $row++) {
//if cell[$row,B] là đề tài mới
if (!empty($data[$row]['B'])) {
//luu de tai
$cap_quan_ly = Default_Model_Functions::convert_vi_to_en(strtolower(trim($data[$row]['M'])));
$deTais[] = array('ten' => $data[$row]['B'], 'linh_vuc' => trim($data[$row]['C']), 'thoi_gian_bat_dau' => trim($data[$row]['K']), 'thoi_gian_hoan_thanh' => trim($data[$row]['L']), 'cap_quan_ly' => $cap_quan_ly, 'kinh_phi' => trim($data[$row]['N']));
//luu chu nhiem
$count++;
$thanhViens[$count][] = array('ma_giang_vien' => trim($data[$row]['E']), 'ho_ten' => preg_replace('/\\s+/u', ' ', trim($data[$row]['F'])), 'hoc_vi' => trim($data[$row]['G']), 'ghi_chu' => trim($data[$row]['H']), 'nhiem_vu' => '1', 'email' => trim($data[$row]['I']), 'so_dien_thoai' => trim($data[$row]['J']));
} else {
//luu thanh vien neu co
if (!empty($data[$row]['F'])) {
$thanhViens[$count][] = array('ma_giang_vien' => trim($data[$row]['E']), 'ho_ten' => preg_replace('/\\s+/u', ' ', trim($data[$row]['F'])), 'hoc_vi' => trim($data[$row]['G']), 'ghi_chu' => trim($data[$row]['H']), 'nhiem_vu' => '0', 'email' => trim($data[$row]['I']), 'so_dien_thoai' => trim($data[$row]['J']));
}
}
}
$session->deTais = $deTais;
$session->thanhViens = $thanhViens;
} catch (Zend_Exception $ex) {
throw $ex;
}
}
/**
* {@inheritDoc}
*/
public function read()
{
$inputFileName = $this->filename;
try {
$inputFileType = \PHPExcel_IOFactory::identify($inputFileName);
$objReader = \PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
} catch (\Exception $e) {
die(sprintf('Error loading file "%s": %s', pathinfo($inputFileName, PATHINFO_BASENAME), $e->getMessage()));
}
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
if (0 !== $this->offset) {
//@todo implement
throw new \InvalidArgumentException('Offset is not yet supported');
}
$properties = [];
for ($rowIndex = 1; $rowIndex <= $highestRow; $rowIndex++) {
$values = $sheet->rangeToArray('A' . $rowIndex . ':' . $highestColumn . $rowIndex, null, null, false);
$values = $values[0];
if ($this->headerDetector->isHeader($rowIndex, $values)) {
$properties = $this->describeProperties($values);
continue;
}
if ($rowIndex < $this->offset) {
continue;
}
if (!count($properties)) {
continue;
}
$this->processValues($properties, $values);
}
}
public function run($args)
{
Yii::import('application.components.PHPExcel.PHPExcel.PHPExcel_IOFactory');
Yii::import('application.modules.store.models');
$inputFileName = Yii::getPathOfAlias('application.components.spreadsheetReader.translates') . '/' . 'cargotogo_products.xlsx';
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load($inputFileName);
$db = \Yii::app()->db;
$max_id = $db->createCommand()->select('MAX(id)')->from('site_store_product')->queryScalar();
$row = 1;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Наименование')->setCellValue('B1', 'Ссылка')->setCellValue('C1', 'Регион')->setCellValue('D1', 'Название подраздела')->setCellValue('E1', 'Ссылка на подраздел ')->setCellValue('F1', 'Название раздела (верхний уровень)')->setCellValue('G1', 'Название раздела (второй уровень)')->setCellValue('H1', 'Ссылка на раздел (второй уровень)');
$row++;
$data = [];
for ($i = 1; $i <= $max_id; $i++) {
$db->setActive(true);
$product = $db->createCommand()->select('id, name, slug, category_id')->from('site_store_product')->where('id=' . $i)->queryRow();
if (!empty($product)) {
echo $product['id'] . "\n";
$region = $db->createCommand()->select('value')->from('site_store_product_attribute_eav')->where('product_id=' . $product['id'] . ' AND attribute="adres_name"')->queryRow();
if (!empty($product['category_id'])) {
$category = $db->createCommand()->select('id, slug, parent_id, name_ru')->from('site_store_category')->where('id=' . $product['category_id'])->queryRow();
}
$data = ['name' => $product['name'], 'link' => 'http://cargotogo.com/store/show/' . $product['slug'], 'region' => isset($region) ? $region['value'] : '', 'sub_category_name' => isset($category) ? $category['name_ru'] : '', 'sub_category_link' => isset($category) ? 'http://cargotogo.com/store/' . $this->getParentStoreCategoriesLink($category, $db) : '', 'main_category_name' => isset($category) ? $this->getMainStoreCategoryName($category, $db) : '', 'second_category_name' => isset($category) ? $this->getSecondStoreCategoryName($category, $db) : '', 'second_category_link' => isset($category) ? 'http://cargotogo.com/store/' . $this->getSecondStoreCategoryLink($category, $db) : ''];
$objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $data['name'])->setCellValue('B' . $row, $data['link'])->setCellValue('C' . $row, $data['region'])->setCellValue('D' . $row, $data['sub_category_name'])->setCellValue('E' . $row, $data['sub_category_link'])->setCellValue('F' . $row, $data['main_category_name'])->setCellValue('G' . $row, $data['second_category_name'])->setCellValue('H' . $row, $data['second_category_link']);
$row++;
}
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save($inputFileName);
}
/**
* (non-PHPdoc)
* @see library/Oara/Network/Oara_Network_Publisher_Interface#getMerchantList()
*/
public function getMerchantList()
{
$merchants = array();
$params = array(new Oara_Curl_Parameter('cmdDownload', 'Download All Active Merchants'), new Oara_Curl_Parameter('strRelationStatus', 'active'));
$urls = array();
$urls[] = new Oara_Curl_Request($this->_domain . '/affiliate/merchants.php', $params);
$result = $this->_client->post($urls);
$folder = realpath(dirname(__FILE__)) . '/../../data/pdf/';
$my_file = $folder . mt_rand() . '.xls';
$handle = fopen($my_file, 'w') or die('Cannot open file: ' . $my_file);
$data = $result[0];
fwrite($handle, $data);
fclose($handle);
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($my_file);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
for ($row = 2; $row <= $highestRow; ++$row) {
$obj = array();
$obj['cid'] = $objWorksheet->getCellByColumnAndRow(0, $row)->getValue();
$obj['name'] = $objWorksheet->getCellByColumnAndRow(1, $row)->getValue();
$merchants[] = $obj;
}
unlink($my_file);
return $merchants;
}
/**
* 添加Excel中的信息到MySql中
* @param [type] $modelid [description]
*/
public function addMessage($modelid)
{
$temp_array = array();
$path = substr(__FILE__, 0, strrpos(__FILE__, DIRECTORY_SEPARATOR));
require_once $path . '/classes/PHPExcel/IOFactory.php';
require_once $path . '/classes/PHPExcel/Reader/Excel2007.php';
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objExcel = $objReader->load($this->file);
$sheet = $objExcel->getSheet();
$highesRow = $sheet->getHighestRow();
$highesColumb = $sheet->getHighestColumn();
//获得表格里的数据
$this->inser_data['inputtime'] = $this->inser_data['updatetime'] = time();
$this->inser_data['status'] = 99;
for ($i = 2; $i < $highesRow; $i++) {
$str = $this->sql;
for ($j = 'A'; $j < $highesColumb; $j++) {
if ($objExcel->getActiveSheet()->getCell("{$j}{$i}")->getValue() != '') {
$str .= ",'" . iconv('UTF-8', 'UTF-8', $objExcel->getActiveSheet()->getCell("{$j}{$i}")->getValue()) . "'";
} else {
break;
}
}
$str .= ')';
echo $str;
exit;
if ($this->db->query($str)) {
$id = $this->db->insert_id();
$this->db->query('insert into ' . $this->db->table_name . '_data' . '(id)value(' . $id . ')');
}
}
}
public function ukAmazonFees()
{
$this->layout = '';
$this->autoRender = false;
$this->loadModel('AmazonFee');
$this->loadModel('Location');
App::import('Vendor', 'PHPExcel/IOFactory');
$objPHPExcel = new PHPExcel();
$objReader = PHPExcel_IOFactory::createReader('CSV');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load('files/uk_amazon_fees.csv');
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
$lastRow = $objPHPExcel->getActiveSheet()->getHighestRow();
$colString = $highestColumn = $objPHPExcel->getActiveSheet()->getHighestColumn();
$colNumber = PHPExcel_Cell::columnIndexFromString($colString);
for ($i = 2; $i <= $lastRow; $i++) {
$this->request->data['category'] = $objWorksheet->getCellByColumnAndRow(0, $i)->getValue();
$this->request->data['referral_fee'] = $objWorksheet->getCellByColumnAndRow(1, $i)->getValue();
$this->request->data['app_min_referral_fee'] = $objWorksheet->getCellByColumnAndRow(2, $i)->getValue();
$country = $this->Location->find('first', array('conditions' => array('Location.county_name' => $objWorksheet->getCellByColumnAndRow(3, $i)->getValue())));
$this->request->data['country'] = $country['Location']['id'];
$this->request->data['platform'] = $objWorksheet->getCellByColumnAndRow(4, $i)->getValue();
$this->AmazonFee->create();
$this->AmazonFee->save($this->request->data);
}
}
public function test()
{
$inputFileName = 'application/xls/Catalogo_clientes.xlsx';
// Read your Excel workbook
try {
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
// var_dump($inputFileType);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
//var_dump($objPHPExcel);
} catch (Exception $e) {
die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
}
// Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
// Loop through each row of the worksheet in turn
for ($row = 4; $row <= $highestRow; $row++) {
// Read a row of data into an array
$rowData[] = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
// Insert row data array into your database of choice here
}
return $rowData;
}
public function procesarExcel()
{
$archivo = $this->obtenerArchivo();
$inputFileType = 'Excel5';
$fileName = './uploads/' . $archivo['file_name'];
//$fileName = './uploads/cajeros.xls';
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($fileName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$CI =& get_instance();
$CI->load->model('cajero_class', 'cajero', TRUE);
for ($i = 2; $i < count($sheetData); $i++) {
$CI->cajero->id = NULL;
$CI->cajero->banco_id = $sheetData[$i]['A'];
$CI->cajero->nombre = $sheetData[$i]['B'];
$CI->cajero->direccion = $sheetData[$i]['C'];
$CI->cajero->horario = $sheetData[$i]['D'];
if (!isset($sheetData[$i]['F'])) {
$valores = split(",", $sheetData[$i]['E']);
$CI->cajero->latitud = $valores[0];
$CI->cajero->longitud = $valores[1];
} else {
$CI->cajero->latitud = $sheetData[$i]['E'];
$CI->cajero->longitud = $sheetData[$i]['F'];
}
$CI->cajero->estado = 'AC';
$CI->cajero->guardar();
}
}
function parseFile($file, $type)
{
$sql = new MySQL();
$sql->connect('127.0.0.1', 'root', 'root');
$objReader = PHPExcel_IOFactory::createReader($type);
$chunkSize = 200;
$i = 1;
$sql->clear('price_v8');
for ($startRow = 0; $startRow <= 5000; $startRow += $chunkSize + 1) {
$chunkFilter = new chunkReadFilter($startRow, $chunkSize);
$objReader->setReadFilter($chunkFilter);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($file);
$data = $objPHPExcel->getActiveSheet()->toArray();
foreach ($data as $k => $v) {
if (trim($data[$k][0]) == 'Артикул' || $data[$k][3] == '' || strstr($data[$k][3], 'камера') || $data[$k][7] == '') {
unset($data[$k]);
} else {
$descr = str_replace('Ш', 'xSTUDEDx', trim($data[$k][3]));
$descr = preg_replace('/[а-яА-Я]/', '', $descr);
$sql->insert('price_v8', array('id' => $i, 'article' => trim($data[$k][0]), 'descr' => str_replace("'", "\\'", $descr), 'cat_num' => trim($data[$k][6]), 'season' => trim($data[$k][7]), 'price' => trim($data[$k][9]), 'amount' => trim(preg_replace('/[а-яА-Яa-zA-Z]{0,}/', '', $data[$k][10]))), true);
$i++;
}
}
}
$sql->close();
return array('counter' => $i);
}
/**
* [excel2Array description]
*
* @param [type] $filepath [description]
* @param array $result [description]
* @return [type] [description]
*/
public function excel2Array($filepath = null, $result = array())
{
if (!file_exists($filepath)) {
App::abort('500', "Error loading file " . $filepath . ": File does not exist");
}
// Read your Excel workbook
try {
$inputFileType = \PHPExcel_IOFactory::identify($filepath);
$objReader = \PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($filepath);
} catch (Exception $e) {
App::abort('500', "Error loading file " . pathinfo($filepath, PATHINFO_BASENAME) . ": " . $e->getMessage());
}
$i = 0;
// Loop through each worksheet
foreach ($objPHPExcel->getWorksheetIterator() as $sheet) {
// Get worksheet dimensions
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
// Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++) {
// Read a row of data into an array
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
$result[$i][] = $rowData;
}
$i++;
}
return $result;
}
public function post_parse_payments()
{
$config = array('path' => DOCROOT . 'uploads/csv', 'randomize' => true, 'ext_whitelist' => array('csv'));
Upload::process($config);
if (Upload::is_valid()) {
//Upload::save();
$file = Upload::get_files();
$uploaded_file = $file[0]['file'];
Package::load("excel");
$excel = PHPExcel_IOFactory::createReader('CSV')->setDelimiter(',')->setEnclosure('"')->setLineEnding("\n")->setSheetIndex(0)->load($uploaded_file);
$objWorksheet = $excel->setActiveSheetIndex(0);
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
//read from file
for ($row = 1; $row <= $highestRow; ++$row) {
$file_data = array();
for ($col = 0; $col <= $highestColumnIndex; ++$col) {
$value = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
$file_data[$col] = trim($value);
}
$result[] = $file_data;
}
print_r($result);
} else {
print "Invalid uploads";
}
}
function read_sheet($file, $employ_array)
{
require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php';
$inputFileName = $file;
// Read your Excel workbook
try {
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
} catch (Exception $e) {
die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
}
$rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator();
$array_data = array();
$i = 1;
foreach ($rowIterator as $row) {
if ($i > sizeof($employ_array) + 2) {
break;
}
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
$rowIndex = $row->getRowIndex();
$array_data[$rowIndex] = array('A' => '', 'B' => '', 'C' => '', 'D' => '', 'E' => '');
foreach ($cellIterator as $cell) {
if ('A' == $cell->getColumn()) {
$array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
} else {
if ('B' == $cell->getColumn()) {
$array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
} else {
if ('C' == $cell->getColumn()) {
$array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
} else {
if ('D' == $cell->getColumn()) {
$array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
} else {
if ('E' == $cell->getColumn()) {
$array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
} else {
if ('F' == $cell->getColumn()) {
$array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
} else {
if ('G' == $cell->getColumn()) {
$array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
}
}
}
}
}
}
}
}
$i++;
}
unset($array_data[1]);
unset($array_data[2]);
echo '<pre>';
return $array_data;
}
function upload_facs_calibur_exp()
{
$file = $_FILES[upload];//has all info about uploaded files
//file properties
$file_name = $file['name'];
$file_tmp = $file['tmp_name'];
$file_size = $file['size'];
$file_error = $file['error'];
//file extension
$file_ext = explode(".", $file_name);
$file_ext = end($file_ext);
//allowed exp file types
$allowed = array('exp','EXP','xls','XLS','xlsx','XLSX');
$file_type_array = explode("(", $file_name);
$file_type_array = explode(").", end($file_type_array));
$file_type = current($file_type_array);
// get the files creation date and save it in an array
$file_date_time = date ("Y-m-d H:i:s", filemtime($file_tmp));
if(in_array($file_ext, $allowed)){
// Import uploaded file to Database
$excelReader = PHPExcel_IOFactory::createReader('Excel2007');
$excelReader->setReadDataOnly(true);
$objPHPExcel = PHPExcel_IOFactory::load($file_tmp);
// die;
//get only the Cell Collection
$cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection();
//extract to a PHP readable array format
foreach ($cell_collection as $cell) {
$column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn();
$row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
//header will/should be in row 1 only. of course this can be modified to suit your need.
if ($row == 1) {
$header[$row][$column] = $data_value;
} else {
$arr_data[$row][$column] = $data_value;
}
}
//send the data in an array format
$data['header'] = $header;
$data['values'] = $arr_data;
echo "<pre>";
print_r($arr_data); die();
}
else{
echo "Wrong file format. Upload again";
$this->load->view('facs_calibur_view');
}
}
public function read($filename, $encode, $file_type)
{
if (strtolower($file_type) == 'xls') {
vendor("Excel.PHPExcel.Reader.Excel5", LIB_PATH . '../Extend/Vendor');
$objReader = PHPExcel_IOFactory::createReader('Excel5');
} else {
if (strtolower($file_type) == 'xlsx') {
vendor("Excel.PHPExcel.Reader.Excel2007", LIB_PATH . '../Extend/Vendor');
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
}
}
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($filename);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$excelData = array();
for ($row = 1; $row <= $highestRow; $row++) {
for ($col = 0; $col < $highestColumnIndex; $col++) {
$excelData[$row][] = (string) $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
}
}
return $excelData;
}
public function uploadExcel($filename = '', $productID, $planID, $sprintID)
{
if ($_FILES['excelFile']['size'] == 0) {
return;
}
// uploadFile
$filePath = $this->getSavePath();
$fname = $this->setPathName(0, $file['extension']);
$uploadfile = $filePath . $fname;
$tmp_name = $_FILES['excelFile']["tmp_name"];
require_once 'PHPExcel.php';
require_once 'PHPExcel/IOFactory.php';
require_once 'PHPExcel/Reader/Excel2007.php';
$result = move_uploaded_file($_FILES['excelFile']["tmp_name"], $this->getSavePath() . $fname);
if ($result) {
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = PHPExcel_IOFactory::load($uploadfile);
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
for ($j = 2; $j <= $highestRow; $j++) {
$str = "";
for ($k = 'A'; $k <= $highestColumn; $k++) {
// $str .= iconv('utf-8', 'gbk', $objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue())."|";//读取单元格
$str .= $objPHPExcel->getActiveSheet()->getCell("{$k}{$j}")->getValue() . "|";
//读取单元格
}
$strs = explode("|", $str);
$bug = new stdclass();
$bug->product = $productID;
$bug->plan = $planID;
if ($sprintID) {
$bug->project = $sprintID;
}
$bug->title = $strs[0];
$bug->stage = $this->lang->importbugs->stageMap[$strs[1]];
$bug->type = $this->lang->importbugs->typeMap[$strs[2]];
$bug->severity = $strs[3];
$bug->steps = $strs[4];
$bug->openedBy = $this->app->user->account;
$this->dao->insert(TABLE_BUG)->data($bug)->exec();
$bugID = $this->dao->lastInsertID();
$action = new stdclass();
$action->objectType = 'bug';
$action->objectID = $bugID;
$action->product = $productID;
$action->project = $sprintID;
$action->actor = $this->app->user->account;
$action->action = 'opened';
$action->date = helper::now();
$action->read = 0;
$this->dao->insert(TABLE_ACTION)->data($action)->exec();
}
unlink($uploadfile);
$msg = $this->lang->importbugs->success;
} else {
$msg = $this->lang->importbugs->fail;
}
return $msg;
}
/**
* 读取 EXCEL
*/
public static function read($file)
{
self::checkFile($file);
$ext = self::getFileExt($file);
try {
if ($ext == self::EXCEL_TYPE_CSV) {
// CSV 格式要特别处理
$PHPReader = \PHPExcel_IOFactory::createReader(self::$fileConf[$ext])->setDelimiter(',')->setEnclosure('"')->setSheetIndex(0);
} else {
$PHPReader = \PHPExcel_IOFactory::createReader(self::$fileConf[$ext]);
}
$PHPReader->setReadDataOnly(true);
$PHPexcel = $PHPReader->load($file);
$workSheet = $PHPexcel->getActiveSheet();
// 获取行数
$highestRow = $workSheet->getHighestRow();
// 获取列数
$highestCol = $workSheet->getHighestColumn();
$highestColIndex = \PHPExcel_Cell::columnIndexFromString($highestCol);
$excelData = array();
for ($row = 1; $row <= $highestRow; $row++) {
for ($col = 0; $col < $highestColIndex; $col++) {
$excelData[$row][] = strval($workSheet->getCellByColumnAndRow($col, $row)->getValue());
}
}
return $excelData;
} catch (\Exception $e) {
return self::error($e->getMessage());
}
}
