Files created with masked content

[adrianboangiu]

Hello,

I have used the following procedure (part of the package TRAD_UTILS) to create a file (I deleted all the code not related with the ExcelGen):

   PROCEDURE t2e_Vocabulary( lang TRANSLATION_LANGUAGE.TRLN_LANGUAGE%TYPE := NULL )
   AS
      l_scope  logger_logs.scope%TYPE := gc_scope_prefix || 't2e_Vocabulary';
      l_params logger.tab_param;

      filename CONSTANT VARCHAR2( 20 ):= 'vocabulary';
      fileext  CONSTANT VARCHAR2( 4 ) := 'xlsx';

      sqlQuery CT.VARCHAR1K           := q'{
SELECT CODE
     , LANGUAGE
     , PHRASE
     , PLURAL
     , OPTIONAL
     , ABBREVIATION
     , GENDER
     , PHRASE1
     , PHRASE2
     , STATUS
     , ORIGIN
  FROM V_VOCABULARY_TRANSLATION
}';
      ctxId    ExcelGen.ctxHandle;
      sheetId  ExcelGen.sheetHandle;
   BEGIN
      FOR i IN 1..languages.COUNT
      LOOP
         IF NVL( lang, languages( i ) )= languages( i ) THEN
            setMainLanguage( languages( i ) );
            ctxId   := ExcelGen.createContext( );

            sheetId := ExcelGen.addSheetFromQuery(
                          p_ctxId     => ctxId
                        , p_sheetName => filename
                        , p_query     => sqlQuery );

            ExcelGen.setTableFormat(
               p_ctxId   => ctxId
             , p_sheetId => sheetId
             , p_style   => 'TableStyleLight8' );
            ExcelGen.setHeader(
               p_ctxId      => ctxId
             , p_sheetName  => filename
             , p_style      => ExcelGen.makeCellStyle(
                                  p_ctxId => ctxId
                                , p_font  => ExcelGen.makeFont( 'Calibri', 10, true )
                                --, p_fill  => ExcelGen.makePatternFill( 'solid', 'LightGray' )
                               )
             , p_frozen     => true
             , p_autoFilter => true
            );
            ExcelGen.setColumnFormat(
               p_ctxId    => ctxId
             , p_sheetId  => sheetId
             , p_columnId => 1
             , p_width    => 20
            );
            ExcelGen.setColumnFormat(
               p_ctxId    => ctxId
             , p_sheetId  => sheetId
             , p_columnId => 2
             , p_width    => 4
            );
            ExcelGen.setColumnFormat(
               p_ctxId    => ctxId
             , p_sheetId  => sheetId
             , p_columnId => 3
             , p_width    => 80
            );
            ExcelGen.setColumnFormat(
               p_ctxId    => ctxId
             , p_sheetId  => sheetId
             , p_columnId => 4
             , p_width    => 40
            );
            ExcelGen.setColumnFormat(
               p_ctxId    => ctxId
             , p_sheetId  => sheetId
             , p_columnId => 5
             , p_width    => 40
            );
            ExcelGen.setColumnFormat(
               p_ctxId    => ctxId
             , p_sheetId  => sheetId
             , p_columnId => 6
             , p_width    => 10
            );
            ExcelGen.setColumnFormat(
               p_ctxId    => ctxId
             , p_sheetId  => sheetId
             , p_columnId => 7
             , p_width    => 10
            );
            ExcelGen.setColumnFormat(
               p_ctxId    => ctxId
             , p_sheetId  => sheetId
             , p_columnId => 8
             , p_width    => 80
            );
            ExcelGen.setColumnFormat(
               p_ctxId    => ctxId
             , p_sheetId  => sheetId
             , p_columnId => 9
             , p_width    => 80
            );
            ExcelGen.setColumnFormat(
               p_ctxId    => ctxId
             , p_sheetId  => sheetId
             , p_columnId => 10
             , p_width    => 4
            );
            ExcelGen.setColumnFormat(
               p_ctxId    => ctxId
             , p_sheetId  => sheetId
             , p_columnId => 11
             , p_width    => 4
            );
            /*
            ExcelGen.setDateFormat(
               p_ctxId   => ctxId
             , p_sheetId => sheetId
             , p_format  => 'dd/mm/yyyy' );
            */
            ExcelGen.createFile(
               p_ctxId     => ctxId
             , p_directory => 'TRAD_FOLDER_' || UPPER( languages( i ) )
             , p_filename  => filename
                              || '.'
                              || LOWER( languages( i ) )
                              || '.'
                              || fileext );
            ExcelGen.closeContext( p_ctxId => ctxId );
         END IF;
      END LOOP;
   END t2e_Vocabulary;

I call the procedure

declare
begin
   TRAD_UTILS.exportExcel( 'RO' );
end;
/

My file is created but:

1. its content is masked (columns arehidden)
2. the column widths have been lost (when displayed). vocabulary.ro.xlsx

What is very strange is that the code worked before and I don't remember any modification of ExcelGen or my package functions in between. Another strange thing I noticed is that if I set the column format twice for the 10th column (instead of setting the format of the 11th colum) the last column is the only one visible.

If I compare the content of the new created file withe the content of previously created files I can see that the folder docProps is missing and the folder theme is missing from the folder xl: instead of

Can you help me see what is going wrong? Thank you.

Best regards, Adrian Boangiu

[mbleron]

Hi Adrian,

Have you changed session NLS_NUMERIC_CHARACTERS since the code last worked? The issue is on this line. NLS param is missing for the TO_CHAR conversion so it inherits session-level value. It should be : to_char(getColumnWidth(sd.columnMap(i).width),'TM9',NLS_PARAM_STRING)

I should be able to commit a code fix today.

Regarding docProps and theme, those are optional parts.

[mbleron]

Fixed in v2.5.1

[adrianboangiu]

Hello, I certainly haven't changed my client version and parameters (SqlDeveloper). I have to see how the session value is computed because one of the changes is that the code is run with another user.

Thank you for your correction.