def increase_col():
wb=load_workbook('report.xlsx')
sheet = wb.get_sheet_by_name('Sheet')
wb.active
curr_col = sheet.max_column
col_count = sheet.max_column
col_count += 1
pre_col =sheet.max_column -1
curr_col=get_column_letter(curr_col)
#pre_col =get_column_letter(pre_col)
new_col= get_column_letter(col_count)
today = datetime.date.today()
first = today.replace(day=1)
lastMonth = first - datetime.timedelta(days=1)
lastMonth= lastMonth.strftime("%B %Y")
next_month = "{:%B %Y}".format(datetime.date.today() + relativedelta.relativedelta(months=1))
month="{:%B %Y}".format(datetime.date.today())
while sheet.cell('%s1' % curr_col).value == month:
pass
else:
sheet['%s1' % new_col] = month
wb.save('report.xlsx')
#increase_col()
def logs (a,b):
wb=load_workbook('report.xlsx',read_only = False, data_only = True)
sheet =wb.get_sheet_by_name('Sheet')
wb.active
mydate = datetime.date.today()
this_month= mydate.strftime("%B %Y")
curr_col= sheet.max_column
curr_row = sheet.max_row
min_col=sheet.min_column
min_col=get_column_letter(min_col)
curr_col = get_column_letter(curr_col)
sheet['%s1' % curr_col] = this_month
sheet.column_dimensions['%s' % curr_col].width = 50
time=datetime.datetime.now().strftime("%d %H:%M")
if b != None:
new_row=curr_row+1
#sheet['%s%d' % (curr_col,new_row)] = a+ " " + b +" "+ time
sheet['%s%d' % (curr_col,column_to_add(curr_col))] = time+">>> "+a+" "+b
else:
new_row=curr_row+1
b = " "
sheet['%s%d' % (curr_col,column_to_add(curr_col))] = time+">>> "+a+" "+b
#print (column_to_add(curr_col))
wb.save('report.xlsx')
def header():
italic24Font = Font(size=18, italic=False)
wb=load_workbook('testing.xlsx',read_only = False, data_only = True)
sheet =wb.get_sheet_by_name('Sheet')
wb.active
month="{:%B %Y}".format(datetime.date.today())
today = datetime.date.today()
sheet['a1'].font=italic24Font
sheet['b1'].font=italic24Font
sheet['c1'].font=italic24Font
sheet.column_dimensions['A'].width = 20
sheet.column_dimensions['B'].width = 30
sheet.column_dimensions['C'].width = 30
sheet['a1']="UID"
sheet['b1']="Name"
sheet['c1']="Email"
start_col =4
start_month=get_column_letter(start_col)
sheet['%s1' % start_month] = "August 2016"
curr_col = sheet.max_column
new_col= curr_col+1
first = today.replace(day=1)
new_col = get_column_letter(new_col)
lastMonth = first - datetime.timedelta(days=1)
lastMonth= lastMonth.strftime("%B %Y")
#print (curr_col)
this_month=get_column_letter(curr_col)
if sheet.cell('%s1' % this_month).value == lastMonth:
sheet['%s1' % new_col] = month
sheet.column_dimensions['%s' % this_month].width = 20
wb.save('testing.xlsx')
def unmerge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
""" Remove merge on a cell range. Range is a cell range (e.g. A1:E1) """
if not range_string:
if start_row is None or start_column is None or end_row is None or end_column is None:
msg = (
"You have to provide a value either for "
"'coordinate' or for 'start_row', 'start_column', 'end_row' *and* 'end_column'"
)
raise InsufficientCoordinatesException(msg)
else:
range_string = "%s%s:%s%s" % (
get_column_letter(start_column),
start_row,
get_column_letter(end_column),
end_row,
)
elif len(range_string.split(":")) != 2:
msg = "Range must be a cell range (e.g. A1:E1)"
raise InsufficientCoordinatesException(msg)
else:
range_string = range_string.replace("$", "")
if range_string in self._merged_cells:
self._merged_cells.remove(range_string)
else:
msg = "Cell range %s not known as merged." % range_string
raise InsufficientCoordinatesException(msg)
def add_column():
wb=load_workbook('testing.xlsx')
sheet = wb.get_sheet_by_name('Sheet')
wb.active
curr_col = sheet.max_column
col_count = sheet.max_column
col_count += 1
pre_col =sheet.max_column -1
curr_col=get_column_letter(curr_col)
pre_col =get_column_letter(pre_col)
new_col= get_column_letter(col_count)
#print (new_col)
today = datetime.date.today()
first = today.replace(day=1)
lastMonth = first - datetime.timedelta(days=1)
lastMonth= lastMonth.strftime("%B %Y")
next_month = "{:%B %Y}".format(datetime.date.today() + relativedelta.relativedelta(months=1))
month="{:%B %Y}".format(datetime.date.today())
#print (next_month)
#sheet['%s1' % curr_col] = month
#print (lastMonth)
if sheet.cell('%s1' % curr_col).value != month:
sheet['%s1' % new_col] = next_month
wb.save('testing.xlsx')
def exportResolve(fileName):
if not os.path.exists("output"):
os.makedirs("output")
disPattern = re.compile("(\xa0){0,}(?P<dis>[0-9/]+).*")
savePath = "output/" + fileName
b = Workbook()
s = b.active
resolve = dbm.getAllResolve()
for case in resolve:
case['Disposition Date'] = disPattern.match(case['Disposition']).group(2)
dataCounts = len(resolve)
if dataCounts == 0:
return False
keys = sorted(resolve[0].keys())
keySize = len(keys)
# Write column head
for col_index in range(1, keySize + 1):
s["%s1"%get_column_letter(col_index)] = keys[col_index - 1]
# Write data from second row
i = 0
for row_index in range(2, dataCounts + 2):
# Reduce index calculation when access a data
data = resolve[i]
for col_index in range(1, keySize + 1):
s["%s%s"%(get_column_letter(col_index), row_index)] = \
data[keys[col_index - 1]]
i += 1
b.save(filename = savePath)
return True
def write_to_spreadsheet(self, spreadsheet_name):
'''Write values from self.data_table to spreadsheet'''
r = 1
c = 1
wb = openpyxl.Workbook()
sheet = wb.active
# Write sheet
for item in self.data_table:
# Number cells
if item.lstrip('-').isdigit():
sheet.cell(row=r, column=c).value = int(item)
sheet.cell(row=r, column=c).number_format = '0'
sheet.column_dimensions[get_column_letter(c)].width = len(item) * 1.5
c += 1
# Emty cells
elif item == '':
c += 1
# Text cells
else:
r += 1
c = 1
sheet.cell(row=r, column=c).value = item
sheet.column_dimensions[get_column_letter(c)].width = len(item) * 2
c += 1
wb.save(spreadsheet_name + '.xlsx')
def write_qc_header(qc_sheet):
headers = ["comment",
"pass/fail",
"#chr",
"gene",
"pos",
"ref",
"alt",
"mutation type",
"status",
"main consequence",
"T VAF",
"N VAF",
"expected VAF",
"transcript link",
"expected HGVSc",
"expected HGVSp",
"mutation ID"
]
row_counter = 1
column_counter = 1
for header in headers:
cell = qc_sheet.cell(row=row_counter, column=column_counter)
cell.value = header
if qc_sheet.column_dimensions[get_column_letter(column_counter)].width < len(str(cell.value)):
qc_sheet.column_dimensions[get_column_letter(column_counter)].width = len(str(cell.value)) + 5
column_counter += 1
row_counter += 1
return row_counter
def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
""" Set merge on a cell range. Range is a cell range (e.g. A1:E1) """
if not range_string and not all((start_row, start_column, end_row, end_column)):
msg = "You have to provide a value either for 'coordinate' or for\
'start_row', 'start_column', 'end_row' *and* 'end_column'"
raise ValueError(msg)
elif not range_string:
range_string = '%s%s:%s%s' % (get_column_letter(start_column),
start_row,
get_column_letter(end_column),
end_row)
elif ":" not in range_string:
if COORD_RE.match(range_string):
return # Single cell, do nothing
raise ValueError("Range must be a cell range (e.g. A1:E1)")
else:
range_string = range_string.replace('$', '')
if range_string not in self._merged_cells:
self._merged_cells.append(range_string)
min_col, min_row, max_col, max_row = range_boundaries(range_string)
rows = range(min_row, max_row+1)
cols = range(min_col, max_col+1)
cells = product(rows, cols)
# all but the top-left cell are removed
for c in islice(cells, 1, None):
if c in self._cells:
del self._cells[c]
def calculate_dimension(self):
"""Return the minimum bounding range for all cells containing data."""
return '%s%d:%s%d' % (
get_column_letter(1),
self.min_row,
get_column_letter(self.max_column or 1),
self.max_row or 1)
def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
""" Set merge on a cell range. Range is a cell range (e.g. A1:E1) """
if not range_string:
if (start_row is None
or start_column is None
or end_row is None
or end_column is None):
msg = "You have to provide a value either for "\
"'coordinate' or for 'start_row', 'start_column', 'end_row' *and* 'end_column'"
raise InsufficientCoordinatesException(msg)
else:
range_string = '%s%s:%s%s' % (get_column_letter(start_column),
start_row,
get_column_letter(end_column),
end_row)
elif ":" not in range_string:
if COORD_RE.match(range_string):
return # Single cell
msg = "Range must be a cell range (e.g. A1:E1)"
raise InsufficientCoordinatesException(msg)
else:
range_string = range_string.replace('$', '')
if range_string not in self._merged_cells:
self._merged_cells.append(range_string)
cells = cells_from_range(range_string)
# only the top-left cell is preserved
for c in islice(chain.from_iterable(cells), 1, None):
if c in self._cells:
del self._cells[c]
def write_key_action_block(self, keyaction, int_row, int_col):
#Write the action name
ka_cell = '%s%s' % (Utils.get_column_letter(int_col), int_row)
worksheet[ka_cell] = action.name
# worksheet.merge_cells('%s%s:%s%s' % (Utils.get_column_letter(int_col), int_row, Utils.get_column_letter(int_col), int_row + action.numParams()))
#Write the action description
desc_cell = '%s%s' % (Utils.get_column_letter(int_col+1), int_row)
worksheet[desc_cell] = action.description
# worksheet.merge_cells('%s%s:%s%s' % (Utils.get_column_letter(int_col+1), int_row, Utils.get_column_letter(int_col + 1), int_row + action.numParams()))
#Write the input parameters for the key action
param_row = int_row
for param in action.input_parameters:
param_cell = '%s%s' % (Utils.get_column_letter(int_col+2), param_row)
worksheet[param_cell] = param.name
param_value_cell = '%s%s' % (Utils.get_column_letter(int_col+3), param_row)
worksheet[param_value_cell] = param.value
param_row+=1
#Write Expected Result
er_cell = '%s%s' % (Utils.get_column_letter(int_col+4), int_row)
worksheet[er_cell] = action.expected_result
# worksheet.merge_cells('%s%s:%s%s' % (Utils.get_column_letter(int_col+4), int_row, Utils.get_column_letter(int_col + 1), int_row + action.numParams()))
if action.numParams() == 0:
int_row+=1
else:
int_row+=action.numParams()
def __str__(self):
fmt = u"{0}!${1}${2}:${3}${4}"
if (self.min_col == self.max_col
and self.min_row == self.max_row):
fmt = u"{0}!${1}${2}"
return fmt.format(self.sheetname,
get_column_letter(self.min_col), self.min_row,
get_column_letter(self.max_col), self.max_row
)
def _check_changes_other_than_code_and_year(first_data, second_data, line_index):
modifications = []
for col_index, obj in enumerate(first_data):
if col_index == ACRONYM_COL_NUMBER and second_data[ACRONYM_COL_NUMBER] != EMPTY_VALUE:
modifications.append('{}{}'.format(get_column_letter(col_index + 1), line_index))
else:
if obj != second_data[col_index] and col_index != ACADEMIC_COL_NUMBER:
modifications.append('{}{}'.format(get_column_letter(col_index + 1), line_index))
return modifications
def setCellValueByColumnAndRow(self,hang,lie,value = None):
for i in self.mergeCellsColumnAndRow:
if hang>=i[1] and hang<=i[3] and lie>=i[0] and lie <=i[2]:
self.__setDanYuanGeWidth(utils.get_column_letter(i[0]));
self.sheet.cell(row=i[1],column=i[0]).value = value;
self.wb.save(self.path);
self.__setDanYuanGeWidth(utils.get_column_letter(lie));
self.sheet.cell(row=hang,column=lie).value = value;
self.wb.save(self.path);
def calculate_dimension(self, force=False):
if not all([self.max_column, self.max_row]):
if force:
self._calculate_dimension()
else:
raise ValueError("Worksheet is unsized, use calculate_dimension(force=True)")
return '%s%d:%s%d' % (
get_column_letter(self.min_column), self.min_row,
get_column_letter(self.max_column), self.max_row
)
def coord(self):
"""
Excel style representation of the range
"""
fmt = "{min_col}{min_row}:{max_col}{max_row}"
if (self.min_col == self.max_col
and self.min_row == self.max_row):
fmt = "{min_col}{min_row}"
return fmt.format(
min_col=get_column_letter(self.min_col),
min_row=self.min_row,
max_col=get_column_letter(self.max_col),
max_row=self.max_row
)
def Convert_Image_To_Excel_Spreadsheet(image_path, resolution=.3, output_file="results.xlsx"):
wb = openpyxl.Workbook() #first create an excel workbook
sheet = wb.get_sheet_by_name("Sheet") #each workbook has a default sheet, so we grab it to use
im = Image.open(image_path)#next we read in our image
width, height = im.size #get the width and height of the image, this will correspond to how many pixels tall and wide the image is.
rgb_im = im.convert('RGB') #now get the rgb data for the image.
#now we need to loop through the image, taking into account the resolution,
for x in range(0, int(width * resolution)):
#for each pixel we use 3 cells in the spreadsheet, each in the same row,
#so only the column value will change for each cell in the pixel.
#also important to note, excel is base 1, so a value of 0 is invalid, this is a slight diversion from how
#we normally code stuff...so we need to take that into acctoun
#calculate and store the colum_indicies for this column.
column_indicies = [get_column_letter((x * 3) + 1), get_column_letter((x * 3) + 2), get_column_letter((x * 3) + 3)]
#in order to preserve the aspect ratio of the image we want to scale down the width of the pixels.
#Our goal is to get 3 cells in a neat square, according to the documentation the height of a cell is 10 px,
#since we have 3 this would normally mean we would have the width be 10/3, however this still resulted in a more rectangle shape,
#with a little tweaking the below creates a close enough square for our 'pixels'
sheet.column_dimensions[column_indicies[0]].width = 10 / 9
sheet.column_dimensions[column_indicies[1]].width = 10 / 9
sheet.column_dimensions[column_indicies[2]].width = 10 / 9
#next we loop through all of the rows for this set of columns.
for row in range(1, int(height * resolution)):
#now we get the rgb data for this pixel. we store this in rgb_data
rgb_data = rgb_im.getpixel((int(x / resolution), int(row / resolution)))
for i in range(3):#next we loop through the 3 cells for this pixel.
colors = [0, 0, 0]#start with pure white,
colors[i] = rgb_data[i]#update a single value from our rgb data
col = get_column_letter((x * 3) + i + 1)#since excel uses a A1 type of coordinate system we need to convert the column number with a letter, thankfully there is allready a function for that in our libraries,
#now get the cell
cell = sheet[col + str(row)]
#just for propserity, I also store the rgb value for this cell, just so it's not blank,
cell.value = rgb_data[i]
#next we recolor the cell.
#the libraries use a string based color scheme, this code takes our colors list and combines them in a way that the libraries
#can make sense of and convert to a color for the cell.
color_string = "".join([str(hex(i))[2:].upper().rjust(2, "0") for i in colors])
cell.fill = PatternFill(fill_type="solid", start_color='FF' + color_string,
end_color='FF' + color_string)
#don't forget to save.
wb.save(output_file)
def rows(self):
"""
Return all cells in range by column
"""
for row in range(self.min_row, self.max_row+1):
yield tuple('%s%d' % (get_column_letter(col), row)
for col in range(self.min_col, self.max_col+1))
def dump_layout_to_excel(layout):
""" https://djangotricks.blogspot.co.uk/2013/12/how-to-export-data-as-excel.html """
array, items = build_pos_array(layout)
response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=Price list.xlsx'
wb = Workbook()
ws = wb.active
ws.title = 'Price List'
widths = [10, 40, 10]
for col_num in range(len(widths)):
ws.column_dimensions[get_column_letter(col_num + 1)].width = widths[col_num]
c = ws.cell(row=1, column=2, value='Price List')
row_num = 2
for row in array:
for col_num in range(len(row)):
if col_num == 0:
if len(row[col_num]) > 2:
description = row[col_num][2]
ws.cell(row=row_num, column=1, value=description)
row_num += 1
else:
if len(row[col_num]) > 2:
item = row[col_num][2]
ws.cell(row=row_num, column=2, value=item.description)
c = ws.cell(row=row_num, column=3, value=item.sale_price)
c.number_format = '£0.00'
row_num += 1
wb.save(response)
return response
def style_range(ws, cell_range, alignment=None, font=None, fill=None, border=None):
"""
:param ws: Excel worksheet instance
:param range: An excel range to style (e.g. A1:F20)
:param alignment: An openpyxl Alignment object
:param font: An openpyxl Font object
:param fill: An openpyxl Fill object
:param border: An openpyxl Border object
"""
start_cell, end_cell = cell_range.split(':')
start_coord = coordinate_from_string(start_cell)
start_row = start_coord[1]
start_col = column_index_from_string(start_coord[0])
end_coord = coordinate_from_string(end_cell)
end_row = end_coord[1]
end_col = column_index_from_string(end_coord[0])
for row in range(start_row, end_row + 1):
for col_idx in range(start_col, end_col + 1):
col = get_column_letter(col_idx)
if alignment:
ws.cell('%s%s' % (col, row)).alignment = alignment
if font:
ws.cell('%s%s' % (col, row)).font = font
if fill:
ws.cell('%s%s' % (col, row)).fill = fill
if border:
ws.cell('%s%s' % (col, row)).border = border
def _parse_row_internal(self, row: ReadOnlyRow):
"""
Parses whole row and returns parsed instance.
"""
instance = {}
required_columns = self.get_required_columns()
for column in self.get_all_columns():
try:
self.parse_column(row, column, instance, column in required_columns)
except XLSImportError as exc:
column_id = self.column_mappings.get(column)
exc.column = get_column_letter(column_id + 1)
exc.column_enum = column
raise exc
for column in self.missing_columns:
parser = self.get_all_columns()[column]
mock_cell = ReadOnlyCell(None, None, None, None)
parser.read_column(row, mock_cell, instance)
self.on_row_read(instance)
return instance
def record(self, data):
from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.utils import get_column_letter
wb = Workbook()
dest_filename = 'empty_book.xlsx'
ws1 = wb.active
ws1.title = "range names"
for row in range(1, 40):
ws1.append([1,2,3,4,5])
ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 3.14
ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
for col in range(27, 54):
_ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
print(ws3['AA10'].value)
wb.save(filename = dest_filename)
def export_xls_zip_all_classes(StudentAdmin, request, queryset):
import openpyxl
from django.utils.encoding import smart_str
from openpyxl.utils import get_column_letter
response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=journal.xlsx'
wb = openpyxl.Workbook()
wb.remove(wb.active)
pos = 1
columns = [
(u"ID", 15),
(u"ФИО", 20),
(u"Класс", 20),
(u"Оценка", 10),
(u"Комментарий", 40),
]
dv = DataValidation(type="decimal",
operator="between",
formula1=0,
formula2=10,
allow_blank=True)
for parallel in Parallel.objects.all():
for group in parallel.class2_set.all():
isinstance(group, Class2)
ws = wb.create_sheet(str(parallel) + str(group.name), pos)
ws.protection.sheet = True
ws.protection.password = '******'
pos += 1
row_num = 0
for col_num in xrange(len(columns)):
c = ws.cell(row=row_num + 1, column=col_num + 1)
c.value = columns[col_num][0]
#c.style.font.bold = True
# set column width
ws.column_dimensions[get_column_letter(col_num + 1)].width = columns[col_num][1]
for student in group.student_set.all():
row_num += 1
row = [
student.pk,
smart_str(student.fname + " " + student.lname + ' ' + student.fathers_name),
smart_str(str(parallel.name) + str(group.name)),
smart_str(''),
smart_str(''),
]
for col_num in xrange(len(row)):
c = ws.cell(row=row_num + 1, column=col_num + 1)
c.alignment = Alignment(wrap_text=True)
lock = False
if col_num + 1 <= 3:
lock = True
c.protection = Protection(locked=lock)
c.value = row[col_num]
response.write(save_virtual_workbook(wb))
return response
def createTimeColumn(self, row, col): ws = self.__ws wf = self.__wf cellWidth = wf.timeCellWidth cellHeight = wf.cellHeight fontSize = wf.timeFontSize self.createHeader(row, col, 'Time') ws.column_dimensions[get_column_letter(col)].width = cellWidth wf.setTopAlignment(row + 1, row + 29, col, col) for i in xrange(0, 29): currentRow = row + i + 1 currentCell = ws.cell(row=currentRow, column=col) ws.row_dimensions[currentRow].height = cellHeight if i%2 == 0: currentCell.value = datetime.datetime.strptime(str(i/2 + 8), '%H') currentCell.number_format = FORMAT_DATE_TIME1 currentCell.font = Font(size=fontSize) if (currentRow + 1)%4 < 2: currentCell.fill = wf.whiteFill else: currentCell.fill = wf.lightgrayFill wf.setBorder(row + 1, row + 29, col, col)
def collapse_cell_addresses(cells, input_ranges=()):
""" Collapse a collection of cell co-ordinates down into an optimal
range or collection of ranges.
E.g. Cells A1, A2, A3, B1, B2 and B3 should have the data-validation
object applied, attempt to collapse down to a single range, A1:B3.
Currently only collapsing contiguous vertical ranges (i.e. above
example results in A1:A3 B1:B3).
"""
ranges = list(input_ranges)
# convert cell into row, col tuple
raw_coords = (coordinate_to_tuple(cell) for cell in cells)
# group by column in order
grouped_coords = defaultdict(list)
for row, col in sorted(raw_coords, key=itemgetter(1)):
grouped_coords[col].append(row)
# create range string from first and last row in column
for col, cells in grouped_coords.items():
col = get_column_letter(col)
fmt = "{0}{1}:{2}{3}"
if len(cells) == 1:
fmt = "{0}{1}"
r = fmt.format(col, min(cells), col, max(cells))
ranges.append(r)
return " ".join(ranges)
def build_sheet(self, data, ws, sheet_name='report', header=None, widths=None):
first_row = 1
column_base = 1
ws.title = re.sub(r'\W+', '', sheet_name)[:30]
if header:
for i, header_cell in enumerate(header):
cell = ws.cell(row=first_row, column=i + column_base)
cell.value = header_cell
cell.font = Font(bold=True)
if widths:
ws.column_dimensions[get_column_letter(i + 1)].width = widths[i]
for row in data:
for i in range(len(row)):
item = row[i]
# If item is a regular string
if isinstance(item, str):
# Change it to a unicode string
try:
row[i] = text_type(item)
except UnicodeDecodeError:
row[i] = text_type(item.decode('utf-8', 'ignore'))
elif type(item) is dict:
row[i] = text_type(item)
try:
ws.append(row)
except ValueError as e:
ws.append([e.message])
except:
ws.append(['Unknown Error'])
def draw(iterable):
for i in iterable:
sheet_ranges.cell(row=i.x+top_left_corner[1]+1,
column=i.y+top_left_corner[0]+1).value = '█'
for i in range(len(sheet_ranges.rows[0])):
sheet_ranges.column_dimensions[get_column_letter(i+1)].width = 3.0
wb.save(OUTPUT_FILENAME)
def point_pos(self, left=0, top=0):
""" tells which cell is under the given coordinates (in pixels)
counting from the top-left corner of the sheet.
Can be used to locate images and charts on the worksheet """
current_col = 1
current_row = 1
column_dimensions = self.column_dimensions
row_dimensions = self.row_dimensions
default_width = points_to_pixels(DEFAULT_COLUMN_WIDTH)
default_height = points_to_pixels(DEFAULT_ROW_HEIGHT)
left_pos = 0
top_pos = 0
while left_pos <= left:
letter = get_column_letter(current_col)
current_col += 1
if letter in column_dimensions:
cdw = column_dimensions[letter].width
if cdw is not None:
left_pos += points_to_pixels(cdw)
continue
left_pos += default_width
while top_pos <= top:
row = current_row
current_row += 1
if row in row_dimensions:
rdh = row_dimensions[row].height
if rdh is not None:
top_pos += points_to_pixels(rdh)
continue
top_pos += default_height
return (letter, row)
def anchor(self):
""" returns the expected position of a cell in pixels from the top-left
of the sheet. For example, A1 anchor should be (0,0).
:rtype: tuple(int, int)
"""
left_columns = (column_index_from_string(self.column) - 1)
column_dimensions = self.parent.column_dimensions
left_anchor = 0
default_width = points_to_pixels(DEFAULT_COLUMN_WIDTH)
for col_idx in range(left_columns):
letter = get_column_letter(col_idx + 1)
if letter in column_dimensions:
cdw = column_dimensions.get(letter).width or default_width
if cdw > 0:
left_anchor += points_to_pixels(cdw)
continue
left_anchor += default_width
row_dimensions = self.parent.row_dimensions
top_anchor = 0
top_rows = (self.row - 1)
default_height = points_to_pixels(DEFAULT_ROW_HEIGHT)
for row_idx in range(1, top_rows + 1):
if row_idx in row_dimensions:
rdh = row_dimensions[row_idx].height or default_height
if rdh > 0:
top_anchor += points_to_pixels(rdh)
continue
top_anchor += default_height
return (left_anchor, top_anchor)
import openpyxl, sys, os
from openpyxl.utils import get_column_letter
#Passed parameters
N = int(sys.argv[1]) #Start position of blank rows
M = int(sys.argv[2]) #Number of blank rows to insert
filename = sys.argv[3]
#Loading the sheet for reading
readingBook = openpyxl.load_workbook('D:\\Scripts\\produceSalesCopy.xlsx')
readingSheet = readingBook.active
#Creating the sheet for writing
writingBook = openpyxl.Workbook()
writingSheet = writingBook.active
#Writing first N lines
for row in range(1,N):
for column in range(1,readingSheet.max_column+1):
columnLetter = get_column_letter(column)
writingSheet[columnLetter+str(row)] = readingSheet[columnLetter+str(row)].value
#Writing next batch of lines after giving space of M number of lines
for row in range(N,readingSheet.max_row+1):
for column in range(1,readingSheet.max_column+1):
columnLetter = get_column_letter(column)
writingSheet[columnLetter+str(row+M)] = readingSheet[columnLetter+str(row)].value
#Saving the workbook
writingBook.save('D:\\Scripts\\'+filename)
import openpyxl, sys, os
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font
n = int(sys.argv[1])
os.chdir(input('Save to Directory: '))
wb = openpyxl.Workbook()
sheet = wb.active
for i in range(1, n + 1):
sheet.cell(row=1, column=i + 1).value = i
sheet.cell(row=i + 1, column=1).value = i
sheet.cell(row=1, column=i + 1).font = Font(bold=True)
sheet.cell(row=i + 1, column=1).font = Font(bold=True)
for eachRow in range(1, n + 1):
for eachCol in range(2, int(sheet.max_column) + 1):
n1 = sheet['A' + str(eachRow + 1)].value
n2 = sheet[get_column_letter(eachCol) + '1'].value
sheet[get_column_letter(eachCol) + str(eachRow + 1)].value = n1 * n2
wb.save('multiplicationTable.xlsx')
print('Multiplication Table is complete.')
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
wb = Workbook()
dest_filename = 'empty_book.xlsx'
ws1 = wb.active
ws1.title = "range names"
for row in range(1, 40):
ws1.append(range(600))
ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 3.14
ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
for col in range(27, 54):
_ = ws3.cell(column=col,
row=row,
value="{0}".format(get_column_letter(col)))
print(ws3['AA10'].value)
wb.save(filename=dest_filename)
wb2 = load_workbook(filename=dest_filename)
sheet_ranges = wb2['range names']
print(sheet_ranges['D18'].value)
def printToFile(xl, results, pairs, winLossMatrix, coasterDict,
preferredFixedWidthFont, manuColors):
print("Writing the results...", end=" ")
if useSpinner:
spinner = Spinner()
spinner.start()
# create and write primary results worksheet
resultws = xl.create_sheet("Ranked Results")
headerRow = [
"Rank", "Coaster", "Total Win Percentage", "Pairwise Win Percentage",
"Total Wins", "Total Losses", "Total Ties", "Pair Wins", "Pair Losses",
"Pair Ties", "Number of Riders"
]
if args.botherRCDB:
headerRow.extend(["Designer/Manufacturer", "Year"])
resultws.append(headerRow)
resultws.column_dimensions['A'].width = 4.83
resultws.column_dimensions['B'].width = 45.83
resultws.column_dimensions['C'].width = 16.83
resultws.column_dimensions['D'].width = 18.83
resultws.column_dimensions['E'].width = 8.83
resultws.column_dimensions['F'].width = 9.83
resultws.column_dimensions['G'].width = 7.83
resultws.column_dimensions['H'].width = 8.33
resultws.column_dimensions['I'].width = 9.33
resultws.column_dimensions['J'].width = 7.33
resultws.column_dimensions['K'].width = 13.83
if args.botherRCDB:
resultws.column_dimensions['L'].width = 23.83
resultws.column_dimensions['M'].width = 8.83
i = 2
for x in results:
resultws.append([
coasterDict[x[0]].overallRank, x[0],
coasterDict[x[0]].totalWinPercentage,
coasterDict[x[0]].pairwiseWinPercentage,
coasterDict[x[0]].totalWins, coasterDict[x[0]].totalLosses,
coasterDict[x[0]].totalTies, coasterDict[x[0]].pairwiseWins,
coasterDict[x[0]].pairwiseLosses, coasterDict[x[0]].pairwiseTies,
coasterDict[x[0]].riders, coasterDict[x[0]].designer,
coasterDict[x[0]].year
])
colorizeRow(resultws, i, [2, 12], coasterDict, x[0], manuColors)
i += 1
resultws.freeze_panes = resultws['A2']
# append coasters that weren't ranked to the bottom of results worksheet
for x in coasterDict.keys():
if x not in [y[0] for y in results] and coasterDict[x].riders > 0:
resultws.append([
"N/A", x, "Insufficient Riders, {0}".format(
coasterDict[x].totalWinPercentage),
"Insufficient Riders, {0}".format(
coasterDict[x].pairwiseWinPercentage),
coasterDict[x].totalWins, coasterDict[x].totalLosses,
coasterDict[x].totalTies, coasterDict[x].pairwiseWins,
coasterDict[x].pairwiseLosses, coasterDict[x].pairwiseTies,
coasterDict[x].riders, coasterDict[x].designer,
coasterDict[x].year
])
colorizeRow(resultws, i, [2, 12], coasterDict, x, manuColors)
i += 1
# append coasters that weren't ridden to the bottom of results worksheet
for x in coasterDict.keys():
if x not in [y[0] for y in results] and coasterDict[x].riders == 0:
resultws.append([
"N/A", x, "No Riders", "No Riders", coasterDict[x].totalWins,
coasterDict[x].totalLosses, coasterDict[x].totalTies,
coasterDict[x].pairwiseWins, coasterDict[x].pairwiseLosses,
coasterDict[x].pairwiseTies, coasterDict[x].riders,
coasterDict[x].designer, coasterDict[x].year
])
colorizeRow(resultws, i, [2, 12], coasterDict, x, manuColors)
i += 1
# create and write pairwise result worksheet
pairws = xl.create_sheet("Ranked Pairs")
pairws.append([
"Rank", "Primary Coaster", "Rival Coaster", "Win Percentage", "Wins",
"Losses", "Ties"
])
pairws.column_dimensions['A'].width = 4.83
pairws.column_dimensions['B'].width = 45.83
pairws.column_dimensions['C'].width = 45.83
pairws.column_dimensions['D'].width = 12.83
pairws.column_dimensions['E'].width = 4.5
pairws.column_dimensions['F'].width = 5.5
pairws.column_dimensions['G'].width = 3.83
i = 2
for x in pairs:
pairws.append([
winLossMatrix[x[0][0], x[0][1]]["Pairwise Rank"], x[0][0], x[0][1],
winLossMatrix[x[0][0], x[0][1]]["Win Percentage"],
winLossMatrix[x[0][0], x[0][1]]["Wins"],
winLossMatrix[x[0][0],
x[0][1]]["Losses"], winLossMatrix[x[0][0],
x[0][1]]["Ties"]
])
colorizeRow(pairws, i, [2], coasterDict, x[0][0], manuColors)
colorizeRow(pairws, i, [3], coasterDict, x[0][1], manuColors)
i += 1
pairws.freeze_panes = pairws['A2']
# create and write Mitch Hawker-style mutual rider comparison worksheet
hawkerWLTws = xl.create_sheet("Coaster vs Coaster Win-Loss-Tie")
headerRow = ["Rank", ""]
for coaster in results:
headerRow.append(coasterDict[coaster[0]].abbr)
hawkerWLTws.append(headerRow)
hawkerWLTws.column_dimensions['A'].width = 4.83
hawkerWLTws.column_dimensions['B'].width = 45.83
for col in range(3, len(results) + 3):
hawkerWLTws.column_dimensions[get_column_letter(col)].width = 12.83
colorizeRow(hawkerWLTws, 1, [col], coasterDict, results[col - 3][0],
manuColors)
for i in range(0, len(results)):
resultRow = [coasterDict[results[i][0]].overallRank, results[i][0]]
winCount = 0
loseCount = 0
tieCount = 0
for j in range(0, len(results)):
coasterA = results[i][0]
coasterB = results[j][0]
cellStr = ""
if coasterA != coasterB:
if winLossMatrix[coasterA, coasterB]["Wins"] > winLossMatrix[
coasterA, coasterB]["Losses"]:
cellStr += "W "
winCount += 1
elif winLossMatrix[coasterA, coasterB]["Wins"] < winLossMatrix[
coasterA, coasterB]["Losses"]:
cellStr += "L "
loseCount += 1
else:
cellStr += "T "
tieCount += 1
cellStr += str(winLossMatrix[coasterA, coasterB]["Wins"]) + "-"
cellStr += str(winLossMatrix[coasterA,
coasterB]["Losses"]) + "-"
cellStr += str(winLossMatrix[coasterA, coasterB]["Ties"])
resultRow.append(cellStr)
hawkerPct = ((winCount +
(tieCount / float(2)) / float(len(results) - 1)) * 100)
resultRow.append(hawkerPct)
hawkerWLTws.append(resultRow)
colorizeRow(hawkerWLTws, i + 2, [2], coasterDict, results[i][0],
manuColors)
hawkerWLTws.freeze_panes = hawkerWLTws['C2']
for col in hawkerWLTws.iter_cols(min_col=3):
for cell in col:
cell.font = preferredFixedWidthFont
# create and write Mitch Hawker-style mutual rider comparison worksheet sorted by Pairwise Win Percentage
resortedResults = sorted(results, key=lambda x: x[2], reverse=True)
hawkerWLT2 = xl.create_sheet("CvC Win-Loss-Tie by PairWin%")
headerRow = ["Rank", ""]
if args.verbose > 0:
print(" ")
for x in resortedResults:
headerRow.append(coasterDict[x[0]].abbr)
if args.verbose > 0:
print("Rank: {0},\tVal: {1}, \tCoaster: {2}".format(
coasterDict[x[0]].overallRank, x[2], x[0]))
hawkerWLT2.append(headerRow)
hawkerWLT2.column_dimensions['A'].width = 4.83
hawkerWLT2.column_dimensions['B'].width = 45.83
for col in range(3, len(resortedResults) + 3):
hawkerWLT2.column_dimensions[get_column_letter(col)].width = 12.83
colorizeRow(hawkerWLT2, 1, [col], coasterDict,
resortedResults[col - 3][0], manuColors)
for i in range(0, len(resortedResults)):
resultRow = [
coasterDict[resortedResults[i][0]].overallRank,
resortedResults[i][0]
]
for j in range(0, len(resortedResults)):
coasterA = resortedResults[i][0]
coasterB = resortedResults[j][0]
cellStr = ""
if coasterA != coasterB:
if winLossMatrix[coasterA, coasterB]["Wins"] > winLossMatrix[
coasterA, coasterB]["Losses"]:
cellStr += "W "
elif winLossMatrix[coasterA, coasterB]["Wins"] < winLossMatrix[
coasterA, coasterB]["Losses"]:
cellStr += "L "
else:
cellStr += "T "
cellStr += str(winLossMatrix[coasterA, coasterB]["Wins"]) + "-"
cellStr += str(winLossMatrix[coasterA,
coasterB]["Losses"]) + "-"
cellStr += str(winLossMatrix[coasterA, coasterB]["Ties"])
resultRow.append(cellStr)
hawkerWLT2.append(resultRow)
colorizeRow(hawkerWLT2, i + 2, [2], coasterDict, resortedResults[i][0],
manuColors)
hawkerWLT2.freeze_panes = hawkerWLT2['C2']
for col in hawkerWLT2.iter_cols(min_col=3):
for cell in col:
cell.font = preferredFixedWidthFont
# create and write sheet to compare where coasters would have ranked if sorted by PairWin%
comparisonws = xl.create_sheet("TotalWin% vs PairWin% Rankings")
comparisonws.append(
["Coaster", "TotalWin% Rank", "PairWin% Rank", "Difference"])
comparisonws.column_dimensions['A'].width = 45.83
comparisonws.column_dimensions['B'].width = 12.83
comparisonws.column_dimensions['C'].width = 12.83
for i in range(0, len(resortedResults)):
coaster = resortedResults[i][0]
oldRank = coasterDict[coaster].overallRank
newRank = i + 1
diff = oldRank - newRank
if diff == 0:
comparisonws.append([coaster, oldRank, newRank, ""])
else:
comparisonws.append([coaster, oldRank, newRank, diff])
if diff <= -16:
diffColor = "ff0000" # maximum red
elif diff < 0:
diffColor = "ff{0}{0}".format(
hex(256 + 16 * diff)[2:]) # gradual red
elif diff < 16:
diffColor = "{0}ff{0}".format(
hex(256 - 16 * diff)[2:]) # gradual green
else:
diffColor = "00ff00" # maximum green
comparisonws.cell(row=i + 2,
column=4).fill = PatternFill("solid",
fgColor=diffColor)
colorizeRow(comparisonws, i + 2, [1], coasterDict, coaster, manuColors)
comparisonws.freeze_panes = comparisonws['A2']
if useSpinner:
spinner.stop()
print(" ")
def validaAux(fila):
sheet = wb['hoja_de_trabajo_bys']
tipo = ""
for row in range(2, sheet.max_row + 1):
if (sheet['F' + str(row)].value == fila["grupo"]
and sheet['H' + str(row)].value == fila["subgrupo"]
and sheet['L' + str(row)].value == fila["procedencia"]
and sheet['Q' + str(row)].value == fila["estado"]
and sheet['N' + str(row)].value == str(
fila["categoria"])): # Compara filtros
if (sheet[fila["celda"] + str(row)].value <> '0'
and sheet[fila["celda"] + str(row)].value is not None
): # Valida que exista un ajuste en bienes y servicios
if (esMayorSumaCH(fila["numajuste"],
sheet['R' + str(row)].value,
sheet['P' + str(row)].value)
and float(sheet[fila["celda"] + str(row)].value) > 0):
if (sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 2) +
str(row)].value <> str(fila["auxPartida"])
or sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 5) +
str(row)].value <> str(
fila["auxContraPartida"])
and sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 1) +
str(row)].value <> str(fila["Partida"])
or sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 4) +
str(row)].value <> str(
fila["ContraPartida"])
): # Valida cuentas de costo hisotrico y depreciacion MAYOR
global contador
contador += 1
muestra_valores(fila)
tipo = "esMayorSumaCH"
print tipo
return False
elif (esMenorSumaCH(fila["numajuste"],
sheet['R' + str(row)].value,
sheet['P' + str(row)].value)
and float(sheet[fila["celda"] + str(row)].value) < 0):
if (sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 2) +
str(row)].value <> str(fila["auxPartida"])
or sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 5) +
str(row)].value <> str(
fila["auxContraPartida"])
and sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 1) +
str(row)].value <> str(fila["Partida"])
or sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 4) +
str(row)].value <> str(
fila["ContraPartida"])
): # Valida cuentas de costo hisotrico y depreciacion menor
global contador
contador += 1
muestra_valores(fila)
tipo = "esMenorSumaCH"
print tipo
return False
elif (esMenorSumaDe(fila["numajuste"],
sheet['S' + str(row)].value,
sheet['R' + str(row)].value,
sheet['P' + str(row)].value)
and float(sheet[fila["celda"] + str(row)].value) < 0):
if (sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 2) +
str(row)].value <> str(fila["auxPartida"])
or sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 5) +
str(row)].value <> str(
fila["auxContraPartida"])
and sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 1) +
str(row)].value <> str(fila["Partida"])
or sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 4) +
str(row)].value <> str(
fila["ContraPartida"])
): # Valida cuentas de costo hisotrico y depreciacion menor
global contador
contador += 1
muestra_valores(fila)
tipo = "esMenorSumaDe"
print tipo
return False
elif (esMayorSumaDe(fila["numajuste"],
sheet['S' + str(row)].value,
sheet['R' + str(row)].value,
sheet['P' + str(row)].value)
and float(sheet[fila["celda"] + str(row)].value) > 0):
if (sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 2) +
str(row)].value <> str(fila["auxPartida"])
or sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 5) +
str(row)].value <> str(
fila["auxContraPartida"])
and sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 1) +
str(row)].value <> str(fila["Partida"])
or sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 4) +
str(row)].value <> str(
fila["ContraPartida"])
): # Valida cuentas de costo hisotrico y depreciacion menor
global contador
contador += 1
muestra_valores(fila)
tipo = "esMayorSumaDe"
print tipo
return False
elif (esBajaSuma(sheet['Q' + str(row)].value,
sheet['P' + str(row)].value,
fila["numajuste"])):
if (sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 2) +
str(row)].value <> str(fila["auxPartida"])
or sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 5) +
str(row)].value <> str(
fila["auxContraPartida"])
and sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 1) +
str(row)].value <> str(fila["Partida"])
or sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 4) +
str(row)].value <> str(
fila["ContraPartida"])
): # Valida cuentas de costo hisotrico y depreciacion menor
global contador
contador += 1
muestra_valores(fila)
tipo = "esBajaSuma"
print tipo
return False
elif (esEliminacionDepreSuma(fila["numajuste"],
sheet['S' + str(row)].value,
sheet['P' + str(row)].value,
sheet['Q' + str(row)].value)):
if (sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 2) +
str(row)].value <> str(fila["auxPartida"])
or sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 5) +
str(row)].value <> str(
fila["auxContraPartida"])
and sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 1) +
str(row)].value <> str(fila["Partida"])
or sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 4) +
str(row)].value <> str(
fila["ContraPartida"])
): # Valida cuentas de costo hisotrico y depreciacion menor
global contador
contador += 1
muestra_valores(fila)
tipo = "esEliminacionDepreSuma"
print tipo
return False
elif (esEliminacionProviValoDesSuma(
sheet['Q' + str(row)].value,
sheet['R' + str(row)].value,
sheet['P' + str(row)].value, fila["numajuste"])
and float(sheet[fila["celda"] + str(row)].value) <> 0):
if (sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 2) +
str(row)].value <> str(fila["auxPartida"])
or sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 5) +
str(row)].value <> str(
fila["auxContraPartida"])
and sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 1) +
str(row)].value <> str(fila["Partida"])
or sheet[get_column_letter(
column_index_from_string(fila["celda"]) + 4) +
str(row)].value <> str(
fila["ContraPartida"])
): # Valida cuentas de costo hisotrico y depreciacion menor
global contador
contador += 1
muestra_valores(fila)
tipo = "esEliminacionProviValoDesSuma"
print tipo
return False
return True
row_start = 1
col_start = 1
row_ends = 29
col_ends = 12
row_span = row_ends - row_start + 1
modi_value = 202000220
string = '合肥惠丰电子科技有限公司 IT末梢终端耗材、零配件出库单 NO:YS03 '
for n in range(counts):
for rows in range(row_start, row_ends + 1):
wbsheet_new.row_dimensions[
rows + n * row_span].height = wbsheet.row_dimensions[rows].height
for cols in range(col_start, col_ends):
wbsheet_new.column_dimensions[get_column_letter(
cols)].width = wbsheet.column_dimensions[get_column_letter(
cols)].width
target_cell = wbsheet_new.cell(row=rows + n * row_span,
column=cols,
value=wbsheet.cell(rows,
cols).value)
if (rows == row_start and cols == col_start):
target_cell = wbsheet_new.cell(row=rows + n * row_span,
column=cols,
value=string + str(modi_value))
modi_value += 1
if (rows == row_start + 10 and cols == col_start):
target_cell = wbsheet_new.cell(row=rows + n * row_span,
column=cols,
value=string + str(modi_value))
modi_value += 1
def get_processed_df_with_formula(df, dept_num):
df_with_row = df.copy()
df_with_row['ROW_NUMBER'] = df_with_row.index + 2
try:
df_header, start_index = get_clean_dataframe(df_with_row, 'date', -1)
except ValueError:
df_header, _ = get_clean_dataframe(df_with_row, 'year', -1)
df_header.columns = list(df_header.columns)[:-1] + ['ROW_NUMBER']
columns_orig = [
item.upper() if isinstance(item, str) else item
for item in df_header.columns
]
df_processed = preprocess_df(df_header, -1, ['ROW_NUMBER'])
year_month_list = list(
set([item for item in zip(df_processed.YEAR, df_processed.MONTH)]))
output_columns = ['DEPARTMENT ID', 'YEAR', 'MONTH', 'PAYMENT', 'BUDGET']
output_df = pd.DataFrame(columns=output_columns)
for i, (year, month) in enumerate(year_month_list):
output_df.loc[i, 'DEPARTMENT ID'] = dept_num
output_df.loc[i, 'YEAR'] = year
output_df.loc[i, 'MONTH'] = month
section = df_processed[((df_processed.YEAR == year) &
(df_processed.MONTH == month))]
rows = section['ROW_NUMBER']
rows = sorted(rows)
payment_header = [
item for item in columns_orig if isinstance(item, str) and (
item.find('PAYMENT') > -1 or item.find('SPENDING') > -1)
and item.find('CUM') == -1
][0]
column_letter_payment = get_column_letter(
columns_orig.index(payment_header) + 1)
output_df.loc[
i,
'PAYMENT'] = f"=SUM('Support Dept {dept_num}'!{column_letter_payment}{rows[0]}:{column_letter_payment}{rows[-1]})"
#print(f'original header is {columns_orig}, \n payment column is {column_letter_payment}')
budget_max = section['BUDGET'].max()
budget_row = rows[list(section['BUDGET']).index(budget_max)]
budget_header = [
item for item in columns_orig if isinstance(item, str)
and item.find('BUDGET') > -1 and item.find('CUM') == -1
][0]
column_letter_budget = get_column_letter(
columns_orig.index(budget_header) + 1)
output_df.loc[
i,
'BUDGET'] = F"='Support Dept {dept_num}'!{column_letter_budget}{budget_row}"
#print(f'original header is {columns_orig}, \n budfet column is {column_letter_budget}')
return output_df
print(c.value)
print('Row' + str(c.row) + ', Column ' + str(c.column) +' is ' + str(c.value))
print('Cell' + c.coordinate + ' is ' +c.value)
print(sheet['C1'].value)
#specifying column and row using integers
print(sheet.cell(row=1, column=2))
print(sheet.cell(row=1, column=2).value)
for i in range(1,8,2):
print(i, sheet.cell(row=i, column=2).value)
print(sheet.max_row)
print(sheet.max_column)
#Converting between Column Letter and Numbers
from openpyxl.utils import get_column_letter, column_index_from_string
print(get_column_letter(1))
print(get_column_letter(2))
print(get_column_letter(27))
print(get_column_letter(920))
print(get_column_letter(sheet.max_column))
print(column_index_from_string('A'))
print(column_index_from_string('AA'))
#Getting rows and columns from the sheets
print(tuple(sheet['A1' :'C3']))
for rowOfCellObjects in sheet['A1':'C3']:
for cellObj in rowOfCellObjects:
print(cellObj.coordinate, cellObj.value)
print('---END OF ROW ---')
format=' %(asctime)s - %(levelname)s- %(message)s')
wb = openpyxl.load_workbook('bob.xlsx')
logging.info(type(wb))
sheet = wb.worksheets[0] #first sheet
logging.info('first worksheet: ' + sheet.title)
logging.info('first cell: value-' + sheet['A1'].value + ' row-' +
str(sheet['A1'].row) + ' column-' + str(sheet['A1'].column) +
' coordinate-' + sheet['A1'].coordinate)
logging.info('first cell: value-' + sheet.cell(1, 1).value + ' row-' +
str(sheet.cell(1, 1).row) + ' column-' +
str(sheet.cell(1, 1).column) + ' coordinate-' +
sheet.cell(1, 1).coordinate)
logging.info('len(row): ' + str(sheet.max_row))
logging.info('len(column): ' + str(sheet.max_column))
logging.info('translate int to str: ' + get_column_letter(sheet.max_row))
logging.info('translate str to int: ' + str(column_index_from_string('B')))
logging.info(tuple(sheet['A1':'C3']))
logging.info('=================excel write==================')
wb1 = openpyxl.Workbook() ##create a new workbook
sheet1 = wb1.worksheets[0]
logging.info('sheet title: ' + sheet1.title) #default sheet name is 'Sheet'
sheet1.title = 'Spam Bacon Eggs sheet' ##Modify first sheet name
logging.info('sheet title: ' + sheet1.title)
wb1.save('bobTestExcel.xlsx')
wb2 = openpyxl.Workbook()
wb2.create_sheet()
wb2.create_sheet(index=0, title='1st sheet')
wb2.create_sheet(index=2, title='middle sheet')
def format_statistics_sheet(self, sheet_name="Statistics"):
"""
Formatting for Statistics Sheet
"""
# Statistics
number_format_list = {
'C': '0',
'E': '0.000',
'F': '0.000000',
'G': '0.00%',
'I': '0.00',
'J': '0.00',
'K': '0.00%',
'M': '0.0',
'N': '0.0000',
'O': '0.00%',
'Q': '0.0',
'R': '0.0000',
'S': '0.00%',
'U': '0.0',
'V': '0.0000',
'W': '0.00%',
'Y': '0.0',
'Z': '0.000',
'AA': '0.000%'
}
width_dict = {
"B": 12,
"C": 2,
"D": 1,
"E": 8,
"F": 11,
"G": 8,
"H": 1,
"I": 13,
"J": 12,
"K": 10,
"L": 1,
"M": 8,
"N": 10,
"O": 10,
"P": 1,
"Q": 8,
"R": 10,
"S": 10,
"T": 1,
"U": 6,
"V": 8,
"W": 10,
"X": 1,
"Y": 8,
"Z": 9,
"AA": 9
}
alignment_list = {"B": "center", "C": "center"}
ws = self.wb[sheet_name]
format_sheet(ws, number_format_list, width_dict, alignment_list)
for offset in range(6):
ws.merge_cells(
get_column_letter(5 + (4 * offset)) + '1:' +
get_column_letter(7 + (4 * offset)) + '1')
format_header(
ws, make_column_property_dict({"2": "center"}, repeat=27,
length=1), 1, 2)
# for cell in col:
# cell.number_format = "DD/MM/YYYY"
# cell.alignment = CENTER
for x in range(1, 14):
cf.cell(row=2, column=3 + x, value=x)
# enter a date with yellow background
cf['D3'] = today_date
cf['D3'].font = FONT_BOLD
cf['D3'].fill = YELLOW_BKGROUND
cf['D3'].alignment = CENTER
for col in range(4, 17):
cf.column_dimensions['{0}'.format(get_column_letter(col))].width = 13
cf['{0}2'.format(get_column_letter(col))].alignment = CENTER
cf['{0}2'.format(get_column_letter(col))].font = FONT_BOLD
cf['{0}3'.format(get_column_letter(col))].number_format = "DD/MM/YYYY"
if col > 4:
# calculate weekly dates
dt = today_date
start = dt - timedelta(days=-7 * (col - 4))
cf['{0}3'.format(get_column_letter(col))].value = start
cf['{0}3'.format(get_column_letter(col))].alignment = CENTER
cf['{0}3'.format(get_column_letter(col))].font = FONT_BOLD
prev_cb = load_workbook(LAST_CASHFLOW_FORECAST_FILENAME)
def read_excel(self):
wb = xl.load_workbook(self.main_excel_path)
sheet = wb['Лист1']
row_max = sheet.max_row
row_min = 1
for i in range(1, row_max):
cell = sheet.cell(row=i, column=1)
cell_row_val = cell.value
if cell_row_val == 'Номер файла' and row_min == 1:
row_min = i
first_col_row_nums = []
cols_dict = {}
try:
type_dict = self.read_type_file()
except:
self.files_classification()
type_dict = self.read_type_file()
signal_type_nums = type_dict['signal_nums']
file_nums_1, file_nums_2 = [], []
for i_row in range(row_min, row_max + 1):
first_col_cell_val = sheet.cell(row=i_row, column=1).value
if isinstance(first_col_cell_val, int):
first_col_row_nums.append(i_row)
fnum_1, fnum_2 = self.fnums_delim(first_col_cell_val)
if fnum_1 in signal_type_nums:
file_nums_1.append(fnum_1)
else:
file_nums_1.append(None)
if fnum_2 in signal_type_nums:
file_nums_2.append(fnum_2)
else:
file_nums_2.append(None)
m = 2
cell = sheet.cell(row=row_min, column=m)
col_name = cell.value
keys = []
while type(col_name) is str:
col_vals = []
keys.append(col_name)
#print(get_column_letter(m), sheet.cell(row=row_min, column=m).value)
for i_row in range(row_min, row_max):
if i_row in first_col_row_nums:
cell_val = sheet.cell(row=i_row, column=m).value
col_vals.append(cell_val)
cols_dict[col_name] = col_vals
if m == 2:
num_of_vals = len(col_vals)
next_col_name = sheet.cell(row=row_min, column=m + 1).value
if type(next_col_name) is str:
m += 1
col_name = sheet.cell(row=row_min, column=m).value
else:
break
col_max = m
print('Последняя колонка:', get_column_letter(col_max), sheet.cell(row=row_min, column=col_max).value)
pl_currents = cols_dict['Ток плазмы, А']
magnetron_delays = cols_dict['Задержка магнетрона, нс']
comments = cols_dict['Комментарий']
mampl_nums, noise_nums, other_nums = [], [], []
for i in range(num_of_vals):
pl_c_val = pl_currents[i]
m_delay = magnetron_delays[i]
if isinstance(pl_c_val, float):
if isinstance(m_delay, int):
if file_nums_1[i] is not None:
mampl_nums.append(file_nums_1[i])
if file_nums_2[i] is not None:
mampl_nums.append(file_nums_2[i])
else:
if file_nums_1[i] is not None:
noise_nums.append(file_nums_1[i])
if file_nums_2[i] is not None:
noise_nums.append(file_nums_2[i])
else:
if file_nums_1[i] is not None:
other_nums.append(file_nums_1[i])
if file_nums_2[i] is not None:
other_nums.append(file_nums_2[i])
row_dicts = {}
for i in range(num_of_vals):
row_dict = {}
for key in keys:
if cols_dict[key][i] is not None:
row_dict[key] = cols_dict[key][i]
if file_nums_1[i] is not None:
row_dicts[file_nums_1[i]] = row_dict
if file_nums_2[i] is not None:
row_dicts[file_nums_2[i]] = row_dict
excel_inf = {'row_dicts': row_dicts,
'comments': comments,
'current_vals': pl_currents,
'noise_nums': noise_nums,
'mamp_nums': mampl_nums,
'other_nums': other_nums}
return excel_inf
def exportWorkbook(request):
# Create a workbook
wb = Workbook(write_only=True)
# Create a named style for the header row
headerstyle = NamedStyle(name="headerstyle")
headerstyle.fill = PatternFill(fill_type="solid", fgColor="70c4f4")
wb.add_named_style(headerstyle)
readlonlyheaderstyle = NamedStyle(name="readlonlyheaderstyle")
readlonlyheaderstyle.fill = PatternFill(fill_type="solid",
fgColor="d0ebfb")
wb.add_named_style(readlonlyheaderstyle)
# Loop over all selected entity types
exportConfig = {"anonymous": request.POST.get("anonymous", False)}
ok = False
for entity_name in request.POST.getlist("entities"):
try:
# Initialize
(app_label, model_label) = entity_name.split(".")
model = apps.get_model(app_label, model_label)
# Verify access rights
permname = get_permission_codename("change", model._meta)
if not request.user.has_perm("%s.%s" % (app_label, permname)):
continue
# Never export some special administrative models
if model in EXCLUDE_FROM_BULK_OPERATIONS:
continue
# Create sheet
ok = True
ws = wb.create_sheet(title=force_text(model._meta.verbose_name))
# Build a list of fields and properties
fields = []
modelfields = []
header = []
source = False
lastmodified = False
owner = False
comment = None
try:
# The admin model of the class can define some fields to exclude from the export
exclude = data_site._registry[model].exclude
except Exception:
exclude = None
for i in model._meta.fields:
if i.name in ["lft", "rght", "lvl"]:
continue # Skip some fields of HierarchyModel
elif i.name == "source":
source = i # Put the source field at the end
elif i.name == "lastmodified":
lastmodified = i # Put the last-modified field at the very end
elif not (exclude and i.name in exclude):
fields.append(i.column)
modelfields.append(i)
cell = WriteOnlyCell(ws,
value=force_text(
i.verbose_name).title())
if i.editable:
cell.style = "headerstyle"
if isinstance(i, ForeignKey):
cell.comment = CellComment(
force_text(
_("Values in this field must exist in the %s table"
) % force_text(i.remote_field.model.
_meta.verbose_name)),
"Author",
)
elif i.choices:
cell.comment = CellComment(
force_text(
_("Accepted values are: %s") %
", ".join([c[0] for c in i.choices])),
"Author",
)
else:
cell.style = "readlonlyheaderstyle"
if not comment:
comment = CellComment(
force_text(_("Read only")),
"Author",
height=20,
width=80,
)
cell.comment = comment
header.append(cell)
if i.name == "owner":
owner = True
if hasattr(model, "propertyFields"):
if callable(model.propertyFields):
props = model.propertyFields(request)
else:
props = model.propertyFields
for i in props:
if i.export:
fields.append(i.name)
cell = WriteOnlyCell(ws,
value=force_text(
i.verbose_name).title())
if i.editable:
cell.style = "headerstyle"
if isinstance(i, ForeignKey):
cell.comment = CellComment(
force_text(
_("Values in this field must exist in the %s table"
) % force_text(i.remote_field.model.
_meta.verbose_name)),
"Author",
)
elif i.choices:
cell.comment = CellComment(
force_text(
_("Accepted values are: %s") %
", ".join([c[0] for c in i.choices])),
"Author",
)
else:
cell.style = "readlonlyheaderstyle"
if not comment:
comment = CellComment(
force_text(_("Read only")),
"Author",
height=20,
width=80,
)
cell.comment = comment
header.append(cell)
modelfields.append(i)
if source:
fields.append("source")
cell = WriteOnlyCell(ws, value=force_text(_("source")).title())
cell.style = "headerstyle"
header.append(cell)
modelfields.append(source)
if lastmodified:
fields.append("lastmodified")
cell = WriteOnlyCell(ws,
value=force_text(
_("last modified")).title())
cell.style = "readlonlyheaderstyle"
if not comment:
comment = CellComment(force_text(_("Read only")),
"Author",
height=20,
width=80)
cell.comment = comment
header.append(cell)
modelfields.append(lastmodified)
# Write a formatted header row
ws.append(header)
# Add an auto-filter to the table
ws.auto_filter.ref = "A1:%s1048576" % get_column_letter(
len(header))
# Use the default manager
if issubclass(model, HierarchyModel):
model.rebuildHierarchy(database=request.database)
query = (model.objects.all().using(request.database).order_by(
"lvl", "pk"))
elif owner:
# First export records with empty owner field
query = (model.objects.all().using(request.database).order_by(
"-owner", "pk"))
else:
query = model.objects.all().using(
request.database).order_by("pk")
# Special annotation of the export query
if hasattr(model, "export_objects"):
query = model.export_objects(query, request)
# Loop over all records
for rec in query.values_list(*fields):
cells = []
fld = 0
for f in rec:
cells.append(
_getCellValue(f,
field=modelfields[fld],
exportConfig=exportConfig))
fld += 1
ws.append(cells)
except Exception:
pass # Silently ignore the error and move on to the next entity.
# Not a single entity to export
if not ok:
raise Exception(_("Nothing to export"))
# Write the excel from memory to a string and then to a HTTP response
output = BytesIO()
wb.save(output)
response = HttpResponse(
content_type=
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
content=output.getvalue(),
)
response["Content-Disposition"] = 'attachment; filename="frepple.xlsx"'
response["Cache-Control"] = "no-cache, no-store"
return response
def generate_expense_report(salesman,
date_range,
expense_list,
r_format='xls'):
expense_report_stream = BytesIO()
total_expenses = 0
if r_format == 'xls':
expense_report = Workbook()
main_sheet = expense_report.active
# Set up some initial styles
main_sheet.freeze_panes = 'B2'
main_sheet.row_dimensions[2].height = 32
main_sheet.row_dimensions[3].height = 22
main_sheet.row_dimensions[4].height = 22
main_sheet.column_dimensions['A'].width = 22
# Enter the name of the Salesman
main_sheet['A2'] = 'Name'
main_sheet['A2'].font = Font(name='Helvetica Neue',
size=12,
bold=True,
color='FFFFFF')
main_sheet['A2'].fill = PatternFill(start_color='004C7F',
end_color='004C7F',
fill_type='solid')
main_sheet['B2'] = str(salesman)
# Loop through the days and add the columns
date_range_it = maya.MayaInterval(
start=maya.when(date_range.split(' - ')[0]),
end=maya.when(date_range.split(' - ')[1]).add(days=1))
col_idx = 2
day_col_labels = {}
all_col_labels = []
for event in date_range_it.split(duration=timedelta(days=1)):
col_label = get_column_letter(col_idx)
day_col_labels[event.start.iso8601().split('T')[0]] = col_label
# Apply styles to the columns
main_sheet.column_dimensions[col_label].width = 16
main_sheet[col_label + '2'].font = Font(name='Helvetica Neue',
size=12,
bold=True,
color='FFFFFF')
main_sheet[col_label + '2'].fill = PatternFill(
start_color='004C7F', end_color='004C7F', fill_type='solid')
main_sheet[col_label + '3'].font = Font(name='Helvetica Neue',
size=11,
bold=True,
color='FFFFFF')
main_sheet[col_label + '3'].fill = PatternFill(
start_color='2F7115', end_color='2F7115', fill_type='solid')
main_sheet[col_label + '3'] = \
event.start.iso8601().split('T')[0]
all_col_labels.append(col_label)
col_idx += 1
last_col_label = get_column_letter(col_idx)
all_col_labels.append(last_col_label)
# Set the date range in the sheet
main_sheet[all_col_labels[-2] + '2'] = 'Date Range'
main_sheet.column_dimensions[last_col_label].width = 16
main_sheet[last_col_label + '2'] = date_range
main_sheet[last_col_label + '2'].alignment = Alignment(wrap_text=True)
main_sheet[last_col_label + '2'].font = Font(name='Helvetica Neue',
size=12,
bold=True,
color='FFFFFF')
main_sheet[last_col_label + '2'].fill = PatternFill(
start_color='004C7F', end_color='004C7F', fill_type='solid')
main_sheet[last_col_label + '3'] = '(Total)'
main_sheet[last_col_label + '3'].font = Font(name='Helvetica Neue',
size=11,
bold=True,
color='FFFFFF')
main_sheet[last_col_label + '3'].fill = PatternFill(
start_color='2F7115', end_color='2F7115', fill_type='solid')
# Loop through the paid by and add it as a group
cur_row = 4
for paid_by in expense_list.keys():
# Set the header for cash expenses
main_sheet['A%s' % cur_row] = paid_by
main_sheet['A%s' % cur_row].font = Font(name='Helvetica Neue',
size=10,
bold=True,
color='FFFFFF')
main_sheet['A%s' % cur_row].fill = PatternFill(
start_color='004C7F', end_color='004C7F', fill_type='solid')
for col_label in all_col_labels:
main_sheet[col_label + str(cur_row)].font = Font(
name='Helvetica Neue', size=10, bold=True, color='FFFFFF')
main_sheet[col_label + str(cur_row)].fill = PatternFill(
start_color='004C7F',
end_color='004C7F',
fill_type='solid')
cur_row += 1
# Add all the cash expenses here
for expense_type, amounts in expense_list[paid_by].items():
if expense_type != 'total':
main_sheet['A%s' % cur_row] = expense_type
main_sheet['A%s' % cur_row].font = Font(
name='Helvetica Neue', size=10, bold=True)
main_sheet['A%s' %
cur_row].alignment = Alignment(wrap_text=True)
line_amount = 0
for t_date, amount in amounts.items():
col_label = day_col_labels[t_date]
main_sheet[col_label + str(cur_row)] = amount
main_sheet[col_label + str(cur_row)].font = Font(
name='Helvetica Neue', size=10, bold=False)
line_amount += amount
main_sheet[last_col_label + str(cur_row)] = line_amount
main_sheet[last_col_label + str(cur_row)].font = Font(
name='Helvetica Neue', size=10, bold=True)
cur_row += 1
# Set the trailer for cash expenses
main_sheet.row_dimensions[cur_row].height = 22
main_sheet['A%s' % cur_row] = 'Total %s Expenses' % paid_by
main_sheet['A%s' % cur_row].font = Font(name='Helvetica Neue',
size=10,
bold=True)
line_amount = 0
for t_date, amount in expense_list[paid_by]['total'].items():
col_label = day_col_labels[t_date]
main_sheet[col_label + str(cur_row)] = amount
main_sheet[col_label + str(cur_row)].font = Font(
name='Helvetica Neue', size=10, bold=True)
line_amount += amount
main_sheet[last_col_label + str(cur_row)] = line_amount
main_sheet[last_col_label + str(cur_row)].font = Font(
name='Helvetica Neue', size=10, bold=True)
total_expenses += line_amount
cur_row += 1
# Set the trailer for the whole file
main_sheet.row_dimensions[cur_row].height = 22
main_sheet[all_col_labels[-2] + str(cur_row)] = 'Total:'
main_sheet[all_col_labels[-2] + str(cur_row)].font = Font(
name='Helvetica Neue', size=10, bold=True)
main_sheet[last_col_label + str(cur_row)] = total_expenses
main_sheet[last_col_label + str(cur_row)].font = Font(
name='Helvetica Neue', size=10, bold=True)
# Finally create a table for this
wsprops = main_sheet.sheet_properties
wsprops.pageSetUpPr = PageSetupProperties(fitToPage=True,
autoPageBreaks=False)
# Create the report and create the django response from it
expense_report.save(expense_report_stream)
elif r_format == 'pdf':
expense_report = SimpleDocTemplate(expense_report_stream,
rightMargin=72,
leftMargin=72,
topMargin=30,
bottomMargin=72,
pagesize=landscape(A4))
# Loop through the days and add the columns
date_range_it = maya.MayaInterval(
start=maya.when(date_range.split(' - ')[0]),
end=maya.when(date_range.split(' - ')[1]).add(days=1))
report_days = []
for event in date_range_it.split(duration=timedelta(days=1)):
report_days.append(event.start.iso8601().split('T')[0])
total_columns = len(report_days) + 1
report_data = [['Name', str(salesman)] +
[''] * (total_columns - 3) + ['Date Range', date_range],
[''] + report_days + ['(Total)']]
report_style = [
('INNERGRID', (0, 0), (-1, -1), 0.5, colors.black),
('BOX', (0, 0), (-1, -1), 0.5, colors.black),
('FONTSIZE', (0, 0), (-1, -1), 8),
('BACKGROUND', (0, 0), (total_columns, 0), '#004C7F'),
('FONTNAME', (0, 0), (total_columns, 0), 'Helvetica-Bold'),
('FONTSIZE', (0, 0), (total_columns, 0), 10),
('TEXTCOLOR', (0, 0), (total_columns, 0), colors.white),
('BACKGROUND', (1, 1), (total_columns, 1), '#2F7115'),
('FONTNAME', (1, 1), (total_columns, 1), 'Helvetica-Bold'),
('FONTSIZE', (1, 1), (total_columns, 1), 9),
('TEXTCOLOR', (1, 1), (total_columns, 1), colors.white)
]
cur_row = 2
for paid_by in expense_list.keys():
# Add the header for this paid_by
report_data.append([paid_by] + [''] * total_columns)
report_style.extend([('BACKGROUND', (0, cur_row),
(total_columns, cur_row), '#004C7F'),
('FONTNAME', (0, cur_row),
(total_columns, cur_row), 'Helvetica-Bold'),
('FONTSIZE', (0, cur_row), (total_columns,
cur_row), 8),
('TEXTCOLOR', (0, cur_row),
(total_columns, cur_row), colors.white)])
cur_row += 1
# Add all the expenses for this paid_by
for expense_type, amounts in expense_list[paid_by].items():
if expense_type != 'total':
line = [expense_type]
line_amount = 0
for day in report_days:
line.append(amounts.get(day, ''))
line_amount += amounts.get(day, 0)
line.append(line_amount)
report_data.append(line)
report_style.extend([
('FONTNAME', (0, cur_row), (0, cur_row),
'Helvetica-Bold'),
('FONTNAME', (total_columns, cur_row),
(total_columns, cur_row), 'Helvetica-Bold'),
])
cur_row += 1
# Add the trailer for this paid_by
pd_trailer = ['Total %s Expenses' % paid_by]
line_totals = 0
for day in report_days:
pd_trailer.append(expense_list[paid_by]['total'].get(day, ''))
line_totals += expense_list[paid_by]['total'].get(day, 0)
pd_trailer.append(line_totals)
report_data.append(pd_trailer)
report_style.append(('FONTNAME', (0, cur_row),
(total_columns, cur_row), 'Helvetica-Bold'))
total_expenses += line_totals
cur_row += 1
# Add the report trailer
report_data.append([''] * (total_columns - 1) +
['Total:', total_expenses])
report_style.append(('FONTNAME', (0, cur_row),
(total_columns, cur_row), 'Helvetica-Bold'))
# Create the table and Set the style
report_table = Table(report_data)
report_table.setStyle(TableStyle(report_style))
expense_report.build([report_table])
return expense_report_stream.getvalue()
wb = openpyxl.Workbook()
sheet = wb.active
make_bold = Font(bold=True)
while cells > 1:
# Write outer cells values and make them bold
sheet.cell(row=cells, column=1).value = cells - 1
sheet.cell(row=1, column=cells).value = cells - 1
sheet.cell(row=cells, column=1).font = make_bold
sheet.cell(row=1, column=cells).font = make_bold
cells -= 1
# Populate the table innards with correct formula
col_length = number + 1
count = 0
while count < number:
colLetter = get_column_letter(sheet.max_column - count)
while col_length > 1:
sheet[colLetter + str(col_length)] = ('=SUM(' + colLetter + '1*A' +
str(col_length) + ')')
col_length -= 1
col_length = number + 1
count += 1
wb.save('multi_table.xlsx')
def validate(settings, excelFile, sheetName, tmpDir, printErrors=False):
'''the main function of valitations, takes settings dictionary (validations)
and returns the validation result
'''
print("Validate Excel Sheet " + sheetName)
errors = []
#open Excel file
print("Parse Excel file")
wb = load_workbook(excelFile,
keep_vba=True,
data_only=True,
read_only=True)
#ws = wb.get_sheet_by_name(sheetName)
ws = wb[sheetName]
progressBar = Bar('Processing', max=ws.max_row)
if 'range' in settings and settings['range'] != None:
settings['range'] = settings['range'] + (str)(ws.max_row)
# range now equals A1:D(150) for example
#iterate excel sheet
rowCounter = 0
for row in ws.iter_rows(settings['range']):
progressBar.next()
columnCounter = 0
rowCounter = rowCounter + 1
#do not parse empty rows
if isEmpty(row):
continue
for cell in row:
columnCounter = columnCounter + 1
try:
value = cell.value
except ValueError:
#case when it is not possible to read value at all from any reason
column = get_column_letter(columnCounter)
coordinates = "%s%d" % (column, rowCounter)
errors.append((coordinates, ValueError))
#find header (first) row
#if the code founded the first header "ID", then it is the header row
if settings['header'] != True:
if value == settings['header']:
#Replace the header with true incase it meets a value of "ID" downside
settings['header'] = True
# skip el row
break
#skip excludes column
if hasattr(cell, 'column') and cell.column in settings['excludes']:
continue
column = get_column_letter(columnCounter)
coordinates = "%s%d" % (column, rowCounter)
## column:A Coordinate:A2, for example
if column in settings['validators']:
for type in settings['validators'][column]:
name = list(type.keys())[0] # notblank, Regex, Length
if name != 'Conditional':
res = isValid(type, value, coordinates, errors)
else:
fieldB = list(type.values())[0]['fieldB']
value2 = ws[fieldB + str(rowCounter)].value
res = isValid(type, value, coordinates, errors, value2)
if not res:
break
elif settings['defaultValidator'] != None:
isValid(settings['defaultValidator'], value, coordinates,
errors)
progressBar.finish()
print("Found %d error(s)" % len(errors))
if (len(errors) > 0):
return markErrors(errors, excelFile, sheetName, tmpDir, printErrors)
return True
def getDateColumn():
for i in range(1, sheet.max_column):
col = get_column_letter(i)
if sheet.cell('%s%s'% (col,'1')).value == currentDate:
return col
async def answer_serial_num(message: types.Message, state=FSMContext):
path_to_file_1 = os.path.abspath('out.xlsx')
path_to_file_2 = os.path.abspath('stock_balance.xlsx ')
path_to_file_3 = os.path.abspath('boriychuk.xlsx')
path_to_file_4 = os.path.abspath('other.xlsx')
path_to_file = [
path_to_file_1, path_to_file_2, path_to_file_3, path_to_file_4
]
data = await state.get_data()
name_list = data.get('answer1')
i = data.get('answer2')
answer = message.text
fin_mess_graph = {}
try:
for path in path_to_file:
graph = {}
regular = str(
name_list[int(answer) - 1]
) # в переменную answer помещяется то, что ответил пользователь в ТГ
wb = openpyxl.load_workbook(path) # Грузим наш прайс-лист
sheets_list = wb.sheetnames # Получаем список всех листов в файле
sheet_active = wb[
sheets_list[0]] # Начинаем работать с самым первым
row_max = sheet_active.max_row # Получаем количество столбцов
column_max = 3
row_min = 1 # Переменная, отвечающая за номер строки
column_min = 1 # Переменная, отвечающая за номер столбца
while column_min <= column_max:
row_min_min = row_min
row_max_max = row_max
while row_min_min <= row_max_max:
row_min_min = str(row_min_min)
word_column = get_column_letter(column_min)
word_column = str(word_column)
word_cell = word_column + row_min_min
data_from_cell = sheet_active[word_cell].value
data_from_cell = str(data_from_cell)
result = re.findall(regular.lower(),
data_from_cell.lower())
if len(result) > 0:
graph['address'] = word_cell
graph['path'] = path
# каждая ячейка, содержащая в себе нужное нам значение, помещается в лист
row_min_min = int(row_min_min)
row_min_min = row_min_min + 1
column_min = column_min + 1
if len(graph) > 0:
if graph['path'] == path_to_file_1:
sel_num = graph['address'][1:]
sel_num = int(sel_num)
main_product_name = sheet_active[sel_num][1].value
product_name = sheet_active[sel_num][1].value
main_manufacture = sheet_active[sel_num][2].value
main_quantity = sheet_active[sel_num][3].value
cost_opt = sheet_active[sel_num][4].value
cost_mem = sheet_active[sel_num][5].value
main_cost_dlr = sheet_active[sel_num][6].value
main_cost_grn = sheet_active[sel_num][7].value
fin_mess_graph[
'main_quantity'] = f'\nСклад "Навионика" кол-во: {main_quantity}'
elif graph['path'] == path_to_file_2:
sel_num = graph['address'][1:]
sel_num = int(sel_num)
product_name = sheet_active[sel_num][1].value
main_manufacture = sheet_active[sel_num][2].value
sh_quantity = sheet_active[sel_num][3].value
cost = sheet_active[sel_num][5].value
try:
sh_opt_cost = sheet_active[sel_num][4].value
sh_opt_cost = round(float(sh_opt_cost))
except:
sh_opt_cost = 'Уточняйте'
fin_mess_graph[
'sh_quantity'] = f'\nСклад "Магазин" кол-во: {sh_quantity}'
elif graph['path'] == path_to_file_3:
sel_num = graph['address'][1:]
sel_num = int(sel_num)
product_name = sheet_active[sel_num][1].value
main_manufacture = sheet_active[sel_num][2].value
b_quantity = sheet_active[sel_num][5].value
try:
cost = float(sheet_active[sel_num][4].value) * 1.1
cost = round(cost)
b_opt_cost = sheet_active[sel_num][4].value
except:
cost = 'Уточняйте'
b_opt_cost = 'Уточняйте'
fin_mess_graph[
'b_quantity'] = f'\nСклад "Борийчук" кол-во: {b_quantity}'
elif graph['path'] == path_to_file_4:
sel_num = graph['address'][1:]
sel_num = int(sel_num)
product_name = sheet_active[sel_num][1].value
main_manufacture = sheet_active[sel_num][2].value
o_quantity = sheet_active[sel_num][5].value
try:
cost = float(sheet_active[sel_num][4].value) * 1.1
cost = round(cost)
o_opt_cost = sheet_active[sel_num][4].value
except:
cost = 'Уточняйте'
o_opt_cost = 'Уточняйте'
fin_mess_graph[
'o_quantity'] = f'\nСклад "Другие поставщики" кол-во: {o_quantity}'
fin_mess = ''
try:
fin_mess += f'\n{main_product_name}'
except:
fin_mess += f'\n{product_name}'
fin_mess += f'\nШифр производителя: {main_manufacture}'
try:
fin_mess += f'\nРРЦ долар с главного склада: {main_cost_dlr}'
fin_mess += f'\nРРЦ грн с главного склада: {main_cost_grn}'
fin_mess += f'\nОпт цена для партнёров: {cost_mem}'
except:
fin_mess += f'\nОпт цена для партнёров: {cost}'
for quantity in fin_mess_graph:
fin_mess += fin_mess_graph[quantity]
await message.answer(fin_mess)
except:
await message.answer('Такого номера нет в списке...')
def column(self):
"""column letter"""
return (self.col_idx or '') and get_column_letter(self.col_idx)
def set_col_size(self, col, size):
self.ws.column_dimensions[get_column_letter(col)].width = size
def read_data(self):
# Use the first sheet
ws_name = self.wb.sheetnames[0]
self.ws = self.wb[ws_name]
# Get row 1
self.header_vals = []
for iCol in range(1, self.ws.max_column+1):
iCol_L = get_column_letter(iCol)
cCell = self.ws['%s1'%(iCol_L)]
self.header_vals.append(cCell.value)
# Check for header
if 'train' in self.header_vals:
self.has_header = True
self.iRow_skip = 1
else:
self.has_header = False
self.header_vals = ['sentences', 'train', 'classify', 'test', 'correct_train', 'correct_test']
self.iRow_skip = 0
# Get the index of columns
if 'sentences' in self.header_vals:
self.index_sent = self.header_vals.index('sentences')
else:
self.index_sent = 0
if 'train' in self.header_vals:
self.index_train = self.header_vals.index('train')
else:
self.index_train = 1
if 'classify' in self.header_vals:
self.index_class = self.header_vals.index('classify')
else:
self.index_class = 2
self.index_class_L = get_column_letter(self.index_class+1)
if 'test' in self.header_vals:
self.index_test = self.header_vals.index('test')
self.has_test = True
else:
self.index_test = 3
self.has_test = False
if 'correct_train' in self.header_vals:
self.index_correct_train = self.header_vals.index('correct_train')
else:
self.index_correct_train = 4
self.index_correct_train_L = get_column_letter(self.index_correct_train+1)
if 'correct_test' in self.header_vals:
self.index_correct_test = self.header_vals.index('correct_test')
else:
self.index_correct_test = 5
self.index_correct_test_L = get_column_letter(self.index_correct_test+1)
# Collect all sentences
self.sent_dat = self.collect_column_data(self.iRow_skip, self.index_sent)
# Collect paired values of sentences and train
self.train_dat = self.collect_column_data(self.iRow_skip, self.index_sent, self.index_train)
# If test is available
if self.has_test:
self.test_dat = self.collect_column_data(self.iRow_skip, self.index_sent, self.index_test)
def processAllBallots(xl, coasterDict, winLossMatrix):
# include spreadsheet containing identifying voter info, if requested
if args.includeExtraInfo > 0:
voterinfows = xl.create_sheet("Voter Info (SENSITIVE)")
voterinfows.append([
"Ballot Filename", "Name", "Email", "City", "State/Province",
"Country", "Coasters Ridden"
])
voterinfows.column_dimensions['A'].width = 24.83
voterinfows.column_dimensions['B'].width = 16.83
voterinfows.column_dimensions['C'].width = 24.83
for col in ['D', 'E', 'F', 'G']:
voterinfows.column_dimensions[col].width = 12.83
# include spreadsheet containing individual ballots, if requested
if args.includeExtraInfo > 1:
# includes each coaster's rank in a separate column
ballotws1 = xl.create_sheet("Ballots with Ranks (SENSITIVE)")
headerRow = ["Ballot Filename"]
for i in range(0, len(coasterDict)):
headerRow.extend(["Rank", "Coaster"])
ballotws1.append(headerRow)
ballotws1.column_dimensions['A'].width = 24.83
for i in range(0, len(coasterDict)):
col1 = (i * 2) + 2
col2 = col1 + 1
ballotws1.column_dimensions[get_column_letter(
col1)].width = 4.83
ballotws1.column_dimensions[get_column_letter(
col2)].width = 45.83
# doesn't include rank data; assumes no coasters are ranked the same
ballotws2 = xl.create_sheet("Ballots Imprecise (SENSITIVE)")
headerRow = ["Ballot Filename"]
for i in range(0, len(coasterDict)):
headerRow.append("Coaster [Rank {0}]".format(i + 1))
ballotws2.append(headerRow)
ballotws2.column_dimensions['A'].width = 24.83
for i in range(0, len(coasterDict)):
ballotws2.column_dimensions[get_column_letter(i +
2)].width = 45.83
# loop over ballots, processing each and saving requested info
for filepath in getBallotFilepaths():
voterInfo, ballotRanks = processBallot(filepath, coasterDict,
winLossMatrix)
if args.includeExtraInfo > 0 and voterInfo:
voterinfows.append(voterInfo)
if args.includeExtraInfo > 1 and ballotRanks:
rowVals1 = [voterInfo[0]]
rowVals2 = [voterInfo[0]]
for coasterAndRank in sorted(ballotRanks.items(),
key=lambda x: x[1]):
if args.verbose > 2:
print("{0}.\t{1}".format(coasterAndRank[1],
coasterAndRank[0]))
rowVals1.extend([coasterAndRank[1], coasterAndRank[0]])
rowVals2.append(coasterAndRank[0])
ballotws1.append(rowVals1)
ballotws2.append(rowVals2)
if args.includeExtraInfo > 0:
voterinfows.freeze_panes = voterinfows['A2']
if args.includeExtraInfo > 1:
ballotws1.freeze_panes = ballotws1['B2']
ballotws2.freeze_panes = ballotws2['B2']
def autosize_ws_columns(worksheet):
for col in worksheet.columns:
column = get_column_letter(col[0].column)
max_length = max(len(str(cell.value)) for cell in col[1:])
adjusted_width = (max_length + 2) * 1.1
worksheet.column_dimensions[column].width = adjusted_width
return e,w,s,n,tb,lb
TOPKDICT={}
TOPK=[]
#way to extract cell indices as integers
for ic, col_cells in enumerate(islice(fsheet.columns,fsheet.max_column)):
#iterates all rows for each column
for ir, row in enumerate(islice(col_cells,fsheet.max_row)):
#iterates within each column all rows
if type(row.value)==str and row.value[1:4]=='SUM':
#print('%s%s: cell.value= %s' % (get_column_letter(ic+1),ir+1,row.value) )
#print("neighborhood", NN_type(ir+1,ic+1,fsheet))
TOPK.append(dsheet.cell(ir+1,ic+1).value)
if TOPK:
for ic, col_cells in enumerate(islice(dsheet.columns, dsheet.max_column)):
for ir, row in enumerate(islice(col_cells, dsheet.max_row)):
print("col and row ", ic, ir)
if type(row.value) == int or type(row.value)==float:
print("neighborhood", NN_type(ir + 1, ic + 1, dsheet))
print('%s%s: cell.value= %s' % (get_column_letter(ic+1),ir+1,row.value) )
TOPKDICT.update({str(ic+1)+ '-'+str(ir+1):dsheet.cell(ir + 1, ic + 1).value})
print(TOPKDICT)
print(max(TOPK))
exit()
def load_filters(ws, init_vector):
FullRange = init_vector +':' + get_column_letter(ws.max_column) \
+ str(ws.max_row)
ws.auto_filter.ref = FullRange
return ws
############################################# Reading from Excel #############################################
# Access the value of a cell on a sheet using "A1" format. This value being column "A" and row "1".
print(sheet["A2"].value)
# Access the value of a specific cell on sheet using numbers for both rows and columns.
print(sheet.cell(row=2, column=2).value)
# Use the above syntax in a loop
for i in range(1, 8):
print(i, sheet.cell(row=i, column=2).value)
# Convert a column location from a letter value to number value or visa versa. Needs to be imported.
print(get_column_letter(1))
print(column_index_from_string("C"))
# Find the total number of rows or columns on a worksheet. Returns answer as an int
print(type(sheet.max_row))
print(sheet.max_column)
# Get a pre defined area from the work sheet
print(sheet['A1': 'C3'])
# Use a loop to print out the area
for row in sheet['A1':'C3']:
for cell in row:
# .coordinate shows the cells location, well surprisingly .value shows the cells value.
print(cell.coordinate, cell.value)
def CreaTabella(squadre): #mi importo tutte le istanze delle squadre
TabellaFinale=openpyxl.Workbook()
Foglio=TabellaFinale.active
Foglio.title="Tabella Squadre"
numSquadre=len(squadre)
c=1
r=3
for squadra in squadre:
listaPortieri=squadra.getListaPortieri()
listaDifensori=squadra.getListaDifensori()
listaCentrocampisti=squadra.getListaCentrocampisti()
listaAttaccanti=squadra.getListaAttaccanti()
for portiere in listaPortieri:
nomePort=portiere.getNome()
squadraPort=portiere.getSquadra()
valorePort=portiere.getValore()
Foglio.cell(row=r,column=c).value=nomePort
Foglio.cell(row=r,column=c+1).value=squadraPort
Foglio.cell(row=r,column=c+2).value=valorePort
r=r+1
r=7
for difensore in listaDifensori:
nomeDif=difensore.getNome()
squadraDif=difensore.getSquadra()
valoreDif=difensore.getValore()
Foglio.cell(row=r,column=c).value=nomeDif
Foglio.cell(row=r,column=c+1).value=squadraDif
Foglio.cell(row=r,column=c+2).value=valoreDif
r=r+1
r=16
for centrocampista in listaCentrocampisti:
nomeCentr=centrocampista.getNome()
squadraCentr=centrocampista.getSquadra()
valoreCentr=centrocampista.getValore()
Foglio.cell(row=r,column=c).value=nomeCentr
Foglio.cell(row=r,column=c+1).value=squadraCentr
Foglio.cell(row=r,column=c+2).value=valoreCentr
r=r+1
r=25
for attaccante in listaAttaccanti:
nomeAtt=attaccante.getNome()
squadraAtt=attaccante.getSquadra()
valoreAtt=attaccante.getValore()
Foglio.cell(row=r,column=c).value=nomeAtt
Foglio.cell(row=r,column=c+1).value=squadraAtt
Foglio.cell(row=r,column=c+2).value=valoreAtt
r=r+1
r=3
c=c+3
a=1
b=1
c=1
d=3
for i in range(numSquadre):
Foglio.merge_cells(start_row=a,start_column=b,end_row=c,end_column=d)
Foglio.merge_cells(start_row=a+1,start_column=b,end_row=c+1,end_column=d)
Foglio.merge_cells(start_row=a+5,start_column=b,end_row=c+5,end_column=d)
Foglio.merge_cells(start_row=a+14,start_column=b,end_row=c+14,end_column=d)
Foglio.merge_cells(start_row=a+23,start_column=b,end_row=c+23,end_column=d)
Foglio.cell(row=2,column=b).value="Portieri"
Foglio.cell(row=2,column=b).font=Font(bold=True,size=12,color='FFFF6600')
Foglio.cell(row=2,column=b).alignment=Alignment(horizontal='center')
Foglio.cell(row=6,column=b).value="Difensori"
Foglio.cell(row=6,column=b).font=Font(bold=True,size=12,color='FF0000FF')
Foglio.cell(row=6,column=b).alignment=Alignment(horizontal='center')
Foglio.cell(row=15,column=b).value="Centrocampisti"
Foglio.cell(row=15,column=b).font=Font(bold=True,size=12,color='FF008000')
Foglio.cell(row=15,column=b).alignment=Alignment(horizontal='center')
Foglio.cell(row=24,column=b).value="Attaccanti"
Foglio.cell(row=24,column=b).font=Font(bold=True,size=12,color='FFFF0000')
Foglio.cell(row=24,column=b).alignment=Alignment(horizontal='center')
letteraColonna=get_column_letter
SquadraTemp=squadre[i]
nomeSquadra=SquadraTemp.getNomeSquadra()
Foglio.cell(row=1,column=b).value=nomeSquadra
Foglio.cell(row=1,column=b).font=Font(bold=True,size=18)
Foglio.cell(row=1,column=b).alignment=Alignment(horizontal='center')
b=b+3
d=d+3
for col in Foglio.columns:
column=col[0].column
if column in [1,4,7,10,13,16,19,22,25,28,31,34,37]:
let=get_column_letter(column)
Foglio.column_dimensions[let].width="17"
TabellaFinale.save('SquadreFantacalcio.xlsx')
import sys
import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font
n = int(sys.argv[1])
wb = openpyxl.Workbook()
sheet = wb.active
column_letter = get_column_letter(n)
for i in range(2, n + 2):
column_style = sheet['A' + str(i)]
column_style.font = Font(bold=True)
sheet['A' + str(i)] = i - 1
row_style = sheet[str(get_column_letter(i)) + '1']
row_style.font = Font(bold=True)
sheet[str(get_column_letter(i)) + '1'] = i - 1
for j in range(2, n + 2):
sheet[str(get_column_letter(i)) + str(j)] = (i - 1) * (j - 1)
wb.save('mt.xlsx')
def format_matches_sheet(self):
ws = self.wb["Matches"]
header_number_format_dict = make_column_property_dict(
{
'5': '0.000',
'6': '0.00'
}, {
"A": "0.000",
"B": "0.00"
}, {
"17": "0.000",
"18": '0.000000',
"19": '0.00%',
"21": "0.00",
"22": "0.00",
"23": '0.00%'
},
repeat=self.n_experiments,
length=3)
header_h_alignment_list = make_column_property_dict(
{
'5': "right",
'6': "right",
}, {
"A": "right",
"B": "right"
}, {
"17": "right",
"18": "right",
"19": "right",
"21": "right",
"22": "right",
"23": "right"
},
repeat=self.n_experiments,
length=3)
# header_v_alignment_list = make_column_property_dict(
# {'5': "center", '6': "center"},
# {"A": "center", "B": "center", "AF": "center", "AG": "center", "AH": "center", "AJ": "center",
# "AK": "center", "AL": "center"})
hits_number_format_dict = make_column_property_dict(
{
'4': '0',
'5': '0',
'6': '0'
}, {"C": "0"}, {
"5": "0.0",
"6": "0.0000",
"7": "0.00%",
"9": "0.0",
"10": "0.0000",
"11": "0.00%",
"13": "0.0",
"14": "0.0000",
"15": "0.00%"
},
repeat=self.n_experiments,
length=3)
hits_alignment_dict = make_column_property_dict(
{
'4': 'center',
'5': 'center',
'6': 'center'
}, {
"B": "center",
"C": "center"
}, {"13": "center"},
repeat=self.n_experiments,
length=3)
width_dict = make_column_property_dict({
"4": 8,
"5": 8,
"6": 11
}, {"B": 12}, {
"5": 6,
"6": 9,
"7": 9,
"9": 6,
"10": 9,
"11": 9,
"13": 5,
"14": 7,
"15": 9,
"17": 8,
"18": 9,
"19": 9,
"21": 14,
"22": 12,
"23": 9,
"4": 1,
"8": 1,
"12": 1,
"16": 1,
"20": 1
},
repeat=self.n_experiments,
length=3)
for row in ws.iter_rows(min_row=1,
max_row=ws.max_row,
min_col=1,
max_col=ws.max_column):
for cell in row:
cell.alignment = Alignment(vertical="center", wrap_text=False)
headers = [x + 1 for x in range(ws.max_row)][3::6]
for column_cells in ws.columns:
length = max(len(as_text(cell.value)) for cell in column_cells)
ws.column_dimensions[get_column_letter(
column_cells[0].column)].width = length
# ws.column_dimensions[column_cells[0].column].bestFit = True
for row in ws.iter_rows(min_row=4,
max_row=ws.max_row,
min_col=1,
max_col=ws.max_column):
for cell in row:
# Column Headers
if cell.row in headers:
if header_number_format_dict and (
cell.column in header_number_format_dict):
cell.number_format = header_number_format_dict[
cell.column]
if header_h_alignment_list and (
cell.column in header_h_alignment_list):
cell.alignment = Alignment(
horizontal=header_h_alignment_list[cell.column],
vertical="bottom",
wrap_text=False)
# Hit Data
else:
if hits_number_format_dict and (
cell.column in hits_number_format_dict):
cell.number_format = hits_number_format_dict[
cell.column]
if hits_alignment_dict and (cell.column
in hits_alignment_dict):
cell.alignment = Alignment(
horizontal=hits_alignment_dict[cell.column],
vertical="center",
wrap_text=False)
if width_dict:
for column in width_dict:
if width_dict[column] == 0:
ws.column_dimensions[column].hidden = True
else:
ws.column_dimensions[column].width = width_dict[column]
format_header(ws,
make_column_property_dict({"4": "center"},
repeat=self.n_experiments,
length=3)) # First Row
format_header(ws,
make_column_property_dict(
{"2": "center"}, {"A": "right"},
repeat=((self.n_experiments * 3) + 25),
length=1), 2, 2) # Second Row
format_header(ws,
make_column_property_dict(
{"2": "center"},
repeat=((self.n_experiments * 3) + 25),
length=1), 3, 3) # Third Row
for offset in range(self.n_experiments):
merge_string = get_column_letter(4 + (
3 * offset)) + '1:' + get_column_letter(6 + (3 * offset)) + '1'
ws.merge_cells(merge_string)
offset = self.n_experiments * 3
for index in [5, 9, 13, 17, 21]:
merge_string = get_column_letter(
index + offset) + '2:' + get_column_letter(index + 2 +
offset) + '2'
ws.merge_cells(merge_string)
import openpyxl
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
wb0 = Workbook()
ws0 = wb0.active
df.columns = ['cadenas']
for x in range(len(df.index)): # numero de filas
for y in range(len(df.cadenas[x])): # caracter de la cadena/ columna
text = (df.cadenas[x])[y]
mau = y + 1
column_letter = get_column_letter(mau)
#ws.cell(row=x,column=y)=text
p = column_letter + str(x + 2)
p = "".join(p)
ws0[p] = str(text)
if x % 10000 == 0 and y == 0:
print(str(itanum(x)) + "/" + str(itanum(len(df.index))))
print("")
for y in range(len(df.cadenas[1])):
text = y
mau = y + 1
column_letter = get_column_letter(mau)
