[Code] Geometry Creation from Excel worksheet data

[Code] Geometry Creation from Excel worksheet data

Postby Dan Rathbun » Sat Mar 12, 2016 7:10 pm

Here is Marthmatica's example, cleaned up and properly namespace wrapped.
(ref: viewtopic.php?f=180&t=23441&p=589250#p589171)

Basically if you use it as a template, you replace the outermost namespace module name "Author" with your own toplevel namespace module name. (And you can also rename the plugin module to whatever you wish, as well.)

I cannot test it currently as I have LibreOffice installed (instead of MS Office.)

XLTiler_0_2_3.rb


Code: Select all
# encoding: UTF-8
#
# Example SketchUp extension: Geometry creation from Excel worksheet data

require 'sketchup.rb'
require 'win32ole'

module Author # outer Author namespace module
  module XLTiler # this particular plugin module

    VERSION = '0.2.3'

    EXCEL = 'Excel.Application'

    @@loaded = false unless defined?(@@loaded)

    class << self # anonymous singleton proxy class instance

      def init()
        #
        @xl = nil
        #
      end ### init()

      def connect_to_excel()
        #
        if @xl.nil?
          # Use WIN320LE.new to start a new spreadsheet instance.
          @xl = WIN32OLE::new(EXCEL)
        else
          # Connect to an already open spreadsheet application.
          # Use WIN320LE.connect to connect to an already open Excel instance.
          @xl = WIN32OLE::connect(EXCEL)
        end
        #
        @xl.visible = TRUE
        #
      rescue
        false
      end ### connect_to_excel()

      def get_value(row,col)
        #
        @xl.activesheet.cells(row,col).value
        #
      end ### get_value()

      def get_size( *size )
        @rows = 24 if @rows.nil? # Rows are face count to create
        @cols = 12 if @cols.nil? # Columns have all 4 face points

        # Note cols is the face pts x1,y1,z1; x2,y2,z2... 1 through 4 xyz points; or 12 cols
        # XL row 1= pt1: 1,   1,   0   pt2: 1,   2,   0   pt3: 2,   2,   0   pt4: 2,   1,   0
        # XL row 2= pt1: 2,   1,   0   pt2: 2,   2,   0   pt3: 3,   2,   0   pt4: 3,   1,   0
        # and so on...

        if size.empty?
          # Use defaults
          rows = @rows  # Rows are face count to create
          cols = @cols  # Columns have all 4 face points
          # Now prompt user to change or confirm size:
          begin
            result = UI.inputbox(["Rows","Cols"],[rows,cols],"Matrix Size")
          rescue => e
            UI.messagebox(e.message)
            retry
          else
            rows, cols = result
          end
        else
          rows, cols = size
        end
        #
        return rows,cols
        #
      end ### get_size()

      # Define the method that is activated from that added menu item click
      def get_xltiler()

        unless connect_to_excel()
          UI.messagebox("Could not connect to Excel !")
          return
        end

        # Instantiate tile matrix origin for Excel rows and columns reference.
        # We'll use a hash whose keys are the integer row numbers. The values
        # will be arrays consisting of #{cols} number of values.
        matrix = {}

        # A return array of faces created:
        faces = []

        # Set row and column count
        result = get_size()
        return if !result # false if user cancelled inputbox.
        @rows, @cols = result

        # rows is how many face tiles you are creating in SketchUp, 1 face tile, per each row

        # BUILD matrix
        # step through all the XL rows
        for r in 1..@rows
          # Each row is an array of #{cols} number of values
          matrix[r]= []
          # In every row populate the matrix from Excel cell values:
          for c in 1..@cols
            matrix[r] << get_value(r,c)
          end
          # Advance loop to next row.
        end
       
        # THE MAIN FACE CREATING ROUTINE
        # stepping through rows, as how many tiles you are creating is total rows,
        # then stepping through cols to populate all 4 face pts per row
        # Get handles to our model and the Entities collection it contains.
        model = Sketchup.active_model
        act   = model.active_entities
        group = act.add_group
        cpt   = group.entities.add_cpoint( [0,0,0] )
        # We add a cpoint to keep group from garbage collection
        ents  = group.entities

        pts = []
        for r in 1..@rows
          row = matrix[r]
          pts.clear
          row.each_slice(3) {|ary| pts << Geom::Point3d::new(ary) }
          # Add new face per this row:
          ents.add_face( *pts )
          # Advance loop to next row.
        end

        cpt.erase! # no longer needed

        @xl = nil # release Excel reference

        return group # an group of the face objects created

      end ### get_xltiler()

    end # anonymous singleton proxy class instance


    ### RUN ONCE CODE
    #
    if !@@loaded

      # Add a menu item to launch our plugin,
      # in its SketchUp menu default name target,
      # and the name we are giving our function in the "Tools" menu.
      UI.menu("Tools").add_item("Get XLTiler") {
        # UI.messagebox("Ye Excel! come up from hell!")
        get_xltiler()
      }

      init()

      @@loaded = true

    end

  end # this particular plugin module
end # outer Author namespace module
1
Last edited by Dan Rathbun on Wed Apr 27, 2016 12:23 am, edited 1 time in total.
    I'm not here much anymore. But a PM will fire email notifications.
    User avatar
    Dan Rathbun 
    PluginStore Author
    PluginStore Author
     

    Re: [Code] Geometry Creation from Excel worksheet data

    Postby Dan Rathbun » Thu Mar 31, 2016 9:45 am

    Updated to v 0.2.1

    Changes to get_size() method:
    • Had neglected to assign good results from inputbox.
    • Wrapped inputbox in begin...rescue to trap and retry input type errors.
    0
      I'm not here much anymore. But a PM will fire email notifications.
      User avatar
      Dan Rathbun 
      PluginStore Author
      PluginStore Author
       

      Re: [Code] Geometry Creation from Excel worksheet data

      Postby Dan Rathbun » Wed Apr 27, 2016 12:34 am

      :!:


      Updated to v 0.2.2

      Fixed get_size() method inputbox begin ... rescue block.
      • Added else clause, and moved rows,cols = results statement inside it. (Was attempting to reference results out of scope.)


      Updated to v 0.2.3

      Fixed get_xltiler() method:
      • Fixed call to group.entities.add_cpoint( [0,0,0] )
        (Argument was a list of 3 values. Need to be an Array or Geom::Point3d object.)


      NOTE: There is a faces = [] statement that is unused in the get_xltiler() method. Originally I returned the created faces in an array, but realized they needed to be in a group to prevent unintended geometry interaction. And later,... forgot to remove the statement.
      0
        I'm not here much anymore. But a PM will fire email notifications.
        User avatar
        Dan Rathbun 
        PluginStore Author
        PluginStore Author
         

        Re: [Code] Geometry Creation from Excel worksheet data

        Postby picpic020960 » Thu Sep 01, 2016 9:07 am

        Bonjour ,

        verry intresting !
        i test with excel and SU14 but when i click on tools/get xltiler after load the .rb file

        , exel open and 'matrix size' window appear , and when click this issue
        -----------------------------------------------------
        load 'c:\temp\XLTiler_0_2_3.rb'
        true
        Error: #<TypeError: no implicit conversion to float from nil>
        c:/temp/XLTiler_0_2_3.rb:128:in `initialize'
        c:/temp/XLTiler_0_2_3.rb:128:in `new'
        c:/temp/XLTiler_0_2_3.rb:128:in `block (2 levels) in get_xltiler'
        c:/temp/XLTiler_0_2_3.rb:128:in `each'
        c:/temp/XLTiler_0_2_3.rb:128:in `each_slice'
        c:/temp/XLTiler_0_2_3.rb:128:in `block in get_xltiler'
        c:/temp/XLTiler_0_2_3.rb:125:in `each'
        c:/temp/XLTiler_0_2_3.rb:125:in `get_xltiler'
        c:/temp/XLTiler_0_2_3.rb:154:in `block in <module:XLTiler>'
        -e:1:in `call'
        --------------------------------------------------------------
        thanks for help or little tutorial with all step (excel and sketchup/ruby)

        Regards
        0

        picpic020960 
         

        Re: [Code] Geometry Creation from Excel worksheet data

        Postby picpic020960 » Thu Sep 01, 2016 12:19 pm

        OK

        works fine !

        but only read data.

        how to for write data to excel from ruby ?

        other question :

        exists 'macro' recorder as in Excel ?

        thanks
        0

        picpic020960 
         

        Re: [Code] Geometry Creation from Excel worksheet data

        Postby Dan Rathbun » Fri Sep 02, 2016 9:11 pm

        picpic020960 wrote:how to for write data to excel from ruby ?

        Try something simple like:
        Code: Select all
              def set_value(row,col,val)
                #
                @xl.activesheet.cells(row,col).value = val
                #
              end ### set_value()



        picpic020960 wrote:other question :

        [Do you know if there] exists 'macro' recorder as in Excel ?

        Whatever exists in Excel, and has a built-in VisualBasic interface, can be accessed via WIN32OLE (from Ruby.)

        So, refer to the VisualBasic object model for Excel:
        MSDN: Object model (Excel VBA reference)
        0
          I'm not here much anymore. But a PM will fire email notifications.
          User avatar
          Dan Rathbun 
          PluginStore Author
          PluginStore Author
           

          SketchUcation One-Liner Adverts

          by Ad Machine » 5 minutes ago



          Ad Machine 
          Robot
           



           

          Return to Developers' Forum

          Who is online

          Users browsing this forum: No registered users and 5 guests