def make_excel(self):
def set_style(the_ws):
ws_style = Style(size=15, alignment=Alignment(horizontal="center", vertical="center"))
the_ws.range("A1", "E1").merge()
for i in range(1, 5):
the_ws.set_col_style(i, ws_style)
the_ws.set_col_style(5, Style(size=30, alignment=Alignment(horizontal="center", vertical="center")))
wb = Workbook()
# ws = wb.new_sheet(u"南区交换", data=self.exchange_south_data)
# set_style(ws)
# ws = wb.new_sheet(u"西南交换", data=self.exchange_wsouth_data)
# set_style(ws)
# ws = wb.new_sheet(u"斋区交换", data=self.exchange_vege_data)
# set_style(ws)
# ws = wb.new_sheet(u"南区赠与", data=self.given_south_data)
# set_style(ws)
# ws = wb.new_sheet(u"西南赠与", data=self.given_wsouth_data)
# set_style(ws)
# ws = wb.new_sheet(u"斋区赠与", data=self.given_vege_data)
# set_style(ws)
ws = wb.new_sheet(u'交换表', data=self.exchange_data)
set_style(ws)
ws = wb.new_sheet(u'赠与表', data=self.given_data)
set_style(ws)
wb.save('stucampus/christmas/info/1.xlsx')
def make_xlsx(self):
wb = Workbook()
ws = wb.new_sheet("sheet1")
row = 1
column = 1
for i in range(0, len(self.stock_list), 1):
for j in range(0, len(self.stock_list[i]), 1):
ws.set_cell_value(row, column, self.stock_list[i][j])
column = column + 1
row = row + 2
column = 1
row = 2
column = 1
for i in range(0, len(self.keywords_list), 1):
for j in range(0, len(self.keywords_list[i]), 1):
ws.set_cell_value(row, column, self.keywords_list[i][j])
column = column + 1
row = row + 2
column = 1
wb.save(self.path)
def __call__(self, data, wrapped=True):
outfile = BytesIO()
# Create workbook
wb = Workbook()
ws = wb.new_sheet("data")
# Headers
headers = self.fields_by_major_version['1'].keys()
final_column = get_column_letter(len(headers))
ws.range("A1", final_column + "1").value = [headers]
# Data
row_count = 2
get_major_version = self.get_major_version
for obj in data.items:
row = [
accessor(obj) for accessor in self.fields_by_major_version[
get_major_version(obj)].values()
]
ws.range("A" + str(row_count),
final_column + str(row_count)).value = [row]
row_count += 1
# Save
wb.save(outfile)
# Return
outfile.seek(0)
return {'file': outfile, 'client_filename': self.client_filename}
def create_workbook(self, data, structure):
# given the data as json and the structure as json too, create a workbook with this data
self.wb = Workbook()
# order the fields for proper display
for sheet_name, sheet_fields in list(structure.items()):
self.sorted_sheet_fields[sheet_name] = self.order_fields(
sheet_fields)
self.pending_processing['main'] = {'data': data, 'is_processed': False}
# from the docs: https://docs.python.org/2.7/tutorial/datastructures.html#dictionaries
# It is sometimes tempting to change a list while you are looping over it;
# however, it is often simpler and safer to create a new list instead
# so lets not change the list while iterating
while len(list(self.pending_processing.keys())) != 0:
all_processed = True
for sheet_name, data in list(self.pending_processing.items()):
if data['is_processed'] == False:
terminal.tprint('Processing ' + sheet_name, 'okblue')
all_processed = False
self.process_and_write(data['data'], sheet_name)
# mark it as processed
self.pending_processing[sheet_name]['is_processed'] = True
break
if all_processed == True:
break
self.wb.save(self.wb_name)
return False
def export_to_xlsx(data, temp_dir):
start_time = datetime.now()
# full_file_path = "{0}{1}temp.xlsx".format(file_path, os.sep)
file_path = "/tmp/temp.xlsx"
try:
wb = Workbook()
ws = wb.new_sheet("Policies", data=data)
ws.set_row_style(1, Style(font=Font(bold=True))) # bold the header row
# save xlsx file and open it as a binary file
wb.save(file_path)
xlsx_file = open(file_path, 'rb')
output = io.BytesIO()
output.write(xlsx_file.read())
# close and delete file
xlsx_file.close()
os.remove(file_path)
except Exception as ex:
m.logger.fatal("\tunable to export to file: {}".format(ex,))
return None
m.logger.info("\tfile export took {}".format(datetime.now() - start_time))
return output
def to_xlsx(self, filename, submissions):
workbook = Workbook()
sheets = {}
for chunk in self.parse_submissions(submissions):
for section_name, rows in chunk.items():
try:
cursor = sheets[section_name]
current_sheet = cursor['sheet']
except KeyError:
current_sheet = workbook.new_sheet(section_name)
cursor = sheets[section_name] = {
"sheet": current_sheet,
"row": 2,
}
for i, label in enumerate(self.labels[section_name], 1):
current_sheet.set_cell_value(1, i, label)
for row in rows:
y = cursor["row"]
for i, cell in enumerate(row, 1):
current_sheet.set_cell_value(y, i, cell)
cursor["row"] += 1
workbook.save(filename)
def export_to_excel(self, filename: str) -> None:
"""
Export in XLSX format.
"""
from pyexcelerate import Workbook # type: ignore
self.__preprocess()
sub = len(self.keys)
if sub >= 1:
data = [
self.values[ctr:ctr + sub]
for ctr in range(0, len(self.values), sub)
]
else:
typer.secho(
"An error occured please check your query.",
fg=typer.colors.RED,
bold=True,
)
sys.exit()
data.insert(0, self.keys)
wb = Workbook()
ws = wb.new_sheet("Analytics", data=data)
wb.save(filename)
typer.secho(
"\nAnalytics successfully created in XLSX format ✅",
bold=True,
)
def to_xlsx(self, filename, submissions):
workbook = Workbook()
sheets = {}
for chunk in self.parse_submissions(submissions):
for section_name, rows in chunk.items():
try:
cursor = sheets[section_name]
current_sheet = cursor['sheet']
except KeyError:
current_sheet = workbook.new_sheet(section_name)
cursor = sheets[section_name] = {
"sheet": current_sheet,
"row": 2,
}
for i, label in enumerate(self.labels[section_name], 1):
current_sheet.set_cell_value(1, i, label)
for row in rows:
y = cursor["row"]
for i, cell in enumerate(row, 1):
current_sheet.set_cell_value(y, i, cell)
cursor["row"] += 1
workbook.save(filename)
def write_cell_data_fast():
wb = Workbook()
ws = wb.new_sheet("sheet name")
ws[1][1].value = 15 # a number
ws[1][2].value = 20
ws[1][3].value = "=SUM(A1,B1)" # a formula
ws[1][4].value = str(datetime.now()) # a date
wb.save("write_cell_data_fast.xlsx")
def output(self, records):
excel_file = self.get_file_path(self.json.file, '.xlsx')
wb = Workbook()
self.set_sheets(wb)
for record in records:
for cell in record:
self.write(cell)
wb.save(excel_file)
def write_cell_data_faster():
wb = Workbook()
ws = wb.new_sheet("sheet name")
ws.set_cell_value(1, 1, 15) # a number
ws.set_cell_value(1, 2, 20)
ws.set_cell_value(1, 3, "=SUM(A1,B1)") # a formula
ws.set_cell_value(1, 4, str(datetime.now())) # a date
wb.save("write_cell_data_faster.xlsx")
def Make_stock_title_xlsx(self, dir_path, file_name):
print("Make method")
wb1 = Workbook()
ws1 = wb1.new_sheet("sheet1")
for i in range(0, len(self.title_list), 1):
ws1.set_cell_value(i + 1, 1, self.title_list[i])
wb1.save(dir_path + '/' + file_name + '.xlsx')
def __init__(self, filename_or_stream, sheet_name='Sheet1'):
self._stream = filename_or_stream
self._workbook = Workbook()
if sheet_name is not None:
self._sheet = self._workbook.new_sheet(sheet_name)
self._default_style = None
self._rowcount = 0
def styling_rows_fast():
from pyexcelerate import Workbook, Color
from datetime import datetime
wb = Workbook()
ws = wb.new_sheet("sheet name")
ws[1][1].value = 123456
ws[1].style.fill.background = Color(255, 0, 0)
wb.save("styling_rows_fast.xlsx")
def styling_columns_fastest():
from pyexcelerate import Workbook, Color, Style, Fill
from datetime import datetime
wb = Workbook()
ws = wb.new_sheet("sheet name")
ws[1][1].value = 123456
ws.set_col_style(1, Style(fill=Fill(background=Color(255, 0, 0, 0))))
wb.save("styling_columns_fastest.xlsx")
def makeExcel(result):
wb = Workbook()
for i in range(len(result[0])):
GAME = result[0][i][0]
MUTATION_RATE = result[0][i][1]
LAYERS = result[0][i][2]
MAX_GENERATIONS = result[0][i][3]
POPULATION_COUNT = result[0][i][4]
ELAPSED_TIME = result[0][i][5]
COL_TOTALS = result[0][i][6]
INFO = result[0][i][7]
GENERATIONS = result[0][i][8]
sheetname = f"Genetics_{i+1}_{MUTATION_RATE}"
ws = wb.new_sheet(f"{i+1}")
for i in range(6):
ws.set_col_style(i + 1, Style(size=-1))
ws.cell("A1").value = GAME
ws.cell("A2").value = "Genetic Alg"
ws.cell("A3").value = sheetname
ws.cell("B1").value = "Mutation rate:"
ws.cell("B2").value = MUTATION_RATE
ws.cell("C1").value = "Generations:"
ws.cell("C2").value = MAX_GENERATIONS
ws.cell("D1").value = "Population count:"
ws.cell("D2").value = POPULATION_COUNT
ws.cell("E1").value = "Hidden Layers:"
lay = ''
for i in range(len(LAYERS)):
lay += f"{LAYERS[i]}, "
ws.cell("E2").value = lay
ws.cell("F1").value = "Time(ms):"
ws.cell("F2").value = ELAPSED_TIME
ws.cell('G1').value = INFO
startrow = 4
startcol = 2
ws[startrow - 1][startcol].value = "Generation"
ws[startrow - 1][startcol + 1].value = "Average Fitness"
ws[startrow - 1][startcol + 2].value = "Max Fitness"
for i in range(len(GENERATIONS)):
for j in range(len(GENERATIONS[0])):
ws[startrow + i][startcol + j].value = int(GENERATIONS[i][j])
ws[startrow + len(GENERATIONS)][startcol].value = "Total:"
ws[startrow + len(GENERATIONS)][startcol + 1] = int(COL_TOTALS[1])
ws[startrow + len(GENERATIONS)][startcol + 2] = int(COL_TOTALS[2])
wb.save(
f"C:\\Users\\dosha\\Desktop\\ExcelFiles\\GA_{GAME}_{dt.datetime.now().strftime('%f')}.xlsx"
)
print("..........Excel file generated..............")
def make_excel(self):
def set_style(the_ws):
ws_style = Style(size=15, alignment=Alignment(horizontal="center", vertical="center"))
the_ws.range("A1", "D1").merge()
for i in range(1, 5):
the_ws.set_col_style(i, ws_style)
wb = Workbook()
ws = wb.new_sheet(u'交换表', data=self.exchange_data)
set_style(ws)
ws = wb.new_sheet(u'赠与表', data=self.given_data)
set_style(ws)
wb.save('stucampus/christmas/info/3.xlsx')
def styling_ranges():
from pyexcelerate import Workbook, Color
from datetime import datetime
wb = Workbook()
ws = wb.new_sheet("test")
ws.range("A1", "C3").value = 1
ws.range("A1", "C1").style.font.bold = True
ws.range("A2", "C3").style.font.italic = True
ws.range("A3", "C3").style.fill.background = Color(255, 0, 0, 0)
ws.range("C1", "C3").style.font.strikethrough = True
wb.save("styling_ranges.xlsx")
def _write_xlsx_output(self, in_both_data=[]):
wb = Workbook()
if in_both_data:
wb.new_sheet('in_both', data=in_both_data)
else:
for name, df in self.dfs.items():
if self.include_options[name]:
sheet_name = f'file_{name}'
column_names = list(df)
data = [
column_names,
] + df.values.tolist()
wb.new_sheet(sheet_name, data=data)
try_to_save = True
while try_to_save:
try:
wb.save(self.output_path)
self.output_saved = True
try_to_save = False
except PermissionError:
try_to_save = msg.askretrycancel(
self.title, f'Could not save file to ({self.output_path}) '
'because there is a file with that name currently '
'open. Close that file to allow for this one to be '
'saved.')
def write_excel(file_name, data, engine='pyexcelerate'):
"""
Write multiple sheets to one excel and save to disk.
Ignore df's index and forbidden MultiIndex columns.
Notes:
1. `pyexcelerate` can be nearly three times faster than `pd.to_excel`.
2. save as csv much faster than excel
:param file_name:
:param data: [(sheet_name, df), ...]
:param engine:
pyexcelerate: modify headers' style, display date properly and don't display nan
pd.to_excel:
pd.to_csv: file_name/sheet_name.csv
:return: None
"""
if engine == 'pyexcelerate':
wb = Workbook()
for sheet_name, df in data:
cols = df.columns.tolist()
if len(df) > 0:
# don't display nan
df = df.fillna('')
# display date properly
for col in cols:
if isinstance(df[col].iloc[0], datetime.date):
df[col] = df[col].astype(str)
ws = wb.new_sheet(sheet_name, [cols] + df.values.tolist())
# modify headers' style
h, w = df.shape
right = num2title(w) + '1'
ws.range("A1",
right).style.fill.background = Color(210, 210, 210, 0)
ws.range("A1", right).style.font.bold = True
ws.range("A1", right).style.alignment.horizontal = 'center'
ws.range("A1", right).style.borders.right.style = '_'
ws.range("A1", right).style.borders.bottom.style = '_'
wb.save(file_name)
elif engine == 'pd.to_excel':
writer = pd.ExcelWriter(file_name)
for sheet_name, df in data:
df.to_excel(writer, sheet_name, index=False)
writer.save()
writer.close()
elif engine == 'pd.to_csv':
dir_name = file_name.replace('.xlsx', '')
makedirs(dir_name)
for sheet_name, df in data:
df.to_csv(os.path.join(dir_name, sheet_name + '.csv'), index=False)
else:
pass
def make_XlsxFile(similarity_2D_array, search_names, save_dir = 'C:/data/2018_09_09_test/2D_array', save_name = '2D_array.xlsx'):
wb = Workbook()
ws = wb.new_sheet('new sheet')
for i in range(0, len(search_names), 1):
ws[1][i + 2].value = search_names[i]
for i in range(0, len(search_names), 1):
ws[i + 2][1].value = search_names[i]
for i in range(0, len(search_names), 1):
for j in range(0, len(search_names), 1):
ws[i + 2][j + 2].value = similarity_2D_array[i][j]
wb.save(save_dir + '/' + save_name)
def specaud():
from datetime import date
from pathlib import Path
from pyexcelerate import Workbook
from rfpack.validatabc import validatab
from rfpack.customparamc import customparam
from rfpack.pntopdc import pntopd
from rfpack.graffullc import graffull
from rfpack.csvfrmxlsxc import xlsxfmcsv
proglabel2.config(text="") # label init
datab = Path('C:/SQLite/20200522_sqlite.db')
pdf_file = date.today().strftime("%y%m%d") + '_Feat1ParAudit.pdf'
pdf_path = datab.parent / pdf_file
xls_file = Path(pdf_path.with_suffix('.xlsx'))
wb = Workbook() # pyexcelerate Workbook
fndtbl = datab.parent / Path('findtable.csv')
tbcstm = datab.parent / Path('tabcustom.csv')
validatab(datab, fndtbl, tbcstm) # locate input tab/parameters in dbabase
pnglist, sheetsdic = customparam(datab, 'tab_par', 5, root, my_progress,
proglabel2) # generates png files
# print Total info in 4 pages, 3 regions per page, bar starts at 60%
pnglist1 = graffull(xls_file, 'Total', 4, 60, root, my_progress,
proglabel2)
pnglist1.extend(pnglist) # review png at the beginning
pntopd(pdf_path, pnglist1, 50, 550, 500, 500) # png to pdf
xlsxfmcsv(xls_file, sheetsdic, 75, root, my_progress, proglabel2)
my_progress[
'value'] = 100 # prog bar increase a cording to i steps in loop
proglabel2.config(text=my_progress['value'])
response = messagebox.showinfo("Specific Audit", "Process Finished")
proglabel3 = Label(root, text=response)
my_progress['value'] = 0 # prog bar increase according to i steps in loop
proglabel2.config(text=" ")
root.update_idletasks()
def get_xml(request):
print "1"
response = HttpResponse(content_type='application/ms-excel')
print "2"
response['Content-Disposition'] = 'attachment; filename="users.xls"'
print "3"
wb = Workbook()
print "4"
ws = wb.new_sheet(u'Реестр')
print "5"
ws.range("A1", "M1").style.font.bold = True
print "6"
fill_xlsx_header(ws)
print "7"
wb.save(response)
return response
def convert_xlsx(request):
update = request.GET.get('file')
to = request.GET.get('to')
path = os.path.join(os.path.dirname(os.path.dirname(__file__)),
'../static/profitability/update_presupuesto/' + update)
data = pd.read_csv(path)
newName = ''
if to == 'excel':
newName = update.replace("csv", "xlsx")
newPath = os.path.join(
os.path.dirname(os.path.dirname(__file__)),
'../static/profitability/update_presupuesto/temp/' + newName)
data['Fecha'] = pd.to_datetime(data.Fecha).dt.strftime('%Y-%m-%d')
data['Fecha'] = pd.to_datetime(data['Fecha'])
# data.to_excel(newPath, sheet_name='OutPut', index=None, float_format='%.5f', startrow=0, header=True)
values = [data.columns] + list(data.values)
wb = Workbook()
ws = wb.new_sheet('OutPut', data=values)
ws.range("H1",
"H" + str(len(data) + 1)).style.format.format = 'dd/mm/yyyy'
wb.save(newPath)
with open(newPath, 'rb') as fh:
response = HttpResponse(fh.read(),
content_type="application/vnd.ms-excel")
response[
'Content-Disposition'] = 'inline; filename=' + os.path.basename(
newPath)
return response
if to == 'puntoycoma':
newName = update.replace("csv", "csv")
newPath = os.path.join(
os.path.dirname(os.path.dirname(__file__)),
'../static/profitability/update_presupuesto/temp/' + newName)
data.to_csv(newPath,
index=None,
sep=';',
encoding='utf-8-sig',
float_format='%.5f',
header=True,
decimal=".")
html = '<a href="/static/profitability/update_presupuesto/temp/' + newName + '">' + newName + '</a>'
return HttpResponse(html)
def styling_cell_fast():
from pyexcelerate import Workbook, Color
from datetime import datetime
wb = Workbook()
ws = wb.new_sheet("sheet name")
ws[1][1].value = 123456
ws[1][1].style.font.bold = True
ws[1][1].style.font.italic = True
ws[1][1].style.font.underline = True
ws[1][1].style.font.strikethrough = True
ws[1][1].style.fill.background = Color(0, 255, 0, 0)
ws[1][2].value = datetime.now()
ws[1][2].style.format.format = 'mm/dd/yy'
wb.save("styling_cell_fast.xlsx")
def styling_cell_faster():
from pyexcelerate import Workbook, Color
from datetime import datetime
wb = Workbook()
ws = wb.new_sheet("sheet name")
ws.set_cell_value(1, 1, 123456)
ws.get_cell_style(1, 1).font.bold = True
ws.get_cell_style(1, 1).font.italic = True
ws.get_cell_style(1, 1).font.underline = True
ws.get_cell_style(1, 1).font.strikethrough = True
ws.get_cell_style(1, 1).fill.background = Color(0, 255, 0, 0)
ws.set_cell_value(1, 2, datetime.now())
ws.get_cell_style(1, 2).format.format = 'mm/dd/yy'
wb.save("styling_cell_faster.xlsx")
def merge_cell():
wb = Workbook()
ws = wb.new_sheet("sheet name")
ws[1][1].value = 15
ws.range("A1", "B1").merge()
ws[1][5].value = 15
ws.range("E1", "G1").merge()
ws[3][1].value = 15
ws.range("A3", "A4").merge()
ws[3][5].value = 15
ws.range("E3", "E5").merge()
wb.save("merge_cell.xlsx")
def xlsxfmcsv(xlsxfl, lst, iterini, root1, my_progress1, proglabel21):
wb = Workbook() # pyexcelerate Workbook
for index in range(len(lst)):
my_progress1['value'] = iterini + round(
index / len(lst) * 15) # prog bar up to iterini + 15
proglabel21.config(text=my_progress1['value']) # prog bar updt
root1.update_idletasks()
df1 = pd.read_csv(xlsxfl.parent / 'csv' /
Path(lst[index]['name'] + '.csv'))
pyexecelerate_to_excel(wb,
df1,
sheet_name=lst[index]['name'],
index=False)
wb.save(xlsxfl)
my_progress1['value'] = iterini + 25 # prog bar up to iterini + 25
proglabel21.config(text=my_progress1['value']) # prog bar updt
root1.update_idletasks()
return
def styling_cell_fastest():
from pyexcelerate import Workbook, Color, Style, Font, Fill, Format
from datetime import datetime
wb = Workbook()
ws = wb.new_sheet("sheet name")
ws.set_cell_value(1, 1, 123456)
ws.set_cell_style(1, 1, Style(font=Font(bold=True)))
ws.set_cell_style(1, 1, Style(font=Font(italic=True)))
ws.set_cell_style(1, 1, Style(font=Font(underline=True)))
ws.set_cell_style(1, 1, Style(font=Font(strikethrough=True)))
ws.set_cell_style(1, 1,
Style(fill=Fill(background=Color(255, 228, 75, 52))))
ws.set_cell_value(1, 2, datetime.now())
ws.set_cell_style(1, 2, Style(format=Format('mm/dd/yy')))
wb.save("styling_cell_fastest.xlsx")
def __save_xls(self):
wb = Workbook()
for model in self.models:
ws = wb.new_sheet(sheet_name=model)
# sets the column name
for j in range(1, len(self.metric_names) + 1):
ws.set_cell_value(1, j + 1, self.metric_names[j - 1])
# ws.set_cell_style(1, j, Style(fill=Fill(background=Color(224, 224, 224, 224))))
ws.set_cell_style(1, j + 1, Style(font=Font(bold=True)))
# sets the cells values
for i in range(1, self.runs + 1):
# sets the first value in col 1 to "runX"
ws.set_cell_value(i + 1, 1, 'run ' + str(i))
for j in range(1, len(self.metric_names) + 1):
try:
ws.set_cell_value(
i + 1, j + 1,
self.metrics[model][self.metric_names[j - 1]][i -
1])
except IndexError:
ws.set_cell_value(i + 1, j + 1, '')
except KeyError:
pass
# after the last run row plus one empty row
offset = self.runs + 3
for i in range(0, len(self.descriptive_stats_names)):
ws.set_cell_value(i + offset, 1,
self.descriptive_stats_names[i])
for j in range(0, len(self.metric_names) - 1):
try:
ws.set_cell_value(
i + offset, j + 2,
self.descriptive_stats[model][self.metric_names[j]]
[self.descriptive_stats_names[i]])
except KeyError:
pass
wb.save(self.outfile)
def main():
wk_source_path = './data_5000.xlsx'
wk_target_path = './data_target.xlsx'
ws_source = init_worksheet(wk_source_path)
wb_target = Workbook()
ws_target = wb_target.new_sheet("Sheet1")
first_row_source = 2
last_row_source = 6842
# l'index de la source commence à 0, celui du target à 1
source_to_target_mapper = {0: 1, 1: 2, 9: 2, 17: 3, 4: 4, 12: 5}
for row_target, line_source in enumerate(range(first_row_source,
last_row_source),
start=5):
for key, value in source_to_target_mapper.items():
ws_target[row_target][value].value = ws_source[line_source][key]
wb_target.save(wk_target_path)
def print_excel_report(query, database, header, gq, filename):
wb = Workbook()
#Print Cover material
ws = wb.new_sheet("Cover")
ws.set_cell_value(1, 1, "Date Generated:")
ws.set_cell_value(1, 2, datetime.datetime.now())
ws.set_cell_value(2, 1, "GEMINI Query:")
ws.set_cell_value(2, 2, query)
ws.set_cell_value(3, 1, "GEMINI Database:")
ws.set_cell_value(3, 2, database)
ws2 = wb.new_sheet("Variants", data=header)
row = 2
for row in gq:
cell = 1
row = row + 1
wb.save(filename)
def styling_cell_some_sample_format():
from pyexcelerate import Workbook, Style
wb = Workbook()
ws = wb.new_sheet("sheet name")
ws.set_col_style(5, Style(size=30)) # set width of column # E col
ws.cell("E1").value = datetime.now()
ws.cell("E1").style.format.format = 'mm/dd/yy hh:MM:ss' # datetime
ws.cell("E2").value = 12345678
ws.cell("E2").style.format.format = '#,##0' # number : 12,345,678
ws.cell("E3").value = 1234.5678
ws.cell("E3").style.format.format = '#,##0.00' # float number : 1,234.57
ws.cell("E4").value = 0.12345
ws.cell("E4").style.format.format = '0.00%' # percentage: 12.35%
wb.save("styling_cell_some_sample_format.xlsx")
def styling_defined_by_objects():
from pyexcelerate import Workbook, Font, Color, Alignment
wb = Workbook()
ws = wb.new_sheet("sheet name")
ws[1][1].value = datetime.now()
ws[1][1].style.font = Font(bold=True,
italic=True,
underline=True,
strikethrough=True,
family="Calibri",
size=10,
color=Color(255, 0, 0))
ws[1][1].style.format.format = 'mm/dd/yy'
ws[1][1].style.alignment = Alignment(
horizontal="left", vertical="bottom", rotation=0,
wrap_text=True) #("left", "center", "right"),
wb.save("styling_defined_by_objects.xlsx")
def make_excel(self):
wb = Workbook()
ws = wb.new_sheet(u'交换表', data=self.exchange_data)
wb.save('stucampus/christmas/info/2.xlsx')
'''
from openpyxl import load_workbook
wb = load_workbook(filename = '/home/ductu/Downloads/3g.xlsx',read_only=True)
ws = wb['Database 3G']
d = ws.cell(row = 4, column = 2)
print d.value
count=1
for row in ws.iter_rows('A2:AJ3'):
print 'number of row ', count
count+=1
for cell in row:
print cell.value
'''
from pyexcelerate import Workbook
wb = Workbook()
ws = wb.new_sheet("test")
ws.range("B2", "C3").value = [[1, 2], [3, 4]]
wb.save("output.xlsx")
return out
itemsCollection = []
# создаём заголовок таблицы
row = []
for item in data['items']:
row.append(item['name'])
row.append('Original URL')
itemsCollection.append(row)
# сами данные парсинга
totalLinks = len(data['links'])
i = 0
for link in data['links']:
i+=1
g.go(link)
row = []
for item in data['items']:
content = parse(item['xpath'], item['type'])
row.append(content)
row.append(link)
itemsCollection.append(row)
print '['+ str(i) +'/'+ str(totalLinks) +']'
wb = Workbook()
ws = wb.new_sheet("sheet name", data=itemsCollection)
ws.set_row_style(1, Style(font=Font(bold=True)))
wb.save("output.xlsx")
