学习笔记 – 第 16 页

MySQL 修改已存在的数据表的自增 id 重置自增 id

24 10 月, 201802 12 月, 2018 学习笔记

ALTER TABLE 表名 AUTO_INCREMENT = 设定的值

例：

mysql> alter table oa_salary_branch_month auto_increment =  1;

oa_salary_branch_month 是表名，不需要字段

PHPExcel 超过 26 列解决方法

24 10 月, 201802 12 月, 2018 学习笔记

ThinkPHP 3.2 使用 PHPExcel

使用 chr(ord('A')++) 的方法进行 Excel 列的自增，当列数超过26列时，会报如下错误

Invalid cell coordinate [1
错误位置
FILE: E:\wwwroot\wanaioa.a.cc\ThinkPHP\Library\Vendor\Excel\PHPExcel\Cell.php 　LINE: 539

原因是 ord('Z') 为 90，当 chr(91) 时为 string(1) "["

但在 Excel 中应为 AA

解决方法是使用 PHPExcel 自带函数 stringFromColumnIndex

调用方法

\PHPExcel_Cell::stringFromColumnIndex(27);

源码为

/**
 *	String from columnindex
 *
 *	@param	int $pColumnIndex Column index (base 0 !!!)
 *	@return	string
 */
public static function stringFromColumnIndex($pColumnIndex = 0)
{
	//	Using a lookup cache adds a slight memory overhead, but boosts speed
	//	caching using a static within the method is faster than a class static,
	//		though it's additional memory overhead
	static $_indexCache = array();

	if (!isset($_indexCache[$pColumnIndex])) {
		// Determine column string
		if ($pColumnIndex < 26) {
			$_indexCache[$pColumnIndex] = chr(65 + $pColumnIndex);
		} elseif ($pColumnIndex < 702) {
			$_indexCache[$pColumnIndex] = chr(64 + ($pColumnIndex / 26)) .
										  chr(65 + $pColumnIndex % 26);
		} else {
			$_indexCache[$pColumnIndex] = chr(64 + (($pColumnIndex - 26) / 676)) .
										  chr(65 + ((($pColumnIndex - 26) % 676) / 26)) .
										  chr(65 + $pColumnIndex % 26);
		}
	}
	return $_indexCache[$pColumnIndex];
}

路径

ThinkPHP/Library/Vendor/Excel/PHPExcel/Cell.php

封装一个 PHPExcel 的导出方法

23 10 月, 201830 1 月, 2019 学习笔记

本篇以 ThinkPHP 为示例

public function getExcel($property,$headArr,$data){

	Vendor('Excel.PHPExcel');
	$objPHPExcel = new \PHPExcel();
	$objPHPExcel
		-> getProperties()
		-> setCreator($property['creator'])
		-> setLastModifiedBy($property['last'])
		-> setTitle($property['title'])
		-> setSubject($property['subject'])
		-> setDescription($property['description'])
		-> setKeywords($property['keywords'])
		-> setCategory($property['category']);

	$column = 1;
	$span = ord("A");
	foreach($headArr as $v){
		$j = chr($span);
		$objPHPExcel->setActiveSheetIndex(0) -> setCellValue($j.$column,$v);
		$span ++;
	}

	$column ++;
	$objActSheet = $objPHPExcel->getActiveSheet();
	foreach($data as $key => $rows){
		$span = ord('A');
		foreach($rows as $keyName => $value){
			$j = chr($span);
			$objActSheet->setCellValue($j.$column, $value);
			$span ++;
		}
		$column ++;
	}

	$objPHPExcel->setActiveSheetIndex(0);

	ob_end_clean();
	ob_start();

	header("Content-Type: application/force-download");
	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header("Content-Disposition:attachment;filename =" . str_ireplace('+', '%20', URLEncode($fileName)).'.xlsx');
	header('Cache-Control: max-age=0');

	$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter -> save('php://output');
	exit ;
}

调用

public function _index_export(){
	$property = array('title'=>'测试');
	$headArr = array('好','坏');
	$data = array(
		array(
			3,4
		),
		array(
			5,6
		)
	);
	$this->getExcel($property,$headArr,$data);
}

优化当超过 26 列

public function getExcel($property,$headArr,$data){

	Vendor('Excel.PHPExcel');
	$objPHPExcel = new \PHPExcel();
	$objPHPExcel
		-> getProperties()
		-> setCreator($property['creator'])
		-> setLastModifiedBy($property['last'])
		-> setTitle($property['title'])
		-> setSubject($property['subject'])
		-> setDescription($property['description'])
		-> setKeywords($property['keywords'])
		-> setCategory($property['category']);

	$column = 1;
	$span = 0;
	foreach($headArr as $v){
		$letter = \PHPExcel_Cell::stringFromColumnIndex($span);
		$objPHPExcel->setActiveSheetIndex(0) -> setCellValue($letter.$column,$v);
		$span ++;
	}

	$objActSheet = $objPHPExcel->getActiveSheet();
	foreach($data as $key => $rows){
		$column ++;
		$span = 0;
		foreach($rows as $keyName => $value){
			$letter = \PHPExcel_Cell::stringFromColumnIndex($span);
			$objActSheet->setCellValue($letter.$column, $value);
			$span ++;
		}
	}

	$objPHPExcel->setActiveSheetIndex(0);

	ob_end_clean();
	ob_start();

	header("Content-Type: application/force-download");
	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header("Content-Disposition:attachment;filename =" . str_ireplace('+', '%20', URLEncode($property['title'])).'.xlsx');
	header('Cache-Control: max-age=0');

	$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter -> save('php://output');
	exit ;
}

可以设置列宽的一种方法

public function exportExcel($expTitle,$expCellName,$expTableData){
	$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
	$fileName = $expTitle.date('_Ymd_His');//or $xlsTitle 文件名称可根据自己情况设定
	$cellNum = count($expCellName);
	$dataNum = count($expTableData);
	Vendor('Excel.PHPExcel');
	$objPHPExcel = new \PHPExcel();
	$cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
	// $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
	// $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));//第一行标题
	for($i=0;$i<$cellNum;$i++){
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'1', $expCellName[$i][1]);
		$objPHPExcel->getActiveSheet()->getColumnDimension($cellName[$i])->setWidth($expCellName[$i][2]); //设置宽度
	}
	// Miscellaneous glyphs, UTF-8
	for($i=0;$i<$dataNum;$i++){
		for($j=0;$j<$cellNum;$j++){
			$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]]);
		}
	}
	header('pragma:public');
	header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
	header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
	$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter->save('php://output');
	exit;
}

