public function actionExport()
{
$id = Yii::app()->request->getParam('id');
$page = Yii::app()->request->getParam('page', 1);
$limit = 50000;
$offset = ($page - 1) * $limit;
$sql = "SELECT msisdn from tools_msisdn \n\t\t\t\tWHERE setting_id=:sid \n\t\t\t\tLIMIT :limit\n\t\t\t\tOFFSET :offset\n\t\t\t\t";
$command = Yii::app()->db->createCommand($sql);
$command->bindParam(':sid', $id, PDO::PARAM_STR);
$command->bindParam(':offset', $offset, PDO::PARAM_INT);
$command->bindParam(':limit', $limit, PDO::PARAM_INT);
$data = $command->queryAll();
$label = array('msisdn' => 'Thuê bao');
$title = Yii::t('admin', 'Export_' . $page);
$excelObj = new ExcelExport($data, $label, $title);
$excelObj->export();
}
static function export($file_export, $content_theme)
{
if (!self::$xls_exporter) {
require_once ROOT_PATH . 'includes/html_to_xls.inc.php';
self::$xls_exporter = new HTML_TO_XLS();
$excel_css = file_get_contents(ROOT_PATH . 'style/excel.css');
self::$xls_exporter->setCSS(str_replace("\r\n", '', $excel_css));
}
self::$xls_exporter->createXls($content_theme, $file_export, true);
}
public function leaderexportAction()
{
$result = $this->roleInfo('L');
$excelExport = new ExcelExport();
$excelExport->LeaderExport($result);
}
function exportXLS($data)
{
require_once 'ExcelExport.php';
$this->exportFileName = $this->getExportFileName();
$cols = $this->getDisplayColumnInfo();
$xls = new ExcelExport();
$colHeader = array();
foreach ($cols as $idx => $col) {
array_push($colHeader, $col['header']);
}
$xls->addRow($colHeader);
foreach ($data as $idx => $record) {
$record = $this->object2array($record);
$recordA = array();
foreach ($cols as $idx => $col) {
$f = $record[$col['fieldIndex']];
array_push($recordA, empty($f) ? '' : $f);
}
$xls->addRow($recordA);
}
$xls->download($this->exportFileName . '.xls');
}
public function actionIndex()
{
$pageSize = Yii::app()->request->getParam('pageSize', Yii::app()->params['pageSize']);
Yii::app()->user->setState('pageSize', $pageSize);
$model = new AdminVideoModel('search');
$copyrightType = Yii::app()->request->getParam('ccp_type', null);
if (isset($_GET['AdminVideoModel'])) {
if (isset($_GET['AdminVideoModel']['created_time']) && $_GET['AdminVideoModel']['created_time'] != "") {
// Re-setAttribute create datetime
$createdTime = $_GET['AdminVideoModel']['created_time'];
if (strrpos($createdTime, "-")) {
$createdTime = explode("-", $createdTime);
$fromDate = explode("/", trim($createdTime[0]));
$fromDate = $fromDate[2] . "-" . str_pad($fromDate[0], 2, '0', STR_PAD_LEFT) . "-" . str_pad($fromDate[1], 2, '0', STR_PAD_LEFT);
$fromDate .= " 00:00:00";
$toDate = explode("/", trim($createdTime[1]));
$toDate = $toDate[2] . "-" . str_pad($toDate[0], 2, '0', STR_PAD_LEFT) . "-" . str_pad($toDate[1], 2, '0', STR_PAD_LEFT);
$toDate .= " 23:59:59";
} else {
$fromDate = date("Y-m-d", strtotime($_GET['AdminVideoModel']['created_time'])) . " 00:00:00";
$toDate = date("Y-m-d", strtotime($_GET['AdminVideoModel']['created_time'])) . " 23:59:59";
}
}
}
$is_composer = "";
if (isset($_GET['is_composer']) && $_GET['is_composer'] > 0) {
$is_composer = $_GET['is_composer'];
}
$is_lyric = "";
if (isset($_GET['is_lyric']) && $_GET['is_lyric'] > 0) {
$is_lyric = $_GET['is_lyric'];
}
$categoryList = AdminGenreModel::model()->gettreelist(2);
$cpList = AdminCpModel::model()->findAll();
$numPage = 1;
$count = 0;
if (isset($_GET['AdminVideoModel'])) {
$limit = 10;
$offset = 0;
$where = "";
if ($_GET['AdminVideoModel']['genre_id'] != '') {
$genreId = (int) $_GET['AdminVideoModel']['genre_id'];
$where .= " and (g.id = '{$genreId}' OR g.parent_id= '{$genreId}')";
}
if ($_GET['AdminVideoModel']['name'] != '') {
$where .= " and t.name LIKE '%" . $_GET['AdminVideoModel']['name'] . "%' ";
}
if ($_GET['AdminVideoModel']['artist_name'] != '') {
$where .= " and a.name LIKE '%" . $_GET['AdminVideoModel']['artist_name'] . "%' ";
}
if ($_GET['is_composer'] == 1 || $_GET['is_composer'] == 2) {
if ($_GET['is_composer'] == 1) {
$where .= " and t.composer_id > 0 ";
} else {
$where .= " and t.composer_id = 0 ";
}
}
if ($_GET['is_lyric'] == 1 || $_GET['is_lyric'] == 2) {
if ($_GET['is_lyric'] == 1) {
$where .= " and ve.description <> '' ";
} else {
$where .= " and (ve.description = '' OR ve.description is null)";
}
}
if ($_GET['AdminVideoModel']['max_bitrate'] != '') {
$bitRate = $_GET['AdminVideoModel']['max_bitrate'];
if ($bitRate == "720") {
$where .= " and t.profile_ids LIKE '%9%'";
}
}
if ($_GET['AdminVideoModel']['created_time'] != '') {
$where .= " and t.created_time BETWEEN '{$fromDate}' AND '{$toDate}' ";
}
$sql = "SELECT count(DISTINCT `t`.`id`)\n\t\t\t\t\tFROM video t \n\t\t\t\t\tINNER JOIN video_status st ON t.id = st.video_id\n\t\t\t\t\tLEFT JOIN video_artist va ON t.id =va.video_id\n\t\t\t\t\tLEFT JOIN artist a ON a.id=va.artist_id\n\t\t\t\t\tLEFT JOIN genre g ON g.id = t.genre_id\n\t\t\t\t\tLEFT JOIN video_extra ve ON t.id = ve.video_id\n\t\t\t\t\tWHERE true {$where}\n\t\t\t\t\t";
$count = Yii::app()->db->createCommand($sql)->queryScalar();
$perPage = 5000;
if ($count <= $perPage) {
$numPage = 1;
} elseif ($count % $perPage == 0) {
$numPage = $count / $perPage;
} else {
$numPage = $count / $perPage + 1;
$numPage = (int) $numPage;
}
if (isset($_GET['export'])) {
$page = Yii::app()->request->getParam('page', 1);
$limit = $perPage;
$offset = ($page - 1) * $limit;
$sql = "SELECT t.id, t.name as video_name, a.name as composer_name, \n\t\t\t\tt.composer_id, t.genre_id, g.name, t.artist_name,ve.description as lyric,\n\t\t\t\tsc.copryright_id as copyright_id,c.appendix_no,c.contract_no \n\t\t\t\tFROM video t\n\t\t\t\tINNER JOIN video_status st ON t.id = st.video_id\n\t\t\t\tLEFT JOIN video_artist va ON va.video_id = t.id\n\t\t\t\tLEFT JOIN artist a ON a.id=t.composer_id\n\t\t\t\tLEFT JOIN genre g ON g.id=t.genre_id\n\t\t\t\tLEFT JOIN video_extra ve ON t.id = ve.video_id\n\t\t\t\tLEFT JOIN video_copyright sc ON sc.video_id=t.id\n\t\t\t\tLEFT JOIN copyright c ON c.id=sc.copryright_id \t\t\t\t\n\t\t\t\tWHERE true {$where}\n\t\t\t\tGROUP BY t.id\n\t\t\t\tORDER BY t.id ASC\n\t\t\t\tLIMIT {$limit}\n\t\t\t\tOFFSET {$offset}\n\t\t\t\t";
$data = Yii::app()->db->createCommand($sql)->queryAll();
$label = array('id' => 'ID', 'video_name' => 'Video', 'artist_name' => 'Ca sỹ', 'composer_name' => 'Nhạc sỹ', 'composer_id' => 'composer_id', 'name' => 'Thể loại', 'lyric' => 'Lyric', 'contract_no' => 'Số hợp đồng', 'cc' => 'Tên CP, Ca sĩ, đại diện HĐ', 'appendix_no' => 'Số phụ lục', 'copyright_id' => 'ID Hợp đồng');
$title = Yii::t('admin', 'Export_' . $page);
$excelObj = new ExcelExport($data, $label, $title);
$excelObj->export();
exit;
}
}
$this->render('index', array('model' => $model, 'categoryList' => $categoryList, 'cpList' => $cpList, 'pageSize' => $pageSize, 'is_composer' => $is_composer, 'copyrightType' => $copyrightType, 'numPage' => $numPage, 'count' => $count, 'is_lyric' => $is_lyric));
}
<?php
# Export all transactions from database into an excel file
# Open database connection
include 'database.php';
# Excel export class
require_once "xls.php";
$xls = new ExcelExport();
# Create headings
$xls->addRow(array("Name", "Phone", "Email", "Address", "City", "State", "Zip", "Country", "School Name", "District", "Title I", "Percent Free Lunch", "Percent Eng Learners", "Percent Non White", "Years Taught", "Grades Taught", "Quantity", "Request Date", "Start Date", "End Date", "Approved", "Shipped"));
# Read from transactions table
$result = mysql_query("SELECT * FROM rentals_tbl Order By requestdate DESC");
while ($row = mysql_fetch_array($result)) {
# Populate spreadsheet cells with database row data
$xls->addRow(array($row['name'], $row['phone'], $row['email'], $row['address'], $row['city'], $row['state'], $row['zip'], $row['country'], $row['schoolname'], $row['district'], $row['titlei'], $row['freelunch'], $row['englearners'], $row['nonwhite'], $row['yearstaught'], $row['gradestaught'], $row['quantity'], $row['requestdate'], $row['startdate'], $row['enddate'], $row['rental_approved'], $row['shipstatus']));
}
# Create excel file for download
$xls->download("qcn-BorrowedSensors.xls");
# Close database connection
mysql_close($link);
<?php
# Export all transactions from database into an excel file
# Open database connection
include 'database.php';
# Excel export class
require_once "xls.php";
$xls = new ExcelExport();
# Create headings
$xls->addRow(array("Name", "Phone", "Email", "Address", "City", "State", "Zip", "Country", "Quantity. @ \$5.00", "Quantity @ \$49.00", "Date of Purchase", "Transaction Id", "Amount Billed", "Tax (Percent)", "Tax Amount", "Grand Total", "\$5 Sensor Approved", "Ship Status", "Email Confirmation Sent"));
# Read from transactions table
$result = mysql_query("SELECT * FROM transactions_tbl Order By purchasedate DESC");
while ($row = mysql_fetch_array($result)) {
# Calculate what was charged to the buyer
if ($row['qtySensor5'] != 0 && $row['qtySensor49'] != 0) {
$billedAmount = $row['qtySensor5'] * 5.0 + $row['qtySensor49'] * 49.0 . '.00';
}
if ($row['qtySensor5'] != 0 && $row['qtySensor49'] == 0) {
$billedAmount = $row['qtySensor5'] * 5.0 . '.00';
}
if ($row['qtySensor5'] == 0 && $row['qtySensor49'] != 0) {
$billedAmount = $row['qtySensor49'] * 49.0 . '.00';
}
# Set tax
$taxregion = $row['region_id'];
if ($taxregion != '4') {
$taxresult = mysql_query("SELECT * FROM taxregions_tbl WHERE id = '{$taxregion}'");
$taxrow = mysql_fetch_array($taxresult);
$tax = $taxrow['tax'];
if (strtotime($row['purchasedate']) < strtotime('07/01/2011')) {
$tax += 1;
public function exportroleAction($type)
{
$manager = $this->session->get('Manager');
if (empty($manager)) {
$this->dataReturn(array('error' => '用户信息失效，请重新登录!'));
return;
}
try {
$excelExport = new ExcelExport();
$file_name = '';
switch ($type) {
case 1:
$result = Examinee::find(array('project_id = ?1 AND type = 0 ', 'bind' => array(1 => $manager->project_id)));
$file_name = $excelExport->ExamineeExport($result, $manager->project_id);
$this->dataReturn(array('success' => $file_name));
return;
break;
case 2:
$result = Manager::find(array('project_id = ?1 AND role = \'I\'', 'bind' => array(1 => $manager->project_id)));
$file_name = $excelExport->InterviewerExport($result, $manager->project_id);
$this->dataReturn(array('success' => $file_name));
return;
break;
case 3:
$result = Manager::find(array('project_id = ?1 AND role = \'L\'', 'bind' => array(1 => $manager->project_id)));
$file_name = $excelExport->LeaderExport($result, $manager->project_id);
$this->dataReturn(array('success' => $file_name));
return;
break;
default:
$this->dataReturn(array('error' => '参数错误-' . $type));
return;
}
} catch (Exception $e) {
$this->dataReturn(array('error' => '列表生成失败'));
return;
}
}
/**
* Created by PhpStorm.
* User: huzhiming
* Date: 2015/10/9
* Time: 15:44
*/
require_once "../model/ExcelExport.php";
require_once "../tools/SQLTool.class.php";
$list = $_GET['list'];
if (isset($_GET['title'])) {
$title = $_GET['title'];
} else {
$title = "用户请求列表";
}
$sqltool = new SQLTool();
$excel = new ExcelExport();
$cell = array("请求编号", "请求标题", "请求分类", "请求方式", "请求时间", "请求描述", "优先级", "影响范围", "复杂程度", "服务协议", "请求人姓名", "请求人帐号", "请求人单位", "请求人职工号", "请求人职务", "请求人电话", "请求人手机", "请求人邮箱", "工程师", "请求状态", "解决方案");
$sql = "select t_req.req_num, t_req.req_title, t_req.req_sort,t_req.req_source, t_req.req_time, t_req.req_content, req_priority,req_effect,\r\n t_req_assign.req_complex,req_time_limit,t_user.name,t_req.req_author,(select depart_name from t_depart where t_depart.depart_id=t_user.depart_id),t_user.staff_id,t_user.position,t_user.phone,\r\n t_user.telephone,t_user.email,t_req_assign.req_engineer,t_req.req_state,t_req.req_solution\r\n from t_req left outer join t_req_assign on t_req.req_num=t_req_assign.req_num and (finish_flag=1 or finish_flag=2),t_user\r\n\t\twhere t_req.req_author = t_user.user_name and t_req.req_num in {$list} order by req_time desc";
$res = $sqltool->dbQuery($sql);
for ($i = 0; $i < count($res); $i++) {
switch ($res[$i][3]) {
case 1:
$res[$i][3] = "网页请求";
break;
case 2:
$res[$i][3] = "电话请求";
break;
case 3:
$res[$i][3] = "邮件请求";
break;
}
public function actionIndex()
{
$pageSize = Yii::app()->request->getParam('pageSize', Yii::app()->params['pageSize']);
Yii::app()->user->setState('pageSize', $pageSize);
$model = new AdminSongModel('search');
$copyrightType = Yii::app()->request->getParam('ccp_type', null);
if (isset($_GET['AdminSongModel'])) {
if (isset($_GET['AdminSongModel']['created_time']) && $_GET['AdminSongModel']['created_time'] != "") {
// Re-setAttribute create datetime
$createdTime = $_GET['AdminSongModel']['created_time'];
if (strrpos($createdTime, "-")) {
$createdTime = explode("-", $createdTime);
$fromDate = explode("/", trim($createdTime[0]));
$fromDate = $fromDate[2] . "-" . str_pad($fromDate[0], 2, '0', STR_PAD_LEFT) . "-" . str_pad($fromDate[1], 2, '0', STR_PAD_LEFT);
$fromDate .= " 00:00:00";
$toDate = explode("/", trim($createdTime[1]));
$toDate = $toDate[2] . "-" . str_pad($toDate[0], 2, '0', STR_PAD_LEFT) . "-" . str_pad($toDate[1], 2, '0', STR_PAD_LEFT);
$toDate .= " 23:59:59";
} else {
$fromDate = date("Y-m-d", strtotime($_GET['AdminSongModel']['created_time'])) . " 00:00:00";
$toDate = date("Y-m-d", strtotime($_GET['AdminSongModel']['created_time'])) . " 23:59:59";
}
}
}
$is_composer = "";
if (isset($_GET['is_composer']) && $_GET['is_composer'] > 0) {
$is_composer = $_GET['is_composer'];
}
$categoryList = AdminGenreModel::model()->gettreelist(2);
$cpList = AdminCpModel::model()->findAll();
if (isset($_GET['AdminSongModel'])) {
$limit = 10;
$offset = 0;
$where = "";
if ($_GET['AdminSongModel']['cp_id'] != '') {
$where .= " and t.cp_id = " . $_GET['AdminSongModel']['cp_id'] . " ";
}
if ($_GET['AdminSongModel']['genre_id'] != '') {
$where .= " and sg.genre_id = " . $_GET['AdminSongModel']['genre_id'] . " ";
}
if ($_GET['AdminSongModel']['name'] != '') {
$where .= " and t.name LIKE '%" . $_GET['AdminSongModel']['name'] . "%' ";
}
if ($_GET['AdminSongModel']['artist_name'] != '') {
$where .= " and t.artist_name LIKE '%" . $_GET['AdminSongModel']['artist_name'] . "%' ";
}
if ($_GET['is_composer'] == 1 || $_GET['is_composer'] == 2) {
if ($_GET['is_composer'] == 1) {
$where .= " and t.composer_id > 0 ";
} else {
$where .= " and t.composer_id = 0 ";
}
}
if ($_GET['AdminSongModel']['max_bitrate'] != '') {
$where .= " and t.max_bitrate = " . $_GET['AdminSongModel']['max_bitrate'];
}
if ($_GET['ccp_type'] != '') {
$where .= " and sc.type = " . $_GET['ccp_type'];
}
if ($_GET['AdminSongModel']['created_time'] != '') {
$where .= " and t.created_time BETWEEN '{$fromDate}' AND '{$toDate}' ";
}
$sql = "SELECT count(DISTINCT `t`.`id`)\n\t\t\t\t\tFROM song t \n\t\t\t\t\tINNER JOIN song_status st ON t.id = st.song_id\n\t\t\t\t\tLEFT JOIN artist a ON a.id=t.composer_id\n\t\t\t\t\tLEFT JOIN song_genre sg ON sg.song_id=t.id\n\t\t\t\t\tLEFT JOIN song_copyright sc ON sc.song_id=t.id\n\t\t\t\t\tLEFT JOIN copyright c ON c.id=sc.copryright_id\n\t\t\t\t\tWHERE t.status=1 {$where}\n\t\t\t\t\t";
$count = Yii::app()->db->createCommand($sql)->queryScalar();
$perPage = 10000;
if ($count <= $perPage) {
$numPage = 1;
} elseif ($count % $perPage == 0) {
$numPage = $count / $perPage;
} else {
$numPage = $count / $perPage + 1;
$numPage = (int) $numPage;
}
if (isset($_GET['export'])) {
$page = Yii::app()->request->getParam('page', 1);
$limit = $perPage;
$offset = ($page - 1) * $limit;
$sql = "SELECT t.id, t.name, t.artist_name, a.name as composer_name, t.composer_id, sg.genre_name, sc.copryright_id as copyright_id,c.appendix_no,c.contract_no, cp.name as cc, '' as ccc \n\t\t\t\tFROM song t\n\t\t\t\tINNER JOIN song_status st ON t.id = st.song_id\n\t\t\t\tLEFT JOIN cp ON t.cp_id = cp.id\n\t\t\t\tLEFT JOIN artist a ON a.id=t.composer_id\n\t\t\t\tLEFT JOIN song_genre sg ON sg.song_id=t.id\n\t\t\t\tLEFT JOIN song_copyright sc ON sc.song_id=t.id\n\t\t\t\tLEFT JOIN copyright c ON c.id=sc.copryright_id\n\t\t\t\tWHERE t.status=1 {$where}\n\t\t\t\tGROUP BY t.id\n\t\t\t\tORDER BY t.id ASC\n\t\t\t\tLIMIT {$limit}\n\t\t\t\tOFFSET {$offset}\n\t\t\t\t";
$data = Yii::app()->db->createCommand($sql)->queryAll();
$label = array('id' => 'ID', 'name' => 'Bài Hát', 'artist_name' => 'Ca sỹ', 'composer_name' => 'Nhạc sỹ', 'composer_id' => 'composer_id', 'contract_no' => 'Số hợp đồng', 'cc' => 'Tên CP, Ca sĩ, đại diện HĐ', 'appendix_no' => 'Số phụ lục', 'ccc' => 'STTPL', 'copyright_id' => 'ID Hợp đồng');
$title = Yii::t('admin', 'Export_' . $page);
$excelObj = new ExcelExport($data, $label, $title);
$excelObj->export();
exit;
}
}
$this->render('index', array('model' => $model, 'categoryList' => $categoryList, 'cpList' => $cpList, 'pageSize' => $pageSize, 'is_composer' => $is_composer, 'copyrightType' => $copyrightType, 'numPage' => $numPage, 'count' => $count));
}
<?php
/**
* Created by PhpStorm.
* User: huzhiming
* Date: 2015/10/8
* Time: 22:14
*/
require_once "ExcelExport.php";
$excel = new ExcelExport();
$cell = array("姓名", "电话", "手机号");
$value = array(array("张三", "122334<a></a>", NULL), array("李四", "23908u0", "3345354343345"), array("李四", "23908u0", "3345354343345"), array("李四", "23908u0", "3345354343345"), array("李四", "23908u0", "3345354343345"), array("李四", "23908u0", "3345354343345"), array("李四", "23908u0", "3345354343345"), array("李四", "23908u0", "3345354343345"));
$excel->setCellTitle($cell, "学生通信录");
$excel->setCellValue($value);
$excel->export();
