Stace's Blog

2012年10月19日星期五

[Excel] 每個開發人員都需要知道的Excel公式

在這邊介紹幾個小弟經常用到的Excel公式，希望對大家使用Excel上有幫助。

1.字串處理
字串處理是用 Excel 給資訊人員最大的福利，尤其是需要批次處理時，不論是執行SQL、執行powershell 或整理報表，用Excel產生多筆的指令再去執行，真是省下很多作業的時間!

(1)合併Excel的欄位值：Excel的指令是=Concatenate(字串1, 字串2, 字串3...)，譬如 =CONCATENATE("insert into tb ([MyID],[MyDate1],[MyDate2],[MyCompany],[MyPosition],[Description]) values (",A2,",'",TEXT(C2,"yyyy/mm/dd"),"','",TEXT(D2,"yyyy/mm/dd"),"','",E2,"','",J2,"','",H2,"')")
的意思就是從A2~H2取欄位值出來，組合成 insert 指令。寫好第一行後，在Excel依次往下拉，就可以產生很多 insert 指令再貼到下Query的工具上，速度!

(2)取出固定文字的左邊或右邊：這種也常遇到，對於辦公室人員，Excel有些是要看的，但是不一定是結構性的資料，所以會有 "員工編號-XXX" 在同一個欄位出現，該如何處理呢 ? 譬如 1000-甲要找出"-"左邊的ID，可以用 Find 這個公式找到"-"的位置，搭配Left，公式是 =LEFT(A1, FIND("-",A1)-1) ，長度要 "-1"是因為只要"100"。

如果要找右邊的，公式就寫成 =RIGHT(A1,LEN(A1)-FIND("-",A1))
公式的內容應該是不難懂，主要是組合多個公式起來，如果一開始不確定，也可以先個別計算，這樣就可以一步一步看結果。

(3)補0(或其他需要重複的字)：主要是用REPT(重複字) 跟 LEN(值的文字長度)組合使用，譬如100,2000,30000都要補成 5碼的長度，公式是=CONCATENATE(REPT("0",5-LEN(B1)),B1)，
分解步驟的話，公式的目的是
- 重複0多次，要重複的次數：固定長度5 減掉100的長度(=3)，所以是重複0共2次
- 字串合併 00 與 100

2.資料取得對應值:在Excel 處理資料時，會遇到需要取得另一個表的值，也就是資料庫的join，可以使用: vlookup 這個公式，公式的格式是=Vlookup(來源的值,要查的範圍,要回傳的欄位位置,是否要完整比對)。
以下圖為例，公式是=VLOOKUP(A1,D:E,2,FALSE) "我要找 A1 對應的值[第1個參數]，去D與E構成的對應表去找[第2個參數]，當A1對應到Excel Column D的值時，回傳第2行[第3個參數]，比對時要完全正確才回傳[第4個參數]。
如果查不到，就會出現#N/A的訊息。

而#N/A是不是很刺眼呢，如果又要拿來串SQL指令時，#N/A會造成CONCATENATE失敗。
該如何避免呢?Excel有另一個公式叫做 ISError ，類似寫程式的try catch例外判斷，搭配IF，如： =IF(ISERROR(VLOOKUP(A1,D:E,2,FALSE)),"--",VLOOKUP(A1,D:E,2,FALSE))
意思是 "如果查到的結果是錯誤，就出現 --，如果不是錯誤，就出現查詢的結果"，
看不到 #N/A，爽度也提高了

要注意的是

Vlookup第2個參數的對應表必須要排序過，要不然是查不到的。
要拿來查詢的值與與對應表的值型態一樣才查的到，1(數字) 與 "1"(文字)是無法比對的上，用改格式的方式去處理會沒用，要用Text這個函數把數字改成文字，1才會變成"1"
如果資料量很大，vlookup 搭配 iserror可能會花時間，不如copy結果出來成另一個Excel在交出去。

另外，
如果你的Excel很複雜，需要查多值，請google "excel array formula vlookup" 希望可以找到你要的答案。
Vlookup也可以自己查自己，因此可以做到類似 recursive 的效果，最適合在產生樹狀結構，如組織的結構，只要把key設為上層，就可以產生出 //公司//董事會//董事長//總經理//台灣分公司//資訊部這樣的多層結構!

3.多IF的判斷：Excel 最單純的 IF是 =IF(條件,條件成立的值，條件不成立的值)。如果要做類似多層的IF判斷時，也就是要 Switch 時或 nested if ，Excel 也是用IF來達成，公式的範例

=IF(A1="A","1",
IF(A1="B","2",
IF(A1="C","3","N")
))
這樣Excel就會判斷
Switch(A1)
{
case "A":"1"
case "B":"2"
case "C":"3"
default: "N"
}
可以看圖也許比較清楚，公式內也可以按 ALT+Enter斷行作些簡單的程式排版，這樣就比較容易知道自己在寫些甚麼了!

