{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'openpyxl'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[1], line 63\u001b[0m\n\u001b[0;32m     60\u001b[0m                     \u001b[39mfor\u001b[39;00m t \u001b[39min\u001b[39;00m \u001b[39mrange\u001b[39m(\u001b[39mlen\u001b[39m(results)):\n\u001b[0;32m     61\u001b[0m                         df[\u001b[39mid\u001b[39m\u001b[39m+\u001b[39m\u001b[39m\"\u001b[39m\u001b[39m_\u001b[39m\u001b[39m\"\u001b[39m\u001b[39m+\u001b[39m\u001b[39mstr\u001b[39m(t\u001b[39m+\u001b[39m\u001b[39m1\u001b[39m)][c] \u001b[39m=\u001b[39m \u001b[39mfloat\u001b[39m(results[t])\n\u001b[1;32m---> 63\u001b[0m df\u001b[39m.\u001b[39;49mto_excel(outputfile,index \u001b[39m=\u001b[39;49m \u001b[39mFalse\u001b[39;49;00m)\n",
      "File \u001b[1;32mc:\\Users\\weiye\\.conda\\envs\\pythontest\\lib\\site-packages\\pandas\\util\\_decorators.py:211\u001b[0m, in \u001b[0;36mdeprecate_kwarg.<locals>._deprecate_kwarg.<locals>.wrapper\u001b[1;34m(*args, **kwargs)\u001b[0m\n\u001b[0;32m    209\u001b[0m     \u001b[39melse\u001b[39;00m:\n\u001b[0;32m    210\u001b[0m         kwargs[new_arg_name] \u001b[39m=\u001b[39m new_arg_value\n\u001b[1;32m--> 211\u001b[0m \u001b[39mreturn\u001b[39;00m func(\u001b[39m*\u001b[39margs, \u001b[39m*\u001b[39m\u001b[39m*\u001b[39mkwargs)\n",
      "File \u001b[1;32mc:\\Users\\weiye\\.conda\\envs\\pythontest\\lib\\site-packages\\pandas\\util\\_decorators.py:211\u001b[0m, in \u001b[0;36mdeprecate_kwarg.<locals>._deprecate_kwarg.<locals>.wrapper\u001b[1;34m(*args, **kwargs)\u001b[0m\n\u001b[0;32m    209\u001b[0m     \u001b[39melse\u001b[39;00m:\n\u001b[0;32m    210\u001b[0m         kwargs[new_arg_name] \u001b[39m=\u001b[39m new_arg_value\n\u001b[1;32m--> 211\u001b[0m \u001b[39mreturn\u001b[39;00m func(\u001b[39m*\u001b[39margs, \u001b[39m*\u001b[39m\u001b[39m*\u001b[39mkwargs)\n",
      "File \u001b[1;32mc:\\Users\\weiye\\.conda\\envs\\pythontest\\lib\\site-packages\\pandas\\core\\generic.py:2374\u001b[0m, in \u001b[0;36mNDFrame.to_excel\u001b[1;34m(self, excel_writer, sheet_name, na_rep, float_format, columns, header, index, index_label, startrow, startcol, engine, merge_cells, encoding, inf_rep, verbose, freeze_panes, storage_options)\u001b[0m\n\u001b[0;32m   2361\u001b[0m \u001b[39mfrom\u001b[39;00m \u001b[39mpandas\u001b[39;00m\u001b[39m.\u001b[39;00m\u001b[39mio\u001b[39;00m\u001b[39m.\u001b[39;00m\u001b[39mformats\u001b[39;00m\u001b[39m.\u001b[39;00m\u001b[39mexcel\u001b[39;00m \u001b[39mimport\u001b[39;00m ExcelFormatter\n\u001b[0;32m   2363\u001b[0m formatter \u001b[39m=\u001b[39m ExcelFormatter(\n\u001b[0;32m   2364\u001b[0m     df,\n\u001b[0;32m   2365\u001b[0m     na_rep\u001b[39m=\u001b[39mna_rep,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m   2372\u001b[0m     inf_rep\u001b[39m=\u001b[39minf_rep,\n\u001b[0;32m   2373\u001b[0m )\n\u001b[1;32m-> 2374\u001b[0m formatter\u001b[39m.\u001b[39;49mwrite(\n\u001b[0;32m   2375\u001b[0m     excel_writer,\n\u001b[0;32m   2376\u001b[0m     sheet_name\u001b[39m=\u001b[39;49msheet_name,\n\u001b[0;32m   2377\u001b[0m     startrow\u001b[39m=\u001b[39;49mstartrow,\n\u001b[0;32m   2378\u001b[0m     startcol\u001b[39m=\u001b[39;49mstartcol,\n\u001b[0;32m   2379\u001b[0m     freeze_panes\u001b[39m=\u001b[39;49mfreeze_panes,\n\u001b[0;32m   2380\u001b[0m     engine\u001b[39m=\u001b[39;49mengine,\n\u001b[0;32m   2381\u001b[0m     storage_options\u001b[39m=\u001b[39;49mstorage_options,\n\u001b[0;32m   2382\u001b[0m )\n",
      "File \u001b[1;32mc:\\Users\\weiye\\.conda\\envs\\pythontest\\lib\\site-packages\\pandas\\io\\formats\\excel.py:918\u001b[0m, in \u001b[0;36mExcelFormatter.write\u001b[1;34m(self, writer, sheet_name, startrow, startcol, freeze_panes, engine, storage_options)\u001b[0m\n\u001b[0;32m    914\u001b[0m     need_save \u001b[39m=\u001b[39m \u001b[39mFalse\u001b[39;00m\n\u001b[0;32m    915\u001b[0m \u001b[39melse\u001b[39;00m:\n\u001b[0;32m    916\u001b[0m     \u001b[39m# error: Cannot instantiate abstract class 'ExcelWriter' with abstract\u001b[39;00m\n\u001b[0;32m    917\u001b[0m     \u001b[39m# attributes 'engine', 'save', 'supported_extensions' and 'write_cells'\u001b[39;00m\n\u001b[1;32m--> 918\u001b[0m     writer \u001b[39m=\u001b[39m ExcelWriter(  \u001b[39m# type: ignore[abstract]\u001b[39;49;00m\n\u001b[0;32m    919\u001b[0m         writer, engine\u001b[39m=\u001b[39;49mengine, storage_options\u001b[39m=\u001b[39;49mstorage_options\n\u001b[0;32m    920\u001b[0m     )\n\u001b[0;32m    921\u001b[0m     need_save \u001b[39m=\u001b[39m \u001b[39mTrue\u001b[39;00m\n\u001b[0;32m    923\u001b[0m \u001b[39mtry\u001b[39;00m:\n",
      "File \u001b[1;32mc:\\Users\\weiye\\.conda\\envs\\pythontest\\lib\\site-packages\\pandas\\io\\excel\\_openpyxl.py:56\u001b[0m, in \u001b[0;36mOpenpyxlWriter.__init__\u001b[1;34m(self, path, engine, date_format, datetime_format, mode, storage_options, if_sheet_exists, engine_kwargs, **kwargs)\u001b[0m\n\u001b[0;32m     43\u001b[0m \u001b[39mdef\u001b[39;00m \u001b[39m__init__\u001b[39m(\n\u001b[0;32m     44\u001b[0m     \u001b[39mself\u001b[39m,\n\u001b[0;32m     45\u001b[0m     path: FilePath \u001b[39m|\u001b[39m WriteExcelBuffer \u001b[39m|\u001b[39m ExcelWriter,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m     54\u001b[0m ) \u001b[39m-\u001b[39m\u001b[39m>\u001b[39m \u001b[39mNone\u001b[39;00m:\n\u001b[0;32m     55\u001b[0m     \u001b[39m# Use the openpyxl module as the Excel writer.\u001b[39;00m\n\u001b[1;32m---> 56\u001b[0m     \u001b[39mfrom\u001b[39;00m \u001b[39mopenpyxl\u001b[39;00m\u001b[39m.\u001b[39;00m\u001b[39mworkbook\u001b[39;00m \u001b[39mimport\u001b[39;00m Workbook\n\u001b[0;32m     58\u001b[0m     engine_kwargs \u001b[39m=\u001b[39m combine_kwargs(engine_kwargs, kwargs)\n\u001b[0;32m     60\u001b[0m     \u001b[39msuper\u001b[39m()\u001b[39m.\u001b[39m\u001b[39m__init__\u001b[39m(\n\u001b[0;32m     61\u001b[0m         path,\n\u001b[0;32m     62\u001b[0m         mode\u001b[39m=\u001b[39mmode,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m     65\u001b[0m         engine_kwargs\u001b[39m=\u001b[39mengine_kwargs,\n\u001b[0;32m     66\u001b[0m     )\n",
      "\u001b[1;31mModuleNotFoundError\u001b[0m: No module named 'openpyxl'"
     ]
    }
   ],
   "source": [
    "import os,re,json\n",
    "import pandas as pd\n",
    "#此处输入题号列表\n",
    "problems = r\"\"\"\n",
    "4080,4122,4312,4451,4557,4276,30019,4356,4320,4359,30020,4091,4400,8101,4157,4440,4370,4224,4328,4444,4247\n",
    "\n",
    "\n",
    "\"\"\"\n",
    "outputfile = r\"临时文件/使用记录结果对比.xlsx\"\n",
    "\n",
    "def generate_number_set(string):\n",
    "    string = re.sub(r\"[\\n\\s]\",\"\",string)\n",
    "    string_list = string.split(\",\")\n",
    "    numbers_list = []\n",
    "    for s in string_list:\n",
    "        if not \":\" in s:\n",
    "            numbers_list.append(s.zfill(6))\n",
    "        else:\n",
    "            start,end = s.split(\":\")\n",
    "            for ind in range(int(start),int(end)+1):\n",
    "                numbers_list.append(str(ind).zfill(6))\n",
    "    return numbers_list\n",
    "\n",
    "problem_list = generate_number_set(problems)\n",
    "with open(r\"d:/mathdeptv2/题库0.3/Problems.json\",\"r\",encoding = \"utf8\") as f:\n",
    "    jsondata = f.read()\n",
    "pro_dict = json.loads(jsondata)\n",
    "class_list = []\n",
    "\n",
    "df = pd.DataFrame()\n",
    "\n",
    "for id in problem_list:\n",
    "    # print(pro_dict[id][\"content\"])\n",
    "    # print(pro_dict[id][\"usages\"])\n",
    "    for usage in pro_dict[id][\"usages\"]:\n",
    "        classname = re.findall(\"([\\S]*届[\\S]*)\",usage)[0]\n",
    "        if not classname in class_list:\n",
    "            class_list.append(classname)\n",
    "class_list.sort()\n",
    "df[\"班级\"] = class_list\n",
    "\n",
    "for id in problem_list:\n",
    "    parts = 1\n",
    "    for usage in pro_dict[id][\"usages\"]:\n",
    "        parts = max(parts,len(re.findall(r\"\\d\\.[\\d]{3}\",usage)))\n",
    "    # print(id,parts)\n",
    "    if parts == 1:\n",
    "        df[id] = [\"N/A\"]*len(class_list)\n",
    "        for c in range(len(class_list)):\n",
    "            for usage in pro_dict[id][\"usages\"]:\n",
    "                if class_list[c] in usage:\n",
    "                    df[id][c] = float(re.findall(r\"\\d\\.[\\d]{3}\",usage)[0])\n",
    "    else:\n",
    "        for j in range(parts):\n",
    "            df[id+\"_\"+str(j+1)] = [\"N/A\"]*len(class_list)\n",
    "        for c in range(len(class_list)):\n",
    "            for usage in pro_dict[id][\"usages\"]:\n",
    "                if class_list[c] in usage:\n",
    "                    results = re.findall(r\"\\d\\.[\\d]{3}\",usage)\n",
    "                    for t in range(len(results)):\n",
    "                        df[id+\"_\"+str(t+1)][c] = float(results[t])\n",
    "\n",
    "df.to_excel(outputfile,index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.7 ('base')",
   "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.9.15"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "e4cce46d6be9934fbd27f9ca0432556941ea5bdf741d4f4d64c6cd7f8dfa8fba"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}