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