I would explain how to write to a specified range in a templated Excel file on this post.
Generally speaking, many people want to make a beautiful formatted Excel file. In these cases, there are three approaches when we use Alteryx.
- Output the formatted contents using the Reporting tools
- Overwrite the range of templated Excel sheets
- Use BI tools(Alteryx output the data source for BI tools)
Pattern 1 is using the reporting tools of Alteryx. We can make tables, charts, texts and maps and then, place them on the report and output it. However Alteryx does not provide the all representations which Excel can. For example, Alteryx can not handle the merged cells or change the size of cell.
I think the best way is using BI tools(pattern 3). But in some cases, it is not the best way. Otherwise, many people still use Excel. So I would explain about pattern 2.
Range writing to a formatted Excel file by Alteryx
In this post, I would like to automate the filling value to templated Excel file bellow.
例えば、以下のようなフォーマット化されたExcelファイルに対して値を埋めるのを自動化したいとします。
In this case, you can use Output Data tool with “Overwrite Sheet or Range” and “Preserve Formatting on Overwrite(Range Required)” options to fill the empty cells.
The Range option must be done in this case. So when you specify the sheet name on the “Select Excel Output” dialogue, you should to check “Specify a range(cells, rows or columns)” and specify the range that you want to fill.
After you run the workflow, it will fill the values as follows.
Other Notes
There are some notes for this way.
- Empty data breakes the format
- Occur the error when you overwrite the cells contain Formulas
- Copy templates and overwrite them
Empty data breakes the format
Originally we prepare the 6 rows of spaces to write the data, however if there is only 3 rows in this time, Designer partially breaks the format in Excel file(there is sometimes not break the format).
For this case, we need to shrink the range to write or keep the range filling with null data. If you want to shrink the range dinamically, you can use Output Data tool with “Take File/Table Name From Field” option or use batch macro.
Occur the error when you overwrite the cells contain Formulas
If there are the cells which contain any formulas in the range of overwrite, Designer show the error bellow.
In this case, we should overwrite except for the range which contain formulas. Or, we can overwrite by Excel formulas. In other words, we can make the Excel formula as string in Designer and overwrite the Excel by them. But you can not overwrite numeric values and Excel formula in the same field because the numeric values are changed into string type in Excel. I would explain more detail about it.
In Designer, I made the data as follows.
As the screen shot, the numeric data is changed into string type. As a result, the Excel formula can not work.
For this case, we can separately overwrite values and Excel formulas.
Also, please note that minor movements may vary with different versions of Designer because the small bug is fixed day and day. This post is based on Designer version 2022.3.1.395 (in particular, older versions do not have the option to preserve formatting).
Copy templates and overwrite them
You can do this task using Blog Input, Blob Output and Output Data tools. Or you also use Run Command tool instead of Blod Input/Output tools.
If you want to copy specific sheets of template Excel and overwrite copied one, you need to use Python tool.
Sample Workflow Download
*This workflow uses “Data Science 100 Knocks (Structured Data Processing Edition)” by the Skill Definition Committee of the Data Scientists Association of Japan
Contents of the next blog
In the next post, I will explain a simple tip for writing to multiple files at once (as I am sure you all know).
コメント