def print_UP_list(UP_list, UP_meta_info, base):
from openpyxl.workbook import Workbook
from openpyxl.worksheet import Worksheet
filename = 'UP_print.xlsx'
wb = Workbook(encoding='mac_roman')
ws = Worksheet(wb, title = 'UP_list') #creating a sheet inside the workbook
ws.freeze_panes = 'A2'
header = ['#',
'UP name',
'unit',
'country',
'infrastructure']
for i in range(6):
header.append('Category ' + str(i))
ws.append(header)
for i in range(len(UP_list)):
UP = UP_list[i]
line = [i + base,
UP_list[i],
UP_meta_info[UP]['unit'],
UP_meta_info[UP]['Country'],
UP_meta_info[UP]['Infrastructure']]
for j in range(6):
try:
line.append(UP_meta_info[UP]['Category type'][j])
except IndexError:
break
ws.append(line)
print 'saving in excel sheet named: ' + filename
wb.add_sheet(ws)
wb.save(filename)
def print_UP_list(UP_list, UP_meta_info, base):
from openpyxl.workbook import Workbook
from openpyxl.worksheet import Worksheet
filename = 'UP_print.xlsx'
wb = Workbook(encoding='mac_roman')
ws = Worksheet(wb, title='UP_list') #creating a sheet inside the workbook
ws.freeze_panes = 'A2'
header = ['#', 'UP name', 'unit', 'country', 'infrastructure']
for i in range(6):
header.append('Category ' + str(i))
ws.append(header)
for i in range(len(UP_list)):
UP = UP_list[i]
line = [
i + base, UP_list[i], UP_meta_info[UP]['unit'],
UP_meta_info[UP]['Country'], UP_meta_info[UP]['Infrastructure']
]
for j in range(6):
try:
line.append(UP_meta_info[UP]['Category type'][j])
except IndexError:
break
ws.append(line)
print 'saving in excel sheet named: ' + filename
wb.add_sheet(ws)
wb.save(filename)
def print_CFs(CF_matrices, EF_list, CF_categories):
from openpyxl.workbook import Workbook
from openpyxl.worksheet import Worksheet
result_filename = 'CF_print.xlsx'
wb = Workbook() #creating a workbook
for method in CF_matrices:
ws = Worksheet(wb, title = method) #creating a sheet inside the workbook
ws.freeze_panes = 'D2'
header = ['compartment',
'substance',
'subcompartment']
for category in CF_categories[method]:
header.append(category)
ws.append(header)
for EF in EF_list:
matrix_column = EF_list.index(EF)
compartment, substance, subcompartment = EF
line = [compartment, substance, subcompartment]
for category in CF_categories[method]:
matrix_line = CF_categories[method].index(category)
CF = CF_matrices[method][matrix_line, matrix_column]
line.append(CF)
ws.append(line)
print 'saving in excel sheet named: ' + result_filename
wb.add_sheet(ws)
wb.save(result_filename)
def create_business_workbook(self): workbook = Workbook() for xlsx in self.business_xlsx: wb = openpyxl.load_workbook(xlsx) ws = wb.get_active_sheet() if re.search(r'NEW', xlsx): ws.title = 'New' elif re.search(r'Reopened', xlsx): ws.title = 'Reopened' elif re.search(r'As', xlsx): ws.title = 'As Designed' elif re.search(r'FIXED', xlsx): ws.title = 'Fixed' elif re.search(r'CANCELLED',xlsx): ws.title = 'Cancelled' elif re.search(r'CLOSED', xlsx): ws.title = 'Closed' elif re.search(r'UNR', xlsx): ws.title = 'Unreproducable' workbook.add_sheet(ws) self.business_workbook = workbook
def create_regular_workbook(self): workbook = Workbook() for xlsx in self.regular_xlsx: wb = openpyxl.load_workbook(xlsx) ws = wb.get_active_sheet() if re.search(r'Open\+bugs',xlsx): ws.title = 'Open' elif re.search(r'Resolved\+I', xlsx): ws.title = 'Resolved' elif re.search(r'Closed\+I', xlsx): ws.title = 'Closed' elif re.search(r'Open\+QC', xlsx): ws.title = 'Open QC' elif re.search(r'CANCELLED', xlsx): ws.title = 'Cancelled' elif re.search(r'Resolved\+QC', xlsx): ws.title = 'Resolved QC' elif re.search(r'Closed\+QC', xlsx): ws.title = 'Closed QC' workbook.add_sheet(ws) self.regular_workbook = workbook
def print_CFs(CF_matrices, EF_list, CF_categories):
from openpyxl.workbook import Workbook
from openpyxl.worksheet import Worksheet
result_filename = 'CF_print.xlsx'
wb = Workbook() #creating a workbook
for method in CF_matrices:
ws = Worksheet(wb, title=method) #creating a sheet inside the workbook
ws.freeze_panes = 'D2'
header = ['compartment', 'substance', 'subcompartment']
for category in CF_categories[method]:
header.append(category)
ws.append(header)
for EF in EF_list:
matrix_column = EF_list.index(EF)
compartment, substance, subcompartment = EF
line = [compartment, substance, subcompartment]
for category in CF_categories[method]:
matrix_line = CF_categories[method].index(category)
CF = CF_matrices[method][matrix_line, matrix_column]
line.append(CF)
ws.append(line)
print 'saving in excel sheet named: ' + result_filename
wb.add_sheet(ws)
wb.save(result_filename)
def print_EF_list(EF_list):
from openpyxl.workbook import Workbook
from openpyxl.worksheet import Worksheet
result_filename = 'EF_print.xlsx'
wb = Workbook() #creating a workbook
ws = Worksheet(wb, title='EF_list') #creating a sheet inside the workbook
ws.freeze_panes = 'A2'
header = ['compartment', 'substance', 'subcompartment']
ws.append(header)
for EF in EF_list:
ws.append(EF)
print 'saving in excel sheet named: ' + result_filename
wb.add_sheet(ws)
wb.save(result_filename)
def print_EF_list(EF_list):
from openpyxl.workbook import Workbook
from openpyxl.worksheet import Worksheet
result_filename = 'EF_print.xlsx'
wb = Workbook() #creating a workbook
ws = Worksheet(wb, title = 'EF_list') #creating a sheet inside the workbook
ws.freeze_panes = 'A2'
header = ['compartment',
'substance',
'subcompartment']
ws.append(header)
for EF in EF_list:
ws.append(EF)
print 'saving in excel sheet named: ' + result_filename
wb.add_sheet(ws)
wb.save(result_filename)
def print_mat(MAT,nom):
from openpyxl.workbook import Workbook
from openpyxl.worksheet import Worksheet
from numpy import array
import csv
import os
wb = Workbook(encoding = 'mac_roman') #creating a workbook
ws = Worksheet(wb, title = nom) #creating a sheet inside the workbook
mat=MAT
for row in range(len(mat)):
ws.append(mat[row].tolist());
wb.add_sheet(ws)
wb.save(nom+'.xlsx')
os.startfile(nom+".xlsx")
class ExcelWriter(object):
"""
Class for writing DataFrame objects into excel sheets, uses xlwt for xls,
openpyxl for xlsx. See DataFrame.to_excel for typical usage.
Parameters
----------
path : string
Path to xls file
"""
def __init__(self, path):
self.use_xlsx = True
if path.endswith('.xls'):
self.use_xlsx = False
import xlwt
self.book = xlwt.Workbook()
self.fm_datetime = xlwt.easyxf(num_format_str='YYYY-MM-DD HH:MM:SS')
self.fm_date = xlwt.easyxf(num_format_str='YYYY-MM-DD')
else:
from openpyxl.workbook import Workbook
self.book = Workbook(optimized_write = True)
self.path = path
self.sheets = {}
self.cur_sheet = None
def save(self):
"""
Save workbook to disk
"""
self.book.save(self.path)
def writerow(self, row, sheet_name=None):
"""
Write the given row into Excel an excel sheet
Parameters
----------
row : list
Row of data to save to Excel sheet
sheet_name : string, default None
Name of Excel sheet, if None, then use self.cur_sheet
"""
if sheet_name is None:
sheet_name = self.cur_sheet
if sheet_name is None: # pragma: no cover
raise Exception('Must pass explicit sheet_name or set '
'cur_sheet property')
if self.use_xlsx:
self._writerow_xlsx(row, sheet_name)
else:
self._writerow_xls(row, sheet_name)
def _writerow_xls(self, row, sheet_name):
if sheet_name in self.sheets:
sheet, row_idx = self.sheets[sheet_name]
else:
sheet = self.book.add_sheet(sheet_name)
row_idx = 0
sheetrow = sheet.row(row_idx)
for i, val in enumerate(row):
if isinstance(val, (datetime.datetime, datetime.date)):
if isinstance(val, datetime.datetime):
sheetrow.write(i,val, self.fm_datetime)
else:
sheetrow.write(i,val, self.fm_date)
elif isinstance(val, np.int64):
sheetrow.write(i,int(val))
elif isinstance(val, np.bool8):
sheetrow.write(i,bool(val))
else:
sheetrow.write(i,val)
row_idx += 1
if row_idx == 1000:
sheet.flush_row_data()
self.sheets[sheet_name] = (sheet, row_idx)
def _writerow_xlsx(self, row, sheet_name):
if sheet_name in self.sheets:
sheet, row_idx = self.sheets[sheet_name]
else:
sheet = self.book.create_sheet()
sheet.title = sheet_name
row_idx = 0
conv_row = []
for val in row:
if isinstance(val, np.int64):
val = int(val)
elif isinstance(val, np.bool8):
val = bool(val)
conv_row.append(val)
sheet.append(conv_row)
row_idx += 1
self.sheets[sheet_name] = (sheet, row_idx)
def test_add_correct_sheet():
wb = Workbook()
new_sheet = wb.create_sheet(0)
wb.add_sheet(new_sheet)
eq_(new_sheet, wb.worksheets[2])
def test_add_incorrect_sheet():
wb = Workbook()
wb.add_sheet("Test")
def test_add_invalid_worksheet_class_instance():
wb = Workbook()
ws = AlternativeWorksheet(parent_workbook=wb)
with pytest.raises(TypeError):
wb.add_sheet(worksheet=ws)
try:
# get workbook-level information
wb.properties = read_properties_core(archive.read(ARC_CORE))
try:
string_table = read_string_table(archive.read(ARC_SHARED_STRINGS))
except KeyError:
string_table = {}
style_table = read_style_table(archive.read(ARC_STYLE))
# get worksheets
wb.worksheets = [] # remove preset worksheet
sheet_names = read_sheets_titles(archive.read(ARC_APP))
for i, sheet_name in enumerate(sheet_names):
sheet_codename = 'sheet%d.xml' % (i + 1)
worksheet_path = '%s/%s' % (PACKAGE_WORKSHEETS, sheet_codename)
if not use_iterators:
new_ws = read_worksheet(archive.read(worksheet_path), wb, sheet_name, string_table, style_table)
else:
xml_source = unpack_worksheet(archive, worksheet_path)
new_ws = read_worksheet(xml_source, wb, sheet_name, string_table, style_table, filename, sheet_codename)
#new_ws = read_worksheet(archive.read(worksheet_path), wb, sheet_name, string_table, style_table, filename, sheet_codename)
wb.add_sheet(new_ws, index = i)
wb._named_ranges = read_named_ranges(archive.read(ARC_WORKBOOK), wb)
except KeyError, e:
raise InvalidFileException(unicode(e))
finally:
archive.close()
return wb
def test_add_invalid_worksheet_class_instance():
wb = Workbook()
ws = AlternativeWorksheet(parent_workbook=wb)
wb.add_sheet(worksheet=ws)
def write_results_disaggregation_UP(impact_method, full_results_UP, CF_categories, UP_meta_info, UP_list, all_system_scores,
all_unit_scores, level_reached, system_scores, system_number,systems):
from openpyxl.workbook import Workbook
from openpyxl.worksheet import Worksheet
from construct_path import construct_path
from copy import copy
filename = 'disaggregation_' + impact_method + '_system' + str(system_number) + '.xlsx'
wb = Workbook(encoding = 'mac_roman') #creating a workbook
ws = Worksheet(wb, title = 'result') #creating a sheet inside the workbook
#creation of headers
header2 = ['instance ID',
'parent instance ID',
'unit process',
'demand',
'unit',
'pruned',
'level',
'infrastructure']
for impact_category in CF_categories:
header2.append(impact_category)
for i in range(1, level_reached + 1):
header2.append('path level ' + str(i))
header2.append('country')
for i in range(4):
header2.append('category ' + str(i))
header1 = []
for i in range(header2.index(CF_categories[0])):
header1.append('')
for impact_category in CF_categories:
matrix_line = CF_categories.index(impact_category)
header1.append(str(system_scores[matrix_line,0]))
ws.append(header1) #writing the header
ws.append(header2) #writing the header
#content of the file
for instance_ID in range(len(full_results_UP)): #for each instance of the full_results
path = construct_path(full_results_UP, instance_ID, UP_list, impact_method, system_number) #construction of the path between the instance and the RF
level = len(path) - 1 #level will be incremented from zero
matrix_column = full_results_UP[instance_ID]['UP_number']
UP_name = UP_list[matrix_column]
demand = copy(full_results_UP[instance_ID]['demand'])
#fetching the info to fill the line
line = [instance_ID,
full_results_UP[instance_ID]['parent'],
UP_name,
demand,
UP_meta_info[UP_name]['unit'],
full_results_UP[instance_ID]['pruned'],
level,
UP_meta_info[UP_name]['Infrastructure']]
for impact_category in CF_categories: #system or unit scores.
matrix_line = CF_categories.index(impact_category)
if full_results_UP[instance_ID]['pruned'] == 1:
line.append(all_system_scores[matrix_line, matrix_column] * demand)
else:
line.append(all_unit_scores[matrix_line, matrix_column] * demand)
for i in range(1, level_reached + 1): #path
try:
line.append(path[i])
except IndexError:
line.append('')
#complementary info
line.append(UP_meta_info[UP_name]['Country'])
for i in range(4):
try:
line.append(UP_meta_info[UP_name]['Category type'][i])
except IndexError:
line.append('')
#print line
ws.append(line) #writing the header
ws.freeze_panes = 'D3'
wb.add_sheet(ws)
wb.save(filename)
class ExcelWriter(object):
"""
Class for writing DataFrame objects into excel sheets, uses xlwt for xls,
openpyxl for xlsx. See DataFrame.to_excel for typical usage.
Parameters
----------
path : string
Path to xls file
"""
def __init__(self, path):
self.use_xlsx = True
if path.endswith('.xls'):
self.use_xlsx = False
import xlwt
self.book = xlwt.Workbook()
self.fm_datetime = xlwt.easyxf(
num_format_str='YYYY-MM-DD HH:MM:SS')
self.fm_date = xlwt.easyxf(num_format_str='YYYY-MM-DD')
else:
from openpyxl.workbook import Workbook
self.book = Workbook(optimized_write=True)
self.path = path
self.sheets = {}
self.cur_sheet = None
def save(self):
"""
Save workbook to disk
"""
self.book.save(self.path)
def writerow(self, row, sheet_name=None):
"""
Write the given row into Excel an excel sheet
Parameters
----------
row : list
Row of data to save to Excel sheet
sheet_name : string, default None
Name of Excel sheet, if None, then use self.cur_sheet
"""
if sheet_name is None:
sheet_name = self.cur_sheet
if sheet_name is None: # pragma: no cover
raise Exception('Must pass explicit sheet_name or set '
'cur_sheet property')
if self.use_xlsx:
self._writerow_xlsx(row, sheet_name)
else:
self._writerow_xls(row, sheet_name)
def _writerow_xls(self, row, sheet_name):
if sheet_name in self.sheets:
sheet, row_idx = self.sheets[sheet_name]
else:
sheet = self.book.add_sheet(sheet_name)
row_idx = 0
sheetrow = sheet.row(row_idx)
for i, val in enumerate(row):
if isinstance(val, (datetime.datetime, datetime.date)):
if isinstance(val, datetime.datetime):
sheetrow.write(i, val, self.fm_datetime)
else:
sheetrow.write(i, val, self.fm_date)
elif isinstance(val, np.int64):
sheetrow.write(i, int(val))
elif isinstance(val, np.bool8):
sheetrow.write(i, bool(val))
else:
sheetrow.write(i, val)
row_idx += 1
if row_idx == 1000:
sheet.flush_row_data()
self.sheets[sheet_name] = (sheet, row_idx)
def _writerow_xlsx(self, row, sheet_name):
if sheet_name in self.sheets:
sheet, row_idx = self.sheets[sheet_name]
else:
sheet = self.book.create_sheet()
sheet.title = sheet_name
row_idx = 0
conv_row = []
for val in row:
if isinstance(val, np.int64):
val = int(val)
elif isinstance(val, np.bool8):
val = bool(val)
conv_row.append(val)
sheet.append(conv_row)
row_idx += 1
self.sheets[sheet_name] = (sheet, row_idx)
def test_add_correct_sheet():
wb = Workbook()
new_sheet = wb.create_sheet(0)
wb.add_sheet(new_sheet)
assert new_sheet == wb.worksheets[2]
class ExcelWriter(object):
"""
Class for writing DataFrame objects into excel sheets, uses xlwt for xls,
openpyxl for xlsx. See DataFrame.to_excel for typical usage.
Parameters
----------
path : string
Path to xls file
"""
def __init__(self, path):
self.use_xlsx = True
if path.endswith('.xls'):
self.use_xlsx = False
import xlwt
self.book = xlwt.Workbook()
self.fm_datetime = xlwt.easyxf(
num_format_str='YYYY-MM-DD HH:MM:SS')
self.fm_date = xlwt.easyxf(num_format_str='YYYY-MM-DD')
else:
from openpyxl.workbook import Workbook
self.book = Workbook() # optimized_write=True)
# open pyxl 1.6.1 adds a dummy sheet remove it
if self.book.worksheets:
self.book.remove_sheet(self.book.worksheets[0])
self.path = path
self.sheets = {}
self.cur_sheet = None
def save(self):
"""
Save workbook to disk
"""
self.book.save(self.path)
def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):
"""
Write given formated cells into Excel an excel sheet
Parameters
----------
cells : generator
cell of formated data to save to Excel sheet
sheet_name : string, default None
Name of Excel sheet, if None, then use self.cur_sheet
startrow: upper left cell row to dump data frame
startcol: upper left cell column to dump data frame
"""
if sheet_name is None:
sheet_name = self.cur_sheet
if sheet_name is None: # pragma: no cover
raise Exception('Must pass explicit sheet_name or set '
'cur_sheet property')
if self.use_xlsx:
self._writecells_xlsx(cells, sheet_name, startrow, startcol)
else:
self._writecells_xls(cells, sheet_name, startrow, startcol)
def _writecells_xlsx(self, cells, sheet_name, startrow, startcol):
from openpyxl.cell import get_column_letter
if sheet_name in self.sheets:
wks = self.sheets[sheet_name]
else:
wks = self.book.create_sheet()
wks.title = sheet_name
self.sheets[sheet_name] = wks
for cell in cells:
colletter = get_column_letter(startcol + cell.col + 1)
xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1))
xcell.value = _conv_value(cell.val)
if cell.style:
style = CellStyleConverter.to_xlsx(cell.style)
for field in style.__fields__:
xcell.style.__setattr__(field,
style.__getattribute__(field))
if isinstance(cell.val, datetime.datetime):
xcell.style.number_format.format_code = "YYYY-MM-DD HH:MM:SS"
elif isinstance(cell.val, datetime.date):
xcell.style.number_format.format_code = "YYYY-MM-DD"
# merging requires openpyxl latest (works on 1.6.1)
# todo add version check
if cell.mergestart is not None and cell.mergeend is not None:
cletterstart = get_column_letter(startcol + cell.col + 1)
cletterend = get_column_letter(startcol + cell.mergeend + 1)
wks.merge_cells('%s%s:%s%s' % (cletterstart,
startrow + cell.row + 1,
cletterend,
startrow + cell.mergestart + 1))
def _writecells_xls(self, cells, sheet_name, startrow, startcol):
if sheet_name in self.sheets:
wks = self.sheets[sheet_name]
else:
wks = self.book.add_sheet(sheet_name)
self.sheets[sheet_name] = wks
style_dict = {}
for cell in cells:
val = _conv_value(cell.val)
num_format_str = None
if isinstance(cell.val, datetime.datetime):
num_format_str = "YYYY-MM-DD HH:MM:SS"
if isinstance(cell.val, datetime.date):
num_format_str = "YYYY-MM-DD"
stylekey = json.dumps(cell.style)
if num_format_str:
stylekey += num_format_str
if stylekey in style_dict:
style = style_dict[stylekey]
else:
style = CellStyleConverter.to_xls(cell.style, num_format_str)
style_dict[stylekey] = style
if cell.mergestart is not None and cell.mergeend is not None:
wks.write_merge(startrow + cell.row,
startrow + cell.mergestart,
startcol + cell.col,
startcol + cell.mergeend,
val, style)
else:
wks.write(startrow + cell.row,
startcol + cell.col,
val, style)
def test_add_incorrect_sheet():
wb = Workbook()
with pytest.raises(TypeError):
wb.add_sheet("Test")
def print_mat_desag(MATA, MATB,incertitude, UP_list_desag,EF_list):
from openpyxl.workbook import Workbook
from openpyxl.worksheet import Worksheet
from copy import copy
from time import time
from scipy.sparse import lil_matrix, find
filename = 'MAT.xlsx'
wb = Workbook(encoding = 'mac_roman')
Ws=wb.active
wb.remove_sheet(Ws)
#creation de la matrice A avec sa matrice d'incertitude
Ws = Worksheet(wb, title = 'Processus')
for UP in UP_list_desag:
line=[UP,UP_list_desag[UP]['origine']]
line.append(UP_list_desag[UP]['process'])
line.append(str(UP_list_desag[UP]['enfants']))
Ws.append(line)
wb.add_sheet(Ws)
Ws = Worksheet(wb, title = 'A')
Ws.freeze_panes = 'B2'
mat=MATA.toarray()
header = ['A']
for UP in UP_list_desag:
header.append(UP_list_desag[UP]['process'])
Ws.append(header)
for row in range(len(mat)):
line=[row]
for col in range(len(mat[row])):
line.append(mat[row][col])
Ws.append(line)
wb.add_sheet(Ws)
Ws = Worksheet(wb, title = 'incertitude A')
Ws.freeze_panes = 'B2'
mat=incertitude['technology']
header = ['incertitude A']
for UP in UP_list_desag:
header.append(UP_list_desag[UP]['process'])
Ws.append(header)
for row in range(len(mat)):
line=[row]
for col in range(len(mat)):
line.append(mat[col][row])
Ws.append(line)
wb.add_sheet(Ws)
#Creation de B avec sa matrice d'incertitude
Ws = Worksheet(wb, title = 'Flux elem')
Ws.freeze_panes = 'A2'
header = ['id','compartment',
'substance',
'subcompartment']
Ws.append(header)
for EF in EF_list:
L=[EF_list.index(EF)]
L.append(EF)
Ws.append(L)
wb.add_sheet(Ws)
Ws = Worksheet(wb, title = 'B')
Ws.freeze_panes = 'B2'
mat=MATB.toarray()
header = ['B']
for UP in UP_list_desag:
header.append(UP_list_desag[UP]['process'])
Ws.append(header)
for row in range(len(mat)):
line=[row]
for col in range(len(mat[row])):
line.append(mat[row][col])
Ws.append(line)
wb.add_sheet(Ws)
Ws = Worksheet(wb, title = 'incertitude B')
Ws.freeze_panes = 'B2'
mat=incertitude['intervention']
header = ['incertitude B']
for UP in UP_list_desag:
header.append(UP_list_desag[UP]['process'])
Ws.append(header)
for row in range(len(mat[0])):
line=[row]
for col in range(len(mat)):
line.append(mat[col][row])
Ws.append(line)
wb.add_sheet(Ws)
wb.save(filename)
print 'fichier enregistre : MAT.xlsx'
def test_add_invalid_worksheet_class_instance():
wb = Workbook()
ws = AlternativeWorksheet(parent_workbook=wb)
wb.add_sheet(worksheet=ws)
def test_add_correct_sheet():
wb = Workbook()
new_sheet = wb.create_sheet(0)
wb.add_sheet(new_sheet)
eq_(new_sheet, wb.worksheets[2])
def test_add_incorrect_sheet():
wb = Workbook()
wb.add_sheet("Test")
def test_add_invalid_worksheet_class_instance():
wb = Workbook()
ws = AlternativeWorksheet(parent_workbook=wb)
with pytest.raises(TypeError):
wb.add_sheet(worksheet=ws)
try:
string_table = read_string_table(archive.read(ARC_SHARED_STRINGS))
except KeyError:
string_table = {}
style_table = read_style_table(archive.read(ARC_STYLE))
# get worksheets
wb.worksheets = [] # remove preset worksheet
sheet_names = read_sheets_titles(archive.read(ARC_APP))
for i, sheet_name in enumerate(sheet_names):
sheet_codename = 'sheet%d.xml' % (i + 1)
worksheet_path = '%s/%s' % (PACKAGE_WORKSHEETS, sheet_codename)
if not use_iterators:
new_ws = read_worksheet(archive.read(worksheet_path), wb,
sheet_name, string_table, style_table)
else:
xml_source = unpack_worksheet(archive, worksheet_path)
new_ws = read_worksheet(xml_source, wb, sheet_name,
string_table, style_table, filename,
sheet_codename)
#new_ws = read_worksheet(archive.read(worksheet_path), wb, sheet_name, string_table, style_table, filename, sheet_codename)
wb.add_sheet(new_ws, index=i)
wb._named_ranges = read_named_ranges(archive.read(ARC_WORKBOOK), wb)
except KeyError, e:
raise InvalidFileException(unicode(e))
finally:
archive.close()
return wb
def test_add_incorrect_sheet():
wb = Workbook()
with pytest.raises(TypeError):
wb.add_sheet("Test")
def test_add_correct_sheet():
wb = Workbook()
new_sheet = wb.create_sheet(0)
wb.add_sheet(new_sheet)
assert new_sheet == wb.worksheets[2]
class ExcelWriter(object):
"""
Class for writing DataFrame objects into excel sheets, uses xlwt for xls,
openpyxl for xlsx. See DataFrame.to_excel for typical usage.
Parameters
----------
path : string
Path to xls file
"""
def __init__(self, path):
self.use_xlsx = True
if path.endswith('.xls'):
self.use_xlsx = False
import xlwt
self.book = xlwt.Workbook()
self.fm_datetime = xlwt.easyxf(
num_format_str='YYYY-MM-DD HH:MM:SS')
self.fm_date = xlwt.easyxf(num_format_str='YYYY-MM-DD')
else:
from openpyxl.workbook import Workbook
self.book = Workbook() # optimized_write=True)
# open pyxl 1.6.1 adds a dummy sheet remove it
if self.book.worksheets:
self.book.remove_sheet(self.book.worksheets[0])
self.path = path
self.sheets = {}
self.cur_sheet = None
def save(self):
"""
Save workbook to disk
"""
self.book.save(self.path)
def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):
"""
Write given formated cells into Excel an excel sheet
Parameters
----------
cells : generator
cell of formated data to save to Excel sheet
sheet_name : string, default None
Name of Excel sheet, if None, then use self.cur_sheet
startrow: upper left cell row to dump data frame
startcol: upper left cell column to dump data frame
"""
if sheet_name is None:
sheet_name = self.cur_sheet
if sheet_name is None: # pragma: no cover
raise Exception('Must pass explicit sheet_name or set '
'cur_sheet property')
if self.use_xlsx:
self._writecells_xlsx(cells, sheet_name, startrow, startcol)
else:
self._writecells_xls(cells, sheet_name, startrow, startcol)
def _writecells_xlsx(self, cells, sheet_name, startrow, startcol):
from openpyxl.cell import get_column_letter
if sheet_name in self.sheets:
wks = self.sheets[sheet_name]
else:
wks = self.book.create_sheet()
wks.title = sheet_name
self.sheets[sheet_name] = wks
for cell in cells:
colletter = get_column_letter(startcol + cell.col + 1)
xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1))
xcell.value = _conv_value(cell.val)
if cell.style:
style = CellStyleConverter.to_xlsx(cell.style)
for field in style.__fields__:
xcell.style.__setattr__(field,
style.__getattribute__(field))
if isinstance(cell.val, datetime.datetime):
xcell.style.number_format.format_code = "YYYY-MM-DD HH:MM:SS"
elif isinstance(cell.val, datetime.date):
xcell.style.number_format.format_code = "YYYY-MM-DD"
# merging requires openpyxl latest (works on 1.6.1)
# todo add version check
if cell.mergestart is not None and cell.mergeend is not None:
cletterstart = get_column_letter(startcol + cell.col + 1)
cletterend = get_column_letter(startcol + cell.mergeend + 1)
wks.merge_cells('%s%s:%s%s' % (cletterstart,
startrow + cell.row + 1,
cletterend,
startrow + cell.mergestart + 1))
def _writecells_xls(self, cells, sheet_name, startrow, startcol):
if sheet_name in self.sheets:
wks = self.sheets[sheet_name]
else:
wks = self.book.add_sheet(sheet_name)
self.sheets[sheet_name] = wks
style_dict = {}
for cell in cells:
val = _conv_value(cell.val)
num_format_str = None
if isinstance(cell.val, datetime.datetime):
num_format_str = "YYYY-MM-DD HH:MM:SS"
if isinstance(cell.val, datetime.date):
num_format_str = "YYYY-MM-DD"
stylekey = json.dumps(cell.style)
if num_format_str:
stylekey += num_format_str
if stylekey in style_dict:
style = style_dict[stylekey]
else:
style = CellStyleConverter.to_xls(cell.style, num_format_str)
style_dict[stylekey] = style
if cell.mergestart is not None and cell.mergeend is not None:
wks.write_merge(startrow + cell.row,
startrow + cell.mergestart,
startcol + cell.col,
startcol + cell.mergeend,
val, style)
else:
wks.write(startrow + cell.row,
startcol + cell.col,
val, style)
def print_mat_desag(MATA, MATB, incertitude, UP_list_desag, EF_list):
from openpyxl.workbook import Workbook
from openpyxl.worksheet import Worksheet
from copy import copy
from time import time
from scipy.sparse import lil_matrix, find
filename = "MAT.xlsx"
wb = Workbook(encoding="mac_roman")
Ws = wb.active
wb.remove_sheet(Ws)
# creation de la matrice A avec sa matrice d'incertitude
Ws = Worksheet(wb, title="Processus")
for UP in UP_list_desag:
line = [UP, UP_list_desag[UP]["origine"]]
line.append(UP_list_desag[UP]["process"])
line.append(str(UP_list_desag[UP]["enfants"]))
Ws.append(line)
wb.add_sheet(Ws)
Ws = Worksheet(wb, title="A")
Ws.freeze_panes = "B2"
mat = MATA.toarray()
header = ["A"]
for UP in UP_list_desag:
header.append(UP_list_desag[UP]["process"])
Ws.append(header)
for row in range(len(mat)):
line = [row]
for col in range(len(mat[row])):
line.append(mat[row][col])
Ws.append(line)
wb.add_sheet(Ws)
Ws = Worksheet(wb, title="incertitude A")
Ws.freeze_panes = "B2"
mat = incertitude["technology"]
header = ["incertitude A"]
for UP in UP_list_desag:
header.append(UP_list_desag[UP]["process"])
Ws.append(header)
for row in range(len(mat)):
line = [row]
for col in range(len(mat)):
line.append(mat[col][row])
Ws.append(line)
wb.add_sheet(Ws)
# Creation de B avec sa matrice d'incertitude
Ws = Worksheet(wb, title="Flux elem")
Ws.freeze_panes = "A2"
header = ["id", "compartment", "substance", "subcompartment"]
Ws.append(header)
for EF in EF_list:
L = [EF_list.index(EF)]
L.append(EF)
Ws.append(L)
wb.add_sheet(Ws)
Ws = Worksheet(wb, title="B")
Ws.freeze_panes = "B2"
mat = MATB.toarray()
header = ["B"]
for UP in UP_list_desag:
header.append(UP_list_desag[UP]["process"])
Ws.append(header)
for row in range(len(mat)):
line = [row]
for col in range(len(mat[row])):
line.append(mat[row][col])
Ws.append(line)
wb.add_sheet(Ws)
Ws = Worksheet(wb, title="incertitude B")
Ws.freeze_panes = "B2"
mat = incertitude["intervention"]
header = ["incertitude B"]
for UP in UP_list_desag:
header.append(UP_list_desag[UP]["process"])
Ws.append(header)
for row in range(len(mat[0])):
line = [row]
for col in range(len(mat)):
line.append(mat[col][row])
Ws.append(line)
wb.add_sheet(Ws)
wb.save(filename)
print "fichier enregistre : MAT.xlsx"
