from openpyxl import load_workbook
import numpy as np
from pathlib import Path

# Load the workbook
folder = Path("C:/Users/benle/Downloads")
workbook = load_workbook(folder/"Stress-Strain.xlsx")

# Extract all sheet names
sheet_names = workbook.sheetnames
for name in sheet_names:
    print(name)

# Get the value of cell B3 from the first sheet
first_sheet = workbook[sheet_names[3]]
cell_b3_value = first_sheet["B3"].value

print(cell_b3_value)

# Read the entire first sheet into a list of lists
data = []
for row in first_sheet.iter_rows(values_only=True):
    data.append(list(row))

# Transpose the data <-- here we are using a new method of the np arrays
transposed_data = np.array(data, dtype=object).T.tolist()

# Create a new sheet for the transposed data
transposed_sheet = workbook.create_sheet(title="Transposed_Data")

# Write the transposed data to the new sheet
for row in transposed_data:
    transposed_sheet.append(row)

# Save the modified workbook
workbook.save(folder/"Stress-Strain Modified.xlsx")

import numpy as np
from plotly.subplots import make_subplots
from codechembook.quickTools import quickOpenFilenames

# First, we need the names of the files that we want to plot
# Ask the user to select the files and then sort the resulting list
data_files = quickOpenFilenames(filetypes = 'CSV files, *.csv')
sorted_data_files = sorted(data_files)

# Next, we will process one file at a time and add it to the plot
titration_series = make_subplots() # start a blank plotly figure object

# Read the data in one file and add it as a scatter trace to the figure object
for file in sorted_data_files: # loop through the data files one at a time
    # Read the data and store it in temporary x and y variables
    x_data, y_data = np.genfromtxt(file,
        delimiter = ',', skip_header = 1, unpack = True)
    
    # Add data as scatter trace with formatted lines and exclude from legend
    titration_series.add_scatter(x = x_data, y = y_data,
                                 line = dict(color = '#808080', width = 1, dash = 'dot'),
                                 name = file.stem + ' eqs', showlegend=False)

# Adjust the appearance of only the first and last traces to highlight
titration_series.update_traces(selector = 0, # specify the initial trace
    line = dict(color = '#008b8b', width = 2, dash = 'solid'), 
    showlegend = True, name = 'initial') 
    
titration_series.update_traces(selector = -1, # specify the final trace
    line = dict(color = '#8b0000', width = 2, dash = 'solid'), 
    showlegend = True, name = 'final') 

# Move the initial trace to the end of the data, so that it is drawn on top
titration_series.data = titration_series.data[1:] + titration_series.data[:1]

# Format the plot area and then show it and then save it
titration_series.update_layout(template = 'simple_white')
titration_series.update_xaxes(title = 'wavelength /nm', range = [270, 1100])
titration_series.update_yaxes(title = 'absorbance', range = [0, 4.5])
titration_series.show("png")

from openpyxl import Workbook
from codechembook.quickTools import importFromPy

# write a function that can accept a lmfit result object and populate an excel shee
def write_fit_to_excel(result, workbook, sheetname):
    '''
    Parameters
    ----------
    result : lmfit result object
    workbook : openpyxl workbook object <-- what we will write to
    sheetname : string <-- name of the sheet to write to
        DESCRIPTION.

    Returns
    -------
    None. The workbook is modified in place.
    '''
    
    active_sheet = wb.create_sheet(title=sheetname)
    
    # first, write the data and fit values
    active_sheet["A1"] = "experimental x"
    active_sheet["B1"] = "experimental y"
    active_sheet["C1"] = "fit y"
    for i, x, ey, fy in zip(range(2,len(result.data)+2), result.userkws['x'], result.data, result.best_fit):
        active_sheet[f"A{i}"] = x
        active_sheet[f"B{i}"] = ey
        active_sheet[f"C{i}"] = fy
        
        
    # then write out the parameters extracted, and the errors
    active_sheet["D1"] = "parameter"
    active_sheet["E1"] = "value"
    active_sheet["F1"] = "uncertainty"
    for i, p in enumerate(result.params):
        active_sheet[f"D{i+2}"] = p
        active_sheet[f"E{i+2}"] = result.params[p].value
        active_sheet[f"F{i+2}"] = result.params[p].stderr

wb = Workbook()

importFromPy('ExtinctionCalc.py', 'result')

write_fit_to_excel(result, wb, "extinction coefficient") 


importFromPy('DecomposeSpectrum.py', 'result')

write_fit_to_excel(result, wb, "spectral decomposition")

wb.save("C:/Users/benle/Downloads/solution.xlsx")

from openpyxl import load_workbook
from docx import Document
from pathlib import Path

# Load the Excel workbook and active sheet
folder = Path("C:/Users/benle/Documents/GitHub/Coding-for-Chemists/Distribution/Code by Chapter/Ch15 - Reading, Editing, and Writing Excel Files")
wb = load_workbook(folder/"titrationReport(1).xlsx", data_only=True)
sheet = wb.active

# Extract data from columns A, B, and C
data = []
for row in sheet.iter_rows(min_row=1, max_col=3, values_only=True):
    data.append(row)

# Create a Word document
doc = Document()
doc.add_heading("Extracted Titration Data", level=1)

# Add a table with the same number of columns
table = doc.add_table(rows=1, cols=3)
hdr_cells = table.rows[0].cells
hdr_cells[0].text = str(data[0][0])
hdr_cells[1].text = str(data[0][1])
hdr_cells[2].text = str(data[0][2])

# Populate the table with the remaining rows
for row in data[1:]:
    cells = table.add_row().cells
    for i in range(3):
        cells[i].text = str(row[i]) if row[i] is not None else ""

# Save the Word document
doc.save(folder/"word_table.docx")