def _create_table(sheet, title, style='TableStyleMedium9'):
"""Takes a worksheet assuming there is only one table of data already
present. Creates an 'Excel Table' from the max_column and max_row range
giving it the passed in title.
A default style is also applied and the column widths adjusted.
"""
# Code originally from:
# https://stackoverflow.com/questions/39529662/python-automatically-adjust-width-of-an-excel-files-columns
for col in sheet.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 + 3 if max_length < 10 else max_length
sheet.column_dimensions[column].width = adjusted_width
c = sheet.max_column - 1
r = sheet.max_row - 1
coords = _indices_to_coords(c,r)
table = Table(displayName=title, ref=f"A1:{coords['coord']}")
style = TableStyleInfo(
name=style, showFirstColumn=False, showLastColumn=False,
showRowStripes=True, showColumnStripes=False
)
table.tableStyleInfo = style
sheet.add_table(table)
def create_new_table(tab_range, tab_name):
'''
Create a new Excel table object with given its range and name.
Parameters
----------
Returns
-------
'''
# Create a style for the table
new_tab_style = TableStyleInfo(name="TableStyleLight15",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=False)
# Create a new Excel table object
new_tab_obj = Table(displayName="_".join(tab_name.split()), ref=tab_range)
# Set the created style as the style for the new table
new_tab_obj.tableStyleInfo = new_tab_style
return new_tab_obj
def create_worksheet(excel_workbook, headers, output_dictionary, sheet_name):
"""
"""
sheet = excel_workbook.create_sheet(title=sheet_name)
for i in range(0, len(headers)):
sheet.cell(column=i + 1, row=1, value=headers[i])
row_id = 2
for key in output_dictionary.keys():
for i in range(0, len(headers)):
if isinstance(output_dictionary[key][headers[i]], list):
sheet.cell(column=i + 1,
row=row_id,
value="{0}".format(",".join(
output_dictionary[key][headers[i]])))
else:
sheet.cell(column=i + 1,
row=row_id,
value="{0}".format(
output_dictionary[key][headers[i]]))
row_id = row_id + 1
if len(headers) > 26: ### Super Ugly !! Fix That
table_cells = "A1:A" + chr(64 + len(headers) - 26)
else:
table_cells = "A1:" + chr(64 + len(headers)) + str(row_id)
style = TableStyleInfo(name="TableStyleMedium9", showRowStripes="True")
table = Table(displayName=sheet_name, ref=table_cells)
table.tableStyleInfo = style
sheet.add_table(table)
return excel_workbook
def list2xls(objects, attributes, path, append=False):
"""
把objects的所有attributes指定的属性保存在path指定的xlsx中
:param objects: list 对象列表
:param attributes: list[str] 属性名称列表
:param path: str 保存路径
:param append: bool 是否追加
"""
if append:
wb = load_workbook(path)
ws = wb.active
if ws.title == 'sheet1' and objects:
ws.title = objects[0].__class__.__name__
else:
wb = Workbook()
ws = wb.active
ws.title = objects[0].__class__.__name__ if objects else 'sheet1'
ws.append(attributes)
for o in objects:
ws.append([getattr(o, a, '') for a in attributes])
if objects:
tab = Table(displayName=ws.title,
ref="%c%d:%c%d" %
(ord('A'), 1, ord('@') + ws.max_column, ws.max_row))
style = TableStyleInfo(name='TableStyleMedium9',
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
wb.save(path)
def stylizacja(plik):
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo
lista = [
'Detal',
'Maszyna',
'Ilość maszyn',
'Ilość sztuk na operację',
'Nr op.',
'Nazwa operacji',
'Tj',
'Norma',
'Uwagi'
]
szer = 0.71
szer_lista = [
7.29,
26.57,
13.57,
22.57,
8,
16.14,
7.29,
8.57,
16
]
work = load_workbook(plik)
worksheet = work[work.sheetnames[0]]
worksheet.insert_rows(0)
worksheet.delete_cols(13)
worksheet.delete_cols(9)
worksheet.delete_cols(8)
worksheet.delete_cols(1)
for h in range(9):
worksheet.column_dimensions[get_column_letter(h + 1)].width = \
szer_lista[h] + szer
for i, col in enumerate(lista):
worksheet.cell(row=1, column=i + 1).value = col
# tabela
rozmiar = 'A1:I' + str(len(worksheet['A']))
tab = Table(displayName='Table1', ref=rozmiar)
style = TableStyleInfo(
name='TableStyleMedium1',
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=False
)
tab.tableStyleInfo = style
worksheet.add_table(tab)
work.save(plik)
work.close()
print('Udane')
def excel_data_to_table(excel_output_path, sheet_name, df):
# Convert data in excel file to table format
wb = openpyxl.load_workbook(filename=excel_output_path)
# Create table
tab = Table(displayName=sheet_name,
ref=f'A1:{chr(len(df.columns) + 64)}{len(df) + 1}')
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=False)
tab.tableStyleInfo = style
# Change header font color
# ws = wb[sheet_name]
# header_col = ws.row_dimensions[1]
# header_col.font = Font(color=colors.WHITE)
# Add table to sheet and save workbook
wb[sheet_name].add_table(tab)
wb.save(excel_output_path)
wb.close()
def writeCodesets(ws, codesets):
ws.append([
"CodeSetName", "CodeSetExternalID", "Datatype", "Code", "Value",
"Mneumonic", "Description", "Mapping", "CodeSetSource",
"CodeSetVersion"
])
rows = 1
for codeset in codesets:
#print(codeset)
for codes in codeset['codes']:
elaboration = ''
desc = ''
for text in codes['documentation']:
if (text['purpose'] == "SYNOPSIS"):
desc = text['text']
elif (text['purpose'] == 'ELABORATION'):
elaboration = text['text']
ws.append([
codeset['name'], codeset['id'], codeset['type'],
codes['value'], desc, codes['name'], elaboration, "",
"FIX Trading Community", "FIXLatest"
])
rows += 1
#print (codes)
codetable = Table(displayName="CodeSetTable", ref=f"A1:J{rows}")
style = TableStyleInfo(name="TableStyleMedium2",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=False)
codetable.tableStyleInfo = style
ws.add_table(codetable)
return
def export_xlsx(request):
wb = Workbook()
ws = wb.active
ws.title = 'Sheet'
all_data = _get_daily_data(_is_confirm=True)
ws.append(["铁路局", "车站", "过车辆数", "报警数", "问题及处理", "问题跟踪"])
for row in all_data:
ws.append([
row[9],
row[1],
row[2],
row[3],
row[4],
row[5],
])
_tab_ref = "A1:F%s" % (len(all_data) + 1)
tab = Table(displayName="Table1", ref=_tab_ref)
style = TableStyleInfo(name="TableStyleMedium9",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
_file_name = datetime.datetime.now().strftime(
'%m%d') + '接发车各站运行情况日统计表.xlsx'
wb.save(_file_name)
return file_download(request, _file_name)
def import_data(self):
df = self._process_data()
workbook = load_workbook(self._save_path())
worksheet = workbook.get_sheet_by_name(self.type)
workbook.remove_sheet(worksheet)
# workbook.save(self._save_path())
writer = pd.ExcelWriter(self._save_path(), engine='openpyxl')
writer.book = workbook
writer.sheets = dict((ws.title, ws) for ws in workbook.worksheets)
# workbook.create_sheet(self.type)
df.to_excel(writer, sheet_name=self.type)
worksheet = writer.sheets[self.type]
table = Table(
displayName=self.type,
ref="A1:{co}{ro}".format(
co=base26(df.shape[1] + 1), ro=df.shape[0] +
1)) # Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=True)
table.tableStyleInfo = style
worksheet.add_table(table)
writer.save()
def save_updated_excel_file(updated_list):
'''
Takes original excel sheet and formats it for use with the scraped data,
including adding color to 2nd column to show green "80FF80" if headshot
exists.
'''
thin_border = Border(bottom=Side(style='thin'))
workbook = Workbook()
worksheet = workbook.active
worksheet.column_dimensions["B"].width = 20
worksheet.cell(3, 2).value = "Player Name"
worksheet.cell(3, 3).value = "Headshot"
worksheet.cell(3, 4).value = "Rank"
row = 4
for line in updated_list:
worksheet.cell(row, 2).value = line[0]
worksheet.cell(row, 3).fill = line[1]
worksheet.cell(row, 3).border = thin_border
worksheet.cell(row, 4).value = line[2]
row += 1
tab = Table(displayName="Table1", ref=("B3:D" + str(row-1)))
style = TableStyleInfo(name="TableStyleLight8", showFirstColumn=False,
showLastColumn=False, showRowStripes=False,
showColumnStripes=True)
tab.tableStyleInfo = style
worksheet.add_table(tab)
workbook.save('docs/test_changed.xlsx')
def makeup_xl(file_name):
"""Applies some cosmetic updates to xlsx file
file_name - path to excel file
"""
wb = load_workbook(filename=file_name)
ws = wb.active
# format as a table
tab = Table(
displayName="meTable1",
ref=ws.dimensions) # ws.dimensions - dimesion with data in sheet
style = TableStyleInfo(name="TableStyleLight9",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=False)
tab.tableStyleInfo = style
ws.add_table(tab)
# set columns width in populated range = 30
try:
i = 1
for i in range(1, (ws.max_column + 1)):
ws.column_dimensions[get_column_letter(i)].width = 30
except ValueError:
pass
wb.save(file_name)
return
def add_module_to_excel(workflow_db, module):
wb = openpyxl.load_workbook(filename=workflow_db)
if module in wb.sheetnames:
print('module {} already exists. If this is an error:'.format(module))
print('1. Delete the worksheet named {}'.format(module))
print('2. Remove the workflow entry from the workflows sheet for {}'.format(module))
print('3. Remove the directory ./{} and all of its contents'.format(module))
else:
ws = wb.create_sheet(module)
ws.append(['Stage', 'Status', 'Function', 'Documentation'])
ws.append([1, 'enabled', 'hello_world', 'Prints Hello World!!'])
ws.move_range("A1:D2", rows=1, cols=1)
table_name = 'control?{}?Function'.format(module)
tab = Table(displayName=table_name, ref="B2:E3")
style = TableStyleInfo(name="TableStyleMedium3", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
_workflow_sheet = wb['workflows']
for table in _workflow_sheet._tables:
if table.name == 'workflow':
# print(get_table_length(table))
_workflow_id = get_table_length(table) + 1
row = [_workflow_id, 'enabled', module, 'Update this workflow documentation']
add_row_to_table(_workflow_sheet, table, row)
wb.save(workflow_db)
def build_table(self):
column = self.column
for j in range(1, self.step + 1):
self.ws.column_dimensions[cell.get_column_letter(j)].width = 25
self.ws.column_dimensions['C'].width = self.ws.column_dimensions[
'D'].width = 60
base_cell = self.ws.cell(row=self.cur_row, column=column)
self.ws.merge_cells(start_row=self.cur_row,
end_row=self.cur_row + 2,
start_column=column,
end_column=column + self.step - 1)
base_cell.value = 'Event Report'
base_cell.alignment = self.align_head
base_cell.font = self.font_head
base_cell.fill = self.fill_head
for i, head in enumerate(
['Date', 'Time', *self.changes_all_event_fields]):
self.ws.cell(row=self.cur_row + 3, column=column + i, value=head)
for row in self.all_signal_data:
self.ws.append(row)
table_row_start = self.cur_row + 3
table_column_end = self.step
table_row_end = len(self.all_signal_data) + table_row_start
tab = Table(displayName='Report',
ref="A" + str(table_row_start) + ":" +
cell.get_column_letter(table_column_end) +
str(table_row_end))
tab.tableStyleInfo = self.style
self.ws.add_table(tab)
def create_excel_table_from_df(df: pd.DataFrame, sheet_: Worksheet, row_ini: 1,
table_name):
"""Crea tabla de Excel en la hoja indicada a partir de un pandas DataFrame.
Parametros:
df: pandas DataFrame
row_ini: fila inicial, por default 1
sheet_: Worksheet object openpyxl
table_name: nombre de la tabla"""
col_last = get_excel_style(1, df.shape[1])[:-1]
# Crear tabla de Excel
tabla_excel = Table(
displayName=table_name,
ref=f"A{row_ini}:{col_last}{df.shape[0] + row_ini}") # nombre y tamaño
# declarar estilos a la tabla
style = TableStyleInfo(name="TableStyleMedium2", showRowStripes=False)
# asignar el estilo
tabla_excel.tableStyleInfo = style
# agregar tabla a la hoja
sheet_.add_table(tabla_excel)
def save_updated_excel_file(updated_list):
"""
Take original excel sheet and format it.
For use with the scraped data,
including adding color to 2nd column to show green "80FF80" if headshot
exists.
"""
thin_border = Border(bottom=Side(style='thin'), left=Side(style='thin'))
thick_border = Border(bottom=Side(style='thick'))
workbook = Workbook()
worksheet = workbook.active
worksheet.column_dimensions["B"].width = 20
worksheet.cell(3, 2).value = "RANK"
worksheet.cell(3, 3).value = "Player Name"
worksheet.cell(3, 4).value = "2017"
worksheet.cell(3, 5).value = "2018"
worksheet.cell(3, 6).value = "2019"
worksheet.cell(3, 7).value = "2020"
row = 4
for line in updated_list:
worksheet.cell(row, 2).value = line[0]
worksheet.cell(row, 2).font = Font(bold=True, size=14.0)
worksheet.cell(row, 2).alignment = Alignment(horizontal="center",
shrinkToFit=True)
worksheet.cell(row, 3).value = line[1]
worksheet.cell(row, 3).alignment = Alignment(horizontal="left")
worksheet.cell(row, 4).border = thin_border
worksheet.cell(row, 4).fill = line[2]
worksheet.cell(row, 5).border = thin_border
worksheet.cell(row, 5).fill = line[3]
worksheet.cell(row, 6).border = thin_border
worksheet.cell(row, 6).fill = line[4]
worksheet.cell(row, 7).border = thin_border
worksheet.cell(row, 7).fill = line[5]
row += 1
# column widths
worksheet.column_dimensions["B"].width = 6
worksheet.column_dimensions["C"].width = 20
worksheet.column_dimensions["D"].width = 10
worksheet.column_dimensions["E"].width = 10
worksheet.column_dimensions["F"].width = 10
worksheet.column_dimensions["G"].width = 10
# thick line for the cutoff rank
for i in range(8):
worksheet.cell(67, i+1).border = thick_border
tab = Table(displayName="Table1", ref=("B3:F" + str(row-1)))
style = TableStyleInfo(name="TableStyleLight8", showFirstColumn=False,
showLastColumn=False, showRowStripes=False,
showColumnStripes=False)
tab.tableStyleInfo = style
worksheet.add_table(tab)
workbook.save('OWGR.xlsx')
def setStyle(self, path="", num=0):
tab = Table(displayName="Table1", ref= "A1:K{0}".format( num+1 ) )
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style
self.ws.add_table(tab)
#self.wb.save("C:\\Users\\gogho\\OneDrive\\桌面\\Chrom\\test\\create_sample.xlsx")
self.wb.save( path + ".xlsx" )
def table(queryset, year):
columns = [
_('ref. number'),
_('Company ref. number'),
_('Full name, date of birth'),
_('City'),
_('Total price for the doctor'),
]
if type_of_table == "staff":
columns.append(_('Total price'))
data = []
for report in queryset:
row = [
report.get_full_ref_number,
report.get_full_company_ref_number,
' '.join((str(report.patients_last_name),
str(report.patients_first_name))) + ', ' +
str(report.patients_date_of_birth.strftime("%d.%m.%Y")),
report.city.name,
report.get_total_price_doctor,
]
if type_of_table == "staff":
row.append(report.get_total_price)
data.append(row)
worksheet.append(columns)
worksheet.column_dimensions["A"].width = 20
worksheet.column_dimensions["B"].width = 25
worksheet.column_dimensions["C"].width = 45
worksheet.column_dimensions["D"].width = 25
worksheet.column_dimensions["E"].width = 25
if type_of_table == "staff":
worksheet.column_dimensions["F"].width = 25
table_ref = "A1:F"
else:
table_ref = "A1:E"
for row in data:
worksheet.append(row)
tab = Table(displayName='year_' + str(year),
ref=table_ref + str(len(data) + 1),
totalsRowShown=True)
style = TableStyleInfo(name="TableStyleMedium9",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=False)
tab.tableStyleInfo = style
worksheet.add_table(tab)
def post(self, request, *args, **kwargs):
rids = loads(request.body)['rids']
if rids:
# find all model instances with an id in rids
objects = self.model.objects.filter(id__in=rids)
else:
# return all model instances from namespace
datafilter = {'archived': False}
# parent_pk is part of the batch system and if found will only fetch a subset of the model with Parent=parent_pk
if 'parent_pk' in kwargs.keys():
datafilter['Parent'] = self.kwargs['parent_pk']
objects = self.model.objects.filter(**datafilter)
# turn objects into a workbook
data = model_to_list_list(objects)
wb = Workbook()
ws = wb.active
ws.title = self.model.__name__
ws.append(self.model.order)
for row in data:
ws.append(
[striptags(x) for x in row]
) # We are removing all HTML tags...THIS MAY NOT ALWAYS BE DESIRED
tab = Table(
displayName="Table1",
ref=f"A1:{get_column_letter(len(self.model.order))}{len(data)}")
style = TableStyleInfo(name="TableStyleMedium9",
showFirstColumn=True,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=False)
tab.tableStyleInfo = style
ws.add_table(tab)
# set column width per user settings
up = apps.get_model('GBEX_app',
'Profile').objects.filter(user=request.user)
if up:
json_data = loads(up[0].table_settings)
if self.model.__name__ in json_data:
if 'column_widths' in json_data[self.model.__name__]:
cws = json_data[self.model.__name__]['column_widths']
for i, col_name in enumerate(self.model.order):
if col_name in cws:
ws.column_dimensions[get_column_letter(
i + 1)].width = units.pixels_to_points(
cws[col_name]) / 6
response = HttpResponse(
save_virtual_workbook(wb),
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
)
response['Content-Disposition'] = 'attachment; filename="export.xlsx"'
return response
def add_table_to_worksheet(_work_sheet,
name,
data,
first_row_is_header=True,
table_style='TableStyleMedium2',
row_offset=2,
col_offset=1):
"""
Add a list of dictionaries (rows) as an Excel table. By default the first row is the header.
The dictionaries are list of key:value pairs constituting each row of the table.
Args:
_work_sheet: (object) An openpyxl ws object (this must be changes to ws name only)
name: (string) The name of the table. Must be globally unique within the Excel workbook
data: (list) Table data as a list of dictionaries with each element constituting a row of the table
first_row_is_header: (bool) True (default) or False. Uses the first element of data as table headers
table_style: (sting) An Excel table style
row_offset: (int) Integer to determine number of spacer rows to add
col_offset: (int) Integer to determine number of spacer columns to add
Returns:
Nothing
"""
if first_row_is_header:
column_headers = data.pop(0)
else:
print('first_row_is_header=False is currently not supported')
exit()
_start_table_data = _new_table_setup(_work_sheet,
column_headers,
row_offset=row_offset,
col_offset=col_offset)
new_table_end_col = _start_table_data[0]
new_table_start_row = _start_table_data[1]
last_data_row = _add_table_data(_work_sheet,
column_headers,
data,
col_offset=col_offset)
# Calculate new data refs and insert table
_t_scol = _get_cell_column_letter(1 + col_offset)
_t_ecol = _get_cell_column_letter(new_table_end_col)
_scoord = '{}{}'.format(_t_scol, new_table_start_row)
_ecoord = '{}{}'.format(_t_ecol, last_data_row)
_t_ref = '{}:{}'.format(_scoord, _ecoord)
tab = Table(displayName=name, ref=_t_ref)
style = TableStyleInfo(name=table_style,
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=False)
tab.tableStyleInfo = style
_work_sheet.add_table(tab)
def create_table(ws, display_name, range):
tab = Table(displayName=display_name, ref=range)
tab.headerRowCount = 1
style = TableStyleInfo(name="TableStyleMedium2",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=False,
showColumnStripes=False)
tab.tableStyleInfo = style
ws.add_table(tab)
def tableCreate(tableName, pos): #pos=位置 e.g.A1:B5
tab = Table(displayName="tableName", ref=pos) # ref is the table range
style = TableStyleInfo(
name="TableStyleMedium9",
showFirstColumn=False, # the style of table
showLastColumn=False,
showRowStripes=True,
showColumnStripes=True)
tab.tableStyleInfo = style # apply the style to table
ws.add_table(tab) # add table to word sheet
def entetes_resultats_generaux(usines:list, nom_fichier:str):
styleFond, styleTeteTab, styleVal, styleTableau, styleEntree, styleTitre = p.STYLES_EXCEL
ligne_depart = 10
try:
document = openpyxl.load_workbook(p.CHEMIN_ECRITURE_RESULTATS+nom_fichier)
except FileNotFoundError:
fonctionsMatrices.print_log_erreur("Le document résultat n'est pas trouvé à l'emplacement "+p.CHEMIN_ECRITURE_RESULTATS+nom_fichier, inspect.stack()[0][3])
sys.exit("Le document résultat n'est pas trouvé à l'emplacement "+ p.CHEMIN_ECRITURE_RESULTATS+nom_fichier)
feuilleprinc = document.get_sheet_by_name(document.get_sheet_names()[0])
feuilleprinc.column_dimensions['B'].width = 24
feuilleprinc.column_dimensions['C'].width = 0
feuilleprinc.column_dimensions['D'].width = 20
feuilleprinc.column_dimensions['E'].width = 10
FretAmont = ["MP bateau", "MP route"]
FretAval = ["Ventes pro","Ventes Jardineries", "Interdepot"]
Intrants = ["Matières Premières", "CO2 issu de la tourbe"]
HorsEnergie = ["Protoxyde d'azote"]
Energie = ["Electricité", "Fuel"]
Emballages = ["PE neuf", "PE recyclé", "PVC", "Carton"]
entetes = FretAmont+FretAval+Intrants+HorsEnergie+Energie+Emballages
colonnes = ["Poste d'émission", "Site", "Empreinte carbone (tCO2e)","", "Emission par m3 (kgCO2e/m3)", ""]
for i in range(len(colonnes)):
feuilleprinc.cell(row = ligne_depart, column = i+2).value = colonnes[i]
feuilleprinc.cell(row=ligne_depart, column=i+2).style = styleTeteTab
i = 0
for poste in entetes:
for j in range(len(usines)):
feuilleprinc["B"+str(i+ligne_depart+1)] = poste
feuilleprinc["C"+str(i+ligne_depart+1)] = str(usines[j][0]) + " " + usines[j][1]
i+=1
alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
tableau = Table(displayName = "Resultats_principaux", ref = "B"+str(ligne_depart)+":"+alphabet[len(entetes)]+str(ligne_depart+i))
tableau.tableStyleInfo = styleTableau
production_totale = sum([x[1]for x in p.PROD_PAR_SITE])
feuilleprinc['C7'] = "Total:"
feuilleprinc['D7'] = "=TEXT(SUM(D11:D"+str(199)+"), \"# ##0\")&+\" tCO2e\""
feuilleprinc['E7'] = "et"
feuilleprinc['F7'] = "=TEXT(1000*SUM(D11:D"+str(199)+")/"+str(production_totale)+", \"# ##0,0\")&+\" kgCO2e/m3\""
# rouge = openpyxl.styles.colors.Color(rgb='00FF0000')
for c in "CDEF":
feuilleprinc[c+"7"].font = Font(size=18, bold=True)
#Si on a la possibilité, on sauvegarde
try:
document.save(p.CHEMIN_ECRITURE_RESULTATS+nom_fichier)
except PermissionError: #Soit à cause du fichier réseau, soit parce que l'excel est ouvert ailleurs
fonctionsMatrices.print_log_erreur("Permissions insuffisantes pour enregistrer le fichier résultat", inspect.stack()[0][3])
except FileNotFoundError: #Emplacement inexistant
fonctionsMatrices.print_log_erreur("Emplacement d'écriture introuvable: "+str(p.CHEMIN_ECRITURE_RESULTATS + nom_fichier), inspect.stack()[0][3])
def final_report(self):
'''
This Class will Take the data from the other classes and create a Final Sheet to be used for the Reports.
It will Write the Data needed in the current iteration of the Reports and append it to the Sheet.
Then it will make the Data appear as a Table in the very last Excel Sheet.
'''
ws = self.wb_wt.create_sheet("DTO Report", 4)
ws = self.wb_wt.worksheets[4]
wt4 = self.wb_sap.worksheets[4]
for rows in wt4.iter_rows(
): # This Will Append the Previous Data from any Other Reports
ws.append([cell.value for cell in rows])
refs = ws.max_row # This Establishes the Length of that Data
# Deletes the Summations from Prev. Reports
ws.delete_rows(refs, refs + 1)
currentDT = datetime.datetime.now() # Current Data
# Variable Data is the data that get written to the Report
data = [[
currentDT.strftime("%m/%d/%Y"),
(self.dto["F9"] + self.dto["F9A"] + self.dto["F9B"] +
self.dto["F9X"]), self.dto["F11A"],
(self.dto["Insourced"] + self.dto["Tooling"] + self.dto["RCI"]),
self.dto["7RMY20"], self.dto["LYNX"], self.dto["None"],
(self.dto["Legacy"] + self.dto["Pre-IT4"] + self.dto["Multiple"] +
self.dto["Aeros"]), self.total_dto, self.NP, self.P,
self.me_approval, self.x_issued, self.t_issued, self.XT,
(self.dto["Maximus"] + self.dto["Saturn"] + self.dto["Isis"]), 0,
0, 0, self.costhr
]]
for row in data: # Appends the Data Row to the Report
ws.append(row)
tablelength = ws.max_row # Determines the Length of the Table w/ the Data now
# Creates Dimensions for the Table based on that Length
tableref = "A1:U" + str(tablelength)
# Creates the Table with a Display Name and the ref. Ref is the prev. Line
tab = Table(displayName="DTOTable", ref=tableref)
style = TableStyleInfo(name="TableStyleDark2",
showFirstColumn=True,
showLastColumn=True,
showRowStripes=True,
showColumnStripes=True)
# Sets Table Style based on the Table Style information parsed in the two lines above.
tab.tableStyleInfo = style
ws.add_table(tab) # Finally Enables the Table to appear
# Theese Lines Write the Summations for the Xiss, Tiss, X->T
ws['M' + str(tablelength + 1)] = "=SUM(M2:M" + str(tablelength) + ")"
ws['N' + str(tablelength + 1)] = "=SUM(N2:N" + str(tablelength) + ")"
ws['O' + str(tablelength + 1)] = "=SUM(O2:O" + str(tablelength) + ")"
# Style of Page Below
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
i = 0
cols = ws.max_column
def write_xlsx(header, file_name, query, conn_str):
'''
Writes the results of the query to an .xlsx file,
the contents is stored in a table, and has the columns width
expanded for ease of use and makes it easier to read.
'''
print file_name
dest_filename = file_name.replace(
'.sql', '_' + time.strftime('%m-%d-%y_%H%M') + '.xlsx')
wb = Workbook()
ws = wb.active
conn = pyodbc.connect(conn_str)
result = conn.execute(query).fetchall()
if result:
# add column header to top of excel list
ws.append(header.split(','))
# generic list, used to write contents pulled from database to the excel file
lst = []
# loop through contents pulled from database and write to excel list
for row in result:
convert_str = []
lst = list(row)
for item in lst:
new_item = str(item)
new_item = new_item.replace(
'\x1f', '') # \x1f breaks writing xlsx files with tables
new_item = new_item.replace('None', '')
convert_str.append(new_item)
ws.append(convert_str)
# Expand all Columns to match Larges Text in column
for column_cells in ws.columns:
length = max(len(str(cell.value)) for cell in column_cells) * 1.3
ws.column_dimensions[column_cells[0].column].width = length
# Add table formatting to the Excel List, Only work to letter Z
# breaks if query is larger than 26 columns
end_column = NUM_TO_ALPHA[len(list(ws.rows)[0])] + str(
len(list(ws.rows)))
tab = Table(displayName='Table', ref='A1:' + end_column)
style = TableStyleInfo(name="TableStyleLight11",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
# write the file
wb.save(dest_filename)
wb.close()
return dest_filename
def xexport_dynamic_data(dict_data):
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
wb = Workbook()
ws = wb.active
organization = Organization.get(id=1)
file_name = "{}.xlsx".format(dict_data.get("file_name"))
headers = dict_data.get("headers")
sheet_name = str(dict_data.get("sheet"))
title = str(dict_data.get("title"))
data = dict_data.get("data")
widths = dict_data.get("widths")
date_ = str(dict_data.get("date"))
extend_rows = dict_data.get("extend_rows")
others = dict_data.get("others")
footers = dict_data.get("footers")
exclude_row = dict_data.get("exclude_row")
format_money = dict_data.get("format_money")
# add column headings. NB. these must be strings
ws.append(headers)
for row in data:
print(row)
ws.append(row)
dict_alph = {
1: "A",
2: "C",
3: "D",
4: "E",
5: "F",
6: "G",
7: "H",
8: "I",
}
REF = "A1:{}{}".format(dict_alph.get(len(headers)), len(data) + 1)
print(REF)
tab = Table(displayName="Table1", ref=REF)
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
wb.save(file_name)
try:
wb.close()
openFile(file_name)
except Exception as e:
print(e)
def saveReports():
tab = Table(displayName="Table1", ref="A1:C503")
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
wb.save(logname + ".xlsx")
def addtable(df, ws):
nrow, ncol = df.shape
title= ws.title
print(title)
# get_column_letter(27)
tab = Table(displayName= title, ref='A1:{col}{row}'.format(col= get_column_letter(ncol), row= nrow + 1))
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleLight8")
tab.tableStyleInfo = style
ws.add_table(tab)
return ws
def final_report(self):
'''
This Class will Take the data from the other classes and create a Final Sheet to be used for the Reports.
It will Write the Data needed in the current iteration of the Reports and append it to the Sheet.
Then it will make the Data appear as a Table in the very last Excel Sheet.
'''
ws = self.wb_wt.create_sheet("Final Report", 4)
ws = self.wb_wt.worksheets[4]
wt4 = self.wb_sap.worksheets[4]
for rows in wt4.iter_rows(
): #This Will Append the Previous Data from any Other Reports
ws.append([cell.value for cell in rows])
refs = ws.max_row #This Establishes the Length of that Data
ws.delete_rows(refs,
refs + 1) #Deletes the Summations from Prev. Reports
currentDT = datetime.datetime.now() #Current Data
#Variable Data is the data that get written to the Report
data = [[
currentDT.strftime("%Y/%m/%d"), self.fnine, self.feleven,
(self.ins + self.tooling + self.rci), self.sevenr, self.lynx,
self.new,
(self.pre + self.saturn + self.maxim + self.legacy + self.aeros +
self.isis), self.total, self.NP, self.P, self.me_approval,
self.x_issued, self.t_issued, self.XT, 0, 0, 0, 0, 0, self.costhr
]]
for row in data: #Appends the Data Row to the Report
ws.append(row)
tablelength = ws.max_row #Determines the Length of the Table w/ the Data now
tableref = "A1:U" + str(
tablelength
) #Creates Dimensions for the Table based on that Length
tab = Table(
displayName="Data", ref=tableref
) #Creates the Table with a Display Name and the ref. Ref is the prev. Line
style = TableStyleInfo(name="TableStyleDark1",
showFirstColumn=True,
showLastColumn=True,
showRowStripes=True,
showColumnStripes=True)
tab.tableStyleInfo = style #Sets Table Style based on the Table Style information parsed in the two lines above.
ws.add_table(tab) #Finally Enables the Table to appear
#Theese Lines Write the Summations for the Xiss, Tiss, X->T
ws['M' + str(tablelength + 1)] = "=SUM(M2:M" + str(tablelength) + ")"
ws['N' + str(tablelength + 1)] = "=SUM(N2:N" + str(tablelength) + ")"
ws['O' + str(tablelength + 1)] = "=SUM(O2:O" + str(tablelength) + ")"
#Style of Page Below
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
i = 0
cols = ws.max_column
for i in range(cols + 1):
ws[chr(65 + i) + "1"].alignment = Alignment(wrap_text=True)
def build_table(self, index):
column = self.column
for j in range(1, self.step + 1):
self.ws.column_dimensions[cell.get_column_letter(j)].width = 25
base_cell = self.ws.cell(row=self.cur_row, column=column)
self.ws.merge_cells(start_row=self.cur_row,
end_row=self.cur_row + 2,
start_column=column,
end_column=column + self.step - 1)
base_cell.value = self.object_fullpaths[index]
base_cell.alignment = self.align_head
if index % 2:
base_cell.font = self.alt_font_head
base_cell.fill = self.alt_fill_head
else:
base_cell.font = self.font_head
base_cell.fill = self.fill_head
fields = {
6: self.changes_mv_fields,
7: self.consumption_mt_fields,
9: self.average_mv_fields
}.get(self.step)
for i, head in enumerate(['Date', 'Time', *fields]):
self.ws.cell(row=self.cur_row + 3, column=column + i, value=head)
for row in self.all_signal_data[index]:
self.ws.append(row)
table_row_start = self.cur_row + 3
table_column_end = self.step
table_row_end = len(self.all_signal_data[index]) + table_row_start
tab = Table(displayName='Report' + str(index),
ref="A" + str(table_row_start) + ":" +
cell.get_column_letter(table_column_end) +
str(table_row_end))
tab.tableStyleInfo = self.style
self.ws.add_table(tab)
if self.signal_type in ['All Measurements', 'All Metering']:
if self.data_type in ['Average', 'Consumption']:
self.build_chart(min_col=6,
max_col=7,
min_row=table_row_start,
max_row=table_row_end)
else:
self.build_chart(min_col=self.step,
min_row=table_row_start,
max_row=table_row_end)
if self.template_path:
self.ws = self.wb.copy_worksheet(self.ws_template)
self.ws.title = 'Report-' + str(self.sheet_index)
self.sheet_index += 1
else:
self.ws = self.wb.create_sheet()
self.cur_row = self.ws.max_row + 2
def get_list():
# http://openpyxl.readthedocs.io/en/default/pandas.html#
path = 'data/sample.txt'
df = pd.read_csv(path, sep='\t')
df['注文日'] = pd.to_datetime(df['注文日'])
wb = Workbook()
ws = wb.get_active_sheet()
ws.append([None, None, None, '商品受注リスト'])
ws.append([
None, None, None, None, None, None,
'作成日: ' + datetime.date.today().strftime('%Y-%m-%d')
])
for r in dataframe_to_rows(df, index=False, header=True):
ws.append(r)
begin_row = 3
end_row = len(df.index) + begin_row
ws['D1'].font = Font(size=20)
ws['D1'].alignment = Alignment(horizontal='center')
ws['G2'].alignment = Alignment(horizontal='right')
for cell in ws['A:A']:
cell.number_format = 'YYYY/MM/DD'
for cell in ws['G:G']:
cell.number_format = '#,##0'
ws.column_dimensions['A'].width = 12
ws.column_dimensions['B'].width = 12
ws.column_dimensions['C'].width = 12
ws.column_dimensions['D'].width = 12
ws.column_dimensions['E'].width = 12
ws.column_dimensions['F'].width = 12
ws.column_dimensions['G'].width = 12
# http://openpyxl.readthedocs.io/en/default/worksheet_tables.html
tab = Table(displayName="Table1",
ref="A" + str(begin_row) + ":G" + str(end_row))
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleLight15",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=False)
tab.tableStyleInfo = style
ws.add_table(tab)
wb.save('data/一覧.xlsx')
def output_xlsx_file(parsed_rules, errors, audit_type, audit, output_file):
audit_bool = audit_type[0] or audit_type[1] or audit_type[2]
wb = Workbook()
ws1 = wb.active
ws1.title = 'Parsed'
ws2 = wb.create_sheet('Errors')
ws1.append(generate_headers('rules'))
ws2.append(generate_headers('errors'))
if audit_bool:
ws3 = wb.create_sheet('Audit')
ws3.append(generate_headers('audit'))
parsed_count = 0
err_count = 0
audit_count = 0
for acl in parsed_rules:
for entry in acl:
ws1.append(entry)
parsed_count += 1
for acl_err in errors:
for entry_err in acl_err:
ws2.append([entry_err])
err_count += 1
if audit_bool:
for entry_audit in audit:
ws3.append(entry_audit)
audit_count += 1
style = TableStyleInfo(name="TableStyleMedium9",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=False)
parsed_range = "A1:R" + str(parsed_count + 1)
errors_range = "A1:A" + str(err_count + 1)
if audit_bool:
audit_range = "A1:D" + str(audit_count + 1)
parsed_tab = Table(displayName='Parsed_Rules',
ref=parsed_range,
tableStyleInfo=style)
errors_tab = Table(displayName='Parsing_Errors',
ref=errors_range,
tableStyleInfo=style)
if audit_bool:
audit_tab = Table(displayName='Auditing_Results',
ref=audit_range,
tableStyleInfo=style)
ws1.add_table(parsed_tab)
ws2.add_table(errors_tab)
if audit_bool:
ws3.add_table(audit_tab)
wb.save(output_file)
"""
创建Excel文件
Version: 0.1
Author: 骆昊
Date: 2018-03-26
"""
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
workbook = Workbook()
sheet = workbook.active
data = [
[1001, '白元芳', '男', '13123456789'],
[1002, '白洁', '女', '13233445566']
]
sheet.append(['学号', '姓名', '性别', '电话'])
for row in data:
sheet.append(row)
tab = Table(displayName="Table1", ref="A1:E5")
tab.tableStyleInfo = TableStyleInfo(
name="TableStyleMedium9", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=True)
sheet.add_table(tab)
workbook.save('./res/全班学生数据.xlsx')
def load_workbook(filename, read_only=False, keep_vba=KEEP_VBA,
data_only=False, guess_types=False, keep_links=True):
"""Open the given filename and return the workbook
:param filename: the path to open or a file-like object
:type filename: string or a file-like object open in binary mode c.f., :class:`zipfile.ZipFile`
:param read_only: optimised for reading, content cannot be edited
:type read_only: bool
:param keep_vba: preseve vba content (this does NOT mean you can use it)
:type keep_vba: bool
:param guess_types: guess cell content type and do not read it from the file
:type guess_types: bool
:param data_only: controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet
:type data_only: bool
:param keep_links: whether links to external workbooks should be preserved. The default is True
:type keep_links: bool
:rtype: :class:`openpyxl.workbook.Workbook`
.. note::
When using lazy load, all worksheets will be :class:`openpyxl.worksheet.iter_worksheet.IterableWorksheet`
and the returned workbook will be read-only.
"""
archive = _validate_archive(filename)
src = archive.read(ARC_CONTENT_TYPES)
root = fromstring(src)
package = Manifest.from_tree(root)
wb_part = _find_workbook_part(package)
parser = WorkbookParser(archive, wb_part.PartName[1:])
wb = parser.wb
wb._data_only = data_only
wb._read_only = read_only
wb._keep_links = keep_links
wb.guess_types = guess_types
wb.template = wb_part.ContentType in (XLTX, XLTM)
parser.parse()
wb._sheets = []
if read_only and guess_types:
warnings.warn('Data types are not guessed when using iterator reader')
valid_files = archive.namelist()
# If are going to preserve the vba then attach a copy of the archive to the
# workbook so that is available for the save.
if keep_vba:
wb.vba_archive = ZipFile(BytesIO(), 'a', ZIP_DEFLATED)
for name in archive.namelist():
wb.vba_archive.writestr(name, archive.read(name))
if read_only:
wb._archive = ZipFile(filename)
# get workbook-level information
if ARC_CORE in valid_files:
src = fromstring(archive.read(ARC_CORE))
wb.properties = DocumentProperties.from_tree(src)
shared_strings = []
ct = package.find(SHARED_STRINGS)
if ct is not None:
strings_path = ct.PartName[1:]
shared_strings = read_string_table(archive.read(strings_path))
if ARC_THEME in valid_files:
wb.loaded_theme = archive.read(ARC_THEME)
apply_stylesheet(archive, wb) # bind styles to workbook
pivot_caches = parser.pivot_caches
# get worksheets
for sheet, rel in parser.find_sheets():
if "chartsheet" in rel.Type:
continue
sheet_name = sheet.name
worksheet_path = rel.target
rels_path = get_rels_path(worksheet_path)
rels = []
if rels_path in valid_files:
rels = get_dependents(archive, rels_path)
if not worksheet_path in valid_files:
continue
if read_only:
ws = ReadOnlyWorksheet(wb, sheet_name, worksheet_path, None,
shared_strings)
wb._sheets.append(ws)
else:
fh = archive.open(worksheet_path)
ws = wb.create_sheet(sheet_name)
ws._rels = rels
ws_parser = WorkSheetParser(ws, fh, shared_strings)
ws_parser.parse()
if rels:
# assign any comments to cells
for r in rels.find(COMMENTS_NS):
src = archive.read(r.target)
comment_sheet = CommentSheet.from_tree(fromstring(src))
for ref, comment in comment_sheet.comments:
ws[ref].comment = comment
# preserve link to VML file if VBA
if (
wb.vba_archive is not None
and ws.legacy_drawing is not None
):
ws.legacy_drawing = rels[ws.legacy_drawing].target
for t in ws_parser.tables:
src = archive.read(t)
xml = fromstring(src)
table = Table.from_tree(xml)
ws.add_table(table)
drawings = rels.find(SpreadsheetDrawing._rel_type)
for rel in drawings:
for c in find_charts(archive, rel.target):
ws.add_chart(c, c.anchor)
for im in find_images(archive, rel.target):
ws.add_image(im, im.anchor)
pivot_rel = rels.find(TableDefinition.rel_type)
for r in pivot_rel:
pivot_path = r.Target
src = archive.read(pivot_path)
tree = fromstring(src)
pivot = TableDefinition.from_tree(tree)
pivot.cache = pivot_caches[pivot.cacheId]
ws.add_pivot(pivot)
ws.sheet_state = sheet.state
ws._rels = [] # reset
parser.assign_names()
#wb._differential_styles.styles = [] # tables may depened upon dxf
archive.close()
return wb
wb = Workbook()
ws = wb.active
data = [
['Apples', 10000, 5000, 8000, 6000],
['Pears', 2000, 3000, 4000, 5000],
['Bananas', 6000, 6000, 6500, 6000],
['Oranges', 500, 300, 200, 700],
]
# add column headings. NB. these must be strings
ws.append(["Fruit", "2011", "2012", "2013", "2014"])
for row in data:
ws.append(row)
tab = Table(displayName="Table1", ref="A1:E5")
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
# wb.save("table.xlsx")
# #Accessing a range called “my_range”:
# my_range = wb.defined_names['my_range']
# # if this contains a range of cells then the destinations attribute is not None
# dests = my_range.destinations # returns a generator of (worksheet title, cell range) tuples
# cells = []
# for title, coord in dests:
