def do_panel_output(args):
#import schema_data.inputs as schema_data_inputs
_validator = get_validator(files('schema_data.inputs').joinpath('panel.json'))
_schema = _validator.schema
wb = Workbook()
default_names = wb.sheetnames
wb.add_named_style(highlight)
_oname = args.panel_output
# Start with the Metadata. Write the header and the value names
ws1 = wb.create_sheet(_schema['properties']['parameters']['title'])
_write_parameters(ws1,_schema['properties']['parameters'])
_fix_width(ws1)
# Now lets make the Panel. Write the header only.
ws2 = wb.create_sheet(_schema['properties']['markers']['title'])
_write_repeating(ws2,_schema['properties']['markers'])
_fix_width(ws2)
# cleanup workbook deleting default sheet name
for _sheet_name in default_names:
#print(_sheet_name)
del wb[_sheet_name]
wb.save(filename = _oname)
def main():
parser = argparse.ArgumentParser()
parser.add_argument("target_dir")
args = parser.parse_args()
target_dir = args.target_dir
file_list = [(f.name, f.stat()) for f in os.scandir(target_dir)]
content_list = [(f[0], format_size(f[1].st_size), format_datetime(f[1].st_ctime),
format_datetime(f[1].st_mtime)) for f in file_list]
content_list.sort(key=lambda a: a[0])
excelpath = os.path.join(target_dir, "Directory List.xlsx")
wb = Workbook()
ws = wb.active
ws.title = os.path.split(target_dir)[1]
ws.page_setup.fitToWidth = 1
ws.append(("Directory",))
ws.append((target_dir,))
ws.append(())
ws.append(("Name", "(Size (KB)", "Date Created", "Date Modified"))
date_style = NamedStyle(name='date_style', number_format='DD/MM/YYYY HH:MM:MM')
wb.add_named_style(date_style)
for i in ('A', 'B', 'C', 'D'):
ws.column_dimensions[i].width = '20'
for i in range(len(content_list)):
for j in range(4):
cell = ws.cell(i+5, j+1)
cell.value = content_list[i][j]
wb.save(excelpath)
def do_report_output(output_path):
_validator = get_validator(files('schema_data.inputs').joinpath('report_definition.json'))
_schema = _validator.schema
wb = Workbook()
default_names = wb.sheetnames
wb.add_named_style(highlight)
# Start with the Metadata. Write the header and the value names
ws0 = wb.create_sheet(_schema['properties']['parameters']['title'])
_write_parameters(ws0,_schema['properties']['parameters'])
_fix_width(ws0)
ws1 = wb.create_sheet(_schema['properties']['population_percentages']['title'])
_write_repeating(ws1,_schema['properties']['population_percentages'])
_fix_width(ws1)
ws2 = wb.create_sheet(_schema['properties']['population_densities']['title'])
_write_repeating(ws2,_schema['properties']['population_densities'])
_fix_width(ws2)
# cleanup workbook deleting default sheet name
for _sheet_name in default_names:
#print(_sheet_name)
del wb[_sheet_name]
wb.save(filename = output_path)
return
def join_Database():
xlsx_to_formated_xlsx = 'media/qbreports/uploads/xlsx_to_formated_xlsx.xlsx'
downlaod_file_name = 'media/qbreports/uploads/custom_qb_report.xlsx'
joined_table_file_name = pandas_df_join()
csv_to_xlsx, google_sheet = finial_file_creation(joined_table_file_name)
dfUpdated = pd.read_csv(csv_to_xlsx, encoding='latin-1')
dfgdoc = pd.read_csv(google_sheet, encoding='latin-1')
print("creating xlsx files")
wb = Workbook()
ws = wb.active
ws.title = 'Matching_Updated'
ws2 = wb.create_sheet()
ws2.title = 'Vendor Price Check Gdoc'
bold = NamedStyle(name='bold')
bold.font = Font(bold=True)
wb.add_named_style(bold)
cell = WriteOnlyCell(ws)
cell.style = 'bold'
greenFill = PatternFill(start_color='32CD32',
end_color='32CD32',
fill_type='solid')
for row in dataframe_to_rows(dfUpdated, index=False, header=True):
ws.append(row)
for row in dataframe_to_rows(dfgdoc, index=False, header=True):
ws2.append(row)
wb.save(xlsx_to_formated_xlsx)
wb = load_workbook(filename=xlsx_to_formated_xlsx)
ws = wb.active
for row in ws.iter_rows():
if row[1].value == "Vendor Name":
for cell in row:
ws[str(cell.coordinate)].font = Font(bold=True)
if row[1].value == None:
for cell in row:
if cell.value != None:
ws[str(cell.coordinate)].fill = greenFill
if row[1].value != None:
for cell in row:
ws[str(cell.coordinate)].font = Font(bold=True)
wb.save(downlaod_file_name)
print("created xlsx files")
qb_Import_File_delete_everything()
return downlaod_file_name
def do_project_folder_output(output_file):
# For now lets keep this with InForm only
_validator = get_validator(files('schema_data.inputs.platforms.InForm').joinpath('project.json'))
_schema = _validator.schema
wb = Workbook()
default_names = wb.sheetnames
wb.add_named_style(highlight)
# Start with the Metadata. Write the header and the value names
ws1 = wb.create_sheet(_schema['properties']['parameters']['title'])
_write_parameters(ws1,_schema['properties']['parameters'])
_fix_width(ws1)
# Now lets make the Panel. Write the header only.
ws2 = wb.create_sheet(_schema['properties']['samples']['title'])
_write_repeating(ws2,_schema['properties']['samples'])
_fix_width(ws2)
# cleanup workbook deleting default sheet name
for _sheet_name in default_names:
#print(_sheet_name)
del wb[_sheet_name]
wb.save(filename = output_file)
def write_to_spreadsheet(self):
'''Formats and writes data to spreadsheet.'''
wb = Workbook()
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=False, size=11)
bd = Side(style='thin', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
wb.add_named_style(highlight) # Register named style
sh1 = wb.active
sh1.title = 'Purchasing Exceptions'
sh1.append(['Part Number'])
sh1['A1'].font = Font(bold=True, size=11)
sh1['A1'].border = Border(left=bd, top=bd, right=bd, bottom=bd)
sh1['A1'].alignment = Alignment(horizontal='center')
rnum = 2
sh1.column_dimensions['A'].width = (30)
# col_width2 = 0
for item in self.part_list:
# sh1_tool_list_data = item
sh1.cell(row=rnum, column=1).value = item
sh1.cell(row=rnum, column=1).style = 'highlight'
# if len(str(sh1_description)) > col_width2:
# col_width2 = len(str(sh1_description))
rnum += 1
# sh1.column_dimensions['A'].width = (col_width2 * 1.125)
save_name = (('{}/Purchasing Exception Report.xlsx').format(self.idir))
self.saved_as_string = ('Results file: {}').format(save_name)
self.file_listbox.insert(tk.END, self.count_string)
self.file_listbox.insert(tk.END, self.saved_as_string)
self.file_listbox.see(tk.END)
wb.save(save_name)
os.startfile(save_name)
def export_xlsx(self, filename):
um = self.units.description
data = [(i+1,v,um) for (i,v) in enumerate(self.values)]
wb = Workbook(write_only = True)
ws = wb.create_sheet()
# create some styles
cellstyle = NamedStyle(name="highlight")
headerstyle = NamedStyle(name='headercell')
wb.add_named_style(cellstyle)
wb.add_named_style(headerstyle)
cellstyle.font = Font(name='Calibri', size=11)
headerstyle.font = Font(name='Calibri', size=11, bold=True)
bd = Side(border_style='thin')
cellstyle.border = Border(bottom=bd, right=bd, top=bd, left=bd)
headerstyle.border = Border(bottom=bd, right=bd, top=bd, left=bd)
header_labels = ['#', 'Value', 'Units']
# write header
header = []
for el in header_labels:
cell = WriteOnlyCell(ws, value=el)
cell.style = 'headercell'
header.append(cell)
ws.append(header)
# write data
for t in data:
row = []
for el in t:
cell = WriteOnlyCell(ws, value=el)
cell.style = 'highlight'
row.append(cell)
ws.append(row)
wb.save(filename) # doctest: +SKIP
def generar_codigos_excel(self):
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
)
response[
'Content-Disposition'] = 'attachment; filename={date}-codigos.xlsx'.format(
date=datetime.now().strftime('%Y%m%d'), )
titulo = NamedStyle(name="titulo")
titulo.font = Font(bold=True, size=10, color='ffffff')
titulo.alignment = Alignment(
horizontal="center",
vertical="center",
wrap_text=True,
)
titulo.fill = PatternFill(
start_color='244062',
end_color='244062',
fill_type='solid',
)
wb = Workbook()
wb.add_named_style(titulo)
sheet = wb.active
column_dimensions = sheet.column_dimensions['A']
column_dimensions.width = 20
column_dimensions = sheet.column_dimensions['B']
column_dimensions.width = 20
column_dimensions = sheet.column_dimensions['C']
column_dimensions.width = 20
column_dimensions = sheet.column_dimensions['D']
column_dimensions.width = 20
column_dimensions = sheet.column_dimensions['E']
column_dimensions.width = 20
sheet['A1'] = "CODIGO PRINCIPAL"
sheet['A1'].style = titulo
sheet['B1'] = "CODIGO EXTERNO"
sheet['B1'].style = titulo
sheet['C1'] = "CODIGO PROVEEDOR"
sheet['C1'].style = titulo
sheet['D1'] = "CODIGO TESTIGO"
sheet['D1'].style = titulo
sheet['E1'] = "CODIGO BOLSA"
sheet['E1'].style = titulo
for codigo in self.__codigos:
sheet.append(
(codigo.numero, codigo.cod_externo, codigo.cod_proveedor,
codigo.cod_testigo, codigo.cod_bolsa))
wb.save(response)
return response
def create_template(template_path, mylogger):
logger = mylogger
logger.info("Reading and validating panel.json")
_validator1 = get_validator(files('schemas').joinpath('panel.json'))
_schema1 = _validator1.schema
logger.info("Reading and validating samples.json")
_validator2 = get_validator(files('schemas').joinpath('samples.json'))
_schema2 = _validator2.schema
logger.info("Reading and validating pipeline.json")
_validator3 = get_validator(files('schemas').joinpath('pipeline.json'))
_schema3 = _validator3.schema
logger.info("Creating the Workbook")
wb = Workbook()
default_names = wb.sheetnames
wb.add_named_style(highlight)
logger.info("Start with Panel Metadata")
ws0 = wb.create_sheet("Panel Parameters")
_write_parameters(ws0, [_schema1['properties']['parameters']])
_fix_width(ws0)
logger.info("Set the panel definition")
ws1 = wb.create_sheet("Panel Definition")
_write_repeating(ws1, _schema1['properties']['markers'])
_fix_width(ws1)
logger.info(
"Create the sample manifest and add some example annotation columns")
example_annotations = ["Batch", "Timepoint", "Response"]
ws2 = wb.create_sheet("Sample Manifest")
ncols = _write_repeating(ws2,
_schema2['properties']['samples'],
exclude=['sample_annotations'])
_write_annotations(ws2, example_annotations, start_idx=ncols)
_fix_width(ws2)
logger.info("Make a table to define the sample annotations")
ws3 = wb.create_sheet("Sample Annotations")
_write_repeating(ws3, _schema2['definitions']['annotation_level'])
_fix_width(ws3)
logger.info("End with the pipeline version")
ws4 = wb.create_sheet("Meta")
_write_parameters(ws4, [_schema3])
_fix_width(ws4)
# Manually set the version
ws4.cell(row=2, column=2).value = get_version()
# cleanup workbook deleting default sheet name
for _sheet_name in default_names:
del wb[_sheet_name]
wb.save(filename=template_path)
return
def csv_to_xlsx(export_datas):
wb = Workbook()
my_style = NamedStyle(name="style")
my_style.border = Border(left=Side(style='dashed'),
right=Side(style='dashed'),
top=Side(style='dashed'),
bottom=Side(style='dashed'))
my_style.font = Font(name='맑은 고딕',
size=11,
bold=False,
italic=False,
underline='none',
strike=False,
color='FF000000')
wb.add_named_style(my_style)
for export_data in export_datas:
print(f"export_data:{export_data}")
page = str(export_data['page'])
index = str(export_data['index'])
sheet_name = f'page-{page.zfill(4)}-table-{index.zfill(2)}'
export_data['sheet_name'] = sheet_name
ws = wb.create_sheet()
ws.title = sheet_name
with open(export_data['csv_path'], 'r', encoding='utf8') as f:
next(f)
for row in csv.reader(f):
ws.append(row)
cols = ws.max_column
rows = ws.max_row
header_fill = PatternFill("solid", fgColor="667b68")
body_fill = PatternFill("solid", fgColor="f7f9f1")
for row in ws.iter_rows(min_row=1,
max_row=rows,
min_col=1,
max_col=cols):
for cell in row:
cell.style = my_style
if (cell.row > 1):
cell.fill = body_fill
else:
cell.fill = header_fill
cell.font = Font(b=True, color="ead253")
output_path = os.path.dirname(export_datas[0]['csv_path'])
output_path += f'\{os.path.basename(output_path)}.xlsx'
wb.save(output_path)
wb.close()
process_table(output_path, export_datas)
def write_xlsx(xlsxname, nameset):
wb = Workbook()
wb.add_named_style(colhead)
# Use first work sheet named 'All' to collect all patches from all banks
ws_all = wb.active
ws_all.title = "All"
ws_all_row = 1
ws_all.freeze_panes = "A2"
# Set column header styles for 'All' worksheet
for col, (value, width, headstyle, _) in COLUMNS_ALL.items():
ws_all[col + "1"] = value
ws_all[col + "1"].style = headstyle
ws_all.column_dimensions[col].width = width
# Create one work sheet for each patch bank
for pbnum, patchbank in enumerate(nameset):
if not patchbank.patchlist:
continue
ws = wb.create_sheet(title=sanitize(patchbank.name))
ws.freeze_panes = "A2"
# Set column header styles for patch bank worksheet
for col, (value, width, headstyle, _) in COLUMNS_BANK.items():
ws[col + "1"] = value
ws[col + "1"].style = headstyle
ws.column_dimensions[col].width = width
for row, patch in enumerate(patchbank.patchlist, start=2):
if patchbank.name != "GM":
ws_all.append((patchbank.name, patchbank.msb, patchbank.lsb,
patch.program, patch.category, patch.name))
ws_all_row += 1
# Set cell styles for row in 'All' work sheet
for column in COLUMNS_ALL:
ws_all["%s%i" %
(column, ws_all_row)].style = COLUMNS_ALL[column][3]
ws.append((patch.program, patch.category, patch.name))
# Set cell styles for row in patch bank work sheet
for column in COLUMNS_BANK:
ws["%s%i" % (column, row)].style = COLUMNS_BANK[column][3]
# Set auto filter for 'All' work sheet
ws_all.auto_filter.ref = ws_all.dimensions
log.info("Writing '%s'...", xlsxname)
wb.save(xlsxname)
def healthcheckreport(healthchecklist, exportlocation):
wb = Workbook()
dest_filename = 'Health-Check-Report.xlsx'
dest_path = exportlocation + '\\' + dest_filename
ws1 = wb.active
# Continue on with work
ws1.title = "Health Check"
ws1.append(['Hostname', 'Error', 'Description'])
startrow = 2
for row in healthchecklist:
ws1['A' + str(startrow)] = row.get('Hostname')
ws1['B' + str(startrow)] = row.get('Error')
ws1['C' + str(startrow)] = row.get('Description')
startrow = startrow + 1
wb.add_named_style(HeaderStyle)
# Set styles on header row
for cell in ws1["1:1"]:
cell.style = 'BoldHeader'
# Set Column Width
for col in ws1.columns:
max_length = 0
column = col[0].column # Get the column name
for cell in col:
try: # Necessary to avoid error on empty cells
if len(str(cell.value)) > max_length:
max_length = len(cell.value)
except:
pass
adjusted_width = (max_length + 2) * 1.2
ws1.column_dimensions[column].width = adjusted_width
# Save File
try:
wb.save(filename=dest_path)
except:
print 'Error creating the report: ' + dest_path + '. File might be currently in use.'
return
# Sorting
try:
excel = win32com.client.Dispatch("Excel.Application")
wb = excel.Workbooks.Open(dest_path)
ws = wb.Worksheets('Health Check')
ws.Range('A2:C50000').Sort(Key1=ws.Range('A1'),
Order1=1,
Orientation=1)
ws.Range('A1:C1').AutoFilter(1)
wb.Save()
excel.Application.Quit()
except:
# Throw no error
pass
print 'Successfully created Health Check Report'
def _generate_spreadsheet_data(cls, request, out, report, *args, **kwargs):
# Create a workbook
wb = Workbook(write_only=True)
ws = wb.create_sheet(title=report.name)
# Create a named style for the header row
readlonlyheaderstyle = NamedStyle(name="readlonlyheaderstyle")
readlonlyheaderstyle.fill = PatternFill(fill_type="solid",
fgColor="d0ebfb")
wb.add_named_style(readlonlyheaderstyle)
# Run the query
conn = None
try:
conn = create_connection(request.database)
comment = CellComment(force_text(_("Read only")),
"Author",
height=20,
width=80)
with conn.cursor() as cursor:
sqlrole = settings.DATABASES[request.database].get(
"SQL_ROLE", "report_role")
if sqlrole:
cursor.execute("set role %s" % (sqlrole, ))
cursor.execute(sql=cls.getSQL(report.sql))
if cursor.description:
# Write header row
header = []
for f in cursor.description:
cell = WriteOnlyCell(ws, value=f[0])
cell.style = "readlonlyheaderstyle"
cell.comment = comment
header.append(cell)
ws.append(header)
# Add an auto-filter to the table
ws.auto_filter.ref = "A1:%s1048576" % get_column_letter(
len(header))
# Write all output rows
for result in cursor.fetchall():
ws.append(
[_getCellValue(i, request=request) for i in result])
# Write the spreadsheet
wb.save(out)
finally:
if conn:
conn.close()
def write_bmon_spreadsheet(sensors, filename):
"""Saves an Excel spreadsheet to 'filename' that contains the neede sensor information
to import into BMON.
"""
columns = [
('Building', 23),
('Sensor ID', 32),
('Title', 28),
('Unit Label', 11),
('Sensor Group', 29),
('Sort Order', 8),
('Is Calc Field', 8),
('Calc or Transform Function', 24),
('Function Parameters', 33),
]
# Make Workbook and get the worksheet
wb = Workbook()
ws = wb.active
ws.title = 'Main'
ws.append(list(zip(*columns))[0])
# create all the sensor rows
for s in sensors:
for param, unit in s['params']:
rec = [
s['bldg'],
f"{s['dev_eui']}_{param}",
f"{s['name']} {config.param_label(param)}",
unit,
]
ws.append(rec)
title = NamedStyle(name='title')
title.font = Font(bold=True)
bd = Side(style='thin', color='000000')
title.border = Border(bottom=bd)
title.alignment = Alignment(horizontal='center', wrap_text=True)
wb.add_named_style(title)
for col in 'ABCDEFGHI':
ws[f'{col}1'].style = 'title'
column_widths = list(zip(*columns))[1]
for i, column_width in enumerate(column_widths):
ws.column_dimensions[get_column_letter(i + 1)].width = column_width
wb.save(filename)
def do_analysis_output(output_file):
_validator1 = get_validator(files('schema_data.inputs').joinpath('panel.json'))
_validator2 = get_validator(files('schema_data.inputs.platforms.InForm').joinpath('analysis.json'))
_schema1 = _validator1.schema
_schema2 = _validator2.schema
#_schema1 = json.loads(files('schema_data.inputs').joinpath('panel.json').read_text())
#_schema2 = json.loads(files('schema_data.inputs.platforms.InForm').joinpath('analysis.json').read_text())
wb = Workbook()
default_names = wb.sheetnames
wb.add_named_style(highlight)
# Start with the Metadata. Write the header and the value names
ws0 = wb.create_sheet(_schema2['properties']['parameters']['title'])
_write_parameters(ws0,[_schema1['properties']['parameters'],_schema2['properties']['parameters']])
_fix_width(ws0)
ws1 = wb.create_sheet(_schema1['properties']['markers']['title'])
_write_repeating(ws1,_schema1['properties']['markers'])
_fix_width(ws1)
ws2 = wb.create_sheet(_schema2['properties']['inform_exports']['title'])
_write_repeating(ws2,_schema2['properties']['inform_exports'])
_fix_width(ws2)
ws3 = wb.create_sheet(_schema2['properties']['mutually_exclusive_phenotypes']['title'])
_write_repeating(ws3,_schema2['properties']['mutually_exclusive_phenotypes'])
_fix_width(ws3)
ws4 = wb.create_sheet(_schema2['properties']['binary_phenotypes']['title'])
_write_repeating(ws4,_schema2['properties']['binary_phenotypes'])
_fix_width(ws4)
ws5 = wb.create_sheet(_schema2['properties']['regions']['title'])
_write_repeating(ws5,_schema2['properties']['regions'])
_fix_width(ws5)
# cleanup workbook deleting default sheet name
for _sheet_name in default_names:
#print(_sheet_name)
del wb[_sheet_name]
wb.save(filename = output_file)
return
def update_workbook_with_new_record(wb: Workbook, report_date: date,
report_time: time):
column_title_to_value_dict = {}
column_title_to_value_dict[
sitrep_column_constants.DATE_COLUMN] = report_date
column_title_to_value_dict[
sitrep_column_constants.TIME_COLUMN] = report_time
# csse data
csse_data_dict = get_csse_data(report_date)
column_title_to_value_dict.update(csse_data_dict)
# vdh Data
vdh_dict = get_vdh_data(report_date)
column_title_to_value_dict.update(vdh_dict)
# Write in No Data
for column_title in NO_DATA_COLUMNS:
column_title_to_value_dict[column_title] = NO_DATA
# https://openpyxl.readthedocs.io/en/stable/api/openpyxl.workbook.workbook.html
# check if style names are registered, and if not, register
workbook_style_names = wb.style_names
for style_name in STYLE_NAME_TO_STYLE_DICT.keys():
if style_name not in workbook_style_names:
wb.add_named_style(STYLE_NAME_TO_STYLE_DICT[style_name])
# Pull the sheet
sheet = wb[file_constants.SHEET_NAME]
# # Add a row at the end
# last_row_number = get_last_row(sheet) + 1
# sheet.insert_rows(last_row_number)
last_row_number = file_constants.get_row_number(report_date)
# With the formulas, now we don't look fro the last row number, we have a hardcoded map.
# Update the last row with values for report_date
for column_title, column_letter in column_title_to_letter_dicts.items():
cell = sheet[get_cell(column_letter, last_row_number)]
if column_title in column_title_to_value_dict:
cell.value = column_title_to_value_dict[column_title]
if column_title in COLUMN_NAME_TO_STYLE_NAME_DICT:
cell.style = COLUMN_NAME_TO_STYLE_NAME_DICT[column_title]
wrap_text="True")
wsh2.border = Border(left=bd2, top=bd2, right=bd2, bottom=bd2)
wsh2.fill = PatternFill("solid", fgColor="305496")
wsh2.font = Font(bold=True, size=15, color="FFFFFF")
ws_odd = NamedStyle(name="ws_odd")
ws_odd.alignment = Alignment(horizontal="center", vertical="center")
ws_odd.border = Border(left=bd2, top=bd2, right=bd2, bottom=bd2)
ws_odd.fill = PatternFill("solid", fgColor="D9E1F2")
ws_odd.font = Font(bold=False, size=12, color="44546A")
ws_even = NamedStyle(name="ws_even")
ws_even.alignment = Alignment(horizontal="center", vertical="center")
ws_even.border = Border(left=bd2, top=bd2, right=bd2, bottom=bd2)
ws_even.font = Font(bold=False, size=12, color="44546A")
wb = Workbook()
wb.add_named_style(wsh1)
wb.add_named_style(wsh2)
wb.add_named_style(ws_odd)
wb.add_named_style(ws_even)
dest_file = 'export.xlsx'
ws1 = wb.active
ws1.title = "Tenant"
ws2 = wb.create_sheet(title="VRF")
ws3 = wb.create_sheet(title="Bridge Domain")
ws4 = wb.create_sheet(title="Subnet")
ws5 = wb.create_sheet(title="DHCP Relay")
ws6 = wb.create_sheet(title="Access Interfaces")
ws7 = wb.create_sheet(title="Static Port Mappings")
ws2 = wb["VRF"]
def export_to_excel(self, bag, export_path=None):
def as_text(value):
if value is None:
return ""
return str(value)
if len(bag) == 0:
return None
try:
# create workbook
wb = Workbook()
# create header style
highlight = NamedStyle(name="highlight")
highlight.font = Font(name='DejaVu Sans', bold=True, size=10)
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
wb.add_named_style(highlight)
# create data style
normal = NamedStyle(name="normal")
normal.font = Font(name='DejaVu Sans', bold=False, size=10)
wb.add_named_style(normal)
# Get worksheet
ws = wb.active
# build header
header = []
for field in HEADER_FIELDS:
header.append(str(HEADER[field]))
ws.append(header)
# Set data
data = []
for sid in bag:
row = []
metadata = self.srvdtb.get_sapnote_metadata(sid)
for field in HEADER_FIELDS:
if field == 'collections':
cols = ', '.join([self.srvclt.get_name_by_cid(col) for col in metadata[field]])
row.append(cols)
elif field == 'releasedon':
excel_date = self.srvutl.get_excel_date(metadata[field])
row.append(excel_date)
else:
row.append(str(metadata[field]))
ws.append(row)
data.append(row)
# assign style to header
for col in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']:
cell = col + '1'
ws[cell].style = 'highlight'
# assign style to data
for col in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']:
for i in range(len(bag)):
cell = col + '%d' % (i + 2)
ws[cell].style = 'normal'
# adjust columns width
try:
for column_cells in ws.columns:
length = max(len(as_text(cell.value)) for cell in column_cells)
ws.column_dimensions[COL[column_cells[0].column]].width = length
except Exception as error:
self.log.error(error)
self.log.error(self.get_traceback())
self.log.error("This piece of code isn't working on Windows...")
# header autofilter and sorting
ws.auto_filter.ref = "A1:J1"
ws.auto_filter.add_sort_condition("I2:I%d" % len(bag))
# save to export path
wb.save(export_path)
return True
except Exception as error:
self.log.error(error)
self.log.error(self.get_traceback())
return False
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 create_groups_xlsx(day):
activate("de")
day = GroupAssignment.objects.monday(day)
days = [day + timedelta(days=i) for i in range(5)]
wb = Workbook()
ws = wb.active
thin_border = Side(border_style="thin", color="00000000")
medium_border = Side(border_style="medium", color="00000000")
font = Font(name="Calibri", size=14)
centered = NamedStyle("centered")
centered.font = font
centered.alignment = Alignment(horizontal="center", vertical="center")
wb.add_named_style(centered)
dark = NamedStyle("dark")
dark.font = font
dark.fill = PatternFill("solid", "cccccc")
dark.border = Border(
top=thin_border, right=thin_border, bottom=thin_border, left=thin_border
)
wb.add_named_style(dark)
darker = NamedStyle("darker")
darker.border = Border(top=thin_border, bottom=thin_border)
darker.font = Font(name="Calibri", size=14, bold=True)
darker.fill = PatternFill("solid", "aaaaaa")
wb.add_named_style(darker)
darker_border_left = NamedStyle("darkerBorderLeft")
darker_border_left.border = Border(
top=thin_border, bottom=thin_border, left=medium_border
)
darker_border_left.font = darker.font
darker_border_left.fill = darker.fill
wb.add_named_style(darker_border_left)
border = NamedStyle("borderThickLeft")
border.border = Border(
top=thin_border, right=thin_border, bottom=thin_border, left=medium_border
)
border.font = font
wb.add_named_style(border)
border = NamedStyle("borderThickBottom")
border.border = Border(bottom=medium_border)
border.font = font
wb.add_named_style(border)
border = NamedStyle("borderThinLeft")
border.border = Border(left=thin_border)
border.font = font
wb.add_named_style(border)
border = NamedStyle("borderThinBottom")
border.border = Border(bottom=thin_border)
border.font = font
wb.add_named_style(border)
borderThin = NamedStyle("borderThin")
borderThin.border = Border(
top=thin_border, right=thin_border, bottom=thin_border, left=thin_border
)
borderThin.font = font
wb.add_named_style(borderThin)
vertical_text = Alignment(text_rotation=90)
def day_column(weekday):
return 2 + 9 * weekday
def style_row(row, style):
ws[c(0, row)].style = style
ws[c(1, row)].style = style
ws[c(day_column(5), row)].style = (
"darkerBorderLeft" if style == "darker" else style
)
for i in range(5):
for j in range(9):
ws[c(day_column(i) + j, row)].style = style
if style == "darker":
ws[c(day_column(i), row)].style = "darkerBorderLeft"
def column_width(column, width):
ws.column_dimensions[columns[column]].width = width
def row_height(row, height):
ws.row_dimensions[row + 1].height = height
ws[c(0, 1)].style = "borderThickBottom"
ws[c(1, 1)].style = "borderThickBottom"
ws[c(day_column(5), 1)].style = "borderThickBottom"
for i, cell in enumerate(
[
date_format(day, "F y"),
"Woche %s" % date_format(day, "W"),
"Auftragsnummer Arbeit",
"LEITUNG",
"ZIVIS",
]
):
ws[c(0, i + 1)] = cell
ws[c(day_column(5), i + 1)] = cell
ws[c(0, i + 1)].style = "borderThinBottom"
ws[c(1, i + 1)].style = "borderThinBottom"
if i > 0:
ws[c(day_column(5), i + 1)].style = "borderThickLeft"
if i < 2:
ws[c(0, i + 1)].style = centered
ws[c(day_column(5), i + 1)].style = centered
column_width(0, 35)
column_width(1, 15)
column_width(day_column(5), 35)
ws[c(0, 1)].style = "borderThickBottom"
ws[c(1, 1)].style = "borderThickBottom"
ws[c(0, 1)].alignment = centered.alignment
ws[c(1, 1)].alignment = centered.alignment
ws[c(0, 2)].style = "borderThinBottom"
ws[c(0, 2)].alignment = centered.alignment
ws[c(day_column(5), 1)].style = "borderThickBottom"
ws[c(day_column(5), 1)].alignment = centered.alignment
ws[c(day_column(5), 2)].style = "borderThickLeft"
ws[c(day_column(5), 2)].alignment = centered.alignment
for i, current in enumerate(days):
ws[c(day_column(i), 0)] = date_format(current, "l")
ws[c(day_column(i), 1)] = date_format(current, "d.m.y")
ws[c(day_column(i), 0)].style = centered
ws[c(day_column(i), 1)].style = "borderThickBottom"
ws[c(day_column(i), 1)].alignment = centered.alignment
ws.merge_cells("%s:%s" % (c(day_column(i), 0), c(day_column(i + 1) - 1, 0)))
ws.merge_cells("%s:%s" % (c(day_column(i), 1), c(day_column(i + 1) - 1, 1)))
ws[c(day_column(i), 2)] = "Absenz"
for k in range(2, 499):
ws[c(day_column(i), k)].style = "borderThickLeft"
ws[c(day_column(i) + 1, k)].style = "borderThin"
for j in range(1, 9):
ws[c(day_column(i) + j, 2)] = "%s)" % j
style = "borderThin" if j % 2 else "dark"
for k in range(2, 499):
ws[c(day_column(i) + j, k)].style = style
ws[c(day_column(i) + j, 2)].alignment = vertical_text
column_width(day_column(i) + j, 7)
ws[c(day_column(i), 2)].alignment = vertical_text
column_width(day_column(i), 7)
row_height(2, 250)
row_height(3, 60)
row_height(4, 60)
# ZIVIS line
style_row(5, "darker")
assignments = defaultdict(list)
seen_assignments = set()
for ga in GroupAssignment.objects.filter(week=day).select_related(
"assignment__drudge__user"
):
assignments[ga.group_id].append(ga.assignment)
seen_assignments.add(ga.assignment_id)
free_assignments = (
Assignment.objects.for_date(day)
.exclude(pk__in=seen_assignments)
.select_related("drudge__user")
)
absences = defaultdict(dict)
for absence in Absence.objects.filter(days__overlap=days):
for day in absence.days:
absences[absence.assignment_id][day] = absence
def add_group(row, group_name, assignments):
ws[c(0, row)] = group_name
ws[c(day_column(5), row)] = group_name
style_row(row, "darker")
# TODO courses (UNA/MSK)
for assignment in assignments:
row += 1
ws[c(0, row)] = assignment.drudge.user.get_full_name()
ws[c(day_column(5), row)] = assignment.drudge.user.get_full_name()
ws[c(0, row)].style = "borderThinBottom"
ws[c(1, row)].style = "borderThinBottom"
ws[c(day_column(5), row)].style = "borderThickLeft"
row_height(row, 35)
if assignment.date_from in days:
ws[c(1, row)] = "NEU"
elif assignment.determine_date_until() in days:
ws[c(1, row)] = "ENDE"
else:
ws[c(1, row)] = date_format(assignment.determine_date_until(), "d.m.y")
for i, current in enumerate(days):
if current < assignment.date_from:
ws[c(day_column(i), row)] = "Vor Beginn"
elif current > assignment.determine_date_until():
ws[c(day_column(i), row)] = "Nach Ende"
elif current in absences[assignment.id]:
ws[c(day_column(i), row)] = absences[assignment.id][
current
].pretty_reason()
# Skip some lines
for i in range(0, max(3, 6 - len(assignments))):
row += 1
row_height(row, 35)
ws[c(0, row)].style = "borderThinBottom"
ws[c(1, row)].style = "borderThinBottom"
ws[c(day_column(5), row)].style = "borderThickLeft"
row += 1
return row
row = 6
for group in Group.objects.active():
row = add_group(row, group.name, assignments[group.id])
row = add_group(row, "Nicht zugeteilt", free_assignments)
return wb
def write_dest(xlsx_name, schema_name):
border = Border(
left=Side(border_style=borders.BORDER_THIN, color='FF000000'),
right=Side(border_style=borders.BORDER_THIN, color='FF000000'),
top=Side(border_style=borders.BORDER_THIN, color='FF000000'),
bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')
)
alignment = Alignment(horizontal='justify', vertical='bottom',
text_rotation=0, wrap_text=False,
shrink_to_fit=True, indent=0)
fill = PatternFill(fill_type=None, start_color='FFFFFFFF')
# 基本的样式
basic_style = NamedStyle(name="basic_style", font=Font(name='Microsoft YaHei')
, border=border, alignment=alignment, fill=fill)
title_style = copy(basic_style)
title_style.name = 'title_style'
title_style.font = Font(name='Microsoft YaHei', b=True, size=20, color='00215757')
title_style.alignment = Alignment(horizontal='center', vertical='bottom',
text_rotation=0, wrap_text=False,
shrink_to_fit=True, indent=0)
title_style.fill = PatternFill(fill_type=fills.FILL_SOLID, start_color='00B2CBED')
header_style = copy(basic_style)
header_style.name='header_style'
header_style.font = Font(name='Microsoft YaHei', b=True, size=15, color='00215757')
header_style.fill = PatternFill(fill_type=fills.FILL_SOLID, start_color='00BAA87F')
common_style = copy(basic_style)
common_style.name = 'common_style'
link_style = copy(basic_style)
link_style.name = 'link_style'
link_style.font = Font(name='Microsoft YaHei', color=colors.BLUE, underline='single')
table_data = load_schema(schema_name)
wb = Workbook()
wb.add_named_style(basic_style)
wb.add_named_style(title_style)
wb.add_named_style(header_style)
wb.add_named_style(common_style)
bstyle = NamedStyle(name='bstyle', border=Border(
bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')))
wb.add_named_style(bstyle)
wb.active.title = "首页列表"
for table in table_data:
ws = wb.create_sheet(title=table[0])
ws.merge_cells('E3:I3') # 合并单元格
ws['E3'].style = title_style
ws['F2'].style = bstyle
ws['G2'].style = bstyle
ws['H2'].style = bstyle
ws['I2'].style = bstyle
ws['J3'].style = bstyle
ws['E3'] = table[0]
ws['E4'].style = header_style
ws['E4'] = '列名'
ws['F4'].style = header_style
ws['F4'] = '类型'
ws['G4'].style = header_style
ws['G4'] = '空值约束'
ws['H4'].style = header_style
ws['H4'] = '默认值'
ws['I4'].style = header_style
ws['I4'] = '备注'
ws.column_dimensions['E'].width = 30
ws.column_dimensions['F'].width = 20
ws.column_dimensions['G'].width = 12
ws.column_dimensions['H'].width = 25
ws.column_dimensions['I'].width = 40
for idx, each_column in enumerate(table[2:]):
ws['E{}'.format(idx + 5)].style = common_style
ws['E{}'.format(idx + 5)] = each_column[0]
ws['F{}'.format(idx + 5)].style = common_style
ws['F{}'.format(idx + 5)] = each_column[1]
ws['G{}'.format(idx + 5)].style = common_style
ws['G{}'.format(idx + 5)] = each_column[2]
ws['H{}'.format(idx + 5)].style = common_style
ws['H{}'.format(idx + 5)] = each_column[3]
ws['I{}'.format(idx + 5)].style = common_style
ws['I{}'.format(idx + 5)] = each_column[4]
ws = wb['首页列表']
ws.merge_cells('D3:F3')
ws['D3'].style = title_style
ws['E2'].style = bstyle
ws['F2'].style = bstyle
ws['G3'].style = bstyle
ws['D3'] = 'MySQL数据库系统表'
ws['D4'].style = header_style
ws['D4'] = '编号'
ws['E4'].style = header_style
ws['E4'] = '表名'
ws['F4'].style = header_style
ws['F4'] = '详情链接'
ws.column_dimensions['D'].width = 15
ws.column_dimensions['E'].width = 35
ws.column_dimensions['F'].width = 45
for inx, val in enumerate(table_data):
ws['D{}'.format(inx + 5)].style = common_style
ws['D{}'.format(inx + 5)] = inx + 1
ws['E{}'.format(inx + 5)].style = common_style
ws['E{}'.format(inx + 5)] = val[1]
linkcell = ws['F{}'.format(inx + 5)]
linkcell.style = link_style
linkcell.value = val[0]
linkcell.hyperlink = '#{0}!{1}'.format(val[0], 'E3')
wb.save(filename=xlsx_name)
def create_excel_headword_file(letter):
cf = config.ConfigFile()
text_files_path = (cf.configfile[cf.computername]['original_files_path'])
# sections_and_pbs = []
# open the specific html file
file_name = letter + ".html"
text_file_path = text_files_path + file_name
with open(text_file_path, 'r') as f:
soup = BeautifulSoup(f)
sections_and_pbs = soup.select(".section,[title='page break']") #pb = page break
# get page numbers and headwords
page_numbers_and_headwords = get_page_numbers_and_headwords(letter, sections_and_pbs)
# od = OrderedDict(sorted(Counter(page_numbers_and_headwords).items()))
pprint.pprint(page_numbers_and_headwords)
# write the Excel file
wb = Workbook()
excel_files_path = (cf.configfile[cf.computername]['excel_files_path'])
excel_file_name = letter + ".xlsx"
excel_file_path = excel_files_path + excel_file_name
page_numbers = list(set(x[0] for x in page_numbers_and_headwords))
for page_counter, page_number in enumerate(page_numbers, 0):
headwords_for_page = [x[1] for x in page_numbers_and_headwords if x[0] == page_number]
#get worksheet title
if page_counter == 0:
active_worksheet = wb.active
active_worksheet.title = str(start_pages[letter])
else:
active_worksheet = wb.create_sheet(title=str(start_pages[letter] + page_counter))
# data validation
dv = DataValidation(type="list", formula1='"yes,no,adjust"', allow_blank=True)
active_worksheet.add_data_validation(dv)
#named styles
style_title = NamedStyle(name="style_title")
style_title.font = Font(bold=True, color=colours[letter], italic=True)
# had to wrap this in try / except because sometimes getting that style_title already existed
# when creating the workbook!
try:
wb.add_named_style(style_title)
except ValueError:
pass
# titles
active_worksheet["A1"] = "Entry"
active_worksheet["A1"].style = 'style_title'
active_worksheet["B1"] = "Headword"
active_worksheet["B1"].style = 'style_title'
active_worksheet["C1"] = "Status"
active_worksheet["C1"].style = 'style_title'
active_worksheet["D1"] = "Adjusted"
active_worksheet["D1"].style = 'style_title'
for counter, headword in enumerate(headwords_for_page, 1):
active_worksheet.cell(row=counter + 1, column=1, value=counter)
active_worksheet.cell(row=counter + 1, column=1).font = Font(bold=True)
active_worksheet.cell(row=counter + 1, column=2, value=headword)
dv.add("C"+str(counter + 1)) # couldn't use row column syntax
rows = range(1, len(headwords_for_page) + 1 + 1)
columns = range(1, 4 + 1)
for row in rows:
for col in columns:
active_worksheet.cell(row, col).alignment = Alignment(horizontal='center', vertical='center')
active_worksheet.sheet_format.defaultRowHeight = 22
active_worksheet.sheet_format.baseColWidth = 12
active_worksheet.sheet_view.zoomScale = 140
active_worksheet.sheet_view.showGridLines = False
active_worksheet.sheet_properties.tabColor = colours[letter]
wb.save(filename = excel_file_path)
return True
str_style.alignment = Alignment(horizontal='center', vertical='center')
str_style.border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
str_style_fill = NamedStyle(name='str_style_fill')
str_style_fill.font = Font(name='微软雅黑', size=11)
str_style_fill.alignment = Alignment(horizontal='center', vertical='center')
str_style_fill.border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
str_style_fill.fill = PatternFill('solid', fgColor='E5E5E5')
wb.add_named_style(title_style)
wb.add_named_style(num_style)
wb.add_named_style(num_style_fill)
wb.add_named_style(str_style)
wb.add_named_style(str_style_fill)
rens = rens_list(cur, '前线')
write(wb, rens, '整体')
write(wb, rens, '车险')
write(wb, rens, '非车险')
write_zhong_zhi(wb, cur, rens, '分公司营业一部')
write_zhong_zhi(wb, cur, rens, '曲靖')
write_zhong_zhi(wb, cur, rens, '文山')
write_zhong_zhi(wb, cur, rens, '大理')
def join_Database():
joined_table_file_name = 'media/qbreports/uploads/qb_omx_joined_tables.csv'
csv_file_to_xlsx = 'media/qbreports/uploads/csv_file_to_xlsx.csv'
xlsx_to_formated_xlsx = 'media/qbreports/uploads/xlsx_to_formated_xlsx.xlsx'
downlaod_file_name = 'media/qbreports/uploads/custom_qb_report.xlsx'
google_doc_sheet = 'media/qbreports/uploads/google_doc_details.csv'
df = pd.DataFrame(
list(QBImportFile.objects.all().values(
'notes', 'customID', 'vendorName', 'invoiceDate', 'invoiceNumber',
'poNumb', 'invocieTotal', 'dropShipFee', 'shippingFee', 'omxPO',
'omxTotal', 'totalDifference', 'discrepancy', 'memo')))
df = df[[
'notes', 'customID', 'vendorName', 'invoiceDate', 'invoiceNumber',
'poNumb', 'invocieTotal', 'dropShipFee', 'shippingFee', 'omxPO',
'omxTotal', 'totalDifference', 'discrepancy', 'memo'
]]
df2 = pd.DataFrame(
list(OMXOrderHistory.objects.all().values('customID', 'itemCode',
'description', 'dropShipCOG',
'qty', 'total')))
df2 = df2[[
'customID', 'itemCode', 'description', 'dropShipCOG', 'qty', 'total'
]]
df3 = pd.merge(df, df2, on='customID', how='left')
df3.to_csv(joined_table_file_name, sep=',', encoding='utf-8')
writeFileList = []
writeFileList.append([
"Notes", 'Vendor Name', 'Invoice Date', 'Invoice Number', 'PO Num',
'Invoice Total', 'Drop Ship Fee', 'Shipping Fee', 'OMX PO',
'OMX Total', 'Total Difference', 'Discrepancy', 'Memo'
])
itemHeader = [
"", "", "", "", "", "", 'Code', 'Item Name', 'QTY',
'OMX Unit Price (Drop Ship)', 'Drop Ship Total', "", ""
]
moreThanOneItem = []
google_doc_headers = [
'Vendor Name', 'Invoice Date', 'Invoice Number', 'OMX Order Number',
'Quantity', "Item Code", 'OMX Price', 'Invoice Price'
]
with open(joined_table_file_name, 'rt',
encoding='utf8') as f, open(csv_file_to_xlsx,
'w',
newline='',
encoding='utf8') as writeCSV, open(
google_doc_sheet,
'w',
newline='',
encoding='utf8') as gdocCSV:
write = csv.writer(writeCSV, delimiter=',')
gwrite = csv.writer(gdocCSV, delimiter=',')
readCSV = csv.DictReader(f, delimiter=',')
fileList = []
newList = []
google_doc_list = []
google_doc_list.append(google_doc_headers)
for row in readCSV:
fileList.append(row)
for i in range(len(fileList)):
google_doc_format = [
fileList[i]['vendorName'], fileList[i]['invoiceDate'],
fileList[i]['invoiceNumber'], fileList[i]['omxPO'],
fileList[i]['qty'], fileList[i]['itemCode'],
fileList[i]['dropShipCOG'], fileList[i]['invocieTotal']
]
invoiceDetails = [
fileList[i]['notes'], fileList[i]['vendorName'],
fileList[i]['invoiceDate'], fileList[i]['invoiceNumber'],
fileList[i]['poNumb'], fileList[i]['invocieTotal'],
fileList[i]['dropShipFee'], fileList[i]['shippingFee'],
fileList[i]['omxPO'], fileList[i]['omxTotal'],
fileList[i]['totalDifference'], fileList[i]['discrepancy'],
fileList[i]['memo']
]
productDetails = [
"", "", "", fileList[i]['invoiceNumber'],
fileList[i]['poNumb'], "", fileList[i]['itemCode'],
fileList[i]['description'], fileList[i]['qty'],
fileList[i]['dropShipCOG'], fileList[i]['total'], "", ""
]
try:
if fileList[i]['omxPO'] in moreThanOneItem and fileList[i][
'invoiceNumber'] == fileList[i - 1]['invoiceNumber']:
writeFileList.append(productDetails)
google_doc_list.append(google_doc_format)
elif fileList[i]['discrepancy'] == None or fileList[i][
'discrepancy'] == 'Duplicate - NOT UPLOADED' or fileList[
i]['discrepancy'] == '':
if fileList[i]['omxPO'] == fileList[i + 1]['omxPO']:
continue
else:
writeFileList.append(invoiceDetails)
elif fileList[i]['omxPO'] == '0':
writeFileList.append(invoiceDetails)
elif fileList[i]['omxPO'] == fileList[i + 1][
'omxPO'] and fileList[i]['invoiceNumber'] == fileList[
i + 1]['invoiceNumber']:
moreThanOneItem.append(fileList[i]['omxPO'])
writeFileList.append(invoiceDetails)
writeFileList.append(itemHeader)
writeFileList.append(productDetails)
google_doc_list.append(google_doc_format)
else:
writeFileList.append(invoiceDetails)
writeFileList.append(itemHeader)
writeFileList.append(productDetails)
google_doc_list.append(google_doc_format)
except IndexError:
print(newList.append(invoiceDetails))
for x in writeFileList:
write.writerow(x)
for x in google_doc_list:
gwrite.writerow(x)
dfUpdated = pd.read_csv(csv_file_to_xlsx, encoding='latin-1')
dfgdoc = pd.read_csv(google_doc_sheet, encoding='latin-1')
wb = Workbook()
ws = wb.active
ws.title = 'Matching_Updated'
ws2 = wb.create_sheet()
ws2.title = 'Vendor Price Check Gdoc'
bold = NamedStyle(name='bold')
bold.font = Font(bold=True)
wb.add_named_style(bold)
cell = WriteOnlyCell(ws)
cell.style = 'bold'
greenFill = PatternFill(start_color='32CD32',
end_color='32CD32',
fill_type='solid')
for row in dataframe_to_rows(dfUpdated, index=False, header=True):
ws.append(row)
for row in dataframe_to_rows(dfgdoc, index=False, header=True):
ws2.append(row)
wb.save(xlsx_to_formated_xlsx)
wb = load_workbook(filename=xlsx_to_formated_xlsx)
ws = wb.active
for row in ws.iter_rows():
if row[1].value == "Vendor Name":
for cell in row:
ws[str(cell.coordinate)].font = Font(bold=True)
if row[1].value == None:
for cell in row:
if cell.value != None:
ws[str(cell.coordinate)].fill = greenFill
if row[1].value != None:
for cell in row:
ws[str(cell.coordinate)].font = Font(bold=True)
wb.save(downlaod_file_name)
qb_Import_File_delete_everything()
return downlaod_file_name
minor_style = NamedStyle(name='minor_style')
thin = Side(border_style='thin', color='000000')
main_style.border = Border(top=thin, left=thin, right=thin, bottom=thin)
minor_style.border = Border(top=thin, left=thin, right=thin, bottom=thin)
main_style.fill = PatternFill('solid', fgColor='6fb1bd')
minor_style.fill = PatternFill('solid', fgColor='b3dee7')
main_style.font = Font(size=12, name='Calibri')
minor_style.font = Font(size=12, name='Calibri')
# open workbook, rename title
wb = Workbook()
ws = wb.active
ws.title = file_name + '_spec_list'
ws.sheet_properties.tabColor = "20FF20"
wb.add_named_style(main_style)
wb.add_named_style(minor_style)
# change width of column (convert 2.85points=1mm)
ws.column_dimensions["A"].width = 20
ws.column_dimensions["B"].width = 130
ws.column_dimensions["C"].width = 60
ws.column_dimensions["D"].width = 35
ws.column_dimensions["E"].width = 45
ws.column_dimensions["F"].width = 20
ws.column_dimensions["G"].width = 20
ws.column_dimensions["H"].width = 25
ws.column_dimensions["I"].width = 40
ws.row_dimensions[1].height = 50
class ExcelCreator:
def __init__(self, json_path, out_put_path):
self.json_path = json_path
self.out_put_path = out_put_path
self.wb = Workbook()
try:
f = open(json_path, 'r')
self.data = json.load(f)
f.close()
except Exception:
raise Exception(
f"Please make sure your test json file exist in path {out_put_path}"
)
self._create_test_sheet()
self.style = self._set_style()
def _create_test_sheet(self):
self.ws_summary = self.wb.create_sheet("DashBoard", 0)
# self.ws_test_details = self.wb.create_sheet("TestDetails", 1)
def _set_style(self):
my_style = NamedStyle(name="my_style")
my_style.font = Font(name='Calibri')
bd = Side(style='thin')
my_style.border = Border(left=bd, top=bd, right=bd, bottom=bd)
self.wb.add_named_style(my_style)
return "my_style"
# Write the summary details in Sheet: DashBoard
def _write_summary(self):
# write config summary to 1 - 2
self._single_line_dict(self.ws_summary, 1, self.data['testConfig'])
# write feature summary from row 4
self._multiple_lines_list(self.ws_summary, 4,
self.data['featureSummary'])
# write test details summary from current max row + 2
from_row = self.ws_summary.max_row + 2
self._multiple_lines_list(self.ws_summary,
from_row,
self.data['testDetails'],
ignore_text="steps")
# write cases total from current max row + 2
from_row = self.ws_summary.max_row + 2
self._single_line_dict(self.ws_summary, from_row,
self.data['casesChart'])
# write steps total from current max row + 2
from_row = self.ws_summary.max_row + 2
self._single_line_dict(self.ws_summary, from_row,
self.data['stepsChart'])
self._auto_adjust_width(self.ws_summary)
# Write the test details in Sheet: TestDetails
# Suggest you to see the test details in html report
# This method is disable default
def _write_details(self):
tests = self.data['testDetails']
for i in range(len(tests)):
steps = tests[i]['steps']
test = tests[i]
del test['steps']
if i == 0:
self._single_line_dict(self.ws_test_details, 1, test)
else:
from_row = self.ws_test_details.max_row + 2
self._single_line_dict(self.ws_test_details, from_row, test)
from_row = self.ws_test_details.max_row + 1
self._multiple_lines_list(self.ws_test_details, from_row, steps)
self._auto_adjust_width(self.ws_test_details)
def _single_line_dict(self, sheet, from_row, dict_data):
index = 1
for key, value in dict_data.items():
sheet.cell(row=from_row, column=index, value=key.title())
sheet.cell(row=from_row, column=index).style = self.style
sheet.cell(row=from_row,
column=index).fill = PatternFill('solid',
fgColor="95B3D7")
sheet.cell(row=from_row + 1, column=index, value=str(value))
sheet.cell(row=from_row + 1, column=index).style = self.style
index += 1
def _multiple_lines_list(self,
sheet,
from_row,
list_data,
ignore_text=None):
index = 1
for k in list_data[0].keys():
if ignore_text and ignore_text == k:
continue
sheet.cell(row=from_row, column=index, value=k.title())
sheet.cell(row=from_row, column=index).style = self.style
sheet.cell(row=from_row,
column=index).fill = PatternFill('solid',
fgColor="95B3D7")
index += 1
for index in range(len(list_data)):
dict_data = list_data[index]
v_index = 1
row = from_row + index + 1
for k, v in dict_data.items():
if ignore_text and ignore_text == k:
continue
v = str(v)
sheet.cell(row=row, column=v_index, value=v)
sheet.cell(row=row, column=v_index).style = self.style
v_index += 1
def _auto_adjust_width(self, sheet):
# auto adjust the width
for col in sheet.columns:
max_length = 0
column = col[0].column_letter
for cell in col:
try: # Necessary to avoid error on empty cells
if len(str(cell.value)) > max_length:
max_length = len(cell.value)
except Exception:
pass
sheet.column_dimensions[column].width = max_length + 2
def save(self):
try:
self._write_summary()
# self._write_details()
self.wb.save(self.out_put_path)
except Exception as e:
raise e
def export_to_excel(self, bag, export_path=None):
def as_text(value):
if value is None:
return ""
return str(value)
if len(bag) == 0:
return None
try:
# create workbook
wb = Workbook()
# create header style
highlight = NamedStyle(name="highlight")
highlight.font = Font(name='DejaVu Sans', bold=True, size=10)
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
wb.add_named_style(highlight)
# create data style
normal = NamedStyle(name="normal")
normal.font = Font(name='DejaVu Sans', bold=False, size=10)
wb.add_named_style(normal)
# Get worksheet
ws = wb.active
# build header
header = []
for field in HEADER_FIELDS:
header.append(str(HEADER[field]))
ws.append(header)
# Set data
data = []
for sid in bag:
row = []
metadata = self.srvdtb.get_sapnote_metadata(sid)
for field in HEADER_FIELDS:
if field == 'collections':
cols = ', '.join([
self.srvclt.get_name_by_cid(col)
for col in metadata[field]
])
row.append(cols)
elif field == 'releasedon':
excel_date = self.srvutl.get_excel_date(
metadata[field])
row.append(excel_date)
else:
row.append(str(metadata[field]))
ws.append(row)
data.append(row)
# assign style to header
for col in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']:
cell = col + '1'
ws[cell].style = 'highlight'
# assign style to data
for col in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']:
for i in range(len(bag)):
cell = col + '%d' % (i + 2)
ws[cell].style = 'normal'
# adjust columns width
try:
for column_cells in ws.columns:
length = max(
len(as_text(cell.value)) for cell in column_cells)
ws.column_dimensions[COL[
column_cells[0].column]].width = length
except Exception as error:
self.log.error(error)
self.log.error(self.get_traceback())
self.log.error(
"This piece of code isn't working on Windows...")
# header autofilter and sorting
ws.auto_filter.ref = "A1:J1"
ws.auto_filter.add_sort_condition("I2:I%d" % len(bag))
# save to export path
wb.save(export_path)
return True
except Exception as error:
self.log.error(error)
self.log.error(self.get_traceback())
return False
def make_excel(data_out: pd.DataFrame) -> None:
wb = Workbook()
wb.add_named_style(headers_style)
wb.add_named_style(base_style)
wb.add_named_style(text_wrap_style)
ws = wb.active
ws.auto_filter.ref = 'A1:M1'
# Add data
for r in dataframe_to_rows(data_out, index=False, header=True):
ws.append(r)
# Set Base style
for columns in ws['A:M']:
for cell in columns[1:]:
cell.style = base_style
# Set style for Headers
cells = ws['A1:M1']
for cell in cells[0]:
cell.style = headers_style
# Set style for Hyperlink
for cell in ws['B:B'][1:]:
cell.hyperlink = cell.value
cell.style = 'Hyperlink'
for column in ['D', 'E', 'J']:
for cell in ws[f'{column}:{column}'][1:]:
cell.style = text_wrap_style
# Set width for columns
columns = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M']
for column in columns:
ws.column_dimensions[column].width = 13
for row in range(len(ws['A'])):
ws.row_dimensions[row].height = 25.5
start = 2
list_to_merge = []
for i in data_out['Дата добавления'].value_counts().sort_index(
ascending=False).values:
list_to_merge.append([start, start + i - 1])
start += i
flag = 2
for i, j in list_to_merge:
ws.merge_cells(f'A{i}:A{j}')
if flag % 2 == 0:
ws[f'A{i}'].fill = headers_style.fill
ws[f'A{i}'].font = headers_style.font
ws[f'A{i}'].alignment = headers_style.alignment
flag += 1
ws.conditional_formatting.add(
f'M{i}:M{j}',
ColorScaleRule(start_type='max',
end_type='min',
start_color='CAFFBF',
end_color='FFD6A5'))
wb.save(BASE_PATH.joinpath('report.xlsx'))
starter_throws.append(result[0])
########################
# Zip up all the lists #
########################
iter_data = list(
zip(opp_list, opp_names, starters_id, starters_names, date_list,
starter_throws))
#########################
# Excel Workbook Stuff #
#########################
wb = Workbook()
# Worksheet Styles
wb.add_named_style(tableBody)
wb.add_named_style(tableHeader)
wb.add_named_style(sheet_header)
############################
# Create tabs for each day #
############################
for i in range(0, len(iter_data)):
opp_team_id = iter_data[i][0]
opp_team_name = iter_data[i][1]
starter_id = iter_data[i][2]
starter_name = iter_data[i][3]
date = iter_data[i][4]
throws = iter_data[i][5]
def process_boards(self, boards):
"""Creates a series of worksheets in a workbook based on Trello board names."""
highlight = NamedStyle(name='highlight')
highlight.font = Font(bold=True, size=20)
tabletop = NamedStyle(name='tabletop')
tabletop.font = Font(bold=True, color='FFFFFF')
bd = Side(style='thin', color="000000", border_style='thin')
tabletop.border = Border(left=bd, right=bd, top=bd, bottom=bd)
tabletop.fill = PatternFill("solid", bgColor="333333")
wb = Workbook()
wb.add_named_style(highlight)
lookup = trello.Trello()
current_row = 6
# Dump the default worksheet from the document.
# TODO: (PS) Is there a better way to handle this?
for sheet in wb:
if "Sheet" in sheet.title:
wb.remove_sheet(sheet)
for board in boards:
if "Projects" in board['name']:
ws = wb.create_sheet(title="{0}".format(board['name']),
index=0)
ws.sheet_properties.tabColor = "0000FF"
elif "Break Fix" in board['name']:
ws = wb.create_sheet(title="{0}".format(board['name']),
index=1)
ws.sheet_properties.tabColor = "FF0000"
elif "Change Control" in board['name']:
ws = wb.create_sheet(title="{0}".format(board['name']),
index=2)
ws.sheet_properties.tabColor = "228B22"
else:
ws = wb.create_sheet(title="{0}".format(board['name'][0:30]),
index=None)
ws['A1'].style = 'highlight'
ws['A1'] = "{0}".format(board['name'])
ws['A2'] = "" # was going to contain board descriptions. Trello have deprecated these, just not from the API
ws['A3'] = "{0}".format(board['url'])
ws['A3'].style = 'Hyperlink'
ws['A4'] = ""
headings = [
"Name", "Description", "Status", "Due Date", "Complete",
"Perc", "Members"
]
ws.append(headings)
header_row = ws[5]
for cell in header_row:
cell.style = tabletop
cards = lookup.cards(board['shortLink'])
# Apply some default column widths to each worksheet
ws.column_dimensions["A"].width = 40
ws.column_dimensions["B"].width = 100
ws.column_dimensions["C"].width = 10
ws.column_dimensions["D"].width = 22
ws.column_dimensions["G"].width = 45
for card in cards:
# TODO: Pretty slow to iterate like this. Improve.
listname = lookup.lists(card['idList'])
member_list = ""
for member in card['members']:
member_list += "{0},".format(member['fullName'])
member_list.replace(',', ', ')
ws["A{0}".format(current_row)] = card['name']
ws["A{0}".format(current_row)].style = 'Output'
ws["B{0}".format(current_row)] = card['desc']
ws["C{0}".format(current_row)] = listname['name']
if 'Conceptual' in listname['name']:
ws["C{0}".format(current_row)].style = 'Accent5'
elif 'Backlog' in listname['name']:
ws["C{0}".format(current_row)].style = 'Accent4'
elif 'In Progress' in listname['name']:
ws["C{0}".format(current_row)].style = 'Accent1'
elif 'Impeded' in listname['name']:
ws["C{0}".format(current_row)].style = 'Bad'
elif 'Completed' in listname['name']:
ws["C{0}".format(current_row)].style = 'Good'
elif 'Stopped' in listname['name']:
ws["C{0}".format(current_row)].style = 'Accent2'
elif 'Planned' in listname['name']:
ws["C{0}".format(current_row)].style = 'Accent4'
elif 'Successful' in listname['name']:
ws["C{0}".format(current_row)].style = 'Good'
elif 'Failed' in listname['name']:
ws["C{0}".format(current_row)].style = 'Bad'
elif 'Cancelled' in listname['name']:
ws["C{0}".format(current_row)].style = 'Neutral'
elif 'Course Development' in listname['name']:
ws["C{0}".format(current_row)].style = 'Neutral'
elif 'On Training' in listname['name']:
ws["C{0}".format(current_row)].style = 'Accent5'
elif 'Once Off Scheduled' in listname['name']:
ws["C{0}".format(current_row)].style = 'Accent5'
else:
ws["C{0}".format(current_row)] = listname['name']
ws["D{0}".format(current_row)] = card['due']
ws["E{0}".format(current_row)] = card['dueComplete']
# ws["F{0}".format(current_row)] = card['closed']
tasks = 0
complete = 0
checklists = lookup.checklists(card['shortLink'])
for checklist in checklists:
for cl in checklist['checkItems']:
tasks += 1
if cl['state'] == 'complete':
complete += 1
if tasks > 0:
perc = 100 * complete / tasks
else:
perc = 0
ws["F{0}".format(current_row)] = "{0}%".format(int(perc))
if perc < 25:
ws["F{0}".format(current_row)].style = 'Bad'
elif perc < 100:
ws["F{0}".format(current_row)].style = 'Neutral'
else:
ws["F{0}".format(current_row)].style = 'Good'
ws["G{0}".format(current_row)] = member_list[:-1]
current_row += 1
current_row = 6
wb.save(self.filename)
class TestReport(object):
def __init__(self, datetimenow=datetime.datetime.now()):
dirname = "Logs/%s" % datetimenow.strftime("%Y-%m-%d_%H_%M_%S")
if not os.path.exists(dirname):
os.mkdir(dirname)
self.wbname = "%s/Report.xlsx" % dirname
self.wb = Workbook()
ft = Font(name=u'Courier New',
size=16,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
fill = PatternFill(fill_type="solid",
start_color='FF88FFFF',
end_color='FF008800')
cellfill = PatternFill(fill_type="solid",
start_color='FFFFFFFF',
end_color='FF000000')
bd = Border(left=Side(border_style="thin",
color='FF001000'),
right=Side(border_style="thin",
color='FF110000'),
top=Side(border_style="thin",
color='FF110000'),
bottom=Side(border_style="thin",
color='FF110000'),
diagonal=Side(border_style=None,
color='FF000000'),
diagonal_direction=0,
outline=Side(border_style=None,
color='FF000000'),
vertical=Side(border_style=None,
color='FF000000'),
horizontal=Side(border_style=None,
color='FF110000')
)
alignment = Alignment(horizontal='general',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
headerstyle = NamedStyle(name="header")
headerstyle.font = ft
headerstyle.fill = fill
headerstyle.border = bd
headerstyle.alignment = alignment
self.wb.add_named_style(headerstyle)
cellstyle = NamedStyle(name="cell")
cellstyle.font = ft
cellstyle.fill = cellfill
cellstyle.border = bd
cellstyle.alignment = alignment
self.wb.add_named_style(cellstyle)
self.current_row = 0
self.chart_row = 0
self.linkbd = bd
pass
def save(self):
self.wb.remove(self.wb.active)
self.wb.save(self.wbname)
def add_sheet(self, sheetname, headerlist):
ws = self.wb.create_sheet(sheetname)
ws.title = sheetname
self.current_row = 1
self.chart_row = 0
ws.append(headerlist)
for col in range(1, len(headerlist) + 1):
ws.cell(self.current_row, col).style = "header"
for col in ws.columns:
column = col[0].column
ws.column_dimensions[column].width = 28
self.current_row = self.current_row + 1
def add_reccord(self, sheetname, valuelist, scrshot=None):
ws = self.wb[sheetname]
ws.append(valuelist)
for col in range(1, len(valuelist) + 1):
ws.cell(self.current_row, col).style = "cell"
if scrshot is not None:
link = worksheet.hyperlink.Hyperlink(ref="", target="./%s" % scrshot)
ws.cell(self.current_row, col + 1).value = "screenshot"
ws.cell(self.current_row, col + 1).hyperlink = link
ws.cell(self.current_row, col + 1).style = "Hyperlink"
ws.cell(self.current_row, col + 1).border = self.linkbd
self.current_row = self.current_row + 1
def add_chart(self, sheetname, x_col, y_col):
ws = self.wb[sheetname]
xtitle = ws.cell(1, x_col).value
ytitle = ws.cell(1, y_col).value
chart = LineChart()
chart.title = "%s - %s" % (ytitle, xtitle)
chart.style = 13
chart.x_axis.title = xtitle
chart.y_axis.title = ytitle
chart.marker = True
chart.smooth = True
y_data = Reference(ws, min_col=y_col, min_row=1, max_col=y_col, max_row=self.current_row)
chart.add_data(y_data, titles_from_data=True)
x_data = Reference(ws, min_col=x_col, min_row=2, max_col=x_col, max_row=self.current_row)
chart.set_categories(x_data)
s0 = chart.series[0]
s0.marker.symbol = "circle"
s0.smooth = True
if 0 == self.chart_row:
self.chart_row = self.current_row
ws.add_chart(chart, "A%d" % self.chart_row)
# row height is 7.5/16 cm by default
self.chart_row = self.chart_row + chart.height / 7.5 * 16 + 2
class NavXlsxFile:
""" navigation xlsx file """
def __init__(self):
# self.cfg_file = cfg_file
self.work_book = Workbook()
self.current_sheet = self.work_book.active
self.current_sheet.title = 'navLog'
self.cursor = {'row': 1, 'col': 1}
self.sheet_style = NamedStyle(name='sheet_style')
self.sheet_style.alignment = Alignment(horizontal='center',
vertical='center')
bd = Side(style='thin', color="000000")
# self.sheet_style.border = Border(left=bd, top=bd, right=bd, bottom=bd)
self.border = Border(left=bd, top=bd, right=bd, bottom=bd)
self.work_book.add_named_style(self.sheet_style)
def createSheet(self, sheet_name):
work_sheet = self.work_book.create_sheet(sheet_name, 0)
self.selectSheet(sheet_name)
return work_sheet
def selectSheet(self, sheet_name):
self.current_sheet = self.work_book[sheet_name]
return self.current_sheet
def setCursor(self, row, col):
if row >= 1 and col >= 1:
self.cursor['row'] = row
self.cursor['col'] = col
def writeCell(self, row, col, value):
work_sheet = self.current_sheet
work_sheet.cell(row, col, value).style = self.sheet_style
def resize(self, sheet_name):
""" resize sheet unit automotive """
try:
for col in range(self.current_sheet.max_column):
col_width = 0
for row in range(self.current_sheet.max_row):
cell_value = self.current_sheet.cell(row + 1,
col + 1).value
if len(str(cell_value)) > col_width:
col_width = len(str(cell_value))
self.current_sheet.column_dimensions[get_column_letter(col+1)].width\
= float(col_width + 3.0)
except TypeError:
print(self.current_sheet.max_column)
def create(self, file_path):
""" create .xlsx file in file system """
try:
self.work_book.save(file_path)
except:
print('create xlsx file faild!')
sys.exit()
def setCellColor(self, sheet_name, row, col, color):
ws = self.work_book[sheet_name]
cell = ws.cell(row, col)
fill = PatternFill("solid", fgColor=color)
cell.fill = fill
def setCellBorder(self, sheet_name, begin_row, begin_col, end_row,
end_col):
ws = self.work_book[sheet_name]
cells = [(cell_row, cell_col)
for cell_row in range(begin_row, end_row + 1)
for cell_col in range(begin_col, end_col + 1)]
for cur_cell in cells:
ws.cell(cur_cell[0], cur_cell[1]).border = self.border
def mergeCell(self,
sheet_name,
begin_row,
end_row,
begin_col=1,
end_col=3):
ws = self.work_book[sheet_name]
cells = [(cell_row, cell_col)
for cell_col in range(begin_col, end_col + 1)
for cell_row in range(begin_row, end_row + 1)]
for cur_col in range(begin_col, end_col + 1):
cells = [(cell_row, cur_col)
for cell_row in range(begin_row, end_row + 1)]
merge_flag = 0
blank_index = 0
cell_index = 0
while cell_index < len(cells):
cur_cell = cells[cell_index]
if ws.cell(cur_cell[0], cur_cell[1]).value != None:
pass
if merge_flag == 0:
merge_flag = 1
else:
merge_flag = 0
if blank_index > 0:
ws.merge_cells(start_row=cur_cell[0] -
(blank_index + 1),
start_column=cur_cell[1],
end_row=cur_cell[0] - 1,
end_column=cur_cell[1])
cell_index -= 1
blank_index = 0
else:
cell_index -= 1
else:
pass
blank_index += 1
if cell_index == len(cells) - 1:
if merge_flag > 0:
ws.merge_cells(start_row=cur_cell[0] - blank_index,
start_column=cur_cell[1],
end_row=cur_cell[0],
end_column=cur_cell[1])
cell_index += 1
def analyseReport(self):
data = []
wb = Workbook()
ws = wb.active
ws.title = "Polarity"
ws.append(["Category", "Positive Polarity", "Positive", "Negative"])
for k, v in self.dictionary.items():
# print("Key : ", k, "\t Values : " , v)
data.append(self.calculatePolarity(k))
ws.append(self.calculatePolarity(k))
for d in data:
print(d)
# heading style
heading_style = NamedStyle(name="heading_style")
heading_style.font = Font(bold=True, color='FF000000')
heading_style.alignment = Alignment(horizontal='center', vertical='top', wrap_text=True)
heading_style.fill = PatternFill(patternType='solid', start_color='FFF300', end_color='FFF300')
heading_style.border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
wb.add_named_style(heading_style)
# first column style
data_style = NamedStyle(name="data_style")
data_style.font = Font(bold=True, color='FF000000')
data_style.alignment = Alignment(horizontal='left', vertical='top', wrap_text=True)
data_style.fill = PatternFill(patternType='solid', start_color='FFBD4A', end_color='FFBD4A')
data_style.border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
wb.add_named_style(data_style)
# cell style
common_style = NamedStyle(name="common_style")
common_style.alignment = Alignment(horizontal='center', vertical='top', wrap_text=True)
common_style.border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
wb.add_named_style(common_style)
# setting column width
ws.column_dimensions["A"].width = 20
ws.column_dimensions["B"].width = 20
ws.column_dimensions["C"].width = 20
ws.column_dimensions["D"].width = 20
# applying style
i, j = 0, 0
for row in ws.rows:
j = 0
for cell in row:
cell_name = re.findall('\w+\d+', str(cell))[0]
if i == 0:
ws[cell_name].style = heading_style
elif i > 0 and j == 0:
ws[cell_name].style = data_style
else:
ws[cell_name].style = common_style
j += 1
i += 1
newfilename = "Polarity_Analysis_" + self.modelname + ".xlsx"
wb.save(newfilename)
os.startfile(newfilename)
def generate_excel(slots=None,
query_course_list=None,
query_teacher_list=None,
query_section_list=None):
if not slots:
return None
day_list = [
"MONDAY", "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", "SATURDAY"
]
wb = Workbook()
wb.remove(wb['Sheet'])
wb.add_named_style(STYLE_SLOT_NORMAL)
wb.add_named_style(STYLE_SLOT_VENUE)
wb.add_named_style(STYLE_SLOT_TIMESLOT)
for daynum in range(len(day_list)):
current_sheet = wb.create_sheet(day_list[daynum])
# layout
current_sheet.print_options.horizontalCentered = True
current_sheet.print_options.verticalCentered = True
current_sheet.page_setup.orientation = current_sheet.ORIENTATION_LANDSCAPE
current_sheet.page_setup.paperSize = current_sheet.PAPERSIZE_LETTER
current_sheet.page_setup.fitToPage = True
current_sheet.page_setup.fitToWidth = 1
current_sheet.page_setup.fitToHeight = 1
# margins
current_sheet.page_margins.bottom = current_sheet.page_margins.left = current_sheet.page_margins.right = current_sheet.page_margins.header = 0.25
current_sheet.page_margins.top = 0.5
# headers
current_sheet.oddHeader.center.text = "&[Tab]"
current_sheet.oddHeader.center.size = 20
current_sheet.oddHeader.center.font = "Arial"
current_sheet.oddHeader.left.text = "ModularTable"
current_sheet.oddHeader.left.size = 20
current_sheet.oddHeader.left.font = "Arial"
current_sheet.oddHeader.right.text = "Tarun Kumar"
current_sheet.oddHeader.right.size = 20
current_sheet.oddHeader.right.font = "Arial"
current_slots = slots.filter(daynum=daynum + 1)
timeslot_list = current_slots.values(
'timeslot__starttime',
'timeslot__endtime').distinct().order_by('timeslot')
venue_list = current_slots.values(
'venuenum', 'venuenum__venuename').distinct().order_by('venuenum')
for row in current_sheet.iter_rows(min_col=2,
max_col=len(timeslot_list) + 1,
max_row=1):
for i, cell in enumerate(row):
cell.style = 'TIMESLOT'
cell.value = f"{timeslot_list[i]['timeslot__starttime']} - {timeslot_list[i]['timeslot__endtime']}"
for row_count, row in enumerate(
current_sheet.iter_rows(min_col=1,
max_col=len(timeslot_list) + 1,
min_row=2,
max_row=len(venue_list) + 1)):
row[0].style = 'VENUE'
row[0].value = current_slots[
row_count * (len(timeslot_list))].venuenum.venuename
for cell_count, cell in enumerate(row[1:]):
current_slot = current_slots[(row_count *
(len(timeslot_list))) +
cell_count]
cell.style = 'NORMAL'
if not current_slot.teachernum:
continue
if (query_teacher_list is None
or current_slot.teachernum.teachernum
in query_teacher_list) and (
query_course_list is None
or current_slot.coursenum.coursenum
in query_course_list) and (
query_section_list is None
or current_slot.sectionnum.sectionnum
in query_section_list):
cell.value = f"{current_slot.teachernum.teachername}\n{current_slot.sectionnum.semester}{current_slot.sectionnum.section}\n{current_slot.coursenum.coursecode}"
for i in range(2, len(timeslot_list) + 2):
current_sheet.column_dimensions[get_column_letter(i)].width = 24
for i in range(2, len(venue_list) + 2):
current_sheet.row_dimensions[i].height = 60
current_sheet.print_area = f'A1:{get_column_letter(len(timeslot_list)+1)}{len(venue_list)+1}'
return save_virtual_workbook(wb)
# 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(ws2['AA10'].value)
# wb.save(filename = '3sheet.xlsx')
# wb = load_workbook(filename = '3sheet.xlsx')
# sheet_ranges = wb['first']
# print(sheet_ranges['B1'].value)
# #Edit Page Setup
wb=Workbook()
ws=wb.active
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
#Creating a Named Style
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True, size=20)
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
#use st yle
wb.add_named_style(highlight)
ws['A1'].style = highlight
ws['D5'].style = 'highlight'
wb.save(filename = 'stylename1.xlsx')
