Get data from worksheet

從worksheet中cell取數,常見辦法如

with sheet1
    name = .cells(i,1)
    company = .cells(i,2)
end with

此方法直接,但是欠缺機動性,假如日後worksheet有任何改動,令到name不是出現在col 1,全部代碼都需要改.

方法1

將各col在代碼頂部定義為常數, 日後需要修改就在頂部定義中修改.修改地方集中到一處,但是還是麻煩

const nameCol = 1 , companyCol = 2
with sheet1
    name = .cells(i,nameCol)
    company = .cells(i,companyCol)
end with

Private Enum AllCols
  nameCol = 1
  companyCol
End Enum

with sheet1
    name = .cells(i,AllCols.nameCol)
    company = .cells(i,AllCols.companyCol)
end with

方法2

充分利用Table (Excel 2007 or higher).

先在worksheet中定義Table,再在VBA中通過ListObjects訪問

Const nameCol = "name", companyCOl = "company"

set tbl = Sheet1.ListObjects("tableName")
with sheet1
  For each row in tbl.ListRows
    name = row.range(, col(nameCol))
    company = row.range(, col(companyCol))
    'do something
  next row
end with

方法3

利用ADO, 對於Excel2003, 連本workbook的時候慎防內存洩露

本文受該文啓發, 特此鳴謝

article clipper remember Get data from worksheet
 

Every Presentation Ever: Communication FAIL

article clipper remember Every Presentation Ever: Communication FAIL
 

Regular Expressions in JavaScript

1.定義

Javascript中,定義正則表達式的方法有兩個:

1) RegExp Literal

/* /pattern/flags; */
var re = /mac/i;

2) RegExp Object Constructor

/* new RegExp("pattern","flags"); */
var re = new RegExp(window.prompt("Please input a regex.","yes|yeah"),"g");

 

2. Flag

1) Global Search

g     The global search flag makes the RegExp search for a pattern throughout the string, creating an array of all occurrences it can find matching the given pattern.

2) Flags

i     The ignore case flag makes a regular expression case insensitive. For international coders, note that this might not work on extended characters such as ?, ü, ?, ?.

3) Multiline Input

m     This flag makes the beginning of input (^) and end of input ($) codes also catch beginning and end of line respectively.

 

3. Pattern

參閱此處

 

4. 應用

1) RegExp.exec(string)

Applies the RegExp to the given string, and returns the match information.

var match = /s(amp)le/i.exec("Sample text")
//match then contains ["Sample","amp"] 

2) RegExp.test(string)

Tests if the given string matches the Regexp, and returns true if matching, false if not.   

var match = /sample/.test("Sample text")
//match then contains false 

3) String.match(pattern)

Matches given string with the RegExp. With g flag returns an array containing the matches, without g flag returns just the first match or if no match is found returns null.   

var str = "Watch out for the rock!".match(/r?or?/g)
//str then contains ["o","or","ro"] 

4) String.search(pattern)

Matches RegExp with string and returns the index of the beginning of the match if found, -1 if not.   

var ndx = "Watch out for the rock!".search(/for/)
//ndx then contains 10 

5) String.replace(pattern,string)

Replaces matches with the given string, and returns the edited string.   

var str = "Liorean said: My name is Liorean!".replace(/Liorean/g,'Big Fat Dork')
//str then contains "Big Fat Dork said: My name is Big Fat Dork!" 

6) String.split(pattern)

Cuts a string into an array, making cuts at matches.   

var str = "I am confused".split(/\s/g)
//str then contains ["I","am","confused"]

 

Example:

var s='<div style="text-align:center;width:inherit;text-color:blue;">SOME TEXT</div>'
var rx=new RegExp("<div .*?>(.*?)</div>","i");
s=s.replace(rx,"$1");

// s="SOME TEXT"
// http://www.tek-tips.com/viewthread.cfm?qid=1231301

article clipper remember Regular Expressions in JavaScript
 

Hide right column in SharePoint Home page

SharePoint中Team Site的主頁, 默認會給創建為按7:3的比例左右分割. 這是一個煩人的問題, 因為很多時候我們並不想以這個比例劃分左右Column.

解決方法:

1. SharePoint Designer

  1. 用SharePoint Designer打開default.aspx
  2. 在Design View中, 點擊右邊webpart 區域, 選擇Menu – Delete Columns from the table. 并改變左邊column的寬度到100%.
  3. 保存,退出

Tip: You can just as easily insert new rows and columns and then add new web part zones from the Insert, SharePoint Controls menu

2. Javascript

