public function run_import($file_upload)
{
$file_path = './upload/files/excel/' . $file_upload['file_name'];
//load the excel library
$this->load->library('excel');
//read file from path
$inputFileType = PHPExcel_IOFactory::identify($file_path);
//die(print_r($inputFileType));
/** Create a new Reader of the type defined in $inputFileType **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($file_path);
//die(print_r($objPHPExcel));
//get only the Cell Collection
$cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection();
//extract to a PHP readable array format
foreach ($cell_collection as $cell) {
$column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn();
$row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
//header will/should be in row 1 only. of course this can be modified to suit your need.
if ($row == 1) {
$header[$row][$column] = $data_value;
} else {
$arr_data[$row][$column] = $data_value;
}
}
//send the data in an array format
$data['header'] = $header;
$data['values'] = $arr_data;
return $arr_data;
}
public static function excelParsing($fileExcel)
{
// $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_sqlite3; /* here i added */
// $cacheEnabled = \PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
// if (!$cacheEnabled) {
// echo "### WARNING - Sqlite3 not enabled ###" . PHP_EOL;
// }
$objPHPExcel = new \PHPExcel();
//$fileExcel = Yii::getAlias('@webroot/templates/operator.xls');
$inputFileType = \PHPExcel_IOFactory::identify($fileExcel);
$objReader = \PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
/** Load $inputFileName to a PHPExcel Object * */
$objPHPExcel = $objReader->load($fileExcel);
$total_sheets = $objPHPExcel->getSheetCount();
$allSheetName = $objPHPExcel->getSheetNames();
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
for ($row = 1; $row <= $highestRow; ++$row) {
for ($col = 0; $col < $highestColumnIndex; ++$col) {
$value = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
$arraydata[$row - 1][$col] = $value;
}
}
return $arraydata;
}
public function test_read_write_excel()
{
$inputFileName = 'print_docs/excel/excel_template/KEMSA Customer Order Form.xlsx';
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
// echo "$inputFileType";die;
$file_name = time() . '.xlsx';
$excel2 = PHPExcel_IOFactory::createReader($inputFileType);
$excel2 = $objPHPExcel = $excel2->load($inputFileName);
// Empty Sheet
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$excel2->setActiveSheetIndex(0);
$excel2->getActiveSheet()->setCellValue('H4', '4')->setCellValue('H5', '5')->setCellValue('H6', '6')->setCellValue('H7', '7')->setCellValue('H8', '7');
// Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++) {
// Read a row of data into an array
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
if (isset($rowData[0][2]) && $rowData[0][2] != 'Product Code') {
$excel2->getActiveSheet()->setCellValue("H{$row}", '7');
}
}
$objWriter = PHPExcel_IOFactory::createWriter($excel2, $inputFileType);
$objWriter->save("print_docs/excel/excel_files/" . $file_name);
}
/**
* [excel2Array description]
*
* @param [type] $filepath [description]
* @param array $result [description]
* @return [type] [description]
*/
public function excel2Array($filepath = null, $result = array())
{
if (!file_exists($filepath)) {
App::abort('500', "Error loading file " . $filepath . ": File does not exist");
}
// Read your Excel workbook
try {
$inputFileType = \PHPExcel_IOFactory::identify($filepath);
$objReader = \PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($filepath);
} catch (Exception $e) {
App::abort('500', "Error loading file " . pathinfo($filepath, PATHINFO_BASENAME) . ": " . $e->getMessage());
}
$i = 0;
// Loop through each worksheet
foreach ($objPHPExcel->getWorksheetIterator() as $sheet) {
// Get worksheet dimensions
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
// Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++) {
// Read a row of data into an array
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
$result[$i][] = $rowData;
}
$i++;
}
return $result;
}
public function __construct($filename = null, $sheet = 0)
{
$this->filename = $filename;
if (file_exists($filename)) {
/* on charge le contenu du fichier dans l'objet phpExcel */
$inputFileType = PHPExcel_IOFactory::identify($filename);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$this->phpExcel = $objReader->load($filename);
/* on implémente dans le tableau */
// Get worksheet dimensions
$sheet = $this->phpExcel->getSheet($sheet);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
// Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++) {
// Read a row of data into an array
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
$this->container[] = $rowData[0];
}
} else {
$this->phpExcel = new PHPExcel();
$this->phpExcel->setActiveSheetIndex($sheet);
}
parent::__construct($this->container);
}
/**
* {@inheritDoc}
*/
public function read()
{
$inputFileName = $this->filename;
try {
$inputFileType = \PHPExcel_IOFactory::identify($inputFileName);
$objReader = \PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
} catch (\Exception $e) {
die(sprintf('Error loading file "%s": %s', pathinfo($inputFileName, PATHINFO_BASENAME), $e->getMessage()));
}
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
if (0 !== $this->offset) {
//@todo implement
throw new \InvalidArgumentException('Offset is not yet supported');
}
$properties = [];
for ($rowIndex = 1; $rowIndex <= $highestRow; $rowIndex++) {
$values = $sheet->rangeToArray('A' . $rowIndex . ':' . $highestColumn . $rowIndex, null, null, false);
$values = $values[0];
if ($this->headerDetector->isHeader($rowIndex, $values)) {
$properties = $this->describeProperties($values);
continue;
}
if ($rowIndex < $this->offset) {
continue;
}
if (!count($properties)) {
continue;
}
$this->processValues($properties, $values);
}
}
public function actionImport()
{
$field = ['fileImport' => 'File Import'];
$modelImport = DynamicModel::validateData($field, [[['fileImport'], 'required'], [['fileImport'], 'file', 'extensions' => 'xls,xlsx', 'maxSize' => 1024 * 1024]]);
if (Yii::$app->request->post()) {
$modelImport->fileImport = \yii\web\UploadedFile::getInstance($modelImport, 'fileImport');
if ($modelImport->fileImport && $modelImport->validate()) {
$inputFileType = \PHPExcel_IOFactory::identify($modelImport->fileImport->tempName);
$objReader = \PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($modelImport->fileImport->tempName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$baseRow = 2;
while (!empty($sheetData[$baseRow]['A'])) {
$model = new Mahasiswa();
$model->nama = (string) $sheetData[$baseRow]['B'];
$model->nim = (string) $sheetData[$baseRow]['C'];
$model->save();
//die(print_r($model->errors));
$baseRow++;
}
Yii::$app->getSession()->setFlash('success', 'Success');
} else {
Yii::$app->getSession()->setFlash('error', 'Error');
}
}
return $this->redirect(['index']);
}
public function test()
{
$inputFileName = 'application/xls/Catalogo_clientes.xlsx';
// Read your Excel workbook
try {
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
// var_dump($inputFileType);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
//var_dump($objPHPExcel);
} catch (Exception $e) {
die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
}
// Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
// Loop through each row of the worksheet in turn
for ($row = 4; $row <= $highestRow; $row++) {
// Read a row of data into an array
$rowData[] = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
// Insert row data array into your database of choice here
}
return $rowData;
}
function read_sheet($file, $employ_array)
{
require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php';
$inputFileName = $file;
// Read your Excel workbook
try {
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
} catch (Exception $e) {
die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
}
$rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator();
$array_data = array();
$i = 1;
foreach ($rowIterator as $row) {
if ($i > sizeof($employ_array) + 2) {
break;
}
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
$rowIndex = $row->getRowIndex();
$array_data[$rowIndex] = array('A' => '', 'B' => '', 'C' => '', 'D' => '', 'E' => '');
foreach ($cellIterator as $cell) {
if ('A' == $cell->getColumn()) {
$array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
} else {
if ('B' == $cell->getColumn()) {
$array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
} else {
if ('C' == $cell->getColumn()) {
$array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
} else {
if ('D' == $cell->getColumn()) {
$array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
} else {
if ('E' == $cell->getColumn()) {
$array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
} else {
if ('F' == $cell->getColumn()) {
$array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
} else {
if ('G' == $cell->getColumn()) {
$array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
}
}
}
}
}
}
}
}
$i++;
}
unset($array_data[1]);
unset($array_data[2]);
echo '<pre>';
return $array_data;
}
public static function readXLS($xlsfile)
{
$fileType = PHPExcel_IOFactory::identify($xlsfile);
$objReader = PHPExcel_IOFactory::createReader($fileType);
$objPHPExcel = $objReader->load($xlsfile);
$sheets = $objPHPExcel->getSheet(0)->toArray();
return $sheets;
}
public function __construct($file_path)
{
require_once "../app/classes/PHPExcel.php";
#将单元格序列化后再进行Gzip压缩，然后保存在内存中
PHPExcel_Settings::setCacheStorageMethod(PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip);
$file_type = PHPExcel_IOFactory::identify($file_path);
$objReader = PHPExcel_IOFactory::createReader($file_type);
self::$objPHPExcel = $objReader->load($file_path);
}
function convertXLStoCSV($infile, $outfile)
{
$fileType = PHPExcel_IOFactory::identify($infile);
$objReader = PHPExcel_IOFactory::createReader($fileType);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($infile);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
$objWriter->save($outfile);
}
function importXLS($filename)
{
//precondition: XLS file has been uploaded ($this->uploadXLS)
//use PHPExcel for this
ini_set('memory_limit', '512M');
$this->load->library('PHPExcel');
$msg = '';
$inputFileName = $filename;
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
//find sheet data_import_spec
$sheetname = 'IWP participants';
$objReader->setLoadSheetsOnly($sheetname);
$objPHPExcel = $objReader->load($inputFileName);
if ($objPHPExcel->getSheetCount() != 1) {
//sheet not found
$msg .= "A worksheet named 'IWP Participants' was not found in this workbook";
return $msg;
} else {
$this->dbAdmin = $this->load->database('admin', true);
$this->dbAdmin->truncate('masterlist');
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
//in fieldmap, the excel column name is the key and the table field name is the value
$fieldmap = array('macroregion' => 'macro_region', 'REGION' => 'region', 'COUNTRY' => 'country', 'IWP PROGRAM' => 'iwp_program', 'YEAR(S)' => 'years', 'FAMILY NAME' => 'family_name', 'GIVEN NAME' => 'given_name', 'OCLC' => 'oclc', 'penname' => 'penname', 'drupal_family_name' => 'drupal_family_name', 'drupal_given_name' => 'drupal_given_name', 'drupal_nid' => 'drupal_nid');
foreach ($sheetData as $rownum => $columns) {
if ($rownum > 2) {
//skip first two rows
if ($rownum == 3) {
//build fieldlist array by matching column headings in first row stored in third row
foreach ($columns as $column_name => $column_value) {
$column_value = trim($column_value);
if (array_key_exists($column_value, $fieldmap)) {
$fieldlist[$column_name] = $fieldmap[$column_value];
//ex $fieldlist['B'] = $fieldmap['macroregion']
}
}
} else {
//build insert array on subsequent rows and submit query
//foreach ($columns as $column_name=>$column_value){
$blankrow = true;
foreach ($fieldlist as $xcelCol => $masterlistField) {
if (!empty($columns[$xcelCol])) {
$blankrow = false;
}
$data[$masterlistField] = $columns[$xcelCol];
}
if (!$blankrow) {
$this->db->insert('masterlist', $data);
$recordsprocessed = $rownum - 1;
}
}
}
}
$msg = "Processed {$recordsprocessed} rows from the masterlist worksheet.";
}
return $msg;
}
/**
* @return \PHPExcel
*/
public function get($id)
{
Assertion::notEmpty($id);
Assertion::string($id);
$filePath = $this->directory . $id;
$inputFileType = \PHPExcel_IOFactory::identify($filePath);
$objReader = \PHPExcel_IOFactory::createReader($inputFileType);
return $objReader->load($filePath);
}
public function prepare()
{
$this->files = array();
$templatePath = $this->templatePath;
if (!empty($_FILES)) {
$tempFile = $_FILES['CrudCode']['tmp_name']['file'];
$fileTypes = array('xls', 'xlsx');
// File extensions
$fileParts = pathinfo($_FILES['CrudCode']['name']['file']);
if (in_array(@$fileParts['extension'], $fileTypes)) {
Yii::import('ext.heart.excel.EHeartExcel', true);
EHeartExcel::init();
$inputFileType = PHPExcel_IOFactory::identify($tempFile);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($tempFile);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$baseRow = 2;
$inserted = 0;
$read_status = false;
//die();
while (!empty($sheetData[$baseRow]['A'])) {
$read_status = true;
$this->modelType = $sheetData[$baseRow]['B'];
$this->model = $sheetData[$baseRow]['C'];
$this->controller = $sheetData[$baseRow]['D'];
$this->modelParent = $sheetData[$baseRow]['E'];
$this->controllerParent = $sheetData[$baseRow]['F'];
$class = @Yii::import($this->model, true);
$table = CActiveRecord::model($class)->tableSchema;
$this->_modelClass = $class;
$this->_table = $table;
$controllerTemplateFile = $templatePath . DIRECTORY_SEPARATOR . 'controller.php';
$this->files[] = new CCodeFile($this->controllerFile, $this->render($controllerTemplateFile));
$files = scandir($templatePath);
foreach ($files as $file) {
if (is_file($templatePath . '/' . $file) && CFileHelper::getExtension($file) === 'php' && $file !== 'controller.php') {
$this->files[] = new CCodeFile($this->viewPath . DIRECTORY_SEPARATOR . $file, $this->render($templatePath . '/' . $file));
}
}
$baseRow++;
}
if ($this->files != array()) {
$this->save();
}
}
} else {
$controllerTemplateFile = $templatePath . DIRECTORY_SEPARATOR . 'controller.php';
$this->files[] = new CCodeFile($this->controllerFile, $this->render($controllerTemplateFile));
$files = scandir($templatePath);
foreach ($files as $file) {
if (is_file($templatePath . '/' . $file) && CFileHelper::getExtension($file) === 'php' && $file !== 'controller.php') {
$this->files[] = new CCodeFile($this->viewPath . DIRECTORY_SEPARATOR . $file, $this->render($templatePath . '/' . $file));
}
}
}
}
/**
* Creates a new Car4 model.
* If creation is successful, the browser will be redirected to the 'view' page.
* @return mixed
*/
public function actionCreate()
{
$model = new Car4();
if ($model->load(Yii::$app->request->post())) {
$uploaded = UploadedFile::getInstance($model, 'myfile');
// $uploaded = UploadedFile::getInstances($model, 'myfile');
if (!empty($uploaded)) {
$upfiles = time() . "." . $uploaded->getExtension();
if ($uploaded->saveAs('../../uploads/' . $upfiles)) {
// $handle = fopen('../../uploads/' . $upfiles, 'r');
}
// echo $handle;
// return;
$filename = '../../uploads/' . $upfiles;
$excelfile = \PHPExcel_IOFactory::identify($filename);
$objReader = \PHPExcel_IOFactory::createReader($excelfile);
$objfile = $objReader->load($filename);
$sheet = $objfile->getSheet(0);
$rowcount = $sheet->getHighestRow();
$columncount = $sheet->getHighestColumn();
$result = 0;
for ($row = 0; $row <= $rowcount; $row++) {
$rowdata = $sheet->rangeToArray('A' . $row . ':' . $columncount . $row, NULL, TRUE, FALSE);
if ($row == 1) {
continue;
}
$name = $rowdata[0][2];
$gen = explode("/", $name);
if ($name == '') {
continue;
}
// echo $rowdata[0][2].'<BR />';
// foreach ($gen as $x){echo $x.'<BR />';}
// echo $gen[0] . ' ' . $gen[1] . ' ' . $gen[2];
//if($row>1){return;}
$model2 = new \backend\Models\Car4();
$model2->brand = $gen[0];
$model2->gen = $gen[1];
$model2->o1 = isset($gen[2]) ? $gen[2] : '';
$model2->o2 = isset($gen[3]) ? $gen[3] : '';
$model2->o3 = isset($gen[4]) ? $gen[4] : '';
$model2->o4 = isset($gen[5]) ? $gen[5] : '';
$model2->o5 = isset($gen[6]) ? $gen[6] : '';
if ($model2->save()) {
$result++;
}
}
}
if ($result > 0) {
return $this->redirect(['index']);
}
} else {
return $this->render('create', ['model' => $model]);
}
}
function convertXLStoCSV($infile, $outfile)
{
App::import('Vendor', 'phpexcel', array('file' => 'phpexcel' . DS . 'Classes' . DS . 'PHPExcel.php'));
$fileType = PHPExcel_IOFactory::identify($infile);
var_dump($fileType);
$objReader = PHPExcel_IOFactory::createReader($fileType);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($infile);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
$objWriter->save($outfile);
}
function importUserFromExcel($inputFileName)
{
if (!class_exists('PHPExcel_IOFactory')) {
require_once 'phar://' . __DIR__ . '/phpexcel.phar';
}
// Read your Excel workbook
try {
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
} catch (Exception $e) {
die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
}
// Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$fields = array('user_id', 'title', 'first_name', 'last_name', 'email');
$visible_fields = array();
$result = array();
for ($row = 1; $row <= $highestRow; $row++) {
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
if (empty($visible_fields)) {
foreach ($rowData[0] as $k => $v) {
if (is_string($v)) {
$v = trim($v);
}
if (in_array($v, $fields)) {
$visible_fields[$v] = $k;
}
}
} else {
if (!(isset($visible_fields['user_id']) && isset($visible_fields['title']) && isset($visible_fields['first_name']) && isset($visible_fields['last_name']))) {
echo "require fields (user_id, title, first_name, last_name)";
break;
}
$it = array();
foreach ($visible_fields as $a => $b) {
$v = $rowData[0][$b];
if (is_string($v)) {
$v = trim($v);
}
$it[$a] = $v;
}
if ($it['user_id'] && $it['title'] && $it['first_name'] && $it['last_name']) {
$result[$it['user_id']] = $it;
} else {
unset($it);
}
}
}
var_dump(array_keys($result));
}
public function display($tpl = null)
{
if ($this->getLayout() !== 'modal') {
// Include helper submenu
CostbenefitprojectionHelper::addSubmenu('import');
}
// Check for errors.
if (count($errors = $this->get('Errors'))) {
JError::raiseError(500, implode('<br />', $errors));
return false;
}
$paths = new stdClass();
$paths->first = '';
$state = $this->get('state');
$this->paths =& $paths;
$this->state =& $state;
// get global action permissions
$this->canDo = CostbenefitprojectionHelper::getActions('import');
// We don't need toolbar in the modal window.
if ($this->getLayout() !== 'modal') {
$this->addToolbar();
$this->sidebar = JHtmlSidebar::render();
}
// get the session object
$session = JFactory::getSession();
// check if it has package
$this->hasPackage = $session->get('hasPackage', false);
$this->dataType = $session->get('dataType', false);
if ($this->hasPackage && $this->dataType) {
$this->headerList = json_decode($session->get($this->dataType . '_VDM_IMPORTHEADERS', false), true);
$this->headerListAdv = array("location_name" => JText::_('COM_COSTBENEFITPROJECTION_LOCATION_NAME'), "year" => JText::_('COM_COSTBENEFITPROJECTION_YEAR'), "cause" => JText::_('COM_COSTBENEFITPROJECTION_CAUSE_ID'), "cause_name" => JText::_('COM_COSTBENEFITPROJECTION_CAUSE_NAME'), "risk" => JText::_('COM_COSTBENEFITPROJECTION_RISK_ID'), "risk_name" => JText::_('COM_COSTBENEFITPROJECTION_RISK_NAME'), "age" => JText::_('COM_COSTBENEFITPROJECTION_AGE_ID'), "age_name" => JText::_('COM_COSTBENEFITPROJECTION_AGE_NAME'), "sex" => JText::_('COM_COSTBENEFITPROJECTION_GENDER_ID'), "sex_name" => JText::_('COM_COSTBENEFITPROJECTION_GENDER_NAME'), "rt_mean" => JText::_('COM_COSTBENEFITPROJECTION_RT_MEAN_VALUE'), "metric" => JText::_('COM_COSTBENEFITPROJECTION_METRIC'), "metric_name" => JText::_('COM_COSTBENEFITPROJECTION_METRIC_NAME'));
// make sure these files are loaded
JLoader::import('PHPExcel', JPATH_COMPONENT_ADMINISTRATOR . '/helpers');
$package = $session->get('package', null);
$package = json_decode($package, true);
$inputFileType = PHPExcel_IOFactory::identify($package['dir']);
if ('csv' == trim(strtolower($inputFileType), '.')) {
$this->headers = CostbenefitprojectionHelper::getFileHeadersCSV($package['dir']);
} else {
$this->headers = CostbenefitprojectionHelper::getFileHeaders($this->dataType);
}
// set active tab
if (in_array('rt_mean', $this->headers) || in_array('metric', $this->headers)) {
$this->activeTab = 'advanced';
} else {
$this->activeTab = 'basic';
}
// clear the data type
$session->clear('dataType');
}
// Display the template
parent::display($tpl);
}
function excelToArray($filePath, $header = true, $sheets = null)
{
//Create excel reader after determining the file type
$inputFileName = $filePath;
/* Identify the type of $inputFileName **/
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
/* Create a new Reader of the type that has been identified **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/* Set read type to read cell data only **/
$objReader->setReadDataOnly(true);
if (!empty($sheets)) {
# See
# https://github.com/AmphibiaWeb/amphibian-disease-tracker/blob/master/phpexcel/Documentation/markdown/ReadingSpreadsheetFiles/05-Reader-Options.md#reading-only-named-worksheets-from-a-file
$objReader->setLoadSheetsOnly($sheets);
}
/* Load $inputFileName to a PHPExcel Object **/
$objPHPExcel = $objReader->load($inputFileName);
$sheetNames = $objPHPExcel->getSheetNames();
if(!in_array($sheets, $sheetNames, true)) {
# If the sheet doesn't exist, just read the first one
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
}
# Get worksheet and built array with first row as header
$objWorksheet = $objPHPExcel->getActiveSheet();
//excel with first row header, use header as key
if ($header) {
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$headingsArray = $objWorksheet->rangeToArray('A1:'.$highestColumn.'1', null, true, true, true);
$headingsArray = $headingsArray[1];
$r = -1;
$namedDataArray = array();
for ($row = 2; $row <= $highestRow; ++$row) {
$dataRow = $objWorksheet->rangeToArray('A'.$row.':'.$highestColumn.$row, null, true, true, true);
if ((isset($dataRow[$row]['A'])) && ($dataRow[$row]['A'] > '')) {
++$r;
foreach ($headingsArray as $columnKey => $columnHeading) {
$columnHeading = trim($columnHeading);
$namedDataArray[$r][$columnHeading] = $dataRow[$row][$columnKey];
}
}
}
} else {
//excel sheet with no header
$namedDataArray = $objWorksheet->toArray(null, true, true, true);
}
return $namedDataArray;
}
public static function getPHPExcelObject($file)
{
if (!file_exists($file)) {
return false;
}
/** Identify the type of $inputFileName * */
$inputFileType = PHPExcel_IOFactory::identify($file);
/** Create a new Reader of the type that has been identified * */
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/** Load $inputFileName to a PHPExcel Object * */
$objPHPExcel = $objReader->load($file);
return $objPHPExcel;
}
public function defineObjPHPExcel($excelFileName)
{
//example "data/wells.xls"
require_once 'lib/Classes/PHPExcel/IOFactory.php';
try {
$inputFileType = PHPExcel_IOFactory::identify($excelFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
$this->objPHPExcel = $objReader->load($excelFileName);
} catch (PHPExcel_Reader_Exception $e) {
die('Error loading file: ' . $e->getMessage());
}
}
/**
* Excel with one sheet
* @param string|null $filePath
* @param bool $withoutSheets
* @throws PhpExcelException
*/
public function __construct($filePath = null, $withoutSheets = true)
{
try {
$this->excel = $filePath ? PhpOffice_PHPExcel_IOFactory::load($filePath) : new PhpOffice_PHPExcel();
} catch (PhpOffice_PHPExcel_Reader_Exception $ex) {
throw new PhpExcelException('Unable to create excel object.', 0, $e);
}
if ($filePath) {
$this->excelType = PhpOffice_PHPExcel_IOFactory::identify($filePath);
}
if (!$filePath && $withoutSheets) {
$this->excel->removeSheetByIndex();
}
}
function parse_excel_file($filename)
{
// подключаем библиотеку
require_once dirname(__FILE__) . '/map_classes/PHPExcel.php';
$result = array();
// получаем тип файла (xls, xlsx), чтобы правильно его обработать
$file_type = PHPExcel_IOFactory::identify($filename);
// создаем объект для чтения
$objReader = PHPExcel_IOFactory::createReader($file_type);
$objPHPExcel = $objReader->load($filename);
// загружаем данные файла в объект
$result = $objPHPExcel->getActiveSheet()->toArray();
// выгружаем данные из объекта в массив
return $result;
}
function importDatax()
{
// header('Content-Type: text/plain');
// pre($_FILES);
$inputFileName = $_FILES['userfile']['tmp_name'];
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
// pre($objReader);
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
// $objWorksheet = $objPHPExcel->setActiveSheetIndexbyName('Sheet1');
$objWorksheet = $objPHPExcel->setActiveSheetIndexbyName('Sheet1');
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$count = count($sheetData);
// pre($count);
$dataexcel = array();
$index = 0;
foreach ($sheetData as $key => $value) {
if ($key >= 3) {
$dataexcel[$index]['A'] = $value['A'];
$dataexcel[$index]['B'] = $value['B'];
$dataexcel[$index]['C'] = $value['C'];
$dataexcel[$index]['D'] = $value['D'];
$dataexcel[$index]['E'] = $value['E'];
$dataexcel[$index]['F'] = $value['F'];
$dataexcel[$index]['G'] = $value['G'];
$dataexcel[$index]['H'] = $value['H'];
$dataexcel[$index]['I'] = $value['I'];
$dataexcel[$index]['J'] = $value['J'];
$dataexcel[$index]['K'] = $value['K'];
$dataexcel[$index]['L'] = $value['L'];
$dataexcel[$index]['M'] = $value['M'];
$dataexcel[$index]['N'] = $value['N'];
$dataexcel[$index]['O'] = $value['O'];
$dataexcel[$index]['P'] = $value['P'];
$dataexcel[$index]['Q'] = $value['Q'];
$dataexcel[$index]['R'] = $value['R'];
$index++;
}
}
// pre($dataexcel);exit;
$data['arrDB'] = $dataexcel;
$_SESSION['dataexcel'] = $dataexcel;
$data_layout["content"] = $this->load->view("kejadian/v_list_excel", $data, true);
$this->load->view($this->admin_layout, $data_layout);
}
/**
* reads a file PHPExcel can understand and converts a contained worksheet into an array
* which can be used to build entities. If the File contains more than one worksheet and it is not named like the Controller
* you have to provide the name of the workshhet to load in the options array.
* If you set $options['append'] to true, the primary key will be deleted.
* @todo Find a way to make to handle primary keys anmed other than id.
*
* @param string $file name of Excel-File with full path. Must be of a readable Filetype (xls, xlsx, csv, ods)
* @param array $options Override Worksheet name, set append Mode
* @return array . The Array has the same structure as provided by request->data
* @throws MissingTableClassException
*/
public function prepareEntityData($file = null, array $options = [])
{
/** load and configure PHPExcelReader * */
\PHPExcel_Cell::setValueBinder(new \PHPExcel_Cell_AdvancedValueBinder());
$fileType = \PHPExcel_IOFactory::identify($file);
$PhpExcelReader = \PHPExcel_IOFactory::createReader($fileType);
$PhpExcelReader->setReadDataOnly(true);
if ($fileType !== 'CSV') {
// csv-files have only one 'worksheet'
/** identify worksheets in file * */
$worksheets = $PhpExcelReader->listWorksheetNames($file);
$worksheetToLoad = null;
if (count($worksheets) === 1) {
$worksheetToLoad = $worksheets[0];
//first option: if there is only one worksheet, use it
} elseif (isset($options['worksheet'])) {
$worksheetToLoad = $options['worksheet'];
//second option: desired worksheet was provided as option
} else {
$worksheetToLoad = $this->_registry->getController()->name;
//last option: try to load worksheet with the name of current controller
}
if (!in_array($worksheetToLoad, $worksheets)) {
throw new MissingTableClassException(__('No proper named worksheet found'));
}
/** load the sheet and convert data to an array */
$PhpExcelReader->setLoadSheetsOnly($worksheetToLoad);
}
$PhpExcel = $PhpExcelReader->load($file);
$data = $PhpExcel->getSheet(0)->toArray();
/** convert data for building entities */
$result = [];
$properties = array_shift($data);
//first row columns are the properties
foreach ($data as $row) {
$record = array_combine($properties, $row);
if (isset($record['modified'])) {
unset($record['modified']);
}
if (isset($options['type']) && $options['type'] == 'append' && isset($record['id'])) {
unset($record['id']);
}
$result[] = $record;
}
/** log in debug mode */
$this->log(count($result) . ' records were extracted from File ' . $file, 'debug');
return $result;
}
/**
* @inheritDoc
*/
public function init()
{
parent::init();
foreach ($this->columns as $key => $column) {
if (is_array($column)) {
$this->columns[$key] = \Yii::createObject(ArrayHelper::merge(['activeDataProvider' => $this->activeDataProvider], $column));
}
}
$this->activeDataProvider->pagination = ['defaultPageSize' => false, 'pageSizeLimit' => false];
$this->reader = \PHPExcel_IOFactory::createReader(\PHPExcel_IOFactory::identify($this->filename));
$this->reader->setReadDataOnly(true);
$this->phpExcel = $this->reader->load($this->filename);
$this->models = ArrayHelper::map($this->activeDataProvider->getModels(), 'primaryKey', function ($item) {
return $item;
});
}
public function importXls($fileDestination)
{
$this->createSeoFilter();
$this->loadLanguages();
try {
$this->reader = PHPExcel_IOFactory::createReader(PHPExcel_IOFactory::identify($fileDestination));
$this->reader->setReadDataOnly(true);
$this->excel = $this->reader->load($fileDestination);
} catch (Exception $exc) {
//echo $exc->getTraceAsString();
$this->errors['ERR_IMPORT_XLS'] = 'Error reading the file, incorrect format';
return false;
}
$this->truncateData();
return $this->importSheets();
}
/**
* Create a XLS template
*
* @param string $group Group Name to include in template
* @param string $issue Issue title to include in template
* @param boolean $to_string return template as string or write to file
*
* @return list($xlsname, $xlsfile) $xlsname: name of xls file for client /
* $xlsfile: filepath to xls file or string containing xls file
*/
function createTemplate($group, $issue, $to_string = false, $format = "Excel5")
{
global $tmpfiles;
// get extension and make sure it's a supported type
switch ($format) {
case 'Excel2007':
$ext = 'xlsx';
break;
case 'Excel5':
default:
$ext = 'xls';
$format = 'Excel5';
break;
}
// load template
$inputFileType = PHPExcel_IOFactory::identify(XLS_TEMPLATE);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load(XLS_TEMPLATE);
$sheet = $objPHPExcel->getSheet(0);
$objRichText = new PHPExcel_RichText();
$objBold = $objRichText->createTextRun(ISSUE_TAG);
$objBold->getFont()->setBold(true);
$objRichText->createText($issue->Title);
$sheet->getCell(ISSUE_CELL)->setValue($objRichText);
$objRichText = new PHPExcel_RichText();
$objBold = $objRichText->createTextRun(GROUP_TAG);
$objBold->getFont()->setBold(true);
$objRichText->createText($group->Name);
$sheet->getCell(GROUP_CELL)->setValue($objRichText);
$objRichText = new PHPExcel_RichText();
$objRichText->createText($issue->Description);
$sheet->setCellValue(DESCRIPTION_CELL, $objRichText);
$sheet->setSelectedCells(DATA_COLUMN_STATEMENT . DATA_ROW_MIN);
$objPHPExcel->getProperties()->setCreator("IC-Discuss");
$xlsname = Utils::sanitizeFilename(APP_TITLE . ' - ' . $group->Name . ' - ' . $issue->Title) . '.' . $ext;
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $format);
if ($to_string) {
ob_start();
$objWriter->save('php://output');
$xlsfile = ob_get_clean();
} else {
$xlsfile = tempnam("tmp", $ext);
$tmpfiles[] = $xlsfile;
$objWriter->save($xlsfile);
}
return array($xlsname, $xlsfile);
}
/**
* 读取一个excel并把内容放入到一个数组中
* @param string $excel_file
* @param integer $sheet_id
* @param array $headers
* @return array
*/
public function readExcel($excel_file, $sheet_id, $headers, $contain_all_column = true)
{
$excel_type = PHPExcel_IOFactory::identify($excel_file);
$excel_reader = PHPExcel_IOFactory::createReader($excel_type);
if (method_exists($excel_reader, "setReadDataOnly")) {
$excel_reader->setReadDataOnly(true);
}
$excel = $excel_reader->load($excel_file);
$count = $excel->getSheetCount();
if ($count < $sheet_id) {
return "无效的excel，工作表个数不对";
} else {
$sheet = $excel->getSheet($sheet_id);
$data = self::readSheet($sheet, $headers, $contain_all_column);
return $data;
}
}
