Wednesday, January 13, 2016

Creating Excel (.xlsx) files with Oracle PL/SQL

The XLSX Excel file format is actually a zipped archive of some xml files with the suffix .xlsx rather than .zip This makes it possible to create XLSX files with (almost) any programming language that can create files and zip them. Since I am working with PL/SQL every now and then and I needed to create xlsx files for a reporting solution, I crated a small library: XLSX writer for Oracle. The source code for this library is on this github repository, some examples are on my homepage.

24 comments:

  1. Hello Rene, I'm trying your package but generates error declaration zipper (AddFile, finish)

    ReplyDelete
  2. Hi Rene, I'm also getting the same error with zipper. Can you please add source for zipper as well?

    ReplyDelete
  3. Hi how can i write to existing excel template?
    Many thanks

    ReplyDelete
  4. Nice post mate, keep up the great work, just shared this with my friendz
    excel courses

    ReplyDelete
  5. How to add multiple sheet in sql_to_xlsx

    ReplyDelete
  6. how can i coloris the cell background?

    ReplyDelete
  7. Hi,

    Which values to color argument of xlsx_writer.add_font function?
    FFFFFF
    #FFFFFF
    red, blue, ...

    font_bold_colored := xlsx_writer.add_font (workbook, 'Arial', 11, 'red');

    ReplyDelete
  8. Hi, I tried also the color feature. I modified the xlsx_writer.xl_styles to change for this line :

    ap(ret, ' ');

    There is no error during the opening of the xlsx, but we don't see color.

    Do you have success with color cells ?

    Thanks.

    ReplyDelete
  9. I change the line no 583.

    color

    by

    color value=" ... "

    ReplyDelete
    Replies
    1. after change, no errors, but still not showing color

      Delete
    2. I made progress by changing to color rgb="...", but want to set the cell background color, bgColor rgb not working, I will continue to search the solution.

      Delete
  10. Hi, it's the same thing for me. I will continue to search the solution.

    What I want to do with colors is to have a sheet with "row striping color" !

    Thanks.

    ReplyDelete
  11. how to do with existing excel sheet

    ReplyDelete
  12. ou have to review the package to improve performance, it takes 30 min to generate a file of 2000 lines with 10 columns.
    It is necessary to empty the table of data rows columns after a number of treated lines (example 100 line), the procedure which loop the lines and the columns of the table is very slow.

    ReplyDelete
  13. Hi,
    how can I do to format number with more than two decimal place?

    eg: 12583.1234

    Alternatively is it possible to use #.# format?

    Best regards,
    Stefano.

    ReplyDelete
  14. Hi, trying to add_border but not sure what to put for raw_

    Do you have examples for adding border?

    ReplyDelete
  15. Hi,

    Also trying to add_border, do have an example?

    ReplyDelete
  16. does anyone had success to create cells with color in background or colored font?

    font_bold_colored := xlsx_writer.add_font (workbook, 'Arial', 11, 'red');

    I'm trying the command already commented by someone in 2019, but without any success till the moment.

    ReplyDelete
    Replies
    1. solution:
      font_bold := xlsx_writer.add_font (workbook, 'Arial', 12, color => 'rgb="ffff0000"');

      Delete
  17. does anyone have already included text orientation in xlsx_writer? I need to write Text with 90 degrees of orientation

    ReplyDelete
  18. Thanks a lot for this Library, Rene.

    I'm using the library to generate an excel file in Oracle APEX, and the library is working fine (with little fine tune because I'm not saving the file inside the package, just generating the BLOB), but I would like to go further in formatting capabilities. So, I would like to know whether it is intended to add next functionalities:

    1) To allow merging cells in the output
    2) To allow adding borders to the cells
    3) To allow adding background colors to the cells

    Thanks a lot
    Kind regards
    Mario

    ReplyDelete