mbleron / excelgen Goto Github PK

Use Case: Make the background lightgray for most column headers, but lightskyblue and purple for specific columns.

We can create headers independent of the table data and have complete control over everything except AutoFilter. This is one workaround and the user can set autofilter manually if they need it, so priority for this seems fairly low.

If we want to set AutoFilter on, we must use setHeader or setTableHeader. This does not give us ability to change the format on individual column header cells.
I attempted setRangeStyle without success. I suspect the setHeader properties come last and overwrite those.

Hello,

I had some code that worked when ExcelGen was installed in the schema containing the views that were exported into Excel.
Now I am trying to have ExcelTable and ExcelGen installed into SYS and it seems that ExcelGen does not work in these circumstances.
Here after the code that fails:

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

   sqlQuery CT.VARCHAR1K           := q'{
SELECT CODE
     , LANGUAGE
     , PHRASE
     , PLURAL
     , ABBREVIATION
     , GENDER
     , PHRASE1
     , PHRASE2
     , STATUS
     , ORIGIN
  FROM V_VOCABULARY_TRANSLATION
}';
   ctxId    ExcelGen.ctxHandle;
   sheetId  ExcelGen.sheetHandle;
BEGIN
   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_frozen     => true
    , p_autoFilter => true
   );
   ExcelGen.createFile(
      p_ctxId     => ctxId
    , p_directory => 'TRAD_FOLDER_EN'
    , p_filename  => filename
                     || '.'
                     || fileext );
   ExcelGen.closeContext( p_ctxId => ctxId );
END;
/

and the error obtained:

Error report -
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SQL", line 1134
ORA-06512: at "SYS.EXCELGEN", line 3048742720
ORA-06512: at line 42
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

The ExcelGen error line number is weird because ExcelGen has only 3748 lines. The error is in the procedure createFile and I suspect that the error is in the call to getFileContent at line 3738.
Is that anything I did wrong?

Thank you

Hello,
Congrats for this very useful project.
I search the possibility to apply color to a column? I couldn’t find it.
Is it possible or will it be possible to do it in the future?
Thank you

How can we create an Excel file using bulk collect or a straightforward for loop instead of a ref cursor?
I need to handle large data files by utilizing values directly from the loop. The current procedure doesn't allow to retrieve the counter value effectively with a ref cursor.

Hi again.
We have some description cells, with a very large description, that need to set as 'Wrap text'.
We have options for DATE an NUMBERS but not for text.

function makeCellStyle (
p_ctxId in ctxHandle
, p_numFmtCode in varchar2 default null
, p_font in CT_Font default null
, p_fill in CT_Fill default null
, p_border in CT_Border default null
, p_alignment in CT_CellAlignment default null
, p_wrap_text in boolean default false
)

or in

function makeAlignment (
p_horizontal in varchar2 default null
, p_vertical in varchar2 default null
, p_wrap_text in boolean default null
)

I think this is to put in Pull request but :
"There aren’t any open pull requests."

If I try to create dummy Excel BLOB with natural number columns (1, 2, 3, 20, 50) it works.
But with columns with decimals the excel file is not correct (3.1415).

FUNCTION get_EXCEL_FROM_QUERY RETURN BLOB as
v_blob BLOB ;
v_ctxId ExcelGen.ctxHandle;
BEGIN
v_ctxId := ExcelGen.createContext();
ExcelGen.addSheetFromQuery (v_ctxId, 'TEST','SELECT 1 n1,2 n2 , 3.1415 pi from dual');
ExcelGen.setNumFormat (v_ctxId, '0.0000' );
v_blob := ExcelGen.getFileContent(v_ctxId);
ExcelGen.closeContext(v_ctxId);
return v_blob;
END get_EXCEL_FROM_QUERY;

Change 3.1415 to 31415 and it will work.

To test from SQL DEveloper:
SELECT EXCEL_GEN.get_EXCEL_FROM_QUERY from dual;

The example :
Setting sheet or column-level cell formats : sample5.xlsx
DO NOT WORKS

Is there merged cells or rows option? if yes, can you kindly provide an example?

Hello mbleron,

Many thanks for your great work, excelGen saved (and still saves) us a lot of time !

Here's what I could need your help on :
My use case is calling addSheetFromQuery with a SQL request I can’t modify, which returns text, dates & number, all in text format.
I’d like to know if there is a way to force a type to be applied on columns, even if not present in the source SQL.

e.g. I'd like to be able to call :
sql_request := 'select ''test'', to_char(sysdate) from dual';
sheet1 := ExcelGen.addSheetFromQuery(ctxId, sheetName, sql_request);

