Skip to main content

Excel Driver in Eras

RPA is often used to automate tasks that involve working with Excel spreadsheets, because Excel is a widely used tool for data analysis, management, and reporting. By using RPA to automate Excel tasks, organizations can save time, reduce errors, and increase efficiency in their processes.

Examples of Excel-related tasks that can be automated using RPA include:

  • Data entry and validation: RPA can be used to automatically enter data into an Excel spreadsheet, validate the data for accuracy and completeness, and flag any errors or discrepancies.
  • Data manipulation: RPA can be used to sort, filter, and manipulate data in an Excel spreadsheet in order to prepare it for analysis or reporting.
  • Reporting: RPA can be used to automatically create and format reports in Excel, including generating charts, tables, and pivot tables.
  • Data extraction: RPA can be used to extract data from Excel spreadsheets and use it to update databases or perform other tasks.
  • Data comparison: RPA can be used to automatically compare data in two or more Excel spreadsheets and flag any discrepancies.

CreateExcelFile​

This method allows users to create excel file.

Parameters

  • json - List<object> = Data to be written to excel file.
  • path - string = Folder where the file will be created
How to use
var data = [
{ name: "Eras", surName: "Test" },
{ name: "Eras2", surName: "Test2" }
];

Excel.CreateExcelFile(data, "users.xlsx");

CreateCsvFile​

This method allows users to create csv file.

Parameters

  • json - List<object> = Data to be written to excel file.
  • path - string = Folder where the file will be created
How to use
var data = [
{ name: "Eras", surName: "Test" },
{ name: "Eras2", surName: "Test2" }
];

Excel.CreateCsvFile(data, "users.csv");

ReadAllSheetAsDataSet​

This method allows users to read all sheet in excel file

Parameters

  • path - string = The path of the file to be read by the robots
How to use
var ds = Excel.ReadAllSheetAsDataSet("C:\\Users\\Eras\\Desktop\\test.xlsx");

var dt1 = ds.Tables[0];
var dt2 = ds.Tables[1];

Excel.CreateExcelFileFromDatatable(dt1, "C:\\Users\\Eras\\Desktop\\test2.xlsx");

ReadSheetAsDatatable​

This method allows users to read specific sheet in excel file

Parameters

  • path - string = Folder where the file will be created
  • sheetName - string = The sheet name to be read by the robots
How to use
var dt = Excel.ReadSheetAsDatatable("C:\\Users\\Eras\\Desktop\\test.xlsx","Sheet2");

var cell1 = dt.GetItem(1, 1);

var result = ConvertToString(cell1);

InfoLog(result);

CreateExcelFileFromDatatable​

This method allows users to modify the excel file.

Parameters

  • dataTable - EraDataTable = Data to be written to excel file.
  • path - string = Folder where the file will be created
How to use
var dt = Excel.ReadSheetAsDatatable("C:\\Users\\Eras\\Desktop\\test.xlsx");

dt.AddFormula(3, 1, "=SUM(A1:A2)")
Excel.CreateExcelFileFromDatatable(dt, "C:\\Users\\Eras\\Desktop\\test2.xlsx");

CreateExcelTemplate​

This method returns EraDataTable instance.

How to use
var dt = Excel.CreateExcelTemplate();

dt.SetItem(0, 0, "From");
dt.SetItem(0, 1, "Body");

AddPivotTable​

This method returns EraDataTable instance.

Parameters

  • dataTable - EraDataTable = Data to be written to excel file.
  • path - string = Folder where the file will be created
How to use
var dt = Excel.ReadSheetAsDatatable("C:\\Users\\Eras\\Desktop\\test.xlsx");

dt.AddPivotRowField("Name");
dt.AddPivotDataField("WorkingHours", "Sum");
dt.AddPivotDataField("WorkingHours", "Average");

Excel.AddPivotTable(dt, "C:\\Users\\Eras\\Desktop\\testpivot.xlsx")