學藝比賽Code

Jen Link 重新命名Use Google Scipt

function FileUrlList() {
  // 將 your_folder_name 替換為實際的資料夾名稱
  var theFolder = "HKSAC";

  // 將 your_spreadsheet_name 替換為實際的試算表名稱
  var ssName = "123";

  var dirID = DriveApp.getFoldersByName(theFolder).next().getId();
  var SS = SpreadsheetApp.create(ssName).getSheets()[0];

  // 獲取指定資料夾中的檔案
  var files = DriveApp.getFolderById(dirID).getFiles();
  var row = 1;

  // 列出所有檔案及其共用連結
  while (files.hasNext()) {
    var file = files.next();
    Logger.log(file.getName() + " => " + file.getUrl());
    SS.getRange(row, 1).setValue(file.getName());
    SS.getRange(row, 2).setValue(file.getUrl());
    row++;
  }
  
  SpreadsheetApp.flush();
}

建立QR CodePython

import requests

data_list = ['data1', 'data2', 'data3'] 

for data in data_list:
    response = requests.get(f'https://api.qrserver.com/v1/create-qr-code/?size=250x250&data={data}')
    with open(f'd:\\{data}.png', 'wb') as f:
        f.write(response.content)

import requests

# 從 data.txt 檔案中讀取資料
with open('data.txt', 'r', encoding='utf-8') as file:
    data_list = [line.strip() for line in file.readlines()]  # 讀取每行並去除換行符

# 生成二維碼並保存為 PNG 檔案
for data in data_list:
    response = requests.get(f'https://api.qrserver.com/v1/create-qr-code/?size=250x250&data={data}')
    with open(f'd:\\{data}.png', 'wb') as f:
        f.write(response.content)

A For P1-P3
A1=有得獎  (甲.學藝比賽得獎學生)
A2=表演 (乙.表演隊伍)

eg.A1+1A+7+NO  A11A71
eg.A2+4B+1+NO  A24B151

//="A1/A2/B1/B2"&A2&B2

B For P4-P6
B1=有得獎  (甲.學藝比賽得獎學生)
B2=表演 (乙.表演隊伍)

=IF(COUNTIF(B:B, B1) = 2, "QR-Code失效", "")

import os
import pandas as pd
import shutil

# 設置文件路徑
excel_file = r'C:\Users\JackFung\Music\@1+2 to eclass.xlsx'  # 修改為你的 Excel 文件路徑
png_source_folder = r'C:\Users\JackFung\Music\P1-P3'  # 修改為你的 PNG 源文件夾路徑

# 讀取 Excel 文件
df = pd.read_excel(excel_file)

# 遍歷 B 列創建文件夾並移動 PNG 文件
for index, row in df.iterrows():
    folder_name = row['KEY']  # A 列名稱
    png_name = row['Code'] + '.png' # B 列名稱，假設 PNG 文件名與 B 列名稱一致
    
    # 創建文件夾
    new_folder_path = os.path.join(png_source_folder, folder_name)
    os.makedirs(new_folder_path, exist_ok=True)

    # 移動 PNG 文件
    png_file_path = os.path.join(png_source_folder, png_name)
    if os.path.isfile(png_file_path):
        shutil.move(png_file_path, new_folder_path)
    else:
        print(f"文件 {png_name} 不存在。")

print("完成！")