雕虫小技 Small Tricks

official site: Google GSuite, click to have a look and have a 7 days free trial

it is the cheapest on the market, compare with Microsoft domain email, QQ email, 163 email
google always offer you the best product experience

here are some tips of using GSuite:

for example, my domain is meesilk.com, so I want to take the $5 per user per month plan, and I intend to create only one user account for $5 per month, at the same time create multiple domain email addresses like admin@meesilk.com, support@meesilk.com, etc,  here is how and some resolution for the issues you may encounter:

Domain ownership verification failed

if you failed to verify your domain owership when you click next next following the register steps, then you can try other ways like adding headers to the index.html file on your hosts, there are many ways to do, check on the link https://support.google.com/a/answer/1071480?hl=en

Add an email alias in G Suite (up to 30 alias)



during the steps popup for you, set the Reply-to address if you want to specify it

Send emails from a different address or alias


now you can enjoy with your domain email, choose sender by selecting from the address list

Cancel your business email



special promo code for GSuite

G Suite Basic Plan


G Suite Business Plan


Each promotion code provides 20% off the first year on either G Suite Basic plan or G Suite Business plan*.





today I identified an issue when install OneBase.org php framework( which is based on Thinkphp 5.0)

the root cause is callling of php internal function parse_url, when your password includes special character like #, the parse_url function wrongly split the string,

work around is by adding a / at the tail of $url

small issue, but may waste plenty of time investigating it because of layering and encapsulation


very simple solution :

click on max/average whatever column you want to sort, click it once or twice until the result is sorted in your expected order, then save, so next time when you open it, it will display in expected sort order,

actually what happens is the grafana json file will change to for example:

“legend”: {
“alignAsTable”: true,
“avg”: true,
“current”: true,
“max”: true,
“min”: false,
“rightSide”: true,
“show”: true,
“sideWidth”: 300,
“sort”: “max”,
“sortDesc”: true,
“total”: true,
“values”: true

ppl asking and discussing it here




tips: copy and paste as value/formula/transpose(row to column, column to row)

Sheet and cell: =Sheet1!$A$2:$A$8

1.How to convert column list to comma separated list in Excel?

Sub ChangeRange()
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
outStr = ""
For Each rng In InputRng
If outStr = "" Then
outStr = rng.Value
outStr = outStr & "," & rng.Value
End If
OutRng.Value = outStr
End Sub

2.How to quickly apply formula to an entire column or row with/without dragging in Excel?
here I recommend copy the formula and then select a starting cell and scroll to the last cell you want to apply and hold down shift+select the last cell, paste,
because for excel’s nature of infinite scrolling in vertical direction, if you do fill down to apply formula, it may apply to lots of empty cells you don’t want to apply to, that will cost high memory usage, and in some case excel may get crashed.
How to remove prefix / suffix from multiple cells in Excel?
3.Use AutoSum to sum numbers
4.Find mismatch value comparing two column


6. Vlookup
7. Match Index
quickly find corresponding values based on comparing the other columns.
8.Transpose/convert multi column to single column
column to table: http://www.cpearson.com/EXCEL/ColumnToTable.aspx
Scatter Chart – Format Data Label – Label options – select Range
How to use SUMIF in Excel – formula examples to conditionally sum cells https://www.ablebits.com/office-addins-blog/2014/11/04/excel-sumif-function-formula-examples/
10. Group and Subtotal
Data-> subtotal
11. value (long number string or date time) as Text
method 1: import as csv, select the column, change the type to text
method 2: Text()
12 group by sum
=IF(A2=A1,””,SUMIF(A:A,A2,B:B)), (A2 is the relative cell you want to sum based on, A1 is the column header, A:A is the column you want to sum based on, the B:B is the column you want to sum the values.) Press Enter key, drag fill handle down to the cells to fill the formula.


#to prevent possible loss of data excel cannot shift non-blank cells off of the worksheet
remove the formats
#before copy paste, make sure that you want to copy the function or value only, otherwise excel by default will apply functions if you have to all cells you paste to, that will cost much longer time and more memory
#stop excel converting the value
1). Change the file extension from .csv to .dif
2). Double click on the file to open it in Excel.
3). The ‘File Import Wizard’ will be launched.
4). Set the ‘File type’ to ‘Delimited’ and click on the ‘Next’ button.
5). Under Delimiters, tick ‘Comma’ and click on the ‘Next’ button.
6). Click on each column of your data that is displayed and select a ‘Column data format’. The column with the numeric value should be formatted as ‘Text’.
7). Click on the finish button, the file will be opened by Excel with the formats that you have specified.
#whitespace, trim not work ?
remove stubborn spaces and characters in a cell
DATA->Text to Columns
Used Script:
CONCATENATE(“exec somefunction(‘”,A2,”‘,”,(0-F2),”,”,C2,”);”)

