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')

Coordinate Table to Map Converter

在產品的測試上，通常將數據轉成圖表或圖形會更有益於分析。尤其在晶圓製造最容易遇到均勻性問題(uniformity)，這時候將raw data轉成wafer上的分佈，可以很快分析出異常元件出現在wafer上的某一區，進而知道和哪一道製程或機台有關。

最近看到python有xlwings這lib，好像可以和VBA連結，但還沒研究很透徹，對我來說好處是執行時excel不用關閉，而且指令很直覺。

*可用coordinate_from_string & column_index_from_string將excel座標轉成數字

raw data format:

因為不喜歡畫面上太多迴圈去讀值, 做法是先個別抓出value, x, y座標的list, 再zip成類似子陣列, 讀值概念如下:

val = xw.Range('A2').vertical.value
Xaxis = xw.Range('B2').vertical.value
Yaxis = xw.Range('C2').vertical.value

zipVal_list = list(zip(val, Xaxis, Yaxis))
for zipVal in zipVal_list:
    print(zipVal)

全程式碼:

from openpyxl.utils import _get_column_letter
from openpyxl.utils import coordinate_from_string, column_index_from_string
import xlwings as xw

xw.Workbook(r'C:\xlwings.xlsx')

val = xw.Range('A2').vertical.value
Xaxis = xw.Range('B2').vertical.value
Yaxis = xw.Range('C2').vertical.value

zipVal_list = list(zip(val, Xaxis, Yaxis))

for zipVal in zipVal_list:
    
    #start point
    zero = coordinate_from_string('G4') # G4 as start point
    zero_x = column_index_from_string(zero[0])-1
    zero_y = zero[1]-1
    
    #_get_column_letter converts int to letter only but not in float
    axis = str(_get_column_letter(int(zipVal[1]) + zero_x) + str(int(zipVal[2]) + zero_y))
    xw.Range(axis).value = zipVal[0]

map result: (可隨raw data的x,y設定轉成任何圖形,ex:圓形分布)