• If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Announcement

Collapse
No announcement yet.

XLSX style issue since 6.1.20221026

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • XLSX style issue since 6.1.20221026

    Hi,

    We have an issue since version Valence 6.1.20221026 with the formatting of xlsx cells.
    We use the styleID dYMD but since the installation of version 20221026 the data isn't shown as a date in the resulting Excel (It worked nicely before). The cell content is correct but Excel shows it as ##########.

    Some RPG code used:
    Code:
    setCol(%addr(col2): 1:'Reference':'char':'bold':15);
    setCol(%addr(col2): 2:'OrderNumber':'num0':'bold':15);
    setCol(%addr(col2): 3:'Article':'char':'bold':10);
    setCol(%addr(col2): 4:'Description':'char':'center bold':30);
    setCol(%addr(col2): 5:'Qty Order':'num0':'bold':15);
    setCol(%addr(col2): 6:'Qty Delivered':'num0':'bold':15);
    setCol(%addr(col2): 7:'Start Date':'dYMD':'bold':15);
    setCol(%addr(col2): 8:'Pick Date':'dYMD':'bold':15);
    setCol(%addr(col2): 9:'Final Date':'dYMD':'bold':15);
    setCol(%addr(col2):10:'Customer Name':'char':'center bold':30);
    setCol(%addr(col2):11:'Customer City':'char':'center bold':25);
    setCol(%addr(col2):12:'Month':'num0':'center bold':15);
    setCol(%addr(col2):13:'Year':'num0':'center bold':15); setCol(%addr(col3):10:'Order Date':'dYMD':'bold':15);
    .
    .
    // --------------------------------------------------------------
    // Wrapper for saving column properties to col array
    // --------------------------------------------------------------
    dcl-proc setCol;
    dcl-pi *n;
    array_pointer pointer const;
    c int(5) const;
    heading varchar(80) const;
    styleID varchar(15) const;
    HstyleID varchar(15) const;
    XLSXcolW packed(7: 4) const;
    end-pi;
    
    dcl-ds pc likeds(vvSSCol) dim(300) based(p_pc);
    dcl-s p_pc pointer;
    
    p_pc = array_pointer;
    
    if c>*zero and c<=%elem(pc);
    pc(c).heading = heading;
    pc(c).XLSXcolW = XLSXcolW;
    pc(c).styleID = styleID;
    pc(c).hStyleID = hStyleID;
    endif;
    
    end-proc;
    .
    .
    vvOut_ssSqlRows(vvOut : SqlStmt : %addr(col2) : cols2);
    The date fields in the sql are numeric(8, 0)
    Looking at the excel xml I see that style 24 is used for these colums

    HTML Code:
    <cols>
    <col bestFit="1" customWidth="1" max="1" min="1" style="14" width="15.0000"/>
    <col bestFit="1" customWidth="1" max="2" min="2" style="4" width="15.0000"/>
    <col bestFit="1" customWidth="1" max="3" min="3" style="14" width="10.0000"/>
    <col bestFit="1" customWidth="1" max="4" min="4" style="14" width="30.0000"/>
    <col bestFit="1" customWidth="1" max="5" min="5" style="4" width="15.0000"/>
    <col bestFit="1" customWidth="1" max="6" min="6" style="4" width="15.0000"/>
    <col bestFit="1" customWidth="1" max="7" min="7" style="24" width="15.0000"/>
    <col bestFit="1" customWidth="1" max="8" min="8" style="24" width="15.0000"/>
    <col bestFit="1" customWidth="1" max="9" min="9" style="24" width="15.0000"/>
    <col bestFit="1" customWidth="1" max="10" min="10" style="14" width="30.0000"/>
    <col bestFit="1" customWidth="1" max="11" min="11" style="14" width="25.0000"/>
    <col bestFit="1" customWidth="1" max="12" min="12" style="4" width="15.0000"/>
    <col bestFit="1" customWidth="1" max="13" min="13" style="4" width="15.0000"/>
    </cols>
    In the previous versions, that showed the data correctly, I see that dYMD (in fact all formats) results in style="0"

    HTML Code:
    <cols>
    <col bestFit="1" customWidth="1" max="1" min="1" style="0" width="15.0000"/>
    <col bestFit="1" customWidth="1" max="2" min="2" style="0" width="15.0000"/>
    <col bestFit="1" customWidth="1" max="3" min="3" style="0" width="10.0000"/>
    <col bestFit="1" customWidth="1" max="4" min="4" style="0" width="30.0000"/>
    <col bestFit="1" customWidth="1" max="5" min="5" style="0" width="15.0000"/>
    <col bestFit="1" customWidth="1" max="6" min="6" style="0" width="15.0000"/>
    <col bestFit="1" customWidth="1" max="7" min="7" style="0" width="15.0000"/>
    <col bestFit="1" customWidth="1" max="8" min="8" style="0" width="15.0000"/>
    <col bestFit="1" customWidth="1" max="9" min="9" style="0" width="15.0000"/>
    <col bestFit="1" customWidth="1" max="10" min="10" style="0" width="30.0000"/>
    <col bestFit="1" customWidth="1" max="11" min="11" style="0" width="25.0000"/>
    <col bestFit="1" customWidth="1" max="12" min="12" style="0" width="15.0000"/>
    <col bestFit="1" customWidth="1" max="13" min="13" style="0" width="15.0000"/>
    </cols>
    I guess that solving an issue in Valence made an issue in my software visible but I am not sure how to solve this.

    Kind regards, Theo

  • #2
    Hi Theo,

    For comparison, if you go into Portal Admin > Apps and click the button to download that list to Excel (button shows when you hover over the Plus button in the lower right), do you see the same issue with style="0"?

    Comment


    • #3
      Hi Robert,

      The Apps excel download shows the Last Used date as yyyy-mm-dd
      The style used for this date column:
      Code:
      <col bestFit="1" customWidth="1" max="5" min="5" style="32" width="11.5000"/>
      In my sheet style dYMD is converted to style="24" so that seems not a correct comparison.

      Kind regards,
      Theo

      Comment

      Working...
      X