<script>
//http://techtrainingnotes.blogspot.com/2008/11/sharepoint-how-to-hide-right-web-part.html
function HideWebPartZone()
{
  var x = document.getElementsByTagName("TD")
  var i=0;
  for (i=0;i<x.length;i++)
  {
    if (x[i].width=="70%")
    {
      // left column
      x[i].style.width="100%"; 

      // center (otherwise empty) column
      if (document.all) // is IE
        var x2=x[i].nextSibling;
      else
        var x2=x[i].nextSibling.nextSibling;

      x2.style.width="0";
      x2.style.display="none";
      x2.innerHTML=""; 

      // right column
      if (document.all) // is IE
        x2=x[i].nextSibling.nextSibling;
      else
        x2=x[i].nextSibling.nextSibling.nextSibling.nextSibling;

      x2.style.width="0";
      x2.style.display="none";
      x2.innerHTML=""; 

      // right margin column
      if (document.all) // is IE
        x2=x[i].nextSibling.nextSibling.nextSibling;
      else
        x2=x[i].nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling;

      x2.style.width="0";
      x2.style.display="none";
      x2.innerHTML="";

      //all done
      return;
    }
  }
}

_spBodyOnLoadFunctionNames.push("HideWebPartZone")
</script>

此方法,在IE7中流暢度欠佳.

用設置Div box的width似乎更理想

3. 直接覆蓋

當創建web part page 之後,需要更改page 的layout (如三欄變為兩欄),可以用同名創建欲改變的layout的web part page 以覆蓋之!

image Hide right column in SharePoint Home page

image 3 Hide right column in SharePoint Home page

image 4 Hide right column in SharePoint Home page

註意:覆蓋會令導致原來頁面中所有內容丟失,請先做好備份! 同時該方法不適用於home page, 因為homepage 異於普通web part page,詳情參見此處

article clipper remember Hide right column in SharePoint Home page
 

SharePoint Cheat sheet

Sharepoint在某些時候,部份的元素實屬多餘,例如Quick Launch Bar等. 要將其去除,最好當然是通過SharePoint Designer. 然而很多時候, 無法通過底層修改SharePoint的模板. 於是,只能求助于CSS.

  1. SharePoint 2007 Cheat Sheet
  2. Cheat Sheet Tool
  3. Style Under Cursor 下載此webpart,并加載到閣下頁面,就可以通過hover的方式,獲取鼠標所指元素的class或者id

下面是Hide Quick Launch Bar代碼

<style>
     .ms-navframe { display: none }
</style>

article clipper remember SharePoint Cheat sheet
 

Observer Pattern in Javascript

Observer Pattern 的重點是, publisher發佈消息之後,subscriber會自動對消息作出反應.

image Observer Pattern in Javascript

I. Publisher

1. Publisher構造函數

  1. 一個數組
  2. 方法:
    1. subscribe    : 添加func到array
    2. unsubscribe: 從array中remove function
    3. publish        : 當publisher publish 消息的時候, 所有subscriber的方法都會給觸發

2. makePublisher(o)

將publisher 的方法複製到object O中,為其添加publisher的方法

該方法中沒有observer

 

Example 1- Here

Example 2 – oreilly (Live)

  • game是window的訂閱者,當window中的onkeypress給觸發,其handlekeypress會立即給觸發
  • Player是game的訂閱者,當game.handlekeypress給觸發,Player的play會立即給觸發
  • game同是又是Player的訂閱者,當Player.play給觸發,game.handlePlay會立即給觸發
  • scoreboard是game的handleplay訂閱者,當game.handlePlay給觸發, scoreboard.update會立即給觸發

window.onkeypress –> game.handlekeypress –> Player:play –> game: handlePlay –> scoreboard.update

 

II. Publisher & Observer

1. Publisher

包含一個數組記錄所有subscribers和publish方法

2. Subscriber

包含subscribe 和 unsubscribe

Example from book 『Pro Javascript Design Patterns』

article clipper remember Observer Pattern in Javascript
 

Proxy Pattern in Javascript

  1. 代理與本體 (RealObject) 實現同樣的接口
  2. 代理工作時,實際也是通過本體工作,call 本體的方法
  3. 代理並不會在本體的基礎上進行修改, 否則就是裝飾模式

Example 1

/* From chapter 3. */

var Publication = new Interface('Publication', ['getIsbn', 'setIsbn', 'getTitle',
  'setTitle', 'getAuthor', 'setAuthor', 'display']);
var Book = function(isbn, title, author) { ... } // implements Publication

/* Library interface. */

var Library = new Interface('Library', ['findBooks', 'checkoutBook', 'returnBook']);

/* PublicLibrary class. */

