PHP PHPExcel_IOFactory类代码示例

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

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

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

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

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

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

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

 /**
  * 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!";
 }

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

 /**
  * 添加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 . ')');
         }
     }
 }