\documentclass[../book.tex]{subfiles}
\graphicspath{{\subfix{.}}}
\begin{document}

\chapter{Reading, Editing, and Writing Excel Files}
\label{09Excel}

\setcounter{section}{-1}  % Start subsections from 0

\input{prereqs/09Excel_prereqs}

\newpage
\section{Problem}
\setcounter{subsection}{-1}  % Start subsections from 0

Things continue to go well! You have taking a large amount of good data, and your advisor is excited about the results. 

There is a non-scientific problem now though... Your advisor is  asking for results formatted in very specific ways. For instance, this morning, you received an email from your advisor with a Microsoft Excel file containing the data from your titration and a plot of it.  

\begin{figure}[H]
    \centering
      \includegraphics[width = 4in]{ChapNarrative/09Excel/Figures/ExcelStart.png}
    \caption{Screenshot of the Excel file that you wish to modify.}
    \label{fig:Excel:InitialExcel}
\end{figure}


They are then asking that you add in the results of your fitting to this in Column C, as well as the results of that fitting to the plot.  They also want the extracted parameter value in the purple boxes, and they want you to preserve the colors they have, as well as color the new cells a red-ish color. 




%You now have a rather large set of data.  You have 20 UV-vis spectra, a pH titration curve, a fit to this curve, and the resulting parameters from this curve. This is all excellent, however, there remains a problem...

''This is so frustrating! Now I have to spend an hour or so just moving information around. Like, do I copy it from the variable explorer in the Spyder IDE?  I thought I was trying to avoid all this point-and-click manipulation.'' 

\First cracks a small smile and says, ``Do you really think I spend my afternoons copying things into Excel? Instead, I let Python do that.  I can read data from Excel, create or modify Excel files, and save all the info I want directly into Excel sheets.  I can use formulas and include plots. It is actually really quite simple.''

You sit there, thinking of all the time you have spent simply copying, pasting, and formatting data into Excel.  You get a glimpse of the things you could be doing instead.  Planning experiments, hanging out with friends, cooking...

With some relish, you ask \first to see her script and have her walk you through a solution to your problem. 

\begin{mdframed}
    \textbf{A word on Excel}
    
    \noindent Allow us to get on our soapbox for a moment, and discuss the use of Microsoft Excel.  This program currently dominates the quantitative workflows of experimental chemistry. There are good reasons for this. It presents a nice grid appearance of data.  It can be used to plot data next to this data.  It can be used to perform even fairly sophisticated analysis (indeed, at the time of this writing, you can include Python in Excel on Windows machines... so it can do \textit{anything} we show in this book).  Certainly, it has enhanced many a working chemist's analysis. 

    At the same time, Excel uses a proprietary format, and the standard interface (Microsoft Excel) costs a significant amount of money. It usually requires intensive user input which can significantly slow many workflows and increase the chance of errors.  Working with very large data sets can be especially slow. At the moment, the web-based version of Excel lacks \textbf{many} features of the one that you can install on your computer.  And, it takes work to make Excel charts appear up to the standards of scientific publications.  For these reason, we believe it is worth considering moving beyond Excel, especially when you need to archive your data. But, Excel remains a standard and is useful enough that it is worth learning how to work with it programmatically.
\end{mdframed}


\section{Solution}
\setcounter{subsection}{-1}  % Start subsections from 0

\subsection{Planning}
``OK,'' \first says. ``Here is what you are going to want to do.''
\begin{enumerate}

    \item Understand the structure of the Excel file you start with.  This way, you can know where to read data from and where you can write data to. In this case, you have data in Columns A and B. 

    \item Identify the structure you want for your final Excel file. Basically, what do you want to report and how do you want to structure it? This includes any formatting you might like. 

    \begin{itemize}
        \item You want to write the results of your fitting into Column C.  You want to color these cells red, and outline them, to match the other cells. 
            
        \item You also need to add in the extracted p$K_a$ and its uncertainty into the purple cells.  You will need to ensure the text is white, in order to see it against the purple. 

        \item Finally, you will want to modify the existing plot, to add in the fit as a line that is color coded to the fill of the cells.  
    \end{itemize}
    


    \item You want to identify how you will get all this information.  Since you are working with a file that has data, you can extract the data from the file, and then fit it using the fitting routine you already wrote. 

    \item Once you have the data and the fit, you can modify the Excel file. 

