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');
}
public function __construct($options)
{
set_time_limit(0);
ini_set('memory_limit', '512M');
glz_importApplicationLib('PHPExcel/Classes/PHPExcel/IOFactory.php');
$csv = new PHPExcel_Reader_CSV();
$csv->setDelimiter($options['delimiter']);
$csv->setEnclosure($options['enclosure']);
$excel = $csv->load($options['filePath']);
$this->worksheet = $excel->getActiveSheet();
}
function file_to_obj_php_excel($inputFileName)
{
$CI =& get_instance();
PHPExcel_Shared_File::setUseUploadTempDirectory(true);
if ($CI->config->item('spreadsheet_format') == 'XLSX') {
$objReader = new PHPExcel_Reader_Excel2007();
} else {
$objReader = new PHPExcel_Reader_CSV();
PHPExcel_Cell::setValueBinder(new TextValueBinder());
}
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
return $objPHPExcel;
}
public function run($args)
{
Yii::import('application.components.PHPExcel.PHPExcel.Reader.PHPExcel_Reader_CSV');
Yii::import('application.components.PHPExcel.PHPExcel.Writer.PHPExcel_Writer_CSV');
Yii::import('application.modules.store.models');
$attributesInputFileName = Yii::getPathOfAlias('application.components.spreadsheetReader.translates') . '/' . 'site_store_attribute.csv';
$objReader = new PHPExcel_Reader_CSV();
$objPHPExcel = $objReader->load($attributesInputFileName);
$db = \Yii::app()->db;
$attributes = $db->createCommand()->select('id, title_ru')->from('site_store_attribute')->queryAll();
$attributesData = [];
foreach ($attributes as $attribute) {
$attributesData[] = ['id' => $attribute['id'], 'title_ru' => $attribute['title_ru']];
}
$attributesRow = 1;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'ID')->setCellValue('B1', 'Название');
$attributesRow++;
foreach ($attributesData as $value) {
$objPHPExcel->getActiveSheet()->setCellValue('A' . $attributesRow, $value['id'])->setCellValue('B' . $attributesRow, $value['title_ru']);
$attributesRow++;
}
$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
$objWriter->setDelimiter(';');
$objWriter->setLineEnding("\r\n");
$objWriter->setSheetIndex(0);
$objWriter->setUseBOM(true);
$objWriter->save($attributesInputFileName);
$optionsInputFileName = Yii::getPathOfAlias('application.components.spreadsheetReader.translates') . '/' . 'site_store_attribute_option.csv';
$objReaderOptions = new PHPExcel_Reader_CSV();
$objPHPExcelOptions = $objReaderOptions->load($optionsInputFileName);
$attributesOptions = $db->createCommand()->select('id, value_ru')->from('site_store_attribute_option')->queryAll();
$attributesOptionsData = [];
foreach ($attributesOptions as $attributeOption) {
$attributesOptionsData[] = ['id' => $attributeOption['id'], 'value_ru' => $attributeOption['value_ru']];
}
$attributesOptionsRow = 1;
$objPHPExcelOptions->setActiveSheetIndex(0)->setCellValue('A1', 'ID')->setCellValue('B1', 'Название');
$attributesOptionsRow++;
foreach ($attributesOptionsData as $value) {
$objPHPExcelOptions->getActiveSheet()->setCellValue('A' . $attributesOptionsRow, $value['id'])->setCellValue('B' . $attributesOptionsRow, $value['value_ru']);
$attributesOptionsRow++;
}
$objWriterOptions = new PHPExcel_Writer_CSV($objPHPExcelOptions);
$objWriterOptions->setDelimiter(';');
$objWriterOptions->setLineEnding("\r\n");
$objWriterOptions->setSheetIndex(0);
$objWriterOptions->setUseBOM(true);
$objWriterOptions->save($optionsInputFileName);
}
/**
* Read given csv file and write all rows to given xls file
*
* @param string $csv_file Resource path of the csv file
* @param string $xls_file Resource path of the excel file
* @param string $csv_enc Encoding of the csv file, use utf8 if null
* @throws Exception
*/
public static function convert($csv_file, $xls_file, $csv_enc = null)
{
if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
$cacheSettings = array('dir' => 'E:/PHPExcel_cache');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
} else {
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
}
//open csv file
$objReader = new PHPExcel_Reader_CSV();
$objReader->setDelimiter(CSVToExcelConverter::guessDelimiter($csv_file));
if ($csv_enc != null) {
$objReader->setInputEncoding($csv_enc);
}
$objPHPExcel_CSV = $objReader->load($csv_file);
$in_sheet = $objPHPExcel_CSV->getActiveSheet();
//open excel file
$objPHPExcel_XLSX = new PHPExcel();
$out_sheet = $objPHPExcel_XLSX->getActiveSheet();
$out_sheet->setTitle('File Content');
//row index start from 1
$row_index = 0;
foreach ($in_sheet->getRowIterator() as $row) {
//if($row_index==20)
// break;
$row_index++;
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
//column index start from 0
$column_index = -1;
foreach ($cellIterator as $cell) {
$column_index++;
$out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell->getValue());
}
}
//write excel file
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel_XLSX);
$objWriter->save($xls_file);
}
/**
* 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
}
public function convertCSV_MTS()
{
$file = trim($this->input->post('filename'));
$inputFileName = "application/csv/mts/" . $file;
$objReader = new PHPExcel_Reader_CSV();
$objReader->setInputEncoding('CP1251');
$objReader->setDelimiter(';');
//$objReader->setEnclosure('');
$objPHPExcel = $objReader->load($inputFileName);
$objPHPExcel->getActiveSheet(0)->insertNewRowBefore(1);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'resource')->setCellValue('B1', 'amount')->setCellValue('C1', 'date')->setCellValue('D1', 'assortment');
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
//var_dump($sheetData);
foreach ($sheetData as $rows) {
echo $rows['A'] . ' ' . $rows['B'] . ' ' . $rows['C'] . ' ' . $rows['D'];
}
$loadedSheetNames = $objPHPExcel->getSheetNames();
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
foreach ($loadedSheetNames as $sheetIndex => $loadedSheetName) {
$objWriter->setSheetIndex($sheetIndex);
$objWriter->save('application/csv/mts/file_' . date('Y-m-d', now()) . '.csv');
unlink($inputFileName);
}
}
/**
* Set CSV enclosure.
*
* @param $enclosure
*
* @return Reader
*/
public function setEnclosure($enclosure)
{
$this->reader->setEnclosure($enclosure);
return $this;
}
public function actionReadNumberExcel()
{
sleep(2);
Yii::$app->response->format = \yii\web\Response::FORMAT_JSON;
$request = Yii::$app->request;
$fileB64 = $request->post('fileB64');
$content = $request->post('content');
$modelGroupId = $request->post('modelGroupId');
//delete all sms model by groupId
//根据groupid删除各自短信模板 但是导入之后前台刷新会丢失groupid 会造成数据库数据累积
//表中含有accountid字段可以根据该字段删除所有模板
if ($modelGroupId != -1) {
SmsModel::deleteAll(['groupId' => $modelGroupId]);
}
$file = base64_decode(substr($fileB64, strpos($fileB64, ";base64,") + 8));
$filePath = Yii::getAlias('@runtime') . '/numberExcel' . date('his') . '.csv';
file_put_contents($filePath, $file);
//csv 由xlsx转换 第一列为号码 第二列替换param1 第三列param2
$phpReader = new \PHPExcel_Reader_Excel2007();
if (!$phpReader->canRead($filePath)) {
$phpReader = new \PHPExcel_Reader_Excel5();
if (!$phpReader->canRead($filePath)) {
$phpReader = new \PHPExcel_Reader_CSV();
if (!$phpReader->canRead($filePath)) {
unlink($filePath);
return ['fileError' => true];
}
}
}
$phpExcel = $phpReader->load($filePath);
$sheets = $phpExcel->getAllSheets();
$recordCount = -1;
for ($si = 0; $si < sizeof($sheets); $si++) {
$sheet = $sheets[$si];
$ingredientFinished = false;
$rowCount = $sheet->getHighestRow();
$recordCount = $rowCount;
$highestCol = $sheet->getHighestColumn();
// LogUtil::error(date('Y-m-d h:i:s') . ' $rowCount: ' . $rowCount . ' $highestCol: ' . $highestCol);
$colCount = ord($highestCol) - 65;
$currentGroupId = $this->guid();
// LogUtil::error(date('Y-m-d h:i:s') . ' $getAccountId: ' . $this->getAccountId());
for ($row = 1; $row <= $rowCount; $row++) {
$realModel = $content;
$number = '';
for ($col = 0; $col <= $colCount; $col++) {
$val = $sheet->getCellByColumnAndRow($col, $row)->getValue();
$val = trim((string) $val);
if ($val === '') {
continue;
}
if ($col == 0) {
$number = $val;
}
if ($col > 0) {
$realModel = str_replace('%param' . $col . '%', $val, $realModel);
}
}
//如果存在空白行则会把模板存入
$SmsModel = new SmsModel();
$SmsModel->groupId = $currentGroupId;
$SmsModel->mobile = $number;
$SmsModel->content = $realModel;
$SmsModel->accountId = $this->getAccountId();
$SmsModel->save();
}
}
LogUtil::error(date('Y-m-d h:i:s') . ' $modelGroupId: ' . $currentGroupId);
return ['recordCount' => $recordCount, 'modelGroupId' => $currentGroupId];
}
<?php
$user_id = $_SESSION['userID'] ? $_SESSION['userID'] : 0;
if (!empty($_FILES['fileToUpload'])) {
$inputFileName = $_FILES['fileToUpload']['tmp_name'];
/** PHPExcel_IOFactory - Reader */
include _DOC_ROOT_ . 'lib/ClassesPHPExel/PHPExcel/IOFactory.php';
// Read CSV
$objReader = new PHPExcel_Reader_CSV();
// สร้าง object ของ Class PHPExcel_Reader_CSV
$objReader->setInputEncoding('CP1252');
// กำหนดค่าต่างตามนี้
$objReader->setDelimiter(',');
$objReader->setEnclosure('');
$objReader->setLineEnding("\r\n");
$objReader->setSheetIndex(0);
$objPHPExcel = $objReader->load($inputFileName);
//<====File Path
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$headingsArray = $objWorksheet->rangeToArray('A1:' . $highestColumn . '1', null, true, true, true);
$headingsArray = $headingsArray[1];
$r = -1;
$dataArray = 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;
$loop = 0;
foreach ($headingsArray as $columnKey => $columnHeading) {
/**
* @author caochunhui@dachuwang.com
* @description 把输出目录里的csv都合成到一个统一的excel文件里
* 每一个csv都占据一个单独的sheet，方便打印
*/
private function _make_excel($dir = '', $out_name, $csv_enc = null)
{
//下面的代码是抄的。
//set cache
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
//open excel file
$write_objPHPExcel = new PHPExcel();
//open csv file
$objReader = new PHPExcel_Reader_CSV();
if ($csv_enc != null) {
$objReader->setInputEncoding($csv_enc);
}
//下面要循环了
$list_arr = get_filenames($dir);
sort($list_arr);
$sheet_cnt = 0;
foreach ($list_arr as $idx => $item) {
if (strpos($item, '.csv') != FALSE) {
//用订单id.csv来命名每一个sheet
$out_sheet = new PHPExcel_Worksheet($write_objPHPExcel, $item);
//$out_sheet->setTitle($item);
$read_objPHPExcel = $objReader->load($dir . $item);
$in_sheet = $read_objPHPExcel->getActiveSheet();
//row index start from 1
$row_index = 0;
foreach ($in_sheet->getRowIterator() as $row) {
$row_index++;
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
//column index start from 0
$column_index = -1;
foreach ($cellIterator as $cell) {
$column_index++;
$out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell->getValue());
}
}
$write_objPHPExcel->addSheet($out_sheet);
}
}
//上面要循环了
//上面的代码是抄的
//write excel file
$objWriter = new PHPExcel_Writer_Excel2007($write_objPHPExcel);
$objWriter->save($out_name);
}
public function actionToCookbook()
{
sleep(2);
Yii::$app->response->format = \yii\web\Response::FORMAT_JSON;
$request = Yii::$app->request;
$fileB64 = $request->post('fileB64');
$file = base64_decode(substr($fileB64, strpos($fileB64, ";base64,") + 8));
$filePath = Yii::getAlias('@runtime') . '/cookbook' . date('his');
file_put_contents($filePath, $file);
$phpReader = new \PHPExcel_Reader_Excel2007();
if (!$phpReader->canRead($filePath)) {
$phpReader = new \PHPExcel_Reader_Excel5();
if (!$phpReader->canRead($filePath)) {
$phpReader = new \PHPExcel_Reader_CSV();
if (!$phpReader->canRead($filePath)) {
unlink($filePath);
return ['fileError' => true];
}
}
}
$phpExcel = $phpReader->load($filePath);
$sheets = $phpExcel->getAllSheets();
$cookbookTitles = [];
for ($si = 0; $si < sizeof($sheets); $si++) {
$sheet = $sheets[$si];
$rowTemp = [];
$cowTemp = [];
$ingredientFinished = false;
$rowCount = $sheet->getHighestRow();
$highestCol = $sheet->getHighestColumn();
$colCount = ord($highestCol) - 65;
$cookbook = [];
//There has a bug
//When the 'cuisineType' row does not exist, the $rowCount will be infinity
//The code blow can avoid this bug
$rowCount = $rowCount > 100 ? 100 : $rowCount;
for ($row = 1; $row <= $rowCount; $row++) {
for ($col = 0; $col <= $colCount; $col++) {
$val = $sheet->getCellByColumnAndRow($col, $row)->getValue();
$val = trim((string) $val);
if ($val === '') {
continue;
}
// Fill title and image
if (!isset($cookbook['title'])) {
$arr = explode('-', $val, 2);
if (empty($arr) || sizeof($arr) < 2) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: title is required');
return ['contentError' => true];
}
if (mb_strlen(trim(trim($arr[1])), 'utf-8') > 30) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: title should less than 30 words');
return ['titleLengthError' => true];
}
$cookbook['image'] = Yii::$app->qiniu->domain . '/' . trim($arr[0]) . '.jpg';
$cookbook['title'] = trim(trim($arr[1]));
unset($arr);
continue;
}
// Find category row
if (!isset($rowTemp['category'])) {
if (!preg_match('/^category$/i', $val)) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: category is required');
return ['contentError' => true];
}
$rowTemp['category'] = $row;
continue;
}
// Fill category
if ($rowTemp['category'] === $row) {
//The first sheet's category row will leads to a bug
if ($si == 0) {
$firstCate = $val;
}
$arr = $this->_spiltByComma($val);
// $arr = preg_split('/[,，]/', $val);
$cookbook['category'] = [];
foreach ($arr as $v) {
$v = trim($v);
if ($v != '') {
$cookbook['category'][] = trim($v);
}
}
$row++;
$col = -1;
unset($arr);
continue;
}
// Find subCategory row
if (!isset($rowTemp['subCategory'])) {
if (!preg_match('/^sub[\\s\\n]*category$/i', $val)) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: subCategory is required');
return ['contentError' => true];
}
$rowTemp['subCategory'] = $row;
continue;
}
// Fill subCategory
if ($rowTemp['subCategory'] === $row) {
// $arr = preg_split('/[,，]/', $val);
$arr = $this->_spiltByComma($val);
$cookbook['subCategory'] = [];
foreach ($arr as $v) {
$v = trim($v);
if ($v != '') {
$cookbook['subCategory'][] = trim($v);
}
}
$row++;
$col = -1;
unset($arr);
continue;
}
// Find cuisineType row
if (!isset($rowTemp['cuisineType'])) {
if (preg_match('/^cuisine[\\s\\n]*type$/i', $val)) {
$rowTemp['cuisineType'] = $row;
continue;
} else {
$rowTemp['cuisineType'] = '';
}
}
// Fill cuisineType
if ($rowTemp['cuisineType'] === $row) {
// $arr = preg_split('/[,，]/', $val);
$arr = $this->_spiltByComma($val);
$cookbook['cuisineType'] = [];
foreach ($arr as $v) {
$v = trim($v);
if ($v != '') {
$cookbook['cuisineType'][] = trim($v);
}
}
$row++;
$col = -1;
unset($arr);
continue;
}
// Find yield row
if (!isset($rowTemp['yield'])) {
if (!preg_match('/^yield$/i', $val)) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: yield is required');
return ['contentError' => true];
}
$rowTemp['yield'] = $row;
continue;
}
// Fill yield
if ($rowTemp['yield'] === $row) {
if (!isset($cookbook['yield'])) {
$cookbook['yield'] = [];
$cookbook['yield']['Quantity'] = $val;
} else {
$cookbook['yield']['unit'] = $val;
}
continue;
}
// Find portionSize row
if (!isset($rowTemp['portionSize'])) {
if (!preg_match('/^portion[\\s\\n]*size$/i', $val)) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: portionSize is required');
return ['contentError' => true];
}
$rowTemp['portionSize'] = $row;
continue;
}
// Fill portionSize
if ($rowTemp['portionSize'] === $row) {
$cookbook['portionSize'] = $val;
$row++;
$col = -1;
continue;
}
//Find ingredient quantity colume
if (!isset($colTemp['idtQuantity'])) {
if (!preg_match('/^quantity$/i', $val)) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: ingredient quantity is required');
return ['contentError' => true];
}
$colTemp['idtQuantity'] = $col;
continue;
}
//Find ingredient unit colume
if (!isset($colTemp['idtUnit'])) {
if (!preg_match('/^unit$/i', $val)) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: ingredient unit is required');
return ['contentError' => true];
}
$colTemp['idtUnit'] = $col;
continue;
}
//Find ingredient name colume
if (!isset($colTemp['idtName'])) {
if (!preg_match('/^ingredient[\\s\\n]*name$/i', $val)) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: ingredient name is required');
return ['contentError' => true];
}
$colTemp['idtName'] = $col;
continue;
}
//Fill ingredient
if (!isset($cookbook['ingredient'])) {
$cookbook['ingredient'] = [];
}
if (!$ingredientFinished) {
// Fill ingredient quantity
if ($col === $colTemp['idtQuantity']) {
$cookbook['ingredient'][$row]['quantity'] = $val;
}
// Fill ingredient unit
if ($col === $colTemp['idtUnit']) {
$cookbook['ingredient'][$row]['unit'] = $val;
}
// Fill ingredient name
if ($col === $colTemp['idtName']) {
$cookbook['ingredient'][$row]['name'] = $val;
}
$ingredientFinished = preg_match('/^preparation[\\s\\n]*method$/i', $val);
if ($ingredientFinished) {
array_pop($cookbook['ingredient']);
}
continue;
}
// Find preparation method description colume
if (!isset($colTemp['ptnDescription'])) {
if (preg_match('/^description$/i', $val)) {
$colTemp['ptnDescription'] = $col;
}
continue;
}
//Fill preparation method
if (!isset($cookbook['preparationMethod'])) {
$cookbook['preparationMethod'] = [];
}
// Fill preparation method description
if ($col === $colTemp['ptnDescription']) {
$cookbook['preparationMethod'][$row]['description'] = [];
$arr = preg_split('/靈感來源\\s*or\\s*貼心小提示/i', $val);
if (empty($arr) || sizeof($arr) !== 2) {
$cookbook['preparationMethod'][$row]['description']['step'] = $val;
$cookbook['preparationMethod'][$row]['description']['creativeExperience'] = '';
} else {
$cookbook['preparationMethod'][$row]['description']['step'] = trim($arr[0]);
$cookbook['preparationMethod'][$row]['description']['creativeExperience'] = trim($arr[1]);
}
unset($arr);
}
}
}
if (!isset($cookbook['ingredient']) || !isset($cookbook['preparationMethod'])) {
unlink($filePath);
return ['contentError' => true];
}
$cookbook['ingredient'] = array_values($cookbook['ingredient']);
$tmpInfo = $this->_findProductUrlAndSave($cookbook['ingredient']);
$cookbook['ingredient'] = $tmpInfo['ingredients'];
unset($tmpInfo);
$cookbook['preparationMethod'] = array_values($cookbook['preparationMethod']);
for ($i = 0; $i < sizeof($cookbook['ingredient']); $i++) {
$cookbook['ingredient'][$i]['id'] = $this->_getRandomId();
}
$cookbook['content'] = $cookbook['preparationMethod'][0]['description']['step'];
$cookbook['creativeExperience'] = $cookbook['preparationMethod'][0]['description']['creativeExperience'];
$cookbooks[] = $cookbook;
unset($rowTemp);
unset($colTemp);
unset($cookbook);
}
unlink($filePath);
if (empty($cookbooks)) {
return [];
}
$results = Cookbook::saveImportedCookbooks($cookbooks, $this->getAccountId());
$cookbookBatch = new CookbookBatch();
$accessToken = Token::getToken();
$user = User::findOne(['_id' => $accessToken->userId]);
$cookbookBatch->operator = $user->name;
$cookbookBatch->cookbooks = $results;
$cookbookBatch->hasImages = false;
$cookbookBatch->accountId = $this->getAccountId();
$cookbookBatch->createdTime = new \MongoDate();
$cookbookBatch->insert();
return sizeof($results);
}
public function fromArray($resource, array $options = [])
{
$options = array_merge(['delimiter' => ',', 'enclosure' => '"', 'escape' => '\\', 'input_encoding' => 'UTF-8', 'parse_file' => false, 'output_format' => 'xlsx'], $options);
$resource = parent::getRealResource($resource, $options);
if (!is_string($resource) || !is_file($resource)) {
$csv = new Csv($options);
$resource = parent::tempStore(uniqid(), $csv->fromArray($resource, $options));
}
PhpExcelNsWrapper::init();
$objReader = new \PHPExcel_Reader_CSV();
$objReader->setInputEncoding($options['input_encoding']);
$objReader->setDelimiter($options['delimiter']);
$objReader->setEnclosure($options['enclosure']);
$objReader->setLineEnding(false !== strpos($resource, "\r\n") ? "\r\n" : "\n");
$objReader->setSheetIndex(0);
$objPHPExcel = $objReader->load($resource);
// The following statement is borrowed from PHPExcel_IOFactory
switch (strtolower($options['output_format'])) {
case 'xlsx':
// Excel (OfficeOpenXML) Spreadsheet
// Excel (OfficeOpenXML) Spreadsheet
case 'xlsm':
// Excel (OfficeOpenXML) Macro Spreadsheet (macros will be discarded)
// Excel (OfficeOpenXML) Macro Spreadsheet (macros will be discarded)
case 'xltx':
// Excel (OfficeOpenXML) Template
// Excel (OfficeOpenXML) Template
case 'xltm':
// Excel (OfficeOpenXML) Macro Template (macros will be discarded)
$extensionType = 'Excel2007';
break;
case 'xls':
// Excel (BIFF) Spreadsheet
// Excel (BIFF) Spreadsheet
case 'xlt':
// Excel (BIFF) Template
$extensionType = 'Excel5';
break;
case 'ods':
// Open/Libre Offic Calc
// Open/Libre Offic Calc
case 'ots':
// Open/Libre Offic Calc Template
$extensionType = 'OOCalc';
break;
case 'slk':
$extensionType = 'SYLK';
break;
case 'xml':
// Excel 2003 SpreadSheetML
$extensionType = 'Excel2003XML';
break;
case 'gnumeric':
$extensionType = 'Gnumeric';
break;
case 'htm':
case 'html':
$extensionType = 'HTML';
break;
case 'csv':
// Do nothing
// This must be handled by the CSV class
break;
default:
break;
}
if (is_null($extensionType)) {
throw new JigException('Unable to identify a reader for this file');
}
$writer = \PHPExcel_IOFactory::createWriter($objPHPExcel, $extensionType);
ob_start();
$writer->save('php://output');
$spreadsheet = ob_get_contents();
ob_end_clean();
return $spreadsheet;
}
/**
* @param $type
* @return \PHPExcel_Reader_CSV|\PHPExcel_Reader_Excel2007|\PHPExcel_Reader_Excel5|\PHPExcel_Reader_OOCalc
* @throws \PHPExcel_Exception
* @codeCoverageIgnore
*/
private static function getReader($type)
{
switch ($type) {
case 'xls':
$objReader = new \PHPExcel_Reader_Excel5();
break;
case 'xlsx':
$objReader = new \PHPExcel_Reader_Excel2007();
break;
case 'ods':
$objReader = new \PHPExcel_Reader_OOCalc();
break;
case 'csv':
$objReader = new \PHPExcel_Reader_CSV();
break;
default:
throw new \PHPExcel_Exception("Unsupported spreadsheet type {$type}");
}
if ($type != 'csv') {
//we don't need format
$objReader->setReadDataOnly(true);
}
return $objReader;
}
private static function prepare_reader($path_to_spreadsheet)
{
$info = pathinfo($path_to_spreadsheet);
$extension = $info['extension'];
if ($extension == "xls") {
$excel_reader = \PHPExcel_IOFactory::createReader('Excel5');
} elseif ($extension == "xlsx") {
$excel_reader = \PHPExcel_IOFactory::createReader('Excel2007');
} elseif ($extension == "zip") {
$excel_reader = \PHPExcel_IOFactory::createReader('Excel2007');
} elseif ($extension == "csv") {
$excel_reader = new \PHPExcel_Reader_CSV();
}
if (!$excel_reader->canRead($path_to_spreadsheet)) {
throw new \Exception('Cannot read this file');
}
if ($extension != "csv") {
$excel_reader->setReadDataOnly(true);
}
$objPHPExcel = $excel_reader->load($path_to_spreadsheet);
return $objPHPExcel;
}
public function excelBasedConstruct($xls_url, $wdtParameters = array())
{
ini_set("memory_limit", "2048M");
if (!$xls_url) {
throw new WDTException('Excel file not found!');
}
if (!file_exists($xls_url)) {
throw new WDTException('Provided file ' . stripcslashes($xls_url) . ' does not exist!');
}
require_once WDT_ROOT_PATH . '/lib/phpExcel/PHPExcel.php';
$objPHPExcel = new PHPExcel();
if (strpos(strtolower($xls_url), '.xlsx')) {
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.xls')) {
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.ods')) {
$objReader = new PHPExcel_Reader_OOCalc();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.csv')) {
$objReader = new PHPExcel_Reader_CSV();
} else {
throw new WDTException('File format not supported!');
}
$objPHPExcel = $objReader->load($xls_url);
$objWorksheet = $objPHPExcel->getActiveSheet();
$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 $dataColumnIndex => $dataColumnHeading) {
$namedDataArray[$r][$dataColumnHeading] = $dataRow[$row][$dataColumnIndex];
if (WDT_DETECT_DATES_IN_EXCEL) {
$cellID = $dataColumnIndex . $row;
if (PHPExcel_Shared_Date::isDateTime($objPHPExcel->getActiveSheet()->getCell($cellID))) {
$namedDataArray[$r][$dataColumnHeading] = PHPExcel_Shared_Date::ExcelToPHP($dataRow[$row][$dataColumnIndex]);
}
}
}
}
}
$namedDataArray = apply_filters('wpdatatables_filter_excel_array', $namedDataArray, $this->getWpId(), $xls_url);
return $this->arrayBasedConstruct($namedDataArray, $wdtParameters);
}
public function convert_sheet_to_array($spreadsheet, $sheet = NULL, $startRow = NULL, $save_params = false)
{
require_once DOC_ROOT . '/vendor/PHPExcel/Classes/PHPExcel.php';
if (!isset($this->open_spreadsheets)) {
$this->open_spreadsheets = array();
}
$temp = explode('.', $spreadsheet);
//to avoid E_STRICT warning - only variables can be passed by reference
$ext = strtolower(end($temp));
if (isset($this->open_spreadsheets['spreadsheet'])) {
$objPHPExcel = $this->open_spreadsheets['spreadsheet'];
} else {
if ($ext == "xls") {
$objReader = \PHPExcel_IOFactory::createReader('Excel5');
} elseif ($ext == "xlsx") {
$objReader = \PHPExcel_IOFactory::createReader('Excel2007');
} elseif ($ext == "zip") {
$objReader = \PHPExcel_IOFactory::createReader('Excel2007');
} elseif ($ext == "csv") {
$objReader = new \PHPExcel_Reader_CSV();
}
if ($ext != "csv") {
$objReader->setReadDataOnly(true);
}
$objPHPExcel = $objReader->load($spreadsheet);
$this->open_spreadsheets['spreadsheet'] = $objPHPExcel;
}
if (is_null($sheet)) {
$objWorksheet = $objPHPExcel->getActiveSheet();
} else {
if ($sheet + 1 > $objPHPExcel->getSheetCount()) {
return false;
}
$objWorksheet = $objPHPExcel->setActiveSheetIndex($sheet);
}
$highestRow = $objWorksheet->getHighestRow();
// e.g. 10
$highestColumn = $objWorksheet->getHighestColumn();
// e.g 'F'
$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
// e.g. 5
$sheet_label = array();
$sheet_value = array();
if (is_null($startRow)) {
$startRow = 1;
}
if ($save_params) {
$FILE = Functions::file_open($save_params['path'] . "/" . $save_params['worksheet_title'] . ".txt", 'w');
}
for ($row = $startRow; $row <= $highestRow; ++$row) {
if ($save_params) {
$saved_row = array();
}
for ($col = 0; $col <= $highestColumnIndex; ++$col) {
$cell = self::cell_value($objWorksheet, $col, $row, $ext);
if ($row == $startRow) {
$sheet_label[] = $cell;
if ($save_params) {
$saved_row[] = $cell;
}
} else {
$index = trim($sheet_label[$col]);
if ($index) {
if ($save_params) {
$saved_row[] = $cell;
} else {
$sheet_value[$index][] = $cell;
}
}
}
}
if ($save_params) {
fwrite($FILE, implode("\t", $saved_row) . "\n");
}
}
if ($save_params) {
fclose($FILE);
}
return $sheet_value;
}
/**
* Reads the data from file in the DB and generates a wpDataTable
*/
public function readFileData($table_data)
{
$columnTypes = array();
if (!empty($table_data['file'])) {
$xls_url = urldecode($table_data['file']);
if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
$xls_url = str_replace(site_url(), str_replace('\\', '/', ABSPATH), $xls_url);
} else {
$xls_url = str_replace(site_url(), ABSPATH, $xls_url);
}
} else {
return _('Empty file', 'wpdatatables');
}
for ($i = 0; $i < count($table_data['columns']); $i++) {
if ($table_data['columns'][$i]['orig_header'] == '%%NEW_COLUMN%%') {
$table_data['columns'][$i]['orig_header'] = 'column' . $i;
}
$columnTypes[$table_data['columns'][$i]['orig_header']] = $table_data['columns'][$i]['type'];
}
$this->_id = $this->generateManualTable($table_data);
require_once WDT_ROOT_PATH . '/lib/phpExcel/PHPExcel.php';
$objPHPExcel = new PHPExcel();
if (strpos(strtolower($xls_url), '.xlsx')) {
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.xls')) {
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.ods')) {
$objReader = new PHPExcel_Reader_OOCalc();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.csv')) {
$objReader = new PHPExcel_Reader_CSV();
} else {
return _('File format not supported!', 'wpdatatables');
}
$objPHPExcel = $objReader->load($xls_url);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$headingsArray = $objWorksheet->rangeToArray('A1:' . $highestColumn . '1', null, true, true, true);
$headingsArray = $headingsArray[1];
$r = -1;
$insertArray = array();
// Insert statement default beginning
$insert_statement_beginning = "INSERT INTO " . $this->_name . " (" . implode(', ', array_values($this->_column_headers)) . ") ";
$insert_blocks = array();
for ($row = 2; $row <= $highestRow; ++$row) {
// Set all cells in the row to their defaults
foreach ($table_data['columns'] as $column) {
$insertArray[$this->_column_headers[$column['orig_header']]] = "'" . esc_sql($column['default_value']) . "'";
}
$dataRow = $objWorksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, true, true);
if (isset($dataRow[$row]['A']) && $dataRow[$row]['A'] > '') {
++$r;
foreach ($headingsArray as $dataColumnIndex => $dataColumnHeading) {
if (!in_array($dataColumnHeading, array_keys($this->_column_headers))) {
continue;
}
if ($columnTypes[$dataColumnHeading] != 'date') {
$insertArray[$this->_column_headers[$dataColumnHeading]] = "'" . esc_sql($dataRow[$row][$dataColumnIndex]) . "'";
} else {
if ($objReader instanceof PHPExcel_Reader_CSV) {
$date = strtotime(str_replace('/', '-', $dataRow[$row][$dataColumnIndex]));
} else {
$date = esc_sql(PHPExcel_Shared_Date::ExcelToPHP($dataRow[$row][$dataColumnIndex]));
}
$insertArray[$this->_column_headers[$dataColumnHeading]] = "'" . date('Y-m-d', $date) . "'";
}
}
}
$insert_blocks[] = '(' . implode(', ', $insertArray) . ')';
if ($row % 100 == 0) {
$this->insertRowsChunk($insert_statement_beginning, $insert_blocks);
$insert_blocks = array();
}
}
$this->insertRowsChunk($insert_statement_beginning, $insert_blocks);
}
public function run($args)
{
Yii::import('application.components.PHPExcel.PHPExcel.Reader.PHPExcel_Reader_CSV');
Yii::import('application.components.PHPExcel.PHPExcel.Writer.PHPExcel_Writer_CSV');
Yii::import('application.modules.dictionary.models');
$inputFileType = 'CSV';
$inputFileName = Yii::getPathOfAlias('application.components.spreadsheetReader.translates') . '/' . 'site_dictionary_dictionary_data.csv';
$objReader = new PHPExcel_Reader_CSV();
$objPHPExcel = $objReader->load($inputFileName);
$db = \Yii::app()->db;
$dictionaries = $db->createCommand()->select('id, group_id, name_ru')->from('site_dictionary_dictionary_data')->queryAll();
$data = [];
$group = [];
foreach ($dictionaries as $dictionary) {
$group = $db->createCommand()->select('name_ru')->from('site_dictionary_dictionary_group')->where('id=' . $dictionary['group_id'])->queryRow();
$data[] = ['id' => $dictionary['id'], 'group_name' => $group['name_ru'], 'name_ru' => $dictionary['name_ru']];
}
$row = 1;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'ID')->setCellValue('B1', 'Название справочника')->setCellValue('C1', 'Значение');
$row++;
foreach ($data as $value) {
$objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $value['id'])->setCellValue('B' . $row, $value['group_name'])->setCellValue('C' . $row, $value['name_ru']);
$row++;
}
$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
$objWriter->setDelimiter(';');
$objWriter->setLineEnding("\r\n");
$objWriter->setSheetIndex(0);
$objWriter->setUseBOM(true);
$objWriter->save($inputFileName);
}