Excel還有一些功能，像是清單整理出唯一值(如 SQL Distinct)、Column 轉 Row，都還滿常用的!
歡迎留言交流!

2012年5月12日星期六

[Sharepoint 2010] 用Powershell建Sharepoint群組並指定權限 / Create Group Powershell and add permission

管理權限一直是各種系統的基礎重點，在Sharepoint跟AD的搭配上，都會以AGDLP去講該如何規劃設計。因此在管理 Sharepoint 群組的權限上，系統管理者(ㄞㄊㄧㄓㄨㄢㄩㄢˊ)又要開熟悉Sharepoint Web/ Sharepoint Designer友善的畫面，以下要說明的是
1.在頂層網站建Group
2.把新建好的Group加到各下層的網站，並停止繼承
3.改下層網站各清單的權限

1.在頂層網站建Group，譬如有3個site

$web = Get-SPWeb "http://sps2010/"
$web.SiteGroups.Add("site1_A", $web.Site.Owner, $web.Site.Owner,"")
$web.SiteGroups.Add("site1_B", $web.Site.Owner, $web.Site.Owner,"")
$web.SiteGroups.Add("site1_C", $web.Site.Owner, $web.Site.Owner, "")
$web.SiteGroups.Add("site2_A", $web.Site.Owner, $web.Site.Owner,"")
$web.SiteGroups.Add("site2_B", $web.Site.Owner, $web.Site.Owner,"")
$web.SiteGroups.Add("site2_C", $web.Site.Owner, $web.Site.Owner, "")
$web.SiteGroups.Add("site3_A", $web.Site.Owner, $web.Site.Owner,"")
$web.SiteGroups.Add("site3_B", $web.Site.Owner, $web.Site.Owner,"")
$web.SiteGroups.Add("site3_C", $web.Site.Owner, $web.Site.Owner, "")
$web.Update()
$web.Dispose()

這一段沒什麼學問，如果需要一次建很多，其實可以用EXCEL去組合上面的字。建完Group後，如果需要先放群組的權限，建議用Sharepoint Designer，用Web去管理會在那邊等等等...

2.把新建好的Group加到各下層的網站，並停止繼承

###########################
#
# 函示：add group permission
#
###########################
function AddGroupToSite ($web, $groupName, $permLevel)
{
    $account = $web.SiteGroups[$groupName]
    $assignment = New-Object Microsoft.SharePoint.SPRoleAssignment($account)
    $role = $web.RoleDefinitions[$permLevel]
    $assignment.RoleDefinitionBindings.Add($role);
    $web.RoleAssignments.Add($assignment)
}
#########

#Subsites待修改：看有哪些子網站要執行
$SubSites = @("site1","site2","site3"
)
for($i=0 ; $i -lt $SubSites.count ; $i++)
{
 $url = "http://sps2010/" + $SubSites[$i]
 $web = Get-SPWeb $url
 $web.BreakRoleInheritance($false)
 #Subsites待修改，看Group的名字
 $grp1 = $SubSites[$i]+"_A"
 $grp2 = $SubSites[$i]+"_B"
 $grp3 = $SubSites[$i]+"_C"
 AddGroupToSite -web $web -groupName $grp1 -permLevel "Read"
 AddGroupToSite -web $web -groupName $grp2 -permLevel "Read"
 AddGroupToSite -web $web -groupName $grp3 -permLevel "Read"
 $web.Dispose() 
 Write-Output $SubSites[$i] + " Completed!"
}

3.改下層網站各清單的權限

