locked
How to Change Excel Field Color Dynamically RRS feed

  • Question

  • User1360833881 posted

    Hi,

    I have class (which contains collection of records), I am passing it to a method to generate report in Excel, however based on certain condition, like one of the filed of current class is true, then I need to change one Excel field color. My code is shown below.

     var costStructureReport = new
                {
                    CurrentQuotation = ptCostStructure.GetCostStructureReport()
                };
        var reportEngine = new ReportEngine();
     string fileName = reportEngine.ProcessReport(ReportNames.ProjectDownload_Template, reportname + ".xlsx", costStructureReport);
    
    

    In above "CurrentQuotation" is table row in excel "tbv.CurrentQuotation", which is contains fields like fld.MatCost, fld.ManfCost and so on, fld.MatCost need to be changed to red color if fld.MaterialCost_ByUser is true Thanks  in advance. 

    Monday, March 29, 2021 1:07 PM

All replies

  • User1535942433 posted

    Hi alibasha202@gmail.com,

    According to your description,I'm guessing that you have used methods to general ReportEngine to excel.

    However,what's your type of the project? What is GetCostStructureReport() and ProcessReport()? Could you post full codes to us?It will help us to solve your problems.

    Best regards,

    Yijing Sun

    Tuesday, March 30, 2021 2:36 AM
  • User1341756031 posted

    Try something like that

    ws.Cells[row, clmn].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)

    More on ... C# Excel

    Tuesday, March 30, 2021 5:19 AM
  • User1360833881 posted

    Hi Yijing Sun,

    Thanks for your reply. In the above code ReportEngine  is the Microsoft class whereas "ProcessReport" is the method in that class, and GetCostStructureReport() is the method which just read from DB and return List of objects i.e; returns List of records. 

    Anyway, using the below link, I am able to loop through the rows and able to get exact cell, but unable to set its color. Please see the code below.

    https://stackoverflow.com/questions/33363249/how-do-i-dynamically-set-the-forecolour-of-my-column-data-based-on-the-value-whe

    var costStructureReport = new
                {
                    CurrentQuotation = rptCostStructure.GetCostStructureReport()
                };
     var reportEngine = new ReportEngine();
    
     string fileName = reportEngine.ProcessReport(ReportNames.ProjectDownload_Template, reportname + ".xlsx", costStructureReport);
    
                var ep = new ExcelPackage(new FileInfo(fileName));
                var sheet1 = ep.Workbook.Worksheets["SPR_ProjectDownload"];
                var row = sheet1.Dimension.End.Row;
                
                for(int i=0;i< costStructureReport.CurrentQuotation.Count;i++)
                {
                    if (costStructureReport.CurrentQuotation[i].MaterialCost_ByUser)
                    {
                        sheet1.Cells[i + 2, 12].Style.Font.Color.SetColor(System.Drawing.Color.Red);
                        sheet1.Cells[i + 2, 12].Style.Font.Bold = true;
                      
                    }
    
                }

    Tuesday, March 30, 2021 6:58 AM
  • User1535942433 posted

    Hi alibasha202@gmail.com,

    As far as I think,your codes have no problems. I suggest you could debug and break point these codes to check:

    1.Whether these codes have errors?

    2.Whether have the cell which MaterialCost_ByUser is true.

    3.Whether you could correctly position at these cells which MaterialCost_ByUser is true.

    Best regards,

    Yijing Sun

    Tuesday, March 30, 2021 8:42 AM
  • User1360833881 posted

    Hi Yij Sun,

    Thanks for the reply. I checked all your 3 points, nothing seems to be wrong. While I debug, I can see "MaterialCost_ByUser" is true for one row and false for another, also I can see the exact value of excel cell. Don't know why color is not applying, even Bold is also not applying.

    Tuesday, March 30, 2021 11:24 AM
  • User1535942433 posted

    Hi alibasha202@gmail.com,

    If you make sure you could get the right cell coordinates and values which you need to set the font color, you could check if you have looked wrong of  the sheet which exported.

    Note: The excel coordinates are based on 1 not 0. So i is from 1.

    I have created a test with setcolor and it works. Just like this:

    public ActionResult ExportToExcel()
            {
                List<P> pxy = new List<P>
                {
                    new P{ Name="Customer1", X=10,Y=20},
                    new P{ Name="Customer2",X=90,Y=24},
                    new P{ Name="Customer3",X=34,Y=63},
                    new P{ Name="Customer4",X=67,Y=1},
                    new P{ Name="Customer5",X=24,Y=84},
                    new P{ Name="Customer6",X=51,Y=44},
                    new P{ Name="Customer7",X=97,Y=92},
                    new P{ Name="Customer8",X=77,Y=13},
                    new P{ Name="Customer9",X=35,Y=39},
                    new P{ Name="Customer10",X=85,Y=29}
                };
                var result = pxy.ToList();
                using (var excel = new ExcelPackage())
                {
                    var workSheet = excel.Workbook.Worksheets.Add("Worksheet Name");
                    workSheet.Cells[1, 1].LoadFromCollection(result, PrintHeaders: true, TableStyle: OfficeOpenXml.Table.TableStyles.Medium6);
                    workSheet.Cells[2, 2].Style.Font.Color.SetColor(System.Drawing.Color.Red);
                    workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
                    return File(excel.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Reports.xlsx");
                }
    
            }
            public class P
            {
                public string Name { get; set; }
                public int X { get; set; }
                public int Y { get; set; }
            }

    Best regards,

    Yijing Sun

    Wednesday, March 31, 2021 6:30 AM