########################################################################
####
#### Title:  cb2excel.magik
#### Author:  Mark Cederholm
#### Last Revised:  29-Jul-2002 option to export current table only
####
#### Notes:
####
#### Export collection_browser results to Excel.  Only displayed
#### physical, derived, or text join fields are populated.
####
#### This method is very dirty if interrupted because Excel will still
#### be active in memory.  You may wish to use task manager to kill
#### the instance.  Don't kill it, however, if you plan to use this
#### method again in your Smallworld session, as it may cause further
#### interruptions to hang.
####
########################################################################


#**********************************************************************
# Initialisation
#**********************************************************************

_block

   # Add messages for Excel export

   _local m << message_handler.new(:browser)
   m.create_message_non_ds(:export_to_excel, :en_gb, "Export to Excel")
   m.create_message_non_ds(:ee!no_records, :en_gb, "No records to export.")
   m.create_message_non_ds(:ee!multiple_tabs, :en_gb, "Multiple tables exist.")
   m.create_message_non_ds(:ee!export_current, :en_gb, "Export current table.")
   m.create_message_non_ds(:ee!export_all, :en_gb, "Export all tables.")
   m.create_message_non_ds(:ee!progress, :en_gb, "Processing... #1 of #2")

   # Add method to extra options menu

   collection_browser.extra_options.add({:export_to_excel,
                                         :|export_to_excel()|, _false})

_endblock
$

#**********************************************************************
# Public method
#**********************************************************************

_pragma(classify_level=basic, topic={query,browser}, usage={external})
_method collection_browser.export_to_excel()

   ##
   ## Export collection_browser results to Excel.  Only displayed
   ## physical, derived, or text join fields are populated.
   ##
   ## This method is very dirty if interrupted because Excel will still
   ## be active in memory.  You may wish to use task manager to kill
   ## the instance.  Don't kill it, however, if you plan to use this
   ## method again in your Smallworld session, as it may cause further
   ## interruptions to hang.
   ##

   # Check if valid set

   b << .browser_engine
   q << b.current_set
   _if q _is _unset _orif q.n_objects = 0
   _then
      _self.message_alert(:ee!no_records)
      _return
   _endif

   # Set up list of tables to export

   s << {_self.current_table}
   _if q.sources().size > 1
   _then
      result << _self.show_query({_self.message(:ee!export_current, "Export current table."),
                                  _self.message(:ee!export_all, "Export all tables.")},
                                 {:current, :all}, :current,
                                  _self.message(:ee!multiple_tabs, "Multiple tables exist."))
      _if result _is :all
      _then
         s << q.sources().as_simple_vector()
      _endif
   _endif

   _protect

      # Create and populate Excel workbook

      excel << ole_client.create_object("Excel.Application")
      workbooks << excel.workbooks()
      workbook << workbooks.add()

      # Loop through tables and export each one separately

      _for t _over s.fast_elements()
      _loop

         # Create sheet and export table

         sheet << excel.sheets.add()

         _self.int!export_to_excel(t, sheet)

         sheet.release_object()
         sheet.post_mortem()

      _endloop

      excel.visible << _true

   _protection

      sheet.release_object()
      sheet.post_mortem()
      workbook.release_object()
      workbook.post_mortem()
      workbooks.release_object()
      workbooks.post_mortem()
      excel.release_object()
      excel.post_mortem()
      system.gc(_true)
      system.gc(_true)

   _endprotect

_endmethod
$

#**********************************************************************
# Private method
#**********************************************************************

_pragma(classify_level=basic, topic={query,browser}, usage={external})
_private _method collection_browser.int!export_to_excel(t, sheet)

   ## Export contents of collection browser to an Excel spreadsheet.
   ## Only visible fields are sent out.

   # Date format: set as appropriate for Excel environment

   df << date_time_format.new_with_properties(:date_format_string, "#m/#d/#Y")

   # Set up visible fields

   b << .browser_engine
   q << b.current_set
   b.current_table << t

   vis_candidates << b.field_visibility
   vis << rope.new()
   formats << hash_table.new()
   _for f _over vis_candidates.fast_elements()
   _loop

      fname << f.name
      _if f.is_physical? _or f.is_derived? _then
         ftype << f.type
         _if ftype.enumerator _isnt _unset _then
            formats[fname] << :string
         _elif ftype.is_string? _then
            formats[fname] << :string
         _elif ftype.phys_type _is :ds_date _then
            formats[fname] << :date
         _endif
      _elif f.is_join? _then
         _if f.join_type _isnt :text _then
            _continue
         _endif
         formats[fname] << :string
      _else
         _continue
      _endif
      vis.add(f)

   _endloop

   # Title and captions

   title << t.external_name.write_string
   sheet.name << title

   row << 1
   sheet.cells(row,1).value << title
   row +<< 2
   width << rope.new()
   col << 1
   _for f _over vis.fast_elements()
   _loop
      val << f.external_name.write_string
      width.add(val.size)
      sheet.cells(row,col).value << val
      col +<< 1
   _endloop
   row +<< 2

   # Set up read stream for table

   test_proc << _proc(r)
                   _import t
                   >> (r.source_collection _is t)
                _endproc
   pred << predicate.using(test_proc)
   rset << q.record_read_stream(pred)

   # Read records and populate sheet

   stat << .item_hash[:status_label]
   recnum << 0
   nrecs << rset.size
   _loop

      e << rset.get()
      _if e _is _unset
      _then
         _leave
      _endif

      recnum +<< 1
      _if recnum _mod 10 = 0 _then
         msg << _self.message(:ee!progress,
                              "Processing...",
                              recnum.write_string,
                              nrecs.write_string)
         stat.label << msg
      _endif

      col << 1
      _for f _over vis.fast_elements()
      _loop

         fname << f.name
         valu << e.perform(fname)
         ftype << formats[fname]
         _if ftype _is :string
         _then
            val << "'" + valu.write_string
         _elif ftype _is :date _and valu _isnt _unset
         _then
            val << df.format(valu)
         _else
            val << valu.write_string
         _endif

         sheet.cells(row,col).value << val
         width[col] << width[col].max(val.size)

         col +<< 1

      _endloop

      row +<< 1

   _endloop

   # Resize columns, not to exceed 50
   # (to prevent Excel from bombing over long text)

   _for i _over range(1,width.size)
   _loop
      w << width[i] + 5
      sheet.columns(i).columnwidth << w.min(50)
   _endloop

   >> recnum

_endmethod
$