Пример #1
0
 /**
  * 获取数据
  * @param int $page 第几页数据
  */
 public function getData($page)
 {
     $this->_getReadFileObj();
     $phpExcel = $this->_instance->load($this->_filePath);
     $currentSheet = $phpExcel->getSheet($page);
     return $currentSheet->toArray();
 }
Пример #2
0
 /**
  * 读取excel信息
  * xls,xlsx,
  * 
  * @param string $fileName         文件绝对路径
  * @param bool $dataArea	       excel区域 
  *
  * @return array() | string
  */
 public static function readExcel($fileName = '', $contentType = false, $dataArea = false)
 {
     ini_set('memory_limit', '300M');
     $ext = strtolower(substr(strrchr($fileName, '.'), 1));
     //2003 excel
     if ($ext == 'xls') {
         $objReader = new PHPExcel_Reader_Excel5();
     }
     //2007 excel
     if ($ext == 'xlsx') {
         $objReader = new PHPExcel_Reader_Excel2007();
     }
     $objPHPExcel = $objReader->load($fileName);
     if ($dataArea == true) {
         $objPHPExcel->setActiveSheetIndex(3);
     }
     $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
     if ($contentType) {
         //返回字符串
         $str = '';
         foreach ($sheetData as $k => $v) {
             foreach ($v as $kk => $vv) {
                 $str .= $vv;
             }
         }
         return $str;
     }
     return $sheetData;
 }
Пример #3
0
 /**
  * @param $excelFileName
  * @param $excelFileExts
  * @return array
  * @throws \PHPExcel_Exception
  */
 private function _excelParse($excelFileName, $excelFileExts)
 {
     import("Org.Util.PHPExcel");
     import("Org.Util.PHPExcel.Shared.Date");
     $PHPDate = new \PHPExcel_Shared_Date();
     if ($excelFileExts == 'xls') {
         import("Org.Util.PHPExcel.Reader.Excel5");
         $PHPReader = new \PHPExcel_Reader_Excel5();
     } else {
         if ($excelFileExts == 'xlsx') {
             import("Org.Util.PHPExcel.Reader.Excel2007");
             $PHPReader = new \PHPExcel_Reader_Excel2007();
         }
     }
     $PHPExcel = $PHPReader->load($excelFileName);
     // 获取表中的第一个工作表,如果要获取第二个,把0改为1,依次类推
     $currentSheet = $PHPExcel->getSheet(0);
     // 获取总列数
     $allColumn = $currentSheet->getHighestColumn();
     // 获取总行数
     $allRow = $currentSheet->getHighestRow();
     // 循环获取表中的数据,$currentRow表示当前行,从哪行开始读取数据,索引值从0开始
     for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
         // 从哪列开始,A表示第一列
         for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
             // 数据坐标
             $address = $currentColumn . $currentRow;
             $excelData[$currentRow][$currentColumn] = (string) $currentSheet->getCell($address)->getValue();
         }
     }
     return ['path' => $excelFileName, 'data' => $excelData];
 }
 public function testSave()
 {
     $objReader = new \PHPExcel_Reader_Excel2007();
     $excel = $objReader->load('tests/metadata/template/simple.xlsx');
     $actual = $this->sut->save($excel, 'test.xlsx');
     $this->assertEquals('tests/metadata/template/test.xlsx', $actual);
     $this->assertFileExists('tests/metadata/template/test.xlsx');
 }
Пример #5
0
 public function getWorkbook($isExcelFile = false)
 {
     $data = $this->getReportData();
     $xls_reader = new PHPExcel_Reader_Excel2007();
     $workbook = $xls_reader->load(APPLICATION_PATH . 'modules/stat/templates/report6.xlsx');
     $sheet = $workbook->getActiveSheet();
     $sheet->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(2, 4);
     // В шаблоне пока не работает
     //    $sheet->getSheetView()->setZoomScale(120);
     $guide_vuz = $this->getEstablishments('GUIDE_STRUCTURE', 'SORTORDER');
     $guide_eduform = $this->getGuideItems('T_EDUFORM');
     $guide_speciality = $this->getSpecialities(false, false, 'CT.SORTORDER', 'SORTORDER');
     $eduFormStr = mb_strtolower($guide_eduform[$this->params['eduform']], 'utf8');
     $endDate = new DateTime($this->getAddValueDate($this->params['period'], '_ADD_ENDDATE'));
     $endDateStr = $endDate->format('d.m.Y');
     $sheet->setCellValue('A1', sprintf($sheet->getCell('A1')->getValue(), $endDateStr, $eduFormStr));
     $lastEstNum = -1;
     $vuznum = 1;
     $current_row = 5;
     $sheet->insertNewRowBefore($current_row + 1, count($data));
     foreach ($data as $datarow) {
         $curEstNum = $datarow['ESTABLISHMENT_NUM'];
         if ($lastEstNum != $curEstNum) {
             $lastEstNum = $curEstNum;
             if ($vuznum > 1) {
                 // Разделительная линия между вузами
                 $sheet->insertNewRowBefore($current_row + 1, 1);
                 $sheet->getRowDimension($current_row)->setRowHeight(0.75);
                 $current_row++;
             }
             $sheet->insertNewRowBefore($current_row + 1, 1);
             $sheet->setCellValueByColumnAndRow(0, $current_row, $vuznum);
             $sheet->setCellValueByColumnAndRow(1, $current_row, $guide_vuz[$curEstNum]);
             $sheet->mergeCellsByColumnAndRow(1, $current_row, 9, $current_row);
             $sheet->getStyle("B{$current_row}:J{$current_row}")->getFont()->setBold(true);
             $sheet->getStyle("A{$current_row}:J{$current_row}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($this->colorLevel1);
             $current_row++;
             $vuznum++;
         }
         //      $sheet->getRowDimension($current_row)->setOutlineLevel(1);//->setVisible(false);
         if (!$datarow['SPECIALITY_NUM']) {
             $sheet->setCellValueByColumnAndRow(1, $current_row, 'Вакантних місць немає');
             $sheet->mergeCellsByColumnAndRow(1, $current_row, 9, $current_row);
             $sheet->getStyle("B{$current_row}:J{$current_row}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
             $current_row++;
             continue;
         }
         $sheet->setCellValueByColumnAndRow(1, $current_row, $guide_speciality[$datarow['SPECIALITY_NUM']]);
         $sheet->setCellValueByColumnAndRow(2, $current_row, "=SUM(D{$current_row}:J{$current_row})");
         for ($col = 1; $col <= 7; $col++) {
             $sheet->setCellValueByColumnAndRow($col + 2, $current_row, $datarow["C{$col}"]);
         }
         $current_row++;
     }
     $sheet->removeRow($current_row, 1);
     return $workbook;
 }
 protected function import_data($parentFile, $childFile, $parentExts = 'xls', $childExts = 'xls')
 {
     //导入PHPExcel类库,因为PHPExcel没有用命名空间,只能inport导入
     import("Org.Util.PHPExcel");
     //创建PHPExcel对象,注意,不能少了\
     $PHPExcel = new \PHPExcel();
     //如果excel文件后缀名为.xls,导入这个Excel5类否则导入Excel2007
     if ($parentExts == 'xls' && $childExts == 'xls') {
         import("Org.Util.PHPExcel.Reader.Excel5");
         $PHPReader = new \PHPExcel_Reader_Excel5();
     } else {
         if ($parentExts == 'xlsx' && $childExts == 'xlsx') {
             import("Org.Util.PHPExcel.Reader.Excel2007");
             $PHPReader = new \PHPExcel_Reader_Excel2007();
         }
     }
     //载入上传的文件
     $PHPExcel = $PHPReader->load($parentFile);
     $ParentExcel = $PHPReader->load($parentFile);
     $ChildExcel = $PHPReader->load($childFile);
     //获取表中的第一个工作表,如果要获取第二个,把0改为1,依次类推
     $currentSheet = $PHPExcel->getSheet(0);
     $parentCurrentSheet = $ParentExcel->getSheet(0);
     $childCurrentSheet = $ChildExcel->getSheet(0);
     //获取工作表总列数
     $allColumn = $currentSheet->getHighestColumn();
     $parentAllColumn = $parentCurrentSheet->getHighestColumn();
     $childAllColumn = $childCurrentSheet->getHighestColumn();
     //获取工作表总行数
     $allRow = $currentSheet->getHighestRow();
     $parentAllRow = $parentCurrentSheet->getHighestRow();
     $childAllRow = $childCurrentSheet->getHighestRow();
     //循环获取表中的数据,$j表示当前行,从哪行开始读取数据,索引值从0开始
     for ($j = 2; $j <= $allRow; $j++) {
         $str = "";
         //从A列读取数据
         for ($k = 'A'; $k <= $allColumn; $k++) {
             $str .= $PHPExcel->getActiveSheet()->getCell("{$k}{$j}")->getValue() . ',';
         }
         //获取的字符串截取成数组
         $strs = explode(",", $str);
         $this->install_data($strs);
     }
 }
Пример #7
0
 /**
  * Reads the file into a PHPExcel object.
  * 
  * @param string $filename The file to parse.
  * 
  * @return \PHPExcel Returns the PHPExcel object for the file.
  * 
  * @throws ParseException when the reader encounters an error.
  */
 private static function readFile(string $filename) : \PHPExcel
 {
     try {
         $reader = new \PHPExcel_Reader_Excel2007();
         $reader->setReadDataOnly(true);
         $xlsx = $reader->load($filename);
     } catch (\Exception $e) {
         throw new ParseException($e->getMessage());
     }
     return $xlsx;
 }
Пример #8
0
 /**
 * 
 * @param string $filePath
 * @return
 */
 private function readExcel($filePath)
 {
     $filePath = $this->dir() . $filePath;
     if (!is_file($filePath)) {
         return FALSE;
     }
     $PHPReader = new \PHPExcel_Reader_Excel2007();
     if ($PHPReader->canRead($filePath)) {
         return $PHPReader->load($filePath);
     }
     return FALSE;
 }
 public function readExcel($inputFileName)
 {
     echo 'Loading file ', pathinfo($inputFileName, PATHINFO_BASENAME), ' using PHPExcel_Reader_Excel5<br />';
     //        $objReader = new PHPExcel_Reader_Excel5();
     $objReader = new PHPExcel_Reader_Excel2007();
     //	$objReader = new PHPExcel_Reader_Excel2003XML();
     //	$objReader = new PHPExcel_Reader_OOCalc();
     //	$objReader = new PHPExcel_Reader_SYLK();
     //	$objReader = new PHPExcel_Reader_Gnumeric();
     //	$objReader = new PHPExcel_Reader_CSV();
     $objPHPExcel = $objReader->load($inputFileName);
     //        echo '<hr />';
     $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
     return $sheetData;
 }
Пример #10
0
 public function goods_import($filename, $exts = 'xls')
 {
     //导入PHPExcel类库,因为PHPExcel没有用命名空间,只能inport导入
     require_once APP_PATH . 'PHPExcel/PHPExcel.php';
     require_once APP_PATH . 'PHPExcel/PHPExcel/Reader/Excel5.php';
     // 用于其他低版本xls
     require_once APP_PATH . 'PHPExcel/PHPExcel/Reader/Excel2007.php';
     // 用于 excel-2007 格式
     //创建PHPExcel对象,注意,不能少了\
     $PHPExcel = new PHPExcel();
     //如果excel文件后缀名为.xls,导入这个类
     if ($exts == 'xls') {
         $PHPReader = new PHPExcel_Reader_Excel5();
     } elseif ($exts == 'xlsx') {
         $PHPReader = new PHPExcel_Reader_Excel2007();
     } else {
         exit('错误的格式');
     }
     //载入文件
     $PHPExcel = $PHPReader->load($filename);
     //获取表中的第一个工作表,如果要获取第二个,把0改为1,依次类推
     $currentSheet = $PHPExcel->getSheet(0);
     //获取总列数
     $allColumn = $currentSheet->getHighestColumn();
     //获取总行数
     $allRow = $currentSheet->getHighestRow();
     //循环获取表中的数据,$currentRow表示当前行,从哪行开始读取数据,索引值从0开始
     for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
         //从哪列开始,A表示第一列
         for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
             //数据坐标
             $address = $currentColumn . $currentRow;
             //读取到的数据,保存到数组$arr中
             $data[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
         }
     }
     foreach ($data as $info) {
         $order_id = $info['A'];
         $name = $info['G'];
         $num = $info['H'];
         if (!empty($order_id) && !empty($name) && !empty($num)) {
             $data['id'] = $order_id;
             $data['order_info'] = json_encode(array('num' => $num, 'name' => $name));
             $result = M("Order")->save($data);
             $result = R("Api/Api/publish", array($order_id));
         }
     }
 }
Пример #11
0
 /**
  * @dataProvider providerDump
  */
 public function testDump(Config $config, $withDate, $generatedValues, $expectedFile, $expected)
 {
     $dumper = new ExcelDumper();
     $dumper->initialize($config, self::$cacheDir, $withDate);
     foreach ($generatedValues as $row) {
         $dumper->dumpRow($row);
     }
     $filename = $dumper->finalize();
     $this->assertFileExists($filename);
     $ext = 'xlsx';
     $this->assertRegExp('/' . basename($expectedFile, '.' . $ext) . '_\\d{4}-\\d{2}-\\d{2}_\\d{2}-\\d{2}-\\d{2}.' . $ext . '/', basename($filename));
     $reader = new \PHPExcel_Reader_Excel2007();
     $excel = $reader->load(self::$fixtures . '/' . $expectedFile);
     $data = $excel->getActiveSheet()->toArray();
     $this->assertEquals($expected, $data);
 }
Пример #12
0
 /**
  * Loads source data from local file into memory.
  */
 private function load_source_data_from_file()
 {
     require_once DOC_ROOT . '/vendor/PHPExcel/Classes/PHPExcel.php';
     // NOTE: In development it takes 24 Seconds to load and uses 318 MB of memory.
     $start = microtime(true);
     debug('Loading source file.');
     try {
         $reader = new \PHPExcel_Reader_Excel2007();
         $reader->setReadDataOnly(true);
         $this->source_loaded = $reader->load($this->source_file_path);
         unset($reader);
     } catch (\Exception $e) {
         throw new \Exception('Error loading source data from ' . "{$this->source_file_path}: {$e->getMessage()}");
     }
     $this->profile($start);
 }
Пример #13
0
 private function read_excel($filename, $extension = 'xls')
 {
     //导入PHPExcel类库
     import('ORG.Util.PHPExcel');
     $PHPExcel = new PHPExcel();
     //根据扩展名判断所用的解析方式
     if ($extension == 'xls') {
         import("ORG.Util.PHPExcel.Reader.Excel5");
         $PHPReader = new PHPExcel_Reader_Excel5();
     } else {
         if ($extension == 'xlsx') {
             import("ORG.Util.PHPExcel.Reader.Excel2007");
             $PHPReader = new PHPExcel_Reader_Excel2007();
         }
     }
     //读取excel文件
     $PHPExcel = $PHPReader->load($filename);
     //获取Sheet0中的数据
     $currentSheet = $PHPExcel->getSheet(0);
     //获取行数、列数
     $allColumn = $currentSheet->getHighestColumn();
     $allRow = $currentSheet->getHighestRow();
     //循环遍历excel表格,重点是getCell和getValue方法
     for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
         for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
             $address = $currentColumn . $currentRow;
             $data[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
         }
     }
     //循环所得数据插入数据库,至于从2开始嘛,执行var_dump($data)你就懂了
     // echo "<pre>";
     // var_dump($data);die;
     for ($i = 2; $i < count($data); $i++) {
         $school = array('school' => $data[$i]['B']);
         $find = M('school')->where($school)->find();
         if ($find) {
             $school_id = $find['id'];
         } else {
             $school_id = M('school')->data($school)->add();
         }
         $joiner = array('username' => $data[$i]['C'], 'school_id' => $school_id);
         M('joiner')->data($joiner)->add();
     }
     //然而我并没有想出该怎么判断成功和失败
     $this->success('Excel导入成功');
 }
