Beispiel #1
3
 function create()
 {
     //load our new PHPExcel library
     $this->load->library('excel');
     //activate worksheet number 1
     $this->excel->setActiveSheetIndex(0);
     //name the worksheet
     $this->excel->getActiveSheet()->setTitle('test worksheet');
     //set cell A1 content with some text
     $this->excel->getActiveSheet()->setCellValue('A1', 'This is just some text value');
     //change the font size
     $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
     //make the font become bold
     $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
     //merge cell A1 until D1
     $this->excel->getActiveSheet()->mergeCells('A1:D1');
     //set aligment to center for that merged cell (A1 to D1)
     $this->excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
     $filename = 'just_some_random_name.xls';
     //save our workbook as this file name
     header('Content-Type: application/vnd.ms-excel');
     //mime type
     header('Content-Disposition: attachment;filename="' . $filename . '"');
     //tell browser what's the file name
     header('Cache-Control: max-age=0');
     //no cache
     //save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
     //if you want to save it as .XLSX Excel 2007 format
     $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
     //force user to download the Excel file without writing it to server's HD
     $objWriter->save('php://output');
 }
Beispiel #2
3
 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;
 }
function get_comments_xls($filename, $cons)
{
    require_once TEMPLATEPATH . '/app/PHPExcel.php';
    global $wpdb;
    $sql = "SELECT post_title,comment_ID,comment_author, comment_date_gmt, comment_content\n\t\t\tFROM {$wpdb->comments}\n\t\t\t\tLEFT OUTER JOIN {$wpdb->posts} ON ({$wpdb->comments}.comment_post_ID = {$wpdb->posts}.ID)\n\t\t\t\tINNER JOIN {$wpdb->term_relationships} as r1 ON ({$wpdb->posts}.ID = r1.object_id)\n\t\t\t\tINNER JOIN {$wpdb->term_taxonomy} as t1 ON (r1.term_taxonomy_id = t1.term_taxonomy_id)\n\t\t\tWHERE comment_approved = '1'\n\t\t\t\tAND comment_type = ''\n\t\t\t\tAND post_password = ''\n\t\t\t\tAND t1.taxonomy = 'category'\n\t\t\t\tAND t1.term_id = " . $cons . "\n\t\t\torder by comment_date_gmt";
    $qr = $wpdb->get_results($sql, ARRAY_N);
    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();
    // Set properties
    $objPHPExcel->getProperties()->setCreator("Consultator")->setLastModifiedBy("Consultator")->setTitle("Consultator")->setSubject("Consultator")->setDescription("Αρχείο Εξαγωγής Σχολίων")->setKeywords("Σχόλια")->setCategory("Αρχείο Σχολίων");
    // Add some data // Headers
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Άρθρο')->setCellValue('B1', 'Κωδικός Σχολίου')->setCellValue('C1', 'Σχολιαστής')->setCellValue('D1', 'Ημερομηνία Υποβολής')->setCellValue('E1', 'Σχόλιο');
    $objPHPExcel->getActiveSheet()->fromArray($qr, NULL, 'A2');
    // Rename sheet
    $objPHPExcel->getActiveSheet()->setTitle('Σχόλια Διαβούλευσης');
    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);
    // Redirect output to a client’s web browser (Excel5)
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="' . $filename . '.xls"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
    $objPHPExcel->disconnectWorksheets();
    unset($objPHPExcel);
    exit;
}
 /**
  * get excel contents
  * @return array
  * @link http://fun-program.net/phpexcel-read-excel
  * @link http://stackoverflow.com/questions/4562527/how-to-find-out-how-many-rows-and-columns-to-read-from-an-excel-file-with-phpexc
  * @link http://stackoverflow.com/questions/27708459/getting-cell-as-string-in-phpexcel-by-column-and-row
  */
 public function getContents()
 {
     if ($this->validate()) {
         $result = [];
         $reader = PHPExcel_IOFactory::createReaderForFile($this->file->tempName);
         $reader->setReadDataOnly(false);
         $excel = $reader->load($this->file->tempName);
         $sheet = $excel->getSheetByName($this->activeSheetName);
         if (!isset($sheet)) {
             throw new Exception("取得Excel資料,發生錯誤,請確認[{$this->activeSheetName}]標籤是否存在或合法(標籤名稱必須為英文)");
         }
         $rows = $sheet->getRowIterator();
         $rowIndex = 0;
         foreach ($rows as $row) {
             $cellIndex = 0;
             $cellIterator = $row->getCellIterator();
             $cellIterator->setIterateOnlyExistingCells(false);
             $result[++$rowIndex] = [];
             foreach ($cellIterator as $cell) {
                 $result[$rowIndex][++$cellIndex] = (string) $cell->getCalculatedValue();
             }
         }
         return $result;
         /*
         // 另一種簡潔寫法
         $reader = PHPExcel_IOFactory::load($this->file->tempName);
         return $reader->getActiveSheet()->toArray(null, true, true, true);
         */
     }
     throw new Exception('must be validate excel file.', 1);
 }
