Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Set cell value using Excel interop

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!

like image 913
Arthurdent510 Avatar asked Jan 27 '11 00:01

Arthurdent510


People also ask

How to set cell value in excel c#?

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.

How can I edit an existing Excel file in C#?

The following code illustrates how to edit the above excel file and replace all Central occurences by ABCD : // path to your excel file string path = "C:/****/sample_data. xlsx"; FileInfo fileInfo = new FileInfo(path); ExcelPackage package = new ExcelPackage(fileInfo); ExcelWorksheet worksheet = package. Workbook.


2 Answers

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.

like image 102
Andy Mikula Avatar answered Nov 13 '22 05:11

Andy Mikula


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.

like image 45
Chris Spicer Avatar answered Nov 13 '22 07:11

Chris Spicer