public function importExcel($function = null)
{
if ($this->upload_file()) {
if ($function) {
$this->onImportRow = $function;
}
//$reader = \PHPExcel_IOFactory::createReader( /*$this->defaultFormat*/ );
$objPHPExcel = \PHPExcel_IOFactory::load($this->_uploaded_file->tempName);
//$objPHPExcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
foreach ($objWorksheet->getRowIterator() as $i => $row) {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
$row = [];
foreach ($cellIterator as $cell) {
$row[] = $cell->getValue();
}
$this->_current_row = $i;
if ($this->import_row(['row' => $row, 'index' => $i, 'max_row' => $highestRow])) {
} else {
break;
}
}
}
}
function readexcel()
{
$file = './excel/akun.xls';
//load the excel library
$this->load->library('excel');
//read file from path
$objPHPExcel = PHPExcel_IOFactory::load($file);
//get only the Cell Collection
$cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection();
//extract to a PHP readable array format
foreach ($cell_collection as $cell) {
$column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn();
$row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
//header will/should be in row 1 only. of course this can be modified to suit your need.
if ($row == 1) {
$header[$row][$columns] = $data_value;
} else {
$arr_data[$row][$col] = $data_value;
}
}
//send the data in an array format
$data['header'] = $header;
$data['values'] = $arr_data;
}
public function Run()
{
$resArray = [];
$objPHPExcel = @\PHPExcel_IOFactory::load($this->filePath);
if ($objPHPExcel) {
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$headerArray = $sheetData[1];
unset($sheetData[1]);
foreach ($sheetData as $k => $itemArray) {
$itemArray = @array_combine($headerArray, $itemArray);
if ($itemArray) {
try {
$model = DynamicModel::validateData($itemArray, [[['id', 'type', 'available', 'bid', 'price', 'currencyId', 'categoryId', 'name', 'ISBN'], 'required']]);
} catch (UnknownPropertyException $e) {
continue;
}
if (isset($model) && !$model->hasErrors()) {
$resArray[] = $itemArray;
}
}
}
}
$this->response = $resArray;
unlink($this->filePath);
return $this;
}
function addxls()
{
$data['error'] = '';
//$area=$_POST['area_id'];
//$city=$_POST['city_id'];
$file_path = $_FILES['csv']['tmp_name'];
$file_type = $_FILES['csv']['type'];
$this->load->library('PHPExcel');
if ($file_type == 'text/csv') {
$objReader = new PHPExcel_Reader_CSV();
$PHPExcel = $objReader->load($file_path);
} else {
//echo "bye";die();
$PHPExcel = PHPExcel_IOFactory::load($file_path);
}
$objWorksheet = $PHPExcel->getActiveSheet();
$highestrow = $objWorksheet->getHighestRow();
//echo $highestrow;die();
for ($i = 2; $i <= $highestrow; $i++) {
$obj_insData = array('State' => addslashes($PHPExcel->getActiveSheet()->getCell('A' . $i)->getCalculatedValue()));
//$saveddata = $this->xls_model->getdata($obj_insData['Code']);
if ($obj_insData == '' && count($obj_insData) == '0') {
continue;
} else {
//echo "hello"; die();
mysql_query("INSERT INTO addessdata (state,city,pincode) VALUES\n\t\t\t\t\t(\n\t\t\t\t\t\t\n\t\t\t'" . addslashes($PHPExcel->getActiveSheet()->getCell('A' . $i)->getCalculatedValue()) . "',\n\t\t\t'" . addslashes($PHPExcel->getActiveSheet()->getCell('B' . $i)->getCalculatedValue()) . "',\n\t\t\t'" . addslashes($PHPExcel->getActiveSheet()->getCell('C' . $i)->getCalculatedValue()) . "'\n\t\t\t \n\t\t\t \n\t\t\t \n\t\t\t\t\t)\n\t\t\t\t");
}
}
$this->session->set_flashdata('message', 'Your Data File Uploaded Succcessfully.!!');
redirect($this->config->item('base_url') . 'xlsdata/lists');
}
/**
* 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 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());
}
}
/**
*
* @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());
}
/**
* Read Excel File Content and parse it into array
* @param string $file
* @return array
*/
public function readFile($file)
{
App::import('Vendor', 'PHPExcel/IOFactory');
$info = PHPExcel_IOFactory::load($file);
$sheetData = $info->getActiveSheet()->toArray(null, true, true, true);
return $sheetData;
}
public function __construct($filePath = '')
{
//如果存在就实例化读取对象
if (file_exists($filePath)) {
$this->PHPExcel = PHPExcel_IOFactory::load($filePath);
}
}
public function excel_oku()
{
$this->load->library('PHPExcel');
$dosyaAdi = 'dosyalar/upload/Servis Takip.xlsx';
$objPHPExcel = PHPExcel_IOFactory::load($dosyaAdi);
$objPHPExcel->getProperties()->setCreator("Kadir TUTAK")->setLastModifiedBy("Kadir TUTAK")->setTitle("Servis Takip")->setSubject("Servis Takip")->setDescription("Servis Takip")->setKeywords("Kadir TUTAK Servis Takip")->setCategory("Servis Takip");
/*
$cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection();
foreach($cell_collection AS $cell) {
$column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn();
$row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
if($row == 1) {
$header[$row][$column] = $data_value;
} else {
$arr_data[$row][$column] = $data_value;
}
}
$basliklar = $header;
$veriler = $arr_data;
foreach($veriler AS $veri) {
echo '<div style="clear: both;">'.$veri['A'].' - '.$veri['B'].' - '.$veri['C'].'</div>';
}
*/
}
protected function executeImpl($params, $type, $name, $key)
{
if (!$params->check("upload") || isset($_POST[$params->get("upload")])) {
$loader = new Vizualizer_Plugin($type);
// アップされたファイルのデータを取得する。
if ($_FILES[$key]["error"] == UPLOAD_ERR_OK) {
// Excelファイルを読み込む
$book = PHPExcel_IOFactory::load($_FILES[$key]["tmp_name"]);
// 処理を実行する
$data = $this->process($params, $book);
// トランザクションの開始
$connection = Vizualizer_Database_Factory::begin(strtolower($type));
try {
foreach ($data as $item) {
$model = $loader->loadModel($name);
foreach ($item as $col => $value) {
$model->{$col} = $value;
}
$model->save();
}
// エラーが無かった場合、処理をコミットする。
Vizualizer_Database_Factory::commit($connection);
// 画面をリロードする。
if (!$this->continue) {
// 登録に使用したキーを無効化
$this->removeInput("upload");
$this->reload();
}
} catch (Exception $e) {
Vizualizer_Database_Factory::rollback($connection);
throw new Vizualizer_Exception_Database($e);
}
}
}
}
public function getXLS($xls, $Activsheet = 0)
{
//открывет xls и возвращает масив
if (file_exists($xls)) {
include_once 'Classes/PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load($xls);
$objPHPExcel->setActiveSheetIndex($Activsheet);
$aSheet = $objPHPExcel->getActiveSheet();
$array = array();
//этот массив будет содержать массивы содержащие в себе значения ячеек каждой строки
//получим итератор строки и пройдемся по нему циклом
foreach ($aSheet->getRowIterator() as $row) {
//получим итератор ячеек текущей строки
$cellIterator = $row->getCellIterator();
//пройдемся циклом по ячейкам строки
$item = array();
//этот массив будет содержать значения каждой отдельной строки
foreach ($cellIterator as $cell) {
//заносим значения ячеек одной строки в отдельный массив
array_push($item, iconv('utf-8', 'cp1251', $cell->getCalculatedValue()));
}
//заносим массив со значениями ячеек отдельной строки в "общий массв строк"
array_push($array, $item);
}
} else {
$array = false;
echo '<br>файл не существует<br>';
}
return $array;
}
function read_xlsfile($filename)
{
/** PHPExcel_IOFactory */
include_once 'Classes/PHPExcel/IOFactory.php';
$inputFileName = $filename;
try {
PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
} catch (Exception $e) {
die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
}
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$row = 0;
foreach ($sheetData as $v => $i) {
if ($row == 0) {
$row = 1;
continue;
}
if (count($i) == 3 && filter_var($i['C'], FILTER_VALIDATE_EMAIL)) {
$query[] = "('" . implode("','", $i) . "')";
}
}
if (count($query) == 1000) {
bulk_insert($query);
$query = '';
}
return $query;
}
/**
* Excel 파일을 CSV 파일로 변환
*
* @param $excelPath string excel 파일 경로
* @param $csvPath string csv 파일 경로
* @return void
*/
public static function convertExcelIntoCsv(string $excelPath, string $csvPath)
{
echo "\nConverting Excel into CSV format...\n";
try {
// excel 파일을 로드하여 PHPExcel 선언
$objPhpExcel = \PHPExcel_IOFactory::load($excelPath);
// Excel->CSV 형식의 Object로 변환
$objWriter = new \PHPExcel_Writer_CSV($objPhpExcel);
// csv 경로에 같은 파일이 있으면 삭제
if (file_exists($csvPath)) {
echo "CSV file rewriting...\n";
unlink($csvPath);
}
// 해당 경로에 csv 파일 저장
$objWriter->save($csvPath);
echo "Conversion success! \n";
} catch (\PHPExcel_Reader_Exception $re) {
die('Error loading file: ' . $e->getMessage());
} finally {
// 메모리 release 작업
if ($objPhpExcel instanceof \PHPExcel_IOFactory) {
$objPhpExcel->disconnectWorksheets();
unset($objPhpExcel);
}
unset($objWriter);
}
}
protected function _beforeSave(Kwf_Model_Row_Interface $row)
{
$model = Kwf_Model_Abstract::getInstance('Kwf_Uploads_Model');
$uploadsRow = $model->getRow($row->upload_id);
if (!$uploadsRow) {
throw new Kwf_Exception_Client(trlKwf('File not found.'));
}
$source = $uploadsRow->getFileSource();
$target = 'temp/xlsimport_' . date('YmdHis') . '.' . $uploadsRow->extension;
copy($source, $target);
// copy with extension for xlsimport
$excel = PHPExcel_IOFactory::load($target);
if (!$excel) {
throw new Kwf_Exception_Client(trlKwf('Could not read excel'));
}
$excel = $excel->getActiveSheet();
$message = $this->_import($excel);
$this->view->message = null;
if ($message) {
if (is_string($message)) {
$this->view->message = nl2br($message);
}
}
$uploadsRow->delete();
unlink($target);
}
public function testExportXLSFile()
{
$this->_object = new Controller_ExtJS_Attribute_Export_Text_Default($this->_context);
$manager = MShop_Attribute_Manager_Factory::createManager($this->_context);
$ids = array();
foreach ($manager->searchItems($manager->createSearch()) as $item) {
$ids[] = $item->getId();
}
$params = new stdClass();
$params->lang = array('de');
$params->items = $ids;
$params->site = 'unittest';
$result = $this->_object->exportFile($params);
$this->assertTrue(array_key_exists('file', $result));
$file = substr($result['file'], 9, -14);
$this->assertTrue(file_exists($file));
$phpExcel = PHPExcel_IOFactory::load($file);
if (unlink($file) === false) {
throw new Exception('Unable to remove export file');
}
$phpExcel->setActiveSheetIndex(0);
$sheet = $phpExcel->getActiveSheet();
$this->assertEquals('Language ID', $sheet->getCell('A1')->getValue());
$this->assertEquals('Text', $sheet->getCell('G1')->getValue());
$this->assertEquals('de', $sheet->getCell('A9')->getValue());
$this->assertEquals('color', $sheet->getCell('B9')->getValue());
$this->assertEquals('red', $sheet->getCell('C9')->getValue());
$this->assertEquals('default', $sheet->getCell('D9')->getValue());
$this->assertEquals('name', $sheet->getCell('E9')->getValue());
$this->assertEquals('', $sheet->getCell('G9')->getValue());
}
/**
*
* @return type
*/
public function getExelData()
{
$this->objPHPExcel = PHPExcel_IOFactory::load($this->filename);
$this->collectData();
$this->dump($this->data, "Parsed xls");
return $this->data;
}
public function bulkadd()
{
$this->load->library('excel');
//echo base_url();die;
$file = 'D:\\xampp\\htdocs\\CI\\application\\third_party\\testdata.xlsx';
//load the excel library
//read file from path
$objPHPExcel = PHPExcel_IOFactory::load($file);
echo "!11";
//get only the Cell Collection
$cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection();
//extract to a PHP readable array format
foreach ($cell_collection as $cell) {
$column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn();
$row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
//header will/should be in row 1 only. of course this can be modified to suit your need.
if ($row == 1) {
$header[$row][$column] = $data_value;
} else {
$arr_data[$row][$column] = $data_value;
}
echo "=--=";
}
//send the data in an array format
$data['header'] = $header;
$data['values'] = $arr_data;
$this->load->view('header');
$this->load->view('user/user_buld_add');
$this->load->view('footer');
}
public function load($filePath)
{
$this->filePath = $filePath;
$this->phpExcel = PHPExcel_IOFactory::load($filePath);
$data = $this->phpExcel->getActiveSheet()->toArray(null, true, true);
$this->setArrayData($data, false);
}
function read()
{
$file = './assets/antenna/antenna.xlsx';
//load the excel library
$this->load->library('excel');
//read file from path
$objPHPExcel = PHPExcel_IOFactory::load($file);
//get only the Cell Collection
$cell_collection = $objPHPExcel->getActiveSheet('')->getCellCollection();
//extract to a PHP readable array format
foreach ($cell_collection as $cell) {
$column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn();
$row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
//header will/should be in row 1 only. of course this can be modified to suit your need.
if ($row == 1) {
$header[$row][$column] = $data_value;
} else {
$arr_data[$row][$column] = $data_value;
}
}
//send the data in an array format
$data['header'] = $header;
$data['values'] = $arr_data;
$table = "antenna";
foreach ($arr_data as $d) {
echo json_encode($d);
$ins = array('antenna_model' => $d['A'], 'antenna_gain' => $d['B'], 'aperture' => $d['C'], 'hor_bw' => $d['D']);
$this->Modeldb->insert($ins, $table);
}
}
public function getArray()
{
# Start Lib
$objPHPExcel = PHPExcel_IOFactory::load($this->fileName);
$objPHPExcel->setActiveSheetIndex(0);
$aSheet = $objPHPExcel->getActiveSheet();
# Собрать массивы
$rows = array();
$i = 0;
foreach ($aSheet->getRowIterator() as $row) {
$cellIterator = $row->getCellIterator();
foreach ($cellIterator as $cell) {
$cell->getCalculatedValue() != '' ? $rows[$i][] = trim($cell->getCalculatedValue()) : FALSE;
}
$i++;
}
if (!empty($rows[0])) {
$rows['cols'] = $rows[0];
unset($rows[0]);
}
$newRows = array();
foreach ($rows as $k => $v) {
if ('cols' !== $k) {
foreach ($v as $a => $b) {
!empty($rows['cols'][$a]) && ($newRows[$k][$rows['cols'][$a]] = $b);
}
}
}
return $newRows;
}
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!");
}
/**
* Creates an instance of PHPExcel based of a file path.
* @param $filePath
* @return \PHPExcel
*/
public function createWorkBook($filePath)
{
$info = pathinfo($filePath);
$this->fileExtension = $info['extension'];
switch ($this->fileExtension) {
// if text or csv use PHPExcel_Reader_CSV else use loader
//due to inability of the PHPExcel IOfactory being
//unable to handle csv/txt
case 'txt':
$objReader = new \PHPExcel_Reader_CSV();
$objReader->setInputEncoding($this->fileInfo['encoding']);
$objReader->setDelimiter($this->fileInfo['delimiter']);
$objReader->setEnclosure($this->fileInfo['enclosure']);
$objReader->setSheetIndex(0);
$this->workbook = $objReader->load($filePath);
break;
case 'csv':
$objReader = new \PHPExcel_Reader_CSV();
$objReader->setInputEncoding($this->fileInfo['encoding']);
$objReader->setDelimiter($this->fileInfo['delimiter']);
$objReader->setEnclosure($this->fileInfo['enclosure']);
$objReader->setSheetIndex(0);
$this->workbook = $objReader->load($filePath);
break;
default:
$this->workbook = \PHPExcel_IOFactory::load($filePath);
break;
}
return $this->workbook;
// return created workbook
}
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;
}
function read_ilias_users($fname)
{
$xls = PHPExcel_IOFactory::load($fname);
$xls->setActiveSheetIndex(0);
$sheet = $xls->getActiveSheet();
$nRow = $sheet->getHighestRow();
$nColumn = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn());
$arr = [];
for ($i = 5; $i <= $nRow; $i++) {
for ($j = 0; $j <= $nColumn; $j++) {
$row[$j] = trim($sheet->getCellByColumnAndRow($j, $i)->getValue());
}
if ($row[0] != '' and $row[1] != '') {
$fname = explode(' ', trim($row[0]));
$lname = explode(' ', trim($row[1]));
foreach ($fname as $item) {
if ($item != '') {
$arr[$i][] = $item;
}
}
foreach ($lname as $item) {
if ($item != '') {
$arr[$i][] = $item;
}
}
}
}
return $arr;
}
/**
* Private/protected methods
*/
protected function getExcelReader($filename)
{
if (!$this->excelReader) {
$this->excelReader = \PHPExcel_IOFactory::load($filename);
}
return $this->excelReader;
}
function PrepareValues()
{
set_include_path(get_include_path() . PATH_SEPARATOR . '../../../Classes/');
include 'PHPExcel/IOFactory.php';
$inputFileName = $this->destination;
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$NumRow = 0;
foreach ($sheetData as $key => $value) {
if ($NumRow == 0) {
$NumRow++;
} else {
switch ($this->method) {
case 'additions':
$this->saveDBrow($value);
echo "<p>Row succesfully added!</p>";
break;
case 'removals':
$this->removeDBrow($value);
echo "<p>Row succesfully removed!</p>";
break;
case 'updates':
$result = $this->UpdateDBrow($value);
if ($result) {
echo "<p>Row succesfully Updated!</p>";
}
break;
}
}
}
}
/**
* @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;
}
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>';
}
}
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;
}
