def fill_jinja_references(workbook, data):
"""Fill-in Jinga-style references, iterating over all
worksheet pages to fill-in all occurrences of each reference 1 by 1.
Args:
workbook (Workbook): A Workbook object.
data (dict): A dictionary of context data.
"""
context = {snake_case(key): values for key, values in data.items()}
sheets = workbook.worksheets
for sheet in sheets:
refs = get_jinja_references(sheet)
for key, cells in refs.items():
clean_key = snake_case(key)
value = context.get(clean_key, 0)
for cell in cells:
if value:
sheet[cell] = str(value)
else:
sheet[cell] = 'MISSING'
def get_worksheet_headers(sheet):
"""Get the headres of a worksheet.
Args:
sheet (Worksheet): An openpyx; Excel file object.
Returns:
headers (list): A list of header strings.
"""
headers = []
for cell in sheet[1]:
headers.append(snake_case(cell.value))
return headers
def get_data_block(sheet, coords, expand=None):
"""Get a data block.
Args:
sheet (Sheet): The worksheet containing the data block.
coords (str): The inclusive coordinates of the data block.
expand (str): Optionally expand the range of values.
Returns
(dict): A dictionary of the data in the data block.
"""
data = {}
values = sheet.range(coords).options(expand=expand).value
for item in values:
key = snake_case(item[0])
value = item[1]
data[key] = value
return data
def import_worksheet(filename, sheetname, range_start='A1'):
"""Read the data from a given worksheet using xlwings.
Args:
filename (str): The name of the Excel file to read.
range_start (str): Optional starting cell.
Returns:
list(dict): A list of dictionaries.
"""
app = xlwings.App(visible=False)
book = xlwings.Book(filename)
sheet = book.sheets(sheetname)
excel_data = sheet.range(range_start).expand('table').value
keys = [snake_case(key) for key in excel_data[0]]
data = [dict(zip(keys, values)) for values in excel_data[1:]]
book.close()
app.quit()
return data
def read_worksheet(path, filename='Upload'):
"""Read the imported data, iterating over the rows and
getting value from each cell in row.
Args:
path (str or InMemoryFile): An Excel workbook to read.
filename (str): The name of the worksheet to upload.
Returns:
(DataFrame): A Pandas DataFrame of the results.
"""
# FIXME:
# try:
# workbook = openpyxl.load_workbook(path, data_only=True)
# sheet = workbook.get_sheet_by_name(filename)
# headers = get_worksheet_headers(sheet)
# return pd.DataFrame(get_worksheet_data(sheet, headers))
# except:
print('Path:', path)
data = pd.read_csv(path)
data.columns = [snake_case(x) for x in data.columns]
return data
def import_worksheet_data(model_type):
"""A function called from Excel to import data by ID
from Firestore into the Excel workbook.
Args:
model_type (str): The data model at hand.
"""
# Initialize the workbook.
book = xlwings.Book.caller()
worksheet = book.sheets.active
config_sheet = book.sheets['cannlytics.conf']
config = get_data_block(config_sheet, 'A1', expand='table')
show_status_message(
worksheet,
coords=config['status_cell'],
message='Importing %s data...' % model_type,
background=config['success_color'],
)
# Read the IDs.
id_cell = increment_row(config['table_cell'])
ids = worksheet.range(id_cell).options(expand='down', ndim=1).value
# Get your Cannlytics API key from your .env file, location specified
# by env_path on the cannlytics.config sheet.
load_dotenv(config['env_path'])
api_key = os.getenv('CANNLYTICS_API_KEY')
# Get the worksheet columns.
columns = worksheet.range(config['table_cell']).options(expand='right',
ndim=1).value
columns = [snake_case(x) for x in columns]
# Get data using model type and ID through the API.
base = config['api_url']
org_id = worksheet.range(config['org_id_cell']).value
headers = {
'Authorization': 'Bearer %s' % api_key,
'Content-type': 'application/json',
}
if len(ids) == 1:
url = f'{base}/{model_type}/{ids[0]}?organization_id={org_id}'
else:
url = f'{base}/{model_type}?organization_id={org_id}&items={str(ids)}'
response = requests.get(url, headers=headers)
if response.status_code != 200:
show_status_message(worksheet,
coords=config['status_cell'],
message='Error importing data.',
background=config['error_color'])
return
# Format the values.
items = []
data = response.json()['data']
if not data:
show_status_message(worksheet,
coords=config['status_cell'],
message='No data found.',
background=config['error_color'])
return
try:
for item in data:
values = []
for column in columns:
values.append(item.get(column))
items.append(values)
except AttributeError:
values = []
for column in columns:
values.append(data.get(column))
items = [values]
# Insert all rows at the same time.
worksheet.range(id_cell).value = items
# Show success status message.
show_status_message(
worksheet,
coords=config['status_cell'],
message='Imported %i %s.' % (len(ids), model_type),
)
def generate_coas(
import_files,
output_pages,
coa_template='./coa_template.xlsm',
# render_file='./CoAs/coa_render.xlsm',
limits={}):
"""Generate certificates of analysis.
Args:
import_files (list): A list of files to import.
output_pages (list): A list of pages to include in the PDF.
coa_template (str): The path of the CoA Template.
limits (dict): A dictionary of limits and LOQ for analytes.
"""
# Create CoA folder if one does not exist.
Path('CoAs').mkdir(parents=True, exist_ok=True)
dir_path = os.path.dirname(os.path.realpath(__file__))
# Create a copy of the template.
abs_coa_template = os.path.join(dir_path, coa_template)
coa_template_copy = abs_coa_template.replace('.xlsm', '_copy.xlsm')
copyfile(abs_coa_template, coa_template_copy)
# Iterate over all import files.
for import_file in import_files:
# Get all sample, results, client, etc. data.
# abs_import_file = os.path.join(dir_path, import_file)
all_data = pd.read_excel(import_file)
# Get all the masses.
masses = {}
for _, row in all_data.iterrows():
key = snake_case(row['assay'])
masses[key] = row['test_mass']
# Aggregate all data for a sample
data = all_data.groupby('sample_id', as_index=False).first()
# Fill in sample details.
for _, row in data.iterrows():
# Get sample data as a dictionary.
sample_data = {**row.to_dict(), **limits}
sample_id = sample_data['sample_id']
if not sample_id: # FIXME: Skip nan
continue
# Calculate terpene and cannabinoid results.
sample_data = calculate_results(sample_data,
analysis='terpenes',
mass=masses['terpenes'],
dilution_factor=40)
sample_data = calculate_results(sample_data,
analysis='cannabinoids',
mass=masses['potency'],
dilution_factor=40 * 50)
# Iterate over worksheet pages to fill-in
# all occurrences of each reference 1 by 1.
template_workbook = openpyxl.load_workbook(coa_template_copy,
keep_vba=True)
fill_jinja_references(template_workbook, sample_data)
# FIXME: Get output pages dynamically
try:
ws_index_list = get_worksheet_indexes(template_workbook,
output_pages)
except:
if len(output_pages) == 3:
ws_index_list = [3, 4, 5]
else:
ws_index_list = [3, 4, 5, 6]
# Save the rendered template, temporarily.
abs_render_file = os.path.join(dir_path, f'CoAs/{sample_id}.xlsm')
template_workbook.save(abs_render_file)
# Future: Insert signatures
# Future: Insert QR Code
# Future: Touch up the CoA.
# ws.oddHeader.left.text = "Page &[Page] of &N"
# Mark failures as red
# a1.font = Font(color="FF0000", italic=True) # the change only affects A1
# Create a PDF.
output_file = f'CoAs/{sample_id}.pdf'
# FIXME:
excel = create_coa_pdfs(abs_render_file, ws_index_list,
output_file)
# Future: Upload the PDF.
# Future: Create download link and short link for the CoA.
# Future: Upload the CoA data.
# - See https://cloud.google.com/functions/docs/writing/http#uploading_files_via_cloud_storage
# Remove temporary files.
# os.remove(abs_render_file)
os.remove(coa_template_copy)
# Ensure Excel is visible.
excel.ScreenUpdating = True
excel.DisplayAlerts = True
excel.EnableEvents = True
def test_snake_case():
"""Test string to snake-case."""
key = snake_case('% Dev. from the mean')
assert key == 'percent_dev_from_the_mean'