[Code] Geometry Creation from Excel worksheet data

[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

[Dan Rathbun]

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.

[Dan Rathbun]

---

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.

[picpic020960]

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

[picpic020960]

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

[Dan Rathbun]

@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)