顯示具有 Programme程式相關 標籤的文章。 顯示所有文章
顯示具有 Programme程式相關 標籤的文章。 顯示所有文章

2023年12月29日 星期五

用PYTHON程式,解析XML文件檔案資訊

XML檔,文字編輯器打開,出現一堆HTML標記<>對稱TAG資訊,如何簡單去除TAG資訊方便我們解讀?
可透過下面PYTHON程式(BeautifulSoup模組)進行解析查看

右鍵傳送至XmlParser.py  快速傳遞檔案參數,直接轉換快速作法:
步驟一: 開始| 執行 | Sendto:shell  , (即  C:\Users\YourName\AppData\Roaming\Microsoft\Windows\SendTo  )
步驟二: 將下面程式另字成 XmlParser.py(右鍵傳送到桌面當成捷徑,產生lnk檔) ,並將該lnk檔案複製到上面SendTo資料夾內  
步驟三: 點選您欲轉換之XML檔案,並按右鍵,指定給 XmlParser,即可快速執行XML格式檔案檢視


import sys
from bs4 import BeautifulSoup

def remove_html_tags(html):
    soup = BeautifulSoup(html, 'html.parser')
    text = soup.get_text(separator='\n', strip=True)
    return text

def process_xml_file(file_path):
    with open(file_path, 'r', encoding='utf-8') as file:
        xml_data = file.read()
        # 假設 XML 内容位於 BODY 標記中,則可將其提取出来
        start_index = xml_data.find("<BODY>") + len("<BODY>")
        end_index = xml_data.find("</BODY>")
        xml_body = xml_data[start_index:end_index].strip()

        #  移除 HTML 標記,並整理為可讀性文本
        text_content = remove_html_tags(xml_body)

        # 產出結果至螢幕
        print(text_content)
        
        #將畫面將時暫停,方便複製下來,貼到EXCEL去分析,並按任意鍵後離開
        input("Press any key to continue!")

if __name__ == "__main__":
    if len(sys.argv) != 2:
        print("Usage: python XmlParser.py <xml_file_path>")
        sys.exit(1)

    xml_file_path = sys.argv[1]
    process_xml_file(xml_file_path)

 

XML格式開放資料集查詢:

 展覽資訊、電影資訊、演唱會資訊


No-Break Space 不換行空格 之處理 ChrW(160)

情境:
因原自網頁來源資料,內含NBSP不換行空格(即 &nbsp; ) ,故藉由 PYTHON程式去除 空白特殊字元,
才不會看起來一樣,但對電腦(如:EXCEL之vlookup比對函數)來說,後面多個空白字元,就是2個不同的字串值。


import os
import sys

def replace_string_in_file(file_path):
    # 讀取欲去處理之原始檔案內容
    with open(file_path, 'r', encoding='utf-8') as file:
        content = file.read()

    # 替換NBSP特殊字元
    replaced_content = content.replace('&nbsp;', '')

    # 取得原檔案的檔名和副檔名
    file_name, file_extension = os.path.splitext(file_path)

    # 產生新檔案名稱( 原檔名_1 )
    new_file_name = file_name + '_1' + file_extension

    # 寫入去除空白字元後的內容,到新檔案
    with open(new_file_name, 'w', encoding='utf-8') as new_file:
        new_file.write(replaced_content)

    print(f"已完成去除空白字元,並產製新檔:{new_file_name}")


if __name__ == "__main__":
    if len(sys.argv) != 2:
        print("Usage: python RemoveNSPF.py <Original_file_path>")
        sys.exit(1)

    nspf_file_path = sys.argv[1]
    replace_string_in_file(nspf_file_path)



相關查詢:

No-Break Space 不換行空格 ChrW(160)

如何檢視空白CRLF換行等特殊字元神器? 可透過Notepad++文字編輯器,查看 檢視(V) | 特殊字元 | 顯示所有字元

空白字元導致vlookup查詢函數失敗,因此需將NBSP特殊字元予以去除

2023年12月24日 星期日

Wav to Txt 聲音檔案轉換成純文字檔 (逐字稿)

 情境:逐字稿 公司提供開完會後之WAV錄音檔,想轉換成純文字檔,節省打字時間


需預先載入:語音辨識相關套件

pip install SpeechRecognition



以下為PYTHON程式:

import os,sys
import subprocess


import speech_recognition as sr

# language='ja-JP' 可改成辨識日文語系; language='en-US' 可改成辨識英文語系
def convert_audio_to_text(audio_file):
    recognizer = sr.Recognizer()
    with sr.AudioFile(audio_file) as source:
        audio = recognizer.record(source)
    try:
        text = recognizer.recognize_google(audio,
language='zh-TW')
        return text
    except sr.UnknownValueError:
        return "無法辨識音訊"
    except sr.RequestError:
        return "無法連接到語音識別服務"


#'開啟GUI 取得來源檔(準備欲轉換聲音來源,開啟檔案對話視窗宣告處理)
import tkinter as tk
from tkinter import filedialog

root = tk.Tk()
root.withdraw()

# 取得轉換聲音,轉成純文字檔來源資訊
file_path = filedialog.askopenfilename(initialdir = "/",title = "Select file for OCR (選擇欲辨識聲音檔轉文字檔)",filetypes = (("Audio files","*.wav"),("All files","*.*")))

text1 = convert_audio_to_text(file_path)
print(text1)

with open('wavfile.txt', mode = 'w') as f:
    f.write(text1)
    f.close()

#將剛產出之文字檔,直接開啟顯示出來

subprocess.Popen('explorer "wavfile.txt"')


其它辦識資源 聲音轉換成純文字:
OpenAI (whisper)
 

其它轉換成文字相關工具:
圖檔轉換成文字檔 (Jpg to txt)

2023年12月23日 星期六

以PYTHON執行,經由langchain代理程式介接,使用CHATOPEN AI服務

