Jayanta Mukherjee

Jayanta Mukherjee

  • NA
  • 120
  • 43.3k

Add column values of a excel using c# & spire.xls

May 19 2021 1:45 PM
I've got a workbook where there are 30 columns and the first two rows are sort of headers, so the actual working data starts from 3rd row .
 
What I'm trying to do is that open the workbook and search for a duplicate value on the first column cells and if any duplicate value is found then sum the values present in the  20th columns of all those duplicate rows and add it to the 20th column of the first matched row and remove all the remaining duplicate rows and if that creates a blank row also delete that.
 
I've done
  1. string filePath=@"D:\temp\TEST.xlsx";  
  2.   
  3. Workbook workbook = new Workbook();  
  4.   
  5. workbook.LoadFromFile(filePath);  
  6. Worksheet sheet = workbook.Worksheets[0];  
  7.   
  8. var lookupRanges = sheet.Range["A3:A" + sheet.LastRow];  
  9.   
  10. // get the duplicated row numbers  
  11. var duplicatedRows = lookupRanges.Rows  
  12.         .GroupBy(x=> x.Columns[0].Value)  
  13.         .Where(x=> x.Count() > 1)  
  14.         .Select(x=> x.Last().Columns[0].Row)  
  15.         .ToList();  
  16.   
  17. //if any duplicated row is found then add the column 20 values of the said rows to the first row  
  18. if (duplicatedRows.Any())  
  19. {  
  20.         var sumRows = lookupRanges.Rows  
  21.                 .GroupBy(x=> x.Columns[19].Value)  
  22.                 .Where(x=> x.Count() > 1)  
  23.                 .Select(x=>x.First().Sum(c=>c.Columns[19].Value));  
  24. }  
  25.   
  26. //remove the duplicate rows & blank rows if any  
  27. foreach (var rownum in duplicatedRows)  
  28. {  
  29.         sheet.DeleteRow(rownum);  
  30. }  
  31.   
  32. workbook.Save();  
But getting errors Cannot implicitly convert type 'string' to 'int' (CS0029) & Cannot convert lambda expression to delegate type 'System.Func<Spire.Xls.Core.IXLSRange,int>' because some of the return types in the block are not implicitly convertible to the delegate return type (CS1662)
 
Also, is there a more efficient way of doing this not that I know for sure that my above approach actually works...
Help 

Answers (1)