TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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
string
filePath=@
"D:\temp\TEST.xlsx"
;
Workbook workbook =
new
Workbook();
workbook.LoadFromFile(filePath);
Worksheet sheet = workbook.Worksheets[0];
var lookupRanges = sheet.Range[
"A3:A"
+ sheet.LastRow];
// get the duplicated row numbers
var duplicatedRows = lookupRanges.Rows
.GroupBy(x=> x.Columns[0].Value)
.Where(x=> x.Count() > 1)
.Select(x=> x.Last().Columns[0].Row)
.ToList();
//if any duplicated row is found then add the column 20 values of the said rows to the first row
if
(duplicatedRows.Any())
{
var sumRows = lookupRanges.Rows
.GroupBy(x=> x.Columns[19].Value)
.Where(x=> x.Count() > 1)
.Select(x=>x.First().Sum(c=>c.Columns[19].Value));
}
//remove the duplicate rows & blank rows if any
foreach
(var rownum
in
duplicatedRows)
{
sheet.DeleteRow(rownum);
}
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
Reply
Answers (
1
)
C# Tutorial Best .
How to write a unittest for a controller methode in .NET core?