langchain代理程式介接功能,雷同 瀏覽器add-on外掛程式 或 EXCEL增益集功能概念

下面為PYTHON程式,經由langchain代理程式介接,使用CHATOPEN AI服務

from langchain.chat_models import ChatOpenAI

#llm = ChatOpenAI(openai_api_key="...  此處需將最前面註解#去除,並將您在CHATOPEN產的API金鑰值,貼在此處以便讓您有權存取使用AI服務")

# 大型語言模型LLM 參數匯入糸統
from langchain.llms import OpenAI

# LLM 參數,查詢參數精準度初始化調整
llm = OpenAI(temperature=0.7)

# 輸入您欲查詢之資訊
text = "請摘要說明Langchain Agent,可提供界接雲端服務有哪些 (如:wolfram、chatopenai、serpapi agent)? 請列舉幾個生活上實用範例"

# 經由LLM 及ChatOpenAI服務,將詢問結果產出
prediction = llm(text)
print(prediction.strip())


程式產出結果:  (等同在瀏覽器下 PROMPT提示資訊手動詢問AI:請摘要說明Langchain Agent,可提供界接雲端服務有哪些 (如:wolfram、chatopenai)? 請列舉幾個生活上實用範例)

Langchain Agent是一種人工智能平台,其主要功能是提供界接雲端服務。它可以幫助用戶快速接入各種雲端服務,提供更加便捷和高效的使用體驗。Langchain Agent可以提供的界接雲端服務包括:

1. Wolfram:Wolfram是一個強大的數據分析和計算平台,它可以幫助用戶解決各種數學和科學問題。Langchain Agent可以幫助用戶接入Wolfram,讓用戶可以直接在Agent的界面上進行數據分析和計算,無需打開瀏覽器或下載軟件。

2.ChatOpenAI:它是一個開放式人工智能平台,可以提供自然語言處理、對話式機器人、語言翻譯等功能,開發人員可以通過Langchain AI程式庫輕鬆與ChatOpenAI進行交互,從而實現智能對話機器人等應用。


後記:

一、使用CHATGPT服務需要存取使用權,故需取得API KEY,並貼在程式中 或 設定openai_api_key 變數值於電腦系統環境變數中。

二、匯入必要之函式庫 ,備妥執行環境,如: 

pip install langchain
三、如此PYTHON程式,就可自動化去運用AI功能,而不用瀏覽器去操作詢問了 

 



2023年12月22日 星期五

用PYTHON程式協助products sampling抽驗順位決策


情境:
商品抽驗順位決策參考,

希望依不同部門別,對其業管的商品進行抽樣檢查,
並優先對較久沒被 抽驗過之商品進行查驗,且如果累計抽查次數較多,則將抽驗順位向後調整,
依部門別,每次提供建議抽查之6樣商品


此種用法不限對抽驗商品名稱,亦可用在業務稽核用途;只要將,部門別 換做為總經理室、人事室、會計室 ,而商品名稱 換做 同仁姓名 ,可增加公平性

(抽驗商品資訊如下,有生鮮部、熟食部、及零食部,各自有抽驗過紀錄)

部門   商品名稱  抽查日     累計抽查次
生鮮部    海鮮    1121201       4
生鮮部    貢丸    1121102     2
生鮮部    甜不辣    1121002     1
生鮮部    凍豆腐    1120903     1
生鮮部    花枝丸    1120801        1
生鮮部    黑輪    1120703     1
生鮮部    海鮮    1120603        4
生鮮部    魚丸    1120503     1
生鮮部    海鮮    1121201     1
生鮮部    貢丸    1121102     1
生鮮部    甜不辣    1121002     1
生鮮部    凍豆腐    1120903     1
熟食部    羊肉串    1121201     1
熟食部    雞肉串    1121102     1
熟食部    肉包    1121002     2
熟食部    菜包    1120903     3
熟食部    炒飯    1120801     2
熟食部    炒麵    1120703     1
熟食部    雞塊    1120603        1
熟食部    肉包    1120603     2
熟食部    菜包    1120503       3
熟食部    烤玉米    1120401     4
零食部    花生    1121101     6
零食部    蜜餞    1121001     5



PROMPT提示訊息下法: (AI協助寫PYTHON程式)
針對D:\samplingLIST.csv,每列資料分別為部門別、 商品名稱、 抽檢日、累計抽驗數,
請以PYTHON程式, 協助分析提供下次優先抽驗商品名稱,
依部門別進行分類後,各自提供建議優先抽驗商品名稱,列出6商品,
請以 抽檢日為主要判斷,找出 商品名稱 最少被抽驗到的商品列為最優先抽驗,
以累計抽驗數為次要判斷,亦即次數較高者,則抽驗順位向後

最後,請將結果放置於 D:\samplingOUTPUT.TXT,分別為 每部門別及  分別列出6件建議優先抽驗商品名稱。

產出結果,如下:   (減少漏網出錯,並提昇工作效率)
熟食部
烤玉米
雞塊
炒麵
炒飯
雞肉串
羊肉串


生鮮部
魚丸
黑輪
花枝丸
凍豆腐
甜不辣
貢丸


零食部
蜜餞
花生 


import csv
#自D:\samplingLIST.csv 載入檔案,略過標題列
with open('D:/samplingLIST.csv', newline='', encoding='UTF-8') as csvfile:
    reader = csv.reader(csvfile)
    header = next(reader)  # 略過標題列
    data = [row for row in reader]

# 按部門、檢查日期和累计檢查次數,對資料進行排序
data.sort(key=lambda x: (x[0], x[2], int(x[3])))

# 對分類資料進行反覆運算,並跟蹤每樣產品的檢測次數
inspections = {}
for row in data:
    department = row[0]
    product = row[1]
    if department not in inspections:
        inspections[department] = {}
    if product not in inspections[department]:
        inspections[department][product] = {'inspection_count': 0, 'inspection_dates': set()}
    inspections[department][product]['inspection_count'] += 1
    inspections[department][product]['inspection_dates'].add(row[2])

