none
Excel Web App - Copy or over-write a table from one sheet to another using javascript API. RRS feed

  • Question

  • Hi,

    I am developing an Excel Web App for Office 2013. I am having a requirement where I need to have four different table formatted with different shades. Since I cannot have this formatting (like giving background color to a cell/column) at run time,  the idea is to have  four different table formats defined in one of the excel sheet (which would be in a hidden sheet). At run time, based on certain condition, I want to pick up one of the formats and have it on my main sheet.

    What are the APIs for doing this copy of table from one sheet to another retaining the format?

    Thanks,
    Mayur.

    Thursday, March 21, 2013 6:11 PM

Answers

  • Hi, Mayur

    The current set of JavaScript APIs for Excel are not capable of copying style information (colour, font, size, etc). However, there may be a workaround: look into conditional formatting. Based on values in certain cells, you can apply predetermined formats. For example, when you write the data to the spreadsheet, you can colour cell backgrounds based on the string in their column header.

    Thanks!

    -Michael

    Friday, March 22, 2013 1:17 AM
    Moderator

All replies

  • Hi, Mayur

    The current set of JavaScript APIs for Excel are not capable of copying style information (colour, font, size, etc). However, there may be a workaround: look into conditional formatting. Based on values in certain cells, you can apply predetermined formats. For example, when you write the data to the spreadsheet, you can colour cell backgrounds based on the string in their column header.

    Thanks!

    -Michael

    Friday, March 22, 2013 1:17 AM
    Moderator
  • Thanks Michael. That's an awesome work around and works great!

    Just to add to that. I also need to be able to do locking based on certain values of the cell. I tried looking up if that could be done through formula (like conditional formatting) but found none. The alternatives that I see for it are

    1. Data Validation - I can use this, but this will not work when data is pasted into the cells. Copy/Paste is an important functionality that would be needed so I cannot disable it either.
    2. Check the cell coordinates in "Office.EventType.BindingSelectionChanged" event handler. Again, Copy/Paste  would not work.
    3. The last solution is Macros. But we don't want to have macros for Office App.

    Is there anything else that we can do to achieve the locking?

    Thanks,
    Mayur

    Friday, March 22, 2013 9:20 PM
  • hi Mayur,

    Looking into your questions, seems you are working around the issues you encountered. But now with Office 2013 SP1 new App APIs, you can directly achieve the goal! In the SP1 update, we introducted 3 new APIs and update 1 API about table formatting. So now you can set almost all the table formats (background color, font style/color, border, table options....) at run time. Please check here for more details:

    1. setFormatsAsync http://msdn.microsoft.com/en-us/library/office/dn482536(v=office.1501401).aspx

    2. setTableOptionsAsync http://msdn.microsoft.com/en-us/library/office/dn482539(v=office.1501401).aspx

    3. clearFormatsAsync http://msdn.microsoft.com/en-us/library/office/dn482537(v=office.1501401).aspx

    4. setSelectedDataAsync http://msdn.microsoft.com/en-us/library/office/fp142145.aspx  new added formatting options.

    Monday, March 17, 2014 5:27 AM
  • jipyua,

    I'm trying to use the new APIs introduced in SP1, but any time I try to set formatting I get a 5001 (An internal error has occurred) error code. This happens with both setFormatsAsync and setDataAsync with formatting options supplied. clearFormatsAsync works. Any help or examples you can point me to would be greatly appreciated!

    Ian

    Thursday, May 1, 2014 7:12 PM
  • Jipyua,

    I tried and those are working for Desktop version, but they did not work for Excel Online. However, the documentation mention that they should work for the both.

    Does these methods work with Excel Online too or only with Desktop version? Any feedback would be helpful.

    Thanks,

    Samir

    Wednesday, May 14, 2014 1:23 AM