Set cell value using Excel interop

Question

Ok, so I'm trying to set the value of a cell with the excel interop library. I am able to do it with the following:

sheet.Cells[row, col] = value;

but it's terribly slow for how many I'm setting. So I'm trying to go this route:

Range excelRange = sheet.UsedRange;
excelRange.Cells.set_Item(row, col, value);

The code executes, but no data is put in the cell. Any suggestions on what I'm missing? Thanks!

Is there any value in "value"? Is "value" of right type so it can be fit in Cell? — Tomas Voracek, Jan 27 '11 at 00:30

score 25 · Accepted Answer · answered Jan 27 '11 at 00:20

Your first method should work fine for any reasonable (and a lot of unreasonable) amounts of cells, provided you have disabled screen updating (Application.ScreenUpdating = false). The Knowledgebase Article describing how to set cells using C# accesses by row and column as well.

score 22 · Answer 2 · answered Jan 27 '11 at 09:47

22

Have you tried setting all of the values at once, rather than iterating through your array and setting one cell at a time? That way you only have to pass data over the COM boundary once, rather than once per cell.

Excel is very flexible in this regard. Try the following:

int[] intArray = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
Range rng = excelApp.get_Range("A1", "J1");
rng.Value = intArray;

You should this faster than iterating over each of the cells you're wanting to set.

Other than that, turn off ScreenUpdated as Andy suggests and also consider setting calculation to manual until you've finished your copy process.

answered Jan 27 '11 at 09:47

Chris Spicer

2,144
1
13
22

Hmm, I tried this and it worked, but there are several cells that are merged across the row and it's not laying down the values properly. I am using an object array, as the data is mixed, some decimals, some strings. – Arthurdent510 Jan 27 '11 at 17:39
5

You can also use a multidimensional array to populate a range. – Jon Peterson Dec 21 '12 at 17:08
Hmmm this doesn't work for me. I'm assigning `object[]` to `range.Value` and all cells get the value of the array's first item. Please help. – Mr. TA Dec 28 '18 at 19:17
2

Figured it out! You **have** to use a 2-dimensional array when populating a range consisting of more than 1 row. Excel Interop does **not** automatically break a regular (1-dimensional) array by rows. – Mr. TA Dec 28 '18 at 19:22

score 2 · Answer 3 · answered Mar 09 '17 at 05:05

Simple solution but here you will need to install Package Microsoft.Office.Interop.Excel by writting in nuget console

Install-Package Microsoft.Office.Interop.Excel

   //test excel file
   Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

        Workbook workbook = excel.Workbooks.Open(System.Windows.Forms.Application.StartupPath + "/TrainedFaces/AttendanceLog.xlsx", ReadOnly: false, Editable: true);
        Worksheet worksheet = workbook.Worksheets.Item[1] as Worksheet;
        if (worksheet == null)
            return;

        var abc = worksheet.Cells[2, 1].Value;
        Range row1 = worksheet.Rows.Cells[1, 1];
        Range row2 = worksheet.Rows.Cells[2, 1];

        row1.Value = "Test100";
        row2.Value = "Test200";


        excel.Application.ActiveWorkbook.Save();
        excel.Application.Quit();
        excel.Quit();

score -8 · Answer 4 · edited Aug 13 '14 at 07:40

-8

Please try

[excelWorksheet].Cells[1, 1] = "[String you want]";

edited Aug 13 '14 at 07:40

Bhushan Kawadkar

28,279
5
35
57

answered Aug 13 '14 at 07:11

Marc Zeroc

19
1

Set cell value using Excel interop

4 Answers4

Linked