# 每個部門,提供至少的六個優先檢查產品清單,產出結果到
samplingOUTPUT.TXT  
with open('D:/samplingOUTPUT.TXT', 'w', encoding='UTF-8') as outfile:
    for department in inspections:
        outfile.write(f"\n\n{department}\n")
        sorted_products = sorted(inspections[department], key=lambda x: (len(inspections[department][x]['inspection_dates']), inspections[department][x]['inspection_count']))
        for product in sorted_products[:6]:
            outfile.write(f"{product}\n")

 

後記:

一、一開始是用VBA程式,但TRY了近10次都出現RUNTIME ERROR,改用PYTHON程式,1次就處理完成也沒有BUG,只要抽驗歷史紀錄持續更新,今後哪個最久沒被抽驗商品,也就無所遁形了😏。

二、PYTHON程式要讀檔類型為UTF-8編碼,可透過NOTEPAD++之主選單  編碼(N) | 轉成UTF-8 | 存檔至 D:\samplingLIST.csv

2023年12月16日 星期六

EXCEL VBA常用參考資訊

EXCEL VBA運用框架關鍵資訊

應用程式(Application),Microsoft Excel 是一個應用程式,它可以打開並編輯工作簿,每個工作簿可以包含多個工作表。


工作簿(Workbook):Excel 文件的容器,可以包含多個工作表。一個工作簿可以包含多個工作表,並且可以進行保存、打開和編輯。

工作表(Worksheet):Excel 中的一個分頁,用於方便歸納分類和存儲數據用途頁籤。每個工作簿可以包含多個工作表。工作表包含由列和欄組成的單元格(小方格)。


儲存格(Cell):工作表中的一個方格,用於存儲數據。每個儲存格由列和行交叉形成,可以包含文本、數字、公式等。

 
列(Column):工作表中的垂直方向的一系列儲存格。每一列由字母表示,例如 A、B、C 等。


行(Row):工作表中的水平方向的一系列儲存格。每一行由數字表示,例如 1、2、3 等。

 
範圍(Range):工作表中的一個連續儲存格的集合。範圍可以是單個儲存格,也可以是多個相鄰儲存格的組合,它亦是指定套用公式之範圍 的設定小幫手。


參考範例 (將多個工作簿儲存後,並將它關閉工作簿儲存後,並將它關閉)
For Each wbs In Application.Workbooks
   wbs.Save
Next wbs
Application.Quit

Windows("活頁簿1").Activate          'EXCEL預設開啟,即為工作簿(或活頁簿)
    ActiveWindow.Close


參考範例
ActiveWorkbook.SaveAs "C:\Test.xls"

With Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName = ThisWorkbook.Path & "\"
.Title = "開啟檔案對話窗格"

參考範例
Set ws1 = ThisWorkbook.Worksheets("工作表1")
lastRow1 = ws1.Cells(ws1.Rows.Count, 2).End(xlUp).Row  ' 左邊為列的計數;右邊數字2,代表B欄位,取得最後列的資料筆數,回傳給lastRow1變數


參考範例
Workbooks("Test.xls").Worksheets("工作表1").Range("A1").Value = 88

Range("A1").Select
ActiveCell.Value = 88  '目前儲存格A1,被選取的值,指定為88


執行外部程式(如:可執行預先寫好的Batch批次檔(OOO.bat)或讓EXCEL透過SHELL,直接執行PYTHON程式,如下面範例)

Shell "python c:\DoSomething.py", vbNormalFocus          'EXCEL去執行外部PYTHON應用程式



Application.DisplayAlerts = False
'控制 Excel 應用程式是否顯示警告訊息。
ActiveWindow.SelectedSheets.Delete
'設定刪除選定 頁籤時,不會有警告。


Application.ScreenUpdating = False
'控制 Excel 應用程式是否更新螢幕顯示。

Application.EnableEvents = True
'控制 Excel 應用程式,將會觸發各種事件,例如工作表的更改事件、工作簿的打開和關閉事件等。亦即,當您在 VBA 程式中設置了一個工作表的更改事件,該事件將會被觸發,並且相應的程式碼將被執行。 

Application.CutCopyMode = False
 '清除剪貼簿中的內容

     
Application.StatusBar = "正在更新資料..."
' 進行資料更新的程式碼,於下方狀態列,告知使用者EXCEL仍在更新資料


On Error Resume Next
'當程式碼執行遇到錯誤時,會忽略該錯誤並繼續執行下一行程式碼。

On Error GoTo Label01 
'當程式碼執行遇到錯誤時,會將控制權轉移到指定的錯誤處理程式碼 (Label01:)。




公式(Formula),自動貼在整A1到A100儲存格,貼上公式,省去拖拉之處理時間

Sub DuplicateFormulaFlag()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    
    ' 設定要操作的工作表
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' 修改為你的工作表名稱
    
    ' 設定要操作的範圍
    Set rng = ws.Range("A1:A100")
    
    ' 使用 For Each 迴圈逐一處理每個儲存格
    For Each cell In rng
        ' 將公式貼上儲存格
        cell.Formula = "=IF(COUNTIF(" & rng.Address & "," & cell.Address & ")>1,""重複"",""沒重複"")"
    Next cell
End Sub




檢查File是否存在函數
      Private Function FileExists(fname) As Boolean '判斷Excel檔是否存在
'   Returns TRUE if the file exists
        Dim x As String
        x = Dir(fname)
        If x <> "" Then FileExists = True _
        Else  FileExists = False
End Function


常用函數

