kinmubo.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Untitled72.ipynb",
      "provenance": [],
      "authorship_tag": "ABX9TyNI9hysoSQGbHLezX7NKmVL",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/ykitaguchi77/Colab_Scripts/blob/master/kinmubo.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "O0q8_Z3wGzHC"
      },
      "source": [
        "import pandas as pd\n",
        "import openpyxl\n",
        "from openpyxl import load_workbook\n",
        "import xlwt\n",
        "from datetime import datetime\n",
        "\n",
        "pd.set_option('display.max_rows', 150)\n",
        "\n",
        "#勤務簿をアップロードして右クリック→パスをコピペ\n",
        "excel_path = '/content/診療等従事記録簿_令和3年度10月.xlsx'\n"
      ],
      "execution_count": 24,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DAl04vgPksLD"
      },
      "source": [
        "#エクセルを開く\n",
        "book = load_workbook(filename=excel_path, read_only=False)\n",
        "\n",
        "#1枚目のシートを指定\n",
        "sheets = book.sheetnames\n",
        "sheet = book[sheets[0]]\n",
        "\n",
        "#日付に関する変数の定義\n",
        "dt_now = datetime.now()\n",
        "year = dt_now.year\n",
        "month = sheet[\"S2\"].value"
      ],
      "execution_count": 31,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fh7ZzuJkRGkD",
        "outputId": "d55014c8-1874-40f1-fb06-9b8f5a1796f8"
      },
      "source": [
        "minB_col = 2\n",
        "minB_row = 5\n",
        "maxB_col = 2\n",
        "maxB_row = sheet.max_row\n",
        "first_wk = True\n",
        "p=4#行数\n",
        "\"\"\"\n",
        "for row in sheet.iter_rows(min_col=minB_col, min_row=minB_row, max_col=maxB_col, max_row=maxB_row):\n",
        "    for cell in row:\n",
        "        p+=1\n",
        "        print(p)\n",
        "        date = cell.value\n",
        "        if isinstance(date, int):           \n",
        "            #日付から曜日を出す\n",
        "            d = str(year)+'/'+str(month)+'/'+str(date)\n",
        "            d = datetime.strptime(d, '%Y/%m/%d')\n",
        "            wkd = d.weekday()\n",
        "\n",
        "            if wkd == 5:\n",
        "                pass\n",
        "            elif wkd ==6:\n",
        "                if first_wk == True:\n",
        "                    sheet.cell(row=p, column=4).value = \"9:00\"\n",
        "                    sheet.cell(row=p, column=6).value = \"11:00\"\n",
        "                    not first_wk #Boolを反転\n",
        "            elif wkd == 0:\n",
        "                sheet.cell(row=p, column=4).value = \"13:00\"\n",
        "                sheet.cell(row=p, column=6).value = \"15:00\"\n",
        "            elif wkd == 1 or 2 or 3 or 4:\n",
        "                sheet.cell(row=p, column=4).value=\"8:30\"\n",
        "                sheet.cell(row=p, column=6).value = \"12:30\"\n",
        "                sheet.cell(row=p, column=7).value = \"13:00\"\n",
        "                sheet.cell(row=p, column=9).value = \"19:30\"\n",
        "\"\"\"\n",
        "print(\"done\")\n",
        "book.save(\"kinmubo.xlsx\")"
      ],
      "execution_count": 32,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "done\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RjvoLeBtHGNk"
      },
      "source": [
        "#sheet_namesメソッドでExcelブック内の各シートの名前をリストで取得できる\n",
        "input_sheet_name = input_book.sheet_names\n",
        "#lenでシートの総数を確認\n",
        "num_sheet = len(input_sheet_name)\n",
        "#シートの数とシートの名前のリストの表示\n",
        "print (\"Sheet の数:\", num_sheet)\n",
        "print (input_sheet_name)\n",
        "\n",
        "#シートの内容を表示\n",
        "df = input_book.parse(input_sheet_name[0])\n",
        "df.head(132)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "92cH8AudHgEB"
      },
      "source": [
        "df.loc[0,0] = \"aaaaaa\"\n",
        "#df.loc[df.iloc[:,3].str.contains(\"日\", na=False),4] = \"aaaaaaa\"\n",
        "print(df.iloc[3,4])\n",
        "df.iloc[3,4] = \"8:30 ～ 12:30\"\n",
        "print(df.iloc[3,4])\n",
        "\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "p4zW3RjtJWO0"
      },
      "source": [
        "df.to_excel('/content/test.xlsx',\n",
        "            index=False, header=False)"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}