topic RE: Write Collection to excel file in Product Forum

Write Collection to excel file

PrajjwalRai — Fri, 27 Sep 2019 12:23:00 GMT

Hi all

I have a collection which I want to write to an excel file . Can someone provide me the C# code for the same along with the dll's? I am aware of the write collection feature, but I want to do it faster since my collection is quite large.

------------------------------
Prajjwal Rai
Application Engineer
Schneider Electric Private Limited
------------------------------

RE: Write Collection to excel file

AndreyKudinov — Tue, 01 Oct 2019 20:21:00 GMT

Copy whole xml and paste this as new page into Excel VBO (click after last tab and pick paste) - should be much faster that default, although behaves a bit different in some cases:
https://pastebin.com/4cm2J4A7

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

RE: Write Collection to excel file

ErinConnerley — Sat, 19 Oct 2019 18:17:00 GMT

Instead of using StringWriter it would be fastest to convert the BP Collection which is actually a System.DataTable into a 2D Array. Excel supports Setting a range equal to a 2d array in one write. This usually will take about 1-2 seconds total run time for a collection which is 100,000 Rows by 10 Columns.

------------------------------
Erin Connerley
Automation Consultant
Agilify Automation
America/Indiana/Indianapolis
------------------------------

RE: Write Collection to excel file

AndreyKudinov — Sun, 20 Oct 2019 17:09:00 GMT

@ErinConnerley That is what it does.
StringWriter is there only to serialize a subcollection into a cell. It is hardly useful in practice (I did it for debug purposes originally), could be skipped instead.

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------