Please enable JavaScript.
Coggle requires JavaScript to display documents.
automate excel with python - Coggle Diagram
automate excel with python
modules
openpyxl
requirements
xlsx and later
import
from openpylx import workbook, load_workbook
workbook
Construction
new_book = Workbook()
book = open_workbook(fpath)
properties
sheetnames
functions
create_sheet(sheet_name)
save(fpath)
worksheet
Construction
sheet = book['Sheet1']
sheet = book.create_sheet(sheet_name)
sheet = book.active
properties
functions
append()
merge_cells(range) #range: "A1:D1"
insert_rows(row_pos)
delete_rows(row_pos)
insert_cols(int_col_pos)
delete_cols(int_col_pos)
move_range("C1:D11", rows=2, cols=2) # row, cols: int delta
cell
cell_A1 = sheet['A1]
properties
value
font
utilities
import openpyxl.utils
get_column_letter(int_val)
int_val starts from 1, NOT ZERO
Styles
from openpyxl.styles import Font
col_A1.font = Font(bold=True, color="0000FFFF")
doc
xlrd
supports xls files
workbook
worksheet
an excel tab
my_workbook.sheetbyindex(0)
cell
access by index
my_worksheet.cell_value(column,#, row#)
format datetime
date_tuple = xlrd.xdate_as_tuple(raw_value, book.datemode)
datestr = datetime.datetime(date_tuple).strftime('%y/%m/%d')
properties
nrows
ncols
array of worksheets
my_workbook = xlrd.open_workbook(fpath,
formatting_info=True
)
properties
xf_list
object info of all cells
c = sheet.cell(row, col)
xf = book.xf_list[c.xf_index]
font_color = book.colour_map.get(xf.font_index)
Tutorials
Write
Modify
Read
xlsxwriter
open file
xlrd.open_workbook()
copy data
book.sheetbyindex(0)
modify data
copy row by row to a list
create new file
book = xlsxwriter.Workbook(fpath)
sheet = book.add_worksheet()
populate new file