0

I know this is going to be very specific, and I'll be lucky if anyone who has experience on this topic sees this, but I've run out of patience trying to search for answers so I thought I might see if anyone in the community knows.

I'm trying to build a PeopleCode generated report that reads data from a staging table and writes it to an Excel spreadsheet. To do this, I'm using an Oracle library called "PSSpreadsheet". Other than the PeopleBooks documentation, there is little to no information about the library online. My problems lie with these two functions: SetCellHeaderStyle() and SetColumnWidth().

First of all, SetCellHeaderStyle() takes the following parameters:

  • rownum/colnum
  • redvalue
  • bluevalue
  • greenvalue
  • boldweight
  • color
  • fontheight
  • fontname

I only have issues with the red/blue/green values. No matter combination of numbers I set as parameters (0 to 256), the cells I apply the function to are gray and have a double lined border. This means if I want to use this function and change any formatting it provides, I have to deal with this bizarre color/border style.

The next function SetColumnWidth() only takes two parameters:

  • colnum
  • width

Pretty straightforward wouldn't you agree? When I run my code (where &SS is my spreadsheet object):

&SS.SetColumnWidth(1, 25);
&SS.SetColumnWidth(2, 25);
&SS.SetColumnWidth(3, 1);
&SS.SetColumnWidth(15, 1);
&SS.SetColumnWidth(27, 1);

Which is meant to set column A and B to width 25; and column C, O and AA to width 1, it just collapses the columns. As in they all have a width of 0 when I create the report.

Can anyone help to understand how to apply these formatting functions to my code? I'm sort of at my wit's end with this stuff and I'm not sure if the library sucks, or if I just don't understand it.

Thanks in advance.

wolφi
  • 8,091
  • 2
  • 35
  • 64
mattmorin
  • 1
  • 2
  • Could you provide your code for the header style issue? One though is that the parameters are ordered incorrectly. I think the functionality is fairly new (PT 8.55, I think) so might not hurt to open an SR with Oracle to find out if these are bugs or if the documentation is incorrect. – otherted Jul 09 '18 at 17:11

2 Answers2

0

For Set Cell Header Style I see the same.

This seems to be based on OPENXML. You can save the xlsx file as a .zip file and see what is being placed in the spreadsheet. [xl/styles.xml, etc.] It seems like the color parameter sets the font color although I haven't figured out exactly what number corresponds to what color entry in Excel other than by trial and error. The Red Green Blue values do not seem to set anything? fontheight and fontname do seem to work

Set Column Width is apparently 0 based so column A is 0, B is 1,

Grant Foster
  • 722
  • 2
  • 11
  • 21
Joseph Blakeman
  • 1
0

For the colour number see this link

Color parameter in SetCellHeaderStyle, is used to set the color for the font. This is a Indexed color value, equivalent to Apache POI library XSSFColor Index. To set the red color, We need to pass value equivalent to IndexedColors.RED.getIndex().

I used JavaObject as follows

Local JavaObject &xlColor = GetJavaClass("org.apache.poi.ss.usermodel.IndexedColors");
 &ssObject.SetCellHeaderStyle(&iXLrow, 1, 255, 255, 0, 20, &xlColor.RED.getIndex(), 12, "Calibri");

See this other link for different colors.

It also looks like boldWeight does nothing.
Also not the SetCellHeaderStyle only worked for me if I open spreadsheet with row set to true.

&ssObject.Open(&strOutFileName, True);
Crocsx
  • 2,534
  • 1
  • 28
  • 50
user3487157
  • 1