function import_Books($file)
{
    set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
    include 'PHPExcel/IOFactory.php';
    require_once 'private/LMS_Engine.php';
    $engine = new LMS_Engine();
    try {
        $objPHPExcel = PHPExcel_IOFactory::load($file);
        $Total_Sheet = $objPHPExcel->getSheetCount();
        for ($num = 0; $num < $Total_Sheet; $num++) {
            $Sheet = $objPHPExcel->getSheet($num)->toArray(null, true, true, true);
            $Row = count($Sheet);
            for ($pos = 3; $pos < $Row; $pos++) {
                $Title = trim($Sheet[$pos]['B']);
                $Author = trim($Sheet[$pos]['C']);
                $Publisher = trim($Sheet[$pos]['D']);
                $Pub_Year = trim($Sheet[$pos]['E']);
                $Pub_Add = trim($Sheet[$pos]['F']);
                $Call_ID = trim($Sheet[$pos]['G']);
                $Copy_Num = trim($Sheet[$pos]['H']);
                $Category = trim($Sheet[$pos]['I']);
                $Shelf_Store = trim($Sheet[$pos]['J']);
                $engine->add_new_book($Title, 1, $Author, $Publisher, $Pub_Year, $Pub_Add, $Call_ID, $Copy_Num, $Shelf_Store);
            }
        }
    } catch (Exception $e) {
        die('Error loading file "' . pathinfo($file, PATHINFO_BASENAME) . '": ' . $e->getMessage());
    }
}
Beispiel #6
0
 protected function execute(InputInterface $input, OutputInterface $output)
 {
     ini_set('memory_limit', -1);
     $output->writeln('--- vidal:user_specialty started');
     $container = $this->getContainer();
     $em = $container->get('doctrine')->getManager();
     $pdo = $em->getConnection();
     $filename = $this->getContainer()->get('kernel')->getRootDir() . '/User.orig.xlsx';
     $objPHPExcel = \PHPExcel_IOFactory::load($filename);
     //  Get worksheet dimensions
     $sheet = $objPHPExcel->getSheet(0);
     $highestRow = $sheet->getHighestRow();
     $highestColumn = $sheet->getHighestColumn();
     $stmt = $pdo->prepare('UPDATE user SET primarySpecialty_id = ?, secondarySpecialty_id = ? WHERE username = ?');
     $i = 0;
     $output->writeln('Total rows: ' . $highestRow);
     for ($row = 1; $row <= $highestRow; $row++) {
         $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
         if (isset($rowData[0]) && isset($rowData[0][6]) && isset($rowData[0][8]) && !empty($rowData[0][1]) && !empty($rowData[0][6])) {
             $email = $rowData[0][1];
             $primary = $this->getSpecialty($rowData[0][6]);
             $secondary = $this->getSpecialty($rowData[0][8]);
             $stmt->bindParam(1, $primary);
             $stmt->bindParam(2, $secondary);
             $stmt->bindParam(3, $email);
             $stmt->execute();
             $i++;
             if ($i && $i % 100 == 0) {
                 $output->writeln('... ' . $i);
             }
         }
     }
     $output->writeln("+++ vidal:user_specialty {$i} loaded!");
 }
