from excel_com import excel_com
from sheet_info_class import sheet_info
from class_excel_format import format
import mso
class excel_class(excel_com):
def __init__(self,error):
self.e=error
self.connect()
self.add_workbook()
#self.delete_sheet("Sheet1")
self.delete_sheet("Sheet2")
self.delete_sheet("Sheet3")
self.sheets=[]
self.sheets_info={}
self.active_sheet='Sheet1'
self.selection=None
self.debug=True
self.i=sheet_info(None,error)
self.horisontal=0
self.vertical=1
self.cache_cell_objects=True #Set to false if memory problem exists for large sheets
def set_output_file(self,outfile):
self.outfile=outfile
def __del__(self):
pass
#if self.wb_open:
# self.save(self.outfile)
# self.close_workbook()
def __exit__():
pass
#if self.wb_open:
# self.close_workbook()
def save_and_close(self):
if self.wb_open:
self.save(self.outfile)
self.close_workbook()
def add_sheet(self,name):
assert isinstance(name, str)
assert name not in self.sheets
assert name not in self.sheets_info
self._add_sheet(name)
if self.active_sheet=='Sheet1':
self.delete_sheet("Sheet1")
self.active_sheet=name
self.sheets.append(name)
self.sheets_info[name]=sheet_info(name,self.e)#Instane of 'sheet_info' class
def hide_sheet(self,sheet):
self.check_sheet(sheet)
self._hide_sheet(sheet)
def sheet_gridlines_off(self,sheet):
self.check_sheet(sheet)
self.gridlines_off(sheet)
def activate_sheet(self,sheet):
self.check_sheet(sheet)
self._activate_sheet(sheet)
self.active_sheet=sheet
def merge_cells_col(self,sheet,numofcells,row=None,col=None):
self.check_sheet(sheet)
if row==None or col==None:
(row,col)=self.get_current_location(sheet)
self._merge_cells_col(sheet,row, col,numofcells)
def merge_cells_col_active_sheet(self,numovcells):
self.merge_cells_col(self.active_sheet,numovcells)
def select_cells_col(self,sheet,numofcells,row=None,col=None):
self.check_sheet(sheet)
if row==None or col==None:
(row,col)=self.get_current_location(sheet)
self._select_cells_col(sheet,row, col,numofcells)
def select_cells_col_active_sheet(self,numovcells):
self.select_cells_col(self.active_sheet,numovcells)
def merge_selection(self):
self._merge_selection()
def rowcol_to_cell_active_sheet(self,row,col):
return self._rowcol_to_cell(self.active_sheet,row,col)
def create_line_graph(self,sheet):
self.check_sheet(sheet)
chrt=self._add_chart_ws(sheet)
self.set_chart_type(chrt,'xlLine')
return chrt
def create_graph(self,sheet,Left=100, Width=375, Top=75, Height=225):
self.check_sheet(sheet)
Left, Width, Top, Height=self.sheets_info[sheet].add_chart(left=Left, width=Width, top=Top, height=Height)
if type=='xlSurface':
chrt=self._add_chart_ws(sheet,Left=Left, Width=Width, Top=Top, Height=Height)
else:
chrt=self._add_chart_ws(sheet,Left=Left, Width=Width, Top=Top, Height=Height)
return chrt
def set_chart_type(self,chrt,type):
self._set_chart_type(chrt,type)
if type in ['xlColumnClustered']:
self.set_chart_xvalue_labels_oruentation(chrt,'xlTickLabelOrientationUpward')
if type in ['xlLine']:
self.AxisBetweenCategories(chrt,'xlCategory',False)
if type in ['xlXYScatterSmooth', 'xlXYScatterSmoothNoMarkers', 'xlXYScatter']:
self.set_axes_crosses_left(chrt)
if type in ['xlSurface']:
chrt.SetElement(mso.msoElementSeriesAxisTitleRotated)
def insert_file(self,sheet,filename):
self.check_sheet(sheet)
self._insert_file(sheet,filename)
def insert_file_active_sheet(self,filename):
self.insert_file(self.active_sheet,filename)
#************** Status check *****************
def is_valid_header(self,header):
return self.i.get_passfail_string() in header
def case_insensetive_sheet_exists(self,sheet):
assert isinstance(sheet, str)
return sheet.lower() in [sn.lower() for sn in self.sheets]
def sheet_exists(self,sheet):
assert isinstance(sheet, str)
return sheet in self.sheets
def header_set(self,sheet):
assert isinstance(sheet, str)
return sheet in self.sheets
return self.sheets_info[sheet].is_header_set()
def is_header_set_active_sheet(self):
return self.sheets_info[self.active_sheet].is_header_set()
def is_same_header_active_sheet(self,header):
return self.is_same_header(self.active_sheet,header)
def is_same_header(self,sheet,header):
return self.sheets_info[sheet].compare_header(header)
def is_col_name_in_header(self,sheet,val,type):
self.check_sheet(sheet)
return self.sheets_info[sheet].is_col_name_in_header(val,type)
def is_col_name_in_header_active_sheet(self,val,type):
return self.is_col_name_in_header(self.active_sheet,val,type)
def is_var_exists(self,sheet,varname):
self.check_sheet(sheet)
return self.sheets_info[sheet].is_var_exists(varname)
def is_var_exists_active_sheet(self,varname):
return self.is_var_exists(self.active_sheet,varname)
def is_sheet_has_passfail_column(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].is_sheet_has_passfail_column()
#************************************************************************
#**************************** Set section *******************************
#************************************************************************
def set_generated_col(self,sheet,first,last):
self.check_sheet(sheet)
self.sheets_info[sheet].set_generated_col(first,last)
def set_sort(self,sheet,config):
self.check_sheet(sheet)
self._set_sort(sheet,config)
def set_next_table(self,sheet,start,shift_direction=None):
if shift_direction==None:
shift_direction=self.horisontal
if shift_direction==self.horisontal:
self.set_start_col(sheet,start)
self.set_current_col(sheet,start)
self.set_current_row(sheet,self.get_start_row(sheet))
else:
self.set_start_row(sheet,start)
self.set_current_row(sheet,start)
self.set_current_col(sheet,self.get_start_col(sheet))
def set_next_table_active_sheet(self,start,shift_direction=None):
if shift_direction==None:
shift_direction=self.horisontal
self.set_next_table(self.active_sheet,start,shift_direction=self.horisontal)
def set_merged_col_cell(self,sheet,col_num,value,format,type='value'):
if not isinstance(col_num, int):
self.e.print_error("Number of columns must be integer, given '%s'"%(str(col_num)))
self.check_sheet(sheet)
assert type in ['formula','array_formula','value']
col=self.get_current_col(sheet)
self.select_cells_col(sheet,col_num)
self.merge_selection()
self.set_format_selection(format)
if type=='value':
self.set_cell_value(sheet,value)
elif type=='formula':
self.set_cell_formula(sheet,value)
elif type=='array_formula':
self.set_cell_array_formula(sheet,value)
else:
raise Exception("Unexpected type %s"%(type))
self.set_current_col(sheet,col+col_num)
def set_merged_col_cell_active_sheet(self,col_num,value,format,type='value'):
self.set_merged_col_cell(self.active_sheet,col_num,value,format,type)
def set_cell(self,sheet,value,format,type='formula'):
self.check_sheet(sheet)
assert type in ['formula','array_formula','value']
self.set_cell_format(sheet,format)
if type=='formula':
self.set_cell_formula(sheet,value)
elif type=='array_formula':
self.set_cell_array_formula(sheet,value)
elif type=='value':
self.set_cell_value(sheet,value)
else:
raise Exception("Unexpected type %s"%(type))
def set_cell_active_sheet(self,value,format,type='formula'):
self.set_cell(self.active_sheet,value,format,type)
def set_columns_autofit(self,sheet,col1, col2=None):
self._columns_auto_fit(sheet,col1,col2)
def set_columns_autofit_active_sheet(self,col1,col2=None):
self.set_columns_autofit(self.active_sheet, col1, col2)
def set_first_data_row_to_current(self,sheet):
self.check_sheet(sheet)
self.sheets_info[sheet].set_first_data_row_to_current()
def set_first_data_row_to_current_active_sheet(self):
self.set_first_data_row_to_current(self.active_sheet)
def set_last_data_row_to_current(self,sheet,diff):
self.check_sheet(sheet)
assert isinstance(diff, int)
self.sheets_info[sheet].set_last_data_row_to_current(diff)
def set_last_data_row_to_current_active_sheet(self,diff=0):
self.set_last_data_row_to_current(self.active_sheet,diff)
def set_header(self,sheet,header,corner_cols_num=None):
self.check_sheet(sheet)
if corner_cols_num!=None:
first_col=self.sheets_info[sheet].get_current_col()
last_corner_col_num=first_col+corner_cols_num-1
else:
last_corner_col_num=None
for col_name in header:
assert isinstance(col_name, str)
(row,col)=self.sheets_info[sheet].add_header_col(col_name)
self.set_cell_value(sheet,col_name,row,col)
if not self.sheets_info[sheet].close_header(last_corner_col_num):
return False #Duplication in column names
else:
self.sheets_info[sheet].set_header_row()
self.next_line(sheet)
return True#Ok status
def set_active_sheet_header(self,header,corner_cols_num=None):
return self.set_header(self.active_sheet,header,corner_cols_num)
def set_transpose(self,sheet):
self.sheets_info[sheet].set_transpose_mode()
def set_transpose_active_sheet(self):
self.set_transpose(self.active_sheet)
def set_start_location(self,sheet,row,col):
self.check_sheet(sheet)
self.sheets_info[sheet].set_start_location(row,col)
def set_active_sheet_start_location(self,row,col):
self.set_start_location(self.active_sheet,row,col)
def set_current_location(self,sheet,row,col):
self.check_sheet(sheet)
self.sheets_info[sheet].set_current_location(row,col)
def set_current_col(self,sheet,col):
self.check_sheet(sheet)
self.sheets_info[sheet].set_current_col(col)
def set_current_col_active_sheet(self,col):
self.set_current_col(self.active_sheet,col)
def set_current_row(self,sheet,row):
self.check_sheet(sheet)
self.sheets_info[sheet].set_current_row(row)
def set_currentrow_active_sheet(self,row):
self.set_current_row(self.active_sheet,row)
def set_active_sheet_current_location(self,row,col):
self.set_current_location(self.active_sheet,row,col)
def set_cell_value(self,sheet, value,row=None,col=None):
self.check_sheet(sheet)
if row==None and col==None:
(row,col)=self.get_current_location(sheet)
self._set_cell_value(sheet, row, col, value)
self.next_cell(sheet)
def set_cell_value_active_sheet(self,value):
self.set_cell_value(self.active_sheet,value)
def set_cell_array_formula(self,sheet, value,row=None,col=None):
self.check_sheet(sheet)
if row==None and col==None:
(row,col)=self.get_current_location(sheet)
self._set_cell_formula_array(sheet, row, col, value)
self.next_cell(sheet)
def set_cell_array_formula_active_sheet(self, value,row=None,col=None):
self.set_cell_array_formula(self.active_sheet, value,row=None,col=None)
def set_cell_formula(self,sheet, value,row=None,col=None):
self.check_sheet(sheet)
if row==None and col==None:
(row,col)=self.get_current_location(sheet)
self._set_cell_formula(sheet, row, col, value)
self.next_cell(sheet)
def set_cell_formula_active_sheet(self, value,row=None,col=None):
self.set_cell_formula(self.active_sheet, value,row=None,col=None)
def set_cell_comment(self,sheet,comment):
self.check_sheet(sheet)
(row,col)=self.get_current_location(sheet)
self._set_cell_comment(sheet, row, col, comment)
def set_cell_comment_active_sheet(self,comment):
(row,col)=self.get_current_location(self.active_sheet)
self._set_cell_comment(self.active_sheet, row, col, comment)
def set_cell_format(self,sheet,format,row=None,col=None):
self.check_sheet(sheet)
assert isinstance(format, dict)
if row==None and col==None:
(row,col)=self.get_current_location(sheet)
if 'font' in format:
self.set_font_property(sheet, row, col,format['font'])
if 'interior' in format:
self.set_interior(sheet, row, col,format['interior'])
if 'borders' in format:
self.set_cell_borders(sheet, row, col,format['borders'])
if 'properties' in format:
self.set_cell_properties(sheet, row, col,format['properties'])
def set_cell_format_active_sheet(self,format,row=None,col=None):
self.set_cell_format(self.active_sheet, format,row,col)
def set_format_selection(self,format):
if 'font' in format:
self.set_font_property_selection(format['font'])
if 'interior' in format:
self.set_interior_selection(format['interior'])
if 'borders' in format:
self.set_cell_borders_selection(format['borders'])
if 'properties' in format:
self.set_cell_properties_selection(format['properties'])
def set_cell_value_row(self,sheet,data):
row=self.get_current_row(sheet)
first_col=self.get_current_col(sheet)
last_col=first_col+len(data)-1
self.set_row_range(sheet,row,first_col,last_col,data)
self.next_line(sheet)
def set_cell_value_row_active_sheet(self,data):
self.set_cell_value_row(self.active_sheet,data)
def add_sheet_data_block(self,sheet,data,type='value',name=None):
self.check_sheet(sheet)
if type not in ['value','formula','array_formula']:
self.e.print_error("Unsupported type %s"%(type))
# row1=self.get_current_row(sheet)
# col1=self.get_current_col(sheet)
# row2=row1+len(data)-1
# col2=col1+len(data[0])-1
# self._set_range_value(sheet,row1, col1,row2, col2,data,type)
# self.sheets_info[sheet].set_current_row(row2+1)
#data is dictionary where keys is: data,data_type,name
data_dict={'data':data,'data_type':type}
if name!=None:
data_dict['name']=name
(row1, col1,row2, col2)=self.sheets_info[sheet].set_data_block(data=data_dict,function=self._set_range_value)
return (row1, col1,row2, col2)
def add_data_block_active_sheet(self,data,type='value',name=None):
return self.add_sheet_data_block(self.active_sheet,data,type,name)
def replace_sheet_data_links_by_values_active_sheet(self):
self.replace_sheet_data_links_by_values(self.active_sheet)
def replace_sheet_data_links_by_values(self,sheet):
self.check_sheet(sheet)
row1=self.get_first_data_row(sheet)
col1=self.get_first_data_col(sheet)
row2=self.get_last_data_row(sheet)
col2=self.get_last_data_col(sheet)
#self._select_range(sheet,row1, col1,row2, col2)
self._copy_range(sheet,row1, col1,row2, col2)
self._past_values_range(sheet,row1, col1,row2, col2)
#self.unselect()
def set_start_col(self,sheet,col_num):
self.check_sheet(sheet)
self.sheets_info[sheet].set_start_col(col_num)
def set_start_col_active_sheet(self,col_num):
self.set_start_col(self.active_sheet,col_num)
def set_start_row(self,sheet,row_num):
self.check_sheet(sheet)
self.sheets_info[sheet].set_start_row(row_num)
def set_start_row_active_sheet(self,col_num):
self.set_start_row(self.active_sheet,col_num)
def set_cell_conditional_formatting(self,sheet,format,row=None,col=None):
self.check_sheet(sheet)
if row==None or col==None:
(row,col)=self.get_current_location(sheet)
self._set_cell_conditional_formatting(sheet, row, col,format)
def set_cell_conditional_formatting_active_sheet(self,format,row=None,col=None):
self.set_cell_conditional_formatting(self.active_sheet,format,row,col)
def set_cell_validation(self,sheet,format,row=None,col=None):
self.check_sheet(sheet)
if row==None or col==None:
(row,col)=self.get_current_location(sheet)
self._set_cell_validation(sheet, row, col,format)
def set_cell_validation_active_sheet(self,format,row=None,col=None):
self.set_cell_validation(self.active_sheet,format,row,col)
#*********************************************************************
#**************************** Get section ****************************
#*********************************************************************
def get_sheet_data_block_names(self,sheet):
return self.sheets_info[sheet].get_sheet_data_block_names()
def get_column_range(self,sheet,name,type):
self.check_sheet(sheet)
col_info=self.sheets_info[sheet].get_column_range(name,type)
first_cell=self._rowcol_to_cell(sheet,col_info['first_row'],col_info['col'])
last_cell=self._rowcol_to_cell(sheet,col_info['last_row'],col_info['col'])
return "%s!%s:%s"%(sheet,first_cell,last_cell)
def get_excell_range_from_rowcol(self,sheet,row1,col1,row2,col2):
first_cell=self._rowcol_to_cell(sheet,row1,col1)
last_cell=self._rowcol_to_cell(sheet,row2,col2)
return "%s!%s:%s"%(sheet,first_cell,last_cell)
def get_generated_col(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_generated_col()
def get_header_row(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_header_row()
def get_header_row_active_sheet(self):
return self.get_header_row(self.active_sheet)
def get_numofcornercols(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_num_of_corner_cols()
def get_cell_value_from_memory(self,sheet,col_name,row_num,col_type):
self.check_sheet(sheet)
col_num=self.get_col_num_by_name(sheet,col_name,col_type)
return self.sheets_info[sheet].get_cell_value(sheet,row_num,col_num)
def get_row_values_from_memory(self,sheet,row):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_row_values_from_memory(sheet,row)
def load_whole_sheet_data(self,sheet):
self.check_sheet(sheet)
self.sheets_info[sheet].load_data_from_sheet(self._get_range_value)
def delete_sheet_data(self,sheet):
self.check_sheet(sheet)
self.sheets_info[sheet].delete_sheet_data()
def get_data_lines_iterator(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_data_line_iterator()
def get_data_col_iterator(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_data_col_iterator()
def get_column_names(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_column_names()
def get_column_names_active_sheet(self):
return self.get_column_names(self.active_sheet)
def get_first_data_col(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_first_data_col()
def get_last_data_col(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_last_data_col()
def get_first_data_row(self,sheet):
return self.sheets_info[sheet].get_first_data_row()
def get_first_data_row_active_sheet(self):
return self.get_first_data_row(self.active_sheet)
def get_last_data_row(self,sheet):
return self.sheets_info[sheet].get_last_data_row()
def get_last_data_row_active_sheet(self):
return self.get_last_data_row(self.active_sheet)
def get_current_location(self,sheet):
self.check_sheet(sheet)
return (self.sheets_info[sheet].get_current_row(),self.sheets_info[sheet].get_current_col())
def get_current_location_active_sheet(self):
return self.get_current_location(self.active_sheet)
def get_current_row(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_current_row()
def get_current_row_active_sheet(self):
return self.get_current_row(self.active_sheet)
def get_current_col(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_current_col()
def get_current_col_active_sheet(self):
return self.get_current_col(self.active_sheet)
def get_active_sheet(self):
return self.active_sheet
def get_col_values(self,sheet,col_num):
self.check_sheet(sheet)
first_row=self.sheets_info[sheet].get_first_data_row()
last_row=self.sheets_info[sheet].get_last_data_row()
return self._get_range_value(sheet,first_row, col_num,last_row, col_num)
def get_col_values_active_sheet(self,col_num):
return self.get_col_values(self.active_sheet,col_num)
def get_col_values_by_name(self,sheet,col_name,col_type):
col_num=self.sheets_info[sheet].get_col_num_by_name(col_name,col_type)
return self.get_col_values(sheet,col_num)
def get_col_values_by_name_active_sheet(self,col_name,col_type):
return self.get_col_values_by_name(self.active_sheet,col_name,col_type)
def get_corner_column_names(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_corner_column_names()
def get_measure_column_names(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_measure_column_names()
def get_first_measure_column(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_first_measure_column()
def get_last_measure_column(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_last_measure_column()
def get_full_header(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_header()
def get_corner_type(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_corner_type()
def get_start_col(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_start_col()
def get_start_row(self,sheet):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_start_row()
def get_start_coordinates(self,sheet):
self.check_sheet(sheet)
return (self.sheets_info[sheet].get_start_row(),self.sheets_info[sheet].get_start_col())
def get_start_coordinates_active_sheet(self):
return self.get_start_coordinates(self.active_sheet)
def get_col_num_by_name(self,sheet,name,type):
self.check_sheet(sheet)
return self.sheets_info[sheet].get_col_num_by_name(name,type)
#************** Pointer movement **************
def next_cell(self,sheet):
self.check_sheet(sheet)
self.sheets_info[sheet].next_cell()
def next_cell_active_sheet(self):
self.next_cell(self.active_sheet)
def next_line(self,sheet):
self.check_sheet(sheet)
self.sheets_info[sheet].next_line()
def next_line_active_sheet(self):
self.next_line(self.active_sheet)
def next_col(self,sheet):
self.check_sheet(sheet)
self.sheets_info[sheet].next_col()
def next_col_active_sheet(self):
self.next_col(self.active_sheet)
def next_table(self,sheet):
self.check_sheet(sheet)
self.sheets_info[sheet].set_next_table()
def next_table_active_sheet(self):
self.next_table(self.active_sheet)
#************** Not Excel functions **************
def add_var_info_current_sheet(self,var_name,multiplyer):
self.add_var_info(self.active_sheet,var_name,multiplyer)
def add_var_info(self,sheet,var_name,multiplyer):
self.check_sheet(sheet)
self.sheets_info[sheet].add_var_info(var_name,multiplyer)
def set_var_selector_location(self,sheet,varname,row=None,col=None):
self.check_sheet(sheet)
if row==None or col==None:
(row,col)=self.get_current_location(sheet)
self.sheets_info[sheet].set_var_selector_location(varname,row,col)
def set_var_selector_location_active_sheet(self,varname,row=None,col=None):
self.set_var_selector_location(self.active_sheet,varname,row,col)
def get_var_info(self,sheet,var_name):
return self.sheets_info[sheet].get_var_info(var_name)
def get_var_info_active_sheet(self,var_name):
return self.get_var_info(self.active_sheet,var_name)
def check_sheet(self,sheet):
assert isinstance(sheet, str)
assert sheet in self.sheets
assert sheet in self.sheets_info
def colnum_to_letter(self,sheet,col):
return self._colnum_to_letter(sheet,col)
def main():
e=excel_class()
e.add_sheet("Mysheet1")
e.set_transpose_active_sheet()
e.set_active_sheet_header(['a','b','c'])
e.set_cell_value_row_active_sheet([1,2,3])
e.save(r"C:\Users\stepanov\Documents\projects\R31\cds_be\measures_final\test.xlsx")
#e.close_workbook()
if __name__=="__main__":
main()
exit(0)
