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');
}
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());
}
}
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 . ')');
}
}
}
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);
}
/**
* @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);
}
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);
}
}
/**
* @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;
}
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>';
}
}
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;
}
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');
}
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;
}
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");
}
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;
}