VLOOKUP函數,是一個簡單的垂直查找所要查「特定字串值 」,並可回傳查找值在第一列中的匹配,對應相對位置之陣列值
(如:查找姓名,依其對應陣列中同列欄位位置資訊,如:身份證號;或可透過查此函數,與其它工作表之相對資訊,比對查看「特定字串值」,是否為在職人員等)。
INDEX與MATCH函數提供更靈活的查找和檢索功能,可以根據多個條件進行查找並返回更多的結果。





Excel表,第1列為標題列,請在工作表1 ,請協助用VBA程式,將所有 工作表1 中之空白列全數刪除,並以B欄做為判斷資料是否為空值


Sub DeleteActiveEmptyRows()   '刪除  作動中之工作表,所有空白列
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim sheetName As String
    sheetName = ActiveSheet.Name
    
    ' 取得作動中之  工作表
    Set ws = ThisWorkbook.Worksheets(sheetName)
      
    ' 取得最後一列的行數 (2代表,以B欄位做為研判資料筆數基準)
    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
    
    ' 從最後一列往上檢查每一列的B欄是否為空值,如果是則刪除該列
    For i = lastRow To 2 Step -1
        If ws.Cells(i, 2).Value = "" Then
            ws.Rows(i).Delete
        End If
    Next i
End Sub


進階資訊:

EXCEL 可自行撰寫類別元件,檔案類型為.cls,經由模組(Module)中之巨集程式,將其呼叫執行
例如下面為AI,撰寫出64位元上網VBA CLASS元件,所以EXCEL執行如RS-232 數據通訊CLASS元件應該也不是難事
Private Declare PtrSafe Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" (ByVal Agent As String, ByVal AccessType As LongPtr, ByVal ProxyName As String, ByVal ProxyBypass As String, ByVal Flags As LongPtr) As LongPtr

Private Declare PtrSafe Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" (ByVal hInternetSession As LongPtr, ByVal ServerName As String, ByVal ServerPort As Integer, ByVal UserName As String, ByVal Password As String, ByVal Service As LongPtr, ByVal Flags As LongPtr, ByVal Context As LongPtr) As LongPtr
    

Private Declare PtrSafe Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As LongPtr) As Boolean
    

Private Declare PtrSafe Function InternetReadFile Lib "wininet.dll" (ByVal hConnect As LongPtr, ByVal Buffer As String, ByVal NumberOfBytesToRead As LongPtr, NumberOfBytesRead As LongPtr) As Boolean
    

Private Declare PtrSafe Function HttpOpenRequest Lib "wininet.dll" Alias "HttpOpenRequestA" (ByVal hHttpSession As LongPtr, ByVal Verb As String, ByVal ObjectName As String, ByVal Version As String, ByVal Referer As String, ByVal AcceptTypes As LongPtr, ByVal Flags As LongPtr, Context As LongPtr) As LongPtr


串列通訊傳輸元件

Private Declare PtrSafe Function BuildCommDCB Lib "kernel32" Alias "BuildCommDCBA" _
    (ByVal lpDef As String, lpDCB As DCB) As Long

    

PROMPT提示訊息下法: 

請寫VBA程式,運用上面元件(BuildCommDCBA),執行RS-232傳輸測試

文字檔有網頁開啟清單,用PYTHON程式,將網頁全數開啟,並找出特定字串,將其另存至文字檔中

情境說明:

PROMPT提示訊息下法:   (範例2) 

D:/HyperLinkSource.txt 此檔案內含連結位址,請用PYTHON寫程式,CHROME瀏覽器,開啟該文字檔案全數連結,並按任意鍵繼續瀏覽下個頁面


import webbrowser

# 读取文本文件中的链接
file_path = 'D:/HyperLinkSource.txt'

with open(file_path, 'r') as file:
    # 逐行读取链接
    for line in file:
        # 移除行尾的换行符
        url = line.strip()
        
        # 使用webbrowser模块打开链接
        webbrowser.open(url)
        
        # 等待用户按下任意键继续
        input("Press Enter to continue...")
         
 

 

PROMPT提示訊息下法:      (範例2) 

D:\HyperLinkSource.txt 此檔案內含連結位址,請用PYTHON寫一程式,運用requests與BeautifulSoup模組,以預設瀏覽器,開啟該文字檔案提供全數連結

,並請協助各自讀取每個連結之網頁內容後,將網頁內容中有包含  詳洽本公司: 開始 到  < 回訊息列表   間的字串值 將字串值取出至D:\BrowsingString.txt ,

該檔如存在請刪除,重新建檔,且該檔包含 該連結位址 及 網頁內容之特定字串,如找不到資料詳洽之特定字串,則用 找不到特定字串, 處理,並請接續處理下一筆連結之瀏覽檢視作業

import os
import requests
from bs4 import BeautifulSoup

# 讀取連結位址檔案
file_path = r'D:\HyperLinkSource.txt'
with open(file_path, 'r') as file:
    links = file.readlines()

# 刪除 output_file 若存在
output_file = r'D:\BrowsingString.txt'
if os.path.exists(output_file):
    os.remove(output_file)

# 開啟瀏覽器並處理每個連結
for link in links:
    link = link.strip()  # 移除連結前後的空白字符
    try:
        # 使用requests獲取網頁內容
        response = requests.get(link)
        response.raise_for_status()  # 確保獲取網頁成功
        webpage_content = response.text

        # 使用BeautifulSoup解析網頁內容
        soup = BeautifulSoup(webpage_content, 'html.parser')

        # 尋找特定字串值
        start_marker = '詳洽本公司:'
        end_marker = '< 回訊息列表'
        start_index = webpage_content.find(start_marker)
        end_index = webpage_content.find(end_marker)

        # 提取特定字串值
        if start_index != -1 and end_index != -1:
            substring = webpage_content[start_index + len(start_marker):end_index].strip()
        else:
            substring = '@@ 找不到特定字串'

        # 將特定字串值寫入檔案
        with open(output_file, 'a', encoding='utf-8') as file:
            file.write(f'{link}\n')
            file.write(f'{substring}\n\n\n')
    except requests.exceptions.RequestException as e:
        print(f'無法獲取連結:{link}')
        print(f'錯誤訊息:{e}')

