def write_anon_books(self, ddir):
if not os.path.isdir(ddir):
if os.path.exists(ddir):
raise ValueError
os.makedirs(ddir, exist_ok=True)
for i, b in enumerate(self._books):
newbook = os.path.join(ddir, 'anonymized_%d.xlsx' % i)
with Workbook(newbook) as wb:
bold = wb.add_format({'bold': True})
self._write_next_book(b, wb, bold)
def write_presentation(self, values, records, output):
workbook = Workbook(output, {'constant_memory': True})
worksheet = workbook.add_worksheet()
for column, value in enumerate(values):
worksheet.write_string(0, column, value)
for row, record in enumerate(records):
value_iterator = enumerate([unicode(record[v]) for v in values])
for column, text in value_iterator:
worksheet.write_string(row + 1, column, text)
workbook.close()
def write_df_to_excel(df,
header=None,
filename_end=None,
bold_row_0=False,
bold_col_0=False):
"""
Writes a dataframe with a datetime index to a file.
Takes in an optional header and extra label for the filename.
The first rows and columns can be bolded in the saved excel file with bold_[row/col]_0.
:param pd.DataFrame df: Pandas dataframe to write to file
**must have a datetime index
:param list header: list of headers to write to the file if desired. Usually, it's ['date'] + df.columns.tolist()
:param str filename_end: a string to append to the filename if desired, otherwise the current date is used only
:param bool bold_row_0: True to bold the first row, defaults to false/non-bold
:param bool bold_col_0: True to bold the first column, defaults to false/non-bold
:return: None; writes to file
"""
from xlsxwriter import Workbook
df.index = df.index.map(lambda x: datetime.strftime(
x, '%Y-%m-%d %H:%M:%S')) # change index to ISO8601 time
datestamp = datetime.now().strftime("%Y_%m_%d")
filename = f"{datestamp}_{filename_end}.xlsx" if filename_end else f"{datestamp}_data.xlsx"
workbook = Workbook(filename)
workbook.nan_inf_to_errors = True
worksheet = workbook.add_worksheet('Data')
row_0_fmt = workbook.add_format(
{'bold': bold_row_0}) # create row formats to apply based on inputs
col_0_fmt = workbook.add_format({'bold': bold_col_0})
if header:
for col, head in enumerate(header):
worksheet.write(0, col, head, row_0_fmt)
row_count = 1 if header else 0 # start on row 1 if a header was provided
for row in df.iterrows():
col_count = 1
worksheet.write(row_count, 0, row[0],
col_0_fmt) # write index to first col
for col in row[
1]: # row[1] is all values that aren't the index - pandas df.iterrows() specific
worksheet.write(row_count, col_count, col)
col_count += 1
row_count += 1
workbook.close()
def save(self, sa):
"""
:param sa:
"""
ext = os.path.splitext(sa)[1][1:].strip().lower()
if self.nparray is None:
raise ValueError
t = self.nparray[:, 0]
r_a = self.nparray[:, 1]
if sa:
if ext == 'dat':
r_n = r_a / 10
n_nparray = np.column_stack((t, r_n))
np.savetxt(sa, n_nparray, delimiter='\t', fmt=['%d', '%.3f'])
elif ext == 'xslx':
try:
from xlsxwriter import Workbook
except ImportError:
raise XLSWImportError
wb = Workbook(sa)
ws = wb.add_worksheet("summary_distances")
ws.write_row(0, 0, ('Time, ps', 'COM, \u212b'))
ws.write_column(1, 0, t)
ws.write_column(1, 1, r_a)
wb.close()
elif ext == 'xls':
try:
from xlwt import Workbook as Workbook
except ImportError:
raise XLWTImportError
wb = Workbook()
ws = wb.add_sheet("summary_distances")
ws.write(0, 0, 'Time, ps')
ws.write(0, 1, 'COM, \u212b')
for i, t_i in enumerate(t, start=1):
ws.write(i, 0, t_i)
for j, r_a_j in enumerate(r_a, start=1):
ws.write(j, 1, r_a_j)
wb.save(sa)
else:
raise BadExtError
def _open_worksheet(xlsx_file):
"""
Enable XlsxWriter Worksheet object to be opened, operated on, and
then automatically closed within a `with` statement. A filename or
stream object (such as a ``BytesIO`` instance) is expected as
*xlsx_file*.
"""
workbook = Workbook(xlsx_file, {'in_memory': True})
worksheet = workbook.add_worksheet()
yield workbook, worksheet
workbook.close()
def csv_to_xlsx():
for csvfile in glob.glob(os.path.join(".", "*.csv")):
workbook = Workbook(csvfile[:-4] + "-" + str(datetime.date.today()) + ".xlsx")
worksheet = workbook.add_worksheet()
with open(csvfile, "rt", encoding="utf8") as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
worksheet.write(r, c, col)
workbook.close()
os.remove(csvfile)
def mt_excel_files(store, case_obj, temp_excel_dir):
"""Collect MT variants and format line of a MT variant report
to be exported in excel format
Args:
store(adapter.MongoAdapter)
case_obj(models.Case)
temp_excel_dir(os.Path): folder where the temp excel files are written to
Returns:
written_files(int): the number of files written to temp_excel_dir
"""
today = datetime.datetime.now().strftime("%Y-%m-%d")
samples = case_obj.get("individuals")
query = {"chrom": "MT"}
mt_variants = list(
store.variants(case_id=case_obj["_id"],
query=query,
nr_of_variants=-1,
sort_key="position"))
written_files = 0
for sample in samples:
sample_id = sample["individual_id"]
display_name = sample["display_name"]
sample_lines = export_mt_variants(variants=mt_variants,
sample_id=sample_id)
# set up document name
document_name = (
".".join([case_obj["display_name"], display_name, today]) +
".xlsx")
workbook = Workbook(os.path.join(temp_excel_dir, document_name))
Report_Sheet = workbook.add_worksheet()
# Write the column header
row = 0
for col, field in enumerate(MT_EXPORT_HEADER):
Report_Sheet.write(row, col, field)
# Write variant lines, after header (start at line 1)
for row, line in enumerate(
sample_lines, 1): # each line becomes a row in the document
for col, field in enumerate(
line): # each field in line becomes a cell
Report_Sheet.write(row, col, field)
workbook.close()
if os.path.exists(os.path.join(temp_excel_dir, document_name)):
written_files += 1
return written_files
def test_header_construction(self):
class TestSheet(ReportSheet):
def fetch_records(self):
return []
pre_data_rows = 5
SheetColumn('key', header_2='foo', header_4='bar')
sheet = TestSheet(Workbook(BytesIO()))
col = sheet.units[0]
assert col.header_data == ['', '', 'foo', '', 'bar']
def test_header_construction_overflows_sheet(self):
class TestSheet(ReportSheet):
def fetch_records(self):
return []
pre_data_rows = 3
SheetColumn('key', header_2='foo', header_4='bar')
with pytest.raises(ProgrammingError) as exc_info:
TestSheet(Workbook(BytesIO()))
assert 'not enough pre-data rows on sheet' in str(exc_info)
def __exelwriter(
self, csvfile
): # creates same named as csvfile xlsx and write data to it
wb = Workbook(self.__reportsdirectory + csvfile[:-4] + '.xlsx')
ws = wb.create_sheet("Sheet")
with open(csvfile, 'rt', newline="") as f:
reader = csv.reader(f, delimiter=';')
for subarray in reader:
subarray.append(csvfile[:-3])
ws.append(subarray)
wb.save(self.__reportsdirectory + csvfile[:-3] + '.xlsx')
def create(self):
self._workbook = Workbook(self._filename, {'remove_timezone': True})
self._date_format = self._workbook.add_format(
{'num_format': 'dd/mm/yy'})
self._wrap_format = self._workbook.add_format()
self._wrap_format.set_text_wrap()
for group_id, group_name in self._groups.items():
worksheet = self._workbook.add_worksheet(group_name)
self._write_headers(worksheet)
self._worksheets[group_id] = worksheet
self._worksheets_lines[group_id] = 1
def create_excel_file(self, excel_filename):
"""
This function creates a workbook(excel file) and returns its object.
:param excel_filename: Name of the file to be created. (include .xlsx in name)
:return: object of Workbook class (excel file)
"""
try:
# Create an new Excel file and add a worksheet.
workbook = Workbook(excel_filename)
return workbook
except Exception as err:
logging.error("Error occured while creating excel file: {}. Error : {}".format(excel_filename, err))
def write(self):
super().write()
self.workbook = Workbook(self.filename + ".xlsx")
self.cell_formats = {}
for key, value in self.colours.items():
self.cell_formats[key] = self.workbook.add_format()
self.cell_formats[key].set_bg_color(value)
for sheet in self.sheets:
self.write_worksheet(sheet)
self.workbook.close()
def __init__(self, fn):
self.logFilename = fn
print(f'logfile name: {fn}')
if os.path.exists(fn):
os.remove(fn)
self.headerWritten = False
self.rowNum = 0
if fn.lower().endswith('.xls') or fn.lower().endswith('.xlsx'):
self.logFileHandle = Workbook(fn, {'constant_memory': True})
else:
self.logFileHandle = open(fn, 'w+')
self.fn = fn
def report_plans_xls(request):
output = io.BytesIO()
data = report_plans()
wb = Workbook(output, {'in_memory': True})
ws = wb.add_worksheet()
ws.write(0, 0, 'Quantidade de Assinantes')
ws.write(0, 1, 'Quantidade de Assinantes Principais')
ws.write(0, 2, '%')
ws.write(0, 3, 'Quantidade de Assinantes Secundários')
ws.write(0, 4, '%')
ws.write(1, 0, data['stbs_total'])
ws.write(1, 1, data['stbs_principal']['total'])
ws.write(1, 2, data['stbs_principal']['percent'])
ws.write(1, 3, data['stbs_secondary']['total'])
ws.write(1, 4, data['stbs_secondary']['percent'])
ws.write(2, 0, 'Dados de cobrança TV linear')
ws.write(2, 3, 'Dados de cobrança TVoD')
ws.write(3, 0, 'Planos')
ws.write(3, 1, 'Quantidade de Assinantes')
ws.write(3, 2, 'Total')
ws.write(3, 3, '%')
ws.write(3, 4, 'Planos')
ws.write(3, 5, 'Quantidade de STBs com TVoD')
ws.write(3, 6, 'Total')
i = 4
for plan, values in data['plans'].items():
ws.write(i, 0, str(plan))
ws.write(i, 1, values['stbs'])
if 'stbs_value' in values:
ws.write(i, 2, values['stbs_value'])
ws.write(i, 3, values['stbs_percent'])
ws.write(i, 4, str(plan))
ws.write(i, 5, values['tvod'])
if 'tvod_value' in values:
ws.write(i, 6, values['tvod_value'])
i += 1
ws.write(i, 0, 'Total')
i += 1
ws.write(i, 0, 'Valor total TV linear')
ws.write(i, 1, 'Valor total TVoD')
ws.write(i, 2, 'Valor total')
i += 1
ws.write(i, 0, data['stbs_value'])
ws.write(i, 1, data['tvods_value'])
ws.write(i, 2, data['total_value'])
wb.close()
output.seek(0)
response = HttpResponse(output.read())
response['Content-Disposition'] = \
'attachment; filename=relatorio_stbs.xlsx'
return response
def action(args):
book = Workbook()
(infiles, ) = args.infiles
for fname in infiles:
(f_path, f_name) = os.path.split(fname)
(f_short_name, f_extension) = os.path.splitext(f_name)
print(fname)
write_workbook(f_short_name, book, fname)
book.filename = args.outfile
book.close()
def write_xlsx(serializer, headers, title_text):
output = StringIO.StringIO()
workbook = Workbook(output)
worksheet_s = workbook.add_worksheet('Summary')
# Here we will adding the code to add data
title = workbook.add_format({
'bold': True,
'font_size': 14,
'align': 'center',
'valign': 'vcenter'
})
header = workbook.add_format({
'bg_color': '#92d050',
'color': 'black',
'align': 'center',
'valign': 'top',
'border': 1
})
cell = workbook.add_format({
'align': 'left',
'valign': 'top',
'text_wrap': False,
'border': 1
})
hearder_len = len(headers)
header_range = xl_range(0, 0, 0, hearder_len - 1)
worksheet_s.merge_range(header_range, title_text, title)
# column header
for col, field in enumerate(headers):
worksheet_s.write(1, col, field[1], header)
worksheet_s.set_column(col, col, width=field[2])
# column content
for row, line in enumerate(serializer.data, start=2):
# Write a line for each record
for col, field in enumerate(headers):
keys = field[0].split('.')
value = line
for key in keys[:-1]:
value = value[key]
if "|" in keys[-1]:
key, flt = keys[-1].split("|")
value = field_display(value[key], flt)
else:
value = value[keys[-1]]
worksheet_s.write(row, col, value, cell)
workbook.close()
xlsx_data = output.getvalue()
# xlsx_data contains the Excel file
return xlsx_data
def sqlExcel1():
global window, canvas, paper, filename, inImage, outImage, inW, inH, outW, outH
# save할 파일 결정
outfilename = asksaveasfile(parent=window, mode='wb',
defaultextension="*.xlsx", filetypes=(("xlsx파일", "*.xlsx"), ("모든파일", "*.*")))
wb = Workbook(outfilename)
ws = wb.add_worksheet(os.path.basename(filename))
with open(filename, 'rb') as fReader:
for i in range(inW):
for j in range(inH):
data = inImage[i][j] # 저장되어 있던 inImage에서 data 추출
ws.write(i, j, data) # index마다 쓰기
wb.close()
def __init__(self, f, name='Workbook', freeze_rows=1, auto_sizing=True):
from xlsxwriter import Workbook
self.stream = f
self.worksheet = None
self.workbook = Workbook(self.stream)
self.formats = {}
self.worksheet = None
if name:
self.new_worksheet(name=name)
self.freeze_rows = freeze_rows
self.auto_sizing = auto_sizing
def createWorkbook(output_dir, un):
''' Creates Excel workbook with sheet of
AGOL content for a user. Intended to be used
to create a slicer.'''
workbook = Workbook(output_dir)
sheet = workbook.add_worksheet('AGO Items-{}'.format(un))
sheet.write('A1', 'WebApp')
sheet.write('B1', 'WebMap')
sheet.write('C1', 'Feature Layer')
sheet.write('D1', 'Feature Service')
sheet.write('E1', 'Folder')
return (workbook, sheet)
def to_file(self, filename):
"""
:param filename: The filename of the workbook that you want to write data in.
"""
with Workbook(filename) as workbook:
worksheet = workbook.add_worksheet()
header = (u'书名', u'作者/译者', u'出版信息', u'评分', u'评价人数', u'简介')
worksheet.write_row(0, 0, header)
row = 1
for pagination in range(1, self.max_pagination + 1):
page = Page(self.tag, pagination)
for book in page.get_books():
worksheet.write_row(row, 0, book)
row += 1
def create_output_file():
# create output excel file
global book, sheet
try:
xlsx_path, ext = os.path.splitext(xmlfile)
book = Workbook(xlsx_path + '.xlsx')
sheet = book.add_worksheet()
except:
print("Error at create Excel file to output.\n")
time.sleep(5)
sys.exit()
return
def save_xlsx(sheet_mapping, destination):
destination = Path(destination).expanduser()
from xlsxwriter import Workbook
workbook = Workbook(str(destination))
for name, rows in sheet_mapping.items():
worksheet = workbook.add_worksheet(name)
for r, row in enumerate([rows.keys()] + rows):
for c, col in enumerate(row):
worksheet.write(r, c, col)
workbook.close()
def __init__(self, fout_xlsx, nt_flds, **kws):
# KEYWORDS FOR WRITING DATA:
self.vars = WrXlsxParams(nt_flds, **kws)
# Workbook
from xlsxwriter import Workbook
self.workbook = Workbook(fout_xlsx)
self.fmt_hdr = self.workbook.add_format(self.dflt_fmt_hdr)
self.fmt_txt = {
'plain': self.workbook.add_format(self.vars.fmt_txt_arg[0]),
'plain bold': self.workbook.add_format(self.vars.fmt_txt_arg[3]),
'very light grey':
self.workbook.add_format(self.vars.fmt_txt_arg[1]),
'light grey': self.workbook.add_format(self.vars.fmt_txt_arg[2])
}
def write(self, file: str, data: dict):
workbook = Workbook(file)
self.header_format = workbook.add_format({
'bold': True,
'align': 'center',
'bg_color': 'white'
})
for key, value in data.items():
sheet = workbook.add_worksheet(key)
row_count = self.write_header(sheet, value)
self.write_data_frames(sheet, value, row_count)
workbook.close()
def verified_excel_file(store, institute_list, temp_excel_dir):
"""Collect all verified variants in a list on institutes and save them to file
Args:
store(adapter.MongoAdapter)
institute_list(list): a list of institute ids
temp_excel_dir(os.Path): folder where the temp excel files are written to
Returns:
written_files(int): the number of files written to temp_excel_dir
"""
document_lines = []
written_files = 0
today = datetime.datetime.now().strftime("%Y-%m-%d")
LOG.info("Creating verified variant document..")
for cust in institute_list:
verif_vars = store.verified(institute_id=cust)
LOG.info("Found {} verified variants for customer {}".format(
len(verif_vars), cust))
if not verif_vars:
continue
unique_callers = set()
for var_type, var_callers in CALLERS.items():
for caller in var_callers:
unique_callers.add(caller.get("id"))
cust_verified = export_verified_variants(verif_vars, unique_callers)
document_name = ".".join([cust, "_verified_variants", today]) + ".xlsx"
workbook = Workbook(os.path.join(temp_excel_dir, document_name))
Report_Sheet = workbook.add_worksheet()
# Write the column header
row = 0
for col, field in enumerate(VERIFIED_VARIANTS_HEADER +
list(unique_callers)):
Report_Sheet.write(row, col, field)
# Write variant lines, after header (start at line 1)
for row, line in enumerate(
cust_verified, 1): # each line becomes a row in the document
for col, field in enumerate(
line): # each field in line becomes a cell
Report_Sheet.write(row, col, field)
workbook.close()
if os.path.exists(os.path.join(temp_excel_dir, document_name)):
written_files += 1
return written_files
def make_xls():
workbook = Workbook(WRITE_FILE[:-4] + '.xlsx')
worksheet = workbook.add_worksheet()
worksheet.set_column('A:B', 10)
worksheet.set_column('B:E', 18)
with codecs.open(WRITE_FILE, 'r', 'utf-8') as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
worksheet.write(r, c, col)
workbook.close()
print('Your file is ---{}---!'.format(WRITE_FILE))
print("Done!")
def convert_csv(filespath):
filespath = pathlib.Path(filespath)
for csvfile in glob.glob(os.path.join(filespath, '*.csv')):
print("converting csv file: " + str(csvfile))
workbook = Workbook(csvfile[:-4] + '.xlsx', {'constant_memory': True})
worksheet = workbook.add_worksheet()
with open(csvfile, 'rt', encoding='utf8') as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
worksheet.write(r, c, col)
workbook.close()
os.remove(csvfile)
return True
def write_runtime_to_file(system_result_dir, run_time, file_name):
experiment_file_name = join_path(system_result_dir, file_name)
if os.path.exists(experiment_file_name):
return
wb = Workbook(experiment_file_name)
sheet = wb.add_worksheet("run_time")
row = 0
for item in run_time.keys():
sheet.write(row, 0, item)
col = 1
for time in run_time[item]:
sheet.write(row, col, time)
col += 1
row += 1
wb.close()
def from_nested_iters(cls, data):
"""Builds an XlrdSheetData from nested iterables.
param data: is nested iterables representing data for each cell.
For example, `[('A1', 'B1'), ('A2', 'B2')]` represents two rows and two columns
of data.
"""
workbook = Workbook(BytesIO())
sheet = workbook.add_worksheet('test')
for row, row_data in enumerate(data):
for column, cell_data in enumerate(row_data):
sheet.write(row, column, cell_data)
return XlrdSheetData(xlsx_to_reader(workbook).sheet_by_index(0))
