Will Excel Functions/Formulas run in Confluence?

[Troy S]

I'm using Confluence 3.5 now.

Before Confluence renders an Excel Sheet on page, will it run the formulas/functions first?

For example, I have a the following Function in the cell Sheet1!A1

=NOW()

This is a time function that prints the current date and time.

I create a Confluence Page and put the following:

{viewxls:name=datastore.xls|sheet=sheet1|row=0|col=0}

When Irefresh the page, I want the "NOW()" function to execute and update the date/time. Is this possible?

I am trying to create a datastore so that I can update various reports and graphs dynamically from one location. Are there other options?

Thanks,

[Chuck Gould · Accepted solution]

Troy,

There are security reasons your administrator may have for not exposing a data connection. It is not unusual for the system admin to have to define the SQL data source before you can access it and you may have to take steps to firewall the database to address their security concerns. Don’t give up. Work with them. Show them the benefits after you become more comfortable with the tools.

The documentation on the CustomWare Scaffolding and Reporting plugins are improving. My best sources are:

· The basic documentation originally offered by Customware at: http://wiki.customware.net/repository/display/AtlassianPlugins/Recipe+Book

o There is a lot of reading and piecing things together, but the recipes help.

o My understanding is that the plugins include the libraries that allow you to access information (Suppliers) using the Reporting macros. The Scaffolding macros allow you to add your own data elements to pages as mentioned before.

o I tend to think of “pages” as “rows” in a database and Scaffolding data as the fields in the rows.

· Atlassian Answers – as you have already found.

· Customware’s Get Satisfaction site – community powered support.
http://community.customware.net/customware

· Customware’s latest documentation – an evolving site with additional Recipes’
http://wiki.customware.net/repository/display/REPORT/Recipe+Library

· Google is my best friend I usually search “Confluence <macro name>” or “CustomWare <macro name” and find plenty of references. "Google Confluence tips and tricks" or "Confluence Recipe" or "Confluence How to" and see what other sites are doing. I would expose my tips, but our administrators don't allow anonymous access to the wiki.

The way I learned to use Scaffolding and Reporting was from these sources and trial and error. I had our administrators create a test wiki space for me and then would create sample pages exercising the various macros in a limited capacity using snippets of code samples until I figured out the syntax and relationships. By keeping the examples simple and focused to one set of macros, trying all the parameters, I was able to understand how they work and predict their behavior. In essence I made my own recipes - very basic ones. Then I started building more complex solutions from my more basic snippets.

Some basic tips I learned the hard way:

· Reporting plugin

o Some macros collect data differently and depending on the scope of the query and number of pages involved you may find some reporter macros more efficient than others. I use the local-report macro rather than the content-report macro for this reason. Both work, local-reporter is usually quicker. Keep this in mind if your page rendering seems to take forever.

· Scaffolding plugin

o The Rich Text editor tends to inject line feeds “\\” into code when accessing pages with Scaffolding data. This changes the wiki markup to a point where the Scaffolding macros may not perform. Removing the extra “\\” allows the macros to work again and the data is still there.

o Your version may behave differently.

· Live templates

o Once you have working code you can deliver pages with Scaffolding data as live templates. This has the benefit of rendering the page from a template as though it was typed in the wiki page. Updates to the template automatically propagate to all pages using it each time the page is rendered.

§ This eliminates having to manually update each Scaffolding pages for code changes.

§ Prevents users from editing the code.

§ Requires that you have Space Admin access to create the templates or have your administrators create them for you (go for the Space Admin access).

· Start small. Work in steps. Document your methods. Be patient. Plan out what you want to do on paper before beginning coding.

· Review existing articles for similar questions, often I explore the forums for ideas and find tips for things I want to do in unrelated articles where someone is attempting to do something else.

· Finally post questions to the forums. Be patient for the response.

Ultimately you will get there. It just takes time.

[Troy S]

What is the difference between these two solutions? They both appear to work.

{report-info:global:current date|format=MMMM dd, yyyy h:mm a}
{report-info:global:current date > date:h:mm a 'on' MMMM d, yyyy}

They both appear to display the same, but one uses a "format" parameter and the other uses ">" (pipe command?).

Thanks,

[Chuck Gould]

We are using Confluence 3.4.9.

My testing shows that values in a spreadsheet are displayed with the value contained when the spreadsheet was last saved and that the viewxls macro only renders the last known values.

We have the CustomWare Scaffolding and Reporting plugins installed and you can achieve a dynamic date/time stamp on a page when it renders by using the report-info macro.

http://wiki.customware.net/repository/display/AtlassianPlugins/report-info

The plugins are very powerful, but require some investment in learning how to use them. The Scaffolding allows embedding data into pages and the Reporting allows reporting that data along with system data via Suppliers. Suppliers are information providers of different types.

An example of how the report-info macro is used to render the system data and time from the Global suppliers.

http://wiki.customware.net/repository/display/AtlassianPlugins/Global+Supplier

We are using the Scaffolding and Reporting plugins to collect and report data. The data is generally reported into tables and occasionally rendered as charts. This makes them semi-dynamic - they are updated when the page is rendered.

Hope this helps.

[CelesteCS]

Hello!

For any calculations in Confluence, including any manipulations with table data, you may use CelesteCS Math for Confluence.

Date parameters and functions are supported, including NOW() function so you may calculate a difference between current date and any other.

There is a plenty of other functions in the macro, so you may perform any calculations using formulas in EXCEL style.

Thanks!

[Chuck Gould]

Troy,

Macros like report-info often have parameters like format that you can add to the macro separated by a vertical bar "|" as part of the statement. That is the first example. It is taking a value and controlling the display.

Output from macros's can also be piped to into formatting. You also can pipe it into math functions to modify values of numerics.

An example of this I am using to auto-size Gantt charts is available at:

http://community.customware.net/customware/topics/gantt_chart_auto_sizing

along with the explanation from CustomWare.

[Troy S]

Thanks Chuck.

Thanks for looking into the Excel Function/Formula idea. I was afraid that what you suggested would be true.

Is there a way to import data into Confluence in order for it to be used by Scaffolding and Reporting Macros? I have found very basic and slim documentation on Scaffolding and Reporting Macros. Is there anything of more sustance that describe how to use these Macros?

I would really love to use a data connection into an Oracle Database, but our Nazi Administrator will not let me go that route.

Thanks again,