$web = Get-SPWeb "http://sps2010/"
#List Permission
#
#兩個清單要改：
#ListA: url是 /Site1/List/ListA
#ListB: url是 /Site1/SitePicLib
#如果有更多要改，就一直加在函示裡面
#
###########################
#
# 函示：Change List Permission
#
###########################
function ChangeListPermission ($strSiteName)
{
 $SubSites = $strSiteName
 $grp1 = $SubSites+"_A"
 $grp2 = $SubSites+"_B"
 $grp3 = $SubSites+"_C"
 $url = "http://sps2010/" + $SubSites
 $web = Get-SPWeb $url
 $admaccount = $web.EnsureUser("SHAREPOINT\system")
 #############################ListA#############################
 #$ListR = $web.Lists["ListA"]
 $strListURL = "/" + $SubSites + "/Lists/ListA"
 $ListR = $web.GetList($strListURL)
 $ListR.BreakRoleInheritance($false)
 $account = $web.SiteGroups[$grp2]
 $assignment = New-Object Microsoft.SharePoint.SPRoleAssignment($account)
 $assignment.RoleDefinitionBindings.Add(($web.RoleDefinitions | Where-Object { $_.Type -eq "Contributor" }))
 $ListR.RoleAssignments.Add($assignment)
 $account = $web.SiteGroups[$grp4]
 $assignment = New-Object Microsoft.SharePoint.SPRoleAssignment($account)
 $assignment.RoleDefinitionBindings.Add(($web.RoleDefinitions | Where-Object { $_.Type -eq "Contributor" }))
 $ListR.RoleAssignments.Add($assignment)
 $ListR.RoleAssignments.Remove($admaccount)
 #############################ListB#############################
 $strListURL = "/" + $SubSites + "/SitePicLib"
 $ListR = $web.GetList($strListURL)
 $ListR.BreakRoleInheritance($false)
 $account = $web.SiteGroups[$grp2]
 $assignment = New-Object Microsoft.SharePoint.SPRoleAssignment($account)
 $assignment.RoleDefinitionBindings.Add(($web.RoleDefinitions | Where-Object { $_.Type -eq "Contributor" }))
 $ListR.RoleAssignments.Add($assignment)
 $account = $web.SiteGroups[$grp4]
 $assignment = New-Object Microsoft.SharePoint.SPRoleAssignment($account)
 $assignment.RoleDefinitionBindings.Add(($web.RoleDefinitions | Where-Object { $_.Type -eq "Contributor" }))
 $ListR.RoleAssignments.Add($assignment)
 $account = $web.SiteGroups[$grp1]
 $assignment = New-Object Microsoft.SharePoint.SPRoleAssignment($account)
 $assignment.RoleDefinitionBindings.Add($web.RoleDefinitions["Read"])
 $ListR.RoleAssignments.Add($assignment)
 $account = $web.SiteGroups[$grp3]
 $assignment = New-Object Microsoft.SharePoint.SPRoleAssignment($account)
 $assignment.RoleDefinitionBindings.Add($web.RoleDefinitions["Read"])
 $ListR.RoleAssignments.Add($assignment)
 $ListR.RoleAssignments.Remove($admaccount)
 $web.Dispose()
}
###########################

#待修改
#實際呼叫函示
ChangeListPermission -strSiteName "site1"
ChangeListPermission -strSiteName "site2"
ChangeListPermission -strSiteName "site3"

權限這樣就差不多設定完成了，搭配Excel更快！
如果要對Sharepoint Group加AD Group，請看下一篇!

參考資料：
1.PowerShell to create SharePoint groups http://blog.pointbeyond.com/2011/06/03/powershell-to-create-sharepoint-groups/

#DontLikeSP

2012年3月1日星期四

[Sharepoint 2010] 用Powershell 建網站 / Create web with Powershell

有鑑於 Sharepoint建網站的UI太友善了，讓要建多個網站的管理員會非常熟悉建站的動作跟設定，因此小弟到蒐集網路上各種建站的Powershell，東拼西湊成一個建多個站的Powershell:

1.如果有自訂的web template，則需要找出ID跟Name，可用:

 #待修改
 $url = "http://sps2010/"
 $site= new-Object Microsoft.SharePoint.SPSite($url )
 $loc= [System.Int32]::Parse(1033)
 $templates= $site.GetWebTemplates($loc)
   foreach ($child in $templates)
      {
               write-host $child.Name "  " $child.Title
      }
 $site.Dispose()

查詢出的結果會類似
{2F1B367A-5FF5-444E-B3BC-DBB73E1FEDXX}#SiteTemplateName SiteTemplate_Title
只需要用到前面的，Title只是用來識別的

2.create it!
以下的Powershell有包含以下步驟
(1).建立一個新的網站
(2).設定 "網站設定"的"其他語言"，增加中文(1028)
(3).設定 "網站設定"的"覆寫翻譯"
(4).設定 "管理網站功能"的 "SharePoint Server Publishing"(為了master page)
(5).設定 Master Page

#待修改

$SiteCollectionURL = "http://sps2010"

#待修改


$SiteCollectionTemplate = "
{2F1B367A-5FF5-444E-B3BC-DBB73E1FEDXX}#SiteTemplateName "

#預設語系英文

$SiteCollectionLanguage = 1033

#新站的URL

$SubSites = @("UrlA",

"UrlA",

"UrlA",

)

#新站的標題(Title)

$SubSiteNames =@("TitleA",

"TitleB",

"TitleC",

)

for($i=0 ; $i -lt $SubSites.count ; $i++)

