Keep Format - Writing DT

Help Activities
, ,
1

Hi all,

Wondering what might be the best way to approach pasting writing my DataTable into a template Excel/Workbook whilst keeping the formatting of the sheet I’m writing too - for clarity, it’s a very large DT with thousands of rows - but Ideally would like to keep the template I’m writing to’s formatting, and also ideally the formulas to stay the same, so that when I write into the cells - the formula would persist on the data.

If not the formulas, if anyone even knows the best method to keep the format would be a big help.

Cheers

2

Hi @KB_Fleet,

What is the source of this datatable? Are you reading it from an excel sheet and then pasting into another?

Or you are creating it in your code?

if you are copying it between the sheets, you can use Copy/paste range activity.

if you need to read data first from excle into datatable, and then paste it , you can use Preserve format option during read range activity.

Regards
Sonali

3

@KB_Fleet in the read range property enable the preserve formatting enable so it will read the data as it is format.
cheers

4

Thanks for the help!

I am actually reading an Excel as a DB query with SQL as the data is so large this is the best/quickest way.

After this hoping to use this DB data and paste into another excel/workbook template and retain THAT excel/wb formatting.

Appreciate the help

5

@KB_Fleet,

Thanks for sharing.

have you tried pasting the data, is it stripping away the formatting?

Regards
Sonali

6

Do you mean using the copy/paste activity?
Will try this now and let you know!

7

@KB_Fleet

yeah, either copy paste activity or write range activity to write datatable to excel.

Observe the behaviour in both cases.

Regards
Sonali

8

You could simply use “Write Range Workbook” activity to write a datatable to a preformated workbook.

The format of target workbook will be preserved in case you “Write Range” to already openned workbook - see screenshot below.

The image shows a workflow sequence where a target workbook is assigned to an object and data is written to a specified sheet and cell within that workbook. (Captioned by AI)
The image shows a workflow sequence where a target workbook is assigned to an object and data is written to a specified sheet and cell within that workbook. (Captioned by AI)1086×389 27.7 KB

In contrary, if you “Write Range” to file directly the format will be owerwritten.

image
image1101×205 16.4 KB

Cheers