[EXCEL] 日期格式 Excel 民國轉西元

在EXCEL中，如果要將民國的日期格式(A欄)轉為西元格式(B欄)，如下圖
可以再B 欄利用公式完成
B1公式範例=DATE(MID(A2,1,3)+1911,MID(A2,5,2),MID(A2,8,2))

如果日期格式還想要順便按照希望的顯示樣式 例如月日 皆為兩位數
C欄則可以直接利用A欄位直接改為
C1公式範例=TEXT(DATE(MID(A2,1,3)+1911,MID(A2,5,2),MID(A2,8,2)),"yyyy/MM/dd")

[SQL Server 2012] Maintenance Plan Maintenance Cleanup Task 備份檔案沒有刪除

我的問題是 我在SQL Maintenance Plan  做了保留三天的備份，可是不知道怎麼搞得 有些設定就是可以成功清除三天份外的資料庫備份檔，卡關了很久終於找出原因了!
我使用的是SQL SERVER 2012，這幾天突然腦袋清晰解決了這件事情

我的備份方法是針對每一個DB 會自動產生一個資料夾

所以我當然希望他在清除的時候可以針對每個資料夾

原來我的問題出在清除的副檔案名稱 千萬不要加上『.』

另外要記得勾選include first-level subfolders

終於解決心中的惱人的小問題~分享給也遇到卡關的人 😍

[好用工具]監控軟體使用網路流量 Process Monitor

啟動Process Monitor後 可以先讓他執行一陣子，再開始過濾你要的東西，
我這次正好要監控兩個應用程式，了解它們一個使用者各佔用了多少頻寬
因此再篩選的地方指定了兩個主程式

接著到Tools →Process Activity Summary去看結果

當然你監控的時間越久，數值越準確，但結果跑起來有些吃力就是了~

我這個結果是在SERVER上跑的，上面有不少使用者正在執行，可以從中用最大值來做評估
整個使用流量，覺得非常的實用，也看出自己開發的軟體相比品質算好的(自我滿足😆)

參考連結
http://blog.darkthread.net/blogs/darkthreadtw/archive/2007/08/18/977.aspx
https://docs.microsoft.com/en-us/sysinternals/downloads/procmon

[GrapeCity][Spread Windows Forms] 錯誤 Task could not find "LC.exe" using the SdkToolsPath ""

錯誤訊息(Error):
Task could not find "LC.exe" using the SdkToolsPath "" or the registry key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SDKs\NETFXSDK\4.6.1\WinSDK-NetFx40Tools-x86". Make sure the SdkToolsPath is set and the tool exists in the correct processor specific location under the SdkToolsPath and that the Microsoft Windows SDK is installed xxx

解決方法:
這個狀況其實就是預設Spread一開所放置的是免費試用的版本
開新的專案(Project)要先手動直接刪除，再重新編譯就會再次自動產生有付費後的版本

[OFFICE2010][PowerPoint] 將PPT轉為網頁

PPT轉網頁方法

1.      請一個網頁一個PPT檔案

2.      在PPT 中按下Alt+F11

接著在View選擇Immediate Window

3.      在Immediate Window 輸入下面這串文字後直接Enter

※紅字為指定存檔位置，檔名切記不使用中文且不要有空白

ActivePresentation.SaveAs "D:\temp\Default.htm", ppSaveAsHTML, msoFalse

4.      轉出後會產生一個htm網頁檔與相同檔名資料夾，將這兩個檔案直接放置

Website資料夾中即可

5.      常見問題

þ有連結的請固定在前方加上紅字http://xxx.xxx.xxx/*****.htm

[Windows Forms] [VB.NET] 實現利用AD帳號登入Windows Form

我過去的設計都是走獨立SQL帳號密碼管理
但這次的需求是要利用走現有的AD認證，方便使用者不需要記多組帳號密碼





以下直接列出我的實作方式

1.先加入參考

2.直接開一個檔案為LdapAuthentication.vb
完整程式碼如下:

Imports System
Imports System.Text
Imports System.Collections
Imports System.DirectoryServices


