SHARE
TWEET

Untitled

a guest Apr 21st, 2019 90 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. {
  2.  "cells": [
  3.   {
  4.    "cell_type": "code",
  5.    "execution_count": 1,
  6.    "metadata": {},
  7.    "outputs": [],
  8.    "source": [
  9.     "import numpy as np\n",
  10.     "import calendar\n",
  11.     "from datetime import date\n",
  12.     "import xlwings as xw"
  13.    ]
  14.   },
  15.   {
  16.    "cell_type": "code",
  17.    "execution_count": 3,
  18.    "metadata": {},
  19.    "outputs": [],
  20.    "source": [
  21.     "wb = xw.Book(u\"/Users/hoge/Dropbox/2016/業務日誌/gyomu_nisshi_2016_hoge.xls\")"
  22.    ]
  23.   },
  24.   {
  25.    "cell_type": "code",
  26.    "execution_count": 4,
  27.    "metadata": {},
  28.    "outputs": [],
  29.    "source": [
  30.     "# define the year and month to edit\n",
  31.     "year = 2016\n",
  32.     "month = 9\n",
  33.     "\n",
  34.     "# don't touch range\n",
  35.     "if month <= 8:\n",
  36.     "    print(\"Don't touch this month 2016!\")\n",
  37.     "    exit()\n",
  38.     "\n",
  39.     "# activate the sheet and put my name\n",
  40.     "xw.Sheet(u'%i月' % month).activate()\n",
  41.     "xw.Range('I4').value = u'氏名[ 名前 ]'"
  42.    ]
  43.   },
  44.   {
  45.    "cell_type": "code",
  46.    "execution_count": 5,
  47.    "metadata": {},
  48.    "outputs": [],
  49.    "source": [
  50.     "# dates start from the 9th row\n",
  51.     "row_date_start, row_date_end = 9, calendar.monthrange(year, month)[1] + 8  # in the case for \n",
  52.     "\n",
  53.     "row_dates = np.arange(row_date_start, row_date_end+1)\n",
  54.     "dates = row_dates - 9 + 1\n",
  55.     "\n",
  56.     "# set default arrival/departure times\n",
  57.     "time_arrive_default = \"9:30\"\n",
  58.     "time_depart_default = \"18:30\"\n",
  59.     "\n",
  60.     "# set default category of the work (業務)\n",
  61.     "gyomu_default = 'E'\n",
  62.     "\n",
  63.     "# loop on dates to change all cells to the default entry\n",
  64.     "for i, r in enumerate(row_dates):\n",
  65.     "    weekday_index = date(year, month, dates[i]).weekday()\n",
  66.     "    if (weekday_index != 5 and weekday_index != 6) and (not xw.Range('M%i' % r).value):\n",
  67.     "        xw.Range('C%i' % r).value = time_arrive_default\n",
  68.     "        xw.Range('D%i' % r).value = time_depart_default\n",
  69.     "        xw.Range(u'%s%i' % (gyomu_default, r)).value = u'○'\n",
  70.     "    else:\n",
  71.     "        xw.Range('C%i' % r).value = ':'\n",
  72.     "        xw.Range('D%i' % r).value = ':'\n",
  73.     "        xw.Range(u'%s%i' % (gyomu_default, r)).value = u''"
  74.    ]
  75.   },
  76.   {
  77.    "cell_type": "code",
  78.    "execution_count": 6,
  79.    "metadata": {},
  80.    "outputs": [],
  81.    "source": [
  82.     "# save changes\n",
  83.     "wb.save()"
  84.    ]
  85.   },
  86.   {
  87.    "cell_type": "code",
  88.    "execution_count": null,
  89.    "metadata": {
  90.     "collapsed": true
  91.    },
  92.    "outputs": [],
  93.    "source": []
  94.   },
  95.   {
  96.    "cell_type": "code",
  97.    "execution_count": null,
  98.    "metadata": {
  99.     "collapsed": true
  100.    },
  101.    "outputs": [],
  102.    "source": []
  103.   }
  104.  ],
  105.  "metadata": {
  106.   "kernelspec": {
  107.    "display_name": "Python 3",
  108.    "language": "python",
  109.    "name": "python3"
  110.   },
  111.   "language_info": {
  112.    "codemirror_mode": {
  113.     "name": "ipython",
  114.     "version": 3
  115.    },
  116.    "file_extension": ".py",
  117.    "mimetype": "text/x-python",
  118.    "name": "python",
  119.    "nbconvert_exporter": "python",
  120.    "pygments_lexer": "ipython3",
  121.    "version": "3.5.6"
  122.   }
  123.  },
  124.  "nbformat": 4,
  125.  "nbformat_minor": 1
  126. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top