2021年度第2回卒研セミナー(2021/04/22)

関連サイトと資料

関連データ

openpyxlライブラリを用いたExcelワークシート処理

test4.py
import math
from openpyxl import Workbook
   
row_titles = [
    '元の数', '2乗', '3乗', '2乗根', '3乗根',
    '逆数', 'log(底2)', 'log(底3)', '常用対数', '自然対数'
]
   
calcs = [
    lambda i: i,
    lambda i: i**2,
    lambda i: i**3,
    lambda i: math.sqrt(i),
    lambda i: i**(1./3),
    lambda i: 1./i,
    lambda i: math.log2(i),
    lambda i: math.log(i, 3),
    lambda i: math.log10(i),
    lambda i: math.log(i)
]
  
wb1 = Workbook()
  
ws1 = wb1.active
ws1.title = '数値計算早見表'
  
for i in range(len(row_titles)):
    cell = ws1.cell(row=i+1, column=1)
    cell.value = row_titles[i]
  
for i in range(100):
    for j in range(len(calcs)):
        cell = ws1.cell(row=j+1, column=i+2)
        cell.value = calcs[j](i + 1)
  
wb1.save('tet4.xlsx')
    

test5.py
import math
import openpyxl
  
row_titles = [
    '元の数', '2乗', '3乗', '2乗根', '3乗根',
    '逆数', 'log(底2)', 'log(底3)', '常用対数', '自然対数'
]
  
calcs = [
    lambda i: i,
    lambda i: i**2,
    lambda i: i**3,
    lambda i: math.sqrt(i),
    lambda i: i**(1./3),
    lambda i: 1./i,
    lambda i: math.log2(i),
    lambda i: math.log(i, 3),
    lambda i: math.log10(i),
    lambda i: math.log(i)
]
  
wb1 = openpyxl.load_workbook('test4.xlsx')
ws2 = wb1.create_sheet()
ws2.title = '数値計算早見表(101~200)'
  
for i in range(len(row_titles)):
    cell = ws2.cell(row=i+1, column=1)
    cell.value = row_titles[i]
  
for i in range(100):
    for j in range(len(calcs)):
        cell = ws2.cell(row=j+1, column=i+2)
        cell.value = calcs[j](i + 1 + 100)
  
wb1.save('test5.xlsx')
    

test6.py
from openpyxl.styles import NamedStyle, PatternFill, Font
from openpyxl.styles.colors import Color
import openpyxl
  
row_titles = [
    '元の数', '2乗', '3乗', '2乗根', '3乗根',
    '逆数', 'log(底2)', 'log(底3)', '常用対数', '自然対数'
]
  
headings = NamedStyle(
    name = 'headings',
    font = Font(size=14, color='FFFFFFFF'),
    fill = PatternFill(patternType = 'solid', fgColor = Color(rgb='1e90ff'))
)
  
wb1 = openpyxl.load_workbook('test5.xlsx')
ws1 = wb1.get_sheet_by_name('数値計算早見表')
ws2 = wb1.get_sheet_by_name('数値計算早見表(101~200)')
  
for ws in [ws1, ws2]:
    for i in range(len(row_titles)):
        cell = ws.cell(row=i+1, column=1)
        cell.style = headings
    
    for i in range(100):
        cell = ws.cell(row=1, column=i+2)
        cell.style = headings
   
ws1.column_dimensions['A'].width = 15
ws2.column_dimensions['A'].width = 15
   
wb1.save('test6.xlsx')
    

python-docxライブラリとopenpyxlライブラリを用いたWord文書処理

excel1.py
from openpyxl import load_workbook
from docx import Document
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.shared import Pt
import datetime as dt
import locale
  
locale.setlocale(locale.LC_CTYPE, "Japanese_Japan.932")
  
wb2 = load_workbook('managers.xlsx', data_only=True)
ws = wb2.worksheets[0]
  
data = []
for row in ws.values:
  data.append(list(row))
data = data[1:] 
  
