|
import warnings
import openpyxl as op
from openpyxl.utils import column_index_from_string as str2idx
with warnings.catch_warnings():
warnings.filterwarnings("ignore",category=UserWarning)
wb = op.load_workbook('2023_A-puzl99_schedule_240214.xlsm',data_only=True)
ws = wb['Sheet2']
fo = open('pzl99a.txt','w')
def get_row (ws,rnum,A,Z):
row = list(ws[A+str(rnum):Z+str(rnum)][0])
for i,cell in enumerate(row):
row[i]= cell.value if cell.value else ''
return row
for rnum in range(ws.min_row,ws.max_row+1):
prefix,name,postfix,trimstr,addstr,substr1,substr2=get_row(ws,rnum,'G','M')
offset,bits,rw,desch,defaultval=get_row(ws,rnum,'N','R')
if len(name)==0: continue
#----------------------------------------------------#
import warnings
import openpyxl as op
with warnings.catch_warnings():
warnings.filterwarnings("ignore",category=UserWarning)
#wb = op.load_workbook('io_mux_table.xlsx',data_only=True,read_only=False,keep_vba=True)
#sheet = wb['io_mux']
sheet = op.load_workbook('io_mux_table.xlsx')['io_mux']
#sheet = op.load_workbook('io_mux_table.xlsx',read_only=True,data_only=True,keep_vba=True)['io_mux']
output_list = []
output_list = []
iomux_file = open('output_iomux_b.txt',"w")
mod0 =''
i_key=[]
i_num={}
for col_num in range(50):
mod=str(sheet.cell(row=3,column=col_num+3).value)+":"
if mod=='None:': mod=mod0
else: mod0=mod
val=mod+str(sheet.cell(row=5,column=col_num+3).value.split("(")[0])
i_key.append(val)
i_num[val]=col_num
for i in range(6,200):
i_list = []
for col_num in range(50):
val=sheet.cell(row=i, column=col_num + 3).value
i_list.append(str(val) if val else None)
#------------------------------------------------------------------#
1. Write to Excel
#------------------------------------------------------------------#
from openpyxl import Workbook
wb = Workbook()
ws = wb.create_sheet('Sheet1')
ws = wb.active
ws['A1'] = '23'
ws.append([1,2,3])
ws.cell(3, 3, 51470)
ws.cell(5, 5, '5행5열')
wb.save("C:/tmp/숫자.xlsx")
#------------------------------------------------------------------#
2. Read from Excel
#------------------------------------------------------------------#
from openpyxl import load_workbook
wb = load_workbook("C:/tmp/output.xlsx", data_only=True)
ws = wb['Sheet1']
ws2= load_workbook("C:/tmp/output.xlsx", data_only=True)['Sheet2']
print(ws['B2'].value)
print(ws.cell(3, 2).value)
get_cells = ws['B3' : 'B6']
for row in get_cells:
for cell in row:
print(cell.value)
for row in ws.rows:
print(row)
for column in ws.columns:
print(column)
all_values = []
for row in ws.rows:
row_values = []
for cell in row:
row_values.append(cell.value)
all_values.append(row_values)
print(all_values)
cell.column_letter
cell.row
dir(cell)
['__class__', '__delattr__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__slots__', '__str__', '__subclasshook__', '_bind_value', '_comment', '_hyperlink', '_style', '_value', 'alignment', 'base_date', 'border', 'check_error', 'check_string', 'col_idx', 'column', 'column_letter', 'comment', 'coordinate', 'data_type', 'encoding', 'fill', 'font', 'has_style', 'hyperlink', 'internal_value', 'is_date', 'number_format', 'offset', 'parent', 'pivotButton', 'protection', 'quotePrefix', 'row', 'style', 'style_id', 'value']
for row_num in range(sheet.min_row,sheet.max_row+1):
i = str(row_num)
if sheet['B'+i].value != 'assign' : continue
>>> from openpyxl.utils import get_column_letter, column_index_from_string
>>> get_column_letter(2)
'B'
>>> get_column_letter(27)
'AA'
>>> get_column_letter(900)
'AHP'
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb['Sheet1']
>>> get_column_letter(sheet.max_column)
'C'
>>> column_index_from_string('A') # Get A's number. |
|