and still have a way to force sysdate to be recognized as a date in generated xls file, without modifying sql_request

Thanks !

Hello,

Do you have a forecast for the release date of v3? There are some interesting features announced in this version.

Thank you
Adrian Boangiu

When a cell has no value (NULL in a query result), a style applied to the row or column via set_table_column_properites or set_table_row_properties does not apply to the empty cell. This is most apparent when a background color is part of the style.
I note this with an example in Creating Multiple Row Headers with ExcelGen PL/SQL Spreadsheet Generator near the top of the blog post beneath the screenshot.
I suspect you are addressing an issue with setting the style at the sheet level which causes it to extend the style past the end of the data, but the current behavior seems suboptimal.

I would prefer that the set_table_[row,column]_properties methods recognize the boundaries of the query result set and set the property on all cells in the orthogonal region populated as a "table", whether an individual cell is populated or not.

A similar problem could happen with put_cell, so a method or parameter option to put a null value such that the background style is applied would be useful. I may be wrong on this one as my own code may have skipped null cells. :)

I could also be wrong on the situation with set_row_properties and set_column_properties. I'm asserting that they set the style for the entire sheet, spreading a background color past the extent of any data. That is my recollection, but I didn't build a test today to prove it.

Sometimes it is necessary to add a sheet to excel to store basic data, but it is not necessary to display it. Can this feature be supported?

We have cellStyleHandle assignment at the cell, column, row and header level.
We have Number, Date and DateTime formatting that can be applied at the sheet or workbook level.
My request is for a sheet (and/or workbook) level default cell style. Specifically our business partners want their giant spreadsheets to use Arial 8 point font.
Just like with column properties, I can see the header cells not necessarily using this default. That seems appropriate since the header will almost always need a modified style anyway.
The method we must use today is to set it in the header, but then also set the style for every column. That isn't terribly onerous, but it is easy to change the number of columns in the query and forget to update the loop limit for the style assignment.

Since the workaround isn't that hard, I don't think it is a very high priority. I might be able to figure out how to provide a pull request for this, but I know you did a lot of work in style management, including CSS, so it may be biting off more than I can chew.

When I use the putCell procedure to generate xlsx, If there are too many rows(half a million e.g), this error will occur

When creating a sheet from a query or cursor that returns 0 rows, and also setting a style using setTableFormat, Excel complains and offers to recover the workbook. If you answer Yes, the sheet with the column headers only is provided as expected, but without the style

.
If I comment out setTableFormat on line 11, Excel does not complain.

Workaround is to determine whether query will return any rows and only set the style if it does. Unfortunately, ExcelGen does not expose back to the caller the number of rows returned by the query or on the sheet. Whether the specific issue with the style can be addressed or not, it would be useful to add a function to return the number of rows the query generated for any given sheet.

Test case code:

WITH 
FUNCTION get_xlsx RETURN BLOB AS
    v_blob          BLOB;
    v_ctxId         ExcelGen.ctxHandle;
    v_sheetHandle   BINARY_INTEGER;
BEGIN
        v_ctxId := ExcelGen.createContext();
        v_sheetHandle := ExcelGen.addSheetFromQuery(v_ctxId, 'Test 0 rows','SELECT SYSDATE AS "my date col" FROM DUAL WHERE 1=0', p_sheetIndex => 1);
        -- freeze the top row with the column headers
        ExcelGen.setHeader(v_ctxId, v_sheetHandle, p_frozen => TRUE);
        ExcelGen.SetTableFormat(p_ctxid=> v_ctxId, p_sheetid=> v_sheetHandle, p_style=> 'TableStyleMedium7');
        
        v_blob := ExcelGen.getFileContent(v_ctxId);
        ExcelGen.closeContext(v_ctxId);
        RETURN v_blob;
END;
SELECT get_xlsx FROM DUAL
;

Currently 'Column names are derived from SQL source query'.

Would it be possible to set the column name?
This will avoid Oracle limits.

Currently, the width of the column is the same for everyone.
Would it be possible to set the width of the column, as part of format?

procedure setColumnFormat (
p_ctxId in ctxHandle
, p_sheetId in sheetHandle
, p_columnId in pls_integer
, p_format in varchar2
, p_column_name in varchar2 [ NO MANDATORY ]
, p_column_width in NUMBER [ NO MANDATORY ]
);

For example for this QUERY:
SELECT 1 as "This is the name of this column to test the name ÑÄÖÜ" from dual;

ExcelGen.addSheetFromQuery(ctxId, 'test', 'SELECT 1 as "This is the name of this column to test the name ÑÄÖÜß" from dual ');