/**
 * 
 * @param unknown_type $tipoArchivo
 */
function procesarArchivo($tipoArchivo)
{
    //$dbLink = getConnection();
    //ajustamos el maximo de tiempo de ejecucion a 10 minutos para la carga de los archivos
    ini_set("max_execution_time", 60 * 10);
    //limpiamos el archivo de errores para esta corrida
    initErrorFile();
    //leemos el archivo Excel en una estructura mas manejable
    $objPHPExcel = PHPExcel_IOFactory::load(getUploadedXLSFileToProcess());
    if ($tipoArchivo == 'clientes') {
        return insertarCliente($objPHPExcel);
    } else {
        if ($tipoArchivo == 'lineasVentasPaquetes') {
            return insertarLineaVentasPaquetesCredito($objPHPExcel);
        } else {
            if ($tipoArchivo == 'recibos') {
                return insertarRecibo($objPHPExcel);
            } else {
                if ($tipoArchivo == 'ventasPaquetes') {
                    return insertarVentasPaquetesCredito($objPHPExcel);
                }
            }
        }
    }
    //cerramos la conexion a la base de datos
    //mysql_close($dbLink);
    //eliminamos el archivo temporal
    $objPHPExcel->disconnectWorksheets();
    $objPHPExcel = null;
    unlink(getUploadedXLSFileToProcess());
}
Beispiel #8
0
 function dictionnaryExportAction()
 {
     $this->disableLayout();
     $this->disableView();
     $select = $this->_db->select();
     $select->from('Static_Texts')->order('ST_LangID');
     $objPHPExcel = new PHPExcel();
     $objPHPExcel->setActiveSheetIndex(0);
     $objPHPExcel->getActiveSheet()->setCellValue('A1', "Identifier");
     $objPHPExcel->getActiveSheet()->setCellValue('B1', "LangID");
     $objPHPExcel->getActiveSheet()->setCellValue('C1', "Value");
     $objPHPExcel->getActiveSheet()->setCellValue('D1', "Type");
     $items = $this->_db->fetchAll($select);
     $item_count = count($items);
     for ($i = 0; $i < $item_count; $i++) {
         $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $items[$i]['ST_Identifier']);
         $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $items[$i]['ST_LangID']);
         $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $items[$i]['ST_Value']);
         $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $items[$i]['ST_Type']);
     }
     header("Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
     header("Content-Disposition: attachment;filename=dictionnary.xlsx");
     // output the file
     $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
     $objWriter->save('php://output');
 }
Beispiel #9
0
 /**
  * Format a recordset
  *
  * @param Garp_Model $model
  * @param array $rowset
  * @return string
  */
 public function format(Garp_Model $model, array $rowset)
 {
     $phpexcel = new PHPExcel();
     PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
     // set metadata
     $props = $phpexcel->getProperties();
     if (Garp_Auth::getInstance()->isLoggedIn()) {
         $userData = Garp_Auth::getInstance()->getUserData();
         $bootstrap = Zend_Controller_Front::getInstance()->getParam('bootstrap');
         if ($bootstrap) {
             $view = $bootstrap->getResource('view');
             $userName = $view->fullName($userData);
             $props->setCreator($userName)->setLastModifiedBy($userName);
         }
     }
     $props->setTitle('Garp content export – ' . $model->getName());
     if (count($rowset)) {
         $this->_addContent($phpexcel, $model, $rowset);
     }
     /**
      * Hm, PHPExcel seems to only be able to write to a file (instead of returning
      * an XLS binary string). Therefore, we save a temporary file, read its contents
      * and return those, after which we unlink the temp file.
      */
     $tmpFileName = APPLICATION_PATH . '/data/logs/tmp.xls';
     $writer = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
     $writer->save($tmpFileName);
     $contents = file_get_contents($tmpFileName);
     unlink($tmpFileName);
     return $contents;
 }
