Write DataTable to Excel file in Coded Workflow (WriteRange)

Help Activities
, , ,
1

My current approach to write a DataTable into an Excel file looks like this:

    private void TestFunction(DataTable dataTable)
    {
      var tempFilePath = CreateTempFilePath("test.xlsx");
      Log("Creating Excel file: " + tempFilePath);

      var sequence = new System.Activities.Statements.Sequence();

      var excelScope = new UiPath.Excel.Activities.ExcelApplicationScope() {
        CreateNewFile = true,
        WorkbookPath = tempFilePath
      };

      var writeRange = new UiPath.Excel.Activities.WriteRange() {
        AddHeaders = true,
        WorkbookPath = tempFilePath,
        SheetName = "Sheet",
        DataTable = dataTable
      };

      sequence.Activities.Add(excelScope);
      sequence.Activities.Add(writeRange);

      var workflowInvoker = new System.Activities.WorkflowInvoker(sequence);
      workflowInvoker.Invoke();
    }

However, the following Exception is thrown on execution:

The following errors were encountered while processing the workflow tree:
‘Literal’: Literal only supports value types and the immutable type System.String. The type System.Data.DataTable cannot be used as a literal.

1 Like
2

Along with any other fixes and improvements we recommend using for variables / arguments names that are different from keywords, classes …

1 Like
3

Thank you for the quick response!
Unfortunately, renaming all the variables didn’t help.

4

so we can approach within the next steps

  • checking the decission on usage classic-EAS, modern Excel
  • and then focus on the remaining blockers
5

What do you mean by that?
If there’s another / better way of doing this, I’d ask kindly for an example or a hint how it’s done.

6

@mschlosser Please try the following:

private void TestFunction(DataTable dataTable)
{
var tempFilePath = CreateTempFilePath(“test.xlsx”);
Log("Creating Excel file: " + tempFilePath);

// Define the workflow application and the required activities
var sequence = new System.Activities.Statements.Sequence();

// Create an Excel scope activity
var excelScope = new UiPath.Excel.Activities.ExcelApplicationScope()
{
    CreateNewFile = true,
    WorkbookPath = tempFilePath
};

// Create a WriteRange activity and configure it to use a variable for the DataTable
var writeRange = new UiPath.Excel.Activities.WriteRange()
{
    AddHeaders = true,
    WorkbookPath = tempFilePath,
    SheetName = "Sheet",
    DataTable = new System.Activities.Variable<System.Data.DataTable>() { Default = dataTable }
};

// Add the Excel scope to the sequence
sequence.Activities.Add(excelScope);
// Inside the Excel scope, add the WriteRange activity
excelScope.Body = new System.Activities.Statements.ActivityAction()
{
    Handler = writeRange
};

// Create a workflow invoker and execute the sequence
var workflowInvoker = new System.Activities.WorkflowInvoker(sequence);
workflowInvoker.Invoke();

}

1 Like
7

I had to make some changes to make it compile / run but it still doesn’t solve the problem.

This is the updated code with your suggestions:

private void TestFunction(DataTable dataTable)
{
      var tempFilePath = CreateTempFilePath(“test.xlsx”);

      Log("Creating Excel file: " + tempFilePath);

      var dtVar = new System.Activities.Variable<DataTable>() { Default = dataTable };

      var sequence = new System.Activities.Statements.Sequence();
      sequence.Variables.Add(dtVar);

      var excelScope = new UiPath.Excel.Activities.ExcelApplicationScope() {
        CreateNewFile = true,
        WorkbookPath = tempFilePath,
      };

      var writeRange = new UiPath.Excel.Activities.WriteRange() {
        AddHeaders = true,
        WorkbookPath = tempFilePath,
        SheetName = "Sheet",
        DataTable = dtVar
      };

      excelScope.Body = new System.Activities.ActivityAction<UiPath.Excel.WorkbookApplication>() { Handler = writeRange };

      sequence.Activities.Add(excelScope);


      var workflowInvoker = new System.Activities.WorkflowInvoker(sequence);
      workflowInvoker.Invoke();
}
8

The error message still remains the same?

9

Pls try the

private void TestFunction(DataTable dataTable)

{
    var tempFilePath = CreateTempFilePath("test.xlsx");
    Log("Creating Excel file: " + tempFilePath);

    var dtVar = new System.Activities.Variable<DataTable>() { Default = dataTable };

    var sequence = new System.Activities.Statements.Sequence();
    sequence.Variables.Add(dtVar);

    var excelScope = new UiPath.Excel.Activities.ExcelApplicationScope()
    {
        CreateNewFile = true,
        WorkbookPath = tempFilePath
    };

    var writeRange = new UiPath.Excel.Activities.WriteRange()
    {
        AddHeaders = true,
        WorkbookPath = tempFilePath,
        SheetName = "Sheet",
        DataTable = dtVar
    };

    excelScope.Body = new System.Activities.Statements.ActivityAction<UiPath.Excel.WorkbookApplication>() { Handler = writeRange };

    sequence.Activities.Add(excelScope);

    var workflowInvoker = new System.Activities.WorkflowInvoker(sequence);
    workflowInvoker.Invoke();
}
1 Like
10

The only difference I can see here is:

excelScope.Body = new System.Activities.ActivityAction<UiPath.Excel.WorkbookApplication>() { Handler = writeRange };

vs

excelScope.Body = new System.Activities.Statements.ActivityAction<UiPath.Excel.WorkbookApplication>() { Handler = writeRange };

which doesn’t compile ("CS0234 The type or namespace name 'ActivityAction<>' does not exist in the namespace 'System.Activities.Statements' (are you missing an assembly reference?)")

Yes, same error.

11

@mschlosser You share some sample files with project and let me try the same in my system.

12

Sure, here’s a minimal project containing the discussed code:

WriteToExcel.zip (1,8 KB)

13

There are samples on GitHub on how to use Excel activities in Coded automations, have you checked those yet?

14

Thanks for pointing me to the GitHub repo! I didn’t know that at all and it’s a very useful resource!
I tried to use the code from the examples but for me even some of the namespaces aren’t recognized by UiPath (e.g. UiPath.Excel.Activities.API).
The example projects json contains "studioVersion": "24.4.0.0" but my version is 23.10.4. Maybe my version is too old? If so, where can I get the 2024 version? The product lifecycle page doesn’t contain it yet.

1 Like
15

I also noticed the alpha-versions in the dependencies:

"dependencies": {
    "UiPath.Excel.Activities": "[2.23.3-alpha.5435483]",
...

So is UiPath.Excel.Activities.API part of that new version and if so, where can I get it?

16

This doesn’t seem to be Excel related as even this single line doesn’t work:

var test = new System.Activities.Variable<DataTable>("testDataTable", new DataTable());

Same error message.