Has this error:
ERROR SQLERRM: ORA-06502: PL / SQL: numeric or value error: string buffer too small

ORA-06512: in "SYS.DBMS_SQL", line 2084
ORA-06512: in "ARCHIVDBAP.EXCELGEN", line 1131
ORA-06512: in "ARCHIVDBAP.EXCELGEN", line 1199
ORA-06512: in "ARCHIVDBAP.EXCELGEN", line 2176
ORA-06512: in "ARCHIVDBAP.EXCELGEN", line 3007
ORA-06512: in "ARCHIVDBAP.TEST", line 90

can we create multi line dynamic headers with filters for different columns in sheet or table in a sheet? if we can please share example otherwise I'll request for enhancement. thanks

Using putStringCell with relative positioning can cause Excel to give error "We found a problem with some content in 'blahblah.xlsx'. Do you want us to try to recover as much as we can?...". Selecting Yes causes it to throw out the cell content that was added with putStringCell. The log of repairs says "Removed Records: Cell information from /xl/worksheets/sheet1.xml part". Happens with XLSB also.

Took several hours to isolate this into a simple, reproducible test case. There must be something subtle going on because adding more elements to the sheet, as you had in the sample named relative-positioning.sql, makes the problem go away. Using absolute positioning, the problem does not happen. Found in a much more complex program. Workaround for me is to use absolute positioning.

relative-positioning-bug.zip

I have a prototype for maintaining rowcount sum after each fetch in a member attribute of sql_metadata_t. Then for any given sheet/table combination, the below function will return the rowcount (but only after calling getFileContent/createFile and before closeContext). I can create a pull request if you are willing.
It is enough of a kludge that I can be talked out of it; however, I'm on my third client where eveybody wants the rowcount(s), even if it is after the spreadsheet is created. If you have a better suggestion for how to structure it, I'm willing to do the work. If you don't want to include it, I'm OK with that too.

 function getRowCount(
    p_ctxId       in ctxHandle
  , p_sheetIdx    in sheetHandle 
  , p_tableIdx    in tableHandle
  ) return pls_integer
  is
  begin
    loadContext(p_ctxId);
    return currentCtx.sheetDefinitionMap(p_sheetIdx).tableList(p_tableIdx).sqlMetadata.rowcount;
  end getRowCount;

Can i do column autofit?

Would it be possible to use your library to populate an existing excel file with data?

For example. I have an excel file with two sheets:

• "Sheet 1" - hidden sheet to be filled with data
• "Report" - visible sheet that is built on the data from "Sheet 1" with all the blows and whistled (formatting, pagination, print range, headers, footers, images etc.)

I would like to be able to:

• Open an existing workbook from a file or a blob variable in PL/SQL
• Populate data in an empty "Sheet 1" including column headers using an SQL query
• Save the workbook to a file or to a blob variable (column)

I would be really interesed if this is possible or if you have ever considered such use-case.

Hello,

ExcelGen does not have an uninstall script (like ExcelTable).

I create the following one (taking as example the one from ExcelTable and the ExcelGen install script):

prompt Dropping package ExcelGen ...
drop package ExcelGen;

prompt Dropping package XUTL_XLSB ...
drop package xutl_xlsb;

prompt Dropping package ExcelTypes ...
drop package ExcelTypes;

prompt Dropping type ExcelTableCell ...
drop type ExcelTableCell;

prompt Dropping package XUTL_OFFCRYPTO ...
drop package xutl_offcrypto;

prompt Dropping package XUTL_CDF ...
drop package xutl_cdf;

Best regards,

Package body xutl_xlsb obtained via:

git clone --recurse-submodules https://github.com/mbleron/ExcelGen.git

fails to compile. Error at line 1894 trying to read table xlsb_record_types.

I commented out the procedure read_all in the package spec and body in order to continue. It is not functionality I'm using.

Would it be possible to overload the procedures addSheetFromQuery and addTable to support queries with more than 32767 bytes in size?
At the moment the parameter p_query is defined as "in varchar2".

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

xutl_xlsb.put_BeginWsView(stream);

Currently the generated table is inserted fix position A1.
It would be very interesting to be able to place the table in the desired position, leaving space at the top.

And a method to assign the text to those cells.

README.md entries for putDateCell, putNumberCell and putStringCell have "putCell" as the procedure name. Perhaps you initially planned to make it overloaded.

Hi Marc Bleron!

I have not found how to set a special format for a particular column (or cell) to make the content link.

Is there a way to set the custom format?
Similar to this:

In a multisheet XLSB workbook, all sheet tabs appear selected when opening the file.
Issue is in XUTL_XLSB.make_BeginWsView where fSelected flag is set to 1 by default.

Hi,

I found when some not printable/special characters in string, the generated excel file cannot be opened.

This is a case I met:

`
test.xlsx
select '�' f1, ascii('�') f2, length('�') f3, lengthb('�') f4 from dual
F1 F2 F3 F4

� 26 1 1
`

The generated excel of above sql attached for your reference.

Regards
Wicky

I was unable to convince a security committee to allow gant execute on DBMS_CRYPTO. I'm not going to defend them, but understand they are under tremendous pressure to minimize privs, and the word crypto is scary, and they aren't really technically sophisticated. I stood no chance.

Consider using PLSQL_CCFLAGS and conditional compilation ($if $$use_crypto $then $end) to make it optional during install. I wound up just commenting out the relevant lines, but this technique is one I've used with much success.

PS
Awesome work delivering ExcelGen. I have users who love getting multi-tabbed spreadsheets instead of having to load multiple CSV files. Everybody happy happy! Also, your README.md inspired me to do better. Thanks much.

The documentation lacks the description of a parameter p_excludeCols in procedures addSheetFromQuery, addSheetFromCursor and addTable.
Was this intentional (maybe left only for testing purpose)?
I tried it today and found it pretty useful though.

I'm having a blast with version 3. My business users have been clamoring for multi-row headers and merged cell banners among other things and now it is possible. I see the code. Lots of hard work went into the release and I'm grateful.
I do not have an answer for this. I'm just sharing the pain for your consideration.

I understand and use the absolute location within the sheet to set the active Pane for freezing rows and columns. The fact that Excel makes you think about the upper left corner of the area that is allowed to scroll is backwards from thinking about what rows and columns are frozen. Everybody stumbles on that at first.

When writing code for this though, we are thinking in terms of the query. If I want to freeze the header row and 2 left most columns I need to figure out where the top left cell of the data is within the sheet. When we have the query column headers, the data starts on row 2. So as per your example, A2 freezes the top row but does not freeze any columns. C2 freezes the top (header) row and columns A and B. We implement this with makeCellRef(p_colIdx=>3,p_rowIdx=>2).

That's bad enough. When you throw in that the table may be offset within the sheet (like when I shift the query table down to insert more column header rows), it gets much worse. The fact that we are dealing with two different coordinate systems, one of which is 0 based and the other 1 based is an "I need to get out a piece of paper and draw this" gob-stopper. I've written a helper procedure for my immediate use case. It is messy and I'm not happy with it. Perhaps a version of setting the sheet anchor from the coordinate system of a tableId would be helpful. I want one that lets me use the coordinates of what I want to freeze, but that may be straying too far from what Excel does.

I can and will write my own utility to do what I want, but if I'm having this trouble, I expect others will as well. Something to consider for what I think will be a common use case of multi-row column headers.

Invalid sheet content generated when using setColumnFormat with no width.

running the procedure, in proc makeGradientSheet, from the line:

ExcelGen.putCell(ctx, sheet6, 3, 1, p_style => ExcelGen.makeCellStyleCss(ctx, 'background: linear-gradient(89.2deg, rgb(0, 0, 0) 10.4%, rgb(255, 0, 0) 37.1%, rgb(255, 216, 51) 64.3%, rgb(255, 255, 255) 90.5%);'));

generate error :
19:20:22 ORA-06502: PL/SQL: errore : errore di conversione da carattere a numero di numero o valore

hi, could anyone help i am trying to figure a way to multiple table datasets outputted to one sheet and defined in ExcelGen.addSheetFromQuery which column the data will go into any help would be much appreciated

Hi,
First of all, this is a very neat PL/SQL package.
Would it be possible to format a cell conditionally, based on its content ?
Example: if SQL column contains "X" then write the value in BOLD, etc..
Keep up the good work!
Thanks,

I had hoped to use one table for multi-row column headers, then the main table below them using relative positioning. It works but using setTableColumnProperties on both tables in the sheet does not work as expected. In my real code the resulting spreadsheet causes excel to crash with no information (which could be something else, but this is the first thing I could prove). In the attached example it sets the font on the first table but not the background color. On the second table it does not set the font.
I'm guessing both of these calls are really doing a sheet level property call and are stepping on each other?
multi-table-col-properties-bug.zip

bug_column_format.zip
Attached files demonstrate a bug that manifests under some conditions when overriding the column header name using setTableColumnProperties. The bug is that not all columns from the query are written to the spreadsheet. Observation from the original program is that the data type (date) may not have been correct on one of the last columns present when there is data. Not sure that hint is helpful.