Beispiel #10
0
 /**
  * Load excel file
  * @param  string $filename filename to be loaded
  */
 protected function loadFile($filename)
 {
     if (!file_exists($filename)) {
         throw new \Exception($filename . " Not Found!");
     }
     $this->phpExcel = \PHPExcel_IOFactory::load($filename);
 }
 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!";
 }
Beispiel #12
0
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);
}
 protected function footer($objPHPExcel, $start, $file_name, $format, $html_title)
 {
     $start++;
     $jemaat = get_jemaat_from_user_id(Yii::app()->user->getId());
     $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A{$start}:G{$start}")->setCellValue("A{$start}", "Dicetak oleh: " . $jemaat->real_name);
     $start++;
     $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A{$start}:G{$start}")->setCellValue("A{$start}", "Pada tanggal " . get_date_today('dd/MM/yyyy') . " jam " . get_time_now());
     ob_end_clean();
     ob_start();
     if ($format == 'excel') {
         header('Content-Type: application/vnd.ms-excel');
         header("Content-Disposition: attachment;filename={$file_name}.xls");
         header('Cache-Control: max-age=0');
         $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
         $objWriter->save('php://output');
     } else {
         $objPHPExcel->getActiveSheet()->setShowGridlines(false);
         $mPDF1 = Yii::app()->ePdf->mpdf();
         $mPDF1 = Yii::app()->ePdf->mpdf('', 'A4');
         $objWriter = new PHPExcel_Writer_HTML($objPHPExcel);
         $header = $objWriter->generateHTMLHeader(true);
         $header = str_replace("<body>", "<body onload='window.print();'>", $header);
         $header = str_replace("Untitled Spreadsheet", $html_title, $header);
         $html = $header . $objWriter->generateStyles(true) . $objWriter->generateSheetData() . $objWriter->generateHTMLFooter();
         if ($format == 'pdf') {
             $mPDF1->WriteHTML($html);
             $mPDF1->Output('MutasiKasDitangan.pdf', 'D');
         } else {
             echo $html;
         }
     }
 }
 protected function execute(InputInterface $input, OutputInterface $output)
 {
     $this->container = $this->getContainer();
     $this->em = $this->getContainer()->get('doctrine')->getManager();
     //$this->em->getConnection()->getConfiguration()->setSQLLogger(null);
     $this->formCSRF = $this->container->get('form.csrf_provider');
     $this->adminPool = $this->container->get('sonata.admin.pool');
     $this->user = $this->em->getRepository('ApplicationSonataUserBundle:User')->findOneBy(array('id' => $input->getArgument('user_id')));
     $this->translator = \AppKernel::getStaticContainer()->get('translator');
     $token = new UsernamePasswordToken($this->user, $this->user->getPassword(), "public", $this->user->getRoles());
     $this->getContainer()->get("security.context")->setToken($token);
     // Fire the login event
     $event = new InteractiveLoginEvent($this->getContainer()->get('request'), $token);
     $this->getContainer()->get("event_dispatcher")->dispatch("security.interactive_login", $event);
     $this->targetProcess = $input->getArgument('target');
     $this->user_id = $input->getArgument('user_id');
     $this->pid = $input->getArgument('pid');
     $file = $input->getArgument('file');
     $objReader = \PHPExcel_IOFactory::createReaderForFile($file);
     $objReader->setReadDataOnly(true);
     $objPHPExcel = $objReader->load($file);
     $sheet = $objPHPExcel->getSheet(0);
     $rows = $sheet->toArray();
     $this->processClient($rows);
     $this->messages = $this->getCountMessageImports();
     $this->sendNotification();
     echo serialize(array('messages' => $this->messages, 'import_counts' => $this->_import_counts, 'pid' => $this->pid, 'absErrorLogFilename' => $this->absErrorLogFilename));
 }
