Edit And Update Excel DocumentLast Updated: 07/15/2016 Introduced in Verision: 2.0 |
This tutorial demonstrates how to use specific Steps in the Flow Designer to get values from an Excel Document, and how to Update values in the Excel Document (Set Values, Replace Values, Delete Row, etc.) These Steps can be found in Data > List > Excel and CSV category in the ToolBox.
Example:
We begin in the Designer Folder with clicking Create Flow option from the Folder Actions panel.
Next, we Name the Flow and click Create to proceed to the Flow Designer.
In the Flow Designer we are going to use Create Or Copy Data step to get an Excel Document. In the Flow Designer Wizard we can use Search Bar to locate Create Or Copy Data Step.
After we clicked Add for Create Or Copy Data step, we need to configure it. In the Data Definitions pop-up window we click Add New link to define our Data.
Then, we give our Data Name, and define its type as FileData. When finished, we can save and close this Data Definition window.
Our Create Or Copy Data step configurations should look as following… We can click Add to continue.
Now, we can pick an Excel Document for the File variable we have just defined. The Excel Document we will use in this tutorial looks as follows:
To pick the Excel Document we navigate to the Properties of Create or Copy Data step. We locate Inputs configurations and define a Mapping type for the File variable as Constant. Next, we use Value picker to pick the file we want to use.
When we picked the file, its Name appears in the Value box…
Next, we add Get Line Count step from All Steps [Catalog] > Data > List > Excel and CSV category in the Flow Designer Wizard. We add this step next to Create or Copy Data step in our Flow. This step returns the number of Rows in the Excel Document.
Then, we need to map data for this step. We choose Show Mapping Editor option from this step’s Actions menu.
In the Mapping Editor we use our File from Create or Copy Data step as an input field. Next, we define file type, and disable ignoreFirstRow field. When finished, we click Ok to save and close Mapping Editor.
Back in the Flow Designer we add Get Row step from All Steps [Catalog] > Data > List > Excel and CSV category in the Flow Designer Wizard. This step returns data in the desirable Row.
To configure this step we call Mapping Editor, like we did for the previous step. Data mapping is similar to the previous step except we need to provide this step with the desirable Row Number. Rows in this step are zero indexed. In this example we want this step to return first Row from our Excel Document so we input 0 as the Value for the Constant rowNumber field. Then, we save and close Mapping Editor.
Back in the Flow Designer we add to our Flow Get Value step from All Steps [Catalog] > Data > List > Excel and CSV category in the Flow Designer Wizard. This step gets data from the desirable cell in the Excel File.
In the Mapping Editor for this step we use our Excel File as an input field. Next, we define row and column number to point to the desirable cell in the Excel Document. We set ignoreFirstRow field to False. Click Ok to save and close Mapping Editor…
Back in the Flow Designer we add to our Flow Get Row As Array step from All Steps [Catalog] > Data > List > Excel and CSV category in the Flow Designer Wizard. This is another option to get data from the desirable Row in the Excel File. In this case the Step returns an Array of Strings. Each String in this Array is a single Value from each column in the Row.
Again, we call Mapping Editor to map data for this Step. Data mapping is the same as for Get Row step. In this example we want to return second Row so we input 1 for rowNumber field. Click Ok to save and close Mapping Editor.
Next, we need to create a Form for our Flow to display the information that these steps return from the Excel Document. We use [Pick or Create Form] step from Flows, Rules, Forms and Reports > Forms[Interaction] category in the Flow Designer Wizard.
After we added [Pick or Create Form] step we click Pick or Create Form link. Then, in the resulting pop-up window we Name our Form and click Create to proceed to the Form Designer.
Our Form Design is simple. We use Label components from Data category to indicate what Data we are displaying. Next, to display actual values, we use Create label (value) components from Flow Data category under the respecting Flow Data subcategories that are available. To display the Array of Strings that our Get Row As Array step returns, we use Simple List component from List category in the ToolBox. We configure this list as following… We define its Type String and provide the required field values. Finally we used Button component from Actions category as an outcome from our Form. When we finish our Form design we can save the Form and close the Form Designer.
Back in the Flow Designer we use Mapping Editor to map data to our [Pick or Create Form] step. In the Mapping Editor all data should be mapped except for our Simple List component. We connect the outcome from Get Row As Array step to our RowAsArray list on the Form. Then, we save and close Mapping Editor.
Next part of this tutorial demonstrates how to edit Data in the Excel Document. First, we are going to add Delete Row step from All Steps [Catalog] > Data > List > Excel and CSV category in the Flow Designer Wizard. This step deletes a desirable Row from the Excel File.
We call Mapping Editor for this step to map data. In the Mapping Editor we use our Excel File as an input field. Additionally, we define fileType, disable ignoreFirstRow option and define the desirable rowNumber that we want to delete. Row Numbers are 0 indexed. In this example we define rowNumber 3 because we want to delete fourth Row from our Excel File. Save and close Mapping Editor, when finished.
Back in the Flow Designer we add Replace Value In Column step from All Steps [Catalog] > Data > List > Excel and CSV category in the Flow Designer Wizard. This step replaces value in the specified column in the Excel Document.
We need to specify column for this step and a new value we want this step to replace with. Select Show Mapping Editor from step’s Options Menu. In the Mapping Editor we use our Excel File as an input field. Next, we define fileType and a column number. Mention, that for this step we need to define oldValue and newValue. This step iterates through the values in the defined column and if it finds oldValue, it replaces this value with newValue. When we finished mapping data we can save and close Mapping Editor.
Next, we add Set Numeric Value step from All Steps [Catalog] > Data > List > Excel and CSV category in the Flow Designer Wizard. This step is designed to set desirable numeric value for the specific cell in the Excel Document. This value will be treated in the Excel File as a number.
We call Mapping Editor for this step to configure it. Data Mapping is simple for this step. We use our Excel File as an input field. Then, we define column and row number where we want to set numeric value. Finally, we define desirable value. Click Ok to save and close Mapping Editor.
If the value we want to set for the cell in the Excel Document does not have to be numeric, we can use Set Value step. We add this step from All Steps [Catalog] > Data > List > Excel and CSV category in the Flow Designer Wizard.
Data mapping in the Mapping Editor for this step is exactly the same as for Set Numeric Value step except this time we use a text string for newValue field. Then, save and close Mapping Editor.
If we want to set calculated value in the Excel Document, we can use Set Formula Value step from All Steps [Catalog] > Data > List > Excel and CSV category in the Flow Designer Wizard.
We call Mapping Editor for this step. In the Mapping Editor we use our Excel File as an input field. Then we define column and row number. Finally, in the newValue field we type desirable formula for the Excel Document. Then, we click Ok to save and close Mapping Editor.
Back in the Flow Designer we add Supply Default For Column step from All Steps [Catalog] > Data > List > Excel and CSV category in the Flow Designer Wizard. This step is designed to set desirable Default values in column for the fields that are empty.
Data mapping is simple for this step. We use Excel File as an input. Then, we define column number and provide defaultValue field with desirable Default value. Then, we save and close Mapping Editor.
Now we have enough Excel Document steps covered in this tutorial. We need to update our Excel File with all changes we have done. For this purposes we add Update File With Data step from All Steps [Catalog] > Data > List > Excel and CSV category in the Flow Designer Wizard.
We can use Properties panel for this step to map the Data. We locate Inputs section in the Properties and define fileType as Constant. Finally, for the input field we select Mapping type Select Value, and use Path picker to select our Excel File.
Lastly, we need to add a Form to our Flow for user to be able to download our updated Excel Document. We use Download File Form from Flows, Rules, Forms and Reports > Forms[Interaction] > [Current Folder] category.
This Form was predesigned for this tutorial. It simply has two controls on it. First, it is a Button control from Actions category. Second, it is File Download component from File Handling category. File Download component configured as following…
Back in the Flow Designer we need to map Data for our Form step. We navigate to this step’s Properties and locate Inputs section. In the Inputs section we select Constant Mapping type for DownloadFile control and use Path picker to select our Excel File.
We connect the steps in our Flow as follows. Next, we can click Debug Flow link on the Top Panel in the Flow Designer to test our Flow.
Note that in Decisions version 3.5 and higher, you’ll need to click Test Flow to access the Debugger.
Our First Form opens in the Debugger. We can see that it displays all information from our Excel Document as we designed. We click Done to close the Form and continue Flow execution.
Next, our Download File Form opens in the Debugger. We click Download File control.
Then, we name and Save our updated Excel File…
Decisions informs us that File was saved…
Next, we close our Download File Form by clicking Done button…
In the Diagram Tab in the Debugger we can see that our Flow ran to the End Step with no errors.
We can locate and open our new Excel Document to observe all changes we have done to it in the Flow.
