Create an Excel-file with PL/SQL 20188367001

Create an Excel-file with PL/SQL

For this project I took an Apex-plugin I have written, (IR) Report to Excel (xlsx), and turned it into a PL/SQL package. With this package it’s very easy to create an Excel 2007 file with only a few lines of PL/SQL code.

begin
as_xlsx.query2sheet( 'select * from dual' );
as_xlsx.save( 'MY_DIR', 'my.xlsx' );
end;

The main purpose for this package is getting data from the database into an Excel file, so I deliberate did not include some Excel functionality, such as formulas, into the package. Excel itself is a far better tool for such things.

Anton

The source code for the package: as_xlsx
P.S. I have added the possibility to add Comments and MergedCells to the Excel-file
P.S.2 And bold/italic fonts
P.S.3 Fixed issue with timezones with a regionname
P.S.4 Fixed issue with XML-escaping from text
P.S.5 Fixed NLS-issue with column width
P.S.6 Added p_rgb to get_font
P.S.7 Fixed bug in add_string
P.S.8 Fixed set_autofilter (only one autofilter per sheet, added _xlnm._FilterDatabase)
Added list_validation = drop-down
P.S.9 Added freeze_pane
P.S.10 Fixed another bug in add_string
P.S.11 version 20, but important change: changed to date1904=false

136 Comments

  1. Michael Gilmore August 14, 2014
  2. Tony Miller August 11, 2014
    • Anton Scheffer August 12, 2014
      • Tony Miller August 12, 2014
        • Charles Tannenbaum August 27, 2014
  3. Joseph Charpak July 31, 2014
  4. Greg July 14, 2014
  5. Elad July 13, 2014
  6. Moritz Klein (@commi235) July 10, 2014
  7. youssef July 10, 2014
  8. Ajit Bakshi May 27, 2014
  9. stf174 May 21, 2014
  10. Dheeraj April 16, 2014
    • Ramkumar April 22, 2014
      • Anton Scheffer April 22, 2014
  11. Pinta April 13, 2014
    • Pinta April 13, 2014
    • Anton Scheffer April 13, 2014
      • Pinta April 13, 2014
  12. Boydus April 2, 2014
    • Anton Scheffer April 2, 2014
  13. Bala March 27, 2014
    • Anton Scheffer March 27, 2014
  14. mrbrightwork January 8, 2014
    • Anton Scheffer January 8, 2014
  15. Anton Scheffer December 16, 2013
    • tony miller December 16, 2013
  16. Robertson December 16, 2013
  17. Robertson December 12, 2013
  18. Bill December 9, 2013
    • Anton Scheffer December 9, 2013
      • Bill December 9, 2013
  19. Ralph Bieber December 3, 2013
  20. Newbie December 1, 2013
  21. Newbie December 1, 2013
    • Anton Scheffer December 1, 2013
  22. Albain DSM November 6, 2013
    • Anton Scheffer November 7, 2013
  23. Chuck Wolf October 31, 2013
    • Anton Scheffer October 31, 2013
      • Chuck Wolf October 31, 2013
        • tony miller October 31, 2013
          • Chuck Wolf October 31, 2013
        • Anton Scheffer October 31, 2013
          • tony miller October 31, 2013
  24. tony miller October 28, 2013
  25. Adam Stearn October 25, 2013
  26. Mike Chambers September 23, 2013
  27. carzmetic September 19, 2013
  28. euelvis September 14, 2013
  29. Mike Chambers August 29, 2013
    • euelvis September 14, 2013
  30. Mike Chambers August 29, 2013
  31. medan August 26, 2013
  32. gilles August 13, 2013
  33. Robyn Dyke July 1, 2013
  34. gilles April 4, 2013
    • Btissam August 13, 2013
      • Anton Scheffer August 13, 2013
  35. Stefan Pastrilov February 24, 2012
  36. James Van Zandweghe February 17, 2012
  37. James Van Zandweghe February 16, 2012
    • Anton Scheffer February 16, 2012
  38. dieterdaniel January 19, 2012
  39. Anton Scheffer January 19, 2012
  40. dieterdaniel January 19, 2012
  41. dieterdaniel January 19, 2012
    • Anton Scheffer January 19, 2012
  42. TonyReed January 17, 2012
  43. TonyReed December 6, 2011
    • Anton Scheffer December 7, 2011
  44. TonyReed December 6, 2011
  45. TonyReed December 6, 2011
    • Anton Scheffer December 6, 2011
  46. TonyReed December 6, 2011
  47. Amit December 5, 2011
  48. TonyReed December 5, 2011
    • Anton Scheffer December 5, 2011
  49. jareeq December 5, 2011
  50. Amit December 4, 2011
    • Anton Scheffer December 4, 2011
  51. kk November 30, 2011
  52. Peter November 9, 2011
    • Anton Scheffer November 9, 2011
  53. Peter November 9, 2011
    • Anton Scheffer November 9, 2011
  54. Peter November 8, 2011
    • Anton Scheffer November 8, 2011
  55. Peter November 7, 2011
  56. Anton Scheffer October 27, 2011
  57. Peter October 27, 2011
  58. gary October 27, 2011
  59. jareeq October 10, 2011
  60. jareeq October 7, 2011
  61. jareeq October 7, 2011
    • Anton Scheffer October 9, 2011
  62. Anton Scheffer September 29, 2011
  63. hUGO September 29, 2011
  64. prunoki September 22, 2011
  65. prunoki September 21, 2011
  66. prunoki September 20, 2011
    • Anton Scheffer September 20, 2011
  67. Vivian September 20, 2011
    • Anton Scheffer September 20, 2011
  68. Partha Sarathi Paul September 13, 2011
    • Anton Scheffer September 14, 2011
  69. Dirk Leiendorf September 7, 2011
    • Anton Scheffer September 7, 2011
  70. Tomaž September 7, 2011
    • Anton Scheffer September 7, 2011
  71. Abe August 10, 2011
  72. Peter August 10, 2011
  73. Stevenquave August 1, 2011
  74. stevenquave July 28, 2011
    • Anton Scheffer July 28, 2011
  75. Anton Scheffer July 27, 2011
  76. Stevenquave July 27, 2011
  77. dpigetyphie April 9, 2011
  78. dpigetyphie April 8, 2011
    • Anton Scheffer April 8, 2011
  79. Björn March 22, 2011
  80. Björn March 22, 2011
    • Anton Scheffer March 22, 2011
  81. Vasya March 17, 2011
  82. Anton Scheffer March 16, 2011
  83. Vasya March 16, 2011
  84. Achim Rosenfeld March 3, 2011
  85. Achim Rosenfeld March 2, 2011
    • Anton Scheffer March 2, 2011
  86. Vasya March 2, 2011
  87. Anton Scheffer March 1, 2011
  88. Pradeep February 25, 2011
  89. Anton Scheffer February 21, 2011
  90. Marc Thompson February 21, 2011
  91. Anton Scheffer February 20, 2011
  92. Anon February 20, 2011