How to insert an array to a single row in Excel using ClosedXML

Question

I have an array and I need to put the elements in one row, every element of the array should be in a different cell.

foreach (var cell in range.Cells())
        {
            int index = 0;
            cell.Value = arrayCompany[index];
            index++;

            if (index == count)
            {
                break;
            }
        }

My array has 10 elements and the excel file generates one row but in all 10columns, cell value is the last element of the array. I want each element to be in a different column. A1=arrayCompany[0],B1=arrayCompany[1] ... 10thcolumn = arrayCompany[9]

Please detail what you want to occur, what the current code does and how i differs form what you expect. Some samples / examples would be even better. — mjwills
– mjwills, Commented Jul 10, 2017 at 12:04
my array has 10 elements and the excel file generates one row but in all 10columns, cell value is the last element of the array. I want each element to be in a different column. A1=arrayCompany[0],B1=arrayCompany[1] ... 10thcolumn = arrayCompany[9] — Toni
– Toni, Commented Jul 10, 2017 at 12:10

Francois Botha · Accepted Answer · 2021-07-19 15:34:22Z

8

You can use

cell.InsertData(data: yourEnumerable, transpose: true);

Notice the transpose parameter, which allows you to insert your values in a row. By default, values of an enumerable would be inserted below each other.

edited Jul 19, 2021 at 15:34

answered Jul 13, 2017 at 17:02

Francois Botha

4,8891 gold badge39 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

surpavan Over a year ago

Transpose is main point to note, I was erroring out for 30mins+ because I missed the transpose and data was getting overwritten.

Francois Botha Over a year ago

@surpavan I updated the answer to highlight the transpose parameter.

Toni · Accepted Answer · 2017-07-10 12:15:23Z

1

            for (int index = 0; index < 10; index++)
            {
                var cell = range.Cell(1, index + 1);
                cell.Value = arrayCompany[index];
            }

This is working, but I guess there is a more clean solution for the problem.

answered Jul 10, 2017 at 12:15

Toni

771 gold badge3 silver badges15 bronze badges

Comments

rickvdbosch · Accepted Answer · 2017-07-10 12:03:59Z

0

I've used this:

worksheet.Cell(x, y).Value = toExport.AsEnumerable();

Where my toExport variable is a list of objects. It does not only enumerate all properties to columns, it also inserts every instance in a new row.

This might also work with an array.

answered Jul 10, 2017 at 12:03

rickvdbosch

15.8k2 gold badges47 silver badges60 bronze badges

1 Comment

Toni Over a year ago

I have tried this solution, but it's inserting every element in a new row. I want to insert all the elements in one single row which means each element in different column.

Collectives™ on Stack Overflow

How to insert an array to a single row in Excel using ClosedXML

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related