後記:取出的網頁資訊,仍保留許多HTML語法資訊,故如要估資料再次運用,可能此方法不是最佳方式,只是測試PYTHON程式,捉取網頁內容特定2關鍵字,將其抽取出至另1個文字檔D:\BrowsingString.txt

EXCEL 同產品名稱 ,銷售金額 ,用VBA程式查詢


EXCEL表中,分別有訂單編號、訂購日期、產品名稱、單價、數量、小計等欄位,
想瞭解下表中  同一產品名稱 ,其銷售一共多少錢?


   A          B            C         D       E        F   
訂單編號    訂購日期    產品名稱    單價    數量    小計
A0001        112/5/1        鉛筆           20        6        120
A0002        112/6/6        原子筆        30        2        60
A0003        112/7/7        剪刀           46        2        92
A0004        112/6/8        美工刀        40        2        80
A0005        112/8/9        鉛筆           20        4        80
A0006        112/6/10     橡皮擦        15        2        30
A0007        112/9/9        精美提袋    80        3        240
A0008        112/9/12      原子筆        30        4        120
A0009        112/10/13    剪刀           46        2        92
A0010        112/11/15    鉛筆           20        2        40


藉由AI(ChatGPT)協助將同一產品名稱,歸納成同一群組後,彙總成單筆銷售數量。
PROMPT提示訊息下法:
EXCEL表中,有訂單編號、訂購日期、產品名稱、單價、數量、小計欄,請以EXCEL VBA程式,將相同 產品名稱 進行排序,歸納為同一群組,並將 相同產品名稱 彙集成1筆 單價及數量,
結果分別存放至    I2欄為產品名稱 J2欄為單價 K2欄為相同品項數量加總


