2017年5月27日 星期六

[Excel VBA] Generate QR Code (2)

 Excel   VBA    QR Code  


▌Introduction

延續上一篇[Excel VBA] Generate QR Code(1)的程式碼，我們在需要產生多個QR Code的需求中，便需要建立一個專用的資料夾來存放不同的QR Code圖片，以讓Excel在每個QR Code的Reference link不至於參考到同一張圖片。

我們將學習以下VBA之應用：
1.  產生多個QR Code
2.  如何操作非工作(Activate)中之WorkSheet儲存格
3.  如何建立資料夾


▋相關文章
[Excel VBA] Generate QR Code (1)
[Excel VBA] Generate QR Code (2)



▌Implement

▋目標

我們將以下每筆資料(共四筆)分別產出一張QR Code並放到另外一張工作表：QR Code。
並且在儲存後下次重新打開，儲存的四張QR Code是正確的。







▋設定工作表及儲存格名稱

請先設定好兩張工作表(WorkSheet)的名稱，然後在QR Code工作表，設定以下儲存格名稱分別為
Agile1, Agile2 ~ Agile4






▋在產生QR Code按鈕的事件程式碼，以迴圈讀取每筆資料




Private Sub QRCodeGen_Click()
    Dim idx As Integer
    For idx = 1 To 4
        genQRcode(idx)
    Next idx
End Sub


▋主程式

Private Sub genQRcode(idx As Integer)
    Dim qrcodeValue As String
    qrcodeValue = ActiveSheet.Cells(idx, 1).value 'QR Code value

    ' Set image path
    Dim qrcodeImgDir As String ' QR Code圖片資料夾位置
    Dim qrcodeImgPath As String ' QR Code圖片位置
    qrcodeImgDir = ActiveWorkbook.Path & "\" & Format(DateTime.Now, "yyyy-MM-dd")
   
    ' Set different image name for every QR Code
    qrcodeImgPath = qrcodeImgDir & "\" & "qrcode" & "_" & idx & Format(DateTime.Now, "hhmmss") & ".png"

    ' Create image folder
    If Dir(qrcodeImgDir, vbDirectory) = "" Then
        createDirectory qrcodeImgDir
    End If

    'Create QR Code image
    Call getQRCodeImg(qrcodeImgPath, qrcodeValue)

    'Set QR Code image to  another WorkSheet
    Dim sheet As Worksheet
    Dim cellName As String
    Dim qrcodeRange As Range
      Set sheet = ActiveWorkbook.Sheets("QR Code") ' Get another WorkSheet
      cellName = "Agile" & idx
      Set qrcodeRange = sheet.Range(cellName)
      Call deleteCell(sheet, qrcodeRange)
    Call appendQRCode(sheet, qrcodeRange, qrcodeImgPath)

End Sub


▋建立存放QR Code圖片的資料夾

Sub createDirectory(directoryPath)
    MkDir directoryPath
End Sub



▋更新函式： 傳入放QR Code的工作表和儲存格

Private Sub appendQRCode(sheet As Worksheet, qrcodeRange As Range, qrcodeImgPath As String)

    Dim img As Picture
    Set img = sheet.Pictures.Insert(qrcodeImgPath)

    With img
        .ShapeRange.LockAspectRatio = msoFalse
        '.Top = ActiveSheet.Cells(33, 10).Top
        '.Left = ActiveSheet.Cells(33, 10).Left
        .Left = qrcodeRange.Left + 5
        .Top = qrcodeRange.Top + 5
    End With
End Sub

Private Sub deleteCell(sheet As Worksheet, curcell As Range)
    Dim sh As Shape
    For Each sh In sheet.Shapes
        If sh.TopLeftCell.Address = curcell.Address Then sh.Delete
    Next
End Sub


▋產生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



▌Demo






▌Reference




張貼者： 於 沒有留言:

2017年5月23日 星期二

[JS] Unit testing with Karma and Jasmine

 karma    Jasmine    gulp  


▌Introduction

This is a simple sample for javascript unit-testing with karma, Jasmine and gulp.

karma :
A simple tool that allows you to execute JavaScript code in multiple real browsers.

Jasmine :
A Behavior Driven Development testing framework for JavaScript.

karma-jasmine :
Adapter for the Jasmine testing framework.



▌Sample codes
Github



▌Implement

▋Install packages

▋Jasmine-core
▋karma
▋karma-chrome-launcher (or karma-firefox-launcher, karma-ie-launcher)
▋karma-jasmine
▋gulp


▋Create karma.conf.js

We can use the following command to create the karma.conf.js step by step, for more information, take a look at Karma : Configuration

$> karma init karma.conf.js

In this example, my test folders are as following,




 So here is my karma.conf.js

Notice I used jquery in my javascript files, so the jquery library must be included in the files configuration.

module.exports = function (config) {
    config.set({
        browsers: ['Chrome'],
        frameworks: ['jasmine'],

        basePath: '',
        files: [
            // dependencies
            '../lib/jquery/dist/jquery.min.js',

            //Target js
            '../scripts/*.js',     

            //Test files
            'spec/*.spec.js',
        ],
        autoWatch: false, //Watching files and executing the tests if the files changes.
        singleRun: true //If true, Karma will run tests and then exit.
    });
};


