Using the SUMIF() of EPPLUS For Excluding Rows Programmatically in MVC C#

Question

Please I have a report spool from an SQL Table and exported it to Microsoft Office Excel through EPPLUS.

Category FIELD Contains the different types of Clients. When the client is a staff, the Commission is waived.

Category='001' and '002', etc. were attached to each row of data in the Table.

Category ='001' - Staff Male

Category ='002' - Staff Female

Category = '003' - Others

etc.

Whenever the system encounters the staff code It should Format the values of the row with "disc" for example **"10,000.00disc"** and exclude it from the SUM TOTAL

I have done the Query in SQL but I don't know how to handle it in Excel EPPLUS using SUMIF() or other functions.

CREATE TABLE GenReport(
	Category varchar (3) NULL,
	TotalCommission Decimal (19,4) NULL,
	EarnedCommission Decimal (19,4) null

)

INSERT INTO GenReport (Category, TotalCommission, EarnedCommission)
VALUES ('001', 3600.0000, 3600.0000 );
INSERT INTO GenReport (Category, TotalCommission, EarnedCommission)
VALUES ('001', 564827.1800, 564827.1800 );
INSERT INTO GenReport (Category, TotalCommission, EarnedCommission)
VALUES ('002', 2700.0000, 2700.0000 );
INSERT INTO GenReport (Category, TotalCommission, EarnedCommission)
VALUES ('002', 4700.0000, 4700.0000 );
INSERT INTO GenReport (Category, TotalCommission, EarnedCommission)
VALUES ('003', 6800.0000, 6800.0000 );

DECLARE @Category VARCHAR (3)
SET @Category='001'
SELECT CASE WHEN Category=@Category 
	THEN CONCAT(TotalCommission , '*disc') 
	ELSE CONCAT(TotalCommission , '')  END AS TotalCommission    
FROM GenReport 

What I have done so far in MVC EPPLUS

        public void ReportExcelDetail(string string id, DateTime? startDate, DateTime? endDate)
        {

            ProductionReportPrintContainer reportForPrint = _GenerateReportService.GetProductionReportForPrint(id, startDate, endDate);

            //Create a new spreadsheet from scratch
            ExcelPackage excel = new ExcelPackage();

            var ws = excel.Workbook.Worksheets.Add("ProductionReport");


	    //Header Lists
            ws.Cells["J5"].Value = "CATEGORY";
            ws.Cells["K5"].Value = "COMMISSION AMOUNT";
            ws.Cells["N5"].Value = "EARNED COMMISSION";
            ws.Cells["P5"].Value = "DAYS";

            int rowStart = 6;

	    foreach (var item in reportForPrint.ReportPrintDTO.productionReturnDetailList)
            {
                ws.Cells[string.Format("J{0}", rowStart)].Value = item.Category;
                ws.Cells[string.Format("K{0}", rowStart)].Value = item.TotalCommission;
                ws.Cells[string.Format("N{0}", rowStart)].Value = item.EarnedCommission;
                ws.Cells[string.Format("P{0}", rowStart)].Value = item.TotalNumberofDay; 
                rowStart++;
            }

            //AutofitClumns
            ws.Cells.AutoFitColumns();

	    using (ExcelRange totalCommision = ws.Cells[numRows + 1, 11])
            {
                totalCommision.Formula = "Sum(" + ws.Cells[1, 11].Address + ":" + ws.Cells[numRows, 11].Address + ")";
                totalCommision.Style.Font.Bold = true;
                totalCommision.Style.Numberformat.Format = "###,##0.00"
                
            }

}

The Final Report output in Excel

Thanking you in anticipation of your quick response.

Best regards,

Abiodunajai

Answer 1

Hi @abiodunajai,

From what I understand, you want to calculate the SUM TOTAL, not count the lines containing the disc characters.

I think if you want to do it programmatically, you can try to use String.Contains method. if (!totalCommision.ToString().Contains("disc"))

 using (ExcelRange totalCommision = ws.Cells[numRows + 1, 11])
            {
                if (!totalCommision.ToString().Contains("*disc"))
                {
                    totalCommision.Formula = "Sum(" + ws.Cells[1, 11].Address + ":" + ws.Cells[numRows, 11].Address + ")";
                    totalCommision.Style.Font.Bold = true;
                    totalCommision.Style.Numberformat.Format = "###,##0.00";
                }
            }

Best regards,
Lan Huang

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.