/** * 获取数据 * @param int $page 第几页数据 */ public function getData($page) { $this->_getReadFileObj(); $phpExcel = $this->_instance->load($this->_filePath); $currentSheet = $phpExcel->getSheet($page); return $currentSheet->toArray(); }
/** * 读取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; }
/** * @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'); }
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); } }
/** * 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; }
/** * * @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; }
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)); } } }
/** * @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); }
/** * 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); }
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导入成功'); }
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]); }
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; }
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); }
/** * 读取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; }
/** * 生成订单附件 * @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";
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; }
<?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;
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
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();
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; } }
/** * @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); } } }
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'); }
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; }