[Excel VBA] Generate QR Code (1)

 Excel   VBA    QR Code  

▌Introduction

---

我在此篇文章將利用Excel的VBA環境，將儲存格內容傳送到 google charts api 以產生QR Code圖片，並從http response將此圖片透過本機站存檔的方式再加入到Excel內容中。

▋相關文章

[Excel VBA] Generate QR Code (1)

[Excel VBA] Generate QR Code (2)

▌Google Charts Api

---

請至 developers.google.com 查看更多API的參數和使用方式。

                 

▌Implement

---

▋加入按鈕和資料

請從【開發人員】→【插入】，加入一個ActiveX控制項按鈕。

然後在第一個儲存格放入要轉成QR Code的資料。

▋撰寫按鈕Click事件

Private Sub QRCodeGen_Click()     Dim qrcodeImgPath As String     Dim qrcodeValue As String     qrcodeImgPath = ActiveWorkbook.Path & "\QRCode.png"  'QR Code暫存圖片名稱     qrcodeValue = Cells(1, 1).value     'Create QR Code image     Call getQRCodeImg(qrcodeImgPath, qrcodeValue)     'Append QR Code image to sheet     Call appendQRCode(qrcodeImgPath) End Sub

PS. ActiveWorkbook.Path 會取得目前的Excel所在的資料夾路徑。

▋送出http Request給Google Charts API，並儲存產生的QR Code至本機

Private Sub getQRCodeImg(imgPath As String, value As String)     Dim fileNum As Long     Dim apiUri As String     Dim fileData() As Byte     Dim tmpImgPath As String     Dim winHttpReq As Object     Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")     apiUri = "https://chart.googleapis.com/chart?cht=qr&chs=130x130&chl=" + value     winHttpReq.Open "GET", apiUri, False     winHttpReq.Send     fileData = winHttpReq.ResponseBody     Open imgPath For Binary Access Write As #1     Put #1, 1, fileData     Close #1 End Sub

▋將QR Code圖片放到Excel儲存格

Private Sub appendQRCode(qrcodeImgPath As String)     Dim img As Picture     Set img = ActiveSheet.Pictures.Insert(qrcodeImgPath)     With img         .ShapeRange.LockAspectRatio = msoFalse         .Top = ActiveSheet.Cells(2, 1).Top         .Left = ActiveSheet.Cells(2, 1).Left     End With End Sub

▌Demo

---

▌Reference

---

▋Stackoverflow : Get picture as http GET response and insert to spreadsheet without saving the picture

▋Stackoverflow : How to get current working directory using vba?

[jquery] deferred and promise

 jquery   deferred    promise  

▌Introduction

---

This article will shows the basic usage of deferred and promise in jquery.

▌Sample code

---

Codepen

▌Implement

---

▋deferred and promise

function getData(filter) {     var deferred = $.Deferred();     try {         //Do ajax calls         deferred.resolve(data);     }     catch (e) {         deferred.reject(e);     }     return deferred.promise(); }

getData().then((data) => {     //callback… }).fail(e => { console.log(e); });

▋Use $.when to deal with promises

$.when(getData(filter1), getData(filter)).done((data1, data2) => {     //callback… }).fail((e) => {     console.log(e); });

▌Reference

---

▋jQuery.Deferred()

▋deferred.promise()

▋deferred.resolve()

▋deferred.reject()

▋jQuery.when()

[jquery] jqGrid local data sample

 jquery    jqGrid    rxjs  

▌Introduction

---

jqGrid is a powerful jquery grid plugin. In this article, I will create a local data sample for the starter of jqGrid.

▌Environment

---

l   jQuery 3.1.1

l   jqGrid 4.6.0

▌Sample codes

---

Codepen

▌Implement

---

▋Install packages

jquery

jqGrid

jqueryUI

▋HTML

<div>     <table id="myGrid"></table> </div> <div id="pager"></div>

▋JS

var base = []; $(function () {     $("#myGrid").jqGrid({         //Display Column Titles         colNames: ["Id", "Month", "Income", "Payout", "Payout(%)"],         //model name and settings         colModel: [             { name: "id", hidden: true },             { name: "month", width: 130 },             {                 name: "income",                 editable: true,                 summaryTpl: "{0}",                 summaryType: function (value, name, record) {                     value = value || 0;                     var fieldData = parseFloat(record[name]);                     return (+value + +fieldData).toFixed(1);                 }             },             {                 name: "payout",                 editable: true,                 summaryTpl: "{0}",                 summaryType: function (value, name, record) {                     value = value || 0;                     var fieldData = parseFloat(record[name]);                     return (+value + +fieldData).toFixed(1);                 }             },             {                 name: "payoutPercentage",                 formatter: "currency",                 formatoptions: { suffix: " %" }             }         ],         pager: "#pager",         autowidth: true,         multiselect: false,         onSelectRow: editRow,         datatype: "local",         grouping: true,         groupingView: {             groupField: ["month"],             groupColumnShow: [true],             groupText: ["<b>{0}</b>"],             //groupOrder: ["asc"],             groupSummary: [true],             groupCollapse: false         },         loadComplete: function () {             //Read all rows from jqGrid             //var rows = grid.jqGrid('getGridParam', 'data'); //var rows = grid.getGridParam('data');             let grid = $("#myGrid");             let indexes = grid.jqGrid("getGridParam", "_index");             let rowid;             for (rowid in indexes) {                 if (indexes.hasOwnProperty(rowid)) {                     console.log(grid.jqGrid("getCell", rowid, "id"));                 }             }         }     });     //Start main codes here .....     createBase();     updateBase();     renderGrid(base).then(() => {         //Do something...     }); });

u  Use summaryTpl and summaryType on any of colModel to create a summary cell on footer.

Simply set summaryType:"sum" or use the custom summary function like the above codes.

u  grouping and groupingView are used for grouping.

▋Create local data for jqGrid

function createBase() {     base = JSON.parse(JSON.stringify(jsonData)); //Deep clone a new array } /* JSON foo data */ let jsonData = [     {         id: 1,         month: "2017/03",         income: 1234.55,         payout: 234.46     }, … ];

▋Update the local data (optional)

function updateBase() {     base.forEach(x => {         x.payoutPercentage = (x.payout / x.income * 100).toFixed(2);     }); }

▋Render local data to jqGrid

function renderGrid(data) {     var deferred = $.Deferred();     var grid = $("#myGrid");     grid         .jqGrid("setGridParam", {             datatype: "local",             data: data         })         .trigger("reloadGrid");     $("#myGrid").jqGrid("navGrid", "#pager", {         edit: false,         add: false,         del: false,         search: false,         refresh: false,         view: true     });     deferred.resolve();     return deferred.promise(); }

▋Demo

▌Reference

---

GitHub - tonytomov/jqGrid: jQuery grid plugin

[Windows cmd] Change default code page

 CMD    Windows command  

▌Change code page

---

Find all code pages on Wikipedia

Type the command as following to change the code page to 65001(UTF-8 Unicode).

chcp 65001

▌Default code page

---

1.Open RUN (Hotkeys: Win+R) and type regedit to open register.

2.Go to [HKEY_LOCAL_MACHINE\Software\Microsoft\Command Processor]

3.Add new key and value:
Autorun: chcp  65001

▌Reference

---

▋Stackoverflow: Change default code page of Windows console to UTF-8