1. cannot store into flash drive even the file size within its available space limits, try to format it to NTFS


2.boot from usb stick: cannot find usb drive in boot menu, it could be that your usb stick is not marked as active.

follow steps below to activate your flash drive



Administrative Tools

System Information

window partition – disk manager

ubuntu partition – use another linux system to do it ( because you cannot umount the disk when use let alone partition it)


SSH auto close – try admin, or remote server cannot connected ( try other machine out of your current intranet)


cannot find the menu?

try tab for computer

try hardware/software menu button or home button


google chrome freezes my laptop

1. Try clearing your cache and cookies (Ctrl+Shift+Backspace). Start by clearing from the time period you started facing the issue, then expand to “beginning of time”.
2. Uncheck Use hardware acceleration (Menu > Settings > Show Advanced Settings > System menu).


Crack Process demonstrated by GIF:

crack ** site reg location by LIU YUE
crack ** site reg location by LIU YUE


document.domain = "**.com";
var imwebReg = {ver: 10016,inited: !1,isSeaPhone: $("imweb_phoneArea") ? !0 : !1,switchToSeaPhone: !1,cellphone: "",checkPhoneMap: {},phoneCheck_clock: 0,defaultValue: {countryId: "1",provinceId: "11",cityId: "1",country: "China",province: "Beijing",city: "Dongcheng",yearType: "0",year: "0",month: "0",day: "0"},key:
setTimeout(function() {
$.http.loadScript(imwebReg.url.ver + "?v=" + Math.random())
}, 1E3);
document.body.onmouseover = function() {
document.body.onmouseover = null
function initLocation(a) {



,MV:{n:"Masvingo"},MN:{n:"Matabeleland North"},MS:{n:"Matabeleland South"},MD:{n:"Midlands"}}}});


I decide to export all location data as sql script

first, get json data:

tencent registration

tencent registration

convert string and json format might be tricky, remember replace double quotes with \” before conversion.

by digging into data structure,
Here is my code snippets to generate sql script from the data