▋Make a sample for testing

Assume that we would like to test the following html and script.

▋HTML

<div class="row">
    <div class="col-md-2">
        <input type="text" class="form-control" id="numA" />
    </div>
    <div class="col-md-2">
        <input type="text" class="form-control" id="numB" />
    </div>
    <div class="col-md-3">
        <input type="button" class="form-control" id="add" value="Add" />
    </div>
    <div class="col-md-3">
        <input type="button" class="form-control" id="minus" value="Minus" />
    </div>
    <div class="col-md-2">
        Result : <label id="result"></label>
    </div>
</div>


▋JS

'use strict';

$(function () {
    $('#add').click(function () {
        a = $('#numA').val();
        b = $('#numB').val();
        $('#result').text(add(+a, +b));
    });

    $('#minus').click(function () {
        a = $('#numA').val();
        b = $('#numB').val();
        $('#result').text(minus(+a, +b));
    });
})


function add(a, b) {
    return a + b;
}

function minus(a, b) {
    return a - b;
}





Where I put the html and js file to.




▋Create jasmine tests

We are going to write our first unit tests on functions, add and minus, to ensure that they works correctly.

First, add a new test file, *.spec.js




▋demo.index.spec.js

describe('Demo: function test', function () {

    it('should return 6 for 1 + 5', function () {
        expect(add(1, 5)).toEqual(6);
    });

    it('should return 2 for 6 - 4', function () {
        expect(minus(6, 4)).toEqual(2);
    });
});

For more Jasmine tutorials, go to jasmine.github.io.


▋Use gulp to start the test

Open gulpfile.js, add the following scripts to enable running test with gulp.

var gulp = require('gulp');
var server = require('karma').Server;

gulp.task('test', function () {
    new server({
        configFile: __dirname + '/wwwroot/test/karma.conf.js',
        singleRun: true
    }).start();
});


Then start the test by the following command,

$> gulp test

Succeed result:




Or fail result:



▌Handling HTML fixtures

▋Install packages

▋karma-html2js-preprocessor :
Preprocessor for converting HTML files into JS strings.

▋karma-fixture :
A plugin for the Karma test runner that loads .html and .json fixtures.



▋Update karma.conf.js

module.exports = function (config) {
    config.set({
        browsers: ['Chrome'],
        frameworks: ['jasmine','fixture'],

        basePath: '',
        files: [
            // dependencies
            '../lib/jquery/dist/jquery.min.js',

            //Target js
            '../scripts/*.js',     

            //Test files
            'spec/*.spec.js',

            //Inject html
            'html/*.html'
        ],
        preprocessors: {
            'html/*.html': ['html2js']
        },
        autoWatch: false,
        singleRun: true
    });
};


▋Refactor javascript

Before we create Jasmine test, we have to know how the preprocessor works.

This preprocessor converts HTML files into JS strings and publishes them in the global window.__html__, so that you can use these for testing DOM operations.
(From Github: karma-html2js-preprocessor)

If we would like to simulate the click event on the 【Add】 or 【Minus】button in the tests, we need to re-add event listener on the DOM.

So let’s refactor the javascript and create an event handler to isolate the handler codes from event listener.


▋JS

'use strict';
$(function () {
    $('#add').click(function () {
        eventHandler(handler.add);
    });

    $('#minus').click(function () {
        eventHandler(handler.minus);
    });

})

let handler = {
    get add() {
        return "add";
    },
    get minus() {
        return "minus";
    }
};

function eventHandler(handler) {
    let a = $('#numA').val();
    let b = $('#numB').val();
    let rslt = 0;
    switch (handler) {
        case 'add':
            rslt = add(+a, +b);
            break;
        case 'minus':
            rslt = minus(+a, +b);
            break;
        default:
            break;
    }
    $('#result').text(rslt);
};
//Skip...



▋Create Jasmine tests

describe('Demo: Html test', function () {

    // inject the HTML fixture for the tests
    beforeEach(function () {
        fixture.base = 'html';
        fixture.load('demo.index.html');

        //Register events
        document.getElementById('add').addEventListener('click', function () { eventHandler(handler.add); });
        document.getElementById('minus').addEventListener('click', function () { eventHandler(handler.minus); });
    });

    // remove the html fixture from the DOM
    afterEach(function () {
        fixture.cleanup();
    });

    it('should return 10 for 4 + 6 on html', function () {
        document.getElementById('numA').value = 4;
        document.getElementById('numB').value = 6;

        document.getElementById('add').click();

        let actual = document.getElementById('result').innerHTML;
        expect(+actual).toEqual(10);
    });

    it('should return 2 for 6 - 4 on html', function () {
        document.getElementById('numA').value = 6;
        document.getElementById('numB').value = 4;

        document.getElementById('minus').click();

        let actual = document.getElementById('result').innerHTML;
        expect(+actual).toEqual(2);
    });

});


Run test again …





▌Reference
▋Karma Tutorial - Unit Testing JavaScript




張貼者： 於 沒有留言:
訂閱： 文章 (Atom)