Write Excel reports with formulas using Altair Compose

RSGarciarivas

Altair Compose is an environment for doing math calculations, handling data, automating workflows and much more. It is a very robust tool, much more suited than Excel for computationally demanding calculations due to the plethora of functions available through its libraries.

 

However, sometimes our priority is to have a solution that any person can handle and visualize, regardless of their expertise coding or the availability of a specific software. With this in mind, let’s go over a nice way of writing Excel files in which, instead of handling all computations in Compose, formulas present in their cells handle all data processing. In that way, any person can look at, and understand, the whole report and its processes by only reviewing the Excel.

 

First, let’s reset our environment with close all, clear, and clc. This is almost always a good practice. Let’s also create some random vectors, A and B, with rand and cast them into cells (instead of matrices) for writing into Excel more easily. We use mat2cell for this.

 

close all, clear, clc

 

% Data to write & handle in Excel

n = 6;

A = mat2cell(rand(n,1), ones(1,n));

B = mat2cell(rand(n,1), ones(1,n));

 

Apart from A and B, let’s create cell C, initially empty, and fill it out in a for loop with strings ‘= A1 + B1’, ‘= A2 + B2’, …, ‘= An + Bn’. Square brackets [ ] are used for concatenating strings, among other things like defining a matrix, and num2str casts a number into string format so we can incorporate it.

 

C = cell(n,1);

for i = 1:n

      C{i} = ['= A', num2str(i+1), ' + B', num2str(i+1)];

end

 

Now that the data is ready, let’s define the name of the Excel file to write and open it for writing using xlsopen. Let’s also arrange our data by concatenating some headers with A, B and C. We can then write those contents using xlswrite.

 

% Declare output file name and open it in write mode

filename = 'Compose-created Excel Report.xlsx';

fid = xlsopen(filename, 1);

 

% Contents to be written into cells A1, B1 & C1

contents = [{'Column 1', 'Column 2', 'Total'}; A, B, C];

 

% Write contents into file and close it

xlswrite(fid, contents);

 

Before closing the file. Let’s add one last calculation by formatting a string to add up all values from column C. In this call to xlswrite, given that I wrote 6 random values in columns A and B (n = 6), I’m writing ‘=SUM(C2:C7)’, specifying I want this to be written in sheet 1 and in cell C8.

 

xlswrite(fid, ['=SUM(C2:C', num2str(n+1), ')'], 1, ['C' num2str(n+2)]);

xlsclose(fid);

 

If you open the file, you will see a table like this:

 

Column 1	Column 2	Total
0.548813502	0.544883177	1.09369668
0.592844617	0.847251737	1.440096354
0.715189365	0.423654797	1.138844162
0.844265744	0.623563697	1.467829441
0.60276337	0.645894115	1.248657486
0.85794562	0.384381708	1.242327328
		7.631451451

 

Upon examination of each cell in column C, you will be able to look at the formulas we defined.

 

This is a heavily oversimplified example, but it works for showing off how to handle the process. Much more complex reports can be created with these commands. Try it for yourself and share your reports with anyone!