HOWTO question on formating cells in spreadsheet

[FM MIS]

I just started looking at jOpenDocument as of yesterday.  Looks good so far.  I am able to create a spreadsheet via the example on the website successfully. 

 

What I need to now is format a spreadheet.  Specifically I would like to set the format of the top row to Bold (as in a header row) and set the freeze point to force the headers to always be shown when the user scrolls the data.

 

Any suggestions on how to do this would be helpful. 

 

 

Thanks.

 

Keith

[Mis At FMCorp]

[Sylvain Cuaz]

Mis At FMCorp a écrit :

> What I need to now is format a spreadheet. Specifically I would like
> to set the format of the top row to Bold (as in a header row) and set
> the freeze point to force the headers to always be shown when the user
> scrolls the data.

Hi,

The easiest thing to do to apply styles, is to first define them in
OpenOffice (via the F11 shortcut). Then you just call setStyleName()
with the name you gave it. The vertical split is not standard but I've
included in the attachement how OO does it.

HTH,
Sylvain.

[FM MIS]

Hi Sylvain and thanks very much for your help and examples.

Couple of questions:  1).  Your example presumes a template based approach, which is OK, but I was hoping for a fully programmatic approach if its possible.  But your example does seem to point me in the direction I need to go, presuming I can create the style through code.   2).  I'm presuming that the reason for the way to are doing the freeze is that the api doesn't directly approach this concept (at least yet)?

Thanks again

Keith

import java.io.File;
import java.io.IOException;

import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.xpath.XPath;
import org.jopendocument.dom.spreadsheet.Sheet;
import org.jopendocument.dom.spreadsheet.SpreadSheet;

public class Example {

   public static void main(String[] args) throws IOException, JDOMException {
       final SpreadSheet spread = SpreadSheet.createFromFile(new File(args[0]));
       final Sheet sheet = spread.getSheet(0);
       // set a style defined in OpenOffice
       sheet.getCellAt("A1").setStyleName("myBoldStyle");
       // the vertical split is not part of OpenDocument, but this how OpenOffice uses it
       // see the whole settings.xml for more
       final XPath xp = spread
               .getXPath("./office:settings/config:config-item-set[@config:name='ooo:view-settings']/config:config-item-map-indexed[@config:name='Views']//config:config-item-map-entry[@config:name=$sheetName]");
       xp.setVariable("sheetName", sheet.getElement().getAttributeValue("name", spread.getNS().getTABLE()));
       final Element sheetSettings = (Element) xp.selectSingleNode(spread.getPackage().getDocument("settings.xml").getRootElement());
       final Element splitMode = (Element) spread.getXPath("./config:config-item[@config:name='VerticalSplitMode']").selectSingleNode(sheetSettings);
       // 0 to disable
       splitMode.setText("1");
       final Element splitPos = (Element) spread.getXPath("./config:config-item[@config:name='VerticalSplitPosition']").selectSingleNode(sheetSettings);
       // height in pixels
       splitPos.setText("64");

       spread.saveAs(new File("out"));
   }
}

[Guillaume Maillard]

Hi Keith,

You are right, the code assumes that you create the style in your template with OpenOffice.
Is there a reason you would preffer to create it programmaticaly ?

A template is by nature done by humans that always preffers a GUI. It reminds me why MS Office won over LateX ;)
From our point of view, an automation is supposed to "inject values".

The "freeze" is done this way because "freeze" is not in the OpenDocument specification, but is a feature provided by OpenOffice.
That's why you see the prefix "ooo:".

Regards,
Guillaume
 

2009/5/14 FM MIS <misat...@gmail.com>

[FM MIS]

Guillaume:

Thank you for your comments.  The "freeze" "feature" helps me understand the boundary between the ODF spec and the OOo app.  Thanks for the clarification although I'm not sure why the distinction was made at this level.   I guess it falls in the same category as why formulas are not part of the ODF Spec (according to my very limited understanding--case in point why MS doesn't handle formulas in their ODF implementation in the upcoming MS Office service pack).  Again, not to start a discussion here on the merits of the decision,  it just doesn't seem intuitive to me why that decision was made (or not made as the case might be).

On the question of why I would prefer to create the styles programmaticaly:  similarly to the above question is the intuitiviness.  Being dependent on external files in an enclosed application for a singular purpose simply adds more mud to the application.  Managable, yes.  But from my perspective, I would prefer fewer file dependancies than more.

Here is my situation.  Maybe this will help everyone understand how this library might be being used in the real world. 

I work for a small custom manufacturor of plastic goods.  We have about 125 total employees in the whole organization.  I manage the computer systems for this company with help from one other person, my network administrator.  I am the sole developer of any software.  My primary role in developing software is to provide my users the ability to extract pertainate data from our ERP system in a form that allows them to view and anaylse the current state of inventory and other company information.  My primary means of providing this information is SQL queries which are built into a custom shell with filter parameters that the user provides at the time of run.  My result, presently, is written to a *.csv file, which I pass as a parameter to scalc.exe to open.  This works very well.  The primary concern that I have is that I don't have the ability through this mechinism to provide a view-ready spreadsheet.  By view-ready I mean that the end user must do all formating him/her-self.  Also there is not an easy way to time-stamp or describe the resultset in the file--so the user must provide that info him/her-self.  (I. E. what query was ran, when was the data extracted, what were the parameters for the query, who ran the query)  This info would be placed on the default page layout so that if the user decided to print the data, the context would be provided as part of the report.  (I also am planning on placing this info in the metadata of the ODS.)  Pushing this info to the ODS would be the second step in development for this project after first just creating an ODS and then getting the basic formating down. 

