Python编辑Excel工具使用

常用的模块工具

根据上述表格及调研，选择使用OpenPyXL 链接
主要使用到的模块有：
openpyxl.workbook spec链接
openpyxl.worksheet spec链接

常用 openpyxl api

>>> from openpyxl import workbook, load_workbook
>>> wb = Workbook()
>>> wb.save("template.xlsx")
>>> wb = load_workbook('test.xlsx') # open excel as workbook object
>>> wb[sheetname] #return a worksheet object
>>> wb.save(filename) # save file, filename is a string
>>> wb.sheetnames #return worksheet names as list of strings
>>> wb.worksheets # return list of openpyxl.worksheet.worksheet.Worksheet

>>> ws = wb.active # return a active worksheet object
>>> ws['A1'].value # w/r able
>>> ws['A1'].number_format
>>> ws['A1'] = datetime.datetime(2010, 7, 21)
>>> ws["A1"] = "=SUM(1, 1)" # entry formula
>>> ws.merge_cells('A2:D2')
>>> ws.unmerge_cells('A2:D2')
>>> ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
>>> ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
>>> ws.append(["Fruit", "2011", "2012", "2013", "2014"])
>>> ws.tables["A1:D10"] # return a table object
>>> ws.iter_cols(min_col=None, max_col=None, min_row=None, max_row=None)
>>> ws.cols(min_col=None, max_col=None, min_row=None, max_row=None)
>>> ws.iter_rows(min_col=None, max_col=None, min_row=None, max_row=None)
>>> ws.rows(min_col=None, max_col=None, min_row=None, max_row=None)
>>> ws.max_column
>>> ws.max_row
>>> ws.values # return all cell values by row as generator
>>> ce = ws.cell(row, column, value=None) #return a cell object
>>> ce.column # Column number of this cell (1-based) 
>>> ce.coordinate # This cell’s coordinate (ex. ‘A5’)
>>> ce.col_idx # The numerical index of the column
>>> ce.data_type
>>> ce.is_date # True if the value is formatted as a date bool
>>> ce.row # Row number of this cell (1-based)
>>> ce.value