Có thể bạn quan tâm
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
& => &
=> '
" => "
< => <
> => >
\ => \
/ => /
( => (
) => )
* => *
[ => [
] => ]
! => !
= => =
# => #
% => %
^ => ^
: => :
{ => {
} => }
` => `
~ => ~
Xem Thêm : Chia sẻ theme wordpress Astra Pro miễn phí
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 <info@asuzac.com.vn>
* @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 . '&' . 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 NukeVietFilesDownload($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.
Nguồn: https://vncode.info
Danh mục: Thủ Thuật Chia Sẻ Hay