#(A)
  
for i in range(len(data)):
  doc = Document() #(B)
  area = data[i][0]
  manager = data[i][1]
  users = data[i][2]
  willuse = data[i][3]
  
  p_date = doc.add_paragraph()
  p_date.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.RIGHT
  p_date.text = dt.date.today().strftime('%Y年%#m月%#d日')
  
  doc.add_paragraph().text = f'{area}地区担当'
  doc.add_paragraph().text = f'{manager}様'
  
  p_right = doc.add_paragraph()
  p_right.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.RIGHT
  p_right.text = 'サービス利用促進プロジェクト 清水'
  
  p_large = doc.add_paragraph()
  p_large.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER
  run_pl = p_large.add_run()
  run_pl.font.size = Pt(14)
  run_pl.text = "2020年分利用調査概要"
  
  doc.add_paragraph().text = ' 本サービスの利用促進に御尽力ありがとうございます。2020年分の利用調査を集計いたしました。'
  
  p_d = doc.add_paragraph()
  p_d.add_run().text = f' {manager}様が御担当の{area}地区では、'
  p_d.add_run().text = f'{users}名のお客様が'
  p_d.add_run().text = f'本サービスをすでに利用されています。また、'
  p_d.add_run().text = f'ご利用予定、またはその可能性があるとお考えの方が'
  p_d.add_run().text = f'{willuse}名いらっしゃいます。'
  
  doc.add_paragraph().text = ' 以後ますますがんばってください。'
  #(C)
  
  doc.save(area+'地区担当_'+manager+'様.docx') #(D)
#(E)
    

python-docxライブラリとopenpyxlライブラリとpython-pptxライブラリを用いたWord文書処理

powerpoint1.py
from docx import Document
from openpyxl import load_workbook
from pptx import Presentation
from pptx.util import Pt
from pptx.chart.data import CategoryChartData
from pptx.enum.chart import XL_CHART_TYPE
  
# Word文書を読み込む
doc = Document('areamemo.docx')
areamemo = [p.text for p in doc.paragraphs]
# 利用するデータをareainfoに格納
areainfo = [text.split(':') for text in areamemo[1:]]
  
# Excelブックを読み込む
wb = load_workbook('managers.xlsx', data_only=True)
ws = wb.worksheets[0]
data = []
for row in ws.values:
  data.append(list(row))
data = data[1:] 
# 利用するデータをusersとareanamesに格納
areanames = [list(u) for u in zip(*data)][0]
users = [list(u) for u in zip(*data)][2]
  
# PowerPointファイルを読み込む
prs3 = Presentation('report2.pptx')
  
# スライドを追加
slide2 = prs3.slides.add_slide(prs3.slide_layouts[5]) 
slide2.shapes[0].text = '各地区の特徴'
# 表を追加
categories = ['地区', '顧客数', '特徴']
left = Pt(10)
top = Pt(120)
width = Pt(700)
height = Pt(400)
tshape = slide2.shapes.add_table(
  len(areainfo)+1, len(categories),  
  left, top, width, height)
table = tshape.table
table.columns[0].width = Pt(100)
table.columns[1].width = Pt(100)
table.columns[2].width = Pt(500)
for i in range(len(categories)):
  table.cell(0, i).text = categories[i]
for i in range(len(areainfo)):
  table.cell(i+1, 0).text = areainfo[i][0]
  table.cell(i+1, 1).text = str(users[i]) 
  table.cell(i+1, 2).text = areainfo[i][1]
  
# スライドを追加
slide3 = prs3.slides.add_slide(prs3.slide_layouts[5]) 
slide3.shapes[0].text = '地区ごとの顧客数' 
# グラフを追加
c_data = CategoryChartData()
c_data.categories = areanames 
c_data.add_series('顧客数', users) 
slide3.shapes.add_chart(
  XL_CHART_TYPE.COLUMN_CLUSTERED,
  left, top, width, height, 
  c_data)
  
prs3.save('report3.pptx')