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

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' 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

Try the following code:

string filePath = @"D:\TEST.xlsx";
Workbook workbook = new Workbook();
int columnIndex = 20;
workbook.LoadFromFile(filePath);
Worksheet sheet = workbook.Worksheets[0];
var lookupRanges = sheet.Range[3,1,sheet.LastRow,1];
// get the duplicated row numbers
var duplicatedRows = lookupRanges.Rows
.GroupBy(x => x.Columns[0].DisplayedText)
.Where(x => x.Count() > 1)
.SelectMany(x => x.Skip(1))
.Select(x => x.Columns[0].Row)
.ToList();
var lookupRanges2 = sheet.Range[3,1,sheet.LastRow, columnIndex];
if (duplicatedRows.Any())
{
var firstRows = lookupRanges2.Rows
.GroupBy(x => x.Columns[0].DisplayedText)
.Where(x => x.Count() > 1)
.Select(item => item.First()).ToList();
var sumValue = lookupRanges2.Rows
.GroupBy(x => x.Columns[0].DisplayedText)
.Where(x => x.Count() > 1)
.Select(item => item.Sum(c=>c.Columns[columnIndex-1].NumberValue)).ToList();
for (int i =0;i
{
firstRows[i].Columns[columnIndex-1].NumberValue = sumValue[i];
}
}
for (int i = 0; i< duplicatedRows.Count; i++)
{
sheet.DeleteRow(duplicatedRows[i]-i);
}
workbook.SaveToFile("D:/out.xlsx");

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Leon D5y ago
Accepted answer
Try the following code:
string filePath = @"D:\TEST.xlsx";

            Workbook workbook = new Workbook();
            int columnIndex = 20;
            workbook.LoadFromFile(filePath);
            Worksheet sheet = workbook.Worksheets[0];

            var lookupRanges = sheet.Range[3,1,sheet.LastRow,1];
            // get the duplicated row numbers
            var duplicatedRows = lookupRanges.Rows
                   .GroupBy(x => x.Columns[0].DisplayedText)
                   .Where(x => x.Count() > 1)
                   .SelectMany(x => x.Skip(1))
                   .Select(x => x.Columns[0].Row)
                   .ToList();

            var lookupRanges2 = sheet.Range[3,1,sheet.LastRow, columnIndex];

            if (duplicatedRows.Any())
            {
                var firstRows = lookupRanges2.Rows
                        .GroupBy(x => x.Columns[0].DisplayedText)
                        .Where(x => x.Count() > 1)
                        .Select(item => item.First()).ToList();
                var sumValue = lookupRanges2.Rows
                        .GroupBy(x => x.Columns[0].DisplayedText)
                        .Where(x => x.Count() > 1)
                        .Select(item => item.Sum(c=>c.Columns[columnIndex-1].NumberValue)).ToList();
                for (int i =0;i
                {
                    firstRows[i].Columns[columnIndex-1].NumberValue = sumValue[i];
                }
            }

            for (int i = 0; i< duplicatedRows.Count; i++)
            {
                sheet.DeleteRow(duplicatedRows[i]-i);
            }

            workbook.SaveToFile("D:/out.xlsx");