再生能源.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getpvdata()-> pd.DataFrame:\n",
    "    #取得http://10.60.59.63:8501/remsweb/apply/rems01022/home 已受理案件資料\n",
    "    \"\"\"\n",
    "    至RNIS取得目前已備案資料\n",
    "    \"\"\"\n",
    "    import requests\n",
    "    import json\n",
    "    import pandas as pd\n",
    "    import xlwings as xw\n",
    "    url = \"http://10.60.59.63:8501/remsapi/apply/rems01022/query\"\n",
    "    payload = {\"branchid\":\"106\",\"energytype\":\"\",\"status\":\"\",\"wholesaleType\":\"\",\"query\":\"false\",\"export\":\"false\",\"count\":\"10\"}\n",
    "    headers = {\"Accept\":\"application/json, text/javascript, */*; q=0.01\",\n",
    "    \"Accept-Encoding\":\"gzip, deflate\",\n",
    "    \"Accept-Language\":\"zh-TW,zh;q=0.9,en-US;q=0.8,en;q=0.7\",\n",
    "    \"Connection\":\"keep-alive\",\n",
    "    \"Content-Length\":\"107\",\n",
    "    \"Content-Type\":\"application/json;charset=UTF-8\",\n",
    "    \"Cookie\":\"JSESSIONID=Krff1T_F8Yq_dXVy-KPFANv4a63SNTQARz6iRPlh-ZABhBFa0Q0C!-1373575639;TS0122fc2b=0101fa365134e6a9664b011c7db74be257d90f02002fd7336305277231539d635bdacd3f83515976fb983bf53931804f28162facd3d14d10f56d6cfae22ab5e868d4ccd523\",\n",
    "    \"Host\":\"10.60.59.63:8501\",\n",
    "    \"Nbsdarcd\":\"08\",\n",
    "    \"Nbsuser\":\"457984\",\n",
    "    \"Origin\":\"http://10.60.59.63:8501\",\n",
    "    \"Referer\":\"http://10.60.59.63:8501/remsweb/apply/rems01022/home\",\n",
    "    \"User-Agent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36\",\n",
    "    \"X-Requested-With\":\"XMLHttpRequest\"}\n",
    "\n",
    "    res = requests.post(url,data=json.dumps(payload),headers=headers)\n",
    "    #res.text 轉為json再轉為dataframe\n",
    "    res_json = json.loads(res.text)\n",
    "    df = pd.DataFrame(res_json[\"data\"])\n",
    "    #drop approvedid \n",
    "    df.drop(columns=[\"approvedid\"],inplace=True)\n",
    "    #Index(['branchid', 'descr', 'approvedid', 'formid', 'applicant', 'srcAddress','deConta', 'wholesaleContain', 'wholesaleDemand', 'contractDate','inpalDate', 'runDate', 'memo'],dtype='object')\n",
    "    columns = [\"區處別\",\"設置型別\",\"受理編號\",\"設置者\",\"設置地點\",\"裝置\\n容量\",\"躉售容量\",\"躉售\\n方式\",\"簽約日期\",\"併聯日期\",\"運轉中抄日\",\"備註說明\"]\n",
    "    df.columns = columns\n",
    "    return df\n",
    "# try:\n",
    "#     df.to_excel(\"再生能源-已受理案件.xlsx\",index=False)\n",
    "# except:\n",
    "#     print(\"再生能源-已受理案件.xlsx 被開啟\")\n",
    "#     wb = xw.Book(\"再生能源-已受理案件.xlsx close\")\n",
    "#     #關閉再生能源-已受理案件.xlsx\n",
    "#     wb.close()\n",
    "#     df.to_excel(\"再生能源-已受理案件.xlsx\", index=False)\n",
    "\n",
    "#tpcmeter 台電電號　url =http://10.60.59.63:8501/remsapi/apply/rems01019/query02Part02 method = post payload={\"formid\":\"106100PV0003\"}\n",
    "def tpcmeter(pvnumber:str)->str:\n",
    "    \"\"\"\n",
    "    輸入PV編號取得電號\n",
    "    \"\"\"\n",
    "    import json\n",
    "    import requests\n",
    "    headers = {\n",
    "    \"Accept\":\"application/json, text/javascript, */*; q=0.01\",\n",
    "    \"Accept-Encoding\":\"gzip, deflate\",\n",
    "    \"Accept-Language\":\"zh-TW,zh;q=0.9,en-US;q=0.8,en;q=0.7\",\n",
    "    \"Connection\":\"keep-alive\",\n",
    "    \"Content-Length\":\"25\",\n",
    "    \"Content-Type\":\"application/json;charset=UTF-8\",\n",
    "    \"Cookie\":\"JSESSIONID=qcxUH5FxkgHtPfuGty4ltxxcR95wOEd5IpGwyZTBk5Ozy4FEfylr!-1016557022; TS0122fc2b=0101fa36510d33fabb242a3f3ee227eaf8b92cfa5f4796c2ece4e36122ec24a5f62f29823158068f6492ac17d65db87be54808a6448ee6c36914067d31bc8df06bd2a3a9f7\",\n",
    "    \"Host\":\"10.60.59.63:8501\",\n",
    "    \"Nbsdarcd\":\"08\",\n",
    "    \"Nbsuser\":\"457984\",\n",
    "    \"Origin\":\"http://10.60.59.63:8501\",\n",
    "    \"Referer\":\"http://10.60.59.63:8501/remsweb/apply/rems01019/home\",\n",
    "    \"User-Agent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36\",\n",
    "    \"X-Requested-With\":\"XMLHttpRequest\"}\n",
    "\n",
    "    url = \"http://10.60.59.63:8501/remsapi/apply/rems01019/query02Part02\"\n",
    "\n",
    "    payloads = json.dumps({\"formid\":pvnumber})\n",
    "    res = requests.post(url,headers=headers,data=payloads)\n",
    "    tpc_meter = json.loads(res.text)[\"data\"][0][\"tpcmeter\"]\n",
    "               \n",
    "    return tpc_meter\n",
    "\n",
    "\n",
    "#以台電電號 取得座標 http://10.210.35.218/DMQService/api/DistributedCustomerInformation/GetDistributedCustomerInformation\n",
    "def meternumber_to_tpcdata(meternumber)->tuple:\n",
    "    \"\"\"\n",
    "    回傳三個資料\n",
    "    1.ownertpclid 台電座標\n",
    "    2.feeder(codetet) 饋線代號\n",
    "    3.tpc_address(address) 台電桿號\n",
    "    \"\"\"\n",
    "    #檢查meternumber是否為11碼\n",
    "    if len(meternumber)<11:\n",
    "        meternumber = \"0\"+str(meternumber)\n",
    "\n",
    "    import requests\n",
    "    import json\n",
    "    headers = {\n",
    "    \"Accept\":\"application/json, text/plain, */*\",\n",
    "    \"Accept-Encoding\":\"gzip, deflate\",\n",
    "    \"Accept-Language\":\"zh-TW,zh;q=0.9,en-US;q=0.8,en;q=0.7\",\n",
    "    \"Connection\":\"keep-alive\",\n",
    "    \"Content-Length\":\"138\",\n",
    "    \"Content-Type\":\"application/json\",\n",
    "    \"Host\":\"10.210.35.218\",\n",
    "    \"Origin\":\"http://10.210.35.218\",\n",
    "    \"Referer\":\"http://10.210.35.218/DMQSWeb/dmqs/distributedcustomer-information\",\n",
    "    \"User-Agent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36\"}\n",
    "    url = \"http://10.210.35.218/DMQService/api/DistributedCustomerInformation/GetDistributedCustomerInformation\"\n",
    "    \n",
    "    payloads = json.dumps({\"ohug\":\"\",\"Codetxt\":\"\",\"ownertpclid\":\"\",\"customername\":\"\",\"meternumber\":meternumber,\"vlevel\":\"\",\"hvlv\":\"\",\"group1\":\"\",\"paralletype\":\"\"})\n",
    "    res = requests.post(url,headers=headers,data=payloads)\n",
    "    data = json.loads(res.text)\n",
    "    #台電座標 ownertpclid\n",
    "    if data[\"data\"] == []:\n",
    "        return \"\", \"\", \"\"\n",
    "    else:\n",
    "        ownertpclid = data[\"data\"][0][\"ownertpclid\"]\n",
    "        #饋線代號\n",
    "        feeder = data[\"data\"][0][\"codetxt\"]\n",
    "        #台電桿號\n",
    "        tpc_address = data[\"data\"][0][\"address\"]\n",
    "        return ownertpclid, feeder, tpc_address \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "#電號 這樣跑太久超過22分鐘\n",
    "df_merged['電號']=df_merged['受理編號'].apply(lambda x: tpcmeter(x))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged[['台電座標','動態饋線','台電桿號']] = df_merged['受理編號'].apply(lambda x: meternumber_to_tpcdata(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([                   '區處別',                   '設置型別',\n",
       "                         '受理編號',                    '設置者',\n",
       "                         '設置地點',                 '裝置\\n容量',\n",
       "                         '躉售容量',                 '躉售\\n方式',\n",
       "                         '簽約日期',                   '併聯日期',\n",
       "                        '運轉中抄日',                   '備註說明',\n",
       "                       '併聯\\n方式',                   '審查人員',\n",
       "                           '饋線',                     '電號',\n",
       "       ('台電座標', '動態饋線', '台電桿號')],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = getpvdata()\n",
    "try:\n",
    "    df.to_excel(\"再生能源-已受理案件.xlsx\",index=False)\n",
    "except:\n",
    "    import xlwings as xw\n",
    "    print(\"再生能源-已受理案件.xlsx 被開啟\")\n",
    "    wb = xw.Book(\"再生能源-已受理案件.xlsx close\")\n",
    "    #關閉再生能源-已受理案件.xlsx\n",
    "    wb.close()\n",
    "    df.to_excel(\"再生能源-已受理案件.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\Users\\C036039\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\openpyxl\\worksheet\\header_footer.py:48: UserWarning: Cannot parse header or footer so it will be ignored\n",
      "  warn(\"\"\"Cannot parse header or footer so it will be ignored\"\"\")\n",
      "c:\\Users\\C036039\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\openpyxl\\worksheet\\header_footer.py:48: UserWarning: Cannot parse header or footer so it will be ignored\n",
      "  warn(\"\"\"Cannot parse header or footer so it will be ignored\"\"\")\n",
      "c:\\Users\\C036039\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\openpyxl\\worksheet\\header_footer.py:48: UserWarning: Cannot parse header or footer so it will be ignored\n",
      "  warn(\"\"\"Cannot parse header or footer so it will be ignored\"\"\")\n",
      "c:\\Users\\C036039\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\openpyxl\\worksheet\\header_footer.py:48: UserWarning: Cannot parse header or footer so it will be ignored\n",
      "  warn(\"\"\"Cannot parse header or footer so it will be ignored\"\"\")\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "pv_path = 'Z:/(靜)-000受理登記簿/PV系統審查登記簿.xlsm'\n",
    "df1= pd.read_excel(pv_path,sheet_name='113',engine='openpyxl',header=1,usecols='B:E,J,M,T')\n",
    "df2 = pd.read_excel(pv_path,sheet_name='112',engine='openpyxl',header=1,usecols='B:E,J,M,T')\n",
    "df3=pd.read_excel(pv_path,sheet_name='111',engine='openpyxl',header=1,usecols='B:E,J,M,T')\n",
    "df4=pd.read_excel(pv_path,sheet_name='110',engine='openpyxl',header=1,usecols='B:E,J,M,T')\n",
    "df4.columns = ['受理編號', '設置者', '設置地點', '裝置\\n容量 (kW)',  '併聯\\n方式','審查人員', '饋線']\n",
    "df5=pd.read_excel(pv_path,sheet_name='109',engine='openpyxl',header=1,usecols='B:E,H,K,O')\n",
    "df5.columns = ['受理編號', '設置者', '設置地點', '裝置\\n容量 (kW)', '併聯\\n方式', '審查人員', '饋線']\n",
    "df6=pd.read_excel(pv_path,sheet_name='108',engine='openpyxl',header=1,usecols='B:E,G,J,N')\n",
    "df7=pd.read_excel(pv_path,sheet_name='107',engine='openpyxl',header=1,usecols='B:E,G,J,N')\n",
    "df7.columns = ['受理編號', '設置者', '設置地點', '裝置\\n容量 (kW)', '併聯\\n方式', '審查人員', '饋線']\n",
    "df8=pd.read_excel(pv_path,sheet_name='106',engine='openpyxl',header=1,usecols='B:E,G,J,N')\n",
    "df8.columns = ['受理編號', '設置者', '設置地點', '裝置\\n容量 (kW)', '併聯\\n方式', '審查人員', '饋線']\n",
    "df9=pd.read_excel(pv_path,sheet_name='105',engine='openpyxl',header=1,usecols='B:E,G,J,N')\n",
    "df9.columns = ['受理編號', '設置者', '設置地點', '裝置\\n容量 (kW)', '併聯\\n方式', '審查人員', '饋線']\n",
    "df10=pd.read_excel(pv_path,sheet_name='104',engine='openpyxl',header=1,usecols='B:E,G,J')\n",
    "df10.columns = ['受理編號', '設置者', '設置地點', '裝置\\n容量 (kW)', '併聯\\n方式','審查人員']\n",
    "merged= pd.concat([df1,df2,df3,df4,df5,df6,df7,df8,df9,df10])\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df_merged = pd.merge(df,merged[['受理編號','併聯\\n方式','審查人員','饋線']],how='left',on='受理編號')\n",
    "# 將日期列轉換為 datetime 格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 將日期欄位轉換為 datetime 格式\n",
    "import pandas as pd\n",
    "\n",
    "# df_merged[['簽約日期','併聯日期','運轉中抄日']]= pd.to_datetime(df_merged[['簽約日期','併聯日期','運轉中抄日']],unit='ms')\n",
    "df_merged['簽約日期'] = pd.to_datetime(df_merged['簽約日期'], unit='ms').dt.strftime('%Y/%m/%d')\n",
    "df_merged['併聯日期'] = pd.to_datetime(df_merged['併聯日期'], unit='ms').dt.strftime('%Y/%m/%d')\n",
    "df_merged['運轉中抄日'] = pd.to_datetime(df_merged['運轉中抄日'], unit='ms').dt.strftime('%Y/%m/%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged.to_excel('再生能源-已受理案件.xlsx',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df= pd.read_excel('再生能源資料-RNIS.xlsx',sheet_name =\"案件查詢 - 查詢\",usecols='B:W')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "conn = sqlite3.connect('d:/規劃課資料.db')\n",
    "df.to_sql('再生能源資料', conn, if_exists='replace', index=False)\n",
    "conn.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Z:\\(靜)-000受理登記簿\\PV系統審查登記簿.xlsm 再生能源excel檔, 該檔案為唯讀\n",
    "#使用pandas做篩選\n",
    "import pandas as pd\n",
    "df_b = pd.read_excel(r'Z:\\(靜)-000受理登記簿\\PV系統審查登記簿.xlsm', sheet_name='113',engine='openpyxl',usecols='B:T')\n",
    "df_a = pd.read_excel('D:/python/再生能源資料-RNIS.xlsx',sheet_name='案件查詢 - 查詢',usecols='C')\n",
    "mask =type(df_b.iloc[:,0]) == type(df_a.iloc[:,0])\n",
    "df_a.iloc[:,0]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#取得Z:\\002再生能源\\000-太陽光電\\00-審查核准下所有目錄含子目錄的檔案名稱\n",
    "\n",
    "\n",
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "# 指定需要掃描的目錄路徑\n",
    "base_dir = r'Z:\\002再生能源\\000-太陽光電\\00-審查核准'\n",
    "\n",
    "# 創建一個空列表存放目錄名稱\n",
    "directory_names = []\n",
    "\n",
    "# 遍歷目錄及子目錄\n",
    "for root, dirs, files in os.walk(base_dir):\n",
    "    for dir_name in dirs:\n",
    "        if \"內線\" in dir_name:\n",
    "            directory_names.append(os.path.join(root, dir_name))\n",
    "\n",
    "# 創建一個 DataFrame 並將列表寫入到 Excel 文件中\n",
    "df = pd.DataFrame({'Directory Name': directory_names})\n",
    "df.to_excel('filename.xlsx', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "base_dir = r'Z:\\\\002再生能源\\\\000-太陽光電\\\\00-審查核准'\n",
    "directory_names = []\n",
    "hyperlinks = []\n",
    "\n",
    "for root, dirs, files in os.walk(base_dir):\n",
    "    for dir_name in dirs:\n",
    "        if \"內線\" in os.path.basename(dir_name):\n",
    "            directory_path = os.path.join(root, dir_name)\n",
    "            directory_names.append(directory_path)\n",
    "            hyperlinks.append(directory_path)\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'Directory Name': directory_names,\n",
    "    'hyperlink': hyperlinks\n",
    "})\n",
    "df.to_excel('filename.xlsx', index=False, hyperlink=df['hyperlink'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(\"filename.xlsx\",index=False)\n",
    "df.to_excel(\"filename.xlsx\",index=False,hyperlinks=df[])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_excel('filename.xlsx')\n",
    "\n",
    "hyperlink = []\n",
    "for i, row in df.iterrows():\n",
    "   \n",
    "        hyperlinks.append(row[0])\n",
    "\n",
    "df.to_excel('filename.xlsx', index=False,hyperlinks=hyperlink)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "import sqlite3\n",
    "# Z:/(靜)-000受理登記簿/PV系統審查登記簿.xlsm', sheet_name='113',engine='openpyxl',usecols='B:T')\n",
    "# d:/python/再生能源資料-RNIS.xlsx\n",
    "#我想使用類似vlookup方法比較\"再生能源資料-RNIS.xlsx 工作表=\"案件查詢 - 查詢\"  c 欄 與\"PV系統審查登記簿.xlsm 工作表=\"113\" B欄 若一樣的話將 \"PV系統審查登記簿.xlsm\" 工作表=113的m欄回貼到 再生能源資料-RNIS.xlsx 的W欄，該怎麼做\n",
    "\n",
    "rnis_data = pd.read_excel('d:/python/再生能源資料-RNIS.xlsx',sheet_name=\"案件查詢 - 查詢\")\n",
    "pv_data =pd.read_excel('Z:/(靜)-000受理登記簿/PV系統審查登記簿.xlsm', sheet_name='113',engine='openpyxl',usecols='B:T',header=1)\n",
    "\n",
    "\n",
    "# 使用merge将两个DataFrame合并，根據\"c\"列和\"B\"列进行连接，並將\"PV系統審查登記簿.xlsm\"工作表=113的\"m\"列的数据回贴到\"再生能源資料-RNIS.xlsx\"的\"W\"列\n",
    "merged_data = pd.merge(rnis_data, pv_data, left_on='公司編號', right_on='受理編號', how='left')\n",
    "\n",
    "merged_data.to_excel('d:/python/再生能源資料-RNIS1.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\C036039\\AppData\\Local\\Temp\\ipykernel_85828\\4067999847.py:54: FutureWarning: Passing literal html to 'read_html' is deprecated and will be removed in a future version. To read from a literal string, wrap it in a 'StringIO' object.\n",
      "  df = pd.read_html(res.text,header=0)\n"
     ]
    }
   ],
   "source": [
    "def user_update(mybook=\"D:/05 - 低頻電驛/每日用戶數.xlsm\"):\n",
    "    \"\"\"\n",
    "    mybook = 檔案名稱及路徑 \"c:/123/123.xlsx\"\n",
    "    \n",
    "    存放位置\n",
    "    工作表名稱   \n",
    "        \n",
    "    \"\"\"\n",
    "\n",
    "\n",
    "    #用戶數更新    \n",
    "    import pandas as pd\n",
    "    import requests \n",
    "    import xlwings as xw\n",
    "    import datetime\n",
    "    import logging\n",
    "     \n",
    "    # 設置日誌檔案名稱和級別\n",
    "    logging.basicConfig(filename='error.log', level=logging.ERROR)\n",
    "    wb = xw.Book(mybook)\n",
    "    today =datetime.datetime.today()\n",
    "    mytoday =today.strftime(\"%Y%m%d\")\n",
    "    if mytoday in wb.sheet_names:\n",
    "        print(\"已存在\")\n",
    "    else:\n",
    "\n",
    "\n",
    "        try:\n",
    "                # 您的程式碼\n",
    "            \n",
    "\n",
    "            #用戶更新網址\n",
    "            url = \"http://10.210.35.202/OmsApp201/Users/AllFeederUser.aspx\"\n",
    "\n",
    "            headers = {\n",
    "            \"Accept\":\"text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7\",\n",
    "            \"Accept-Encoding\":\"gzip, deflate\",\n",
    "            \"Accept-Language\":\"zh-TW,zh;q=0.9,en-US;q=0.8,en;q=0.7\",\n",
    "            \"Cache-Control\":\"max-age=0\",\n",
    "            \"Connection\":\"keep-alive\",\n",
    "            \"Content-Length\":\"287\",\n",
    "            \"Content-Type\":\"application/x-www-form-urlencoded\",\n",
    "            \"Cookie\":\"ASPSESSIONIDSSRDQRDC=AIFKCNCBNLGLKDACJFKJJNOE; ASPSESSIONIDSSQASRDC=LNAEBJPBLJJKDLOFFFDKBLAL; ASP.NET_SessionId=wvo53nrxpobvjtib1pioyobf\",\n",
    "            \"Host\":\"10.210.35.202\",\n",
    "            \"Origin\":\"http://10.210.35.202\",\n",
    "            \"Referer\":\"http://10.210.35.202/OmsApp201/Users/AllFeederUser.aspx\",\n",
    "            \"Upgrade-Insecure-Requests\":\"1\",\n",
    "            \"User-Agent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36\"\n",
    "\n",
    "            }\n",
    "            payload = (\"__VIEWSTATE=%2FwEPDwUJNTY1MTgzNjU5D2QWAgIBD2QWBAIHDzwrAAsAZAIJDw9kFgIeB29uY2xpY2sFD19TaG93TG9hZGluZygpO2Rkg62rXE9%2Br8sYmI7whrKeX3rknxs%3D&__VIEWSTATEGENERATOR=21A69B0D&__EVENTVALIDATION=%2FwEWAwKU%2F4a1AwL8o76uBAKyuOLUC3vnzAZb8Q%2Bf3Vn5rc%2BGcuplCmFq&SubmitButton=%C1%F4%C2%C3%AB%F6%B6s\")\n",
    "\n",
    "            res = requests.post(url,headers=headers,data=payload) \n",
    "            df = pd.read_html(res.text,header=0)\n",
    "\n",
    "\n",
    "            \n",
    "            formatted_today = today.strftime(\"%Y-%m-%d\")\n",
    "            formatted_today\n",
    "            \n",
    "            sht = wb.sheets[\"用戶數\"]\n",
    "            sht.range(\"A1\").expand().clear_contents()\n",
    "            sht[\"A1\"].expand().options(index=None,header=True).value = df[1]\n",
    "            sht[\"F1\"].value = \"饋線更新日期:\"\n",
    "            sht[\"G1\"].value = formatted_today\n",
    "            #複製用戶數工作表\n",
    "            sht.copy()\n",
    "            sht2 = wb.sheets[\"用戶數 (2)\"]\n",
    "            sht2.name = mytoday\n",
    "            wb.save()\n",
    "        except Exception as e:\n",
    "            # 記錄異常\n",
    "            logging.error(str(e))  \n",
    "if __name__ == \"__main__\":\n",
    "    user_update()\n",
    "    # import sys\n",
    "    # # 將函數名稱映射到函數對象的字典\n",
    "    # functions = {'user_update': user_update }\n",
    "\n",
    "    # if len(sys.argv) < 2:\n",
    "    #     function_name = 'user_update'\n",
    "    #     mybook = \"D:/05 - 低頻電驛/每日用戶數.xlsm\"\n",
    "    # else:\n",
    "    #     function_name = sys.argv[1]\n",
    "    #     mybook = sys.argv[2] if len(sys.argv) > 2 else None\n",
    "\n",
    "    # if function_name in functions:\n",
    "    #     # 呼叫相應的函數\n",
    "    #     if mybook:\n",
    "    #         functions[function_name](mybook)\n",
    "    #     else:\n",
    "    #         print(f\"請提供 {function_name} 函數的參數\")\n",
    "    # else:\n",
    "    #     print(\"未知的函數:\", function_name)\n",
    "    \n",
    "#執行命令\n",
    "#C:/Users/C036039/AppData/Local/Programs/Python/Python312/python.exe low_frequency.py user_update \"D:\\\\05 - 低頻電驛\\\\01-年度檢討\n",
    "#低頻卸量\\\\!!!111年低頻動作整理(依1110630填報後更新)_目前最新!!!.xlsx\"\n",
    "# def a(name=\"john\"):\n",
    "#     print(f\"A is {name}\")\n",
    "# def b():\n",
    "#     print(\"B\")\n",
    "# functions = {'a': a, 'b': b} \n",
    "# import sys\n",
    "\n",
    "\n",
    "# if len(sys.argv) < 2:\n",
    "#     print(\"請提供要執行的函數名稱\")\n",
    "#     sys.exit(1)    \n",
    "# function_name = sys.argv[1]   \n",
    "# if function_name in functions:\n",
    "#         # 呼叫相應的函數\n",
    "#     functions[function_name](*sys.argv[2:])\n",
    "# else:\n",
    "#     print(\"未知的函數:\", function_name)\n",
    "       "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sht.copy()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}