How to write Data into the Excel
This is a submission of the Enhance your bot building with templates blog post series. In this blog post, I will show you how to automate a process on writing data into an excel file.
Steps to follow:
1.Create a project
2.Include the Excel Script Library
3. Add Activities and functions from ‘Excel Lib’ category
4.Set the data that has to be written to the Excel.
5. Save the excel.
Pre-requisites: Desktop Studio :22.214.171.124 MS Office
1. Create a project and a workflow.
Create a new project. Create a new workflow using Workflow perspective.
2. Include the Excel Library for the project.
3. Add the activities from the Excel Lib.
As the first step in the workflow, include the Activity Initialize Excel to initialize Excel Library.
4. Create a new excel file.
Use the activity Create Excel file to create a new excel file. If you want to write data into an existing excel file use the activity Open Existing Excel file. In this blog, I am creating a new excel file.
5. Activate your worksheet to write data into excel.
6. Prepare Excel data
Create an activity Custom to write the below code. Custom activity is used to add manual code. The excel data should be in an array format. Create an item with an array in the Context. Click on the checkbox, to make the item as an array.
Prepare your Excel data in an array format as below.
rootData.aExcelData = ['Account ID','Opportunity ID','Status']; rootData.aExcelData = ['4892737','1223','Open']; rootData.aExcelData = ['4358635','987','Closed']; rootData.aExcelData = ['68989','8999','In Process'];
6.Set the data into excel.
Use activity Set Values to write the values to the excel. Enter the parameters as below. Excel has a starting column as A and starting row as 1. The data that has to be written to the excel is bound to the field Data.
7. Save the excel
Use the activity Save as Excel to save the new excel file. Enter the location and name of the excel file to be saved.
8.Close the Excel File
Use the activity Close Excel File to close the existing excel file.
9.End Excel file
Use the activity End Excel in order to close the Excel Library once you are done using it in your project
10. Final workflow looks as below
The file is saved in the mentioned location with the output as below.
This blog post should help you to understand the use of the ‘Excel Library’ and how to write data into excel.
I have included the excel library, but cannot see the Set Values or Activate Worksheet(only add worksheet, get Workbook,get worksheet is visible) activity but am able to see the others .
I am using the desktop studio version: 126.96.36.199.
Could you please let me know if I have missed anything ?
please update your version here:
Thank you it worked.
Thank you for sharing, I encountered some issues with the filepath in the Save as Excel File activity. I was able to resolve those issues by escaping the backslashes in the file path like c://User//Documents//SAP//IRPAProject//test.xlsx
Is it working now?Please let me know if you face any issue.I can help you out
Hi Chaitanya Priya
Have encountered the below issues while debugging,can you please help on this.
Please find my replies below.
1.Are you using the standard activity Add New Worksheet or did you use any custom code? Can you send the screen shot of the workflow.
2.At step 7,I am saving the excel, by giving a name to the excel and the folder path i.e the path where the excel has to be saved.
When I created the excel ,Create Excel Activity does not have the properties to give the name of the excel file .Hence we have to save the excel using Save As Excel Activity.
How do I write data from sap table to excel file. I created a variable for sap data as get values for all visible rows and passing into excel variable but it does not work. Could you let me know how this can be achieved.