The table in the zip file needs to be present for 2 of the 3 results in this demonstrattion. No data is needed. No data rows are involved in the demonstration, but it also happens with real data in the program where we found the problem.

Two of the cases show that the bug does NOT manifest unless both factors are present. I've narrowed it down as much as I can for now. To go further I would need to add debug statements in ExcelGen and I'm not yet up to speed on version 3.01 code and model.

If I can do anything that will help you with the test case, please let me know. Thanks Marc.

Could the procedures/functions addSheetFromQuery, addSheetFromCursor, addTable be extended to include an additional parameter to limit the maximum amount of rows to be fetched?
Something like the parameter p_max_rows in the APEX procedure APEX_EXEC.OPEN_QUERY_CONTEXT.
Or could I use the parameter p_pageSize for that purpose?
Maybe its the wrong place to do this limitation anyway and I should limit the rows in the query?

Hi mbleron:
First of all, thank you for the great work, ExcelGen really helps a lot !
I am wondering if ExcelGen has a way to fill formula into specific cell? (Sorry I can not find it in user guild).
For example: Excel colunn A1 ~ A10 contains user training hours. Assign B1 formula with =sum(A:A) for user dynamically summarize the data.
Thank you for your help.

Hi there,

I have an array of 6 to 20 queries with named tabs that I feed to ExcelGen.addSheetFromQuery. However, I noticed that EXCELGEN is somehow reordering the resulting tabs on the workbook in alphabetical order. How can we use the order specified by the array order?

Hello,

I want to use both tools from the same Oracle user but I cannot install with the actual scripts. I get the following error
Error report -
ORA-02303: cannot drop or replace a type with type or table dependents
02303. 00000 - "cannot drop or replace a type with type or table dependents"
when running the second tool install script for
create or replace type ExcelTableCell

I try to modify the install scripts but the components ExcelCommons and MSUtilities are at different version level and therefore I don't know which version shall I take. I think that both tools should share the same ExcelCommons and MSUtilities components.

Best regards

In table content format, sometime I need to align left or right.
Or some columns must be RED.

The use of makeCellStyle can only affect the header.

Apologies for not testing this earlier. I know it was my use case and request that led to this functionality.

Attached test case is configured to set the default style for the entire workbook. Commented out below creation of the sheet is setting it for a single sheet. Neither cause the cell content to use the specified font, while using that same cell style handle with setTableColumnProperties works just fine.
If I'm doing something wrong, it is not obvious. Please advise.
default_style_bug.zip

Marc,
First, version 3 has so many features that I want, it makes me excited to get it into environments I support so that I can take advantage of it. Outstanding work!

I was hoping to install Version 3 with a package name such as ExcelGen_v3 to avoid regression test of multiple production objects that use Version 2. The SDLC rules and processes under which many of us operate make that regression testing expensive.

Unfortunately, there are enough changes in ExcelCommons, especially xutl_xlsb, that I don't think that will fly. Even if the latest version of ExcelCommons is backwards compatible with ExcelGen v2 (I suspect it is), the rules under which we operate mandate regression testing. I need to version all of the changed objects in ExcelCommons and the calls to those in ExcelGen_v3 in order to avoid that regression test expense.

I'm willing to put some work into it, but the way I have done this in the past with version DEFINE constants as part of the object names is ugly and distracting in the editor. Another choice is to actually version the object names in the source. Not crazy about that either.

If I'm going to do it for myself only, I'll likley create a Perl script to transform all impacted names (less than a dozen I think) in a copy of the source for deployment. Brutish, but usually effective beyond the occasional need for a manual tweak.

Is this something you would like to see addressed, or is it beyond the scope of what you would like to support? If the former, I'm willing to put in some elbow grease with a little direction. Please advise.

I suspect this will be swept up in your redesign, but this is what I've observed.

If I call setColumnFormat to set the Excel format string for the number, then I call setColumnProperties to apply a style (font is my goal), then I get the font but not the number format.
If I call setColumnProperties first, then setColumnFormat, I get the number format, but not the style (font).

I looked through documentation and didn't see any way around this. I did not look at the code. Knowing that you are in the middle of reworking all this I didn't go any further.

Readme.md has setSheetProperties with a p_defaultStyle parameter but that does not exist in the package definition in the master branch (assume v3.1).
Looks like setDefaultStyle with a p_sheetId parameter became the preferred method for setting a default cell style and the documentation was just not updated.
Unfortunately, that does not seem to be working. I'll file separate issue for that with a test case. There may be something else I'm doing that stops it from working.