Namespace FormsAuth
    Public Class LdapAuthentication

        Dim _path As String
        Dim _filterAttribute As String

        Public Sub New(ByVal path As String)
            _path = path
        End Sub

        Public Function IsAuthenticated(ByVal domain As String, ByVal username As String, ByVal pwd As String) As Boolean

            Dim domainAndUsername As String = domain & "\" & username
            Dim entry As DirectoryEntry = New DirectoryEntry(_path, domainAndUsername, pwd)

            Try
                'Bind to the native AdsObject to force authentication.
                Dim obj As Object = entry.NativeObject
                Dim search As DirectorySearcher = New DirectorySearcher(entry)

                search.Filter = "(SAMAccountName=" & username & ")"
                search.PropertiesToLoad.Add("cn")
                Dim result As SearchResult = search.FindOne()

                If (result Is Nothing) Then
                    Return False
                End If

                'Update the new path to the user in the directory.
                _path = result.Path
                _filterAttribute = CType(result.Properties("cn")(0), String)

            Catch ex As Exception
                Throw New Exception("Error authenticating user. " & ex.Message)
            End Try

            Return True
        End Function

        Public Function GetGroups() As String
            Dim search As DirectorySearcher = New DirectorySearcher(_path)
            search.Filter = "(cn=" & _filterAttribute & ")"
            search.PropertiesToLoad.Add("memberOf")
            Dim groupNames As StringBuilder = New StringBuilder()

            Try
                Dim result As SearchResult = search.FindOne()
                Dim propertyCount As Integer = result.Properties("memberOf").Count

                Dim dn As String
                Dim equalsIndex, commaIndex

                Dim propertyCounter As Integer

                For propertyCounter = 0 To propertyCount - 1
                    dn = CType(result.Properties("memberOf")(propertyCounter), String)

                    equalsIndex = dn.IndexOf("=", 1)
                    commaIndex = dn.IndexOf(",", 1)
                    If (equalsIndex = -1) Then
                        Return Nothing
                    End If

                    groupNames.Append(dn.Substring((equalsIndex + 1), (commaIndex - equalsIndex) - 1))
                    groupNames.Append("|")
                Next

            Catch ex As Exception
                Throw New Exception("Error obtaining group names. " & ex.Message)
            End Try

            Return groupNames.ToString()
        End Function
    End Class
End Namespace


3.Login按鈕

Sub Login_Click(sender as object,e as EventArgs) Handles btnLogin.Click

  Dim adPath as String = "LDAP://DC=..,DC=.." 'Path to your LDAP directory server

 'LDAP 輸入方式再請另外自己GOOGLE填寫方式

  Dim adAuth as LdapAuthentication = new LdapAuthentication(adPath)
  try
    if(true = adAuth.IsAuthenticated(txtDomain.Text, txtUsername.Text, txtPassword.Text)) then
      Dim groups as string = adAuth.GetGroups()

      'Create the ticket, and add the groups.
      Dim isCookiePersistent as boolean = chkPersist.Checked
      Dim authTicket as FormsAuthenticationTicket = new FormsAuthenticationTicket(1, _
           txtUsername.Text,DateTime.Now, DateTime.Now.AddMinutes(60), isCookiePersistent, groups)

      'Encrypt the ticket.
      Dim encryptedTicket as String = FormsAuthentication.Encrypt(authTicket)

      'Create a cookie, and then add the encrypted ticket to the cookie as data.
      Dim authCookie as HttpCookie = new HttpCookie(FormsAuthentication.FormsCookieName, encryptedTicket)

      if(isCookiePersistent = true) then
authCookie.Expires = authTicket.Expiration
      end if
      'Add the cookie to the outgoing cookies collection.
      Response.Cookies.Add(authCookie)

      'You can redirect now.
      Response.Redirect(FormsAuthentication.GetRedirectUrl(txtUsername.Text, false))
    
    else
      errorLabel.Text = "Authentication did not succeed. Check user name and password."
    end if

  catch ex as Exception
    errorLabel.Text = "Error authenticating. " & ex.Message
  end try
End sub




文章參考
 vb.net開發的login程式(win form),帳號要如何與AD帳號整合?
https://social.msdn.microsoft.com/Forums/zh-TW/5fe87afe-340a-41eb-bab4-a8707400a9a3/vbnetloginwin-formad?forum=232


如何使用表單驗證及 Visual Basic .NET 通過 Active Directory 驗證
https://support.microsoft.com/zh-tw/help/326340/how-to-authenticate-against-the-active-directory-by-using-forms-authen

[VB.Net] 程式自動建立捷徑到桌面上


If (Not File.Exists(String.Format("{0}\程式.lnk", My.Computer.FileSystem.SpecialDirectories.Desktop))) Then '檢查桌面
CreateShortCut("檔案位置", "程式.exe", "描述") '產生捷徑
End If



Sub CreateShortCut(ByRef strFolder As String, ByRef strExe As String, ByRef strName As String)
Dim sc, objWS As Object
objWS = CreateObject("WScript.Shell")
With objWS
sc = .CreateShortCut(.SpecialFolders("Desktop") & "\" & Replace(strExe, ".EXE", ".LNK", 1, , 1))
End With
With sc
.TargetPath = objWS.ExpandEnvironmentStrings(strFolder & "\" & strExe)
.WorkingDirectory = strFolder
.Description = strName
.WindowStyle = 4
.IconLocation = objWS.ExpandEnvironmentStrings(strFolder & "\" & strExe & ",0")
.Save()
End With
End Sub