Sub 彙集產品資料()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim productName As String
    Dim price As Double
    Dim quantity As Long
    Dim summaryRow As Long

    ' 設定工作表,這裡假設資料在第一個工作表
    Set ws = ThisWorkbook.Sheets(1)

    ' 找到最後一行
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' 初始化目標列
    summaryRow = 2 ' 假設第一行是標題,從第二行開始

    ' 按照產品名稱進行排序
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=ws.Range("C2:C" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.Sort
        .SetRange ws.Range("A1:F" & lastRow)
        .Header = xlYes ' 假設有標題行
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    ' 循環處理每一行資料
    For i = 2 To lastRow ' 假設第一行是標題,從第二行開始

        ' 取得產品名稱
        productName = ws.Cells(i, 3).Value ' 假設產品名稱在第3欄

        ' 取得單價和數量
        price = ws.Cells(i, 4).Value ' 假設單價在第4欄
        quantity = ws.Cells(i, 5).Value ' 假設數量在第5欄

        ' 如果是相同的產品名稱,則彙集數據
        If productName = ws.Cells(summaryRow, 9).Value Then
            ' 加總數量
            ws.Cells(summaryRow, 11).Value = ws.Cells(summaryRow, 11).Value + quantity
        Else
            ' 新的產品,將結果寫入新的一行
            summaryRow = summaryRow + 1
            ws.Cells(summaryRow, 9).Value = productName ' 產品名稱
            ws.Cells(summaryRow, 10).Value = price ' 單價
            ws.Cells(summaryRow, 11).Value = quantity ' 數量
        End If
    Next i

End Sub



請按照以下步驟使用此程式:
STEP1:開啟您的Excel檔案。
STEP2:按下Alt + F11進入VBA編輯器。
STEP3:在VBA編輯器中,插入一個新的模組Module。
STEP4:將上述VBA程式碼,複製並貼上到新的模組Module中。
STEP5:在VBA編輯器中,按下F5運行程式。


相關可運用AI工具輔助寫程式資源


VBA程式,如何將2個特定字串間之字串取出

情境說明:
Excel表,第一列為標題列,A欄為空白,B欄為超連結文字,C欄為超連結網址(如下所示)

        A                         B                                        C
1                    examples1公司超連結簡介    https://www.example1.com.tw/Link?pageId=12345&type

想將C欄中,取出 pageId= &type 間,之數字 取出,並將它複製至 A欄位中,應該如何做


PROMPT提示訊息下法:
excel  表,第一列為標題列,請用VBA程式,將C欄位中,pageId=  右邊字串,到 &type=  為止之字串,複製到A欄位

Sub CopyString()   ' 取出網址列特定編號,自C欄位,複製至 A欄位
    Dim lastRow As Long
    Dim i As Long
    Dim str As String

    ' 取得最後一列的行數
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row

    ' 逐行處理,從第二列開始
    For i = 2 To lastRow
        ' 判斷C欄位中是否包含 "pageId="
        If InStr(1, Cells(i, "C").Value, "pageId=") > 0 Then
            ' 取得 "pageId=" 右邊的字串
            str = Mid(Cells(i, "C").Value, InStr(1, Cells(i, "C").Value, "pageId=") + Len("pageId="))
            ' 判斷是否包含 "&type="
            If InStr(1, str, "&type=") > 0 Then
                ' 取得 "&type=" 左邊的字串
                str = Left(str, InStr(1, str, "&type=") - 1)
            End If
            ' 將字串複製到A欄位
            Cells(i, "A").Value = str
        End If
    Next i
End Sub

2023年12月10日 星期日

延續先前分公司,各自單獨建立分頁  之範例

Excel 分頁過多(分公司多,以致於移動分頁困擾),希望於每個分頁的K1儲存格(即各個分公司 分頁K1儲存格),
再設定一個回總索引頁(即「 各分公司索引清單」)


Perplexity.ai
藉由Perplexity AI(ChatGPT)協助將每個各自獨立分公司分頁中的K1儲存格,建立回總索引頁(即「 各分公司索引清單」)之超連結,方便作業。
PROMPT提示訊息下法:
EXCEL表,有多個分頁,其中 各分公司索引清單,已有指向各分頁之超連結,
請用EXCEL VBA程式,幫忙於 各分頁中 K1儲存格,增設  各分公司索引清單 超連結 ,並將K1更名為  回索引清單。


Sub AddHyperlinkAndRename()
    Dim ws As Worksheet
    Dim wsRef As Worksheet
    Dim LastRow As Long
    Dim LastCol As Long
    Dim i As Long
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "各分公司索引清單" Then
            LastRow = ws.Cells(ws.Rows.Count, "K").End(xlUp).Row
            LastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
            
            For i = 2 To LastRow
                ws.Range(ws.Rows.Count, i).Value = ""
                ws.Range(ws.Rows.Count, i).EntireRow.Hidden = True
            Next i
            
            ws.Range("K1").Value = "回索引清單"
          
            
              ' 在各分頁的K1儲存格增設超連結
              
              ws.Range("K1").Hyperlinks.Add Anchor:=ws.Range("K1"), Address:="", SubAddress:="'" & "各分公司索引清單" & "'!A1"
                    
            
        End If
    Next ws
End Sub


請按照以下步驟使用此程式:
STEP1:開啟您的Excel檔案。
STEP2:按下Alt + F11進入VBA編輯器。
STEP3:在VBA編輯器中,插入一個新的模組Module。
STEP4:將上述VBA程式碼,複製並貼上到新的模組Module中。
STEP5:在VBA編輯器中,按下F5運行程式。

用EXCEL VBA程式,各自單獨建立分公司分頁表,並增設超連結方便索引

因業務需要,有5個分公司(甲~戊)業務要管,想用EXCEL表,

提供給各單位填報回傳資料進行管理,並以分公司名稱 各自單獨建立1個工作表,

同時增設超連結方便索引 。

EXCEL表已先建立好各分公司名稱於A欄位中,如下:
         A
1  分公司名稱  
2  甲分公司
3  乙分公司
4  丙分公司
5  丁分公司
6  戊分公司


藉由AI(ChatGPT)協助建立各自建立分公司獨立分頁,並提供超連結設置,方便檢索


PROMPT提示訊息下法:
EXCEL表中,自A2儲存格起,有多個分公司 放於A欄位中,請提供VBA程式,幫忙建立 分公司分頁 ,並且設置超連結,方便檢索



Sub CreateCompanySheets()
    Dim ws As Worksheet
    Dim company As Range
    
    ' 在第一個工作表中建立一個名為 「 各分公司索引清單」的分頁
    Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "各分公司索引清單"
    
    ' 在「 各分公司索引清單」分頁中設置標題
    ws.Range("A1").Value = "分公司"
    
    ' 從A2儲存格起,逐一遍歷分公司名稱
    For Each company In ThisWorkbook.Sheets(1).Range("A2:A" & ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row)
        ' 檢查分公司分頁是否已存在
        If Not SheetExists(company.Value) Then
            ' 如果分公司分頁不存在,則建立一個新的分頁
            ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = company.Value
        End If
        
        ' 在「 各分公司索引清單」分頁中設置超連結
        ws.Hyperlinks.Add Anchor:=ws.Range("A" & ws.Cells(Rows.Count, 1).End(xlUp).Row + 1), _
                          Address:="", _
                          SubAddress:="'" & company.Value & "'!A1", _
                          TextToDisplay:=company.Value
    Next company
    
    ' 回到  第一個工作表
    ThisWorkbook.Sheets(1).Activate

End Sub

Function SheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    
    SheetExists = False
    For Each ws In ThisWorkbook.Sheets
        If ws.Name = sheetName Then
            SheetExists = True
            Exit Function
        End If
    Next ws
End Function


請按照以下步驟使用此程式:

STEP1:開啟您的Excel檔案。
STEP2:按下Alt + F11進入VBA編輯器。
STEP3:在VBA編輯器中,插入一個新的模組Module。
STEP4:將上述VBA程式碼,複製並貼上到新的模組Module中。
STEP5:在VBA編輯器中,按下F5運行程式。

VBA程式,將會在您的Excel檔案中建立一個名為「 各分公司索引清單」的分頁,並在該分頁中列出 所有分公司的超連結。點擊超連結即可跳轉到相對應的分公司 分頁 。

2023年12月9日 星期六

將多個不同分公司EXCEL成本花費表,合併成單一EXCEL總表

總公司有許多分公司,甲、乙、丙分公司,各自維護獨立 成本花費EXCEL報表,每月需匯整成單一EXCEL總表中


甲、乙、丙分公司各自有成本花費EXCEL報表,且它們成本花費報表  ,其首欄位都有相同花費成本細項,但欄位放置位置都不一定相同


匯整甲、乙、丙分公司,成本花費EXCEL報表欄位全數欄位有    地點,姓名,加班費,差旅費,雜費,租賃費,其它 等欄位


所以,總公司,每月均需匯整甲、乙、丙分公司之成本花費EXCEL報表,合併成單一 EXCEL成本花費總表

To merge the three Excel files into a single file named "MergeOK.xlsx" in the "總公司" folder which located in the D drive,

藉由AI(ChatGPT)寫合併EXCEL
PROMPT提示訊息下法:
在D槽中有 總公司 資料夾,存放3個EXCEL檔,每份表格的首欄為標題,分別有地點,姓名,加班費,差旅費,雜費,租賃費,其它 欄位,每個表格的首欄標題位置皆不同,請寫PYTHON程式
,將3個EXCEL表格,合併成 MergeOK.XLSX



import pandas as pd

# File paths 甲分公司檔名為file1.xlsx 、乙分公司檔名為file2.xlsx、丙分公司檔名為file3.xlsx


folder_path = 'D:/總公司/'
file_names = ['file1.xlsx', 'file2.xlsx', 'file3.xlsx']

# Read each Excel file into a DataFrame
dfs = []
for file_name in file_names:
    file_path = folder_path + file_name
    df = pd.read_excel(file_path)
    dfs.append(df)

# Merge the DataFrames
merged_df = pd.concat(dfs)

# Save the merged DataFrame to a new Excel file
output_file = folder_path + 'MergeOK.xlsx'
merged_df.to_excel(output_file, index=False)



相關查詢:
How to join Excel data from Multiple files?

複数のExcelファイルを1つにまとめる方法?


後記:
合併多個EXCEL檔成單一檔方法很多,可以用EXCEL本身功能,或用VBA、或用PYTHON,如果要改成VBA,本範例是用PYHTON,如要使用VBA,只要將PROMPT提示訊息下法,將上面  請寫PYTHON程式  ,改成 EXCEL VBA程式即可

2023年12月8日 星期五

用PYTHON程式,對Disk Analysis磁碟空間剖析

隨著儲存空間愈做愈大,如果沒有做管理將造成備份時間、儲存空間成本的增加、使用效能下降等,因此對磁碟儲存狀態剖析管理就相對重要,可透過PYTHON程式,對磁碟機進行分析檔案目錄、檔名、修改日期、檔案大小進行分析,將結果產出至OUTPUT.TXT,方能掌控磁碟空間之使用(以下為對D槽進行分析)。

import os
from datetime import datetime, timedelta

def analysis_directory(path):
    with open('OUTPUT.TXT', 'w', encoding='UTF-8') as f:
        for root, dirs, files in os.walk(path):
            for file in files:
                file_path = os.path.join(root, file)
                file_size = os.path.getsize(file_path)
                file_date = os.path.getmtime(file_path)
                file_date_str =
datetime.fromtimestamp(file_date).strftime('%Y/%m/%d %H:%M:%S')
                f.write(f'{file_path}\t{file_date_str}\t{file_size}\n')

analysis_directory('d:\\')   
   # 將 'D:\' 改為您想要剖析的磁碟機路徑

 

備註:

上面程式,測試如有型態錯誤訊息,可做調整

#程式開頭,另再加入此行
import time 

#請將datetime物件,替換成下面time物件

datetime.fromtimestamp(file_date).strftime('%Y/%m/%d %H:%M:%S')

#改用time物件

time.strftime('%Y-%m-%d %H:%M:%S')

########################################################

除上面剖析方式外,可搭配DOS指令 TREE 樹狀圖型結構,快速檢視檔案架構

步驟一: 開始  | 執行 | cmd   ,進入DOS 命令提示列

步驟二:  tree /f d: > d:\TreeView.txt     

(將D槽檔案結構,結果產出至   d:\TreeView.txt     )




 

2022年8月19日 星期五

Json2Csv(JavaScript Object Notation轉換成CSV格式)

 作法1:( react-gh-pages離線套件)
將JSON格式轉換成CSV純文字讀取格式工具
步驟1:下載解壓縮 react-gh-pages
步驟2:開啟json-gh-pages資料夾下之 index.html
步驟3:將JSON格式貼於上方視窗,即可轉換成 CSV純文字格式 (預設20筆,點選Download the entire CSV)



作法2:(搭配Notepad++ 外掛工具)
經由Notepad++外掛方式(Plug-in)


作法3:(Python程式)

#'開啟檔案總管 (開啟轉換後之檔案使用)'

import subprocess

import os

import pandas as pd

#'開啟GUI 取得來源檔(準備捉取JSON之來源檔案路徑)

import tkinter as tk

from tkinter import filedialog

root = tk.Tk()


file_path = filedialog.askopenfilename(initialdir = "/",title = "Select file for JSON to csv(選擇欲處理之JSON檔案)",filetypes = (("Json files","*.json"),("Text files","*.txt"),("all files","*.*")))

df = pd.read_json (open(file_path, "r", encoding="utf8"))

print(df)  # 顯示讀取出之DataFrame資訊

input ("Press any key to continue!")


df.to_csv (r'd:\json2csv\Convert_Json_File.csv', index = None)

#將剛產出之CSV文字檔,直接開啟顯示出來

subprocess.Popen('explorer "d:\json2csv\Convert_Json_File.csv"')

2022年1月13日 星期四

以PowerShell指令查詢Outlook之GAL全域通訊清單


步驟1:執行cmder程式,
步驟1-1:按+ Create new console
步驟1-2:選1:{Shells}
步驟1-3:選6:{PowerShells(Admin)}


步驟2: 將下列指令,逐行貼上 (註解: 

第3行  | where {$_ -match '@taipei.abc.com.tw'}  表示篩選ABC公司之台北分支辦公室 、

           | Sort-Object -Property department  以部門別排序後再產出結果  )

 

$OUTLOOK = New-Object -ComObject "Outlook.application"

$Galist=$outlook.getnamespace("MAPI").AddressLists.Item("全域通訊清單").AddressEntries

$Galist|%{$_.GetExchangeUser() |select companyname,department,Name,PrimarySmtpAddress,BusinessTelephoneNumber,JobTitle} | where {$_ -match '@taipei.abc.com.tw'}  | Sort-Object -Property department > c:\users\username\Gal_Taipei.txt


步驟3:檢視C槽資料夾,產出結果 c:\users\username\Gal_Taipei.txt


相關介面:
cmder命令提示字元(PowerShell)


相關GAL查詢
EXCEL VBA執行GAL查詢



2022年1月12日 星期三

正規表示式Regular Expression ( FQDN filter)

 將純文字格式下,篩選出FQDN (Domain Name名稱)

TEXTのうちドメイン名までを正規表現を用いて抽出する(FQDN)
 Regular expression which will match a valid domain name

 

背景說明:

公司因業務需要,原始來源為PDF格式,需將其內文中之FQDN網域位址,提取出來。可先經由pdftotext將PDF檔,轉換成TEXT格式後,再經由grep指令將FQDN抽出。


下列為BATCH批次檔

 
rem  將PDF檔先轉換成暫存純文字檔案
pdftotext %1 txtTempPDF.txt

rem 將PDF轉換完成為TEXT文字檔後,經由grep工具取出FQDN網域相關資訊
grep -E '[a-zA-Z0-9.\-_]{1,63}\.[a-zA-Z0-9.\-_]{1,63}' txtTempPDF.txt  -o > Tipip_FQDN.txt

rem 開啟經由grep篩選出之FQDN資訊
explorer Tipip_FQDN.txt

 

相關篩選IP參考資訊:

 (OpenSource)Pdftotext  ,PDF文字轉換工具

https://myblog-johnnyit.blogspot.com/2021/09/pdfip.html

 

2021年9月27日 星期一

PDF檔轉換成純文字檔,並抽取出IP資訊

Regular expression IPv4 addresses cover the range 0.0.0.0 to 255.255.255.255
IPv4 アドレスにマッチする正規表現

步驟一:

備妥PDF轉換成TEXT環境套裝程式

依作業系統環境,選擇下載套裝程式
https://miktex.org/download

步驟二:
安裝執行basic-miktex
備妥PDF轉換純文字程式,PDFTOTEXT

步驟三:
下列為BATCH批次檔
rem  將PDF檔先轉換成暫存純文字檔案
pdftotext %1 txtTempPDF.txt

rem 將PDF轉換完成為TEXT文字檔後,經由grep工具取出IP資訊
grep -E '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}' txtTempPDF.txt  -o > Tipip.txt

rem 開啟經由grep篩選出之IP資訊
explorer Tipip.txt



參考資訊:
grep 過濾篩選指令

Windows環境,直接執行UnixLike指令

Utility programs (e.g. "grep") in the cygwin bash environment 相關執行檔案


2020年9月16日 星期三

auto-py-to-exe 轉換PY程式成為EXE

Step1:

安裝執行auto-py-to-exe圖形GUI界面之PYTHON轉換EXE套件程式

Step2:

將前個PYTHON程式( PYTHON旋轉PDF之程式載入),即可產出EXE執行檔


參考資訊:

auto-py-to-exe PYTHON轉換成EXE執行檔套件


PYTHON提供旋轉rorate PDF

下面為PYTHON自訂旋轉PDF (將下列紅色部分,貼在Notepad記事本,另存成RoratePDF.py)

 # importing the required modules彙入相關必要之模組 

import PyPDF2 

import sys

#'開啟檔案總管 (開啟轉換後之檔案使用)'

import subprocess

#'開啟GUI 取得來源檔(找出欲旋轉之PDF檔案)

import tkinter as tk

from tkinter import filedialog


root = tk.Tk()

root.withdraw()


FilePathName = filedialog.askopenfilename(initialdir = "/",title = "Select file for rotate (選擇欲旋轉之PDF檔案)",filetypes = (("PDF files","*.pdf"),("all files","*.*")))


  

def PDFrotate(origFileName, newFileName, rotation): 

  

    # creating a pdf File object of original pdf 

    pdfFileObj = open(origFileName, 'rb') 

      

    # creating a pdf Reader object 

    pdfReader = PyPDF2.PdfFileReader(pdfFileObj) 

  

    # creating a pdf writer object for new pdf 

    pdfWriter = PyPDF2.PdfFileWriter() 

      

    # rotating each page 

    for page in range(pdfReader.numPages): 

  

        # creating rotated page object 

        pageObj = pdfReader.getPage(page) 

        pageObj.rotateClockwise(rotation) 

  

        # adding rotated page object to pdf writer 

        pdfWriter.addPage(pageObj) 

  

    # new pdf file object 

    newFile = open(newFileName, 'wb') 

      

    # writing rotated pages to new file 

    pdfWriter.write(newFile) 

  

    # closing the original pdf file object 

    pdfFileObj.close() 

      

    # closing the new pdf file object 

    newFile.close() 

      

  

def main(): 

  

    # original pdf file name 

    origFileName = FilePathName

     

    # new pdf file name 

    newFileName = 'rotated_example.pdf'

      

    # rotation angle 自訂旋轉角度

    rotation_data = int(input("Enter a rotation angle(90、180): "))

      

    # calling the PDFrotate function 

    PDFrotate(origFileName, newFileName, rotation_data) 

      

if __name__ == "__main__": 

    # calling the main function 

    main() 


2014年11月24日 星期一

to periodically Search file in php on web pages


<   ? php
$url = 'http://www.example.com.tw/it/unnessaryfile.doc';
$array = get_headers($url);
$string = $array[0];
if(strpos($string,"200"))
  {
    echo 'url exists';
    $myfile = fopen("C:/Users/%username%/Desktop/Server_found_unnessaryfile.txt", "w") or die("Unable to open file!");
    $txt = "請檢查Server,同步到非必要檔案至網頁?????????\n";
    fwrite($myfile, $txt);
    fclose($myfile);
  }
  else
  {
    echo 'url does not exist';
  }
?  >
 上面網頁存成 URLCheck.php



存成ServerFileCheck.bat檔,讓開機時,自動呼叫執行  URLCheck.php  ,每天開機時自動檢查,是否有多餘非必要檔案(Server_found_unnessaryfile.txt)。

%comspec% /c php "E:\Apache\htdocs\URLCheck.php" 




開機啟動執行機碼,自動呼叫執行  E:\ServerFileCheck.bat
REG ADD "HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Run" /V "URLCheck_php" /t REG_SZ /F /D "E:\ServerFileCheck.bat"