CODEWITHSUNDEEP
c#excelrangeautofillworksheet
maria
maria

Reputation: 413

How to populate values from a list to a range of cells in an Excel using Excel.Interop?

I'm trying to populate values from a List in my model to a range of cells in an Excel file. The values in my student id List are = 11, 22, 33, 44 and i want them to be on Cell C8 to C11, respectively.

I tried to assign them as values for cell range ["C8:C11"] and AutoFill them. 

private void IdFiller(IdGroupModel id, Worksheet sheet)

            foreach (var studentId in id.idList)
            {
                sheet.Range["B8"].Value = 1;
                sheet.Range["B8"].AutoFill(sheet.Range["B8:B19"], XlAutoFillType.xlFillSeries);

                sheet.Range["C8"].Value2 = studentId.name;
                //sheet.Range["C8"].AutoFill(sheet.Range["C8","C19"], XlAutoFillType.xlFillValues);

                sheet.Range["D8"].Value = studentId.age;
                sheet.Range["D8"].AutoFill(sheet.Range["D8:D19"], XlAutoFillType.xlFillValues);
            }
        }

But all i got in all of the cells is the value "44".

Upvotes: 1

Views: 1074

Answers (2)

Piotr P
Piotr P

Reputation: 324

Or just use for loop

private void IdFiller(IdGroupModel id, Worksheet sheet)  {

  for (int i = 1; i < id.idList.Length(); i++)            
  {                
   sheet.Range["B" + i.ToString()].Value = 1;                
   sheet.Range["C" + i.ToString()].Value2 = id.idList[i-1].name;  
   sheet.Range["D" + i.ToString()].Value = id.idList[i-1].age; 
  }        
 }

Upvotes: 1

Jeremy Thompson
Jeremy Thompson

Reputation: 65672

In your ForEach try add an Index counter.

private void IdFiller(IdGroupModel id, Worksheet sheet)  { 
int i =7;
 foreach (var studentId in id.idList)            
 {                
  i+=1;
  sheet.Range["B" + i.ToString()].Value = 1;                
  sheet.Range["C" + i.ToString()].Value2 = studentId.name;  
  sheet.Range["D" + i.ToString()].Value = studentId.age; 
 }        
}

Upvotes: 1

Related Questions