var PublicLibrary = function(books) { // implements Library
  this.catalog = {};
  for(var i = 0, len = books.length; i < len; i++) {
    this.catalog[books[i].getIsbn()] = { book: books[i], available: true };
  }
};
PublicLibrary.prototype = {
  findBooks: function(searchString) {
    var results = [];
    for(var isbn in this.catalog) {
      if(!this.catalog.hasOwnProperty(isbn)) continue;
      if(searchString.match(this.catalog[isbn].getTitle()) ||
          searchString.match(this.catalog[isbn].getAuthor())) {
        results.push(this.catalog[isbn]);
      }
    }
    return results;
  },
  checkoutBook: function(book) {
    var isbn = book.getIsbn();
    if(this.catalog[isbn]) {
      if(this.catalog[isbn].available) {
        this.catalog[isbn].available = false;
        return this.catalog[isbn];
      }
      else {
        throw new Error('PublicLibrary: book ' + book.getTitle() +
          ' is not currently available.');
      }
    }
    else {
      throw new Error('PublicLibrary: book ' + book.getTitle() + ' not found.');
    }
  },
  returnBook: function(book) {
    var isbn = book.getIsbn();
    if(this.catalog[isbn]) {
      this.catalog[isbn].available = true;
    }
    else {
      throw new Error('PublicLibrary: book ' + book.getTitle() + ' not found.');
    }
  }
};

Example 2

/* PublicLibraryProxy class, a useless proxy. */

var PublicLibraryProxy = function(catalog) { // implements Library
  this.library = new PublicLibrary(catalog);
};
PublicLibraryProxy.prototype = {
  findBooks: function(searchString) {
    return this.library.findBooks(searchString);
  },
  checkoutBook: function(book) {
    return this.library.checkoutBook(book);
  },
  returnBook: function(book) {
    return this.library.returnBook(book);
  }
};

/* PublicLibraryVirtualProxy class. */

var PublicLibraryVirtualProxy = function(catalog) { // implements Library
  this.library = null;
  this.catalog = catalog; // Store the argument to the constructor.
};
PublicLibraryVirtualProxy.prototype = {
  _initializeLibrary: function() {
    if(this.library === null) {
      this.library = new PublicLibrary(this.catalog);
    }
  },
  findBooks: function(searchString) {
    this._initializeLibrary();
    return this.library.findBooks(searchString);
  },
  checkoutBook: function(book) {
    this._initializeLibrary();
    return this.library.checkoutBook(book);
  },
  returnBook: function(book) {
    this._initializeLibrary();
    return this.library.returnBook(book);
  }
};

有些類或對象的創建開銷大,而且不需要在實例化后立即訪問其數據 http://blog.thankphp.net/?p=22

Example 3: DirectoryProxy

/* Directory interface. */

var Directory = new Interface('Directory', ['showPage']);

/* PersonnelDirectory class, the Real Subject */

var PersonnelDirectory = function(parent) { // implements Directory
  this.xhrHandler = XhrManager.createXhrHandler();
  this.parent = parent;
  this.data = null;
  this.currentPage = null;

  var that = this;
  var callback = {
    success: that._configure,
    failure: function() {
      throw new Error('PersonnelDirectory: failure in data retrieval.');
    }
  }
  xhrHandler.request('GET', 'directoryData.php', callback);
};
PersonnelDirectory.prototype = {
  _configure: function(responseText) {
    this.data = eval('(' + reponseText + ')');
    ...
    this.currentPage = 'a';
  },
  showPage: function(page) {
    $('page-' + this.currentPage).style.display = 'none';
    $('page-' + page).style.display = 'block';
    this.currentPage = page;
  }
};

/* DirectoryProxy class, just the outline. */

var DirectoryProxy = function(parent) { // implements Directory

};
DirectoryProxy.prototype = {
  showPage: function(page) {

  }
};

/* DirectoryProxy class, as a useless proxy. */

var DirectoryProxy = function(parent) { // implements Directory
  this.directory = new PersonnelDirectory(parent);
};
DirectoryProxy.prototype = {
  showPage: function(page) {
    return this.directory.showPage(page);
  }
};

/* DirectoryProxy class, as a virtual proxy. */

var DirectoryProxy = function(parent) { // implements Directory
  this.parent = parent;
  this.directory = null;
  var that = this;
  addEvent(parent, 'mouseover', that._initialize); // Initialization trigger.
};
DirectoryProxy.prototype = {
  _initialize: function() {
    this.directory = new PersonnelDirectory(this.parent);
  },
  showPage: function(page) {
    return this.directory.showPage(page);
  }
};

/* DirectoryProxy class, with loading message. */

