def DFtoExcel(df, FolderName, FileName):
write_df = df.loc[:, ["FileName", "hyperlink", "Sheet Name"]]
# Path Cell_Search_By_Key
MainFolder = "C:\\Cell_Search_By_Key"
FolderPath = os.path.join(MainFolder, FolderName)
if not os.path.exists(FolderPath):
os.makedirs(FolderPath)
os.chdir(FolderPath)
ExcelName = "%s.xlsx" % FileName
writer = ExcelWriter(ExcelName)
write_df.to_excel(writer, "Result", index=False)
writer.save()
# turn path into hyperlink
Excel_Path = os.path.join(FolderPath, ExcelName)
wb = Workbook(Excel_Path)
# wb = Workbook.caller()
checkArr = Range("B2").vertical.value
i = 2
for check in checkArr:
RangeName = "B%d" % (i)
displayRange = "A%d" % (i)
address = Range(RangeName).value
display_name = Range(displayRange).value
i += 1
try:
Range(RangeName).add_hyperlink(address, text_to_display=address)
except:
pass
wb.save()
wb.close()
return "FINISH"
def tableau_data():
Workbook.caller()
save_path = str(dr_pivot_path())
save_path = save_path[:save_path.rindex('\\')]
ddr_data = dr.raw_pivot()
d = dr.tableau_campaign_data(ddr_data)
s = search.merge_data()
tableau = d.append(s)
tableau['Quarter'] = qquarter()
if Range('merged', 'A1').value is None:
chunk_df(tableau, 'merged', 'A1')
# If data is already present in the tab, the two data sets are merged together and then copied into the data tab.
else:
past_data = pd.read_excel(dr_pacing_path(), 'merged', index_col=None)
past_data = past_data[past_data['Campaign'] != 'Search']
appended_data = past_data.append(tableau)
Sheet('merged').clear()
chunk_df(appended_data, 'merged', 'A1')
#Range('Sheet3', 'AT1').value = pd.to_datetime(ddr_data['Date'].max()) + datetime.timedelta(days= 1)
wb = Workbook()
Sheet('Sheet1').name = 'DDR Data'
chunk_df(ddr_data, 'DDR Data', 'A1')
wb.save(save_path + '\\' + 'DR_Raw_Data.xlsx')
wb.close()
def write_array_to_xl_using_xlwings(ar, file, sheet):
# Note: if file is opened In Excel, it must be first saved before writing
# new output to it, but it may be left open in Excel application.
wb = Workbook(file)
Sheet(sheet).activate()
Range(sheet, 'A1').value = ar.astype(str)
wb.save()
def months_stat(self, q_months, year):
q_df = self.period_calc(q_months, year)
sum_q = self.period_stat(q_months, year)
wb = Workbook()
sh = Sheet.add("Summary", wkb = wb)
row_flag = write_to_excel(q_df, sh = sh)
row_flag = write_to_excel(sum_q, sh = sh, row_flag = row_flag)
sh = Sheet.add("Master", wkb = wb)
row_flag = write_to_excel(self.active_on_the_day(t_month_end(q_months[-1], year)) .data.pipe(ready_excel),
sh = sh)
sh1 = Sheet.add("Aggregate", wkb = wb)
row_flag = write_to_excel('New Leases During the Period', sh = sh1)
new_leases_list = self.new_analysis(t_month_start(q_months[0], year), t_month_end(q_months[-1], year)) .data.pipe(ready_excel)
row_flag = write_to_excel(new_leases_list, sh = sh1, row_flag = row_flag)
row_flag = write_to_excel('Expired During the Period', sh = sh1, row_flag = row_flag)
expired_leases_list = self.old_analysis(t_month_start(q_months[0], year), t_month_end(q_months[-1], year)) .data.pipe(ready_excel)
row_flag = write_to_excel(expired_leases_list, sh = sh1, row_flag = row_flag)
r_expired_leases_list, r_new_leases_list, period_rate = self.renewal_a(q_months, year)
sh1 = Sheet.add("Renewal", wkb = wb)
row_flag = write_to_excel('Renewed Leases During the Period', sh = sh1)
row_flag = write_to_excel('Original Leases', sh = sh1, row_flag = row_flag)
row_flag = write_to_excel(r_expired_leases_list.pipe(ready_excel), sh = sh1, row_flag = row_flag)
row_flag = write_to_excel('Renewed Leases', sh = sh1, row_flag = row_flag)
row_flag = write_to_excel(r_new_leases_list.pipe(ready_excel), sh = sh1, row_flag = row_flag)
row_flag = write_to_excel('Weighted Average Reversion Rate', sh = sh1, row_flag = row_flag)
row_flag = write_to_excel(period_rate, sh = sh1, row_flag = row_flag)
quarter = q_months[-1]//3
for tower in range(1,3):
sh_new = Sheet.add("Tower {tower} {year} Q{quarter}".format(tower = tower, year = year, quarter = quarter), wkb = wb)
row_flag = write_to_excel('Tower {tower} New Leases During the Period'.format(tower = tower), sh = sh_new)
new_leases_list_T = new_leases_list.loc[new_leases_list['BLDG'] == tower].copy()
row_flag = write_to_excel(new_leases_list_T, sh = sh_new, row_flag = row_flag)
row_flag = write_to_excel('Tower {tower} Expired Leases During the Period'.format(tower = tower), sh = sh_new, row_flag = row_flag)
expired_leases_list_T = expired_leases_list.loc[expired_leases_list['BLDG'] == tower].copy()
row_flag = write_to_excel(expired_leases_list_T, sh = sh_new, row_flag = row_flag)
Sheet('Sheet1').delete()
wb.save("Operating Statistics Q{quarter} {year}".format(quarter = quarter, year = year))
#wb.close()
return "OK"
def write_array_to_sheet(filepath, sheet, arr):
path = _fullpath(filepath) # Workbook(path) seems to fail unless full path is provided
if os.path.exists(path):
wb = Workbook(path)
Sheet(sheet).activate()
Range("A1").value = arr
wb.save()
else:
raise FileNotFound(path)
def savexlsMethod(self):
print('Saving excel File')
self.saveNameExcel = os.path.splitext(str(self.filepath).split("/")[-1])[0]
wbOut = Workbook()
Range('A1').value = ['Tempature [C]','Relative Humidity [%]','Dew Point [C]']
Range('A2').value = self.data
wbOut.save()
def write_array_to_xl_using_xlwings(ar, file, sheet):
# Note: if file is opened In Excel, it must be first saved before writing
# new output to it, but it may be left open in Excel application.
wb = Workbook(file)
Sheet(sheet).activate()
def nan_to_empty_str(x):
return '' if type(x) == float and np.isnan(x) else x
Range(sheet, 'A1').value = [[nan_to_empty_str(x) for x in row] for row in ar]
wb.save()
def saveFile(self, accountManageFileName):
"""
Save the destination file.
"""
from xlwings import Workbook, Range
wb = Workbook(accountManageFileName)
for placeToWrite in self.infoToSave:
sheet = placeToWrite[0]
cell = placeToWrite[1]
data = self.infoToSave[placeToWrite]
Range(sheet, cell).value = data
wb.save(accountManageFileName)
def output_forecasts(pacing_data):
pacing_data['Week'] = pacing_data['Date'].apply(lambda x: main.monday_week_start(x))
pacing_data = pd.pivot_table(pacing_data, index= ['Site', 'Tactic', 'Metric'],
columns= ['Week'], values= 'value', aggfunc= np.sum).reset_index()
wb = Workbook(main.dr_pacing_path())
Sheet('forecast_data').clear_contents()
Range('forecast_data', 'A1', index= False).value = pacing_data
wb.save()
wb.close()
def xlo(df, filename=None):
""" show pandas dataframe or series in excel sheet
uses xlwings which allows writing to open file
"""
if not filename:
filename = "_temp.xlsx"
if not os.path.isfile(filename):
wb = Workbook()
Sheet("Sheet2").delete()
Sheet("Sheet3").delete()
else:
wb = Workbook(filename)
Sheet.add()
Range("A1").value = df
wb.save(filename)
def test_save_path(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, 'TestFile.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save(target_file_path)
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False, app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_save_path(self):
cwd = os.getcwd()
wb1 = Workbook()
target_file_path = os.path.join(cwd, 'TestFile.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save(target_file_path)
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_save_path(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, 'TestFile.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save(target_file_path)
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False, app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_save_naked(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False)
target_file_path = os.path.join(cwd, wb1.name + '.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save()
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_save_naked(self):
if sys.platform.startswith('darwin'):
os.chdir(os.path.expanduser("~") + '/Library/Containers/com.microsoft.Excel/Data/')
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, wb1.name + '.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save()
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False, app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_save_path(self):
if sys.platform.startswith('darwin'):
folder = os.path.expanduser("~") + '/Library/Containers/com.microsoft.Excel/Data/'
if os.path.isdir(folder):
os.chdir(folder)
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, 'TestFile.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save(target_file_path)
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False, app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def run_concatenation(directory=None):
import re, os
from xlwings import Workbook, Range, Application
wb = Workbook(app_visible=False)
lastRow = 3
newlastRow = lastRow
lastColumn = 6
if directory is None:
directory = os.getcwd()
for file in os.listdir(directory):
findling = re.search(r'.*Data.*[0-9]{2,3}.*.xls$', file)
if findling:
wb2 = Workbook(os.path.abspath(findling.group()), \
app_visible=False)
addedRows = Range((4, 1), wkb = wb2).table.last_cell.row - 3
newlastRow = lastRow + addedRows
Range((lastRow+1, lastColumn+1), wkb = wb).value = \
findling.group()
Range((lastRow+1, 1), wkb = wb).table.value = \
Range((4, 1), (newlastRow, lastColumn), wkb = wb2).value
lastRow = newlastRow
wb2.close()
wb.save(os.path.join(os.getcwd(), 'Output.xlsx'))
wb.close()
def publishers(dr):
cd, t2t, fbx, search, pros, aal = data_transform.dr_placement_types()
week = week_of(dr)
# Publisher Performance
pub_dr = dr[(dr['Campaign'] == 'DR') & (dr['Date'] >= main.quarter_start())]
pub_dr = pub_dr.groupby(['Site', 'Placement Messaging Type', 'Week', 'Date'])
pub_dr = pd.DataFrame(pub_dr.sum()).reset_index()
#pub_dr = pub_dr[(pub_dr['NTC Media Cost'] != 0)]
pub_dr['Tactic'] = np.where((pub_dr['Placement Messaging Type'].str.contains(cd) == True) &
(pub_dr['Placement Messaging Type'].str.contains(fbx) != True), 'C/D Remessaging',
np.where(pub_dr['Placement Messaging Type'].str.contains(fbx) == True,
'FBX Remessaging', pub_dr['Placement Messaging Type']))
# Quarter
q_dr = pub_dr[pub_dr['Date'] >= main.quarter_start()]
q_dr = q_dr.groupby(['Site', 'Tactic'])
q_dr = pd.DataFrame(q_dr.sum()).reset_index()
q_dr = goals(q_dr)
# Last Week
last_week = pub_dr[pub_dr['Week'] == pub_dr['Week'].max()]
last_week = last_week.groupby(['Site', 'Tactic'])
last_week = pd.DataFrame(last_week.sum()).reset_index()
last_week.rename(columns={'NET Media Cost': week}, inplace= True)
# Publishers Overall
sites = q_dr.groupby('Site')
sites = pd.DataFrame(sites.sum()).reset_index()
sites['CPGA'] = sites['NET Media Cost'] / sites['Total GAs']
# Brand Remessaging
br = dr[dr['Campaign'] == 'Brand Remessaging']
br_quarter = br[br['Date'] >= main.quarter_start()]
br_lw = br[br['Week'] == br['Week'].max()]
br_quarter = br_quarter.groupby('Site')
br_quarter = pd.DataFrame(br_quarter.sum().reset_index())
br_quarter['Traffic Yield'] = br_quarter['Total Traffic Actions'].astype(float) / \
br_quarter['Impressions'].astype(float)
br_lw = br_lw.groupby('Site')
br_lw = pd.DataFrame(br_lw.sum().reset_index())
pacing_wb = Workbook(main.dr_pacing_path())
pacing_wb.set_current()
Range('Publisher Performance', 'A13', index=False, header=False).value = q_dr[
['Site', 'Tactic', 'Total GAs']]
Range('Publisher Performance', 'E13', index= False, header= False).value = q_dr['NET Media Cost']
Range('Publisher Performance', 'D13', index= False, header= False).value = last_week[week]
Range('Publisher Performance', 'D12').value = week
Range('Publisher Performance', 'A32', index= False, header= False).value = sites[['Site', 'Total GAs', 'CPGA']]
Range('Publisher Performance', 'C7').value = pub_dr['Week'].max().strftime('%m/%d/%Y')
Range('Publisher Performance', 'B45', index= False, header= False).value = \
br_quarter[['Traffic Yield', 'Impressions', 'Total Traffic Actions']]
Range('Publisher Performance', 'G45', index= False, header= False).value = br_quarter['NET Media Cost']
Range('Publisher Performance', 'F45', index= False, header= False).value = br_lw['NET Media Cost']
Range('Publisher Performance', 'F44').value = week
pacing_wb.save()
pacing_wb.close()
Sheet.add('abc',after=lastSheetVal)
Sheet('abc').name=NewSheetName
#Paste Data
Range(RangeLimit).value=TempData
#hypelink
for hyperRow in xrange(2,AllRRow):
RangeName=('B%d' % (hyperRow))
address=Range(RangeName).value
try:
Range(RangeName).add_hyperlink(address, text_to_display=address)
except:
pass
wbTarget.save()
wb.close()
wbTarget.close()
except:
#No Main Excel Exist, Create New One
Excel_Path = os.path.join(CWPath, 'Result-Output.xlsx')
wb = Workbook(Excel_Path)
#rename worksheet
Sheet('Result',wkb=wb).name=NewSheetName
wb.save(TargetPath)
wb.close()
pass
import re
import time
import shutil
import codecs
import subprocess
import pexpect
import sys
from xlwings import Workbook, Sheet, Range, Chart
# from PDFWriter import PDFWriter
graded_folder_path = "/Users/evermal/Downloads/COMP248-Q-2"
student_id_regex = '.*([0-9]{8}).*'
# Create a connection with a new workbook.
template_workbook = Workbook('/Users/evermal/Documents/COMP248-Assigment2/examples/COMP248_Q_F2015_GradeSheet for A2.xlsx')
template_workbook.save('/Users/evermal/Documents/COMP248-Assigment2/examples/newCOMP248_Q_F2015_GradeSheet for A2.xlsx')
template_workbook.close()
# open nem workbook to work with
professors_workbook = Workbook('/Users/evermal/Documents/COMP248-Assigment2/examples/newCOMP248_Q_F2015_GradeSheet for A2.xlsx')
# go through each line of the table
for x in xrange(7,101):
# set the profesors spreedsheet as current workbook
professors_workbook.set_current()
student_id_cell = str(Range('B'+str(x)).value)
if student_id_cell is not None:
student_id_matcher = re.match(student_id_regex, student_id_cell)
if student_id_matcher is not None:
student_id = student_id_matcher.group(1)
print student_id
# create feedback workbook
student_id_matcher = re.match(student_id_regex, absolute_file_name)
student_id = student_id_matcher.group(1)
template_workbook = Workbook(template_feedback_workbook_path)
Range('B4').value = student_id
Range('B15').value = personalized_message
Range('B16').value = height_validation + width_validation
Range('B17').value = drawing_of_the_house_roof
Range('B18').value = drawing_of_the_house_body
Range('B19').value = repetition_process
Range('B20').value = extra_point
Range('D15').value = personalized_message_comment
Range('D16').value = height_and_width_comment
Range('D17').value = roof_comment
Range('D18').value = body_comment
Range('D19').value = comment_5
Range('D20').value = extra_point_comment
student_feedback_workbook_path = root+'/'+student_id+'.xlsx'
template_workbook.save(student_feedback_workbook_path)
student_workbook = Workbook(student_feedback_workbook_path)
template_workbook.close()
subprocess.call(["subl", absolute_file_name])
success = raw_input('success?')
if success == 'y':
student_workbook.close()
class ExcelTemplateRenderer(object):
"""
A class to render an Excel template using data stored
in a database and given a specification file.
Parameters
----------
db_engine : object
the :class:`sqlalchemy.engine.Engine` object used to
connect and query the database.
template_name : str
path to the `xlsx` template file.
spec_filename : str
path to the specification file (yaml format).
output_dirname : str
path to the directory where to store the
generated excel file(s) (i.e., the rendered template(s)).
jinja_env : object or None
allow to provide a :class:`jinja2.Environment` object,
helpful when using custom filters in the specification file
(optional).
Notes
-----
The specification file (yaml format) consists of a list
of render blocks. TODO: documentation on the format.
"""
def __init__(self, db_engine, template_name,
spec_filename, output_dirname, jinja_env=None):
self.db_engine = db_engine
self.template_name = template_name
self.spec_filename = spec_filename
self.output_dirname = os.path.abspath(output_dirname)
if jinja_env is None:
self.jinja_env = jinja2.Environment()
else:
self.jinja_env = jinja_env
with open(self.spec_filename, 'r', encoding='utf-8') as f:
self.render_blocks = yaml.load(f)
os.makedirs(self.output_dirname, exist_ok=True)
logger.info("output directory is %s", self.output_dirname)
def open_template_as_current_wkb(self):
self.wkb = Workbook(
os.path.abspath(self.template_name),
app_visible=False
)
self.wkb.set_current()
def save_current_wkb(self, filename):
filepath = os.path.join(self.output_dirname, filename)
self.wkb.save(filepath)
logger.info("created %s", filepath)
def save_current_wkb_as_pdf(self, filename, worksheet_name):
filepath = os.path.join(self.output_dirname, filename)
try:
ws = Sheet(worksheet_name, wkb=self.wkb)
ws.xl_sheet.ExportAsFixedFormat(0, filepath)
logger.info("created %s", filepath)
except Exception as e:
logger.error("failed to export pdf")
logger.error("detailled error: %s - %s",
e.__class__.__name__, str(e))
def close_current_wkb(self):
self.wkb.close()
def insert_one_series(self, series, cell_specification):
"""
Populate the current workbook given a single
:class=:`pandas.Series` object.
"""
if not len(series):
return
# contiguous cells
#TODO: (use vertical and horizontal properties of xlwings)
# non-contiguous user-defined cells
for cs in cell_specification.get('cells', []):
ws = cs.get('worksheet') or Sheet.active(self.wkb).name
content = self.jinja_env.from_string(cs['content']).render(**series)
logger.debug("insert content '%s' at cell '%s' in sheet '%s'",
content, cs['cell'], ws)
Range(ws, cs['cell']).value = content
def insert_one_dataframe(self, df, cell_specification):
"""
Populate the current workbook given a single
:class=:`pandas.DataFrame` object.
"""
if not len(df):
return
index = cell_specification.get('index', False)
header = cell_specification.get('header', False)
top_left_cell = cell_specification.get('top_left_cell', 'A0')
logger.debug("insert %d by %d rows/cols dataframe "
"at cell '%s' in sheet '%s'",
len(df), len(df.columns),
str(top_left_cell), Sheet.active(self.wkb).name)
Range(top_left_cell, index=index, header=header).value = df
def apply_render_block(self, render_block, query_context=None,
**kwargs):
"""
Apply a single render block in the specification file.
- `query_context` (mappable or None) is a context used when
rendering the database query with jinja2 (optional).
- **kwargs is used to overwrite any key/value pair
in the render block.
"""
# override render_block key/val with kwargs
render_block.update(kwargs)
logger.info("processing render block '%s'",
render_block.get('name', '<unamed>'))
# query the DB into a pandas DataFrame
if query_context is None:
query_context = dict()
query_template = self.jinja_env.from_string(
render_block['query'].strip()
)
query = query_template.render(**query_context)
logger.debug("rendered query: \n'''\n%s\n'''", query)
df = pd.read_sql(query, self.db_engine)
logger.debug("query returned %d record(s)", len(df))
# TODO: calculate extra columns and add it to the DataFrame
# activate worksheet if provided
ws_name = render_block['cell_specification'].get('worksheet') or None
if ws_name is not None:
ws2reactivate_name = Sheet.active(self.wkb).name
Sheet(ws_name, wkb=self.wkb).activate()
# apply the render_block, apply recusively included blocks,
# and save the rendered workbook(s) if needed
apply_by_row = render_block.get('apply_by_row', False)
save_as = render_block.get('save_as', None)
if apply_by_row and save_as is not None:
logger.info("%d file(s) to generate", len(df))
if apply_by_row:
for row, pseries in df.iterrows():
self.insert_one_series(
pseries, render_block['cell_specification']
)
for item in render_block.get('include', []):
if isinstance(item, dict):
block_name = item.pop('render_block')
override_vars = item
else:
block_name = item
override_vars = {}
block = [b for b in self.render_blocks
if b['name'] == block_name][0]
self.apply_render_block(block,
query_context=pseries,
**override_vars)
if save_as is not None:
tpl = save_as['filename']
filename = self.jinja_env.from_string(tpl).render(**pseries)
self.save_current_wkb(filename)
# save to pdf
if save_as.get('export_pdf', False):
filename_pdf = os.path.splitext(filename)[0] + '.pdf'
if ws_name is None:
logger.error(
"(export to pdf) no worksheet specified"
)
else:
self.save_current_wkb_as_pdf(filename_pdf, ws_name)
# re-open the template, re-activate the worksheet
self.close_current_wkb()
self.open_template_as_current_wkb()
if ws_name is not None:
Sheet(ws_name, wkb=self.wkb).activate()
else:
self.insert_one_dataframe(
df, render_block['cell_specification']
)
# TODO: include and save_as in this case
# re-activate former worksheet if needed
if ws_name is not None:
Sheet(ws2reactivate_name, wkb=self.wkb).activate()
def render(self):
"""Main render method."""
self.open_template_as_current_wkb()
save_render_blocks = [block for block in self.render_blocks
if 'save_as' in block.keys()]
for block in save_render_blocks:
self.apply_render_block(block)
self.wkb.close()
def write_xlsx(self,
xlsx_filename,
is_mag_phase=False,
delete_objects=True):
"""
Writes an OP2 file based on the data we have stored in the object
:param xlsx_filename: the name of the XLSX file to write
:param is_mag_phase: should complex data be written using Magnitude/Phase
instead of Real/Imaginary (default=False; Real/Imag)
Real objects don't use this parameter.
:param delete_objects: should objects be deleted after they're written
to reduce memory (default=True)
"""
from xlwings import Workbook, Sheet #, Range, Chart
from pywintypes import com_error
print('writing %s' % xlsx_filename)
if isinstance(xlsx_filename, str):
workbook = Workbook() # Creates a connection with a new workbook
try:
workbook.save(xlsx_filename)
except com_error:
raise RuntimeError('Close %s' % xlsx_filename)
else:
raise NotImplementedError(type(xlsx_filename))
# assert isinstance(xlsx_filename, file), 'type(xlsx_filename)= %s' % xlsx_filename
# op2 = xlsx_filename
# xlsx_filename = op2.name
# print('xlsx_filename =', xlsx_filename)
#op2_ascii.write('writing [3, 7, 0] header\n')
#if markers == [3,]: # PARAM, POST, -1
#self.read_markers([3])
#data = self.read_block()
#self.read_markers([7])
#data = self.read_block()
#data = self._read_record()
#self.read_markers([-1, 0])
#elif markers == [2,]: # PARAM, POST, -2
isheet = 1
if 0:
#_write_markers(op2, op2_ascii, [3, 0, 7])
#tape_code = b'NASTRAN FORT TAPE ID CODE - '
Sheet(isheet).name = sheet_name
sheet = Sheet(isheet)
sheet['A1'].value = 'NASTRAN FORT TAPE ID CODE'
sheet['B1'].value = tape_code
nastran_version = b'NX8.5 ' if self.is_nx else b'XXXXXXXX'
sheet['A2'].value = 'nastran_version'
sheet['B2'].value = nastran_version
isheet = +1
if self.grid_point_weight.reference_point is not None and 0:
if has_attr(result, 'write_xlsx'):
self.grid_point_weight.write_xlsx(workbook, page_stamp,
self.page_num)
else:
print("*op2 - grid_point_weight not written")
#is_mag_phase = False
# eigenvalues are written first
for ikey, result in sorted(iteritems(self.eigenvalues)):
# header
#print('%-18s SUBCASE=%i' % (result.__class__.__name__, isubcase))
if has_attr(result, 'write_xlsx'):
result.write_xlsx(xlsx)
if delete_objects:
del result
else:
print("*xlsx - %s not written" % result.__class__.__name__)
# asdf
# then eigenvectors
# has a special header
for isubcase, result in sorted(iteritems(self.eigenvectors)):
(subtitle, label) = self.iSubcaseNameMap[isubcase]
if hasattr(result, 'write_xlsx'):
print('%-18s SUBCASE=%i' %
(result.__class__.__name__, isubcase))
result.write_xlsx(workbook, is_mag_phase=is_mag_phase)
if delete_objects:
del result
else:
print("*xlsx - %s not written" % result.__class__.__name__)
# asdf
# finally, we writte all the other tables
# nastran puts the tables in order of the Case Control deck,
# but we're lazy so we just hardcode the order
#if markers == [3,]: # PARAM, POST, -2
#self.read_markers([3])
#data = self.read_block()
#self.read_markers([7])
#data = self.read_block()
#data = self._read_record()
oef = [
# OEF - forces
# alphabetical order...
self.cbar_force,
# beam
#self.cbeam_forces,
#self.cbar100_forces,
#self.cbend_forces,
self.cbeam_force,
self.cbush_force,
self.celas1_force,
self.celas2_force,
self.celas3_force,
self.celas4_force,
self.cdamp1_force,
self.cdamp2_force,
self.cdamp3_force,
self.cdamp4_force,
#self.plateForces, # centroidal elements
#self.plateForces2, # bilinear elements
self.conrod_force,
self.cquad4_force,
self.cquad8_force,
self.crod_force,
self.cshear_force,
self.ctria3_force,
self.ctria6_force,
self.ctube_force,
# other
#self.cgap_force, #self.solidPressureForces,
]
oes1x1 = [
# cbars/cbeams
self.cbar_stress,
self.cbeam_stress,
# bush
self.cbush_stress,
self.cbush1d_stress_strain,
# rods
#self.nonlinearRodStress,
self.celas1_stress,
self.celas2_stress,
self.celas3_stress,
self.celas4_stress,
self.chexa_stress,
self.conrod_stress,
self.cpenta_stress,
self.cquad4_stress,
self.cquad8_stress,
self.cquadr_stress,
self.crod_stress,
self.cshear_stress,
self.ctetra_stress,
self.ctria3_stress,
self.ctria6_stress,
self.ctriar_stress,
self.ctube_stress,
]
oes1c = [
self.cquad4_composite_stress,
self.cquad8_composite_stress,
self.cquadr_composite_stress,
self.ctria3_composite_stress,
self.ctria6_composite_stress,
self.ctriar_composite_stress,
#self.nonlinearPlateStress,
self.ctriax_stress, #self.hyperelasticPlateStrain,
]
#stress = oes1x1 + oes1c
strain = [
# bars/beams
self.cbar_strain,
self.cbeam_strain,
# springs,
self.celas1_strain,
self.celas2_strain,
self.celas3_strain,
self.celas4_strain,
# plates
self.ctria3_strain,
self.cquad4_strain,
self.cshear_strain,
self.cquad4_composite_strain,
self.cquad8_composite_strain,
self.cquadr_composite_strain,
self.ctria3_composite_strain,
self.ctria6_composite_strain,
self.ctriar_composite_strain,
#self.nonlinearPlateStrain,
#self.ctriax_strain, self.hyperelasticPlateStress,
# solids
self.ctetra_strain,
self.cpenta_strain,
self.chexa_strain,
# rods
#self.nonlinearRodStrain, # non-vectorized
self.chexa_strain,
self.conrod_strain,
self.cpenta_strain,
self.cquad4_strain,
self.cquad8_strain,
self.cquadr_strain,
self.crod_strain,
self.cshear_strain,
self.ctetra_strain,
self.ctria3_strain,
self.ctria6_strain,
self.ctriar_strain,
self.ctube_strain,
# bush
self.cbush_strain,
]
oug = [
self.accelerations,
self.displacements,
self.displacementsPSD,
self.displacementsATO,
self.displacementsRMS,
#self.scaled_displacements, # ???
self.temperatures,
self.velocities,
self.eigenvectors,
]
oqg_mpc = [
self.mpc_forces,
]
oqg_spc = [
self.spc_forces,
]
ogs = [
self.grid_point_stresses,
self.grid_point_volume_stresses,
]
ogp = [
self.grid_point_forces,
]
other = [
#self.forceVectors,
#self.loadVectors,
self.thermal_load_vectors,
]
isubcases = sorted(self.iSubcaseNameMap.keys())
#title = self.title
res_categories = [('OUG', oug), ('OQG_SPC', oqg_spc),
('OQG_MPC', oqg_mpc),
('OEF', oef), ('OES1X', oes1x1), ('OES1C', oes1c),
('OSTR', strain), ('OGS', ogs), ('OGP', ogp),
('other', other)]
res_outs = {}
# TODO: add a summary sheet
# TODO: this may need to be reworked such that all of subcase 1
#is printed before subcase 2
for res_category_name, res_category in res_categories:
#print("res_category_name = %s" % res_category_name)
for res_type in res_category:
res_keys = isubcases
for res_key in res_keys:
isubcase = res_key
if isubcase in res_type:
#(subtitle, label) = self.iSubcaseNameMap[isubcase]
result = res_type[isubcase]
element_name = ''
if hasattr(result, 'element_name'):
element_name = ' - ' + result.element_name
if hasattr(result, '_write_xlsx'):
class_name = result.__class__.__name__
sheet_name = '%s_%s' % (class_name, isubcase)
assert len(sheet_name) < 31, sheet_name
Sheet(isheet).name = sheet_name
sheet = Sheet(isheet)
print(' %s - isubcase=%i%s' %
(result.__class__.__name__, isubcase,
element_name))
result._write_xlsx(sheet, is_mag_phase=False)
isheet += 1
else:
print(" *xlsx - %s not written" %
result.__class__.__name__)
#footer = [4, 0, 4]
workbook.save()
def update_excel_dashboard(project):
print('Started function.')
### filekey requires at least first two characters filename
file_key = return_file_key(project.name)
dash_filename = return_filename(file_key, dash_list)
### dispatch project class object, importing data into Project Class
if dash_filename != None:
filename = LOCAL_DASH_PATH + dash_filename
save_filename = LOCAL_DASH_PATH + file_key + '-dashboard-' + datetime.today().strftime("%d-%b-%Y_T%H_%M") + '.xlsx'
else:
filename = LOCAL_DASH_PATH + PP_DASH_TEMPLATE
save_filename = LOCAL_DASH_PATH + file_key + '-dashboard-' + datetime.today().strftime("%d-%b-%Y_T%H_%M") + '.xlsx'
wb = Workbook(filename)
excel = wb.xl_app
excel.visible = True
pending_tasks = Sheet('pending_tasks')
project_plan = Sheet('project_plan')
sheet_dict = {'project_plan': (project_plan, project.Task_list), 'pending_tasks': (pending_tasks, get_pending_tasks(project))}
cat_task_dict = get_cat_task_dict(project)
for sheet_name in list(sheet_dict.keys())[:2]:
row =2
next_row = Range(sheet_dict[sheet_name][0].name, (row,1), (row,11))
clear_range = Range(sheet_dict[sheet_name][0].name, (2,1), (500,11))
clear_range.value = ''
for item in sheet_dict[sheet_name][1]:
task = item.task
task_fields = [task.WBS, item.action, project.get_phase(item), task.Name, task.Start, task.Finish, task.Deadline, task.PercentComplete, \
item.status(), item.status_override, item.recovery_plan]
next_row.value = task_fields
row = row +1
next_row = Range(sheet_dict[sheet_name][0].name, (row,1), (row,9))
print('Updated project plan and pending tasks.')
### milestone dashboard
row = 5
next_row = Range('milestones', (row,2), (row,4))
for item in get_milestones(project):
due_date = "Not assigned"
task = item.task
if task.Deadline != 'NA':
if task.Deadline.date() < task.Finish.date() and task.PercentComplete != 100:
due_date = task.Deadline
mitigation = 'New target date: ' + task.Finish.strftime("%d-%b-%Y") + '. ' + item.recovery_plan
else:
due_date = task.Finish
mitigation = item.recovery_plan
else:
due_date = task.Finish
mitigation = item.recovery_plan
task_fields = [task.Name, due_date, item.status(), mitigation]
next_row.value = task_fields
row = row +1
next_row = Range('milestones', (row,2), (row,4))
print('Updated milestones.')
row = 2
clear_range = Range('CAPA status', (2,1), (500,11))
clear_range.value = ''
### project
project_status_row = Range('CAPA status', (row,1), (row,10))
task_fields = get_project_status(project)
project_status_row.value = task_fields
project_status = Range('milestones', (1,4), (1,4))
project_status.value = task_fields[6]
capa_status_row = Range('CAPA status', (row+1,1), (row+1,10))
task_fields = get_capa_status(project)
capa_status_row.value = task_fields
capa_status = Range('milestones', (2,4), (2,4))
capa_status.value = task_fields[6]
row = row + 2
### interim controls
next_row = Range('CAPA status', (row,1), (row,11))
IC_cat_dash = get_cat_dash(project, 'IC')
for task_fields in IC_cat_dash:
next_row.value = task_fields
row = row +1
next_row = Range('CAPA status', (row,1), (row,11))
### CA / PA dashboard
for task_fields in get_cat_dash(project, 'CA'):
next_row.value = task_fields
row = row +1
next_row = Range('CAPA status', (row,1), (row,11))
for task_fields in get_cat_dash(project, 'PA'):
if task_fields != None:
next_row.value = task_fields
row = row +1
next_row = Range('CAPA status', (row,1), (row,11))
### remediation
for task_fields in get_cat_dash(project, 'RM'):
next_row.value = task_fields
row = row +1
next_row = Range('CAPA status', (row,1), (row,11))
print('Updated CAPA status.')
#### implementation
row = 2
clear_range = Range('implementation', (2,1), (500,11))
clear_range.value = ''
next_row = Range('implementation', (row,1), (row,11))
for procedure in get_implementation(project):
next_row.value = procedure
row = row +1
next_row = Range('implementation', (row,1), (row,11))
print('Updated implementation.')
wb.save(save_filename)
#print(save_filename)
wb.close()
class ExcelTemplateRenderer(object):
"""
A class to render an Excel template using data stored
in a database and given a specification file.
Parameters
----------
db_engine : object
the :class:`sqlalchemy.engine.Engine` object used to
connect and query the database.
template_name : str
path to the `xlsx` template file.
spec_filename : str
path to the specification file (yaml format).
output_dirname : str
path to the directory where to store the
generated excel file(s) (i.e., the rendered template(s)).
jinja_env : object or None
allow to provide a :class:`jinja2.Environment` object,
helpful when using custom filters in the specification file
(optional).
Notes
-----
The specification file (yaml format) consists of a list
of render blocks. TODO: documentation on the format.
"""
def __init__(self,
db_engine,
template_name,
spec_filename,
output_dirname,
jinja_env=None):
self.db_engine = db_engine
self.template_name = template_name
self.spec_filename = spec_filename
self.output_dirname = os.path.abspath(output_dirname)
if jinja_env is None:
self.jinja_env = jinja2.Environment()
else:
self.jinja_env = jinja_env
with open(self.spec_filename, 'r', encoding='utf-8') as f:
self.render_blocks = yaml.load(f)
os.makedirs(self.output_dirname, exist_ok=True)
logger.info("output directory is %s", self.output_dirname)
def open_template_as_current_wkb(self):
self.wkb = Workbook(os.path.abspath(self.template_name),
app_visible=False)
self.wkb.set_current()
def save_current_wkb(self, filename):
filepath = os.path.join(self.output_dirname, filename)
self.wkb.save(filepath)
logger.info("created %s", filepath)
def save_current_wkb_as_pdf(self, filename, worksheet_name):
filepath = os.path.join(self.output_dirname, filename)
try:
ws = Sheet(worksheet_name, wkb=self.wkb)
ws.xl_sheet.ExportAsFixedFormat(0, filepath)
logger.info("created %s", filepath)
except Exception as e:
logger.error("failed to export pdf")
logger.error("detailled error: %s - %s", e.__class__.__name__,
str(e))
def close_current_wkb(self):
self.wkb.close()
def insert_one_series(self, series, cell_specification):
"""
Populate the current workbook given a single
:class=:`pandas.Series` object.
"""
if not len(series):
return
# contiguous cells
#TODO: (use vertical and horizontal properties of xlwings)
# non-contiguous user-defined cells
for cs in cell_specification.get('cells', []):
ws = cs.get('worksheet') or Sheet.active(self.wkb).name
content = self.jinja_env.from_string(
cs['content']).render(**series)
logger.debug("insert content '%s' at cell '%s' in sheet '%s'",
content, cs['cell'], ws)
Range(ws, cs['cell']).value = content
def insert_one_dataframe(self, df, cell_specification):
"""
Populate the current workbook given a single
:class=:`pandas.DataFrame` object.
"""
if not len(df):
return
index = cell_specification.get('index', False)
header = cell_specification.get('header', False)
top_left_cell = cell_specification.get('top_left_cell', 'A0')
logger.debug(
"insert %d by %d rows/cols dataframe "
"at cell '%s' in sheet '%s'", len(df), len(df.columns),
str(top_left_cell),
Sheet.active(self.wkb).name)
Range(top_left_cell, index=index, header=header).value = df
def apply_render_block(self, render_block, query_context=None, **kwargs):
"""
Apply a single render block in the specification file.
- `query_context` (mappable or None) is a context used when
rendering the database query with jinja2 (optional).
- **kwargs is used to overwrite any key/value pair
in the render block.
"""
# override render_block key/val with kwargs
render_block.update(kwargs)
logger.info("processing render block '%s'",
render_block.get('name', '<unamed>'))
# query the DB into a pandas DataFrame
if query_context is None:
query_context = dict()
query_template = self.jinja_env.from_string(
render_block['query'].strip())
query = query_template.render(**query_context)
logger.debug("rendered query: \n'''\n%s\n'''", query)
df = pd.read_sql(query, self.db_engine)
logger.debug("query returned %d record(s)", len(df))
# TODO: calculate extra columns and add it to the DataFrame
# activate worksheet if provided
ws_name = render_block['cell_specification'].get('worksheet') or None
if ws_name is not None:
ws2reactivate_name = Sheet.active(self.wkb).name
Sheet(ws_name, wkb=self.wkb).activate()
# apply the render_block, apply recusively included blocks,
# and save the rendered workbook(s) if needed
apply_by_row = render_block.get('apply_by_row', False)
save_as = render_block.get('save_as', None)
if apply_by_row and save_as is not None:
logger.info("%d file(s) to generate", len(df))
if apply_by_row:
for row, pseries in df.iterrows():
self.insert_one_series(pseries,
render_block['cell_specification'])
for item in render_block.get('include', []):
if isinstance(item, dict):
block_name = item.pop('render_block')
override_vars = item
else:
block_name = item
override_vars = {}
block = [
b for b in self.render_blocks
if b['name'] == block_name
][0]
self.apply_render_block(block,
query_context=pseries,
**override_vars)
if save_as is not None:
tpl = save_as['filename']
filename = self.jinja_env.from_string(tpl).render(
**pseries)
self.save_current_wkb(filename)
# save to pdf
if save_as.get('export_pdf', False):
filename_pdf = os.path.splitext(filename)[0] + '.pdf'
if ws_name is None:
logger.error(
"(export to pdf) no worksheet specified")
else:
self.save_current_wkb_as_pdf(filename_pdf, ws_name)
# re-open the template, re-activate the worksheet
self.close_current_wkb()
self.open_template_as_current_wkb()
if ws_name is not None:
Sheet(ws_name, wkb=self.wkb).activate()
else:
self.insert_one_dataframe(df, render_block['cell_specification'])
# TODO: include and save_as in this case
# re-activate former worksheet if needed
if ws_name is not None:
Sheet(ws2reactivate_name, wkb=self.wkb).activate()
def render(self):
"""Main render method."""
self.open_template_as_current_wkb()
save_render_blocks = [
block for block in self.render_blocks if 'save_as' in block.keys()
]
for block in save_render_blocks:
self.apply_render_block(block)
self.wkb.close()
# Prepare Third Sheet
sheet3 = pd.read_csv(file_iv, delim_whitespace=True)
Sheet.add('csv_iv',after='csv_ir')
Range('csv_iv','A1',index=False).value = sheet3
Range('csv_iv','A:ZZ').autofit('c')
# Save WorkSheet
xlspath = xls_dir + "reference_view_balance_" + datetime + ".xlsx"
wb.save(xlspath)
wb.close()
# Empty Files
open( file_itogo,"w").close()
open( file_ir,"w").close()
open( file_iv,"w").close()
else:
print ( "Check File Size, It shouldn't be empty")
else:
print ("Check All files exists ")
def write_xlsx(self, xlsx_filename, is_mag_phase=False,
delete_objects=True):
"""
Writes an OP2 file based on the data we have stored in the object
:param xlsx_filename: the name of the XLSX file to write
:param is_mag_phase: should complex data be written using Magnitude/Phase
instead of Real/Imaginary (default=False; Real/Imag)
Real objects don't use this parameter.
:param delete_objects: should objects be deleted after they're written
to reduce memory (default=True)
"""
from xlwings import Workbook, Sheet#, Range, Chart
from pywintypes import com_error
print('writing %s' % xlsx_filename)
if isinstance(xlsx_filename, str):
workbook = Workbook() # Creates a connection with a new workbook
try:
workbook.save(xlsx_filename)
except com_error:
raise RuntimeError('Close %s' % xlsx_filename)
else:
raise NotImplementedError(type(xlsx_filename))
# assert isinstance(xlsx_filename, file), 'type(xlsx_filename)= %s' % xlsx_filename
# op2 = xlsx_filename
# xlsx_filename = op2.name
# print('xlsx_filename =', xlsx_filename)
#op2_ascii.write('writing [3, 7, 0] header\n')
#if markers == [3,]: # PARAM, POST, -1
#self.read_markers([3])
#data = self.read_block()
#self.read_markers([7])
#data = self.read_block()
#data = self._read_record()
#self.read_markers([-1, 0])
#elif markers == [2,]: # PARAM, POST, -2
isheet = 1
if 0:
#_write_markers(op2, op2_ascii, [3, 0, 7])
#tape_code = b'NASTRAN FORT TAPE ID CODE - '
Sheet(isheet).name = sheet_name
sheet = Sheet(isheet)
sheet['A1'].value = 'NASTRAN FORT TAPE ID CODE'
sheet['B1'].value = tape_code
nastran_version = b'NX8.5 ' if self.is_nx else b'XXXXXXXX'
sheet['A2'].value = 'nastran_version'
sheet['B2'].value = nastran_version
isheet =+ 1
if self.grid_point_weight.reference_point is not None and 0:
if has_attr(result, 'write_xlsx'):
self.grid_point_weight.write_xlsx(workbook, page_stamp, self.page_num)
else:
print("*op2 - grid_point_weight not written")
#is_mag_phase = False
# eigenvalues are written first
for ikey, result in sorted(iteritems(self.eigenvalues)):
# header
#print('%-18s SUBCASE=%i' % (result.__class__.__name__, isubcase))
if has_attr(result, 'write_xlsx'):
result.write_xlsx(xlsx)
if delete_objects:
del result
else:
print("*xlsx - %s not written" % result.__class__.__name__)
# asdf
# then eigenvectors
# has a special header
for isubcase, result in sorted(iteritems(self.eigenvectors)):
(subtitle, label) = self.iSubcaseNameMap[isubcase]
if hasattr(result, 'write_xlsx'):
print('%-18s SUBCASE=%i' % (result.__class__.__name__, isubcase))
result.write_xlsx(workbook, is_mag_phase=is_mag_phase)
if delete_objects:
del result
else:
print("*xlsx - %s not written" % result.__class__.__name__)
# asdf
# finally, we writte all the other tables
# nastran puts the tables in order of the Case Control deck,
# but we're lazy so we just hardcode the order
#if markers == [3,]: # PARAM, POST, -2
#self.read_markers([3])
#data = self.read_block()
#self.read_markers([7])
#data = self.read_block()
#data = self._read_record()
oef = [
# OEF - forces
# alphabetical order...
self.cbar_force,
# beam
#self.cbeam_forces,
#self.cbar100_forces,
#self.cbend_forces,
self.cbeam_force,
self.cbush_force,
self.celas1_force,
self.celas2_force,
self.celas3_force,
self.celas4_force,
self.cdamp1_force,
self.cdamp2_force,
self.cdamp3_force,
self.cdamp4_force,
#self.plateForces, # centroidal elements
#self.plateForces2, # bilinear elements
self.conrod_force,
self.cquad4_force,
self.cquad8_force,
self.crod_force,
self.cshear_force,
self.ctria3_force,
self.ctria6_force,
self.ctube_force,
# other
#self.cgap_force, #self.solidPressureForces,
]
oes1x1 = [
# cbars/cbeams
self.cbar_stress, self.cbeam_stress,
# bush
self.cbush_stress, self.cbush1d_stress_strain,
# rods
#self.nonlinearRodStress,
self.celas1_stress, self.celas2_stress, self.celas3_stress, self.celas4_stress,
self.chexa_stress,
self.conrod_stress,
self.cpenta_stress,
self.cquad4_stress, self.cquad8_stress, self.cquadr_stress,
self.crod_stress,
self.cshear_stress,
self.ctetra_stress,
self.ctria3_stress, self.ctria6_stress, self.ctriar_stress,
self.ctube_stress,
]
oes1c = [
self.cquad4_composite_stress, self.cquad8_composite_stress, self.cquadr_composite_stress,
self.ctria3_composite_stress, self.ctria6_composite_stress, self.ctriar_composite_stress,
#self.nonlinearPlateStress,
self.ctriax_stress, #self.hyperelasticPlateStrain,
]
#stress = oes1x1 + oes1c
strain = [
# bars/beams
self.cbar_strain, self.cbeam_strain,
# springs,
self.celas1_strain, self.celas2_strain, self.celas3_strain, self.celas4_strain,
# plates
self.ctria3_strain, self.cquad4_strain,
self.cshear_strain,
self.cquad4_composite_strain, self.cquad8_composite_strain, self.cquadr_composite_strain,
self.ctria3_composite_strain, self.ctria6_composite_strain, self.ctriar_composite_strain,
#self.nonlinearPlateStrain,
#self.ctriax_strain, self.hyperelasticPlateStress,
# solids
self.ctetra_strain,
self.cpenta_strain,
self.chexa_strain,
# rods
#self.nonlinearRodStrain, # non-vectorized
self.chexa_strain,
self.conrod_strain,
self.cpenta_strain,
self.cquad4_strain,
self.cquad8_strain,
self.cquadr_strain,
self.crod_strain,
self.cshear_strain,
self.ctetra_strain,
self.ctria3_strain,
self.ctria6_strain,
self.ctriar_strain,
self.ctube_strain,
# bush
self.cbush_strain,
]
oug = [
self.accelerations,
self.displacements, self.displacementsPSD, self.displacementsATO, self.displacementsRMS,
#self.scaled_displacements, # ???
self.temperatures,
self.velocities, self.eigenvectors,
]
oqg_mpc = [self.mpc_forces,]
oqg_spc = [self.spc_forces,]
ogs = [self.grid_point_stresses, self.grid_point_volume_stresses, ]
ogp = [self.grid_point_forces,]
other = [
#self.forceVectors,
#self.loadVectors,
self.thermal_load_vectors,
]
isubcases = sorted(self.iSubcaseNameMap.keys())
#title = self.title
res_categories = [
('OUG', oug),
('OQG_SPC', oqg_spc),
('OQG_MPC', oqg_mpc),
('OEF', oef),
('OES1X', oes1x1),
('OES1C', oes1c),
('OSTR', strain),
('OGS', ogs),
('OGP', ogp),
('other', other)
]
res_outs = {}
# TODO: add a summary sheet
# TODO: this may need to be reworked such that all of subcase 1
#is printed before subcase 2
for res_category_name, res_category in res_categories:
#print("res_category_name = %s" % res_category_name)
for res_type in res_category:
res_keys = isubcases
for res_key in res_keys:
isubcase = res_key
if isubcase in res_type:
#(subtitle, label) = self.iSubcaseNameMap[isubcase]
result = res_type[isubcase]
element_name = ''
if hasattr(result, 'element_name'):
element_name = ' - ' + result.element_name
if hasattr(result, '_write_xlsx'):
class_name = result.__class__.__name__
sheet_name = '%s_%s' % (class_name, isubcase)
assert len(sheet_name) < 31, sheet_name
Sheet(isheet).name = sheet_name
sheet = Sheet(isheet)
print(' %s - isubcase=%i%s' % (result.__class__.__name__, isubcase, element_name))
result._write_xlsx(sheet, is_mag_phase=False)
isheet += 1
else:
print(" *xlsx - %s not written" % result.__class__.__name__)
#footer = [4, 0, 4]
workbook.save()
df[(df['region'] == 'NW') &
(df['product'] == 'ROJ')]['price'])] * 12
Range('pricing', 'D30:O30').value = [float(
df[(df['region'] == 'SW') &
(df['product'] == 'ROJ')]['price'])] * 12
# FCOJ
Range('pricing', 'D33:O33').value = [float(
df[(df['region'] == 'NE') &
(df['product'] == 'FCOJ')]['price'])] * 12
Range('pricing', 'D34:O34').value = [float(
df[(df['region'] == 'MA') &
(df['product'] == 'FCOJ')]['price'])] * 12
Range('pricing', 'D35:O35').value = [float(
df[(df['region'] == 'SE') &
(df['product'] == 'FCOJ')]['price'])] * 12
Range('pricing', 'D36:O36').value = [float(
df[(df['region'] == 'MW') &
(df['product'] == 'FCOJ')]['price'])] * 12
Range('pricing', 'D37:O37').value = [float(
df[(df['region'] == 'DS') &
(df['product'] == 'FCOJ')]['price'])] * 12
Range('pricing', 'D38:O38').value = [float(
df[(df['region'] == 'NW') &
(df['product'] == 'FCOJ')]['price'])] * 12
Range('pricing', 'D39:O39').value = [float(
df[(df['region'] == 'SW') &
(df['product'] == 'FCOJ')]['price'])] * 12
wb.save()
def save_xl_using_xlwings(file):
wb = Workbook(file)
wb.save()
MainFolder = 'C:\\Search_Result'
FolderPath = os.path.join(MainFolder, row[1])
if not os.path.exists(FolderPath):
os.makedirs(FolderPath)
#Search and generate excel
path='//Ecs01//ddb00/final_price'
GetResult = Search_History.SearchFile(path,row[2],'None')
if GetResult != 'No Results':
CWPath = '\\\\ecsbks01\\swap\\DDD00\\virtualenv\\WinPython-32bit-2.7.10.2\\python-2.7.10\\Project_Evaluate_Excel\\Search_History'
Excel_Path = os.path.join(CWPath, 'Result-Output.xlsx')
wb = Workbook(Excel_Path)
#wb = Workbook.caller()
ExcelName = ('%s.xlsx' % row[2])
ExcelPath = os.path.join(FolderPath,ExcelName)
wb.save(ExcelPath)
wb.close()
else:
print "No result"
NoFoundArr.append([row[1],row[2]])
#Write No result tag in to NotFound.xlsx
print NoFoundArr
NotFoundPath = 'C:\\Search_Result\\Not_Found.xlsx'
wb = Workbook(NotFoundPath)
wb = Workbook.caller()
i=0
for line in NoFoundArr:
from xlwings import Workbook, FileFormat
this_dir = os.path.dirname(os.path.abspath(__file__))
# Template
template_path = os.path.abspath(os.path.join(this_dir, os.pardir, 'xlwings', 'xlwings_template.xltm'))
workbook_paths = [template_path]
# Examples
root = os.path.abspath(os.path.join(this_dir, os.pardir, 'examples'))
for root, dirs, files in os.walk(root):
for f in files:
if f.endswith(".xlsm"):
workbook_paths.append((os.path.join(root, f)))
for path in workbook_paths:
wb = Workbook(path)
wb.xl_workbook.VBProject.VBComponents.Remove(wb.xl_workbook.VBProject.VBComponents("xlwings"))
wb.xl_workbook.VBProject.VBComponents.Import(os.path.abspath(os.path.join(this_dir, os.pardir, 'xlwings', 'xlwings.bas')))
if 'xlwings_template' in wb.fullname:
# TODO: implement FileFormat in xlwings
wb.xl_workbook.Application.DisplayAlerts = False
wb.xl_workbook.SaveAs(template_path, FileFormat=FileFormat.xlOpenXMLTemplateMacroEnabled)
wb.xl_workbook.Application.DisplayAlerts = True
else:
wb.save()
wb.close()
def update_program_dashboard(program):
filename = LOCAL_DASH_PATH + PROGRAM_DASH_TEMPLATE
print('Excel program template filename: ', filename)
save_filename = LOCAL_DASH_PATH + 'program_dashboard_' + datetime.today().strftime("%d-%b-%Y_T%H_%M") + '.xlsx'
wb = Workbook(filename)
excel = wb.xl_app
excel.visible = True
### milestone dashboard
for file_key in enumerate(FILE_KEYS):
project = program[file_key[1]]
row = 4 + 2 * file_key[0]
due_date_row = Range('team milestones', (row,3), (row,19))
status_row= Range('team milestones', (row+1,3), (row+1,19))
milestones = get_milestones(project)
due_date_row.value = [task.get_due_date() for task in milestones]
status_row.value = [task.status() for task in milestones]
### CAPA Status
row=2
clear_range = Range('CAPA status', (2,1), (500,11))
clear_range.value = ''
for file_key in enumerate(FILE_KEYS):
project = program[file_key[1]]
project_status_row = Range('CAPA status', (row,1), (row,10))
project_status_row.value = get_project_status(project)
capa_status_row = Range('CAPA status', (row+1,1), (row+1,10))
capa_status_row.value = get_capa_status(project)
row = row + 2
next_row = Range('CAPA status', (row,1), (row,10))
IC_cat_dash = get_cat_dash(project, 'IC')
for task_fields in IC_cat_dash:
next_row.value = task_fields
row = row +1
next_row = Range('CAPA status', (row,1), (row,11))
### CA / PA dashboard
for task_fields in get_cat_dash(project, 'CA'):
next_row.value = task_fields
row = row +1
next_row = Range('CAPA status', (row,1), (row,11))
for task_fields in get_cat_dash(project, 'PA'):
if task_fields != None:
next_row.value = task_fields
row = row +1
next_row = Range('CAPA status', (row,1), (row,11))
### remediation
for task_fields in get_cat_dash(project, 'RM'):
next_row.value = task_fields
row = row +1
next_row = Range('CAPA status', (row,1), (row,11))
row = row +1
### Implementation
row=2
clear_range = Range('implementation', (2,1), (500,11))
clear_range.value = ''
for file_key in enumerate(FILE_KEYS):
project = program[file_key[1]]
for procedure in get_implementation(project):
next_row = Range('implementation', (row,1), (row,10))
next_row.value = procedure
row = row + 1
wb.save(save_filename)
wb.close()
excel.quit()
from xlwings import Workbook, Sheet, Range, Chart
wb = Workbook(r'C:\Users\mih\Desktop\Stock_Screener\Proj1.xlsx')
Range('A1').value = 'Two 2'
print Range('A1').value
Range('A1').value = [['Too 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 40.0]]
Range('A1').table.value # or: Range('A1:C2').value
Sheet(1).name
chart = Chart.add(source_data=Range('A1').table)
wb.save("C:\Users\mih\Desktop\Stock_Screener\Proj1.xlsx")
#generate load proportionality factor data
xy_result = session.XYDataFromHistory(
name='LPF' + model_num,
odb=odb,
outputVariableName='Load proportionality factor: LPF for Whole Model',
steps=('NonLinear_Buckling', ),
)
length_xy = len(xy_result)
#extract collapse load proportionality factor
for L in range(0, length_xy - 1):
if xy_result[L + 1][1] - xy_result[L][1] > 0:
if xy_result[L + 1][1] > 0:
if L + 1 == length_xy - 1:
Range("Sheet1",
"EM" + model_num).value = xy_result[L + 1][1]
else:
continue
else:
Range("Sheet1", "EM" + model_num).value = xy_result[L][1]
else:
Range("Sheet1", "EM" + model_num).value = xy_result[L][1]
break
#save excel sheet
wb.save("C:\\Temp\\Abaqus\\OR\\Wharf\\New\\Randomization.xlsm")