{

 #(1).建立一個新的網站,根據指定的範本與語系，描述則預設為空白

 $SiteUrl = ""

 $SiteUrl = $SiteCollectionURL + "/"

 $SiteUrl = $SiteUrl += $SubSites[$i]

 $web=New-SPWeb $SiteUrl -Name $SubSiteNames[$i]  -UseParentTopNav -Language $SiteCollectionLanguage -Description " "

 #在我的環境，要再套用一次才會work, 不知道為什麼!

 $web.ApplyWebTemplate($SiteCollectionTemplate) 

 #(2).設定 "網站設定"的"其他語言"，增加中文(1028)

 $web.IsMultilingual = $true

 $spReg = New-Object Microsoft.SharePoint.SPRegionalSettings $web

 #顯示安裝的language pack

 $spReg.InstalledLanguages

 $web.AddSupportedUICulture(1028)

 #(3).設定 "網站設定"的"覆寫翻譯"

 $web.OverwriteTranslationsOnChange = $true;

 #(4).設定 "管理網站功能"的 "SharePoint Server Publishing"(為了master page)

 Enable-SPFeature -identity "PublishingWeb" -URL $SiteUrl

 $web.Dispose()

 #Write-Output "完成: " += $SubSites[$i]


}

#

#(5).設定 Master Page

#因為需要web.update 所以分開執行

#

for($i=0 ; $i -lt $SubSites.count ; $i++)

{

 $SiteUrl = ""

 $SiteUrl = $SiteCollectionURL + "/"

 $SiteUrl = $SiteUrl += $SubSites[$i]

 $web = Get-SPWeb $SiteUrl

 $web.AllProperties["__InheritsCustomMasterUrl"] = "False";

 #待修改

 $web.CustomMasterUrl = "/_catalogs/masterpage/myv4.master"

 $web.AllProperties["__InheritsMasterUrl"] = "False";

  #待修改

 $web.MasterUrl = "/_catalogs/masterpage/myv4.master"

 $web.Update()

 $web.Dispose()

}

希望sharepoint後代多考慮管理員批次處理的功能，時間不是用來做重複的管理工作!

參考:

Create Web from Custom Template in SharePoint 2010 (PowerShell and STSADM) http://mkdot.net/mknetug/mk_sp/b/darko/archive/2011/02/17/create-web-from-custom-template-in-sharepoint-2010.aspx
Powershell Script to create subsites within a site collection http://www.sharepointfix.com/2011/04/powershell-script-to-create-subsites.html

#DontLikeSP

2012年2月18日星期六

[How to] 如何用C#使用Windows Fax / Fax with C# in Windows Fax Service Environment

[How to] Fax with C# in Windows Fax Service Environment

透過 Windows Fax Service，以程式方式發送傳真

使用語言: C#
加入參考 ( FXSRESM.dll ) 路徑在： C:\windows\system32\FXSRESM.dll
Code 如下：

FaxServer faxServer = new FaxServer();

//IP是Fax Server IP
faxServer.Connect("192.168.1.1");
FaxDocument aDoc = new FaxDocument();

//要FAx的檔案
aDoc.Body = "C:\\Temp\\1.docx";

aDoc.ReceiptAddress = "stace@xxx";

//收傳真的電話
aDoc.Recipients.Add("88880000", "Recp");
aDoc.ConnectedSubmit(faxServer);

就這樣就可以發Fax出去, Stupid and simple! Good!

其他傳真的封面/封底/Server傳送的狀態還需要再詳細看SDK與 COM Object的其他method! 基本上應該還要確認一下對方是否有傳成功，就類似人為動作一樣..

參考:

Using the Fax Service SDK http://msdn.microsoft.com/en-us/library/windows/desktop/ms693392(v=vs.85).aspx
Fax Service Extended COM Objects http://msdn.microsoft.com/en-us/library/windows/desktop/ms693456(v=vs.85).aspx

訂閱：文章 (Atom)

Stace's Blog

2012年10月19日星期五

[Excel] 每個開發人員都需要知道的Excel公式

2012年5月12日星期六

[Sharepoint 2010] 用Powershell建Sharepoint群組並指定權限 / Create Group Powershell and add permission

2012年3月1日星期四

[Sharepoint 2010] 用Powershell 建網站 / Create web with Powershell

2012年2月18日星期六

[How to] 如何用C#使用Windows Fax / Fax with C# in Windows Fax Service Environment

關於我自己

網誌存檔

2012年10月19日 星期五

[Excel] 每個開發人員都需要知道的Excel公式

2012年5月12日 星期六

[Sharepoint 2010] 用Powershell建Sharepoint群組並指定權限 / Create Group Powershell and add permission

2012年3月1日 星期四

[Sharepoint 2010] 用Powershell 建網站 / Create web with Powershell

2012年2月18日 星期六

[How to] 如何用C#使用Windows Fax / Fax with C# in Windows Fax Service Environment

2012年10月19日星期五

2012年5月12日星期六

2012年3月1日星期四

2012年2月18日星期六