var DirectoryProxy = function(parent) { // implements Directory
  this.parent = parent;
  this.directory = null;
  this.warning = null;
  this.interval = null;
  this.initialized = false;
  var that = this;
  addEvent(parent, 'mouseover', that._initialize); // Initialization trigger.
};
DirectoryProxy.prototype = {
  _initialize: function() {
    this.warning = document.createElement('div');
    this.parent.appendChild(this.warning);
    this.warning.innerHTML = 'The company directory is loading...';

    this.directory = new PersonnelDirectory(this.parent);
    var that = this;
    this.interval = setInterval(that._checkInitialization, 100);
  },
  _checkInitialization: function() {
    if(this.directory.currentPage != null) {
      clearInterval(this.interval);
      this.initialized = true;
      this.parent.removeChild(this.warning);
    }
  },
  showPage: function(page) {
    if(!this.initialized) {
      return;
    }
    return this.directory.showPage(page);
  }
};

Example 4: DynamicProxy

遠程代理: 有某種遠程資源, 並且為該資源提供的所有功能實現對應的方法.

webproxy

/* DynamicProxy abstract class, incomplete. */

var DynamicProxy = function() {
  this.args = arguments;
  this.initialized = false;
};
DynamicProxy.prototype = {
  _initialize: function() {
    this.subject = {}; // Instantiate the class.
    this.class.apply(this.subject, this.args);
    this.subject.__proto__ = this.class.prototype;

    var that = this;
    this.interval = setInterval(function() { that._checkInitialization(); }, 100);
  },
  _checkInitialization: function() {
    if(this._isInitialized()) {
      clearInterval(this.interval);
      this.initialized = true;
    }
  },
  _isInitialized: function() { // Must be implemented in the subclass.
    throw new Error('Unsupported operation on an abstract class.');
  }
};

/* DynamicProxy abstract class, complete. */

var DynamicProxy = function() {
  this.args = arguments;
  this.initialized = false;

  if(typeof this.class != 'function') {
    throw new Error('DynamicProxy: the class attribute must be set before ' +
      'calling the super-class constructor.');
  }

  // Create the methods needed to implement the same interface.
  for(var key in this.class.prototype) {
    // Ensure that the property is a function.
    if(typeof this.class.prototype[key] !== 'function') {
      continue;
    }

    // Add the method.
    var that = this;
    (function(methodName) {
      that[methodName] = function() {
        if(!that.initialized) {
          return
        }
        return that.subject[methodName].apply(that.subject, arguments);
      };
    })(key);
  }
};
DynamicProxy.prototype = {
  _initialize: function() {
    this.subject = {}; // Instantiate the class.
    this.class.apply(this.subject, this.args);
    this.subject.__proto__ = this.class.prototype;

    var that = this;
    this.interval = setInterval(function() { that._checkInitialization(); }, 100);
  },
  _checkInitialization: function() {
    if(this._isInitialized()) {
      clearInterval(this.interval);
      this.initialized = true;
    }
  },
  _isInitialized: function() { // Must be implemented in the subclass.
    throw new Error('Unsupported operation on an abstract class.');
  }
};

/* TestProxy class. */

var TestProxy = function() {
  this.class = TestClass;
  var that = this;
  addEvent($('test-link'), 'click', function() { that._initialize(); });
    // Initialization trigger.
  TestProxy.superclass.constructor.apply(this, arguments);
};
extend(TestProxy, DynamicProxy);
TestProxy.prototype._isInitialized = function() {
  ... // Initialization condition goes here.
};

 

附錄

Observer pattern in java

http://www.darkmi.com/blog/archives/1961

article clipper remember Proxy Pattern in Javascript
 

How to display a PowerPoint in SharePoint

1. SharePoint 2007

  1. 先將PowerPoint 保存為webpage (.htm)
  2. 在SharePoint建立一個Document Library Upload multiple documents
    image How to display a PowerPoint in SharePoint
  3. Upload 1中提及的webpage, 只需要選擇webpage, folder中其他文件會自行upload
    image 3 How to display a PowerPoint in SharePoint
  4. Create 一個webpage,添加webpart: pageviewer
    image 4 How to display a PowerPoint in SharePoint
  5. 複製3中上傳的webpage link
    image 5 How to display a PowerPoint in SharePoint
  6. 粘貼link到webpart
    image 6 How to display a PowerPoint in SharePoint

2. SharePoint 2010

轉自nothingbutsharepoint

  也是利用pageviewer webpart實現.

兩者都有個缺點,背景都是黑色,不能自定義,或許會跟SharePoint模板格格不入 

article clipper remember How to display a PowerPoint in SharePoint
 

Sketching in Layers

article clipper remember Sketching in Layers
 

Disable open button in file download dialogue

當點擊連接時,要禁止文件(如xls,ppt)在網頁中自動打開,可在網頁頭加入meta 信息.

<script type="text/javascript"> 

var head=document.getElementsByTagName('head')[0];
var meta = document.createElement('meta');
meta.name="DownloadOptions";
meta.content = "noopen"; 

head.appendChild(meta);
</script>

article clipper remember Disable open button in file download dialogue