How do I dynamically set the forecolour of my column data based on the value when exporting excel using EPPLUS?

Question

I have a set of codes to export grid view data to excel using EPPLUS. I am able to export the data to EPPLUS. But how do I export the colour of the font to excel as well? Can I set the dynamic coding for the font colour if ..... colour = green if ..... colour = red?

Thus I will need column4 to be coloured as well according to the value I set.

The below codes is how I set dynamically for gridview:

            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                TableCell cell = e.Row.Cells[16];
                int Num = int.Parse(cell.Text);
                if (Num >= 1&& Num <= 11)
                {
                    cell.ForeColor = Color.Green;
                }
                if (Num >= 12&& Num <= 39)
                {
                    cell.ForeColor = Color.Orange;
                }
..........

        }

The codes for my export:

  protected void EXPORT_BUTTON_Click(object sender, EventArgs e)
        {
            ExcelPackage package = new ExcelPackage();

            ExcelWorksheet Grid = package.Workbook.Worksheets.Add("ORSA ASSESSMENTS"); 


            DataTable Gridview1 = new DataTable();
            for (int i = 0; i < Gridview1.Columns.Count; i++)
            {
                Gridview1.Columns.Add("column" + i.ToString());
            }

            foreach (GridViewRow row in Gridview1.Rows)
            {
                DataRow dr = Gridview1.NewRow();
                for (int j = 0; j < Gridview1.Columns.Count; j++)
                {
                    row.Cells[j].Text = row.Cells[j].Text.Replace("&nbsp;", " ");
                    dr["column" + j.ToString()] = row.Cells[j].Text;

                }

                Gridview1.Rows.Add(dr);
            }


            Grid.Cells["A1"].LoadFromDataTable(Gridview1, true);

            using (ExcelRange rng = Grid.Cells["A1:Z1"])
            {
                rng.Style.Font.Bold = true;
            }

            Grid.Cells[ORSA.Dimension.Address].AutoFitColumns();



                var FolderPath = ServerName + DirectoryLocation + DirectoryFolder + ExportsFolder;
                var filename = ExcelName + @"_"  + ".xlsx";
                var filepath = new FileInfo(Path.Combine(FolderPath, filename));

                Response.Clear();
                package.SaveAs(filepath);
                Response.AddHeader("content-disposition", "attachment; filename=" + filename + ";");
                Response.Charset = "";
                Response.ContentType = "application/vnd.xlsx";
                Response.TransmitFile(filepath.FullName);
                Response.End();

        }

Thus how can I do in my export codes so that I can export or set the forecolour dynamically upon exporting?

thanks

Answer 1

After this line

Grid.Cells["A1"].LoadFromDataTable(Gridview1, true);

Do a looping to loop the count of your DataTable

Example

protected void Page_Load(object sender, EventArgs e)
{
    // Check
    if (!IsPostBack)
    {
        DataTable dt = new DataTable();

        // Create Column
        for(int i = 0; i < 5; i++)
            dt.Columns.Add("column" + i, typeof(int));

        for (int i = 0; i < 10; i++)
            dt.Rows.Add(i, i+1, i+2, i+3, i+4);

        GenerateExcel(dt);
    }
}

private void GenerateExcel(DataTable dt)
{
    using (ExcelPackage pkg = new ExcelPackage())
    {
        ExcelWorksheet ws = pkg.Workbook.Worksheets.Add("Sheet1");
        int num = 0;
        string value = string.Empty;

        ws.Cells[1, 1].LoadFromDataTable(dt, true);

        // Get Your DataTable Count
        int count = dt.Rows.Count;
        int countCol = dt.Columns.Count;
        bool isHeader = true;


        for (int i = 0; i < count; i++ )
        {                
            // Set Border
            for (int j = 1; j <= countCol; j++)
            {
                // Set Border For Header. Run once only
                if (isHeader) ws.Cells[1, j].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin, System.Drawing.Color.Black);

                ws.Cells[2 + i, j].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin, System.Drawing.Color.Black);
            }

            // Set to false
            isHeader = false;

            // Cells 2 + i == Rows, Wondering start with 2 because your 1st rows is Title so 2 is the value
            // Cells 2 == Column make sure your column is fix
            value = ws.Cells[2 + i, 2].Value + ""; // You can use .ToString() if u sure is not null

            // Parse
            int.TryParse(value, out num);

            // Check
            if (num >= 1 && num <= 11)
                ws.Cells[2 + i, 2].Style.Font.Color.SetColor(System.Drawing.Color.Green);
            else if (num >= 12 && num <= 39)
                ws.Cells[2 + i, 2].Style.Font.Color.SetColor(System.Drawing.Color.Orange);
        }

        pkg.SaveAs(new FileInfo("C:\\Test.xlsx"));
    }
}