function index()
{
$this->load->database();
$this->db->select('user_info.user_id,user_info.user_name,user_info.user_sex,hr_info.hr_center,hr_info.hr_department,user_info.user_college,user_info.user_major,user_info.user_phone,user_info.user_qq,user_info.user_remarks');
$this->db->from('hr_user');
$this->db->join('hr_info', 'hr_info.hr_id = hr_user.hr_id');
$this->db->join('user_info', 'user_info.user_id = hr_user.user_id');
$query = $this->db->get();
//$query = mb_convert_encoding("gb2312", "UTF-8", $query);
if (!$query) {
return false;
}
// Starting the PHPExcel library
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('报名表单');
$objPHPExcel->getActiveSheet()->setCellValue('A1', '报名编号');
$objPHPExcel->getActiveSheet()->setCellValue('B1', '姓名');
//$objPHPExcel->getActiveSheet()->setCellValue('A1', 'String');
$objPHPExcel->getActiveSheet()->setCellValue('C1', '性别');
$objPHPExcel->getActiveSheet()->setCellValue('D1', '中心');
$objPHPExcel->getActiveSheet()->setCellValue('E1', '部门');
$objPHPExcel->getActiveSheet()->setCellValue('F1', '学院');
$objPHPExcel->getActiveSheet()->setCellValue('G1', '专业');
$objPHPExcel->getActiveSheet()->setCellValue('H1', '电话');
$objPHPExcel->getActiveSheet()->setCellValue('I1', 'QQ');
$objPHPExcel->getActiveSheet()->setCellValue('J1', '自我介绍');
// Field names in the first row
$fields = $query->list_fields();
$col = 0;
foreach ($fields as $field) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 2, $field);
$col++;
}
// Fetching the table data
$row = 2;
foreach ($query->result() as $data) {
$col = 0;
foreach ($fields as $field) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->{$field});
$col++;
}
$row++;
}
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
//发送标题强制用户下载文件
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Products_' . date('dMy') . '.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
}
public function upload_member_list()
{
$data['groupId'] = $this->input->post('groupId');
// 把群組id放進去
$config["encrypt_name"] = TRUE;
$config['upload_path'] = './uploads/';
$config['allowed_types'] = 'xls|xlsx|csv';
//上傳文件格式
$this->load->library('upload', $config);
//讀取上傳的Lib
if ($this->upload->do_upload()) {
$arr = $this->upload->data();
} else {
$this->load->view('failure', array('message' => '新增成員失敗Q_Q..檔案副黨名必須為' . $config['allowed_types'], 'asideQuery' => $this->asideQuery, 'headerQuery' => $this->headerQuery));
return false;
}
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
$reader = IOFactory::load("./uploads/{$arr['file_name']}");
$sheet = $reader->getActiveSheet();
$query = $this->member_model->select_all_account();
// 先把所有帳號撈出來
for ($i = 1; $i <= $sheet->getHighestRow(); $i++) {
// 判斷所有帳號有沒有衝突到
foreach ($query as $key => $value) {
if ($value->account == $sheet->getCell('A' . $i)->getValue()) {
@unlink("./uploads/{$arr['file_name']}");
// 衝突到先把上傳的excel刪除
$this->load->view('failure', array('message' => '帳號' . $value->account . '已經存在，請確保excel內的帳號沒有衝突', 'asideQuery' => $this->asideQuery, 'headerQuery' => $this->headerQuery));
return false;
}
}
}
date_default_timezone_set("Asia/Taipei");
//設定時區
for ($i = 1; $i <= $sheet->getHighestRow(); $i++) {
// 把每一筆excel的資料新增到SQL
$data['account'] = $sheet->getCell('A' . $i)->getValue();
$data['password'] = $sheet->getCell('B' . $i)->getValue();
$data['name'] = $sheet->getCell('C' . $i)->getValue();
$data['email'] = $sheet->getCell('D' . $i)->getValue();
$data['createTime'] = date("Y-m-d H:i:s", time());
//把時間放進欄位
$this->member_model->insert_data($data);
}
@unlink("./uploads/{$arr['file_name']}");
$this->load->view('success', array('message' => '新增成員成功^_^', 'redirectUrl' => 'member', 'asideQuery' => $this->asideQuery, 'headerQuery' => $this->headerQuery));
return true;
// $this->load->view('')
}
function index()
{
$objPHPExcel = new PHPExcel();
$inputFileType = 'HTML';
$inputFileName = 'assets/file/sample4.html';
$outputFileType = 'Excel2007';
$outputFileName = 'assets/file/sample4.xls';
$filename = "DownloadReport.xls";
$objPHPExcelReader = IOFactory::createReader($inputFileType);
$objPHPExcel = $objPHPExcelReader->load($inputFileName);
$objget = $objPHPExcel->getActiveSheet();
$objget->getColumnDimension('A')->setWidth(5);
$objget->getColumnDimension('B')->setWidth(5);
$objget->getColumnDimension('C')->setWidth(5);
$objget->getColumnDimension('D')->setWidth(20);
$objget->getColumnDimension('E')->setWidth(50);
$objget->getColumnDimension('F')->setWidth(50);
$objget->getColumnDimension('G')->setWidth(20);
ini_set('zlib.output_compression', 'Off');
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
//the folowing two lines make sure it is saved as a xls file
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename=' . $filename);
//simpan dalam file sample.xls
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
/**
* Set search locations
*
* @static
* @access public
* @param array $value
* @throws PHPExcel_Reader_Exception
*/
public static function setSearchLocations($value)
{
if (is_array($value)) {
self::$searchLocations = $value;
} else {
throw new PHPExcel_Reader_Exception('Invalid parameter passed.');
}
}
public function getContent($file)
{
$writer = new HTML(@IOFactory::load($file));
$html = $writer->getContent();
// Remove <style> manually
$html = preg_replace('/<style>.*<\\/style>/s', '', $html);
// Remove HTML tags
return strip_tags($html);
}
public function Insert()
{
if ($this->input->post('Import')) {
set_time_limit(3600);
//FOR UPLOAD
$fileName = $_FILES['fileimport']['name'];
//FOR UPLOAD
$config['upload_path'] = BASEPATH . '../includes/assets/';
$config['file_name'] = $fileName;
$config['allowed_types'] = 'csv';
$config['max_size'] = 1000000;
$this->load->library('upload');
$this->upload->initialize($config);
if (!$this->upload->do_upload('fileimport')) {
$error = $this->upload->display_errors();
echo $error;
exit;
}
//FOR READ
$inputFileName = BASEPATH . '../includes/assets/' . $fileName;
//READ your excel workbook
try {
$inputFileType = IOFactory::identify($inputFileName);
$objReader = IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
} catch (Exception $e) {
die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
}
//Get Worksheet dimendions
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
//Loop through each row of thw Worksheet in turn
for ($row = 1; $row <= $highestRow; $row++) {
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
if (strlen($rowData[0][0]) == 1) {
$new_rowData = '000' . $rowData[0][0];
} else {
if (strlen($rowData[0][0]) == 2) {
$new_rowData = '00' . $rowData[0][0];
} else {
$new_rowData = $rowData[0][0];
}
}
//iNSERT ROW DATA ARRAY INTO YOUR DATABASE OF CHOISE HERE
$data_upload = array('id_kekuatan' => $new_rowData, 'kekuatan' => $rowData[0][1]);
$this->db->insert(TABLE, $data_upload);
}
echo 'Compile for insert to table ' . TABLE . ' success';
} else {
$view_data['controller'] = 'kekuatan';
$view_data['table'] = TABLE;
$view_data['body'] = 'compiler/import';
$this->load->view('compiler/wrapper', $view_data);
}
}
public function insertXls()
{
$inputFileType = 'Excel5';
$inputFileName = 'nameoffile_2.xls';
/** Create a new Reader of the type defined in $inputFileType **/
$objReader = IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
/** Load $inputFileName to a PHPExcel Object **/
$objPHPExcel = $objReader->load($inputFileName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
echo json_encode($sheetData);
}
public function generateDoc()
{
$phpWord = new PhpWord();
$section = $phpWord->createSection();
$section->addText('Hello World!');
$file = 'HelloWorld.docx';
header("Content-Description: File Transfer");
header('Content-Disposition: attachment; filename="' . $file . '"');
header('Content-Type: application/vnd.openxmlformats-officedocument.wordprocessingml.document');
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Expires: 0');
$xmlWriter = IOFactory::createWriter($phpWord, 'Word2007');
$xmlWriter->save("prueba.docx");
}
function index()
{
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("SMA Insan Cendekia Alkautsar")->setTitle("Jadwal pelajaran");
//file title
$objset = $objPHPExcel->setActiveSheetIndex(0);
//inisiasi set object
$objget = $objPHPExcel->getActiveSheet();
//inisiasi get object
$objget->setTitle('Sample Sheet');
//sheet title
$objset->setCellValue('A1', "This is Sample Excel File");
//insert cell value
$objget->getStyle('A1')->getFont()->setBold(true)->setSize(15);
//set font size
//query
$query = $this->db->query("select get_npwpd(c.id, true) as npwpd,c.nama as customernm,c.alamat, c.telphone,c.kodepos,\r\n cd.reg_date as createdatecd, cd.notes as ket, kec.kode as kecamatankd,kec.nama as kecamatannm,\r\n kel.kode as kelurahankd, kel.nama as kelurahannm, cd.customer_status_id, u.nama as usahanm,\r\n cd.usaha_id, cd.konterid, cd.notes, kel2.kode as kel2kd, kel2.nama as kel2nm,\r\n kec2.kode as kec2kd, kec2.nama as kec2nm,\r\n c.kukuhtgl, c.kukuhno, cd.reg_date as reg_datecd\r\n ,(select count(*) FROM pad_customer_usaha cd2\r\n INNER JOIN pad_kelurahan k2 ON cd2.kelurahan_id=k2.id\r\n INNER JOIN pad_kecamatan k3 ON k2.kecamatan_id=k3.id\r\n WHERE cd2.customer_id=c.id and usaha_id=cd.usaha_id and k3.id=kec2.id) as jml\r\n from pad_customer c\r\n inner join pad_customer_usaha cd on c.id=cd.customer_id\r\n inner join pad_kelurahan kel on c.kelurahan_id=kel.id\r\n inner join pad_kecamatan kec on kel.kecamatan_id=kec.id\r\n inner join pad_usaha u on cd.usaha_id=u.id\r\n inner join pad_kelurahan kel2 on cd.kelurahan_id=kel2.id\r\n inner join pad_kecamatan kec2 on kel2.kecamatan_id=kec2.id\r\n order by c.npwpd");
//table header
$cols = array("A", "B", "C", "D", "E", "F");
$val = array("NPWPD", "NAMA", "ALAMAT", "KETERANGAN", "KECAMATAN", "KELURAHAN");
for ($a = 0; $a < 6; $a++) {
$objset->setCellValue($cols[$a] . '3', $val[$a]);
//set borders
$objget->getStyle($cols[$a] . '3')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objget->getStyle($cols[$a] . '3')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objget->getStyle($cols[$a] . '3')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objget->getStyle($cols[$a] . '3')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//set alignment
$objget->getStyle($cols[$a] . '3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//set font weight
$objget->getStyle($cols[$a] . '3')->getFont()->setBold(true);
}
//taruh baris data disini
$colsb = array("A", "B", "C", "D", "E", "F");
$i = 4;
// baris ke
foreach ($query->result() as $row) {
$valb = array($row->npwpd, $row->customernm, $row->alamat, $row->ket, $row->kecamatannm, $row->kelurahannm);
for ($b = 0; $b < 6; $b++) {
$objset->setCellValue($colsb[$b] . $i, $valb[$b]);
}
$i = $i + 1;
}
//simpan dalam file sample.xls
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('assets/file/sample2.xls');
}
public function Insert()
{
if ($this->input->post('Import')) {
//FOR UPLOAD
$fileName = $_FILES['fileimport']['name'];
//FOR UPLOAD
$config['upload_path'] = BASEPATH . '../includes/assets/';
$config['file_name'] = $fileName;
$config['allowed_types'] = 'csv';
$config['max_size'] = 1000000;
$this->load->library('upload');
$this->upload->initialize($config);
if (!$this->upload->do_upload('fileimport')) {
$error = $this->upload->display_errors();
echo $error;
exit;
}
//FOR READ
$inputFileName = BASEPATH . '../includes/assets/' . $fileName;
//READ your excel workbook
try {
$inputFileType = IOFactory::identify($inputFileName);
$objReader = IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
} catch (Exception $e) {
die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
}
//Get Worksheet dimendions
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
//Loop through each row of thw Worksheet in turn
for ($row = 2; $row <= $highestRow; $row++) {
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
//iNSERT ROW DATA ARRAY INTO YOUR DATABASE OF CHOISE HERE
$i = 0;
$data_upload = array('id_kelas_terapi' => $rowData[0][0], 'id_sub_kelas_terapi1' => $rowData[0][1], 'id_sub_kelas_terapi2' => $rowData[0][2], 'id_sub_kelas_terapi3' => $rowData[0][3], 'nama_obat' => $rowData[0][4], 'id_atc_obat' => $rowData[0][5], 'id sediaan' => $rowData[0][6], 'id_kekuatan' => $rowData[0][7], 'id_satuan' => $rowData[0][8], 'subkutan' => $rowData[0][9], 'intrakutan' => $rowData[0][10], 'intramuscular' => $rowData[0][11], 'intravena' => $rowData[0][12], 'intravena_bolus' => $rowData[0][13], 'intra_arteri' => $rowData[0][14], 'intralumbal' => $rowData[0][15], 'intraperitoneal' => $rowData[0][16], 'intrapleural' => $rowData[0][17], 'intracardial' => $rowData[0][18], 'anti_artikuler' => $rowData[0][19], 'implantasi_subkutan' => $rowData[0][20], 'rektal' => $rowData[0][21], 'intranasal' => $rowData[0][22], 'intra_okuler' => $rowData[0][23], 'intra_aurikuler' => $rowData[0][24], 'intrapulmonal' => $rowData[0][25], 'intravaginal' => $rowData[0][26], 'infus_drip' => $rowData[0][27], 'injeksi_infiltr' => $rowData[0][28], 'pv' => $rowData[0][29], 'Tk1' => $rowData[0][30], 'Tk2' => $rowData[0][31], 'Tk3' => $rowData[0][32], 'PRB' => $rowData[0][33], 'PP' => $rowData[0][34], 'restriksi_kelas_terapi' => $rowData[0][35], 'restriksi_sub_kelas_terapi1' => $rowData[0][36], 'restriksi_sub_kelas_terapi2' => $rowData[0][37], 'restriksi_sub_kelas_terapi3' => $rowData[0][38], 'restriksi_obat' => $rowData[0][39], 'tambahan_restriksi_obat' => $rowData[0][40], 'tambahan_restriksi_obat2' => $rowData[0][41], 'restriksi_sediaan' => $rowData[0][42], 'restriksi1' => $rowData[0][43], 'restriksi2' => $rowData[0][44], 'restriksi3' => $rowData[0][45], 'restriksi4' => $rowData[0][46], 'restriksi5' => $rowData[0][47]);
$i++;
$this->db->insert(TABLE, $data_upload);
}
echo 'Compile for insert to table ' . TABLE . ' success';
} else {
$view_data['controller'] = 'fornas';
$view_data['table'] = TABLE;
$view_data['body'] = 'compiler/import';
$this->load->view('compiler/wrapper', $view_data);
}
}
/**
* [index 导出excel方法，用来将query中的内容导出成excel并提供用户下载]
* @return [type] [description]
*/
function index()
{
$query = $this->db->query("select b.firm as '厂商',b.tname as '类型',a.xhgg as '型号规格', \n\t\t\ta.sn as 'S/N',a.zcbh as '资产编号',a.zt as '状态',a.je as '金额',a.dhsj as '到货时间',\n\t\t\tc.pname as '地点',a.azsj as '安装时间',a.fph as '发票号',a.hth as '合同号',a.bz as '备注'\n\t\t\tfrom `t_main` a, `t_type` b,`t_place` c \n\t\t\twhere a.tid = b.tid and a.pid = c.pid\n\t\t\torder by a.pid,a.tid");
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
$objPHPExcel = new PHPExcel();
//创建PHPExcel对象
$objPHPExcel->getProperties()->setTitle("设备总表")->setDescription("none");
//设置excel标题
$objPHPExcel->setActiveSheetIndex(0);
// 设置活动表单
$fields = $query->list_fields();
// 获得数据库查询表头
$col = 0;
foreach ($fields as $field) {
$objPHPExcel->getActiveSheet()->getStyle('A1:O1')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'D1EEEE'))));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
//输出表头内容
$col++;
}
// Fetching the table data
$row = 2;
foreach ($query->result() as $data) {
$col = 0;
foreach ($fields as $field) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->{$field});
$col++;
}
$row++;
}
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
$excelname = 'main' . date('YmdHis', time());
//用时间定义生成的excel文档
$objWriter->save('xls\\' . $excelname . '.xls');
header("Content-Type: application/force-download");
//跳出下载框强制用户下载
header("Content-Disposition: attachment; filename=" . $excelname . '.xls');
//设置下载框文件名
if (readfile('xls\\' . $excelname . '.xls')) {
$data['main'] = $this->main->select_main();
$data['type'] = $this->main->select_distinct_type();
// print_r($data);die;
$this->load->view('index/main.html', $data);
}
}
public function get($id = null)
{
header('Content-Type: application/json');
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
$objPHPExcel = new PHPExcel();
$query = $this->product->get_products($id);
if (!$query) {
echo json_encode(array('status' => false, 'error' => 'Data not found'));
return false;
}
$fields = $query->list_fields();
$result = $query->result();
if (empty($result)) {
echo json_encode(array('status' => true, 'result' => 'Products not found'));
return false;
}
$col = 0;
foreach ($fields as $field) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
$col++;
}
$row = 3;
foreach ($result as &$data) {
$col = 0;
foreach ($fields as $field) {
if ($field == 'created_date') {
$data->{$field} = date('d/m/Y H:i', $data->{$field});
}
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->{$field});
$col++;
}
$row++;
}
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
$filepath = 'products/Products_' . date('dmyHis') . '.xls';
$objWriter->save($filepath);
echo json_encode(array('status' => true, 'result' => $result, 'download_link' => base_url() . $filepath));
}
function index()
{
$query = $this->Excel_model->get();
if (!$query) {
return false;
}
// StartingthePHPExcellibrary
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setTitle("export")->setDescription("none");
$objPHPExcel->setActiveSheetIndex(0);
// Fieldnamesinthefirstrow
$fields = $query->list_fields();
$col = 0;
foreach ($fields as $field) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
$col++;
}
// Fetchingthetabledata
$row = 2;
foreach ($query->result() as $data) {
$col = 0;
foreach ($fields as $field) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->{$field});
$col++;
}
$row++;
}
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
// Sendingheaderstoforcetheusertodownloadthefile
header('Content-Type:application/vnd.ms-excel');
header('Content-Disposition:attachment;filename="Products_' . date('dMy') . '.xls"');
header('Cache-Control:max-age=0');
$objWriter->save('php://output');
}
function index()
{
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("SMA Insan Cendekia Alkautsar")->setTitle("Jadwal pelajaran");
//file title
$objset = $objPHPExcel->setActiveSheetIndex(0);
//inisiasi set object
$objget = $objPHPExcel->getActiveSheet();
//inisiasi get object
$objget->setTitle('Sample Sheet');
//sheet title
$objset->setCellValue('A1', "This is Sample Excel File");
//insert cell value
$objget->getStyle('A1')->getFont()->setBold(true)->setSize(15);
//set font size
//table header
$cols = array("A", "B", "C", "D", "E", "F");
$val = array("No", "Member ID", "Member Username", "Member Address", "Member Phone", "Member Status");
for ($a = 0; $a < 6; $a++) {
$objset->setCellValue($cols[$a] . '3', $val[$a]);
//set borders
$objget->getStyle($cols[$a] . '3')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objget->getStyle($cols[$a] . '3')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objget->getStyle($cols[$a] . '3')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objget->getStyle($cols[$a] . '3')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//set alignment
$objget->getStyle($cols[$a] . '3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//set font weight
$objget->getStyle($cols[$a] . '3')->getFont()->setBold(true);
}
//taruh baris data disini
//simpan dalam file sample.xls
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('assets/file/sample.xls');
}
/**
* 区域用户导出
*/
public function area_import()
{
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
//要导出的数据
$aim = $this->session->area;
//导出后第一行
$row = array('用户id', '姓名', '年龄', '身份证', '手机', '地址', '注册时间');
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getProperties()->setTitle("export")->setDescription("none");
foreach ($row as $key => $value) {
$cwr = chr(65 + $key) . '1';
$objPHPExcel->getActiveSheet()->SetCellValue($cwr, $value);
}
/*"uid"]=> string(6) "123762" ["cell_phone"]=> string(11) "13822220003" ["idcard"]=> string(18) "330823191255550003" ["real_name"]=> string(9) "两句话" ["age"]=> string(1) "0" ["address"]=> string(0) "" ["reg_time"]=> string(10) "1441010603" }*/
foreach ($aim as $key => $value) {
$arr[$key][] = $value['uid'];
$arr[$key][] = $value['cell_phone'];
$arr[$key][] = $value['idcard'];
$arr[$key][] = $value['real_name'];
$arr[$key][] = $value['age'];
$arr[$key][] = $value['address'];
$arr[$key][] = date('Y-m-d', $value['reg_time']);
}
$total = count($arr);
//34
$cr = count($arr['0']);
//获取每个数组有几个元素用来控制列 9
for ($i = 0; $i < $cr; $i++) {
$cwr2 = chr(64 + $i + 1);
for ($j = 2; $j < $total + 2; $j++) {
$cwr = $cwr2 . $j;
//abcdefg
$objPHPExcel->getActiveSheet()->SetCellValue($cwr, ' ' . $arr[$j - 2][$i]);
}
}
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
//发送标题强制用户下载文件
$filename = '同一地区用户.xls';
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=" . $filename);
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');
}
public function inportExcel()
{
//上传对应的Excel文件
//文件上传
//基本配置
$config['upload_path'] = './uploads/';
$config['allowed_types'] = 'xls|xlsx|xl';
$config['max_size'] = '20000';
$config['file_name'] = time() . mt_rand(1000, 9999);
//载入上传类
$this->load->library('upload', $config);
//执行上传
$status = $this->upload->do_upload('Excel');
$wrong = $this->upload->display_errors();
if ($wrong) {
error($wrong);
}
//返回信息
$info = $this->upload->data();
// p($info);
//再进行解析对应的Excel文件
//找到对应的文件
$targetfile = './uploads/' . $info['file_name'];
//生成对应的格式
//需要注意的是,这里的excel文件要对应好不同的格式
$objReader = IOFactory::createReader('Excel2007');
//读取整个execl文件生成excel的object
$objPHPExcel = $objReader->load($targetfile);
//获取总的sheet数
$size = $objPHPExcel->getSheetCount();
//全部数据保存在的数组
$alldata = array();
for ($i = 0; $i < $size; $i++) {
//读取第一行sheet
$sheet = $objPHPExcel->getSheet($i);
//获取总行数
$hightRow = $sheet->getHighestRow();
// 取得总列数
$hightColum = $sheet->getHighestColumn();
//全部的excel对象数组
// p($objPHPExcel->getActiveSheet()->toArray());
// p($hightColum);
// p($hightRow);
// echo "<hr>";
// 获取excel对象的全部数据
$importdata = $objPHPExcel->getActiveSheet()->toArray();
$z = 0;
// 存放字段值数组
$firstname = array();
foreach ($importdata as $key => $value) {
if ($z == 0) {
$firstname = $this->toNameforArray($value);
} else {
$newinfoarr = $this->newarraydata($firstname, $value);
//生成新的数组
array_push($alldata, $newinfoarr);
}
$z++;
}
}
// p($alldata);
//批量导入问题
$this->register->insertAllbatch($alldata);
success('demo/login/getDataIndex', '批量导入' . count($alldata) . '条数据成功....');
}
public function userExport()
{
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
$this->load->model('user_model');
$export_fields = $this->user_model->getFields();
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $export_fields['id'])->setCellValue('B1', $export_fields['name'])->setCellValue('C1', $export_fields['tel'])->setCellValue('D1', $export_fields['address'])->setCellValue('E1', $export_fields['type'])->setCellValue('F1', $export_fields['create_time'])->setCellValue('G1', $export_fields['create_ip']);
$babies = $this->user_model->getAll(1000000000, 0);
foreach ($babies as $key => $user) {
$col = 0;
foreach ($export_fields as $k => $v) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $key + 2, $user[$k]);
$col++;
}
}
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="users_' . date('ymd') . '.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
}
$objPHPExcel->getActiveSheet()->getStyle($active3)->applyFromArray($styleAlignHorizontalCenter);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $i, $data['id_obat']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $i, $data['nbk_obat']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $i, $data['satuan_obat']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $i, $data['stok_awal']);
if(isset($data['tambah'])){
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $i, $data['tambah']);}
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(37, $i, $formula1);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(39, $i, $formula1);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $i, $formula2);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(40, $i, $formula3);
$x=5;
$str=1;
for($z=1;$z<=31;$z++){
$t=$x+$z;
$obatn='obat'.$z;
if(isset($data[$obatn])){
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($t, $i, $data[$obatn]);}
$str++;
}
$i++;}
*/
// $objPHPExcel->getActiveSheet()->getStyle('A6:F' . ($i-1))->applyFromArray($styleThinBlackBorderOutline);
$objPHPExcel->getActiveSheet()->setTitle('Harian');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
//header('Content-Disposition: attachment;filename="rekap_resep_bulanan_' . $namanya . '-' . $tahun . '.xls"');
$objWriter = IOFactory::createWriter($objPHPExcel, "Excel5");
$objWriter->save("php://output");
public function getExcelObject($targetfile, $dsize)
{
// $objReader=IOFactory::createReader('Excel2007');
$objReader = IOFactory::createReaderForFile($targetfile);
//读取整个execl文件生成excel的object
$objPHPExcel = $objReader->load($targetfile);
//获取总的sheet数
$size = $objPHPExcel->getSheetCount();
// 获取第一行的数据
if ($dsize == 1) {
$sheet = $objPHPExcel->getSheet(0);
// // 获取excel对象的全部数据
$importdata = $objPHPExcel->getActiveSheet()->toArray();
return $importdata;
}
}
/**
* 我的交易记录
*/
public function deal_record()
{
//导入类库
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
//导出的是投标列表
$list = $this->session->userdata('deallist');
//设置excel表格标题行
$row = array();
$row[0] = array('交易时间', '交易类型', '影响金额', '代收金额', '说明', '可用余额');
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getProperties()->setTitle("export")->setDescription("none");
foreach ($row[0] as $key => $value) {
$cwr = chr(65 + $key) . '1';
$objPHPExcel->getActiveSheet()->SetCellValue($cwr, $value);
}
foreach ($list as $key => $value) {
$arr[$key][] = $value['date'];
$arr[$key][] = $value['type'];
$arr[$key][] = $value['affect_money'];
$arr[$key][] = $value['collect_money'];
$arr[$key][] = $value['desc'];
$arr[$key][] = $value['account_money'];
}
$total = count($arr);
//34
$cr = count($arr['0']);
//获取每个数组有几个元素用来控制列 9
for ($i = 0; $i < $cr; $i++) {
$cwr2 = chr(64 + $i + 1);
for ($j = 2; $j < $total + 2; $j++) {
$cwr = $cwr2 . $j;
//abcdefg
$objPHPExcel->getActiveSheet()->SetCellValue($cwr, $arr[$j - 2][$i]);
}
}
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
//发送标题强制用户下载文件
$filename = '交易记录.xls';
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=" . $filename);
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');
}
function upload_history_post()
{
$this->db->trans_begin();
$UploadDirectory = 'assets/excel/';
//Upload Directory, ends with slash & make sure folder exist
$NewFileName = "";
//die($UploadDirectory);
// replace with your mysql database details
if (!@file_exists($UploadDirectory)) {
//destination folder does not exist
die('No upload directory');
}
if (isset($_FILES['import']['name'])) {
// $foto = post_safe('gambar');
$FileName = strtolower($_FILES['import']['name']);
//uploaded file name
$FileTitle = $FileName;
$ImageExt = substr($FileName, strrpos($FileName, '.'));
//file extension
$FileType = $_FILES['import']['type'];
//file type
//$FileSize = $_FILES['import']["size"]; //file size
$RandNumber = '';
//rand(0, 999); //Random number to make each filename unique.
//echo $FileType; die;
//$uploaded_date = date("Y-m-d H:i:s");
// if ($foto !== '') {
// @unlink('assets/images/projects/'.$foto);
// }
switch (strtolower($FileType)) {
//allowed file types
// case 'image/png': //png file
// case 'image/gif': //gif file
// case 'image/jpeg': //jpeg file
// case 'application/pdf': //PDF file
// case 'application/msword': //ms word file
case 'application/vnd.ms-excel':
//ms excel file
//ms excel file
case 'application/octet-stream':
//ms excel file
//ms excel file
case 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet':
//xlsx file
// case 'text/plain': //text file
// case 'text/html': //html file
break;
default:
die('Unsupported File!');
//output error
}
//File Title will be used as new File name
$NewFileName = preg_replace(array('/\\s/', '/\\.[\\.]+/', '/[^\\w_\\.\\-]/'), array('_', '.', ''), strtolower($FileTitle));
$NewFileName = $NewFileName . '_' . $RandNumber . $ImageExt;
//Rename and save uploded file to destination folder.
if (move_uploaded_file($_FILES['import']["tmp_name"], $UploadDirectory . $NewFileName)) {
} else {
die('error uploading File!');
}
}
//$media = $this->upload->data('import');
$inputFileName = './assets/excel/' . $NewFileName;
// Read your Excel workbook
$this->load->library(array('PHPExcel', 'PHPExcel/IOFactory'));
try {
$inputFileType = IOFactory::identify($inputFileName);
$objReader = IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
} catch (Exception $e) {
die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
}
// Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
// Loop through each row of the worksheet in turn
for ($row = 2; $row <= $highestRow; $row++) {
// Read a row of data into an array
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
// Insert row data array into your database of choice here
if ($rowData[0][1] !== NULL) {
$get_data = $this->db->get_where('tb_kategori_cuti', array('id' => post_safe('jenis_pengajuan_cuti')))->row();
$data = array('id_kategori_cuti' => post_safe('jenis_pengajuan_cuti'), 'tanggal' => date2mysql_export($rowData[0][7]), 'nip' => str_replace(' ', '', $rowData[0][2]), 'nama' => $rowData[0][1], 'pangkat' => $rowData[0][3] !== NULL ? $rowData[0][3] : '', 'jabatan' => $rowData[0][4] !== NULL ? $rowData[0][4] : '', 'unit_satuan' => $rowData[0][5] !== NULL ? $rowData[0][5] : '', 'status_pengajuan' => 'Selesai', 'no_ijin' => '', 'jumlah' => $rowData[0][6], 'satuan' => 'Hari', 'dari' => date2mysql_export($rowData[0][7]), 'sampai' => $rowData[0][8] !== NULL ? date2mysql_export($rowData[0][8]) : NULL, 'tanggal_cetak' => date2mysql_export($rowData[0][7]), 'keterangan_disetujui' => $get_data->keterangan_pokok_surat, 'tembusan' => $rowData[0][10] !== NULL ? $rowData[0][10] : '', 'import_excel' => 'Ya');
$this->db->insert('tb_pengajuan_cuti', $data);
if ($this->db->trans_status() === FALSE) {
$this->db->trans_rollback();
$result['status'] = FALSE;
}
if ($this->db->trans_status() === FALSE) {
$this->db->trans_rollback();
$result['status'] = FALSE;
}
}
}
if ($this->db->trans_status() === FALSE) {
$this->db->trans_rollback();
$result['status'] = FALSE;
} else {
$this->db->trans_commit();
$result['status'] = TRUE;
}
$this->response($result, 200);
}
function download($spt_id)
{
$wp_id = $this->session->userdata('wp_id');
$customer_id = 0;
//init
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("E-SPTPD KOTA TANGERANG")->setTitle("LAPORAN OMSET");
//file title
$objset = $objPHPExcel->setActiveSheetIndex(0);
//inisiasi set object
$objget = $objPHPExcel->getActiveSheet();
//inisiasi get object
$objget->setTitle('Sample Sheet');
//sheet title
$objset->setCellValue('A1', "LAPORAN OMSET BULANAN");
//insert cell value
$objget->getStyle('A1')->getFont()->setBold(true)->setSize(15);
//set font size
//query
$query = $this->db->query("select c.id as customer_id, c.wpnama as pemiliknm, c.nama as customernm, c.alamat as customeralamat, get_sptpdno(s.id), get_nopd(cu.id,true) nopd, cu.opnm,\r\n get_npwpd(c.id,true) npwpd, u.nama as usahanm, p.nama as pajaknm, get_rekening(r.kode) as rekeningkd, r.nama as rekeningnm,\r\n get_bayarno(s.id,'pad_spt') as bayarno, s.tahun, s.id, s.customer_id, s.customer_usaha_id, s.pajak_id, ROW_NUMBER() OVER (ORDER BY urut) as nomor, urut, omset, keterangan,\r\n case\r\n when urut = 32 then 'Lainnya'\r\n when urut <> 32 then urut::text end no_urut,\r\n\r\n case\r\n when extract(month from s.masadari)=1 then 'JANUARI'\r\n when extract(month from s.masadari)=2 then 'PEBRUARI'\r\n when extract(month from s.masadari)=3 then 'MARET'\r\n when extract(month from s.masadari)=4 then 'APRIL'\r\n when extract(month from s.masadari)=5 then 'MEI'\r\n when extract(month from s.masadari)=6 then 'JUNI'\r\n when extract(month from s.masadari)=7 then 'JULI'\r\n when extract(month from s.masadari)=8 then 'AGUSTUS'\r\n when extract(month from s.masadari)=9 then 'SEPTEMBER'\r\n when extract(month from s.masadari)=10 then 'OKTOBER'\r\n when extract(month from s.masadari)=11 then 'NOPEMBER'\r\n when extract(month from s.masadari)=12 then 'DESEMBER'\r\n end masabulan,\r\n\r\n extract(year from s.masadari) masatahun,\r\n\r\n extract(day from current_timestamp) terimatgl,\r\n case\r\n when extract(dow from current_timestamp)=0 then 'Minggu'\r\n when extract(dow from current_timestamp)=1 then 'Senin'\r\n when extract(dow from current_timestamp)=2 then 'Selasa'\r\n when extract(dow from current_timestamp)=3 then 'Rabu'\r\n when extract(dow from current_timestamp)=4 then 'Kamis'\r\n when extract(dow from current_timestamp)=5 then 'Jumat'\r\n when extract(dow from current_timestamp)=6 then 'Sabtu'\r\n else 'Hari ###'\r\n end terimahari,\r\n\r\n case\r\n when extract(month from current_timestamp)=1 then 'Januari'\r\n when extract(month from current_timestamp)=2 then 'Pebruari'\r\n when extract(month from current_timestamp)=3 then 'Maret'\r\n when extract(month from current_timestamp)=4 then 'April'\r\n when extract(month from current_timestamp)=5 then 'Mei'\r\n when extract(month from current_timestamp)=6 then 'Juni'\r\n when extract(month from current_timestamp)=7 then 'Juli'\r\n when extract(month from current_timestamp)=8 then 'Agustus'\r\n when extract(month from current_timestamp)=9 then 'September'\r\n when extract(month from current_timestamp)=10 then 'Oktober'\r\n when extract(month from current_timestamp)=11 then 'Nopember'\r\n when extract(month from current_timestamp)=12 then 'Desember'\r\n end terimabulan,\r\n extract(year from current_timestamp) terimatahun\r\n\r\n\r\n from\r\n (\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 1 as urut, omset1 as omset, keterangan1 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 2 as urut, omset2 as omset, keterangan2 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 3 as urut, omset3 as omset, keterangan3 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 4 as urut, omset4 as omset, keterangan4 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 5 as urut, omset5 as omset, keterangan5 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 6 as urut, omset6 as omset, keterangan6 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 7 as urut, omset7 as omset, keterangan7 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 8 as urut, omset8 as omset, keterangan8 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 9 as urut, omset9 as omset, keterangan9 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 10 as urut, omset10 as omset, keterangan10 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 11 as urut, omset11 as omset, keterangan11 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 12 as urut, omset12 as omset, keterangan12 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 13 as urut, omset13 as omset, keterangan13 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 14 as urut, omset14 as omset, keterangan14 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 15 as urut, omset15 as omset, keterangan15 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 16 as urut, omset16 as omset, keterangan16 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 17 as urut, omset17 as omset, keterangan17 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 18 as urut, omset18 as omset, keterangan18 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 19 as urut, omset19 as omset, keterangan19 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 20 as urut, omset20 as omset, keterangan20 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 21 as urut, omset21 as omset, keterangan21 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 22 as urut, omset22 as omset, keterangan22 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 23 as urut, omset23 as omset, keterangan23 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 24 as urut, omset24 as omset, keterangan24 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 25 as urut, omset25 as omset, keterangan25 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 26 as urut, omset26 as omset, keterangan26 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 27 as urut, omset27 as omset, keterangan27 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 28 as urut, omset28 as omset, keterangan28 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 29 as urut, omset29 as omset, keterangan29 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 30 as urut, omset30 as omset, keterangan30 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 31 as urut, omset31 as omset, keterangan31 as keterangan from pad_spt\r\n union\r\n select id, customer_id, customer_usaha_id, pajak_id, masadari, tahun, 32 as urut, omset_lain as omset, keterangan_lain as keterangan from pad_spt\r\n ) s\r\n inner join pad_customer_usaha cu on cu.id=s.customer_usaha_id\r\n inner join pad_customer c on c.id=cu.customer_id\r\n inner join pad_kecamatan kec on kec.id=c.kecamatan_id\r\n inner join pad_kelurahan kel on kel.id=c.kelurahan_id and kel.kecamatan_id=c.kecamatan_id\r\n inner join pad_usaha u on u.id=cu.usaha_id\r\n inner join pad_jenis_pajak p on p.id=s.pajak_id\r\n inner join pad_rekening r on p.rekening_id=r.id\r\n where s.id={$spt_id} \r\n order by s.id, s.urut");
$objset->setCellValue('B3', 'Bulan');
$objset->setCellValue('B4', 'NOPD');
$objset->setCellValue('B5', 'Usaha');
$objset->setCellValue('B6', 'Perusahaan');
$objset->setCellValue('B7', 'Alamat');
$objset->setCellValue('C42', 'JUMLAH');
$objset->setCellValue('D42', '=SUM(D10:D41)');
$objget->getStyle('C42')->getFont()->setBold(true);
$objget->getStyle('D42')->getFont()->setBold(true);
foreach ($query->result() as $row) {
$objset->setCellValue('D3', $row->masabulan . ' ' . $row->masatahun);
$objset->setCellValue('D4', $row->nopd);
$objset->setCellValue('D5', $row->usahanm);
$objset->setCellValue('D6', $row->customernm);
$objset->setCellValue('D7', $row->customeralamat);
break;
}
//table header
$cols = array("B", "C", "D", "E");
$val = array("No", "Tanggal", "Jumlah Omset(Rp.)", "Keterangan");
for ($a = 0; $a < 4; $a++) {
$objset->setCellValue($cols[$a] . '9', $val[$a]);
//set borders
$objget->getStyle($cols[$a] . '9')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objget->getStyle($cols[$a] . '9')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objget->getStyle($cols[$a] . '9')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objget->getStyle($cols[$a] . '9')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//set alignment
$objget->getStyle($cols[$a] . '9')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//set font weight
$objget->getStyle($cols[$a] . '9')->getFont()->setBold(true);
$objget->getColumnDimension('D')->setWidth(20);
$objget->getColumnDimension('E')->setWidth(30);
}
//taruh baris data disini
$colsb = array("B", "C", "D", "E");
$i = 10;
// baris ke
foreach ($query->result() as $row) {
if ($row->urut == 32) {
$urut = 'Lainnya';
} else {
$urut = $row->urut;
}
$valb = array($row->nomor, $urut, $row->omset, $row->keterangan);
for ($b = 0; $b < 4; $b++) {
$objset->setCellValue($colsb[$b] . $i, $valb[$b]);
$objget->getStyle($colsb[$b] . $i)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objget->getStyle($colsb[$b] . $i)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objget->getStyle($colsb[$b] . $i)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objget->getStyle($colsb[$b] . $i)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
}
$i = $i + 1;
$customer_id = $row->customer_id;
}
if ($customer_id == $wp_id) {
//cegah id lain download
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('assets/file/omsetxls/OmsetBulananID' . $wp_id . '.xls');
//simpan dalam file sample.xls
sleep(3);
//Save dulu
$data = file_get_contents('assets/file/omsetxls/OmsetBulananID' . $wp_id . '.xls');
// Read the file's contents
$name = 'Laporan Omset Bulanan.xls';
force_download($name, $data);
} else {
echo "<h1>ACCESS FORBIDDEN</h1>";
}
}
public function download($pkey, $method = "echo")
{
_has_user_access_permission(TRUE, array('admin'));
$params = ($params = unserialize_object($pkey)) && is_array($params) ? $params : array();
$company_id = in_array($this->current_user->group_id, array(GROUP_ADMIN, GROUP_STAFF, GROUP_ENGINEER)) ? $this->_post_args('company_id', ARGS_TYPE_INT, array_key_exists(SYS_COMPANY_ID, $params) && gtzero_integer($params[SYS_COMPANY_ID]) ? to_int($params[SYS_COMPANY_ID]) : 0) : $this->current_user->company_id;
$site_id = isset($params[SYS_SITE_ID]) && gtzero_integer($params[SYS_SITE_ID]) ? to_int($params[SYS_SITE_ID]) : 0;
$redirect_url = $this->_post_args('redirect_url', ARGS_TYPE_STRING, $this->agent->referrer());
$site_statuses = array('' => '', 1 => 'OPEN', 2 => 'SUBMITTED', 3 => 'COMPLETED');
$site_info = $this->site_m->details($site_id, $company_id);
if (!$site_info || _has_company_group_access($this->current_user->group_id) && $site_info->company_id != $this->current_user->company_id) {
$this->show_permission_denied_error($method);
}
$company_id = in_array($this->current_user->group_id, array(GROUP_ADMIN, GROUP_STAFF, GROUP_ENGINEER)) ? $this->_post_args('company_id', ARGS_TYPE_INT, $site_info->company_id) : $this->current_user->company_id;
$doc_key = $this->_post_args('doc_key', ARGS_TYPE_STRING) ? $this->_post_args('doc_key', ARGS_TYPE_STRING) : keygen();
$csrf = _get_csrf_nonce();
$headings = array("SITE", "FORM", "DATE ADDED", "ADDED BY", "STATUS", "DATE SUBMITTED", "SUBMITTED BY", "DATE COMPLETED", "COMPLETED BY");
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
// Create a new PHPExcel object
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('List of Site Forms');
$rowNumber = 1;
$col = 'A';
foreach ($headings as $heading) {
$objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading);
$col++;
}
// Loop through the result set
$rowNumber = 2;
foreach ($site_info->site_forms as $site_form) {
$col = 'A';
$objPHPExcel->getActiveSheet()->setCellValue($col++ . $rowNumber, $site_info->site_code);
$objPHPExcel->getActiveSheet()->setCellValue($col++ . $rowNumber, $site_form->form_name);
$objPHPExcel->getActiveSheet()->setCellValue($col++ . $rowNumber, _validate_date($site_form->added_on, 'Y-m-d H:i:s') ? local_time($site_form->added_on, 'M d, Y @ h:ia') : '');
$objPHPExcel->getActiveSheet()->setCellValue($col++ . $rowNumber, $site_form->added_by_name);
$objPHPExcel->getActiveSheet()->setCellValue($col++ . $rowNumber, $site_statuses[$site_form->status]);
$objPHPExcel->getActiveSheet()->setCellValue($col++ . $rowNumber, _validate_date($site_form->submitted_on, 'Y-m-d H:i:s') ? local_time($site_form->submitted_on, 'M d, Y @ h:ia') : '');
$objPHPExcel->getActiveSheet()->setCellValue($col++ . $rowNumber, $site_form->submitted_by_name);
$objPHPExcel->getActiveSheet()->setCellValue($col++ . $rowNumber, _validate_date($site_form->completed_on, 'Y-m-d H:i:s') ? local_time($site_form->completed_on, 'M d, Y @ h:ia') : '');
$objPHPExcel->getActiveSheet()->setCellValue($col++ . $rowNumber, $site_form->completed_by_name);
$rowNumber++;
}
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
// We'll be outputting an excel file
header('Content-type: application/vnd.ms-excel');
// It will be called file.xls
header('Content-Disposition: attachment; filename="' . date('Ymd') . '.xls"');
// Write file to the browser
$objWriter->save('php://output');
}
/**
*
* 导出会议室使用情况
*/
public function export()
{
$data = array();
if (checkRight('room_used_export') === FALSE) {
$this->showView('denied', $data);
exit;
}
$this->load->model('MIS_Room');
$t = $this->input->get('t');
$info = $this->MIS_Room->getUsedList($t);
$room_type = $this->config->item('room_type');
//加载PHPExcel库
require_once THIRD_PATH . 'PHPExcel.php';
require_once THIRD_PATH . 'PHPExcel/IOFactory.php';
$objPHPExcel = new PHPExcel();
//列宽
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(24);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(24);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(24);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(24);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(24);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(24);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(36);
//第一行
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(30);
$objPHPExcel->getActiveSheet()->mergeCells('A1:H1');
$objPHPExcel->getActiveSheet()->setCellValue('A1', '创投大厦' . $room_type[$t] . '使用情况登记表');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//第二行
$objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(20);
$objPHPExcel->getActiveSheet()->setCellValue('A2', '序号');
$objPHPExcel->getActiveSheet()->setCellValue('B2', '日期 ');
$objPHPExcel->getActiveSheet()->setCellValue('C2', '使用单位');
$objPHPExcel->getActiveSheet()->setCellValue('D2', '使用前电量');
$objPHPExcel->getActiveSheet()->setCellValue('E2', '使用后电量');
$objPHPExcel->getActiveSheet()->setCellValue('F2', '使用电量');
$objPHPExcel->getActiveSheet()->setCellValue('G2', '会议时长');
$objPHPExcel->getActiveSheet()->setCellValue('H2', '备注');
$objPHPExcel->getActiveSheet()->getStyle('A2:H2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A2:H2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A2:H2')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$n = 3;
foreach ($info as $k => $item) {
$objPHPExcel->getActiveSheet()->getRowDimension($n)->setRowHeight(20);
$objPHPExcel->getActiveSheet()->setCellValue('A' . $n, $k + 1);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $n, date('Y年m月d日', $item['start_time']));
$objPHPExcel->getActiveSheet()->setCellValue('C' . $n, $item['enterprise_name']);
$objPHPExcel->getActiveSheet()->setCellValue('G' . $n, ($item['end_time'] - $item['start_time']) / 3600 . '小时');
$objPHPExcel->getActiveSheet()->getStyle("A{$n}:H{$n}")->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle("A{$n}:H{$n}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle("A{$n}:H{$n}")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$n++;
}
$objPHPExcel->getActiveSheet()->setTitle($room_type[$t] . '使用情况登记表');
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel2007');
$file_name = $room_type[$t] . "使用情况登记表_" . date("YmdHis") . ".xlsx";
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $file_name . '"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
}
public function addAlls()
{
//上传对应的Excel文件
//文件上传
//基本配置
$config['upload_path'] = './uploads/';
$config['allowed_types'] = 'xls|xlsx|xl';
$config['max_size'] = '20000';
$config['file_name'] = time() . mt_rand(1000, 9999);
//载入上传类
$this->load->library('upload', $config);
//执行上传
$status = $this->upload->do_upload('Excel');
$wrong = $this->upload->display_errors();
if ($wrong) {
error($wrong);
}
//返回信息
$info = $this->upload->data();
// p($info);
//再进行解析对应的Excel文件
//找到对应的文件
$targetfile = './uploads/' . $info['file_name'];
//生成对应的格式
//需要注意的是,这里的excel文件要对应好不同的格式
// $objReader=IOFactory::createReader('Excel2007');
$objReader = IOFactory::createReaderForFile($targetfile);
//读取整个execl文件生成excel的object
$objPHPExcel = $objReader->load($targetfile);
//获取总的sheet数
$size = $objPHPExcel->getSheetCount();
//全部数据保存在的数组
$alldata = array();
for ($i = 0; $i < $size; $i++) {
//读取第一行sheet
$sheet = $objPHPExcel->getSheet($i);
//获取总行数
$hightRow = $sheet->getHighestRow();
// 取得总列数
$hightColum = $sheet->getHighestColumn();
//全部的excel对象数组
// p($objPHPExcel->getActiveSheet()->toArray());
// p($hightColum);
// p($hightRow);
// echo "<hr>";
// 获取excel对象的全部数据
$importdata = $objPHPExcel->getActiveSheet()->toArray();
$z = 0;
// 存放字段值数组
$firstname = array();
foreach ($importdata as $key => $value) {
if ($z == 0) {
$firstname = $this->toNameforArray($value);
$inportsize = $this->IstoMyExcelField($firstname);
// 需要满足最起码的8字段值
if ($inportsize != 8) {
success('AddStudents/index', '导入失败,你导入数据不符合规范...');
break;
}
} else {
$newinfoarr = $this->newarraydata($firstname, $value);
//生成新的数组
array_push($alldata, $newinfoarr);
}
$z++;
}
}
//批量导入问题
// p($alldata);die();
// 1.校验用户是否存在
$mynewUsers = $this->IsHaveUser($alldata);
// p($mynewUsers);die();
$sumsize = count($alldata);
$mynewUserssize = count($mynewUsers);
$num = $sumsize - $mynewUserssize;
// 2.导入过滤的数据
if ($mynewUserssize) {
$this->addstumodel->addAllFromOneExcel($mynewUsers);
}
success('AddStudents/index', '总共导入数据' . $sumsize . '条,成功导入' . $mynewUserssize . '条,失败' . $num . '条[原因:此用户已存在]');
}
/**
* 学生报名信息核对
*/
public function info_check()
{
if ($this->input->post('grade_id')) {
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
$grade_id = intval($this->input->post('grade_id'));
$school_id = intval($this->input->post('school_id'));
if (!$grade_id) {
message('请选择年级');
}
if (!$school_id) {
message('请选择学校');
}
$file = $_FILES['infos']['name'];
$desc = '../../cache/excel/' . $file;
$tmp_name = $_FILES['infos']['tmp_name'];
$extend = strrchr($file, '.');
if (!in_array($extend, array('.xlsx', '.xls'))) {
message('文件类型不合法');
}
if (!move_uploaded_file($tmp_name, $desc)) {
message('文件移动失败，请联系管理员');
}
$reader = new PHPExcel_Reader_Excel2007();
$reader_type = $extend == '.xlsx' ? 'Excel2007' : 'Excel5';
$obj = new IOFactory();
$objreader = $obj::createReader($reader_type)->load($desc);
$sheet = $objreader->getSheet(0);
$allRow = $sheet->getHighestRow();
$file2 = $file . '信息核对反馈表';
$objexcel = new PHPExcel();
$objexcel->setActiveSheetIndex(0)->setCellValue('A1', '姓名');
$objexcel->setActiveSheetIndex(0)->setCellValue('B1', '学号');
$objexcel->setActiveSheetIndex(0)->setCellValue('C1', '年级');
$objexcel->setActiveSheetIndex(0)->setCellValue('D1', '准考证号');
$objexcel->setActiveSheetIndex(0)->setCellValue('E1', '报名');
$objexcel->getActiveSheet()->setTitle($file2);
$objwriter = IOFactory::createWriter($objexcel, $reader_type);
for ($i = 2; $i <= $allRow; $i++) {
$arr = array();
$student_name = $objreader->getActiveSheet()->getCell('A' . $i)->getValue();
$student_number = $objreader->getActiveSheet()->getCell('B' . $i)->getValue();
$student_grade = $objreader->getActiveSheet()->getCell('C' . $i)->getValue();
$space = " ";
$student_name = str_ireplace($space, "", $student_name);
$count = 0;
$objexcel->setActiveSheetIndex(0)->setCellValue('A' . $i, $student_name);
$objexcel->setActiveSheetIndex(0)->setCellValue('B' . $i, $student_number);
$objexcel->setActiveSheetIndex(0)->setCellValue('C' . $i, $student_grade);
$student_search = array('school_id' => $school_id, 'grade_id' => $grade_id, 'keyword' => "CONCAT(last_name,first_name) LIKE '%" . trim($student_name) . "%'");
$student_list = CommonModel::get_list($student_search, 'rd_student', '*');
$number = count($student_list);
if ($number) {
if ($number > 1) {
//2代表错误，一个学校一个年纪有多个重名学生情况
$objexcel->setActiveSheetIndex(0)->setCellValue('D' . $i, '报名出错了(重名情况)');
$objexcel->setActiveSheetIndex(0)->setCellValue('E' . $i, 2);
} else {
$student_info = array_shift($student_list);
$idcard = $student_info['idcard'];
$objexcel->setActiveSheetIndex(0)->setCellValue('D' . $i, $student_info['exam_ticket']);
$objexcel->setActiveSheetIndex(0)->setCellValue('E' . $i, 1);
}
} else {
$objexcel->setActiveSheetIndex(0)->setCellValue('D' . $i, '未报名');
$objexcel->setActiveSheetIndex(0)->setCellValue('E' . $i, 0);
}
}
$objwriter->save($desc);
$data = array('url' => $desc, 'act' => 'update', 'name' => $file);
} else {
$grades = C('grades');
$data = array('act' => 'add', 'grades' => $grades);
}
$this->load->view('/student/info_check', $data);
}
public function getExcel()
{
$result = $this->conn->CIT_SELECT($this->table);
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setTitle("title")->setDescription("description");
// Assign cell values
$objPHPExcel->setActiveSheetIndex(0);
$sheet = $objPHPExcel->getActiveSheet();
$sheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$sheet->setShowGridlines(false);
$sheet->getColumnDimension('A')->setWidth(10);
$sheet->getColumnDimension('B')->setWidth(21.57);
$sheet->getRowDimension('1')->setRowHeight(10);
$sheet->setCellValue('A1', 'ID MANUFAKTUR');
$sheet->setCellValue('B1', 'NAMA MANUFAKTUR');
$counter = 2;
foreach ($result as $row) {
$sheet->setCellValue('A' . $counter, $row["manufakturID"]);
$sheet->setCellValue('B' . $counter, $row["manufakturNama"]);
$counter++;
}
// Save it as an excel 2003 file
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
$filename = date('dmYHis') . "-manufaktur.xls";
$path = "./" . ASSETS . 'userfiles/' . $filename;
$objWriter->save($path);
$result = array();
$result = array("alert" => "<h4 class=\"alert_success\">FILE BERHASIL DI EXPORT. <a href=\"" . $path . "\">KLIK UNTUK MENGUNDUH FILE</a></h4>");
echo json_encode($result);
//echo $filename;
}
/**
* 评估报告下载处理
*
* @return void
*/
public function report_deal()
{
$post = $this->input->post();
/** 上传文件 */
$config['upload_path'] = '../../cache/excel/';
$config['allowed_types'] = '*';
$config['max_size'] = 1024 * 10;
#单位kb
$config['overwrite'] = false;
$this->load->library('upload', $config);
if (!$this->upload->do_upload('file')) {
$this->report($this->upload->display_errors());
} else {
/* 教师信息 */
$teacher = $this->session->userdata('teacher');
$upload_data = $this->upload->data();
$message = array();
$exam_id = $post['exam_id'];
if (empty($exam_id)) {
message('未查询到当前考试期次信息！请联系管理员！');
}
/* 考试期次信息 */
$sql = "SELECT * FROM {pre}exam WHERE exam_id={$exam_id}";
$exam = $this->db->query($sql)->row_array();
if (empty($exam)) {
message('未查询到当前考试期次信息！请联系管理员！');
}
/* 当前考试期次下的学科 */
$sql = "SELECT exam_id,subject_id FROM {pre}exam WHERE exam_pid={$exam_id}";
$subjects_exam_tmp = $this->db->query($sql)->result_array();
if (empty($subjects_exam_tmp)) {
message('当前考试期次下不存在学科！请联系管理员！');
}
$subjects_exam = array();
foreach ($subjects_exam_tmp as $key => $value) {
$subjects_exam[$value['subject_id']] = $value;
}
/* 学科 */
$subjects_tmp = json_decode($teacher['subjects'], true);
if (empty($subjects_tmp) || count($subjects_tmp) <= 0) {
message('当前帐号暂未选择学科，请联系管理员！');
}
$subjects = array();
/* 有报告的学科 */
foreach ($subjects_tmp as $key => $value) {
if (isset($subjects_exam[$value]) || is_numeric($value) && $value == 0) {
$subjects[$value]['is_exist'] = true;
} else {
$subjects[$value]['is_exist'] = false;
}
}
/* 考试期次对应学生 考试期次-> 评估规则 -> 考生*/
$sql = "SELECT er.id AS rule_id,es.uids from {pre}evaluate_student AS es LEFT JOIN {pre}evaluate_rule AS er ON es.rule_id=er.id WHERE er.exam_pid={$exam_id}";
$row = $this->db->query($sql)->row_array();
if (empty($row)) {
message('获取当前考试期次下考生信息失败，请联系管理员！');
}
$rule_id = $row['rule_id'];
$uids = explode(',', $row['uids']);
if (empty($uids) || count($uids) <= 0) {
message('当前考试期次下不存在考生！');
}
/** 读取excel */
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
$inputFileType = IOFactory::identify($upload_data['file_relative_path']);
$objReader = IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($upload_data['file_relative_path']);
$data = $objPHPExcel->getSheet(0)->toArray();
if (empty($data) || count($data) <= 1) {
message('表格数据错误，请下载表格模板从新填写！');
}
$students = array();
foreach ($data as $key => $value) {
if ($key < 1) {
continue;
}
if (empty($value[0])) {
continue;
}
/* 准考证号转换为uid */
$ticket = $value[0];
/* 准考证类型 */
/* if ($exam['exam_ticket_maprule'] > 0) {
$sql = "SELECT uid,student_name AS name FROM {pre}exam_student_list WHERE student_ticket={$ticket}";
} else { */
$sql = "SELECT uid,concat(last_name,first_name) AS name FROM {pre}student WHERE exam_ticket={$ticket}";
/* } */
$row = $this->db->query($sql)->row_array();
$uid = $row['uid'];
$students[$key]['ticket'] = $ticket;
if (!empty($uid) && $uid > 0 && in_array($uid, $uids)) {
$students[$key]['uid'] = $uid;
$students[$key]['name'] = $row['name'];
} else {
$students[$key]['uid'] = false;
}
}
$data = array();
$data['students'] = $students;
$data['subjects'] = $subjects;
$subject_sys = C('subject');
/* 加入总结报告 */
$subject_sys[0] = '总结';
$data['subject_sys'] = $subject_sys;
$data['rule_id'] = $rule_id;
$this->load->view('teacher_download/report_list', $data);
}
}
$xls->getActiveSheet()->getStyle("F{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("G{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("H{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("I{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("J{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("K{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("L{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("M{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("N{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("O{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("P{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("Q{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("R{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("S{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("T{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("U{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("V{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("W{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("X{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("Y{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("Z{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("AA{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("AB{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("AC{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("AD{$i}")->getAlignment()->setHorizontal('center');
$xls->getActiveSheet()->getStyle("A{$i}:AD{$i}")->getFont()->setBold(true);
$xls->getActiveSheet()->getRowDimension($i)->setRowHeight(25);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="R.Askes_' . $bulan . '_' . $tahun . '.xls"');
$objWriter = IOFactory::createWriter($xls, "Excel5");
$objWriter->save("php://output");
/**
* Load document
*/
public function testLoad()
{
$file = __DIR__ . '/_files/templates/blank.docx';
$this->assertInstanceOf('PhpOffice\\PhpWord\\PhpWord', IOFactory::load($file));
}
