def openxyl_row_copy(ws, row, strike=False):
copied_row = []
for cell in row:
new_cell = Cell(ws, value=cell.value)
new_cell.style = copy(cell.style)
new_cell.font = new_cell.font + Font(strike=strike)
copied_row.append(new_cell)
return copied_row
def apply_formatting(data):
logger.info('Matching formatting to worksheet')
for d in data:
d = Cell(ws, column='A', row=1, value=d)
d.font = Font(name='Calibri', size=9)
d.alignment = Alignment(horizontal='center',
vertical='center',
wrap_text=True)
yield d
def rank_cells(self, rank, quality):
c = Cell(self.ws, column="A", row=1, value=rank)
color = methods[rank]
if quality == 0:
# If only 1 locale consists of translation, make color "Reddish".
color = "FF" + color[2:]
elif rank != "EQUAL_LOWERCASE" and quality >= 1:
# If translation is consistent across 2 or more locales, then GREEN.
color = "00FF00"
elif quality > 1:
color = "00FF00"
# Fill background of cell with color
pf = PatternFill("solid", fgColor=colors.Color(color))
c.fill = pf
return c
def setRowAndColumnSizes():
SCALE_FACTOR = 0.5
for col in range(1, 1000):
letter = Cell(ws, row=1, column=col).column_letter
ws.column_dimensions[letter].width = 4 * SCALE_FACTOR
for row in range(1, 1000):
ws.row_dimensions[row].height = 21.0 * SCALE_FACTOR
def copyNumpyArrayToSpreadsheet(baseRow, baseCol):
fig = np.load(f"data/fig{RUG}.npy")
print(f"data/fig{RUG}: shape of Numpy array: {fig.shape}, X={X},Y={Y}")
for r in range(fig.shape[0]):
for c in range(fig.shape[1]):
red = fig[r, c, 0]
green = fig[r, c, 1]
blue = fig[r, c, 2]
row = int(baseRow) + r + 1
col = int(baseCol) + c + 1
value = f"{red:02X}{green:02X}{blue:02X}"
z = Cell(ws, row=row, column=col).column_letter
cellId = f"{z}{row}"
ws[cellId].fill = PatternFill(start_color=f"{value}",
fill_type="solid")
def cell(self, row, column, value=None):
# Returns cell, or creates one if doesn't exist
rowfound = None
cellfound = None
if row <= len(self.mockrows):
rowfound = self.mockrows[row - 1]
if not rowfound:
for i in range(len(self.mockrows), row):
self.mockrows.append([])
rowfound = self.mockrows[row - 1]
if column <= len(rowfound):
cellfound = rowfound[column - 1]
if not cellfound:
cellfound = self._createCell(row=row,
column=column,
value=value)
# Put empty cells before our column
if column > (len(rowfound) + 1):
for i in range(column - len(rowfound) - 1):
rowfound.append(
mock.Mock(Cell(self, row, i, value=None)))
rowfound.append(cellfound)
return cellfound
def format_line(line, type):
size = size_dict[type]
for c in line:
c = Cell(ws, value=c)
c.font = Font(bold=True, size=size)
yield c
.format(date),
sheet_name=None)
locations.append([mia_file, 'MIA'])
except FileNotFoundError:
print('No MIA')
if not locations:
continue
order = [
'EE Status', 'Delays Cancellations', 'Aircraft in Work', 'Training',
'Events'
]
wb = openpyxl.Workbook()
ws = wb.active
ws.title = 'Morning Report'
date_cell = Cell(ws, value='Morning Report: ' + date)
date_cell.font = Font(bold=True, size=14)
ws.append([date_cell])
ws.append([])
cols = ['A', 'B', 'C', 'D', 'E']
widths = [21, 12, 20, 24, 24]
for df_dict, loc in locations:
ws.append(format_line([loc + ' Line Maintenance Operations'],
'header'))
for sheet in order:
df = df_dict[sheet]
ws.append(format_line([sheet], 'title'))
if sheet == 'Events':
df = df.set_index('Customer')
df = remove_excess(df)
df = df.replace(0, np.nan)
def set_highlight(self, cell: Cell) -> None:
cell.fill = PatternFill(fill_type='solid',
fgColor=Color(rgb='FFFFF200', type='rgb'),
bgColor=Color(rgb='FFFFFF00', type='rgb'))
def add_comment(self, cell: Cell, message: str) -> None:
comment = Comment(message, 'Salt Log Checker')
cell.comment = comment
def _get_cell(self, rdrowx, rdcolx):
try:
return self.rdsheet._cells[(rdrowx, rdcolx)]
except:
return Cell(self, row=rdrowx, column=rdcolx)
def styled_cells(data, ws, style):
for c in data:
c = Cell(ws, value=c)
c.style = style
yield c
def setRowAndColumnSizes():
for col in range(1, 1000):
letter = Cell(ws, row=3, column=col).column_letter
ws.column_dimensions[letter].width = 4/2
for row in range(1, 1000):
ws.row_dimensions[row].height = 21.0/2
def SafeCell(*args, value=None, **kwargs):
value = remove_invalid_excel_chars(value)
c = Cell(*args, value=value, **kwargs)
if c.data_type == TYPE_FORMULA:
c.data_type = TYPE_STRING
return c