I envision a single static class with a small number of method calls where I pass the pertinet resultset, parameter list, user info, ect and the class builds the ODS and launches OOo with the new ODS passed.  I defininly can (and will if need be) use a template file for the resulting ODS, but again, it seems that it would be intuitive to have that capability programmatically.   Something similar to:

sheet.getRow(1).getCurrentStyle().setBold(true);

You said: "A template is by nature done by humans that always preffers a GUI. It reminds me why MS Office won over LateX ;)" .  Are you thinking maybe a complex form or template such as an Invoice or something that might actually be a document of some sort?  In that case, I fully agree, a template is most definetely the better way to go.  Logos change and other items might change.  Providing the data to a template in this scenerio would definely be easier than the way I'm doing my stuff.  But I'm only providing minimum formating (bolding the first row, for the user's convenience, and freezing that row).  Then providing as described above the parameters ect for context.

Hope this helps.

Keith

[jOpenDocument, Guillaume]

Hi Keith,

Thank you for the detailed description of your needs, to sum up, you
preffer a 100% java approach
because filling a template seems to be like using dynamite to kill flies.

I agree with you that "sheet.getRow(1).getCurrentStyle().setBold(true);"
is a clean solution too,
easy style handling and creation is something planned for the new releases.

Playing with styles is not as simple as you could think because styles
are shared, hierachical, dependent of installed fonts etc...
that's why we always use OpenOffice for such tasks.

The main idea of the API is something like MVC, we separate content and
form.
Content being handled and maintained in java ; form being manipulated
with OpenOffice.
We provide methods to modify the strucuture of the sheet
(add/insert/... rows and columns).

Look at org.jopendocument.dom.spreadsheet.Table , the method "merge"
uses a TableModel
which is what you are looking for easily inject your data.

http://www.jopendocument.org/start_spreadsheet_2.html seems, for us,
easier than doing all the dark stuffs in java
and far more performant. Will you be happy to modify and deploy a new
version of your application if users preffer red titles instead of bold?

Regards,
Guillaume

[FM MIS]

Guillaume , thanks again for your comments.

I can understand the complexities with styles in the since of a full blown implementation.  And I understand, and it makes good sense that your design is MVC based, separating content and form.  Definitely a good design perspective.  You asked an interesting question on whether I would re-deploy based upon a user's request for red titles verses bold.  Because my deployment audience is small (less than 25 regular users), I have the ability to dictate the bounds of what I will or will not do (to a large extent).  Personal preferences like those, in my particular situation, isn't an option--on the other hand if they need a particular column of data added, then yes I would generally do that if it makes sense.  (Usually just a SQL change which is stored in a database seperate from the application itself--so no re-deployment issues).  If a particular user wishes that kind of formating, I give them the full capablilty if OOo for that purpose.  Thank goodness my users aren't ususally that anal, so its not much of an issue.  From your perspective your API wouldn't have the luxury of limiting the end-developer at that level and I fully understand that.  Which seques into another point:  to build the API to handle the full extent of the style hiearchy would, for me, be way overkill.  I wouldn't use that complexity; I would inform my users that that would be their responsiblity.  From your perspective, you would have to decide whether providing that level of complexity for your users would warrent the development time to support that.  Just a thought, how many of your users would actually build that type of complexity in their application.  I haven't a clue as to the number, but again from my perspective, that degree of 'purity and expansiveness' would be way out of line per my specific needs.  Maybe there is a center ground their where some degree of formating is offered (at a low level of abilities) while the more complex stuff is reserved to the template based approach.  It may not be worth the trouble from your perspective at any level.

Just to further your understanding of my end-users' needs:  By far, most of the time, my end user will run one of my queries, sort and filter the information as needed to glean an answer to a relatively simple question:  such as 'how much finished goods product do we have on hand for customer X's project Y?  Do we have enough to satisfy that customers' immediate demand or will we have to build more of that product with leadtimes ect.)  Once the answer is obtained, that spreadsheet is discarded--so overt and complex styling isn't necessary.  Again I'm 'bolding' and "freezing" the sheet to simply make their lives a little easier while purusing the data to find the answer to their adhoc question--a convenient bit of functionality.

Just my 2 cents worth.

If there is anything I might be able to do to help, let me know.  I don't have the luxury of spending a large amount of my time doing development or design, but maybe I can do something.  I've been looking for several years for this type of solution (presently using Apache POI in some cases building *.xlses--yuck!--not because POI is bad, but why must I beef up MS's ego?) :)

Keep up the good work

Keith