def __init__(self,
path_to_file=None,
sheet_name=None,
read_only=False,
header=0,
create_if_not_exists=False,
overwrite_if_exists=False):
"""
:param path_to_file: If absolute path to excel file is sent, then we create a workbook instance with that file
and use below parameters for further handling.
:param sheet_name: If sheet_name is provided, then create a sheet with the provided name as create_if_not_exists
:param read_only: Default=False; If set to True, then a read_only file is created!
:param create_if_not_exists: Default=False; If set to True, then a new file is created if not present.
:param overwrite_if_exists: Default=False; If set to True, it allows to overwrite file if exists, while creation
Usage: var = XlsxLib(workbook=already_created)
"""
#global LOGGER
#LOGGER = None
self.path_to_file = path_to_file
self.sheet_name = sheet_name
self.read_only = read_only
self.header = header
self.create_if_not_exists = create_if_not_exists
self.overwrite_if_exists = overwrite_if_exists
self.workbook = None
self.status = True
if not string_lib.is_string_empty_or_none(path_to_file):
try:
if os.path.dirname(path_to_file) == "" or os.path.dirname(
path_to_file) is None:
print(
"Absolute path to file - {f}, has not been provided!".
format(f=path_to_file))
elif file_handler.is_file_present(
path_to_file) and os.path.dirname(
path_to_file) is not None:
self.status = self.load_workbook_from_excel_file()
elif create_if_not_exists and not file_handler.is_file_present(
path_to_file):
self.status, self.workbook = XlsxLib.create_new_excel_file(
path_to_new_file=self.path_to_file,
sheet_name=self.sheet_name,
sheet_index=None,
overwrite_if_exists=self.overwrite_if_exists)
else:
self.status = False
print("{f} - File does not exist!".format(
f=self.path_to_file))
except Exception as e:
print("Unable to access or create file- {f}! \nException: {e}".
format(f=self.path_to_file, e=e))
self.status = False
else:
print("{f} - File path can not be empty/None!".format(
f=self.path_to_file))
self.status = False
raise FileNotFoundError(errno.ENOENT, os.strerror(errno.ENOENT),
path_to_file)
def create_new_sheet_in_excel_file(self,
sheet_name,
sheet_index=None,
create_excel_if_not_exists=True):
status = False
create_sheet = False
if file_handler.is_file_present(self.path_to_file):
create_sheet = True
elif file_handler.is_file_present(
self.path_to_file
) is False and create_excel_if_not_exists is True:
print(
" create_excel_if_not_exists = {c} | Trying to create a new excel file at {f}"
.format(c=create_excel_if_not_exists, f=self.path_to_file))
if XlsxLib.create_new_excel_file(
path_to_new_file=self.path_to_file,
sheet_name=sheet_name,
sheet_index=sheet_index):
# The if condition returns True when we successfully create a excel file with sheet_name
create_sheet = False
status = True
else:
create_sheet = True
else:
create_sheet = False
if create_sheet and not status:
try:
self.workbook = load_workbook(self.path_to_file)
is_sheet_present = self.check_if_sheet_present_in_excel_file(
sheet_name)
if not is_sheet_present:
self.workbook.create_sheet(sheet_name)
XlsxLib.save_excel_file(path_to_file=self.path_to_file,
workbook=self.workbook)
status = True
else:
LOGGER.warning(
"Sheet with name {s} already exists!".format(
s=sheet_name))
status = False
except Exception as e:
print(
"Unable to create sheet in workbook! \n Exception :: {0}".
format(e))
status = False
else:
print("Unable to create sheet in excel :: {f}".format(
f=self.path_to_file))
status = False
self.status = status
return status
def load_workbook_from_excel_file(self):
status = False
try:
if file_handler.is_file_present(self.path_to_file):
self.workbook = load_workbook(filename=self.path_to_file,
read_only=self.read_only)
status = True
else:
print("File not present or sheet_name not present in file!")
status = False
except Exception as e:
print("Unable to load workbook from excel file {f}".format(
f=self.path_to_file))
return status
def paste_range_to_excel_sheet(path_to_file,
sheet_name,
copied_data,
start_row,
start_col,
end_row=None,
end_col=None):
status = False
if file_handler.is_file_present(path_to_file):
workbook = load_workbook(path_to_file)
status = True
else:
print("File not present!!")
status = False
end_row = len(
copied_data
) if end_row is None and copied_data is not None else end_row
end_col = max([
len(element) for element in copied_data
]) if end_col is None and copied_data is not None else end_col
if sheet_name is not None and sheet_name != "".strip(" "):
if sheet_name in workbook.sheetnames:
try:
ws = workbook[sheet_name]
count_row = 0
for i in range(start_row, end_row + 1, 1):
count_col = 0
for j in range(start_col, end_col + 1, 1):
ws.cell(row=i, column=j
).value = copied_data[count_row][count_col]
count_col += 1
count_row += 1
XlsxLib.save_excel_file(path_to_file, workbook)
status = True
except Exception as e:
print("Unable to find the sheet {s} in file {f}!".format(
s=sheet_name, f=path_to_file))
status = False
else:
print("Sheet {s}, not present in file {f}!".format(
s=sheet_name, f=path_to_file))
status = False
else:
print(
"Sheet name cannot be None or empty{s}!".format(s=sheet_name))
return status
def create_new_excel_file(path_to_new_file,
sheet_name=None,
sheet_index=None,
overwrite_if_exists=False):
create_file = False
workbook = None
status = False
try:
if file_handler.is_file_present(path_to_new_file):
if overwrite_if_exists:
create_file = True
LOGGER.warning(
"Overwriting file, since overwrite_if_exists = {o}".
format(o=overwrite_if_exists))
else:
create_file = False
workbook = load_workbook(path_to_new_file)
print("File {f}, already exists and overwrite is False!".
format(f=path_to_new_file))
else:
create_file = True
if create_file:
workbook = Workbook()
try:
workbook.create_sheet(title=sheet_name, index=sheet_index)
status = True
except Exception as e:
print(
"Unable to create file {f} with sheet {s} \n Exception :: "
.format(s=sheet_name, f=path_to_new_file, e=e))
status = False
if status:
XlsxLib.save_excel_file(path_to_new_file, workbook)
print("Created a new excel file :: " + path_to_new_file)
status = True
else:
status = False
except Exception as e:
print("Unable to create file {f} with sheet {s} \n Exception :: ".
format(s=sheet_name, f=path_to_new_file, e=e))
status = False
return status, workbook
def copy_range_from_excel_sheet(path_to_file, sheet_name, start_row,
start_col, end_row, end_col):
status = False
try:
if file_handler.is_file_present(
path_to_file) and path_to_file is not None:
workbook = load_workbook(path_to_file)
status = True
else:
print("File not present!!")
status = False
except Exception as e:
exception_msg = "Unable to load {f}! \nException: {e}".format(
f=path_to_file, e=e)
print(exception_msg)
#raise Exception("Unable to load {f}! \nException: {e}".format(f=path_to_file, e=e))
if sheet_name is not None and sheet_name != "".strip(" "):
if sheet_name in workbook.sheetnames and status:
try:
ws = workbook[sheet_name]
range_selected = []
# Loops through selected Rows
for i in range(start_row, end_row + 1, 1):
# Appends the row to a row_selected list
row_selected = []
for j in range(start_col, end_col + 1, 1):
row_selected.append(ws.cell(row=i, column=j).value)
# Adds the row_selected List and nests inside the range_selected
range_selected.append(row_selected)
return range_selected
except Exception as e:
print("Unable to find sheet {s} in file {f}!".format(
s=sheet_name, f=path_to_file))
status = False
else:
print("Sheet {s}, not present in file {f}!".format(
s=sheet_name, f=path_to_file))
status = False
else:
print(
"Sheet name cannot be None or empty{s}!".format(s=sheet_name))
return status