$.each(countryList, function(key, country) {

if (country.hasOwnProperty('n')&&country['n']!=null&&country['n']!=undefined&&country['n']!='') {
var countryCode='NULL';
var provinceCode='NULL';
var cityCode='NULL';
var countryName='NULL';
var provinceName='NULL';
var cityName='NULL';

console.log("insert into locationEN(countryCode,provinceCode,cityCode,country,province,city) values("+countryCode+","+provinceCode+","+cityCode+","+countryName+","+provinceName+","+cityName+")");
console.log("insert into locationEN(countryCode,provinceCode,cityCode,country,province,city) values("+countryCode+","+provinceCode+","+cityCode+","+countryName+","+provinceName+","+cityName+")");

jquery template usage examples:

Simple data: List

inline if else:

If else:

Render html tag and using js function in template

nested loop and complicate operations:

@model List

Course Catalogue

@section scripts{



emotion source(partial):


download icons to local using c# webclient:
The DownloadFileAsync/DownloadFileCompleted members of WebClient use the Event-based Asynchronous Pattern. If you want to use async and await, you should be using the Task-based Asynchronous Pattern.
In this case, you should use the DownloadFileTaskAsync member, as such(Please note that your Context.listOfLocalDirectories.Add and Errors.printError methods should be threadsafe.):

private async Task DownloadFileAsync(DocumentObject doc)
using (WebClient webClient = new WebClient())
string downloadToDirectory = @Resources.defaultDirectory + value.docName;
webClient.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
await webClient.DownloadFileTaskAsync(new Uri(value.docUrl), @downloadToDirectory);

//Add them to the local
catch (Exception)
Errors.printError("Failed to download File: " + value.docName);

private async Task DownloadMultipleFilesAsync(List doclist)
await Task.WhenAll(doclist.Select(doc => DownloadFileAsync(doc)));


* Doc emotions
* @author LIU YUE

//customize hashtable
function Hashtable() {
this._hash = new Object();
this.put = function(key, value) {
if (typeof (key) != "undefined") {
if (this.containsKey(key) == false) {
this._hash[key] = typeof (value) == "undefined" ? null : value;
return true;
} else {
return false;
} else {
return false;
this.remove = function(key) { delete this._hash[key]; }
this.size = function() { var i = 0; for (var k in this._hash) { i++; } return i; }
this.get = function(key) { return this._hash[key]; }
this.containsKey = function(key) { return typeof (this._hash[key]) != "undefined"; }
this.clear = function() { for (var k in this._hash) { delete this._hash[k]; } }

var emotions = new Array();
var categorys = new Array();
var uPeterEmotionsHt = new Hashtable();

$(function () {
var emotionsource="/content/js/" + "emotion_source.js"+ "?v=" + Math.random();
setTimeout(function () { $.getScript(emotionsource)}, 1E3);

function AnalyticEmotion(s) {
if(typeof (s) != "undefined") {
var sArr = s.match(/\[.*?\]/g);
if (sArr != null) {
for (var i = 0; i < sArr.length; i++) { if (uPeterEmotionsHt.containsKey(sArr[i])) { var src = "/content/image/" + uPeterEmotionsHt.get(sArr[i]); var reStr = "";
s = s.replace(sArr[i], reStr);
return s;

$.fn.PeterEmotion = function(target){
var cat_current;
var cat_page;

var eTop = target.offset().top + target.height() + 15;
var eLeft = target.offset().left - 1;

if($('#emotions .categorys')[0]){
$('#emotions').css({top: eTop, left: eLeft});

$('#emotions').css({top: eTop, left: eLeft});




$('#emotions #prev').click(function(){
showCategorys(cat_page - 1);
$('#emotions #next').click(function(){
showCategorys(cat_page + 1);

$.fn.insertText = function(text){
this.each(function() {
if(this.tagName !== 'INPUT' && this.tagName !== 'TEXTAREA') {return;}
if (document.selection) {
var cr = document.selection.createRange();
cr.text = text;
}else if (this.selectionStart || this.selectionStart == '0') {
start = this.selectionStart,
end = this.selectionEnd;
this.value = this.value.substring(0, start)+ text+ this.value.substring(end, this.value.length);
this.selectionStart = this.selectionEnd = start+text.length;
}else {
this.value += text;
return this;
function showCategorys(){
var page = arguments[0]?arguments[0]:0;
if(page < 0 || page >= categorys.length / 5){
$('#emotions .categorys').html('');
cat_page = page;
for(var i = page * 5; i < (page + 1) * 5 && i < categorys.length; ++i){ $('#emotions .categorys').append($('' + categorys[i] + ''));
$('#emotions .categorys a').click(function(){
$('#emotions .categorys a').each(function(){
if($(this).text() == cat_current){
function showEmotions(){
var category = arguments[0]?arguments[0]:'默认';
var page = arguments[1]?arguments[1] - 1:0;
$('#emotions .container').html('');
$('#emotions .page').html('');
cat_current = category;
var path = "/content/image/";
for(var i = page * 72; i < (page + 1) * 72 && i < emotions[category].length; ++i){ $('#emotions .container').append($('' + emotions[category][i].name + ''));
$('#emotions .container a').click(function(){
for(var i = 1; i < emotions[category].length / 72 + 1; ++i){ $('#emotions .page').append($('' + i + ''));
$('#emotions .page a').click(function(){
showEmotions(category, $(this).text());
$('#emotions .categorys a.current').removeClass('current');
$('#emotions .categorys a').each(function(){
if($(this).text() == category){

function loadSource(data) {
for (var i in data) {
if (data[i].category == '') {
data[i].category = '默认';
if (emotions[data[i].category] == undefined) {
emotions[data[i].category] = new Array();
name: data[i].phrase,
icon: data[i].icon
uPeterEmotionsHt.put(data[i].phrase, data[i].icon);


My lecturer assigned us a continuous assignments, I found a note says ” The Phonenix – WYYB -93.7 FM was a ‘real’ radio station, and the web-site shown in the screen shots in Appendix A did exist until a few years ago. However, all other aspects of this document are fictional”.

At first I tried wyyb.com, only shows domain for sale and along with some ads.

Then I started wondering maybe there is an chance I can find these web pages, although this website already shut down for a long time, I believe the cache still be stored somewhere, from stackoverflow there is one method:
http://webcache.googleusercontent.com/search?q=cache:<your url without “http://”>
but I found they only show me the latest cache which shows domain for sale also.

searching further I found this website which is better cachedview


select Archive.org Cache, it will take you to :


all dates when the site being cached are highlighted on this calendar, all yours, play time!


— http://web-archive-me.com/



背景:1年多前在新加坡PC SHOW的Best denki购买了Dell Alienware M14X-R2,pre-install win8 core edition

昨天下午开始,win8突然popup全屏的激活提示窗口,PC SETTING – GO ACTIVATE,我纳闷的点了进去,页面上两个按钮,ACTIVATE 和 ENTER KEY,我点了下activate,结果‘activate failed, try again later’,Error Code 0xC004F074 – No KMS Service is contacted,KMS(KEY MANAGEMENT SERVICE),这么不友好的提示信息,我就百度谷歌了下ksm激活,找到了很多2012年的帖子,先说一下基本命令:

[box title=”slmgr “]
slmgr /ipk x-x-x-x-x 安装产品密钥,也可以说是替换现有密钥,就是那个5x5的序列号product key。
slmgr /skms x.x.x.x 设置KMS服务器IP地址及端口。
slmgr /ato 尝试在线激活Windows。
slmgr.vbs -dlv 显示详细的激活信息,包括:系统版本、激活ID、安装ID、部分产品密钥、许可证状态、剩余 Windows 重置计数等。
slmgr.vbs -xpr 查看当前许可证状态的截止日期,同样我们可以用它来检查Windows是否是永久激活
slmgr.vbs -cpky 从注册表中清除产品密钥信息,这是一项安全举措,清除之后那些声称读取产品密钥的软件就读不到了。
slmgr.vbs -ilc 导入OEM证书,后面为OEM证书的路径。
slmgr.vbs -upk 卸载当前产品密钥,重启电脑之后系统将重归未激活状态,会出现输入密钥和联网激活界面


首先我们要有product key,可以用product key viewer[下载]查看本机的product key,然后还有一个重点是重设KMS服务器IP地址和端口,这个我在网上找了好几个ip都失败了,提示‘KMS can’t contact*****’,(事后我找到了几个2014年最新的WIN8和win8.1 KMS激活工具,声明我没用这种方法,所以不知道效果,需要可以联系我)
我又尝试了下输入我通过那个product key查看工具获取的本机product key,同样无效,提示‘this product key not work’

我折腾了半天,突然想了下,我的机器本身预装的就是正版系统,然后我是购买不久后升级到了win8.1(free upgrade),没理由1年后提示我又要激活!!!怀着疑问,我电话求助best denki售后,新加坡的售后服务比不上天朝,个人观点,这边大多人对工作抱怨很多,没有像天朝即使拿着几千块工资一样热情高涨的情况,接电话的售后也算还好,告诉我去联系dell,关于windows技术问题他们也不懂,我搜索了下,在dell官网新加坡site只看到了1开头的一组号码,都是美国的,新加坡太小,客服都由老美包办了,打了两个不通,有一个说了半天没有人工,我的机器是全球保修,我立马转向天朝,天朝的一个售后mm接了电话,大概了解情况后,给我说了两个方法:1.还原系统回到win8 2.打电话给微软电话激活
我先用了2,mm给的是国内的微软服务中心4008203800,然后对方告诉我联系微软电话激活4008301832,对方告诉我win+r输入 slui 4 的命令,结果弹出‘cannot activate windows by phone’,这个帅哥也不懂为啥,让我打回先前的电话,沟通了一番,他们给我找到了新加坡的微软服务中心(8008523543 60377124671),我电话过去,对方说 are you calling from Singapore?我才知道原来也不是新加坡的,估计不是美国就是马来西亚,这个aunty让我同样输入slui 4的命令,我告诉她做过了,然后她让我去激活页面找一个电话激活的一个序列号,我告诉她激活页面根本没有那个option,只有两个按钮,aunty恍然大悟’oh, i c, you need to go back to your oem, you ask them to activate your machine with oem production key******’,我告诉她我刚在网上用一个软件找到了oem product key,她告诉我’i understand you have the key,but you need to ask the oem activate it as administrator.’,我立即又联系best denki和dell,各种不爽之后,我决定尝试一下自己的想法,最终证明我是对的!

刚才通话时我提到oem的product key是用这两个软件查到的:
AIDA64 Extreme Edition(至尊版):
AIDA64 Business Edition(商业版):

AIDA64 支持Win8.1正式版
AIDA64 支持Win8.1正式版

最后发现解决问题的还是最初我发现的这个帖子Windows 8.1 Activation – Error Code 0xC004F074 – No KMS Service is contacted,只是当时我没有这么去做,还好那个好心的aunty告诉我这个版本是oem版本,所以我才确定直接用oem product key激活(在windows激活页面选择‘enter key’更换密钥),等待了十几秒,windows结果‘you done the activation’,赶紧去system查看,‘windows is activated’,如释重负,不用重装系统了,不用还原了,我的机器可是装备了各式武器,小至av制作等瑞士军刀(笑,各种视频图片处理专业软件)大到开发级重量武器,重装一遍很痛苦。


总结,系统软件问题还真指望不上所谓的技术支持,微软中国美国新加坡的客服中心以及dell售后的知识储备和岗位培训严重滞后!不能责怪best denki 他们也是一无所知,只知道卖产品!!

Obtaining your OEM BIOS Embedded SLP Product Key using RW-Everything (Windows 8.0/8.1 OEM Only)
Slmgr.vbs Options
bestdenki singapore

How to troubleshoot the Key Management Service (KMS)

Windows 8超简单激活 – KMS傻瓜式激活,亲测完美!
预装Win8/8.1电脑重装系统利用电脑内置密钥自动激活操作注意事项 v2.1
KMSpico 10.0.4 | 知名KMS激活神器


0 1344
Although this Articles simply collects and gathers links for all front-end developer's or designer's reference, but it's really important to introduce or remind us...