\end{enumerate}

\noindent ``Of course, there are a few new things to learn."






%
% openpyxl
%
\subsection{Reading and writing excel files using openpyxl}
\label{Reading_and_writing_excel_files_using_openpyxl}

We are going to want to write an Excel file, but this is not as simple as writing a text file.  The reason is that there are all sorts of useful things that Excel files can do, which simple text files cannot.  For instance, we can fill cells, add colors, add formulas, add data visualizations, etc. This all means that the data stored in an Excel file is encoded in a way that is not easily understood by humans. 


Fortunately, there are Python \acite{libraries} that can handle interfacing with Excel files.  We are going to use openpyxl, and probably the simplest way to illustrate its usage is just to write the code we need for this chapter. As a note, we are going to assume you have some familiarity with Excel, specifically with entering data, entering formulas, and creating charts.  If you have not yet learned to do this in Excel, then you may benefit from reading or watching some basic Excel tutorials. Also, it is worth a quick discussion of the structure of an Excel file, to make sure we are on the same page.  

If you open up a Microsoft Excel file, you will notice a few things. 
\begin{itemize}
    \item Across the top, the columns are identified by capital letters: A, B, C, ... AA, AB, ...

    \item Across the left side, the rows are identified by integers, starting with the number 1. 

    \item On the bottom there is a tab for the current sheet, and it contains the name of the sheet.  By default this is ``Sheet1'', but you can change it.

    \item There can be many different sheets in the same file.  By default, there is only 1, but we can add more.  The collection of sheets is known as the Excel workbook.
