PythonのExcelに対する一般的な操作
22031 ワード
いつもPythonを使ってExcelファイルに対していくつか操作を行って、毎回すべてコードを書く必要があって、時間を節約して手間を省くため、今日わざわざ最近書いたコードをまとめて、後で直接呼び出すのが便利です.基本的な機能は次のとおりです.セルの値 を読み込む.セル に値を書き込む読み出し行 読み出し列 挿入行 挿入列 ある範囲のデータ をクリアする.新規sheetテーブル コピーsheetテーブル sheetテーブル を削除 Excelのsheetテーブルの行数を取得する Excelのsheetテーブルの列数を取得する Excelファイル内のすべてのsheetテーブル名 を取得セルの背景色を取得 セルの背景色を設定 セルのフォント色を取得 セルフォントの色を設定 ある範囲の背景色 を設定する.ある範囲のフォント色 を設定する. excelファイル を閉じる
コードは次のとおりです.
注:このコードを書き込むことができます.pyファイルは、使用するたびに呼び出すだけです.
本人はoffice 2010版を使用していますが、他のバージョンでは一部の機能が使用できない可能性がありますので、コメントエリアでのコミュニケーションを歓迎します.
コードは次のとおりです.
# -*- coding: utf-8 -*-
import re
import logging
import win32com
import xlrd
import pandas as pd
import xlwings as xw
from xlwings.utils import rgb_to_int, int_to_rgb
logging.basicConfig(level=logging.INFO,
format='%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s')
def read_cell(path=None, sheet=0, cell="A1"):
"""
:param path: excel
:param sheet: sheet
:param cell:
:return:
"""
logging.info(u"ready to execute [readCell]")
try:
wb = xlrd.open_workbook(path)
if type(sheet) == str:
sht = wb.sheet_by_name(sheet)
elif type(sheet) == int:
sht = wb.sheet_by_index(sheet)
position = get_split_col_row(cell)
logging.info(u"position: [%s]" % position)
pos_col = position[0]
pos_row = position[1]
pos_col_index = get_excel_col_index(pos_col)
co = sht.col_values(pos_col_index, start_rowx=int(pos_row) - 1, end_rowx=int(pos_row))
if len(co) == 0:
co = [""]
logging.debug('read_cell result:[' + str(co[0]) + ']')
return co[0]
except Exception as e:
raise e
finally:
logging.info(u"end execute[readCell]")
def write_cell(path=None, sheet=0, cell="A1", text=None):
"""
:param path: excel
:param sheet: sheet
:param cell:
:param text: excel
:return:
"""
logging.info(u"ready to execute[writeCell]")
try:
wb = xw.Book(path)
sht = wb.sheets[sheet]
sht.range(cell).options(index=False, header=False).value = text
wb.save()
except Exception as e:
raise e
finally:
logging.info(u"end execute[writeCell]")
def read_row(path=None, sheet=0, cell="A1"):
"""
sheet
:param path: excel
:param sheet: sheet
:param cell:
:return:
"""
logging.info(u"ready to execute[read_row]")
try:
wb = xlrd.open_workbook(path)
if type(sheet) == str:
sht = wb.sheet_by_name(sheet)
elif type(sheet) == int:
sht = wb.sheet_by_index(sheet)
position = get_split_col_row(cell)
logging.info(u"position: [%s]" % position)
pos_col = position[0]
pos_row = position[1]
pos_col_index = get_excel_col_index(pos_col)
co = sht.row_values(int(pos_row) - 1, start_colx=pos_col_index)
logging.debug('read_row result:[' + str(co) + ']')
return co
except Exception as e:
raise e
finally:
logging.info(u"end execute[read_row]")
def read_col(path=None, sheet=0, cell="A1"):
"""
sheet
:param path: excel
:param sheet: sheet
:param cell:
:return:
"""
logging.info(u"ready to execute[read_col]")
try:
wb = xlrd.open_workbook(path)
if type(sheet) == str:
sht = wb.sheet_by_name(sheet)
elif type(sheet) == int:
sht = wb.sheet_by_index(sheet)
position = get_split_col_row(cell)
logging.info(u"position: [%s]" % position)
pos_col = position[0]
pos_row = position[1]
pos_col_index = get_excel_col_index(pos_col)
co = sht.col_values(pos_col_index, start_rowx=int(pos_row) - 1)
logging.debug('read_col result:[' + str(co) + ']')
return co
except Exception as e:
raise e
finally:
logging.info(u"end execute[read_col]")
def ins_row(path=None, sheet=0, cell="A1", data=None):
"""
sheet
:param path: excel
:param sheet: sheet
:param cell:
:param data:
:return:
"""
logging.info(u"ready to execute[ins_row]")
try:
wb = xw.Book(path)
sht = wb.sheets[sheet]
sht.range(cell).api.EntireRow.Insert()
sht.range(cell).options(index=False, header=False).value = data
wb.save()
except Exception as e:
raise e
finally:
logging.info(u"end execute[ins_row]")
def ins_col(path=None, sheet=0, cell="A1", data=None):
"""
sheet
:param path: excel
:param sheet: sheet
:param cell:
:param data:
:return:
"""
logging.info(u"ready to execute[ins_col]")
try:
data_list = []
wb = xw.Book(path)
sht = wb.sheets[sheet]
sht.range(cell).api.EntireColumn.Insert()
for i in data:
data_list.append([i])
sht.range(cell).options(index=False).value = data_list
wb.save()
except Exception as e:
raise e
finally:
logging.info(u"end execute[ins_col]")
def del_range(path, sheet=0, cell_1="A1", cell_2="A1"):
"""
del_range(path, sheet=0, cell_1="A1", cell_2="A1")
function： Clear the format and content of cells within the specified range without affecting other cells
parameter：
path: str files path
sheet: str or int sheet name
cell_1: str The cell in the upper left corner
cell_2: str The cell in the lower right corner
instance:
del_range('C:\\iexcel.xlsx', 1, 'A1', 'C3')
del_range('C:\\iexcel.xlsx', 'sheet2', 'B1', 'D5')
"""
logging.debug(u"ready to execute[del_range]")
try:
wb = xw.Book(path)
sht = wb.sheets[sheet]
rng = sht.range(cell_1, cell_2)
rng.clear()
wb.save()
except Exception as e:
raise e
finally:
logging.debug(u"end execute[del_range]")
def creat_sheet(path, sheet=None, before=None, after=None):
"""
sheet
creat_sheet(path, sheet=None, before=None) -> new sheet name
function： creat a sheet
parameter：
path: str files path
sheet: str sheet name
before: str or int the sheet before which the new sheet is added.
return:
new sheet name
instance:
creat_sheet('C:\\Desktop\\iexcel.xlsx')
creat_sheet('C:\\Desktop\\iexcel.xlsx','Sheet4', before=2)
"""
logging.debug(u"ready to execute[creat_sheet]")
try:
wb = xw.Book(path)
if before:
if isinstance(before, int):
before = before + 1
Sheet = xw.sheets.add(name=sheet, before=before)
else:
if isinstance(after, int):
after = after + 1
Sheet = xw.sheets.add(name=sheet, after=after)
wb.save()
return Sheet.name
except Exception as e:
raise e
finally:
logging.debug(u"end execute[creat_sheet]")
def copy_sheet(path, sheet=0, new_sheet_name=None):
"""
sheet
copy_sheet(path, sheet=0 ,new_sheet_name=None) -> new sheet name
function： copy a sheet
parameter：
path: str files path
sheet: str or int sheet name
new_sheet_name: str new sheet name
return:
new sheet name
instance:
copy_sheet('C:\\iexcel.xlsx', sheet=0)
copy_sheet('C:\\iexcel.xlsx', sheet='Sheet1',new_sheet_name='new_sheet')
"""
logging.debug(u"ready to execute[copy_sheet]")
try:
wb = xw.Book(path)
sht = wb.sheets[sheet]
sht.api.Copy(Before=sht.api)
wb.save()
nsht = wb.sheets[sht.index - 2]
if new_sheet_name != None:
nsht.name = new_sheet_name
return nsht.name
except Exception as e:
raise e
finally:
logging.debug(u"end execute[copy_sheet]")
def del_sheet(path, sheet=0):
"""
sheet
del_sheet(path, sheet=0)
function： delete a sheet
parameter：
path: str files path
sheet: str or int sheet name
instance:
del_sheet('C:\\iexcel.xlsx', sheet='sheet1')
del_sheet('C:\\iexcel.xlsx', sheet=1)
"""
logging.debug(u"ready to execute[del_sheet]")
try:
wb = xw.Book(path)
sheet = wb.sheets[sheet]
sheet.delete()
wb.save()
except Exception as e:
raise e
finally:
logging.debug(u"end execute[del_sheet]")
def get_rows_count(path, sheet=0):
"""
Excel sheet
get_rows_count(path, sheet=0) -> rows_count
function： Get the number of form rows
parameter：
path: str files path
sheet: str or int sheet name
return:
the number of rows
instance:
get_rows_count('C:\\iexcel.xlsx', sheet=0) -> 10
get_rows_count('C:\\iexcel.xlsx', sheet='Sheet1') -> 10
"""
logging.debug(u"ready to execute[get_rows_count]")
try:
df = pd.read_excel(path, sheet_name=sheet, header=None)
rows_count = df.shape[0]
return rows_count
except Exception as e:
raise e
finally:
logging.debug(u"end execute[get_rows_count]")
def get_cols_count(path, sheet=0):
"""
Excel sheet
get_cols_count(path, sheet=0) -> cols_count
function： Get the number of form columns
parameter：
path: str files path
sheet: str or int sheet name
return:
the number of columns
instance:
get_cols_count('C:\\iexcel.xlsx', sheet=0 -> 10
get_cols_count('C:\\iexcel.xlsx', sheet='Sheet1') -> 10
"""
logging.debug(u"ready to execute[get_cols_count]")
try:
df = pd.read_excel(path, sheet_name=sheet, header=None)
cols_count = df.shape[1]
return cols_count
except Exception as e:
raise e
finally:
logging.debug(u"end execute[get_cols_count]")
def get_sheet_name_list(path):
"""
Excel sheet
:param path: Excel
:return: sheet
"""
xl = pd.ExcelFile(path)
# sheet
sheet_names = xl.sheet_names
# Excel sheet_name
# sheet_content_list = xl.parse(sheet_name)
return sheet_names
def get_cell_color(path, sheet=0, cell="A1"):
"""
get_cell_color(path, sheet=0, cell="A1") -> color
function： Get the background color of the cell
parameter：
path: str files path
sheet: str or int sheet name
cell: str cell
return:
the background color of the cell
(If you have never set a cell background color,it will return None)
instance:
get_cell_color('C:\\iexcel.xlsx', 'sheet1', 'A1') -> (255,0,0)
get_cell_color('C:\\iexcel.xlsx', 0, 'A2') -> (255,255,0)
Red (255,0,0)
Yellow (255,255,0)
Blue (0,0,255)
White (255,255,255)
Black (0,0,0)
Green (0,255,0)
Purple (128,0,128)
"""
logging.debug(u"ready to execute[get_cell_color]")
try:
wb = xw.Book(path)
sht = wb.sheets[sheet]
rng = sht[cell]
color = rng.color
if color == None:
logging.debug(u"This cell background color is the default background color")
return color
except Exception as e:
raise e
finally:
logging.debug(u"end execute[get_cell_color]")
def set_cell_color(path, sheet=0, cell="A1", color=None):
"""
set_cell_color(path, sheet=0, cell="A1", color=None)
function： Set the background color of the cell
parameter：
path: str files path
sheet: str or int sheet name
cell: str cell
color: str or tuple color
Red '0000FF' (255,0,0)
Yellow '00FFFF' (255,255,0)
Blue 'FF0000' (0,0,255)
White 'FFFFFF' (255,255,255)
Black '000000' (0,0,0)
Green '00FF00' (0,255,0)
Purple '800080' (128,0,128)
instance:
set_cell_color('C:\\iexcel.xlsx', 0, 'A1', (0,0,255))
set_cell_color('C:\\iexcel.xlsx', 1, 'B1', (255,255,255))
set_cell_color('C:\\iexcel.xlsx', 'sheet1', 'C1', 'FFFFFF')
set_cell_color('C:\\iexcel.xlsx', 'sheet2', 'D1', 'FFFFFF')
"""
logging.debug(u"ready to execute[set_cell_color]")
try:
wb = xw.Book(path)
sht = wb.sheets[sheet]
rng = sht[cell]
if isinstance(color, str):
color = int(color, 16)
rng.color = color
wb.save()
except Exception as e:
raise e
finally:
logging.debug(u"end execute[set_cell_color]")
def get_cell_font_color(path, sheet=0, cell='A1'):
"""
get_cell_font_color(path, sheet=0, cell='A1')
function： Get the font color of the cell
parameter：
path: str files path
sheet: str or int sheet name
cell: str cell
return:
the font color of the cell
(If you have never set a cell background color,it will return None)
instance:
get_cell_font_color('C:\\iexcel.xlsx', 'sheet1', 'A1') -> (255,0,0)
get_cell_font_color('C:\\iexcel.xlsx', 0, 'A2') -> (255,255,0)
Red (255,0,0)
Yellow (255,255,0)
Blue (0,0,255)
White (255,255,255)
Black (0,0,0)
Green (0,255,0)
Purple (128,0,128)
"""
logging.debug(u"ready to execute[get_cell_font_color]")
try:
wb = xw.Book(path)
sht = wb.sheets[sheet]
rng = sht[cell]
color = rng.api.Font.Color
if not isinstance(color, tuple):
color = int_to_rgb(color)
if color == None:
logging.debug(u"This cell font color is the default background color")
return color
except Exception as e:
raise e
finally:
logging.debug(u"end execute[get_cell_font_color]")
def set_cell_font_color(path, sheet=0, cell='A1', color='000000'):
"""
set_cell_font_color(path, sheet=0, cell='A1', color='000000')
function： Set the font color of the cell
parameter：
path: str files path
sheet: str or int sheet name
cell: str cell
color: str or tuple color
Red '0000FF' (255,0,0)
Yellow '00FFFF' (255,255,0)
Blue 'FF0000' (0,0,255)
White 'FFFFFF' (255,255,255)
Black '000000' (0,0,0)
Green '00FF00' (0,255,0)
Purple '800080' (128,0,128)
instance:
set_cell_font_color('C:\\iexcel.xlsx', 1, 'A1', '000000')
set_cell_font_color('C:\\iexcel.xlsx', 'sheet2', 'B2', (0,0,0))
"""
logging.debug(u"ready to execute[set_cell_font_color]")
try:
wb = xw.Book(path)
sht = wb.sheets[sheet]
if isinstance(color, tuple): # RGB
color = rgb_to_int(color)
else: # str
color = int(color, 16)
rng = sht[cell]
rng.api.Font.Color = color
wb.save()
except Exception as e:
raise e
finally:
logging.debug(u"end execute[set_cell_font_color]")
def set_range_color(path, sheet=0, cell_1="A1", cell_2="A1", color=None):
"""
set_range_color(path, sheet=0, cell_1="A1", cell_2="A1", color=None)
function： Set the background color of the area cell
parameter：
path: str files path
sheet: str or int sheet name
cell_1: str The cell in the upper left corner
cell_2: str The cell in the lower right corner
color: str or tuple color
Red '0000FF' (255,0,0)
Yellow '00FFFF' (255,255,0)
Blue 'FF0000' (0,0,255)
White 'FFFFFF' (255,255,255)
Black '000000' (0,0,0)
Green '00FF00' (0,255,0)
Purple '800080' (128,0,128)
instance:
set_range_color('C:\\iexcel.xlsx', 1, 'A1', 'C3','000000')
set_range_color('C:\\iexcel.xlsx', 'sheet2', 'B1', 'D5','FFFFFF')
"""
logging.debug(u"ready to execute[set_range_color]")
try:
wb = xw.Book(path)
sht = wb.sheets[sheet]
rng = sht.range(cell_1, cell_2)
if isinstance(color, str):
color = int(color, 16)
rng.color = color
wb.save()
except Exception as e:
raise e
finally:
logging.debug(u"end execute[set_range_color]")
def set_range_font_color(path, sheet=0, cell_1='A1', cell_2='A1', color='000000'):
"""
set_range_font_color(path, sheet=0, cell_1='A1', cell_2='A1',color='000000')
function： Set the font color of the range
parameter：
path: str files path
sheet: str or int sheet name
cell_1: str The cell in the upper left corner
cell_2: str The cell in the lower right corner
color: str or tuple color
Red '0000FF' (255,0,0)
Yellow '00FFFF' (255,255,0)
Blue 'FF0000' (0,0,255)
White 'FFFFFF' (255,255,255)
Black '000000' (0,0,0)
Green '00FF00' (0,255,0)
Purple '800080' (128,0,128)
instance:
set_range_font_color('C:\\iexcel.xlsx', 1, 'A1', 'A2', '000000')
set_range_font_color('C:\\iexcel.xlsx', 'sheet2', 'A1', 'A2', (0,0,0))
"""
logging.debug(u"ready to execute[set_range_font_color]")
try:
wb = xw.Book(path)
sht = wb.sheets[sheet]
if isinstance(color, tuple): # RGB
color = rgb_to_int(color)
else: # str
color = int(color, 16)
rng = sht.range(cell_1, cell_2)
rng.api.Font.Color = color
wb.save()
except Exception as e:
raise e
finally:
logging.debug(u"end execute[set_range_font_color]")
def close_excel(path):
"""
excel
:return:
"""
xl = win32com.client.gencache.EnsureDispatch("Excel.Application") # Excel
xl.Visible = False # Excel
wb = xl.Workbooks.Open(path)
wb.Save()
wb.Close()
def get_split_col_row(string):
"""
:param string: A12
:return: ['A','12']
"""
string = string.upper()
return re.findall(r'[0-9]+|[A-Z]+', string)
def get_excel_col_index(string):
"""
excel ，
:param string: 'B'
:return: 1
"""
s = 0
for c in string:
c = c.upper()
s = s * 26 + ord(c) - ord('A') + 1
return s - 1注:このコードを書き込むことができます.pyファイルは、使用するたびに呼び出すだけです.
本人はoffice 2010版を使用していますが、他のバージョンでは一部の機能が使用できない可能性がありますので、コメントエリアでのコミュニケーションを歓迎します.