none
Need to Set background color of updated cell in Apps for Office in excel RRS feed

  • Question

  • Hello,

    Hope you all are well.

    I have created sample apps for office project that read the excel row and update 1st column of the selected row. That's work perfect for me but I need to change that cell background color but I  am not getting any luck for this. kindly please do need full for this. here I mention my code snippets :

    Office.select("bindings#forsheet1").setDataAsync([[response.id]], { coercionType: "matrix", startRow: response.rowIndex, startCol: 1, rowCount: 1, colCount: 1 },
                                        function (asyncResult) {
                                            if (asyncResult.status == "failed") {
                                                app.showNotification('Error: ' + asyncResult.error.message);
                                            }
                                        });

    here I need to change the background color of the selected row 1st column cell.

    Thanks,

    Friday, May 30, 2014 6:01 AM

Answers

  • Hi,

    Please refer to the article below:

    How to: Format tables in apps for Excel

    Please note:

    This article explains the different features of the formatting API and outlines how to use them. In this release, you can programmatically specify cell formatting and some other options only for tables (not for Office.CoercionType.Text or Office.CoercionType.Matrix data structures) and only in apps for Excel.

    Hope this helps.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Saturday, May 31, 2014 9:54 AM
    Moderator
  • Hi,

    Here is a sample to set format for a table for your reference:

    Office.initialize = function (reason) {
            $(document).ready(function () {
                app.initialize();
                var car1 = "['RecordCode','TypeCode','CodeDesc','SummCode','SummCodeDesc','Sign','Amount','FundsType','BankRefNum','CustRefNum','DescText']\r\n['16','169','1','2','3','4','5','6','7','8','9']\r\n['16','169','1','2','3','4','5','6','7','8','9']";
                $('#SetFormats').click(SetFormatforTable(car1));
            });
        };
    function SetFormatforTable(csvdict)
        {
            var myTable = new Office.TableData();
            myTable = createTablefromTemplate(csvdict);
            Office.context.document.setSelectedDataAsync(myTable, { cellFormat: [{ cells: { row: myTable.rows.length - 1, column: 0 }, format: { fontColor: "yellow" }, width: "autoFit" }] },
                function (result) {
                    var error = result.error
                    if (result.status === "failed") {
                        app.showNotification('Error: ', error.name + ": " + error.message);
                }
            });
        }
    
    function createTablefromTemplate(optionVal) {
            var tableData = new Office.TableData();
            var res = optionVal.split("\r\n");
            for (var i = 0; i < res.length; i++) {
                var datasource = res[i].substring(1, res[i].length - 1).replace(/'/g, '').split(",");
                if (i === 0) {
                    tableData.Header = jQuery.makeArray(datasource);
                }
                tableData.rows.push(jQuery.makeArray(datasource));
            }
            return tableData;
        }

    The result:

    Hope this helps.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 3, 2014 4:44 AM
    Moderator

All replies

  • Hello,

    Now, I had mention coercionType: "table" and apply cellFormat but this cellFormat is not working here I put my snippet.

     Office.select("bindings#forsheet1").setDataAsync(newTable, { coercionType: "table", startRow: response.rowIndex, cellFormat: [{ cells: { row: indexr, column: 0 }, format: { backgroundColor: "orange", fontStyle: "bold", fontColor: "green" } }], startCol: 5, rowCount: 1, colCount: 1 }

    What I miss ?

    Can anyone look it and advice.

    Thankx.

    Friday, May 30, 2014 9:23 AM
  • Hi,

    Please refer to the article below:

    How to: Format tables in apps for Excel

    Please note:

    This article explains the different features of the formatting API and outlines how to use them. In this release, you can programmatically specify cell formatting and some other options only for tables (not for Office.CoercionType.Text or Office.CoercionType.Matrix data structures) and only in apps for Excel.

    Hope this helps.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Saturday, May 31, 2014 9:54 AM
    Moderator
  • Hi,

    Thanks for your reply.

    Yes I am already take the reference of your link but at my end this cellFormat has not working thats why i mention my sample code. Can you please look into any suggest me if I am do something miss.

    And one more thing what you say about this link :

    http://social.msdn.microsoft.com/Forums/officeapps/en-US/b7a2a2f6-3b29-484d-afcc-e6ce4ea11d5f/format-an-excel-cell?forum=appsforoffice

    Thanks.

    Monday, June 2, 2014 5:56 AM
  • Hi,

    I'm afraid it is impossible through current Office JavaScript API.

    If you have any feedbacks for current Office Development Platform, please feel free to submit them to User Voice.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 2, 2014 6:56 AM
    Moderator
  • Hi,

    So is there any other way that we can fix that cellFormat is work for me even it doesnt give any error so we can find any solution. 

    please suggest.

    Thanks.

    Monday, June 2, 2014 9:13 AM
  • Hi,

    Here is a sample to set format for a table for your reference:

    Office.initialize = function (reason) {
            $(document).ready(function () {
                app.initialize();
                var car1 = "['RecordCode','TypeCode','CodeDesc','SummCode','SummCodeDesc','Sign','Amount','FundsType','BankRefNum','CustRefNum','DescText']\r\n['16','169','1','2','3','4','5','6','7','8','9']\r\n['16','169','1','2','3','4','5','6','7','8','9']";
                $('#SetFormats').click(SetFormatforTable(car1));
            });
        };
    function SetFormatforTable(csvdict)
        {
            var myTable = new Office.TableData();
            myTable = createTablefromTemplate(csvdict);
            Office.context.document.setSelectedDataAsync(myTable, { cellFormat: [{ cells: { row: myTable.rows.length - 1, column: 0 }, format: { fontColor: "yellow" }, width: "autoFit" }] },
                function (result) {
                    var error = result.error
                    if (result.status === "failed") {
                        app.showNotification('Error: ', error.name + ": " + error.message);
                }
            });
        }
    
    function createTablefromTemplate(optionVal) {
            var tableData = new Office.TableData();
            var res = optionVal.split("\r\n");
            for (var i = 0; i < res.length; i++) {
                var datasource = res[i].substring(1, res[i].length - 1).replace(/'/g, '').split(",");
                if (i === 0) {
                    tableData.Header = jQuery.makeArray(datasource);
                }
                tableData.rows.push(jQuery.makeArray(datasource));
            }
            return tableData;
        }

    The result:

    Hope this helps.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 3, 2014 4:44 AM
    Moderator