\end{itemize}
Mirroring this structure, the Excel .xlsx file (and thus OpenPyXL's internal structure) is organized in a Workbook$\rightarrow$Worksheet$\rightarrow$Cell hierarchy.  An overview of this hierarchy is shown in Figure \ref{fig:Excel:Heirarchy}.

\begin{figure}[h]
    \centering
    \includegraphics[width=1.8in]{ChapNarrative/09Excel/Figures/Workbook.png}
    \includegraphics[width=1.8in]{ChapNarrative/09Excel/Figures/Worksheet.png}
    \includegraphics[width=1.8in]{ChapNarrative/09Excel/Figures/Cell.png}
    \caption{A brief overview of the organization of an Excel file.}
    \label{fig:Excel:Heirarchy}
\end{figure}

``Excel holds values, formulas, etc, within individual cells, and these cells have addresses that are given by identifying the workbook they are in, the sheet they are in, as well as the column and row, in that order. You can think of this a bit like a directory structure, where the address of any cell is given by: <workbook><sheet><COLUMNrow\#>. Each individual cell also can have formatting applied to it.  We can add a fill color, change the color of the text, add borders, etc.  All of these can be thought of as attributes of the cell.  Additionally, we can also add `charts' which is Excel's name for data visualizations.  These are used to plot data referenced in the workbook."

\subsection{Specifying colors using the hexadecimal standard}
\label{Specifying_colors_using_the_hexadecimal_standard}

\footnote{See Section \ref{Specifying_colors_using_the_rgb_standard}}When you were making colored chemical drawings, you saw that we can specify colors using the rbg or rbga standards.\footnote{See Section \ref{Specifying_colors_using_the_rgb_standard}}  This is not the only standard, and here we are going to have to use \cite{hexadecimal} representations of colors.  These are actually easy to understand, if we first go back and consider the rgb standard. In the rgb standard, we provides values for the red, green, and blue channels. These span values that often run from either 0--1 or from 0--255. 

For people new to coding, 255 might seem like an arbitrary number, but it is not.  Computers work in base 2. That is computers only have 2 digits to work with: 0 and 1. In this base, the number 3 is represented as 11. If you have 2 number places, you can represent up to 4 numbers: 00, 01, 10, and 11. If you had three number places, you could have 8 numbers.  In general, you can represent $2^n$ numbers, when you have $n$ number places. Thus, powers of 2 have special significance, and it turns out that there are 256 values of 0--255, or $2^{8}$ numbers.

If there are $2^{8}$ numbers, then there are also $16^2$ numbers. You can see this by noting that $2^8 = 2^{4+4} = 2^4\times2^4 = 16\times16 = 16^2$. If we made a square grid that was 16 numbers on a side, then we could put all the values from 0--255 in them. If we then assigned each row and column an identifier, we could represent each of the 256 values using just two identifiers---which might prove quite convenient. 

Easy identifiers would be numbers, and because there are 16 rows and 16 columns to identify, we can use the hexadecimal system, which contains the digits: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, a, b, c, d, e, and f. And now, we can represent the full range of 256 values using only 2 numbers.  In this approach, we can run from a value of 0 (\lstinline{00}) to a value of 255 (\lstinline{ff}). 

Remembering that we need values for red, green, and blue, we just repeat this though process for each, meaning that we can simply put together three pairs of such identifiers to get the values. This will mean that each color in the rgb standard can also be specified using 6 digits of hexadecimal number, or three pairs of digits. The first pair is the red value, the second the green value, and the third the blue value.  It is convention to indicate this is a hexadecimal number by placing the \# sign in front.  So, \#000000 is black (no intensity of any color), \#ff0000 is the brightest red (full intensity red, no intensity of any other color), \#ff00ff is magenta (full intensity of red and blue), \#ffffff is white (all three primary colors at their full intensity), and so on.  There are online converters to go between rgb and hexadecimal colors, or you can use color choosing tools in Microsoft Office or other programs.  It is also worth noting that hexadecimal colors are sometimes called `HTML' as they are widely used in designing web pages. 

\medskip

\section{Code}
\setcounter{subsection}{-1}  % Start subsections from 0

``And now we are ready to produce the Excel file for our advisor! Of course, you can write the file one bit at a time.'' \first says. ``If you know how to write the information for the p$H$ titration data, you really know how to write the data for the UV-vis data as well. So why don't I show you how to write the titration sheet and then you can try to do the rest on your own?

\begin{figure}[H]
    \centering
    \includegraphics[]{ChapNarrative/09Excel/Figures/FlowChartCh15.pdf}
    \caption{Flowchart for code that will read in data from an Excel file and then modify this file to include the results from Chapter \ref{05CompFit}.}
    \label{fig:Excel:ActivityDiagram}
\end{figure}

You work together to write the following script:

\lstinputlisting[]{../../Distribution/Code by Chapter/Ch15 - Reading, Editing, and Writing Excel Files/TitrationReportExcel.py}

You run this code, point it to where to find TitrationModel.py, select the file `titrationReport.xlsx in the Catalyst/Titraton/pH folder in your computer, and it outputs an Excel .xlsx file.  Opening the file, you see the following:

\subsection{Line-by-line explanation}

\noindent \textbf{Line} | \textbf{Explanation}

\begin{footnotesize}
\begin{itemize}
    \item [7--12:] Import the libraries that we will need. In addition to the familiar \lstinline{numpy} and \lstinline{lmfit} libraries, we also have three imports for dealing with Microsoft Excel.  The first is a function we use so we can load workbooks.  The second imports components of the module that deals with charts, while the last imports components that allow us to style the cells in the workbook. We will also use \lstinline{importFromPy()}, \lstinline{quickOpenFilename()}, \lstinline{quickSaveFilename()}, and \lstinline{quickPopupMessage()} from \lstinline{codechembook.quickTools}.

    \item [16--17:] As the user to find the Excel workbook containing the data and store the path to that file.

    \item [18:] We use the \lstinline{load_workbook()} function, and provide it the path to the Excel workbook we wish to use. 

    \item [19:] We then select the sheet within the workbook we want to use.  We can reference this using a key-type formulation, referencing the name of a sheet within the workbook.  If this were a default Excel workbook, then the name would be "Sheet 1".  However, the sheet had been renamed by your advisor, so we reference it using this name. 

    \item [22:] It is time to get the data from the Excel workbook.  We want two empty lists that we can add data to as we read it cell by cell.  We will eventually fit the titration model that we developed in Chapter \ref{06AnalFit} to this data.  We create these lists by having two empty lists that we \ul{unpack} to two different variable names. 

    \item [23--28:] We want to pull the data from the Excel file, and it is held in Column A and Column B of the worksheet.  We can reference these columns using them as a key. The default behavior is to return an \cite{iterable} collection of all the \textit{cells} that have values in them. Since Column A and Column B have the same number of entries, they will return collections of the same length. Thus, we can synchronize our iteration through these collections using \lstinline{zip()}. We assign the values we unpack to two variables: \lstinline{eq} and \lstinline{pH}.
    \begin{itemize}
        \item [24:] We are going to want to add the data as \textbf{floats} to our lists.  The first entry is not a number (it is a heading), and so we are not going to be able to convert it to a float.  One way to avoid this would be to slice out the first value in the collection (i.e., \lstinline{ws['A'][1:]}).  However, we would need to change the slice if the structure of the worksheet changed.  A more robust way of handling this would be to use the \lstinline{try... except...} \cite{keyword}.  Inside the \lstinline{try} block, we attempt to convert the entry to a float. If this works, then we will end up appending a number. If it fails (as when we have strings and not numbers), then we will enter the \lstinline{except} block, where we use the keyword \lstinline{pass} to continue with the loop. In this way, we can handle the header without needing to slice. 

        \item [22--23:] For these lines, we get the value in the cell using the \lstinline{.value} \cite{attribute} of our cell. We then attempt to convert this to a float.  If we can convert to a float, then we end up appending it to the growing list holding our values of interest. 
    \end{itemize}

    \item [31:] Using the \lstinline{Model()} function from \lstinline{lmfit} we convert our imported \lstinline{pH_response()} function to a model object, specifying that the \lstinline{eqs} parameter is the independent variable for the model. 

    \item [34--35:] We create a parameters object and then add the fit parameter to it. This is the same as was done in Chapter \ref{06AnalFit}. 
    
    \item [38:] We perform the fit of the \lstinline{pH_response()} function to the data we took from the Excel file. The usage is the basically the same as was shown in Chapter \ref{06AnalFit}, except here we obtain the initial amount of base from the first entry in our equivalents list and use it directly as an argument in the function call rather than initializing a new variable for it.  At this point, we have the fit result object, and are ready to add it to our Microsoft Excel worksheet. 

    \item [41:] Using the \lstinline{Font()} function, we supply a color we wish to use for the font of the cell's text, and then assign this to a variable. The color is white and the \lstinline{Font()} function represents this in a way that Excel can use internally.   In this way, each time we want to use this color, we can simply use the variable. 
    \item [42:] Using the \lstinline{PatternFill()} function, we create a pink-ish color for the best fitting line and the cell area for the fit data.
    \item [43--46:] Using the \lstinline{Border()} function, we generate information about the border we want and assign that to a variable.  This way, we can use the border construction simply by using the variable name. In this case, we are using a series of \cite{keyword_argument}s to specify the formatting of the border on the four sides. Each of these keyword arguments is also a function that accepts a keyword argument about the style of the border.  In this case, we make all borders thin. The reason to include these borders is that, when we color in the fill of a cell, we will no longer see the default borders that excel shows.  So we have to add in our own. 

    \item [49:] Using the address of the cell as a key for our worksheet, we assign the string \lstinline{'fit'} to the cell.  This will serve as a header for the fit values in column C. 

    \item [50:] We set the \lstinline{fill} attribute of the cell to our fill color variable.  This will fill the cell with that color. 

    \item [51:] We now use the \lstinline{border} attribute of the cell and set it equal to our previously created border variable. 

    \item [52--54:] The cells that hold our parameter value and uncertainties did not have headings.  We use these three lines to add them
    
    \item [57--61:] Use \lstinline{for}---\lstinline{enumerate()} to go through each item in \lstinline{pH_fit.best_fit}, which is the best fit value for each dependent variable data point (i.e, equivalents added). We wish to add each variable to a new cell, moving down the row.  By using \lstinline{enumerate()} we will get indices for each value in \lstinline{pH_fit.best_fit}.  We can use this to assign values to the correct cells in the column. We will unpack the index and the value into two variables: \lstinline{i} and \lstinline{value}.

    \begin{itemize}
        \item [58:] We add the value to the cell.  We use \cite{f_string}s to convert the value of the index to a number that we can use in the key.  We add 2 to the value of the index, since index counting starts at 0, and we wish to add the first value to the second row (counting in Excel starts at 1). 

        \item [59:] We set the format for rounding the number in the cell using the Excel format string, which gives a pattern to follow using only zeros.  Here, we want three decimal places, so we use `0.000' to pattern it.

        \item [60:] We assign the fill color to the cell. 

        \item [61:] We assign the border to the cell
    \end{itemize}

    \item [64:] We next assign the value of the best-fitting parameter to the cell E2.  We do this inside of an f-string, using the \lstinline{value} attribute of the Parameter object representing p$K_a$ in the \lstinline{params} dictionary. We also use the formatting \lstinline{:.3f} to make the value a float that has three decimals of precision. 

    \item [65:] We repeat the above, but for the uncertainty in the parameter, using the \lstinline{stderr} attribute.

    \item [66--67:] The cell F2 was not yet purple, and the font was not yet white. So, we make sure the font color will be white (so we can see it against the dark purple) and that the cell is dark purple. For the patter, we directly use the \lstinline{PatternFill()} function, without assigning it to a variable.

    \item [70:] Before we can add anything to the chart, we have to identify where it is.  This is done by accessing the collection of charts and pulling out the one we are interested in. In our sheet, there is only a single chart, and so we just get the first item in the list of charts.  For convenience, we assign this to a variable. 

    \item [73--74:] In order to add data to the chart, we need to identify what that data is.  We do this by setting up a reference to the data, using the \lstinline{Reference()} function. This function accepts a \cite{positional_argument} that is the worksheet where the data resides, followed by four keyword arguments that specify the minimum and maximum row and column addresses for the data. This defines a 2D grid of data that will be used as a reference.  For the $x$-values and $y$-values we only need a column, and we start at the first row and end at a row that is equal to the length of our fit data + 1.  The +1 is again because Excel starts counting rows at 1. 

    \item [77:] We create a series, which is an object that can be added to a chart in Excel.  We use the \lstinline{Series()} function for this, supplying two positional arguments ($y$-data and then $x$-data) as well as if we want to include a title (i.e., a legend entry). In this case, since we are color-coding the data and the line in the chart, we elect not to include the title in the legend. 

    \item [80--81:] We format the series we have created.  In line 80, we specify that we want a line with a solid fill, and then provide the hex code for the color. In line 81, we specify the width of the line in EMUs, which is an internal measurement used in Microsoft office. 

    \item [84:] We add the series to the chart by appending it to the series attribute of the chart object. 

    \item [87--89:] We save the workbook.  First, we ask the user to pick a file name, then we use the \lstinline{save()} method of the \lstinline{Workbook} object to save a new file.  The supplied positional argument is the path to the file to be saved.  If you used the same path as was used when you opened the file, then it will be overwritten (i.e., normal behavior when saving an Excel file).
\end{itemize}
\end{footnotesize}

\begin{figure}
    \centering
    \includegraphics[width = 4in]{ChapNarrative/09Excel/Figures/ExcelEnd.png}
    \caption{Screenshot of an Excel file after  running the code above. This added results from Chapter \ref{05CompFit} and formatted the Excel file, all using Python.}
    \label{fig:Excel:ExcelAfter}
\end{figure}

``I guess this is how you get so much done," you say to \first, ``you don't spend your time on little annoying things like this!"

``I know!  Our advisor always talks about how productive I am---I can never tell whether I should let them know that this is what I'm doing, or just let them think I work all the time!"

\hrule
\input{Exercises/Chapter 15 Excel}
\hrule

\end{document}
