Excel change font color depends on value

2 posts, 0 answers
  1. Bertha
    Bertha avatar
    71 posts
    Member since:
    Aug 2012

    Posted 01 Dec 2017 Link to this post

    I am using kendo.ooxml.Workbook and manually put in rows with value.  I want the font color to become red if the cell value is negative. Is it possible to do it in push row operation?  Or where should I put in this logic?  Thanks.

    rows.push({
    cells: [
    { value: "Net", background: "#d4d4f7" },
    { value: $scope.netActualYTD, background: "#d4d4f7", color:??????? },
    { value: $scope.netBudgetYTD, background: "#d4d4f7", color: ????? },
    { value: $scope.netVarianceYTD, background: "#d4d4f7" },
    { value: "" },
    { value: $scope.netForecastAnnual, background: "#d4d4f7" },
    { value: $scope.netBudgetAnnual, background: "#d4d4f7" },
    { value: $scope.netVarianceAnnual, background: "#d4d4f7" }
    ]
    })

     

     

     

  2. Stefan
    Admin
    Stefan avatar
    1474 posts

    Posted 05 Dec 2017 Link to this post

    Hello, Bertha,

    The desired result could be achieved just before calling kendo.saveAs by programmatically formatting the cells values.

    The code could be similar to this:

    rows.push({
                cells: [
                  { value: "Net", background: "#d4d4f7" },
                  { value: $scope.netActualYTD, background: "#d4d4f7", color:??????? },
                   { value: $scope.netBudgetYTD, background: "#d4d4f7", color: ????? },
                  { value: $scope.netVarianceYTD, background: "#d4d4f7" },
                  { value: "" },
                  { value: $scope.netForecastAnnual, background: "#d4d4f7" },
                  { value: $scope.netBudgetAnnual, background: "#d4d4f7" },
                  { value: $scope.netVarianceAnnual, background: "#d4d4f7" }
                ]
              })
     
            var workbook = new kendo.ooxml.Workbook({
              sheets: [
                {
                  columns: [
                    // Column settings (width)
                    { autoWidth: true },
                    { autoWidth: true },
                    { autoWidth: true },
                    { autoWidth: true },
                    { autoWidth: true }
                  ],
                  // Title of the sheet
                  title: "Orders",
                  // Rows of the sheet
                  rows: rows
                }
              ]
            });
            var sheet = workbook.options.sheets[0];
        // additional logic could be used to determine which column and value have to be formated
            for (var rowIndex = 1; rowIndex < sheet.rows.length; rowIndex++) {
              var row = sheet.rows[rowIndex];
              for (var cellIndex = 0; cellIndex < row.cells.length; cellIndex ++) {
                row.cells[cellIndex].format = "[Blue]#,##0.0_);[Red](#,##0.0);0.0;"
              }
            }
            //save the file as Excel file with extension xlsx
            kendo.saveAs({dataURI: workbook.toDataURL(), fileName: "Test.xlsx"});

    I hope this is helpful.

    Regards,
    Stefan
    Progress Telerik
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Back to Top