Excel generic - Save toggle + Save activity

i_completed
excel
activities
r_18_1

#1

Hi Team,

It could help in the long run to have the ability to toggle saving in excel activities, either per activity (preferred) or as a transaction-like scope.
There are use cases with legitimate reason to iterate over cells and modify something about them (latest being changing cell colors that are not a full range).
If there would be a boolean flag for those to not save immediately, it could save a lot of execution time. For compatibility reasons default should remain to save.
Major candidates for this addition would be WriteCell and SetRangeColor, but others could use it as well (AppendRange, WriteRange etc.).

As a secondary effect this would require an explicit ExcelSave activity, or other make-sure-its-saved mechanism.

While this may sound risky at first (users not saving their changes), with a proper default behaviour it would give more control and also allow effective manipulation of individual cells, which currently is a performance killer.

Thoughts?


When using "Write Cell" in excel application scope, it auto-saves every move
Edit Excel workbook without saving it
#2

I don’t know if I’m understanding this correctly, but in the meantime why not to collect all data and lastly work on the excel file all you might need, so you don’t necesary close it and therefore save it? - I know you have think about this :relieved:-

Because you are saying that after executing certain activity excel will save itself automatically? I am running big data and keep me saying that I am out of memory (8g ram, excel64b)

regards, :slight_smile:


#3

Totally agree with Andrzej

One simple option in Excel App Scope (for saving vs not saving automatically) would be very useful.

Per activity would be even nicer, but if it’s too complicated let’s start the easy way.


#4

Guys, are we still considering this? Another user asked me about it.


#5

Isn’t using 2 Excel scope (which does save) an acceptable workaround till then? Or i’m missing something?

Excel Scope 1
do something
(Save)

Excel Scope 2
(do something)
(Save)

Of course it will increase the complexity of the workflow.


#6

It’s not an issue with too infrequent saving - it’s the opposite.

Currently what it does:

Excel Scope 
{
	for (i = 0; i < 1000; i++)
	{
		WriteCell("A" + i, i); // <- saves after each write currently
	}
}

Possible improvement:

Excel Scope 
{
	for (i = 0; i < 1000; i++)
	{
		WriteCell("A" + i, i); // <- doesn't save
	}
	SaveWorkbook();
}

It would save (pun intended :wink: ) a lot of time.


#7

@Alex_Vasile @Lavinia is it so?


#8

From a quick test (and others findings as well), it is.
ExcelSaveTest.xaml (7.3 KB)

Execution times, .tmp files popping up, last file write time (if you put delays in or use a file watcher) etc. all confirm that it does. Can’t confirm 100% from source, though, for obvious reasons :wink:


#9

The file is saved after any writing or modification brought to it. It should be best to save once, at the end of the Excel Application Scope.
But I don’t think it will come up very soon. @Lavinia?


#10

What about Cancellation/Faulted when saving only at end?
However you decide it will be best, please add a specific documentation to it - right now it’s simple, it might not be in the future if you give more control to the designer :slight_smile:


#11

ok. I moved it to the development inbox.


#12

Though why wouldn’t you use a WriteRange instead of 1000 WriteCell?

move it back to considering :slight_smile:


#13

You know it was a contrived example just to illustrate functionality :wink:

It’s not always feasible to operate on continous, rectangle ranges. Especially for changing cell colors, which are usually either per row, or even per cell.
(Btw - why is there only SetRangeColor, but not SetCellColor?)

Besides, essentially same idea is already marked as planned - there is demand for it :wink:

To be honest, as far as I’m concerned if you’d just disable saving in all ExcelScope activities and add a SaveWorkbook activity (or not even that - there’s always Invoke -> wb.CurrentWorkbook.Save) I’d be happy :wink:


#14

it is out.