Пример #14
0
 public function anyExcel()
 {
     $pFile = iconv('utf-8', 'gbk', 'C:/Users/LJ/Desktop/北邮/灾难恢复方案评估表-关.xlsx');
     $reader = new \PHPExcel_Reader_Excel2007();
     $workbook = $reader->load($pFile);
     $sheet = $workbook->getSheet(0);
     $highestRow = $sheet->getHighestRow();
     $highestColumn = $sheet->getHighestColumn();
     $table = [];
     for ($row = 1; $row < $highestRow; $row++) {
         $table[] = [];
         for ($column = 'A'; $column < $highestColumn; $column++) {
             $coordinate = $column . $row;
             $table[$row - 1][] = $sheet->getCell($coordinate)->getValue();
         }
     }
     return view('bupt.user.excel', ['table' => $table]);
 }
Пример #15
0
 public function getWorkbook()
 {
     $data = $this->getReportData();
     $data = $this->formtable($data);
     $xls_reader = new PHPExcel_Reader_Excel2007();
     $workbook = $xls_reader->load(APPLICATION_PATH . 'modules/stat/templates/report1.xlsx');
     $workbook->getActiveSheet()->setCellValueByColumnAndRow(0, 1, $this->getGuideNodeTitle($this->params['establishment']));
     if (count($data) > 2) {
         $workbook->getActiveSheet()->insertNewRowBefore(5, count($data) - 2);
     }
     foreach ($data as $rownum => $row) {
         $workbook->getActiveSheet()->setCellValueByColumnAndRow(0, $rownum + 4, $row['title']);
         foreach (array('CS', 'CS1', 'CS2', 'CS3', 'CS4', 'CS5', 'CS6', 'CS7', 'CIN', 'CIN1', 'CIN2', 'CIN3', 'CIN4', 'CIN5', 'CIN6', 'CIN7', 'COUT', 'COUT1', 'COUT2', 'COUT3', 'COUT4', 'COUT5', 'COUT6', 'COUT7') as $colnum => $column) {
             $workbook->getActiveSheet()->setCellValueByColumnAndRow($colnum + 1, $rownum + 4, array_key_exists($column, $row) ? $row[$column] : 0);
         }
     }
     return $workbook;
 }
Пример #16
0
 protected function goods_import($filename, $exts = 'xls')
 {
     //导入PHPExcel类库,因为PHPExcel没有用命名空间,只能inport导入
     import("Org.Util.PHPExcel");
     //创建PHPExcel对象,注意,不能少了\
     $PHPExcel = new \PHPExcel();
     //如果excel文件后缀名为.xls,导入这个类
     if ($exts == 'xls') {
         import("Org.Util.PHPExcel.Reader.Excel5");
         $PHPReader = new \PHPExcel_Reader_Excel5();
     } else {
         if ($exts == 'xlsx') {
             import("Org.Util.PHPExcel.Reader.Excel2007");
             $PHPReader = new \PHPExcel_Reader_Excel2007();
         }
     }
     //载入文件
     $PHPExcel = $PHPReader->load($filename);
     //获取表中的第一个工作表,如果要获取第二个,把0改为1,依次类推
     $currentSheet = $PHPExcel->getSheet(0);
     //获取总列数
     $allColumn = $currentSheet->getHighestColumn();
     //获取总行数
     $allRow = $currentSheet->getHighestRow();
     //循环获取表中的数据,$currentRow表示当前行,从哪行开始读取数据,索引值从0开始
     for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
         //从哪列开始,A表示第一列
         for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
             //数据坐标
             $address = $currentColumn . $currentRow;
             //读取到的数据,保存到数组$arr中
             $data[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
         }
     }
     $this->save_import($data);
 }
Пример #17
0
 /**
  * 读取Excel
  */
 public static function reader($file)
 {
     import("Tools.Excel.PHPExcel");
     if (self::_getExt($file) == 'xls') {
         import("Tools.Excel.PHPExcel.Reader.Excel5");
         $PHPReader = new \PHPExcel_Reader_Excel5();
     } elseif (self::_getExt($file) == 'xlsx') {
         import("Tools.Excel.PHPExcel.Reader.Excel2007");
         $PHPReader = new \PHPExcel_Reader_Excel2007();
     } else {
         return false;
     }
     $PHPExcel = $PHPReader->load($file);
     $currentSheet = $PHPExcel->getSheet(0);
     $allColumn = $currentSheet->getHighestColumn();
     $allRow = $currentSheet->getHighestRow();
     for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
         for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
             $address = $currentColumn . $currentRow;
             $arr[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
         }
     }
     return $arr;
 }