Beispiel #15
0
 /**
  * 添加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 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);
 }
Beispiel #17
0
 /**
  * @param $path
  * @return void
  * @throws \PHPExcel_Reader_Exception
  */
 public function load($path)
 {
     //determine correct reader
     $reader = \PHPExcel_IOFactory::createReaderForFile($path);
     //load the file
     $this->object = $reader->load($path);
 }
Beispiel #18
0
function save_sheet($xls_obj, $dirTemp, $titre)
{
    // Nom de l'onglet courant
    $xls_obj->getActiveSheet()->setTitle("{$titre}");
    // if(isset($_POST['date1']) && $_POST['date1'] && isset($_POST['date2']) && $_POST['date2']){
    // $d1 = $_POST['date1'];
    // $d2 = $_POST['date2'];
    // $d1f = preg_replace("/(\d{2})\/(\d{2})\/(\d{4})/","$3$2$1",$d1);
    // $d2f = preg_replace("/(\d{2})\/(\d{2})\/(\d{4})/","$3$2$1",$d2);
    // $filename=  "$dirTemp" . "$titre$d1f".'_'. "$d2f.xlsx";
    // }else{
    // $filename=  "$dirTemp" . mt_rand(1,100000).'.xlsx';
    // }
    $filename = "{$dirTemp}" . mt_rand(1, 100000) . '.xlsx';
    $objWriter = PHPExcel_IOFactory::createWriter($xls_obj, 'Excel2007');
    $objWriter->save($filename);
    header('Pragma: public');
    header('Expires: 0');
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Content-Type: application/force-download');
    header('Content-Type: application/octet-stream');
    header('Content-Type: application/download');
    header("Content-Disposition: attachment;filename={$filename}");
    header('Content-Transfer-Encoding: binary');
    $objWriter->save('php://output');
    unlink($filename);
}
 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);
     }
 }
Beispiel #20
0
 /**
  * @param string $filePath
  *
  * @return array|Student[]
  */
 public function read($filePath)
 {
     $students = [];
     $objPHPExcel = \PHPExcel_IOFactory::load($filePath);
     /** @var \PHPExcel_Worksheet $worksheet */
     foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
         /** @var \PHPExcel_Worksheet_Row $row */
         foreach ($worksheet->getRowIterator() as $row) {
             if (1 === $row->getRowIndex()) {
                 continue;
             }
             $data = [];
             /** @var \PHPExcel_Cell $cell */
             foreach ($row->getCellIterator('A', 'D') as $cell) {
                 switch ($cell->getColumn()) {
                     case 'A':
                         $data['grade'] = (int) $cell->getValue();
                         break;
                     case 'B':
                         $data['full_name'] = $cell->getValue();
                         break;
                     case 'C':
                         $data['school'] = $cell->getValue();
                         break;
                     case 'D':
                         $data['score'] = floatval($cell->getValue());
                         break;
                 }
             }
             $students[] = StudentFactory::createFromArray($data);
         }
     }
     return $students;
 }
 /**
  * Private/protected methods
  */
 protected function getExcelReader($filename)
 {
     if (!$this->excelReader) {
         $this->excelReader = \PHPExcel_IOFactory::load($filename);
     }
     return $this->excelReader;
 }
