sketchucation logo sketchucation
    • Login
    🤑 SketchPlus 1.3 | 44 Tools for $15 until June 20th Buy Now

    [Code] Geometry Creation from Excel worksheet data

    Scheduled Pinned Locked Moved Developers' Forum
    6 Posts 2 Posters 1.9k Views 2 Watching
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • Dan RathbunD Offline
      Dan Rathbun
      last edited by Dan Rathbun

      Here is Marthmatica's example, cleaned up and properly namespace wrapped.
      (ref: http://sketchucation.com/forums/viewtopic.php?f=180%26amp;t=23441%26amp;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

      # 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
      
      

      I'm not here much anymore.

      1 Reply Last reply Reply Quote 0
      • Dan RathbunD Offline
        Dan Rathbun
        last edited by

        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.

        I'm not here much anymore.

        1 Reply Last reply Reply Quote 0
        • Dan RathbunD Offline
          Dan Rathbun
          last edited by

          âť—


          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.

          I'm not here much anymore.

          1 Reply Last reply Reply Quote 0
          • P Offline
            picpic020960
            last edited by

            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

            1 Reply Last reply Reply Quote 0
            • P Offline
              picpic020960
              last edited by

              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

              1 Reply Last reply Reply Quote 0
              • Dan RathbunD Offline
                Dan Rathbun
                last edited by

                @picpic020960 said:

                how to for write data to excel from ruby ?

                Try something simple like:

                      def set_value(row,col,val)
                        #
                        @xl.activesheet.cells(row,col).value = val
                        #
                      end ### set_value()
                
                

                @picpic020960 said:

                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)

                I'm not here much anymore.

                1 Reply Last reply Reply Quote 0
                • 1 / 1
                • First post
                  Last post
                Buy SketchPlus
                Buy SUbD
                Buy WrapR
                Buy eBook
                Buy Modelur
                Buy Vertex Tools
                Buy SketchCuisine
                Buy FormFonts

                Advertisement