Пример #18
0
 /**
  * 生成订单附件
  * @param $order_data 要写入订单模板的数据数组
  * @param $attachment_path = '' 附件生成路径
  * @return string|bool
  */
 public static function create_order_attachment($order_data, $attachment_path = '')
 {
     if (!empty($order_data) && is_array($order_data)) {
         // 引入PHPExcel扩展
         Yii::createComponent('application.extensions.excel.PHPExcel');
         // 租车订单Excel附件 - 模板目录
         $templet_path = ASSETS_FILE . 'templet/tichedan.xls';
         // 租车订单Excel附件 - 正式目录
         $attachment_path = $attachment_path == '' ? SECRET_FILE_PATH . 'Carrental/' . date('Y-m-d', time()) . '/' : $attachment_path;
         try {
             // 正式目录不存在则创建
             if (!file_exists($attachment_path)) {
                 if (!DirectoryFile::dirCreate($attachment_path)) {
                     throw new Exception('创建目录失败!');
                 }
             }
             // 如模板文件存在则复制到正式目录下
             if (file_exists($templet_path)) {
                 // 生成附件名称
                 $attachment_name = 'order_' . time() . '_' . rand(100, 10000) . '.xls';
                 // 复制租车订单模板至正式目录下
                 $status = @copy($templet_path, $attachment_path . $attachment_name);
                 if (!$status) {
                     throw new Exception('模板文件移动失败!');
                 }
             } else {
                 throw new Exception('订单模板文件不存在!');
             }
             // 实例化Excel读取类
             $PHPReader = new PHPExcel_Reader_Excel5();
             if (!$PHPReader->canRead($attachment_path . $attachment_name)) {
                 $PHPReader = new PHPExcel_Reader_Excel2007();
                 if (!$PHPReader->canRead($attachment_path . $attachment_name)) {
                     throw new Exception('不是一个Excel文件!');
                 }
             }
             // 读取Excel工作表
             $PHPExcel = $PHPReader->load($attachment_path . $attachment_name);
             $currentSheet = $PHPExcel->getSheet(0);
             // 重写Excel工作表 将单项信息写入Excel单元格
             $currentSheet->setCellValue('B3', !empty($order_data[0]) ? $order_data[0] : '');
             // 预定号[reservation number]
             $currentSheet->setCellValue('D3', $order_data[1]);
             // 确认号码[confirmation number]
             $currentSheet->setCellValue('B5', $order_data[2]);
             // 租车人姓名[Name]
             $currentSheet->setCellValue('D5', $order_data[3]);
             // 航班号码[Flight number]
             $currentSheet->setCellValue('B19', $order_data[4]);
             // 车辆代码[Car Code]
             $currentSheet->setCellValue('D19', $order_data[5]);
             // 车门数[Doors]
             $currentSheet->setCellValue('D22', $order_data[6]);
             // 空调[Air Con]
             $currentSheet->setCellValue('D20', $order_data[7]);
             // 座位数[Car Seats]
             $currentSheet->setCellValue('B9', $order_data[8]);
             // 取车日期
             $currentSheet->setCellValue('B10', $order_data[9]);
             // 取车时间
             $currentSheet->setCellValue('B11', $order_data[10]);
             // 取车门店[Location]
             $currentSheet->setCellValue('B12', $order_data[11]);
             // 取车地址[Address]
             $currentSheet->setCellValue('B13', $order_data[12]);
             // 取车地电话[Tel]
             $currentSheet->setCellValue('D9', $order_data[13]);
             // 还车日期
             $currentSheet->setCellValue('D10', $order_data[14]);
             // 还车时间
             $currentSheet->setCellValue('D11', $order_data[15]);
             // 还车门店[Location]
             $currentSheet->setCellValue('D12', $order_data[16]);
             // 还车地址[Address]
             $currentSheet->setCellValue('D13', $order_data[17]);
             // 还车地电话[Tel]
             $currentSheet->setCellValue('B23', $order_data[18]);
             // 确认号码[confirmation number]
             $currentSheet->setCellValue('B18', $order_data[19]);
             // 车行名称Supplier
             $currentSheet->setCellValue('B20', $order_data[20]);
             // 车辆类型 Car Type
             $currentSheet->setCellValue('D21', $order_data[21]);
             //变速器类型transmission
             $currentSheet->setCellValueExplicit('B22', $order_data[22], PHPExcel_Cell_DataType::TYPE_STRING);
             //车行的账户号transmission
             $currentSheet->setCellValue('A26', $order_data[23]);
             //里程限制
             $currentSheet->setCellValue('A27', $order_data[24]);
             //车辆保险
             $currentSheet->setCellValue('A28', $order_data[25]);
             //车辆包含的税费
             $currentSheet->setCellValue('A30', $order_data[26]);
             //预定的额外服务
             // 执行Excel写入操作
             $PHPWriter = new PHPExcel_Writer_Excel5($PHPExcel);
             // 生成订单附件
             //                $PHPWriter->save(iconv('utf - 8', 'gbk', $attachment_path . $attachment_name));
             $PHPWriter->save($attachment_path . $attachment_name);
         } catch (Exception $e) {
             echo $e->getMessage();
         }
         echo '<script language="javascript" type="text/javascript">window.top.window.stopUpload(1);</script >';
         return $attachment_path . $attachment_name;
     } else {
         exit('参数不正确!');
     }
 }
    if (copy($_FILES['excel']['tmp_name'], $destino)) {
        ?>
	<!--  
	<label for="	">Archivo Cargado Con Éxito</label>-->
	
	<?php 
    } else {
        echo "Error Al Cargar el Archivo";
    }
    if (file_exists("bak_" . $archivo)) {
        /** Clases necesarias */
        require_once '../Classes/PHPExcel.php';
        require_once '../Classes/PHPExcel/Reader/Excel2007.php';
        // Cargando la hoja de cálculo
        $objReader = new PHPExcel_Reader_Excel2007();
        $objPHPExcel = $objReader->load("bak_" . $archivo);
        $objFecha = new PHPExcel_Shared_Date();
        // Asignar hoja de excel activa
        $objPHPExcel->setActiveSheetIndex(0);
        //conectamos con la base de datos
        $cn = mssql_connect("192.168.1.4", "APLICACIONES", "APLICACIONES") or die("ERROR EN LA CONEXION");
        $db = mssql_select_db("[020BDCOMUN]", $cn) or die("ERROR AL CONECTAR A LA BD");
        // Llenamos el arreglo con los datos  del archivo xlsx
        for ($i = 1; $i <= 300; $i++) {
            $_DATOS_EXCEL[$i]['IDDATOS_RQ'] = $objPHPExcel->getActiveSheet()->getCell('A' . $i)->getCalculatedValue();
            $_DATOS_EXCEL[$i]['CODIGODATOS_RQ'] = $objPHPExcel->getActiveSheet()->getCell('B' . $i)->getCalculatedValue();
            $_DATOS_EXCEL[$i]['CANTDATOS_RQ'] = $objPHPExcel->getActiveSheet()->getCell('D' . $i)->getCalculatedValue();
            $_DATOS_EXCEL[$i]['USUARIO'] = $_SESSION['id_usuario'];
        }
    } else {
        echo "Necesitas primero importar el archivo";
Пример #20
0
 public function upload_report_listing($file_name)
 {
     //  Include PHPExcel_IOFactory
     // include 'PHPExcel/IOFactory.php';
     // include 'PHPExcel/PHPExcel.php';
     // $inputFileName = 'excel_files/garissa_sms_recepients_updated.xlsx';
     // echo $category;exit;
     $inputFileName = 'print_docs/excel/uploaded_files/' . $file_name;
     $objReader = new PHPExcel_Reader_Excel2007();
     $objReader->setReadDataOnly(true);
     $objPHPExcel = $objReader->load($inputFileName);
     // echo "<pre>";print_r($inputFileName);exit;
     $sheet = $objPHPExcel->getSheet(0);
     $highestRow = $sheet->getHighestRow() + 1;
     $highestColumn = $sheet->getHighestColumn();
     // echo "<pre>";print_r($highestRow);echo "</pre>";exit;
     $rowData = array();
     for ($row = 4; $row < $highestRow; $row++) {
         //  Read a row of data into an array
         $rowData_ = $sheet->rangeToArray('A' . $row . ':F' . $row);
         // echo "<pre>";print_r($rowData_);echo "</pre>";
         array_push($rowData, $rowData_[0]);
         //  Insert row data array into your database of choice here
     }
     // echo "<pre>";print_r($rowData);exit;//echo's array
     foreach ($rowData as $r_data) {
         // echo "<pre>";print_r($r_data);echo "</pre>";
         /*
         Result array key
         0 = name
         1 = phone
         2 = email
         3 = county
         4 = subcounty
         5 = mfl
         */
         $status = 1;
         $county_id = $district_id = $facility_code = 0;
         $new_county_id = $new_district_id = $new_facility_code = $usertype = null;
         $county_name = strtolower($r_data[3]);
         //lower case
         $county_name = str_replace(" ", "", $county_name);
         $county_name = str_replace("-", " ", $county_name);
         $county_name = ucwords($county_name);
         //upper first character
         $district = strtolower($r_data[4]);
         $district = ucfirst($district);
         $phone = preg_replace('/\\s+/', '', $r_data[1]);
         // echo "<pre>";print_r($phone);
         // echo "<pre>";print_r($county_name);
         $facility_code = !empty($r_data[5]) ? $r_data[5] : NULL;
         $name = !empty($r_data[0]) ? $r_data[0] : NULL;
         $email = !empty($r_data[2]) ? $r_data[2] : NULL;
         $date_uploaded = date('Y-m-d h:i:s');
         $fault_index = NULL;
         // echo $district;
         $query = "SELECT * FROM facilities WHERE facility_code = '{$facility_code}'";
         $result = $this->db->query($query)->result_array();
         //FACILITY CODE SEARCH
         // echo "<pre>";print_r($result);echo "</pre>";
         $sql = null;
         if (empty($result)) {
             //if no facility code then district
             $queryy = "SELECT * FROM districts WHERE district = '{$district}'";
             $resultt = $this->db->query($queryy)->result_array();
             if (empty($resultt)) {
                 //no district then county
                 $queryyy = "SELECT * FROM counties WHERE county = '{$county_name}'";
                 $resulttt = $this->db->query($queryyy)->result_array();
                 if (empty($resulttt)) {
                     echo "Empty county,subcounty and facility";
                 } else {
                     $county_id = $resulttt[0]['id'];
                     // echo "<pre>\t Only County ".$phone;
                     $new_county_id = $county_id;
                     $usertype = 10;
                 }
             } else {
                 //if district matches
                 $district_id = $resultt[0]['id'];
                 $county_id = $this->get_county_id_for_district($district_id);
                 // echo "<pre>\t District ".$phone;
                 $new_county_id = $county_id;
                 $new_district_id = $district_id;
                 $usertype = 3;
             }
             //district name match
         } else {
             //if facility_code_match
             // echo "<pre>";print_r($result);exit;
             $district_id = $result[0]['district'];
             $county_id = $this->get_county_id_for_district($district_id);
             // echo "<pre>\t Facility: ".$phone;
             $new_county_id = $county_id;
             $new_district_id = $district_id;
             $new_facility_code = $facility_code;
             $usertype = 5;
             // echo "<pre>"; print_r($county_id);exit;
         }
         /*
         					//code for appending 254 to phone numbers
         					if (isset($phone)) {
         						$phone = preg_replace('/\s+/', '', $phone);
         						$phone = ltrim($phone, '0');
         						// echo "<pre>".substr($phone, 0,3);
         						if (substr($phone, 0,3) != '254') {
         							$phone = '254'.$phone;
         						}
         					}else{
         						$phone = NULL;
         					}
         */
         $number_length = isset($phone) ? strlen($phone) : 0;
         // echo "Number Length:  ".$number_length;
         if ($number_length != 12) {
             if (isset($fault_index)) {
                 $fault_index = 3;
                 //both error in phone and district
                 // $status = 2;
             } else {
                 $fault_index = 2;
             }
             $fault_index = 2;
             //overriding both district and phone error as district is not necessarily necessary
             $status = 2;
         }
         /*commented out insertion below*/
         $listing = array();
         $listing_data = array('name' => $name, 'email' => $email, 'phone_number' => $phone, 'facility_code' => $new_facility_code, 'sub_county' => $new_district_id, 'county' => $new_county_id, 'usertype' => $usertype, 'date_uploaded' => $date_uploaded, 'status' => '0');
         array_push($listing, $listing_data);
         // echo "<pre>";print_r($inv);
         $similarity_query = "SELECT * FROM email_listing_new WHERE phone_number = '{$phone}'";
         $similarity = $this->db->query($similarity_query)->result_array();
         //FACILITY CODE SEARCH
         if (empty($similarity)) {
             $insertion = $this->db->insert_batch('email_listing_new', $listing);
             // echo "QUERY SUCCESSFUL. ".$insertion." ".mysql_insert_id()."</br>";
         } else {
             // echo "<pre> Dab on em";
         }
     }
     // echo "<pre>";print_r($inv);
     // unlink($inputFileName);
     // echo "QUERY SUCCESSFUL. LAST ID INSERTED: ".mysql_insert_id(); exit;
     // redirect(base_url().'admin/report_listing');
     // exit;
 }
Пример #21
0
<?php
//INCLUIMOS LA LIBRERIA DEL EXCEL 


require_once("PHPExcel/Classes/PHPExcel/Reader/Excel2007.php");
require_once 'PHPExcel/Classes/PHPExcel.php';



$objReader = new PHPExcel_Reader_Excel2007(); 
$objPHPExcel = $objReader->load("excel.xls");
$objPHPExcel->setActiveSheetIndex(0);


$i=1; 


while($objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue() != ′′) {	
$nombre = $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue(); 	
$email = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue(); 	
$telefono = $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue(); 	 

/*$conexion = mysql_connect("$dbhost", "$dbuser", "$dbpass"); 
$sql = "INSERT INTO usuarios (nombre, email, telefono) VALUES (′$nombre′, ′$email’, ′$telefono’)"; 
$rst=mysql_query($sql,$conexion); 

if(!$rst) {
	die("Error MySQL de Inserción de Datos"); $i++; }
*/
$i++;
}
<?php 
if (!defined('isIncluded')) {
    header('location: index.php');
}
if (!isset($_SESSION['uploadedFile'])) {
    header('location: index.php');
}
set_time_limit(0);
date_default_timezone_set('Europe/Amsterdam');
$_SESSION['AUTOLOAD'] = false;
require_once APP_LIB_PATH . 'PHPExcel' . DS . 'IOFactory.php';
$objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load(APP_EXCEL_PATH . $_SESSION['uploadedFile']);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$_SESSION['AUTOLOAD'] = true;
function select($name, $data)
{
    echo '<select name="' . $name . '">';
    echo '<option value="0">Selecteer een kolom uit het Excel-bestand</option>';
    foreach ($data['1'] as $char => $field) {
        echo '<option value="' . $char . '">' . $field . '</option>';
    }
    echo '</select>';
}
function productlijstExists($naam)
{
    $productlijsten = productlijst::getAll();
    foreach ($productlijsten as $productlijst) {
        if ($productlijst->getProperty('Naam') == $naam) {
            return true;
Пример #23
0
 public function data_upload()
 {
     if ($_POST['btn_save']) {
         $objReader = new PHPExcel_Reader_Excel2007();
         if ($_FILES['file']['tmp_name']) {
             $objPHPExcel = $objReader->load($_FILES['file']['tmp_name']);
         } else {
             $this->session->set_userdata('upload_counter', '1');
             redirect("fcdrr_management/index");
         }
         $arr = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
         $highestColumm = $objPHPExcel->setActiveSheetIndex(0)->getHighestColumn();
         $highestRow = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
         //Top Details
         $facility_name = $arr[5]['B'] . $arr[5]['C'] . $arr[5]['D'] . $arr[5]['E'];
         $province = $arr[6]['B'] . $arr[6]['C'] . $arr[6]['D'] . $arr[6]['E'];
         $facility_code = $arr[5]['R'] . $arr[5]['S'] . $arr[5]['T'];
         $district = $arr[6]['R'] . $arr[6]['S'] . $arr[6]['T'];
         $type_of_service_art = $arr[8]['C'];
         $type_of_service_pmtct = $arr[8]['E'];
         $type_of_service_pep = $arr[8]['H'];
         if ($type_of_service_art && $type_of_service_pmtct && $type_of_service_pep) {
             $services_offered = "ART,PMTCT,PEP";
         } else {
             if ($type_of_service_pmtct && $type_of_service_art) {
                 $services_offered = "ART,PMTCT";
             } else {
                 if ($type_of_service_pep && $type_of_service_art) {
                     $services_offered = "ART,PEP";
                 } else {
                     if ($type_of_service_pmtct && $type_of_service_pep) {
                         $services_offered = "PMTCT,PEP";
                     } else {
                         if ($type_of_service_art) {
                             $services_offered = "ART";
                         }
                         if ($type_of_service_pmtct) {
                             $services_offered = "PMTCT";
                         }
                         if ($type_of_service_pep) {
                             $services_offered = "PEP";
                         }
                     }
                 }
             }
         }
         @$services_offered;
         $programme_sponsor_gok = $arr[4]['D'];
         $programme_sponsor_pepfar = $arr[4]['G'];
         $programme_sponsor_msf = $arr[4]['L'];
         $programme_sponsor = "";
         if ($programme_sponsor_gok) {
             $programme_sponsor = "GOK";
         }
         if ($programme_sponsor_pepfar) {
             $programme_sponsor = "PEPFAR";
         }
         if ($programme_sponsor_msf) {
             $programme_sponsor = "MSF";
         }
         $updated_on = date("U");
         //Reporting Period
         @($beginning = trim($arr[10]['D'] . $arr[10]['E']));
         @($ending = $arr[10]['R'] . $arr[10]['S'] . $arr[10]['T']);
         $start = explode("-", $beginning);
         $day = $start[0];
         $month = $start[1];
         $year = $start[2];
         $beginning = "20" . $year . "-" . $month . "-" . $day;
         $beginning = date('Y-m-d', strtotime($beginning));
         $ending = str_replace('/', '-', $ending);
         $old_ending = strtotime($ending);
         $ending = date('Y-m-d', $old_ending);
         $central_facility = $this->session->userdata('facility');
         $parent = Facilities::getParent($central_facility);
         $central_site = $parent->parent;
         //Comments
         for ($i = 105; $i <= 109; $i++) {
             for ($j = 1; $j <= $highestColumm; $j++) {
             }
             @($comments .= $arr[$i]['A'] . $arr[$i]['B'] . $arr[$i]['C'] . $arr[$i]['D'] . $arr[$i]['E'] . $arr[$i]['G'] . $arr[$i]['H'] . $arr[$i]['L']);
         }
         $unique_id = 0;
         $this->load->database();
         $facility_order_query = $this->db->query("SELECT MAX(id) AS id FROM facility_order");
         $facility_order_results = $facility_order_query->result_array();
         $facility_id = $facility_order_results[0]['id'];
         $order_number = $facility_id + 1;
         $unique_id = md5($order_number . $facility_code);
         $query = $this->db->query("INSERT INTO facility_order (`id`, `status`, `created`, `updated`, `code`, `period_begin`, `period_end`, `comments`, `reports_expected`, `reports_actual`, `services`, `sponsors`, `delivery_note`, `order_id`, `facility_id`,`central_facility`,`unique_id`) VALUES ('{$order_number}', '0', CURDATE(), '{$updated_on}', '2', '{$beginning}', '{$ending}', '{$comments}', NULL, NULL, '{$services_offered}', '{$programme_sponsor}', NULL, NULL, '{$facility_code}','{$central_site}','{$unique_id}');");
         $facility_id = $unique_id;
         $user_id = $this->session->userdata('full_name');
         $query = $this->db->query("SELECT MAX(id) AS id FROM order_comment");
         $results = $query->result_array();
         $last_id = $results[0]['id'];
         $last_id = $last_id + 1;
         $last_id = md5($last_id . $facility_code);
         //Adding comments
         $order_comment = new Order_Comment();
         $order_comment->Order_Number = $facility_id;
         $order_comment->Timestamp = date('U');
         $order_comment->User = $user_id;
         $order_comment->Comment = $comments;
         $order_comment->Unique_Id = $last_id;
         $order_comment->save();
         //Adult ARV Preparations
         for ($i = 18; $i <= 42; $i++) {
             for ($j = 1; $j <= $highestColumm; $j++) {
             }
             $quantity_required_for_supply = $arr[$i]['L'];
             $drug_name = $arr[$i]['A'];
             if ($quantity_required_for_supply != 0) {
                 $drug_id = $drug_name;
                 $basic_unit = $arr[$i]['B'];
                 $beginning_balance = $arr[$i]['C'];
                 $quantity_received_in_period = $arr[$i]['D'];
                 $quantity_dispensed_in_period = $arr[$i]['E'];
                 $adjustments_to_other_facilities = $arr[$i]['G'];
                 $end_of_month_physical_count = $arr[$i]['H'];
                 $quantity_required_for_supply = $arr[$i]['L'];
                 $query = $this->db->query("SELECT MAX(id) AS id FROM cdrr_item");
                 $results = $query->result_array();
                 $last_id = $results[0]['id'];
                 $last_id++;
                 $last_id = md5($last_id . $facility_code);
                 $cdrr_query = $this->db->query("INSERT INTO cdrr_item (`id`, `balance`, `received`, `dispensed_units`, `dispensed_packs`, `losses`, `adjustments`, `count`, `resupply`, `aggr_consumed`, `aggr_on_hand`, `publish`, `cdrr_id`, `drug_id`,`unique_id`) VALUES (NULL, '{$beginning_balance}', '{$quantity_received_in_period}', '{$quantity_dispensed_in_period}', NULL, NULL, '{$adjustments_to_other_facilities}', '{$end_of_month_physical_count}', '{$quantity_required_for_supply}', NULL, NULL, '0', '{$facility_id}', '{$drug_id}','{$last_id}');");
             }
         }
         //Paediatric Preparations
         for ($i = 44; $i <= 76; $i++) {
             for ($j = 1; $j <= $highestColumm; $j++) {
             }
             $quantity_required_for_supply = $arr[$i]['L'];
             $drug_name = $arr[$i]['A'];
             if ($quantity_required_for_supply != 0) {
                 $drug_id = $drug_name;
                 $basic_unit = $arr[$i]['B'];
                 $beginning_balance = $arr[$i]['C'];
                 $quantity_received_in_period = $arr[$i]['D'];
                 $quantity_dispensed_in_period = $arr[$i]['E'];
                 $adjustments_to_other_facilities = $arr[$i]['G'];
                 $end_of_month_physical_count = $arr[$i]['H'];
                 $quantity_required_for_supply = $arr[$i]['L'];
                 $query = $this->db->query("SELECT MAX(id) AS id FROM cdrr_item");
                 $results = $query->result_array();
                 $last_id = $results[0]['id'];
                 $last_id++;
                 $last_id = md5($last_id . $facility_code);
                 $cdrr_query = $this->db->query("INSERT INTO cdrr_item (`id`, `balance`, `received`, `dispensed_units`, `dispensed_packs`, `losses`, `adjustments`, `count`, `resupply`, `aggr_consumed`, `aggr_on_hand`, `publish`, `cdrr_id`, `drug_id`,`unique_id`) VALUES (NULL, '{$beginning_balance}', '{$quantity_received_in_period}', '{$quantity_dispensed_in_period}', NULL, NULL, '{$adjustments_to_other_facilities}', '{$end_of_month_physical_count}', '{$quantity_required_for_supply}', NULL, NULL, '0', '{$facility_id}', '{$drug_id}','{$last_id}');");
             }
         }
         //Drugs for IOs
         for ($i = 78; $i <= 99; $i++) {
             for ($j = 1; $j <= $highestColumm; $j++) {
             }
             $quantity_required_for_supply = $arr[$i]['L'];
             $drug_name = $arr[$i]['A'];
             if ($quantity_required_for_supply != 0) {
                 $drug_id = $drug_name;
                 $basic_unit = $arr[$i]['B'];
                 $beginning_balance = $arr[$i]['C'];
                 $quantity_received_in_period = $arr[$i]['D'];
                 $quantity_dispensed_in_period = $arr[$i]['E'];
                 $adjustments_to_other_facilities = $arr[$i]['G'];
                 $end_of_month_physical_count = $arr[$i]['H'];
                 $quantity_required_for_supply = $arr[$i]['L'];
                 $query = $this->db->query("SELECT MAX(id) AS id FROM cdrr_item");
                 $results = $query->result_array();
                 $last_id = $results[0]['id'];
                 $last_id++;
                 $last_id = md5($last_id . $facility_code);
                 $cdrr_query = $this->db->query("INSERT INTO cdrr_item (`id`, `balance`, `received`, `dispensed_units`, `dispensed_packs`, `losses`, `adjustments`, `count`, `resupply`, `aggr_consumed`, `aggr_on_hand`, `publish`, `cdrr_id`, `drug_id`,`unique_id`) VALUES (NULL, '{$beginning_balance}', '{$quantity_received_in_period}', '{$quantity_dispensed_in_period}', NULL, NULL, '{$adjustments_to_other_facilities}', '{$end_of_month_physical_count}', '{$quantity_required_for_supply}', NULL, NULL, '0', '{$facility_id}', '{$drug_id}','{$last_id}');");
             }
         }
         //PMTCT Regimen 1.Pregnant Women
         for ($i = 19; $i <= 21; $i++) {
             for ($j = 19; $j <= $highestColumm; $j++) {
             }
             $regimen_code = $arr[$i]['S'];
             $regimen_desc = $arr[$i]['T'];
             $no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
             if ($no_of_clients_dispensed_in_period) {
                 $regimen_id = $regimen_code . " | " . $regimen_desc;
                 $query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
                 $results = $query->result_array();
                 $last_id = $results[0]['id'];
                 $last_id++;
                 $last_id = md5($last_id . $facility_code);
                 $next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
             }
         }
         //PMTCT Regimen 2.Infants
         for ($i = 23; $i <= 27; $i++) {
             for ($j = 19; $j <= $highestColumm; $j++) {
             }
             $regimen_code = $arr[$i]['S'];
             $regimen_desc = $arr[$i]['T'];
             $no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
             if ($no_of_clients_dispensed_in_period) {
                 $this->load->database();
                 $regimen_id = $regimen_code . " | " . $regimen_desc;
                 $query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
                 $results = $query->result_array();
                 $last_id = $results[0]['id'];
                 $last_id++;
                 $last_id = md5($last_id . $facility_code);
                 $next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
             }
         }
         //Adult ART First Line Regimens
         for ($i = 33; $i <= 43; $i++) {
             for ($j = 19; $j <= $highestColumm; $j++) {
             }
             $regimen_code = $arr[$i]['S'];
             $regimen_desc = $arr[$i]['T'];
             $no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
             if ($no_of_clients_dispensed_in_period) {
                 $regimen_id = $regimen_code . " | " . $regimen_desc;
                 $query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
                 $results = $query->result_array();
                 $last_id = $results[0]['id'];
                 $last_id++;
                 $last_id = md5($last_id . $facility_code);
                 $next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
             }
         }
         //Adult ART Second Line Regimens
         for ($i = 45; $i <= 58; $i++) {
             for ($j = 19; $j <= $highestColumm; $j++) {
             }
             $regimen_code = $arr[$i]['S'];
             $regimen_desc = $arr[$i]['T'];
             $no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
             if ($no_of_clients_dispensed_in_period) {
                 $regimen_id = $regimen_code . " | " . $regimen_desc;
                 $query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
                 $results = $query->result_array();
                 $last_id = $results[0]['id'];
                 $last_id++;
                 $last_id = md5($last_id . $facility_code);
                 $next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
             }
         }
         //Other Adult ART regimens
         for ($i = 60; $i <= 62; $i++) {
             for ($j = 19; $j <= $highestColumm; $j++) {
             }
             $regimen_code = $arr[$i]['S'];
             $regimen_desc = $arr[$i]['T'];
             $no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
             if ($no_of_clients_dispensed_in_period) {
                 $regimen_id = $regimen_code . " | " . $regimen_desc;
                 $query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
                 $results = $query->result_array();
                 $last_id = $results[0]['id'];
                 $last_id++;
                 $last_id = md5($last_id . $facility_code);
                 $next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
             }
         }
         //Paediatric ART First Line Regimens
         for ($i = 64; $i <= 74; $i++) {
             for ($j = 19; $j <= $highestColumm; $j++) {
             }
             $regimen_code = $arr[$i]['S'];
             $regimen_desc = $arr[$i]['T'];
             $no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
             if ($no_of_clients_dispensed_in_period) {
                 $regimen_id = $regimen_code . " | " . $regimen_desc;
                 $query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
                 $results = $query->result_array();
                 $last_id = $results[0]['id'];
                 $last_id++;
                 $last_id = md5($last_id . $facility_code);
                 $next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
             }
         }
         //Paediatric ART Second Line Regimens
         for ($i = 76; $i <= 84; $i++) {
             for ($j = 19; $j <= $highestColumm; $j++) {
             }
             $regimen_code = $arr[$i]['S'];
             $regimen_desc = $arr[$i]['T'];
             $no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
             if ($no_of_clients_dispensed_in_period) {
                 $regimen_id = $regimen_code . " | " . $regimen_desc;
                 $query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
                 $results = $query->result_array();
                 $last_id = $results[0]['id'];
                 $last_id++;
                 $last_id = md5($last_id . $facility_code);
                 $next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
             }
         }
         //Other Paediatric ART regimens
         for ($i = 86; $i <= 87; $i++) {
             for ($j = 19; $j <= $highestColumm; $j++) {
             }
             $regimen_code = $arr[$i]['S'];
             $regimen_desc = $arr[$i]['T'];
             $no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
             if ($no_of_clients_dispensed_in_period) {
                 $regimen_id = $regimen_code . " | " . $regimen_desc;
                 $query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
                 $results = $query->result_array();
                 $last_id = $results[0]['id'];
                 $last_id++;
                 $last_id = md5($last_id . $facility_code);
                 $next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
             }
         }
         //POST Exposure Prophylaxis(PEP)
         for ($i = 91; $i <= 99; $i++) {
             for ($j = 19; $j <= $highestColumm; $j++) {
             }
             $regimen_code = $arr[$i]['S'];
             $regimen_desc = $arr[$i]['T'];
             $no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
             if ($no_of_clients_dispensed_in_period) {
                 $regimen_id = $regimen_code . " | " . $regimen_desc;
                 $query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
                 $results = $query->result_array();
                 $last_id = $results[0]['id'];
                 $last_id++;
                 $last_id = md5($last_id . $facility_code);
                 $next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
             }
         }
         //ARV Data collection and Reporting Tools
         //1.Name of Data-DAR
         //a.ARVS Collection Tool
         $fifty_arv_page_requested = $arr[116]['D'];
         $three_hundred_arv_page_requested = $arr[116]['E'];
         if ($fifty_arv_page_requested) {
             $dar_arv_quantity_requested = $fifty_arv_page_requested;
         }
         if ($three_hundred_arv_page_requested) {
             $dar_arv_quantity_requested = $three_hundred_arv_page_requested;
         }
         //a.OIs Collection Tool
         $fifty_oi_page_requested = $arr[116]['G'];
         $three_hundred_oi_page_requested = $arr[116]['H'];
         if ($fifty_oi_page_requested) {
             $dar_oi_quantity_requested = $fifty_oi_page_requested;
         }
         if ($three_hundred_oi_page_requested) {
             $dar_oi_quantity_requested = $three_hundred_oi_page_requested;
         }
         //2.Name of Data-FCDRR
         $fcdrr_quantity_requested = $arr[116]['L'];
         //Prepared By details
         $report_prepared_by = $arr[119]['B'] . $arr[119]['C'] . $arr[119]['D'];
         $prepared_by_contact_telephone = $arr[121]['B'] . $arr[121]['C'] . $arr[121]['D'];
         $signature_prepared_by = $arr[119]['G'] . $arr[119]['H'] . $arr[119]['L'];
         $date_prepared_by_signature = $arr[121]['G'] . $arr[121]['H'];
         //Approved By details
         $report_approved_by = $arr[123]['B'] . $arr[123]['C'] . $arr[123]['D'];
         $approved_by_contact_telephone = $arr[126]['B'] . $arr[126]['C'] . $arr[126]['D'];
         $signature_approved_by = $arr[123]['G'] . $arr[123]['H'] . $arr[123]['L'];
         $date_approved_by_signature = $arr[126]['G'] . $arr[126]['H'];
         //$this -> session -> set_userdata('upload_counter','2');
         redirect("order_management/edit_order/{$order_number}");
     }
 }
<?php

/** Incluir la ruta **/
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');
/** Clases necesarias */
require_once 'PHPExcel.php';
require_once 'PHPExcel/Reader/Excel2007.php';
$VIEWDATA = array('Numero' => 0, 'Nombre' => 0, 'Nit' => 0);
// Cargando la hoja de cálculo
$objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load("Factura.xlsx");
// Asignar hoja de calculo activa
$objPHPExcel->setActiveSheetIndex(0);
$VIEWDATA['Numero'] = $objPHPExcel->getActiveSheet()->getCell('A2')->getCalculatedValue();
$VIEWDATA['Nombre'] = $objPHPExcel->getActiveSheet()->getCell('B2')->getCalculatedValue();
$VIEWDATA['Nit'] = $objPHPExcel->getActiveSheet()->getCell('C2')->getCalculatedValue();
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<table width="200" border="1">
  <tr>
    <td> NOmbree: <?php 
echo $VIEWDATA['Nombre'];
?>
 </td>
    <td><?php 
Пример #25
0
 public function import_data()
 {
     $this->load->database();
     header("Content-type:text/html;charset=utf-8");
     $this->load->model('Upfile');
     $url = $this->Upfile->member_upload($this->userinfo['id'], 'user_data');
     $url = substr($url, 1);
     $this->load->library('Phpexcel');
     $PHPReader = new PHPExcel_Reader_Excel2007();
     $PHPExcel = $PHPReader->load($url);
     $currentSheet = $PHPExcel->getSheet(0);
     /*取得一共有多少列*/
     $allColumn = $currentSheet->getHighestColumn();
     /*取得一共有多少行*/
     $allRow = $currentSheet->getHighestRow();
     for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
         $insert['mark'] = $currentSheet->getCell('A' . $currentRow)->getValue();
         $insert['name'] = $currentSheet->getCell('B' . $currentRow)->getValue();
         $insert['mobile'] = $currentSheet->getCell('C' . $currentRow)->getValue();
         $insert['address'] = $currentSheet->getCell('D' . $currentRow)->getValue();
         $insert['email'] = $currentSheet->getCell('E' . $currentRow)->getValue();
         $insert['point'] = $currentSheet->getCell('F' . $currentRow)->getValue();
         $insert['updatetime'] = time();
         $insert['clientid'] = $this->userinfo['id'];
         $num += $this->db->insert('temp_user', $insert);
     }
     echo "<script>window.location.href='/member/vipmember/viplist';</script>";
 }
 $archivo = $_FILES['excel']['name'];
 $tipo = $_FILES['excel']['type'];
 $destino = "bak_" . $archivo;
 if (copy($_FILES['excel']['tmp_name'], $destino)) {
     echo "File loaded successfully";
     echo "<br>";
 } else {
     echo "Error to load file";
 }
 if (file_exists("bak_" . $archivo)) {
     /** Clases necesarias */
     require_once 'Classes/PHPExcel.php';
     require_once 'Classes/PHPExcel/Reader/Excel2007.php';
     // Cargando la hoja de cálculo
     $objReader = new PHPExcel_Reader_Excel2007();
     $objPHPExcel = $objReader->load("bak_" . $archivo);
     $objPlant = $objReader->load("bak_" . $archivo);
     $objFree = $objReader->load("bak_" . $archivo);
     $objFecha = new PHPExcel_Shared_Date();
     // Asignar hoja de excel activa
     $objPHPExcel->setActiveSheetIndex(0);
     // Llenamos el arreglo con los datos  del archivo xlsx
     $Id_laboratorio = "";
     $Id_user = "";
     $Id_storage = "";
     for ($i = 3; $i <= $totalReHost; $i++) {
         $_DATOS_EXCEL[$i]['Name'] = $objPHPExcel->getActiveSheet()->getCell('B' . $i)->getCalculatedValue();
         $_DATOS_EXCEL[$i]['Date_of_collection'] = $objPHPExcel->getActiveSheet()->getCell('C' . $i)->getCalculatedValue();
         $_DATOS_EXCEL[$i]['Collected_by'] = $objPHPExcel->getActiveSheet()->getCell('D' . $i)->getCalculatedValue();
         $_DATOS_EXCEL[$i]['Site_alias'] = $objPHPExcel->getActiveSheet()->getCell('E' . $i)->getCalculatedValue();
         $_DATOS_EXCEL[$i]['id_storage'] = $objPHPExcel->getActiveSheet()->getCell('F' . $i)->getCalculatedValue();
Пример #27
0
 function procesarFormulario()
 {
     $conexion = "inventarios";
     $esteRecursoDB = $this->miConfigurador->fabricaConexiones->getRecursoDB($conexion);
     $cadenaSql = $this->miSql->getCadenaSql('consultar_entrada_acta', $_REQUEST['entrada']);
     $acta = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
     $acta = $acta[0][0];
     $cadenaSql = $this->miSql->getCadenaSql('consultar_elementos_acta', $acta);
     $elementos_acta = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
     $numero_elementos_acta = count($elementos_acta);
     $cadenaSql = $this->miSql->getCadenaSql('consultar_elementos_entrada', $_REQUEST['entrada']);
     $elementos_entrada = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
     $numero_elementos_entrada = count($elementos_entrada);
     // if($numero_elementos_acta==$numero_elementos_entrada){
     // redireccion::redireccionar ( 'noCargarElemento' );
     // }
     $fechaActual = date('Y-m-d');
     $_REQUEST['bodega'] = 0;
     $esteBloque = $this->miConfigurador->getVariableConfiguracion("esteBloque");
     $rutaBloque = $this->miConfigurador->getVariableConfiguracion("raizDocumento") . "/blocks/inventarios/gestionElementos/";
     $rutaBloque .= $esteBloque['nombre'];
     $host = $this->miConfigurador->getVariableConfiguracion("host") . $this->miConfigurador->getVariableConfiguracion("site") . "/blocks/inventarios/gestionEntradas/" . $esteBloque['nombre'];
     $_REQUEST['total_iva_con'] = round($_REQUEST['total_iva_con']);
     $cadenaSql = $this->miSql->getCadenaSql('idElementoMax');
     $elemento_id_max = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
     $elemento_id_max = $elemento_id_max[0][0] + 1;
     switch ($_REQUEST['tipo_registro']) {
         case '1':
             if ($_REQUEST['id_tipo_bien'] == 1) {
                 $cadenaSql = $this->miSql->getCadenaSql('consultar_placa', '1');
                 $placa = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
                 $arreglo = array($fechaActual, $_REQUEST['nivel'], $_REQUEST['id_tipo_bien'], $_REQUEST['descripcion'], $_REQUEST['cantidad'], $_REQUEST['unidad'], $_REQUEST['valor'], $_REQUEST['iva'], $_REQUEST['ajuste'] = 0, $_REQUEST['bodega'], $_REQUEST['subtotal_sin_iva'], $_REQUEST['total_iva'], $_REQUEST['total_iva_con'], $_REQUEST['marca'] != '' ? $_REQUEST['marca'] : 'null', $_REQUEST['serie'] != '' ? $_REQUEST['serie'] : 'null', $_REQUEST['entrada'], $elemento_id_max);
                 $cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_tipo_1', $arreglo);
                 $elemento = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
             } else {
                 if ($_REQUEST['id_tipo_bien'] == 2) {
                     $arreglo = array($fechaActual, $_REQUEST['nivel'], $_REQUEST['id_tipo_bien'], $_REQUEST['descripcion'], $_REQUEST['cantidad'] = 1, $_REQUEST['unidad'], $_REQUEST['valor'], $_REQUEST['iva'], $_REQUEST['ajuste'] = 0, $_REQUEST['bodega'], $_REQUEST['subtotal_sin_iva'], $_REQUEST['total_iva'], $_REQUEST['total_iva_con'], $_REQUEST['marca'] != '' ? $_REQUEST['marca'] : 'null', $_REQUEST['serie'] != '' ? $_REQUEST['serie'] : 'null', $_REQUEST['entrada'], $elemento_id_max);
                     $cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_tipo_1', $arreglo);
                     $elemento = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
                 } else {
                     if ($_REQUEST['id_tipo_bien'] == 3) {
                         if ($_REQUEST['tipo_poliza'] == 0) {
                             $arreglo = array($fechaActual, $_REQUEST['nivel'], $_REQUEST['id_tipo_bien'], $_REQUEST['descripcion'], $_REQUEST['cantidad'] = 1, $_REQUEST['unidad'], $_REQUEST['valor'], $_REQUEST['iva'], $_REQUEST['ajuste'] = 0, $_REQUEST['bodega'], $_REQUEST['subtotal_sin_iva'], $_REQUEST['total_iva'], $_REQUEST['total_iva_con'], $_REQUEST['tipo_poliza'], '0001-01-01', '0001-01-01', $_REQUEST['marca'] != '' ? $_REQUEST['marca'] : 'null', $_REQUEST['serie'] != '' ? $_REQUEST['serie'] : 'null', $_REQUEST['entrada'], $elemento_id_max);
                         } else {
                             if ($_REQUEST['tipo_poliza'] == 1) {
                                 $arreglo = array($fechaActual, $_REQUEST['nivel'], $_REQUEST['id_tipo_bien'], $_REQUEST['descripcion'], $_REQUEST['cantidad'] = 1, $_REQUEST['unidad'], $_REQUEST['valor'], $_REQUEST['iva'], $_REQUEST['ajuste'] = 0, $_REQUEST['bodega'], $_REQUEST['subtotal_sin_iva'], $_REQUEST['total_iva'], $_REQUEST['total_iva_con'], $_REQUEST['tipo_poliza'], $_REQUEST['fecha_inicio'], $_REQUEST['fecha_final'], $_REQUEST['marca'] != '' ? $_REQUEST['marca'] : 'null', $_REQUEST['serie'] != '' ? $_REQUEST['serie'] : 'null', $_REQUEST['entrada'], $elemento_id_max);
                             }
                         }
                         $cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_tipo_2', $arreglo);
                         $elemento = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
                     }
                 }
             }
             $placa = date('Ymd') . "00000";
             $cadenaSql = $this->miSql->getCadenaSql('buscar_repetida_placa', $placa);
             $num_placa = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
             $cadenaSql = $this->miSql->getCadenaSql('idElementoMaxIndividual');
             $elemento_id_max_indiv = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
             $elemento_id_max_indiv = $elemento_id_max_indiv[0][0] + 1;
             $sumaplaca = 0;
             if ($num_placa[0][0] == 0) {
                 for ($i = 0; $i < $_REQUEST['cantidad']; $i++) {
                     $arregloElementosInv = array($fechaActual, $_REQUEST['id_tipo_bien'] == 1 ? NULL : $placa + $sumaplaca, $_REQUEST['serie'] != '' ? $_REQUEST['serie'] : null, $elemento[0][0], $elemento_id_max_indiv);
                     $sumaplaca = $_REQUEST['id_tipo_bien'] == 1 ? $sumaplaca : $sumaplaca++;
                     $cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_individual', $arregloElementosInv);
                     $elemento_id[$i] = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
                     $elemento_id_max_indiv = $elemento_id_max_indiv + 1;
                 }
             } else {
                 if ($num_placa[0][0] != 0) {
                     $cadenaSql = $this->miSql->getCadenaSql('buscar_placa_maxima', $placa);
                     $num_placa = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
                     $placa = $num_placa[0][0];
                     $sumaplaca = 1;
                     for ($i = 1; $i <= $_REQUEST['cantidad']; $i++) {
                         $arregloElementosInv = array($fechaActual, $_REQUEST['id_tipo_bien'] == 1 ? NULL : $placa + $sumaplaca, $_REQUEST['serie'] != '' ? $_REQUEST['serie'] : null, $elemento[0][0], $elemento_id_max_indiv);
                         $sumaplaca = $_REQUEST['id_tipo_bien'] == 1 ? $sumaplaca : $sumaplaca++;
                         $cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_individual', $arregloElementosInv);
                         $elemento_id[$i] = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
                         $elemento_id_max_indiv = $elemento_id_max_indiv + 1;
                     }
                 }
             }
             foreach ($_FILES as $key => $values) {
                 $archivo = $_FILES[$key];
             }
             if ($archivo['type'] == 'image/jpeg') {
                 $data = base64_encode(file_get_contents($archivo['tmp_name']));
                 // Prioridad 1 ('Principal') 0 ('No Principal')
                 $arreglo = array("elemento" => $elemento_id_max, "prioridad" => "1", "imagen" => $data);
                 $cadenaSql = $this->miSql->getCadenaSql('ElementoImagen', $arreglo);
                 $elemento = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
             }
             $datos = array($elemento[0][0], $fechaActual, $_REQUEST['entrada']);
             if ($elemento) {
                 redireccion::redireccionar('inserto', $datos, $_REQUEST['datosGenerales']);
                 exit;
             } else {
                 redireccion::redireccionar('noInserto', $datos);
                 exit;
             }
             break;
         case '2':
             $ingreso = 0;
             $ruta_eliminar_xlsx = $rutaBloque . "/archivo/*.xlsx";
             $ruta_eliminar_xls = $rutaBloque . "/archivo/*.xls";
             foreach (glob($ruta_eliminar_xlsx) as $filename) {
                 unlink($filename);
             }
             foreach (glob($ruta_eliminar_xls) as $filename) {
                 unlink($filename);
             }
             $i = 0;
             foreach ($_FILES as $key => $values) {
                 $archivo[$i] = $_FILES[$key];
                 $i++;
             }
             $archivo = $archivo[0];
             $trozos = explode(".", $archivo['name']);
             $extension = end($trozos);
             if ($extension == 'xlsx' || $extension == 'xls') {
                 if ($archivo) {
                     // obtenemos los datos del archivo
                     $tamano = $archivo['size'];
                     $tipo = $archivo['type'];
                     $archivo1 = $archivo['name'];
                     $prefijo = "archivo";
                     if ($archivo1 != "") {
                         // guardamos el archivo a la carpeta files
                         $ruta_absoluta = $rutaBloque . "/archivo/" . $prefijo . "_" . $archivo1;
                         if (copy($archivo['tmp_name'], $ruta_absoluta)) {
                             $status = "Archivo subido: <b>" . $archivo1 . "</b>";
                             $destino1 = $host . "/archivo/" . $prefijo . "_" . $archivo1;
                         } else {
                             $status = "Error al subir el archivo";
                             echo $status;
                         }
                     } else {
                         $status = "Error al subir archivo";
                         echo $status;
                     }
                 }
                 $arreglo = array($destino1, $archivo1);
                 if (file_exists($ruta_absoluta)) {
                     // Cargando la hoja de cálculo
                     $objReader = new \PHPExcel_Reader_Excel2007();
                     $objPHPExcel = $objReader->load($ruta_absoluta);
                     $objFecha = new \PHPExcel_Shared_Date();
                     // Asignar hoja de excel activa
                     $objPHPExcel->setActiveSheetIndex(0);
                     $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
                     $highestRow = $objWorksheet->getHighestRow();
                     for ($i = 2; $i <= $highestRow; $i++) {
                         $datos[$i]['Nivel'] = $objPHPExcel->getActiveSheet()->getCell('A' . $i)->getCalculatedValue();
                         $datos[$i]['Tipo_Bien'] = $objPHPExcel->getActiveSheet()->getCell('B' . $i)->getCalculatedValue();
                         $datos[$i]['Descripcion'] = $objPHPExcel->getActiveSheet()->getCell('C' . $i)->getCalculatedValue();
                         $datos[$i]['Cantidad'] = $objPHPExcel->getActiveSheet()->getCell('D' . $i)->getCalculatedValue();
                         $datos[$i]['Unidad_Medida'] = $objPHPExcel->getActiveSheet()->getCell('E' . $i)->getCalculatedValue();
                         $datos[$i]['Valor_Precio'] = $objPHPExcel->getActiveSheet()->getCell('F' . $i)->getCalculatedValue();
                         $datos[$i]['Iva'] = $objPHPExcel->getActiveSheet()->getCell('G' . $i)->getCalculatedValue();
                         $datos[$i]['Tipo_poliza'] = $objPHPExcel->getActiveSheet()->getCell('H' . $i)->getCalculatedValue();
                         $datos[$i]['Fecha_Inicio_Poliza'] = $objPHPExcel->getActiveSheet()->getCell('I' . $i)->getCalculatedValue();
                         $datos[$i]['Fecha_Final_Poliza'] = $objPHPExcel->getActiveSheet()->getCell('J' . $i)->getCalculatedValue();
                         $datos[$i]['Marca'] = $objPHPExcel->getActiveSheet()->getCell('K' . $i)->getCalculatedValue();
                         $datos[$i]['Serie'] = $objPHPExcel->getActiveSheet()->getCell('L' . $i)->getCalculatedValue();
                     }
                     for ($i = 2; $i <= $highestRow; $i++) {
                         switch ($datos[$i]['Iva']) {
                             case "1":
                                 $IVA = 0;
                                 break;
                             case "2":
                                 $IVA = 0;
                                 break;
                             case "3":
                                 $IVA = 0.05;
                                 break;
                             case "4":
                                 $IVA = 0.04;
                                 break;
                             case "5":
                                 $IVA = 0.1;
                                 break;
                             case "6":
                                 $IVA = 0.16;
                                 break;
                         }
                         $cadenaSql = $this->miSql->getCadenaSql('idElementoMax');
                         $elemento_id_max = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
                         $elemento_id_max = $elemento_id_max[0][0] + 1;
                         // $arreglo = array (
                         // $fechaActual,
                         // $datos [$i] ['Nivel'],
                         // $datos [$i] ['Tipo_Bien'],
                         // trim ( $datos [$i] ['Descripcion'], "'" ),
                         // $datos [$i] ['Cantidad'],
                         // trim ( $datos [$i] ['Unidad_Medida'], "'" ),
                         // $datos [$i] ['Valor_Precio'],
                         // $datos [$i] ['Ajuste'],
                         // $datos [$i] ['Bodega'],
                         // $datos [$i] ['Cantidad'] * $datos [$i] ['Valor_Precio'],
                         // $datos [$i] ['Cantidad'] * $datos [$i] ['Valor_Precio'] * $datos [$i] ['Iva'],
                         // round ( $datos [$i] ['Cantidad'] * $datos [$i] ['Valor_Precio'] * $datos [$i] ['Iva'] ) + ($datos [$i] ['Cantidad'] * $datos [$i] ['Valor_Precio']),
                         // $datos [$i] ['Tipo_poliza'],
                         // trim ( $datos [$i] ['Fecha_Inicio_Poliza'], "'" ),
                         // trim ( $datos [$i] ['Fecha_Final_Poliza'], "'" ),
                         // trim ( $datos [$i] ['Marca'], "'" ),
                         // trim ( $datos [$i] ['Serie'], "'" ),
                         // $datos [$i] ['Entrada']
                         // );
                         if ($datos[$i]['Tipo_Bien'] == 1) {
                             $cadenaSql = $this->miSql->getCadenaSql('consultar_placa', '1');
                             $placa = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
                             // "1";0;"Exento";
                             // "2";0;"Tarifa de Cero";
                             // "3";0.05;"5%";
                             // "4";0.04;"4%";
                             // "5";0.1;"10%";
                             // "6";0.16;"16%";
                             $arreglo = array($fechaActual, $datos[$i]['Nivel'], $datos[$i]['Tipo_Bien'], trim($datos[$i]['Descripcion'], "'"), $datos[$i]['Cantidad'], trim($datos[$i]['Unidad_Medida'], "'"), $datos[$i]['Valor_Precio'], $datos[$i]['Iva'], $_REQUEST['ajuste'] = 0, $_REQUEST['bodega'], $datos[$i]['Cantidad'] * $datos[$i]['Valor_Precio'], $datos[$i]['Cantidad'] * $datos[$i]['Valor_Precio'] * $IVA, round($datos[$i]['Cantidad'] * $datos[$i]['Valor_Precio'] * $IVA) + $datos[$i]['Cantidad'] * $datos[$i]['Valor_Precio'], is_null($datos[$i]['Marca']) == true ? 'null' : trim($datos[$i]['Marca'], "'"), is_null($datos[$i]['Serie']) == true ? 'null' : trim($datos[$i]['Serie'], "'"), $_REQUEST['entrada'], $elemento_id_max);
                             $cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_tipo_1', $arreglo);
                             $elemento = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
                         } else {
                             if ($datos[$i]['Tipo_Bien'] == 2) {
                                 $datos[$i]['Cantidad'] = 1;
                                 // $arreglo = array (
                                 // $fechaActual,
                                 // $_REQUEST ['nivel'],
                                 // $_REQUEST ['id_tipo_bien'],
                                 // $_REQUEST ['descripcion'],
                                 // $_REQUEST ['cantidad'] = 1,
                                 // $_REQUEST ['unidad'],
                                 // $_REQUEST ['valor'],
                                 // $_REQUEST ['iva'],
                                 // $_REQUEST ['ajuste'] = 0,
                                 // $_REQUEST ['bodega'],
                                 // $_REQUEST ['subtotal_sin_iva'],
                                 // $_REQUEST ['total_iva'],
                                 // $_REQUEST ['total_iva_con'],
                                 // ($_REQUEST ['marca'] != '') ? $_REQUEST ['marca'] : 'null',
                                 // ($_REQUEST ['serie'] != '') ? $_REQUEST ['serie'] : 'null',
                                 // $_REQUEST ['entrada'],
                                 // $elemento_id_max
                                 // );
                                 $arreglo = array($fechaActual, $datos[$i]['Nivel'], $datos[$i]['Tipo_Bien'], trim($datos[$i]['Descripcion'], "'"), 1, trim($datos[$i]['Unidad_Medida'], "'"), $datos[$i]['Valor_Precio'], $datos[$i]['Iva'], $_REQUEST['ajuste'] = 0, $_REQUEST['bodega'], 1 * $datos[$i]['Valor_Precio'], 1 * $datos[$i]['Valor_Precio'] * $IVA, round(1 * $datos[$i]['Valor_Precio'] * $IVA) + 1 * $datos[$i]['Valor_Precio'], is_null($datos[$i]['Marca']) == true ? 'null' : trim($datos[$i]['Marca'], "'"), is_null($datos[$i]['Serie']) == true ? 'null' : trim($datos[$i]['Serie'], "'"), $_REQUEST['entrada'], $elemento_id_max);
                                 $cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_tipo_1', $arreglo);
                                 $elemento = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
                             } else {
                                 if ($datos[$i]['Tipo_Bien'] == 3) {
                                     $datos[$i]['Cantidad'] = 1;
                                     if ($datos[$i]['Tipo_poliza'] == 0) {
                                         $arreglo = array($fechaActual, $datos[$i]['Nivel'], $datos[$i]['Tipo_Bien'], trim($datos[$i]['Descripcion'], "'"), 1, trim($datos[$i]['Unidad_Medida'], "'"), $datos[$i]['Valor_Precio'], $datos[$i]['Iva'], $_REQUEST['ajuste'] = 0, $_REQUEST['bodega'], 1 * $datos[$i]['Valor_Precio'], 1 * $datos[$i]['Valor_Precio'] * $IVA, round(1 * $datos[$i]['Valor_Precio'] * $IVA) + 1 * $datos[$i]['Valor_Precio'], $datos[$i]['Tipo_poliza'], 'NULL', 'NULL', is_null($datos[$i]['Marca']) == true ? 'null' : trim($datos[$i]['Marca'], "'"), is_null($datos[$i]['Serie']) == true ? 'null' : trim($datos[$i]['Serie'], "'"), $_REQUEST['entrada'], $elemento_id_max);
                                         // $arreglo = array (
                                         // $fechaActual,
                                         // $_REQUEST ['nivel'],
                                         // $_REQUEST ['id_tipo_bien'],
                                         // $_REQUEST ['descripcion'],
                                         // $_REQUEST ['cantidad'] = 1,
                                         // $_REQUEST ['unidad'],
                                         // $_REQUEST ['valor'],
                                         // $_REQUEST ['iva'],
                                         // $_REQUEST ['ajuste'] = 0,
                                         // $_REQUEST ['bodega'],
                                         // $_REQUEST ['subtotal_sin_iva'],
                                         // $_REQUEST ['total_iva'],
                                         // $_REQUEST ['total_iva_con'],
                                         // $_REQUEST ['tipo_poliza'],
                                         // 'NULL',
                                         // 'NULL',
                                         // ($_REQUEST ['marca'] != '') ? $_REQUEST ['marca'] : 'null',
                                         // ($_REQUEST ['serie'] != '') ? $_REQUEST ['serie'] : 'null',
                                         // $_REQUEST ['entrada'],
                                         // $elemento_id_max
                                         // );
                                     } else {
                                         if ($datos[$i]['Tipo_poliza'] == 1) {
                                             $arreglo = array($fechaActual, $datos[$i]['Nivel'], $datos[$i]['Tipo_Bien'], trim($datos[$i]['Descripcion'], "'"), 1, trim($datos[$i]['Unidad_Medida'], "'"), $datos[$i]['Valor_Precio'], $datos[$i]['Iva'], $_REQUEST['ajuste'] = 0, $_REQUEST['bodega'], 1 * $datos[$i]['Valor_Precio'], 1 * $datos[$i]['Valor_Precio'] * $IVA, round(1 * $datos[$i]['Valor_Precio'] * $IVA) + 1 * $datos[$i]['Valor_Precio'], $datos[$i]['Tipo_poliza'], trim($datos[$i]['Fecha_Inicio_Poliza'], "'"), trim($datos[$i]['Fecha_Final_Poliza'], "'"), is_null($datos[$i]['Marca']) == true ? 'null' : trim($datos[$i]['Marca'], "'"), is_null($datos[$i]['Serie']) == true ? 'null' : trim($datos[$i]['Serie'], "'"), $_REQUEST['entrada'], $elemento_id_max);
                                             // $arreglo = array (
                                             // $fechaActual,
                                             // $_REQUEST ['nivel'],
                                             // $_REQUEST ['id_tipo_bien'],
                                             // $_REQUEST ['descripcion'],
                                             // $_REQUEST ['cantidad'] = 1,
                                             // $_REQUEST ['unidad'],
                                             // $_REQUEST ['valor'],
                                             // $_REQUEST ['iva'],
                                             // $_REQUEST ['ajuste'] = 0,
                                             // $_REQUEST ['bodega'],
                                             // $_REQUEST ['subtotal_sin_iva'],
                                             // $_REQUEST ['total_iva'],
                                             // $_REQUEST ['total_iva_con'],
                                             // $_REQUEST ['tipo_poliza'],
                                             // $_REQUEST ['fecha_inicio'],
                                             // $_REQUEST ['fecha_final'],
                                             // ($_REQUEST ['marca'] != '') ? $_REQUEST ['marca'] : 'null',
                                             // ($_REQUEST ['serie'] != '') ? $_REQUEST ['serie'] : 'null',
                                             // $_REQUEST ['entrada'],
                                             // $elemento_id_max
                                             // );
                                         }
                                     }
                                     $cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_tipo_2', $arreglo);
                                     $elemento = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
                                 }
                             }
                         }
                         // $cadenaSql = $this->miSql->getCadenaSql ( 'ingresar_elemento_masivo', $arreglo );
                         // $elemento = $esteRecursoDB->ejecutarAcceso ( $cadenaSql, "busqueda" );
                         // $_REQUEST ['cantidad'] = $datos [$i] ['Cantidad'];
                         // $_REQUEST ['serie'] = trim ( $datos [$i] ['Serie'], "'" );
                         // $ingreso = 1;
                         $placa = date('Ymd') . "00000";
                         $cadenaSql = $this->miSql->getCadenaSql('buscar_repetida_placa', $placa);
                         $num_placa = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
                         $cadenaSql = $this->miSql->getCadenaSql('idElementoMaxIndividual');
                         $elemento_id_max_indiv = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
                         $elemento_id_max_indiv = $elemento_id_max_indiv[0][0] + 1;
                         $sumaplaca = 0;
                         switch ($datos[$i]['Tipo_Bien']) {
                             case '1':
                                 $_REQUEST['cantidad'] = $datos[$i]['Cantidad'];
                                 break;
                             case '2':
                                 $_REQUEST['cantidad'] = 1;
                                 break;
                             case '3':
                                 $_REQUEST['cantidad'] = 1;
                                 break;
                         }
                         if ($num_placa[0][0] == 0) {
                             for ($j = 0; $j < $_REQUEST['cantidad']; $j++) {
                                 $arregloElementosInv = array($fechaActual, $datos[$i]['Tipo_Bien'] == 1 ? NULL : $placa + $sumaplaca, is_null($datos[$i]['Serie']) == true ? 'null' : trim($datos[$i]['Serie'], "'"), $elemento[0][0], $elemento_id_max_indiv);
                                 $sumaplaca = $datos[$i]['Tipo_Bien'] == 1 ? $sumaplaca : $sumaplaca++;
                                 $cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_individual', $arregloElementosInv);
                                 $elemento_id[$j] = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
                                 $elemento_id_max_indiv = $elemento_id_max_indiv + 1;
                             }
                         } else {
                             if ($num_placa[0][0] != 0) {
                                 $cadenaSql = $this->miSql->getCadenaSql('buscar_placa_maxima', $placa);
                                 $num_placa = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
                                 $placa = $num_placa[0][0];
                                 $sumaplaca = 1;
                                 for ($j = 1; $j <= $_REQUEST['cantidad']; $j++) {
                                     $arregloElementosInv = array($fechaActual, $datos[$i]['Tipo_Bien'] == 1 ? NULL : $placa + $sumaplaca, is_null($datos[$i]['Serie']) == true ? 'null' : trim($datos[$i]['Serie'], "'"), $elemento[0][0], $elemento_id_max_indiv);
                                     $sumaplaca = $datos[$i]['Tipo_Bien'] == 1 ? $sumaplaca : $sumaplaca++;
                                     $cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_individual', $arregloElementosInv);
                                     $elemento_id[$j] = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
                                     $elemento_id_max_indiv = $elemento_id_max_indiv + 1;
                                 }
                             }
                         }
                         // 							$cadenaSql = $this->miSql->getCadenaSql ( 'buscar_repetida_placa', $placa );
                         // 							$num_placa = $esteRecursoDB->ejecutarAcceso ( $cadenaSql, "busqueda" );
                         // 							if ($num_placa [0] [0] == 0) {
                         // 								for($i = 0; $i < $_REQUEST ['cantidad']; $i ++) {
                         // 									$arregloElementosInv = array (
                         // 											$fechaActual,
                         // 											$placa + $i,
                         // 											$_REQUEST ['serie'],
                         // 											$elemento [0] [0]
                         // 									);
                         // 									$cadenaSql = $this->miSql->getCadenaSql ( 'ingresar_elemento_individual', $arregloElementosInv );
                         // 									$elemento_id [$i] = $esteRecursoDB->ejecutarAcceso ( $cadenaSql, "busqueda" );
                         // 								}
                         // 							} else if ($num_placa [0] [0] != 0) {
                         // 								$cadenaSql = $this->miSql->getCadenaSql ( 'buscar_placa_maxima', $placa );
                         // 								$num_placa = $esteRecursoDB->ejecutarAcceso ( $cadenaSql, "busqueda" );
                         // 								$placa = $num_placa [0] [0];
                         // 								for($i = 1; $i <= $_REQUEST ['cantidad']; $i ++) {
                         // 									$arregloElementosInv = array (
                         // 											$fechaActual,
                         // 											$placa + $i,
                         // 											$_REQUEST ['serie'],
                         // 											$elemento [0] [0]
                         // 									);
                         // 									$cadenaSql = $this->miSql->getCadenaSql ( 'ingresar_elemento_individual', $arregloElementosInv );
                         // 									$elemento_id [$i] = $esteRecursoDB->ejecutarAcceso ( $cadenaSql, "busqueda" );
                         // 								}
                         // 							}
                     }
                     if ($elemento) {
                         redireccion::redireccionar('inserto_M', $fechaActual);
                         exit;
                     } else {
                         redireccion::redireccionar('noInserto', $datos);
                         exit;
                     }
                 }
             } else {
                 redireccion::redireccionar('noExtension');
             }
             break;
     }
 }
Пример #28
0
 /**
  * @param filename 上传的文件
  * $param exts 文件类型
  */
 public function goods_import($filename, $exts = 'xls')
 {
     //导入PHPExcel类库,因为PHPExcel没有用命名空间,只能inport导入
     require_once APP_PATH . 'PHPExcel/PHPExcel.php';
     require_once APP_PATH . 'PHPExcel/PHPExcel/Reader/Excel5.php';
     // 用于其他低版本xls
     require_once APP_PATH . 'PHPExcel/PHPExcel/Reader/Excel2007.php';
     // 用于 excel-2007 格式
     //创建PHPExcel对象,注意,不能少了\
     $PHPExcel = new PHPExcel();
     //如果excel文件后缀名为.xls,导入这个类
     if ($exts == 'xls') {
         $PHPReader = new PHPExcel_Reader_Excel5();
     } elseif ($exts == 'xlsx') {
         $PHPReader = new PHPExcel_Reader_Excel2007();
     } else {
         exit('错误的格式');
     }
     //载入文件
     $PHPExcel = $PHPReader->load($filename);
     //获取表中的第一个工作表,如果要获取第二个,把0改为1,依次类推
     $currentSheet = $PHPExcel->getSheet(0);
     //获取总列数
     $allColumn = $currentSheet->getHighestColumn();
     //获取总行数
     $allRow = $currentSheet->getHighestRow();
     //循环获取表中的数据,$currentRow表示当前行,从哪行开始读取数据,索引值从0开始
     for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
         //从哪列开始,A表示第一列
         for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
             //数据坐标
             $address = $currentColumn . $currentRow;
             //读取到的数据,保存到数组$arr中
             $data[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
         }
     }
     foreach ($data as $info) {
         $nickname = $info['A'];
         $country = trim($info['C']);
         $province = trim($info['D']);
         $city = trim($info['E']);
         $headimgurl = trim($info['F']);
         $sex_text = trim($info['B']);
         switch ($sex_text) {
             case "男":
                 $sex = 1;
                 break;
             case "女":
                 $sex = 2;
                 break;
             case "保密":
                 $sex = 0;
                 break;
             default:
                 $sex = 0;
                 break;
         }
         if (!empty($nickname) && !empty($headimgurl)) {
             $data['nickname'] = $nickname;
             $data['sex'] = $sex;
             $data['country'] = $country;
             $data['province'] = $province;
             $data['city'] = $city;
             $data['headimgurl'] = $headimgurl;
             $result = M("dummy_user")->add($data);
         }
     }
 }
Пример #29
0
 private function read_excel($filename, $extension)
 {
     require "./././Class/PHPExcel.php";
     $PHPExcel = new PHPExcel();
     if ($extension == '.xls') {
         require "./././Class/PHPExcel/Reader/Excel5.php";
         $PHPReader = new PHPExcel_Reader_Excel5();
     } elseif ($extension == '.xlsx') {
         require "./././Class/PHPExcel/Reader/Excel2007.php";
         $PHPReader = new PHPExcel_Reader_Excel2007();
     }
     //读取excel文件
     $PHPExcel = $PHPReader->load($filename);
     //获取Sheet0中的数据
     $currentSheet = $PHPExcel->getSheet(0);
     //获取行数、列数
     $allColumn = $currentSheet->getHighestColumn();
     $allRow = $currentSheet->getHighestRow();
     //循环遍历excel表格,重点是getCell和getValue方法
     for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
         for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
             $address = $currentColumn . $currentRow;
             $data[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
         }
     }
     //循环所得数据插入数据库,至于从2开始嘛,执行var_dump($data)你就懂了
     //echo "<pre>";
     // var_dump($data);die;
     $this->load->model('Admin_Model');
     for ($i = 2; $i <= count($data); $i++) {
         $school = $data[$i]['A'];
         $find = $this->Admin_Model->check($school, 'school', 'school');
         if (!empty($find)) {
             $school_id = $find['id'];
         } else {
             $school_id = $this->Admin_Model->schooladd($school);
         }
         $name = $data[$i]['B'];
         $this->Admin_Model->joineradd($name, $school_id);
     }
     redirect('/admin');
 }
Пример #30
0
 public function getWorkbook($isExcelFile = false)
 {
     $indpapers = @$this->params['indpapers'];
     $vuzes = $this->getReportData();
     $vuzes = $this->formtable($vuzes);
     $xls_reader = new PHPExcel_Reader_Excel2007();
     $workbook = $xls_reader->load(APPLICATION_PATH . 'modules/stat/templates/report5.xlsx');
     $sheet = $workbook->getActiveSheet();
     $sheet->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd($indpapers ? 1 : 4, 8);
     // В шаблоне пока не работает
     $foreighnStr = $this->params['countrytype'] == 0 ? 'вітчизняних та іноземних ' : ($this->getAddValue($this->params['countrytype'], '_ADD_ORIGINID') == 'УКР' ? 'вітчизняних ' : 'іноземних ');
     $eduBaseStr = $this->getAddValue($this->params['edubase'], '_ADD_ORIGINID') == 'Б' ? 'держзамовленням' : 'контрактом';
     $eduForms = array('ДН' => 'що навчаються', 'ВЧ' => 'що навчаються вечірньо', 'ЗЧ' => 'що навчаються заочно', 'ДЦ' => 'що навчаються дистанційно', 'ДВ' => 'що отримують другу вищу освіту');
     $eduFormOriginID = $this->getAddValue($this->params['eduform'], '_ADD_ORIGINID');
     $eduFormStr = $eduForms[$eduFormOriginID] . ' ';
     switch ($eduFormOriginID) {
         case 'ЗЧ':
         case 'ДВ':
             $coefficient = 0.1;
             break;
         case 'ВЧ':
             $coefficient = 0.25;
             break;
         default:
             $coefficient = 1;
     }
     $endDate = new DateTime($this->getAddValueDate($this->params['period'], '_ADD_ENDDATE'));
     if (@$this->params['reportplankind']) {
         $selectedReportPlan = $this->getGuideNodeTitle($this->params['reportplankind']);
         $planKindNum = $this->getAddValue($this->params['reportplankind'], '_ADD_ORIGINID') - 1;
         $endDate->modify("+{$planKindNum} year");
     } else {
         $selectedReportPlan = 'Уточнений план';
     }
     $selectedYear = $endDate->format('Y');
     $sheet->setCellValue('A1', sprintf($sheet->getCell('A1')->getValue(), $selectedReportPlan, $foreighnStr, $eduFormStr, $eduBaseStr, $selectedYear));
     $current_row = 9;
     //    $sheet->getStyle("B17")->getNumberFormat()->setFormatCode('dd.mm.yyyy'); // Если просто задано в шаблоне, то не работает в HTML
     $sheet->insertNewRowBefore($current_row + 1, count($vuzes) * 3);
     // Название вуза + всего + в том числе
     $allEduDirections = array();
     $groupEduDirections = array();
     $vuzezInGroupCount = 0;
     $lastVuzGroup = null;
     foreach ($vuzes as $vuznum => $vuz) {
         if ($indpapers) {
             if ($vuzezInGroupCount > 0) {
                 $sheet->setBreak("A" . ($current_row - 1), PHPExcel_Worksheet::BREAK_ROW);
             }
         } else {
             if ($lastVuzGroup != $vuz['group']) {
                 if ($lastVuzGroup) {
                     $this->showTotal($sheet, $current_row, $groupEduDirections, false, $vuzezInGroupCount > 1);
                 }
                 $vuzezInGroupCount = 0;
                 $lastVuzGroup = $vuz['group'];
                 $groupEduDirections = array();
                 if (!@$this->params['establishment']) {
                     $sheet->insertNewRowBefore($current_row + 1, 1);
                     $sheet->setCellValueByColumnAndRow(0, $current_row, $lastVuzGroup == 1 ? 'ВНЗ 1-2 рівня (2301120)' : 'ВНЗ 3-4 рівня (2301070)');
                     $sheet->mergeCellsByColumnAndRow(0, $current_row, 19, $current_row);
                     $sheet->getStyle("A{$current_row}:T{$current_row}")->getFont()->setBold(true)->setSize(12);
                     $sheet->getStyle("A{$current_row}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                     $current_row++;
                 }
             }
             if ($vuzezInGroupCount > 0) {
                 // Разделительная линия между вузами внутри группы
                 $sheet->insertNewRowBefore($current_row + 1, 1);
                 $sheet->getRowDimension($current_row)->setRowHeight(0.75);
                 $current_row++;
             }
         }
         $vuzezInGroupCount++;
         $vuzrow = $current_row + 1;
         // Запоминаем строку с суммами по вузу!!!
         $sheet->setCellValueByColumnAndRow(0, $current_row, $vuznum + 1);
         $sheet->setCellValueByColumnAndRow(1, $current_row, $vuz['title']);
         $sheet->mergeCellsByColumnAndRow(1, $current_row, 19, $current_row);
         $next_row = $current_row + 1;
         $sheet->getStyle("A{$current_row}:T{$next_row}")->getFont()->setBold(true);
         $sheet->getStyle("A{$current_row}:T{$next_row}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($this->colorLevel1);
         $sheet->getStyle("B{$current_row}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
         $current_row++;
         $sheet->setCellValueByColumnAndRow(1, $current_row, $selectedYear);
         $current_row++;
         $sheet->setCellValueByColumnAndRow(1, $current_row, 'в тому числі');
         $current_row++;
         $dir_count = count($vuz['DATA']);
         $sheet->insertNewRowBefore($current_row + 1, $dir_count);
         // Направления подготовки
         $dirrows = array();
         foreach ($vuz['DATA'] as $dirnum => $dir) {
             $dirrow = $current_row;
             if (!@array_key_exists($dir['title'], $groupEduDirections)) {
                 $groupEduDirections[$dir['title']] = array();
             }
             $currentGroupEduDirection =& $groupEduDirections[$dir['title']];
             if (!@array_key_exists($dir['title'], $allEduDirections)) {
                 $allEduDirections[$dir['title']] = array();
             }
             $currentAllEduDirection =& $allEduDirections[$dir['title']];
             $dirrows[$dirnum] = $current_row;
             $sheet->setCellValueByColumnAndRow(1, $current_row, 'напрям "' . $dir['title'] . '"');
             $sheet->getStyle("A{$current_row}:T{$current_row}")->getFont()->setBold(true);
             $sheet->getStyle("A{$current_row}:T{$current_row}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($this->colorLevel2);
             $current_row++;
             $levels_count = count($dir['DATA']);
             $sheet->insertNewRowBefore($current_row + 1, $levels_count);
             // Уровни подготовки
             $edulevelrows = array();
             foreach ($dir['DATA'] as $edulevelnum => $edulevel) {
                 if (!@array_key_exists($edulevel['title'], $currentGroupEduDirection)) {
                     $currentGroupEduDirection[$edulevel['title']] = array();
                 }
                 $currentGroupEduLevel =& $currentGroupEduDirection[$edulevel['title']];
                 if (!@array_key_exists($edulevel['title'], $currentAllEduDirection)) {
                     $currentAllEduDirection[$edulevel['title']] = array();
                 }
                 $currentAllEduLevel =& $currentAllEduDirection[$edulevel['title']];
                 $edulevelrows[$edulevelnum] = $current_row;
                 $speciality_count = count($edulevel['DATA'], 0);
                 $sheet->insertNewRowBefore($current_row + 2, $speciality_count);
                 $formula_row1 = $current_row + 1;
                 $formula_row2 = $current_row + $speciality_count;
                 // Всего по уровню подготовки
                 for ($col = 2; $col <= 19; $col++) {
                     if ($col == 15) {
                         continue;
                     }
                     $colStr = PHPExcel_Cell::stringFromColumnIndex($col);
                     $sheet->setCellValueByColumnAndRow($col, $current_row, "=SUM({$colStr}{$formula_row1}:{$colStr}{$formula_row2})");
                 }
                 $sheet->setCellValueByColumnAndRow(1, $current_row, "рівень \"{$edulevel['title']}\"");
                 $sheet->getStyle("A{$current_row}:T{$current_row}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($this->colorLevel3);
                 $current_row++;
                 foreach ($edulevel['DATA'] as $specialitynum => $speciality) {
                     if (!@array_key_exists($speciality['title'], $currentGroupEduLevel)) {
                         $currentGroupEduLevel[$speciality['title']] = array($current_row);
                     } else {
                         array_push($currentGroupEduLevel[$speciality['title']], $current_row);
                     }
                     if (!@array_key_exists($speciality['title'], $currentAllEduLevel)) {
                         $currentAllEduLevel[$speciality['title']] = array($current_row);
                     } else {
                         array_push($currentAllEduLevel[$speciality['title']], $current_row);
                     }
                     $sheet->setCellValueByColumnAndRow(1, $current_row, $speciality['title']);
                     $sheet->getStyle("B{$current_row}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
                     foreach (array('CS', null, 'CEXIT4', 'CEXIT3', 'CEXIT2', 'CEXIT1', null, 'CENTER4', 'CENTER3', 'CENTER2', 'CENTER1', null, 'MOVEOUT', null, 'MOVEIN') as $colnum => $column) {
                         if ($column) {
                             $cell = $sheet->setCellValueByColumnAndRow($colnum + 2, $current_row, array_key_exists($column, $speciality) ? $speciality[$column]['val'] : 0, true);
                             if (!$isExcelFile && @$speciality[$column]['kindid']) {
                                 $ids = implode(',', $speciality[$column]['ids']);
                                 //                  alert('datareportid = {$speciality[$column]['datareportid']}; kindid={$speciality[$column]['kindid']}; paramgroupids=$ids')
                                 $cell->getHyperlink()->setUrl("javascript:showValidation({$speciality[$column]['datareportid']},{$speciality[$column]['kindid']},'{$ids}')");
                             }
                         }
                     }
                     $sheet->setCellValueByColumnAndRow(3, $current_row, "=SUM(E{$current_row}:H{$current_row})");
                     $sheet->setCellValueByColumnAndRow(8, $current_row, "=SUM(J{$current_row}:M{$current_row})");
                     $sheet->setCellValueByColumnAndRow(13, $current_row, "=C{$current_row}-D{$current_row}+I{$current_row}");
                     $sheet->setCellValueByColumnAndRow(17, $current_row, "=N{$current_row}-O{$current_row}+Q{$current_row}");
                     $sheet->setCellValueByColumnAndRow(18, $current_row, "=(C{$current_row}*8.5+R{$current_row}*3.5)/12");
                     $sheet->setCellValueByColumnAndRow(19, $current_row, "=S{$current_row}*{$coefficient}");
                     $current_row++;
                 }
             }
             for ($col = 2; $col <= 19; $col++) {
                 if ($col == 15) {
                     continue;
                 }
                 $colStr = PHPExcel_Cell::stringFromColumnIndex($col);
                 $sheet->setCellValueByColumnAndRow($col, $dirrow, "={$colStr}" . implode("+{$colStr}", $edulevelrows));
             }
         }
         for ($col = 2; $col <= 19; $col++) {
             if ($col == 15) {
                 continue;
             }
             $colStr = PHPExcel_Cell::stringFromColumnIndex($col);
             $sheet->setCellValueByColumnAndRow($col, $vuzrow, "={$colStr}" . implode("+{$colStr}", $dirrows));
         }
         if ($indpapers) {
             $sheet->insertNewRowBefore($current_row, 4);
             $sheet->getStyle('A' . $current_row . ':T' . ($current_row + 3))->applyFromArray(array('font' => array('bold' => true, 'size' => 10), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_NONE))));
             $sheet->mergeCellsByColumnAndRow(1, $current_row + 1, 8, $current_row + 1);
             $sheet->setCellValueByColumnAndRow(1, $current_row + 1, @$vuz['STAT2_POST']);
             $sheet->mergeCellsByColumnAndRow(1, $current_row + 2, 8, $current_row + 2);
             $sheet->setCellValueByColumnAndRow(1, $current_row + 2, @$vuz['STAT2_DEGREE']);
             $sheet->mergeCellsByColumnAndRow(9, $current_row + 1, 13, $current_row + 1);
             $sheet->setCellValueByColumnAndRow(9, $current_row + 1, @$vuz['STAT2_FIO']);
             $current_row += 4;
         }
     }
     if (!$indpapers) {
         $this->showTotal($sheet, $current_row, @$groupEduDirections, false, $vuzezInGroupCount > 1, count($vuzes) > 1);
         //-------------- ИТОГ по всем ВУЗам --------------------------------------------
         if (count($vuzes) > 1 && count($allEduDirections) > 0) {
             $this->showTotal($sheet, $current_row, $allEduDirections, true);
         }
         //------------------------------------------------------------------------------
     }
     $sheet->removeRow($current_row, 1);
     // Удаляем лишнюю строку, которая была в шаблоне
     return $workbook;
 }