Beispiel #22
0
function parse($file)
{
    $objExcel = PHPExcel_IOFactory::load($file);
    $sheets = $objExcel->getAllSheets();
    $sheetNum = 1;
    foreach ($sheets as $sheet) {
        echo 'Processing Sheet ', $sheetNum++, '<br>';
        $row = 1;
        while (true) {
            echo 'Processing Row ', $row, '<br>';
            if ($sheet->cellExistsByColumnAndRow(0, $row)) {
                $firstName = $sheet->getCellByColumnAndRow(0, $row)->__toString();
                if ($firstName == 'First Name') {
                    $row++;
                    continue;
                }
                $lastName = $sheet->getCellByColumnAndRow(1, $row)->__toString();
                $email = $sheet->getCellByColumnAndRow(2, $row)->__toString();
                $company = $sheet->getCellByColumnAndRow(3, $row)->__toString();
                $job = $sheet->getCellByColumnAndRow(4, $row)->__toString();
                $tags = $sheet->getCellByColumnAndRow(5, $row)->__toString();
                echo $firstName, ' ', $lastName, ' ', $email, ' ', $company, ' ', $job, ' ', $tags, '<br>';
                addContact($firstName, $lastName, $email, $company, $job, $tags);
            } else {
                $row = -1;
                break;
            }
            $row++;
        }
        echo '<br><br>';
    }
}
Beispiel #23
0
function read_ou($fname)
{
    $xls = PHPExcel_IOFactory::load($fname);
    $xls->setActiveSheetIndex(0);
    $sheet = $xls->getActiveSheet();
    $nRow = $sheet->getHighestRow();
    $nColumn = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn());
    //$cat = '';
    $arr = [];
    for ($i = 2; $i <= $nRow; $i++) {
        for ($j = 0; $j <= $nColumn; $j++) {
            $row[$j] = trim($sheet->getCellByColumnAndRow($j, $i)->getValue());
        }
        if ($row[0] != '' and $row[1] != '' and $row[3] != '') {
            $code = trim($row[0]);
            $mr = trim($row[1]);
            $name = trim($row[3]);
            if ($mr = validate_mr($mr)) {
                //$arr[] = ['code'=>$code, 'mr'=>$mr, 'mr_new'=>validate_mr($mr), 'name'=>$name, 'name_new'=>validate_ou($name)];
                $arr[] = ['code' => $code, 'mr' => $mr, 'name' => $name];
            }
        } else {
            //категория
            //$cat = $row['1'];
        }
    }
    return $arr;
}
 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();
     }
 }
 public static function read($filename)
 {
     $path = Yii::$app->basePath . "/uploads/" . $filename;
     $objReader = \PHPExcel_IOFactory::createReaderForFile($path);
     //$objReader->setLoadSheetsOnly($sheets);
     $objReader->setReadDataOnly(true);
     $objPHPExcel = $objReader->load($path);
     $reader = new ExcelReader();
     foreach ($objPHPExcel->setActiveSheetIndex(0)->getRowIterator() as $row) {
         $cellIterator = $row->getCellIterator();
         $cellIterator->setIterateOnlyExistingCells(false);
         $rowData = [];
         foreach ($cellIterator as $cell) {
             if (!is_null($cell)) {
                 $rowData[] = $cell->getCalculatedValue();
             }
         }
         $reader->data[] = $rowData;
     }
     $reader->columnCount = count($reader->data[0]);
     $colrange = range(0, $reader->columnCount - 1);
     foreach ($colrange as $column) {
         $reader->columns[] = strval($column);
     }
     return $reader;
 }
Beispiel #26
0
 public function stream($filename, $data = null)
 {
     if ($data != null) {
         $col = 'A';
         foreach ($data[0] as $key => $val) {
             $objRichText = new PHPExcel_RichText();
             $objPayable = $objRichText->createTextRun(str_replace("_", " ", $key));
             $this->excel->getActiveSheet()->getCell($col . '1')->setValue($objRichText);
             $col++;
         }
         $rowNumber = 2;
         foreach ($data as $row) {
             $col = 'A';
             foreach ($row as $cell) {
                 $this->excel->getActiveSheet()->setCellValue($col . $rowNumber, $cell);
                 $col++;
             }
             $rowNumber++;
         }
     }
     header('Content-type: application/ms-excel');
     header("Content-Disposition: attachment; filename=\"" . $filename . "\"");
     header("Cache-control: private");
     $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
     $objWriter->save("./assets/{$filename}");
     header("location: " . SITE_LINK . "/assets/{$filename}");
     unlink(SITE_LINK . "/assets/{$filename}");
 }
 public static function exportXLS($items)
 {
     include 'lib/core/lhform/PHPExcel.php';
     $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
     $cacheSettings = array('memoryCacheSize ' => '64MB');
     PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
     $objPHPExcel = new PHPExcel();
     $objPHPExcel->setActiveSheetIndex(0);
     $objPHPExcel->getActiveSheet()->getStyle('A1:AW1')->getFont()->setBold(true);
     $objPHPExcel->getActiveSheet()->setTitle('Report');
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, erTranslationClassLhTranslation::getInstance()->getTranslation('survey/collected', 'Chats'));
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 1, erTranslationClassLhTranslation::getInstance()->getTranslation('survey/collected', 'Department name'));
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 1, erTranslationClassLhTranslation::getInstance()->getTranslation('survey/collected', 'Operator'));
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, 1, erTranslationClassLhTranslation::getInstance()->getTranslation('survey/collected', 'Stars'));
     $attributes = array('virtual_chats_number', 'department_name', 'user', 'average_stars');
     $i = 2;
     foreach ($items as $item) {
         foreach ($attributes as $key => $attr) {
             $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($key, $i, (string) $item->{$attr});
         }
         $i++;
     }
     $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
     // We'll be outputting an excel file
     header('Content-type: application/vnd.ms-excel');
     // It will be called file.xls
     header('Content-Disposition: attachment; filename="report.xlsx"');
     // Write file to the browser
     $objWriter->save('php://output');
 }
