Hướng dẫn xử lý ký tự đặc biệt khi xuất excel

Thứ hai - 21/03/2022 00:07
NukeViet CMS là hệ quản trị nội dung nguồn mở duy nhất của Việt Nam nằm trong danh mục các sản phẩm phần mềm nguồn mở được khuyến khích sử dụng trong các website về TMDT, Tin Tức, Giới thiệu doanh nghiệp,...



Hướng dẫn xử lý ký tự đặc biệt khi xuất excel 

xử lý kí tự đặc biệt trong nukeviet chúng ta sử dụng hàm 

nv_htmlspecialchars

Chuyển đổi các kí tự đặc biệt thành chuỗi  
&  	=>	  &  
\  	=>	  '  
"  	=>	  "  
<  	=>	  &lt;  
>  	=>	  &gt;  
\\  	=>	  &#x005C;  
/  	=>	  &#x002F;  
(  	=>	  &#40;  
)  	=>	  &#41;  
*  	=>	  &#42;  
[  	=>	  &#91;  
]  	=>	  &#93;  
!  	=>	  &#33;  
=  	=>	  &#x3D;  
#  	=>	  &#x23;  
%  	=>	  &#x25;  
^  	=>	  &#x5E;  
:  	=>	  &#x3A;  
{  	=>	  &#x7B;  
}  	=>	  &#x7D;  
`  	=>	  &#x60;  
~  	=>	  &#x7E;
 

Cú pháp: 

$objPHPExcel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($col) . $rowIndex, nv_htmlspecialchars ($title));

Lấy dữ liệu xuống và xử lý ký tự đặc biệt  
// Hien thi danh sach cau tra loi
			$i = $rowIndex + 1;
			$number = 1;
			foreach ($array_data as $data) {
				// $date_time =  date('d/m/Y h:i:s A',$data['publtime']);
				$data['publtime'] = date('d/m/Y',$data['publtime']);
				$j = $columnIndex;
				foreach ($array_title as $field => $title) {  
					$col = PHPExcel_Cell::stringFromColumnIndex($j);
					$CellValue = $data[$field];
					$objPHPExcel->getActiveSheet()->setCellValue($col . $i, nv_htmlspecialchars ($CellValue));
					$j++;
				}
				$i++;
			}
Dưới đây là mẫu xuất excel  
<?php

/**
 * @Project NUKEVIET 4.x
 * @Author ASUZAC.,JSC <[email protected]>
 * @Copyright (C) 2020 ASUZAC.,JSC. All rights reserved
 * @License: Not free read more http://nukeviet.vn/vi/store/modules/nvtools/
 * @Createdate Wed, 16 Dec 2020 08:26:00 GMT
 */

if (!defined('NV_IS_MOD_STATISTICAL')) {
    die('Stop!!!');
}

// $page_title = $module_info['site_title'];
$key_words= $module_info['key_words'];
$contents = '';
$cache_file = '';
//op
$base_url = NV_BASE_SITEURL . 'index.php?' . NV_LANG_VARIABLE . '=' . NV_LANG_DATA . '&amp;' . NV_NAME_VARIABLE . '=' . $module_name;
if (($page < 2 and isset($array_op[0])) or isset($array_op[1])) {
    nv_redirect_location($base_url);
}
if (!defined('NV_IS_MODADMIN') and $page < 5) {
    $cache_file = NV_LANG_DATA . '_' . $module_info['template'] . '-' . $op . '-' . $page . '-' . NV_CACHE_PREFIX . '.cache';
    if (($cache = $nv_Cache->getItem($module_name, $cache_file, 3600)) != false) {
        $contents = $cache;
    }
}
$page_title = $lang_module['content_list'];
/**
 * 
 */
$stype = $nv_Request->get_string('stype', 'get', '-');
$sstatus = $nv_Request->get_int('sstatus', 'get', -1);
$catid = $nv_Request->get_int('catid', 'get', 0);
$per_page_old = $nv_Request->get_int('per_page', 'cookie', 50);
$per_page = $nv_Request->get_int('per_page', 'get', $per_page_old);
$num_items = $nv_Request->get_int('num_items', 'get', 0);
$key = nv_substr($nv_Request->get_title('q', 'get', '', 1), 0, 100);
$from_date = $nv_Request->get_title('from_date', 'get', '', 1);
$to_date = $nv_Request->get_title('to_date', 'get', '', 1);
$check_num = $nv_Request->get_int('choose', 'get', 1);
$pages = $nv_Request->get_int('page', 'get', 1);
$date_array['from_date'] = $from_date;
$date_array['to_date'] = $to_date;

if ($per_page < 1 and $per_page > 500) {
    $per_page = 50;
}
if ($per_page_old != $per_page) {
    $nv_Request->set_Cookie('per_page', $per_page, NV_LIVE_COOKIE_TIME);
}
/**
 * lấy ra số page hiển thị
 */
$i = 5;
$search_per_page = array();
while ($i <= 500) {
    $search_per_page[] = array(
        'page' => $i,
        'selected' => ($i == $per_page) ? ' selected="selected"' : ''
    );
    $i = $i + 5;
}
/**
 * GetSourceNews()
 *
 * @param mixed $sourceid
 * @return
 */
//var_dump(class_exists('PHPExcel'));die;

$check_download = $nv_Request->get_int('export', 'get');
//var_dump(class_exists('PHPExcel'));die;
if(class_exists('PHPExcel')== false){
	$message = 'Please installer composer PHPExcel. Installation Instructions https://github.com/mynukeviet/plugin-msexcel';
    echo "<script>alert('$message')</script>";
}else{
	if(!$check_download){
		function GetSourceNews($sourceid)
		{
			global $db_slave, $module_data;
			if ($sourceid > 0) {
				$sql = 'SELECT title FROM nv4_vi_news_sources WHERE sourceid = ' . $sourceid;
				$re = $db_slave->query($sql);
				if (list ($title) = $re->fetch(3)) {
					return $title;
				}
			}
			return '-/-';
		}
		/**
		 * hiển thị danh mục tìm kiếm
		 *
		 * @param mixed $str
		 * @param mixed $keyword
		 * @return
		 */
			function BoldKeywordInStr($str, $keyword)
			{
				$str = nv_clean60($str, 300);
				if (!empty($keyword)) {
					$tmp = explode(' ', $keyword);
					foreach ($tmp as $k) {
						$tp = strtolower($k);
						$str = str_replace($tp, '<span class="keyword">' . $tp . '</span>', $str);
						$tp = strtoupper($k);
						$str = str_replace($tp, '<span class="keyword">' . $tp . '</span>', $str);
						$k[0] = strtoupper($k[0]);
						$str = str_replace($k, '<span class="keyword">' . $k . '</span>', $str);
					}
				}
				return $str;
			}
			$key = $nv_Request->get_title('q', 'get', '');
			$key = str_replace('+', ' ', $key);
			$key = trim(nv_substr($key, 0, NV_MAX_SEARCH_LENGTH));
			$keyhtml = nv_htmlspecialchars($key);
			$base_url_rewrite = NV_BASE_SITEURL . 'index.php?' . NV_LANG_VARIABLE . '=' . NV_LANG_DATA . '&' . NV_NAME_VARIABLE . '=' . $module_name . '&' . NV_OP_VARIABLE . '=' . $op;
			if (!empty($key)) {
				$base_url_rewrite .= '&q=' . urlencode($key);
			}
			$choose = $nv_Request->get_int('choose', 'get', 0);
			if (!empty($choose)) {
				$base_url_rewrite .= '&choose=' . $choose;
			}
			$catid = $nv_Request->get_int('catid', 'get', 0);
			if (!empty($catid)) {
				$base_url_rewrite .= '&catid=' . $catid;
			}
			$from_date = $nv_Request->get_title('from_date', 'get', '', 0);
			$date_array['from_date'] = preg_replace('/[^0-9]/', '.', urldecode($from_date));
			if (preg_match('/^([0-9]{1,2})\.([0-9]{1,2})\.([0-9]{4})$/', $date_array['from_date'])) {
				$base_url_rewrite .= '&from_date=' . $date_array['from_date'];
			}
			$to_date = $nv_Request->get_title('to_date', 'get', '', 0);
			$date_array['to_date'] = preg_replace('/[^0-9]/', '.', urldecode($to_date));
			if (preg_match('/^([0-9]{1,2})\.([0-9]{1,2})\.([0-9]{4})$/', $date_array['to_date'])) {
				$base_url_rewrite .= '&to_date=' . $date_array['to_date'];
			}
			$page = $nv_Request->get_int('page', 'get', 1);
			if ($page > 1) {
				$base_url_rewrite .= '&page=' . $page;
			}
			$base_url_rewrite = nv_url_rewrite($base_url_rewrite, true);
			$request_uri = $_SERVER['REQUEST_URI'];
			if ($request_uri != $base_url_rewrite and NV_MAIN_DOMAIN . $request_uri != $base_url_rewrite) {
				header('Location: ' . $base_url_rewrite);
				die();
			}
			$array_cat_search = array();
			foreach ($global_array_cat as $arr_cat_i) {
				$array_cat_search[$arr_cat_i['catid']] = array(
					'catid' => $arr_cat_i['catid'],
					'title' => $arr_cat_i['title'],
					'select' => ($arr_cat_i['catid'] == $catid) ? 'selected' : ''
				);
			}
			//$array_cat_search[0]['title'] = $lang_module['search_all'];
			$contents = call_user_func('search_theme', $key, $choose, $date_array, $array_cat_search,$search_per_page);
		$where = '';
		$tbl_src = '';
		if (empty($key) and ($catid == 0) and empty($from_date) and empty($to_date)) {
			$contents .= '<div class="alert alert-danger">' . $lang_module['empty_data_search'] . '</div>';
		} else {
			if ( $from_date != '') {
				preg_match('/^([0-9]{1,2})\.([0-9]{1,2})\.([0-9]{4})$/', $from_date, $m);
				$fdate = mktime(0, 0, 0, $m[2], $m[1], $m[3]);
				$where .= " AND ( publtime >=   $fdate ) ";
			}
			if($to_date !=''){
				preg_match('/^([0-9]{1,2})\.([0-9]{1,2})\.([0-9]{4})$/', $to_date, $m);
				$tdate = mktime(0, 0, 0, $m[2], $m[1], $m[3]);
				$where .= " AND ( publtime <   $tdate ) ";
			}
			if( $from_date != '' && $to_date !='' ){
				preg_match('/^([0-9]{1,2})\.([0-9]{1,2})\.([0-9]{4})$/', $from_date, $m);
				$fdate = mktime(0, 0, 0, $m[2], $m[1], $m[3]);
				preg_match('/^([0-9]{1,2})\.([0-9]{1,2})\.([0-9]{4})$/', $to_date, $m);
				$where .= " AND ( publtime >=   $fdate  AND   publtime <   $tdate) ";
			}
			//NV_PREFIXLANG . '_' . $module_data . '_' . $catid
			if ($catid > 0){
				$table_search = 'nv4_vi_news_' . $catid;
				$tbl_src = 'LEFT JOIN nv4_users tb2 ON tb1.admin_id = tb2.userid LEFT JOIN nv4_vi_news_cat tb3 ON (tb1.catid = tb3.catid)';
				$db_slave->sqlreset()
				->select('tb1.id,tb1.title,tb1.admin_id,tb2.username,tb1.alias,tb1.catid,tb1.hometext,tb1.author,tb1.publtime,tb1.hitstotal,tb1.homeimgfile, tb1.homeimgthumb,tb1.sourceid,tb1.external_link,tb3.title as namecat')
				->from($table_search . ' as tb1 ' . $tbl_src)
				->where('tb1.status=1 AND tb1.catid= '. $catid . $where)
				->order('tb1.' . $order_articles_by . ' DESC')
				->limit($per_page)
				->offset(($page - 1) * $per_page);
			} else if($catid == 0) {
				$table_search = 'nv4_vi_news_rows';
				$db_slave->select('tb1.id,tb1.title,tb1.alias,tb1.catid,tb1.hometext,tb1.author,tb1.publtime,tb1.hitstotal,tb1.homeimgfile, tb1.homeimgthumb,tb1.sourceid,tb1.external_link')
				->order('tb1.' . $order_articles_by . ' DESC')
				->limit($per_page)
				->offset(($page - 1) * $per_page);
			}
			$result = $db_slave->query($db_slave->sql());
			$array_content =  array();
			while (list ($id, $title, $admin_id, $username, $alias, $catid, $hometext, $author, $publtime,$hitstotal, $homeimgfile, $homeimgthumb, $sourceid, $external_link,$_title)= $result->fetch(3)) {
				if ($catid > 0) {
					$catid_i = $catid;
				}
				$array_content[] = array(
					'id' => $id,
					'title' => $title,
					'admin_id'=>$admin_id,
					'username'=>$username,
					'alias' => $alias,
					'catid' => $catid,
					'hometext' => $hometext,
					'author' => $author,
					'publtime' => $publtime,
					'hitstotal' => $hitstotal,
					'sourceid' => $sourceid,
					'external_link' => $external_link,
					'namecat' => $_title
				);
				$array_ids[$catid] = $catid;
			}
			$contents .= search_result_theme($key, $numRecord, $per_page, $page, $array_content, $catid,$array_userid,$array_namecat,$url_download);
		}
	}else{
		$array_data = array();
		$table_search = 'nv4_vi_news_' . $catid;
		$tbl_src = 'LEFT JOIN nv4_users tb2 ON tb1.admin_id = tb2.userid LEFT JOIN nv4_vi_news_cat tb3 ON (tb1.catid = tb3.catid)';
		$db_slave->sqlreset()
		->select('tb1.id,tb1.title,tb1.admin_id,tb2.username,tb1.catid,tb1.author,tb1.publtime,tb1.hitstotal,tb1.sourceid,tb3.title as namecat')
		->from($table_search . ' as tb1 ' . $tbl_src)
		->where('tb1.status=1 AND tb1.catid= '. $catid . $where);
		$result = $db_slave->query($db_slave->sql());
		$a = 0;
		while ($row = $result->fetch()) {
			$array_data[] = $row;
		}
		if (!empty($array_data)) {
			$type = 'xlsx';
			$array_title = array(
				'id' => $lang_module['phpexcel_id'],
				'title' => $lang_module['phpexcel_title'],
				'namecat' => $lang_module['phpexcel_cat'],
				'username' => $lang_module['phpexcel_username'],
				'publtime' => $lang_module['phpexcel_time'],
				'hitstotal' => $lang_module['phpexcel_number']
			);
			$array = array(
				'objType' => 'Excel2007',
				'objExt' => 'xlsx'
			);
			$objPHPExcel = new PHPExcel();
			$objPHPExcel->setActiveSheetIndex(0);
			// Set properties
			$objPHPExcel->getProperties()
				->setCreator($admin_info['username'])
				->setLastModifiedBy($admin_info['username'])
				->setTitle($lang_module['content_list'])
				->setSubject($lang_module['content_list'])
				->setDescription($lang_module['content_list'])
				->setCategory($module_name);
			$columnIndex = 0; // Cot bat dau ghi du lieu
			$rowIndex = 3; // Dong bat dau ghi du lieu
			// Tieu de cot
			$col = $columnIndex;
			foreach ($array_title as $title) {
				$objPHPExcel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($col) . $rowIndex, nv_htmlspecialchars ($title));
				$col++;
			}
			// Hien thi danh sach cau tra loi
			$i = $rowIndex + 1;
			$number = 1;
			foreach ($array_data as $data) {
				// $date_time =  date('d/m/Y h:i:s A',$data['publtime']);
				$data['publtime'] = date('d/m/Y',$data['publtime']);
				$j = $columnIndex;
				foreach ($array_title as $field => $title) {  
					$col = PHPExcel_Cell::stringFromColumnIndex($j);
					$CellValue = $data[$field];
					$objPHPExcel->getActiveSheet()->setCellValue($col . $i, nv_htmlspecialchars ($CellValue));
					$j++;
				}
				$i++;
			}
			$highestRow = $i - 1;
			$highestColumn = PHPExcel_Cell::stringFromColumnIndex($j - 1);
			// Rename sheet
			$objPHPExcel->getActiveSheet()->setTitle('Sheet 1');
			// Set page orientation and size
			$objPHPExcel->getActiveSheet()
				->getPageSetup()
				->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
			$objPHPExcel->getActiveSheet()
				->getPageSetup()
				->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
			// Excel title
			$objPHPExcel->getActiveSheet()->mergeCells('A2:' . $highestColumn . '2');
			$objPHPExcel->getActiveSheet()->setCellValue('A2', strtoupper($lang_module['content_list']));
			$objPHPExcel->getActiveSheet()
				->getStyle('A2')
				->getAlignment()
				->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$objPHPExcel->getActiveSheet()
				->getStyle('A2')
				->getAlignment()
				->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
			// Set color
			$styleArray = array(
				'borders' => array(
					'outline' => array(
						'style' => PHPExcel_Style_Border::BORDER_THIN,
						'color' => array(
							'argb' => 'FF000000'
						)
					)
				)
			);
			$objPHPExcel->getActiveSheet()
				->getStyle('A3' . ':' . $highestColumn . $highestRow)
				->applyFromArray($styleArray);
			// Set font size
			$objPHPExcel->getActiveSheet()
				->getStyle("A1:" . $highestColumn . $highestRow)
				->getFont()
				->setSize(13);
			// Set auto column width
			foreach (range('A', $highestColumn) as $columnID) {
				$objPHPExcel->getActiveSheet()
					->getColumnDimension($columnID)
					->setAutoSize(true);
			}
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $array['objType']);
			$file_src = NV_ROOTDIR . '/' . NV_TEMP_DIR . '/' . change_alias($lang_module['content_list']) . '.' . $array['objExt'];
			$objWriter->save($file_src);
			$download = new NukeViet\Files\Download($file_src, NV_ROOTDIR . '/' . NV_TEMP_DIR);
			$download->download_file();
			die();
		}
	}
}

if (empty($key)) {
    $page_title = $lang_module['search_title'] . NV_TITLEBAR_DEFIS . $module_info['custom_title'];
} else {
    $page_title = $key . NV_TITLEBAR_DEFIS . $lang_module['search_title'];
    if ($page > 2) {
        $page_title .= NV_TITLEBAR_DEFIS . $lang_global['page'] . ' ' . $page;
    }
    $page_title .= NV_TITLEBAR_DEFIS . $module_info['custom_title'];
}
$key_words = $description = 'no';
$mod_title = isset($lang_module['main_title']) ? $lang_module['main_title'] : $module_info['custom_title'];
include (NV_ROOTDIR . "/includes/header.php");
echo nv_site_theme($contents);
include (NV_ROOTDIR . "/includes/footer.php");
Mong bài viết giúp ích được các bạn phần nào trong thiết kế Web. Hãy nhấn nút like và share  để mọi người cùng học hỏi kiến thức mới nhé. Cảm ơn các bạn đã quan tâm VNCODE.

Tác giả: admin, Nukeviet Edu

 Tags: php excel

Tổng số điểm của bài viết là: 0 trong 0 đánh giá

Click để đánh giá bài viết

  Ý kiến bạn đọc

Những tin mới hơn

Những tin cũ hơn

Hỏi đáp với tôi

close
  • smart_toy Hi there 👋 How can I help you today?
send
Bạn đã không sử dụng Site, Bấm vào đây để duy trì trạng thái đăng nhập. Thời gian chờ: 60 giây