openpyxl 新增多個分頁與表頭

一樣openpyxl, 但直接加入分頁與表頭

import openpyxl as pyxl
wb = pyxl.load_workbook(r'C:\input.xlsx')
ws = wb.active

#print(sheet_ranges['B2'].value)
dieNo = []
for col in ws.columns[1]:
    dieNo.append(col.value)
offset = []
for col in ws.columns[2]:
    offset.append(col.value)
Rin = []
for col in ws.columns[3]:
    Rin.append(col.value)
Rout = []
for col in ws.columns[4]:
    Rout.append(col.value)    
x_pos = []
for col in ws.columns[7]:
    x_pos.append(col.value)
y_pos = []
for col in ws.columns[8]:
    y_pos.append(col.value)    

myList = list(zip(dieNo, offset, Rin, Rout, x_pos, y_pos))
#print (myVal[3])

wb0 = pyxl.Workbook()
ws1 = wb0.worksheets[0]
ws1.title = 'offset table'
ws2 = wb0.create_sheet()
ws2.title = 'Rin Rout table'
ws3 = wb0.create_sheet()
ws3.title = 'offset map'
ws4 = wb0.create_sheet()
ws4.title = 'Rin map'
ws5 = wb0.create_sheet()
ws5.title = 'Rout map'

# Mapping
for Unit in myList:
    ws3.cell(row=Unit[5]+2, column=Unit[4]+1).value = Unit[1] # offset map
    ws4.cell(row=Unit[5]+2, column=Unit[4]+1).value = Unit[2] # Rin map
    ws5.cell(row=Unit[5]+2, column=Unit[4]+1).value = Unit[3] # Rout map

# Table
ws1.cell(row=1, column=1).value = 'Design1'
ws1.cell(row=1, column=2).value = 'Design2'
ws1.cell(row=1, column=3).value = 'Design3'
ws1.cell(row=1, column=4).value = 'Design4'
ws1.cell(row=1, column=5).value = 'Design5'
ws1.cell(row=1, column=6).value = 'Design6'

ws2.cell(row=1, column=1).value = 'Design1_Rin'
ws2.cell(row=1, column=2).value = 'Design1_Rout'
ws2.cell(row=1, column=3).value = 'Design2_Rin'
ws2.cell(row=1, column=4).value = 'Design2_Rout'
ws2.cell(row=1, column=5).value = 'Design3_Rin'
ws2.cell(row=1, column=6).value = 'Design3_Rout'
ws2.cell(row=1, column=7).value = 'Design4_Rin'
ws2.cell(row=1, column=8).value = 'Design4_Rout'
ws2.cell(row=1, column=9).value = 'Design5_Rin'
ws2.cell(row=1, column=10).value = 'Design5_Rout'
ws2.cell(row=1, column=11).value = 'Design6_Rin'
ws2.cell(row=1, column=12).value = 'Design6_Rout'

RowCount = 2
for Unit in myList:
    if Unit[0] % 6 == 0:
        col = 6
        ws1.cell(row = RowCount, column = col).value = Unit[1]
        ws2.cell(row = RowCount, column = 2*(col-1)+1).value = Unit[2]
        ws2.cell(row = RowCount, column = 2*(col-1)+2).value = Unit[3]       
        RowCount += 1
    else:
        col = Unit[0] % 6
        ws1.cell(row = RowCount, column = col).value = Unit[1]
        ws2.cell(row = RowCount, column = 2*(col-1)+1).value = Unit[2]
        ws2.cell(row = RowCount, column = 2*(col-1)+2).value = Unit[3]  
    
#ws2.cell(row=1, column=1).value = 333
#ws2['A2']=414 availavle too
wb0.save(r'C:\output.xlsx')

openpyxl 資料處理(mapping)

最近一樣需要轉mapping的程式, 但想將map另存新檔.
但上網查了一下xlwings似乎只能一次處理一份excel, 也就是讀取和存檔必須同一份檔案.
所以用openpyxl試寫看看, 
發現openpyxl其實還不錯. 網路上語法教學更多更詳細.

import openpyxl as pyxl
wb = pyxl.load_workbook(r'C:\input.xlsx')
ws = wb.active

''' 
data format:

value  x   y
12     1   1
42     1   2
5      1   3
......   
'''
Val = []
for col in ws.columns[0]:
    Val.append(col.value)
x_pos = []
for col in ws.columns[1]:
    x_pos.append(col.value)
y_pos = []
for col in ws.columns[2]:
    y_pos.append(col.value)    

myVal = list(zip(Val, x_pos, y_pos))

wb2 = pyxl.Workbook()
ws2 = wb2.active

for i_arr in myVal:
    ws2.cell(row=i_arr[2]+2, column=i_arr[1]+1).value = abs(i_arr[0])
#ws2['A2']=414 available too
    
wb2.save(r'C:\output.xlsx')