Beispiel #28
-1
 function export($list, $params = array())
 {
     if (PHP_SAPI == 'cli') {
         die('This example should only be run from a Web Browser');
     }
     require_once IA_ROOT . '/framework/library/phpexcel/PHPExcel.php';
     $excel = new PHPExcel();
     $excel->getProperties()->setCreator("人人商城")->setLastModifiedBy("人人商城")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("report file");
     $sheet = $excel->setActiveSheetIndex(0);
     $rownum = 1;
     foreach ($params['columns'] as $key => $column) {
         $sheet->setCellValue($this->column($key, $rownum), $column['title']);
         if (!empty($column['width'])) {
             $sheet->getColumnDimension($this->column_str($key))->setWidth($column['width']);
         }
     }
     $rownum++;
     foreach ($list as $row) {
         $len = count($row);
         for ($i = 0; $i < $len; $i++) {
             $value = $row[$params['columns'][$i]['field']];
             $sheet->setCellValue($this->column($i, $rownum), $value);
         }
         $rownum++;
     }
     $excel->getActiveSheet()->setTitle($params['title']);
     header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
     header('Content-Disposition: attachment;filename="' . $params['title'] . '-' . date('Y-m-d H:i', time()) . '.xlsx"');
     header('Cache-Control: max-age=0');
     $writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
     $writer->save('php://output');
     exit;
 }
Beispiel #29
-1
 public function export()
 {
     $IBlockId = $this->GetIdOfTheList($_POST['list']);
     $IBlockCode = $this->GetCodeOfTheList($_POST['list']);
     $arIBlockElements = $this->getArrINlockElements($IBlockId);
     array_unshift($arIBlockElements, array_keys($arIBlockElements[0]));
     $phpexcel = new \PHPExcel();
     /* Каждый раз делаем активной 1-ю страницу и получаем её, потом записываем в неё данные */
     $page = $phpexcel->setActiveSheetIndex(0);
     //Массив с заглавными буквами латинского алфавита
     $letters = $this::getLetters();
     $cellNumber = 1;
     foreach ($arIBlockElements as $arItem) {
         $numLitter = 0;
         foreach ($arItem as $item) {
             $page->setCellValue($letters[$numLitter] . $cellNumber, $item);
             $page->getStyle($letters[$numLitter] . $cellNumber)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
             $page->getStyle($letters[$numLitter] . '1')->getFont()->setBold(true);
             $page->getColumnDimension($letters[$numLitter])->setAutoSize(true);
             $numLitter++;
         }
         $cellNumber++;
     }
     $page->setTitle($IBlockCode);
     $objWriter = \PHPExcel_IOFactory::createWriter($phpexcel, 'Excel2007');
     $objWriter->save('DBExport.xlsx');
     header("Location: DBExport.xlsx");
 }
Beispiel #30
-1
 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;
 }