Skip to Content
Technical Articles

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 :1.0.8.36 MS Office

Instructions:

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[0] = ['Account ID','Opportunity ID','Status'];
	rootData.aExcelData[1] = ['4892737','1223','Open'];
	rootData.aExcelData[2] = ['4358635','987','Closed'];
	rootData.aExcelData[3] = ['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

Output

The file is saved in the mentioned location with the output as below.

Conclusion

This blog post should help you to understand the use of the ‘Excel Library’ and how to write data into excel.

 

7 Comments
You must be Logged on to comment or reply to a post.
  • Hi Chaitanya,

    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: 1.0.5.57.

    Could you please let me know if I have missed anything ?

     

    Thank you

    Narayani

     

     

  • 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

  • Hi Chaitanya Priya

    Have encountered the below issues while debugging,can you please help on this.

    1. During the sheet creation the below error throwing ,
    2. At step 7 you are passing the path with excel file name ,do you manually created the excel file in the folder ?? if yes what about the create file activity?

    Please elaborate.

    Thanks,

    Avinash Y

    /
    • Avinash Y

      Hi Avinash,

      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.

      Regards,

      Priya