调用

public function dumpLogsExcel(){
	//查询数据
	$where['member_card'] = 123456;
	$xlsData = array();//二维数组
	//构建excel
	$xlsName  = "用户评价信息表";
	$xlsCell  = array(
		array('GongSiMc','门店名称',20),
		array('order_num','保养单号',20),
		array('wx_name','微信名',20),
		array('member_card','会员卡号',15),
		array('is_good_name','是否满意',10),
		array('evaluate_item','具体评价',40),
		array('score','得分',10),
		array('create_time','创建时间',20),
	);
	$this->exportExcel($xlsName,$xlsCell,$xlsData);
}

PHP 字符型数字去掉小数点后的零

23 10 月, 201802 12 月, 2018 学习笔记

<?php
    function _floatVal($val){
        if(is_numeric($val)){
            return floatval($val);
        }else{
            return $val;
        }
    }

    $list = array(
        'a'=>'0.00',
        'b'=>'3.00',
        'c'=>'236.30',
        'd'=>'aa',
    );
        
    $list = array_map('_floatVal',$list);
    echo "<pre>";
    var_dump($list);
?>

ThinkPHP 3.2 获取 MySQL 数据库数据表字段的备注信息

22 10 月, 201802 12 月, 2018 学习笔记

//获取备注信息
public function _get_field() {
	$model = new \Think\Model();
	$controller_name = $this->CamelCaseToUnderScore(CONTROLLER_NAME);
	$rs = $model->query("select `column_name`,`column_comment` from information_schema.COLUMNS WHERE TABLE_SCHEMA = '".C('DB_NAME')."' AND TABLE_NAME = '".C('DB_PREFIX').$controller_name."'");
	return $rs;
}

//驼峰命名转下划线命名
public function CamelCaseToUnderScore($str){
	$dstr = preg_replace_callback('/([A-Z]+)/',function($matchs){
	  return '_'.strtolower($matchs[0]);
	},$str);
	return trim(preg_replace('/_{2,}/','_',$dstr),'_');
}

原生 SQL 语句

select * from information_schema.columns where table_schema='db' table_name='user'

魅族 16th Plus 16X 官网提前排队抢购方法测试

17 10 月, 201802 12 月, 2018 学习笔记

SourceTree 跳过注册方法

17 10 月, 201802 12 月, 2018 学习笔记

安装 SourceTree

然后打开本地文件夹

%LocalAppData%\Atlassian\SourceTree\

当前文件夹下创建一个 json 文件，文件名为 accounts.json

accounts Download

[
  {
    "$id": "1",
    "$type": "SourceTree.Api.Host.Identity.Model.IdentityAccount, SourceTree.Api.Host.Identity",
    "Authenticate": true,
    "HostInstance": {
      "$id": "2",
      "$type": "SourceTree.Host.Atlassianaccount.AtlassianAccountInstance, SourceTree.Host.AtlassianAccount",
      "Host": {
        "$id": "3",
        "$type": "SourceTree.Host.Atlassianaccount.AtlassianAccountHost, SourceTree.Host.AtlassianAccount",
        "Id": "atlassian account"
      },
      "BaseUrl": "https://id.atlassian.com/"
    },
    "Credentials": {
      "$id": "4",
      "$type": "SourceTree.Model.BasicAuthCredentials, SourceTree.Api.Account",
      "Username": "",
      "Email": null
    },
    "IsDefault": false
  }
]

重启 SourceTree

SSH 将 Linux 服务器文件拷贝至 Windows 本地

10 10 月, 201816 10 月, 2018 学习笔记

使用 pscp.exe

该程序集成在 PuTTY，安装 PuTTY

https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html

安装完成后即可在命令行里使用 pscp 命令

程序会自动添加环境变量，若找不到命令可手动添加环境变量

然后使用命令（以下示例）

pscp root@198.13.50.216:/root/lnmp-install.log /down/

CentOS 7 修改 yum 源为阿里云

10 10 月, 201816 10 月, 2018 学习笔记

备份本地 yum 源

mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo_bak

获取阿里 yum 源配置文件

wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

清理一下旧包

yum clean all

更新 cache 生成缓存

yum makecache

更新（如果需要）

yum -y update

微信小程序安卓获取设备窗口高度不准确问题

30 9 月, 201816 10 月, 2018 学习笔记

使用 wx.getSystemInfo 获取设备信息的 windowHeight 飘忽不定，导致需要计算的页面布局出现问题

推测的原因是底部状态栏 Bar 与 onLoad 是异步加载，可能状态栏未加载完成就已完成窗口高度的计算。

解决方法：

在 onReady 中调用 wx.getSystemInfo