Export Bill of Materials to Excel; make in Ruby or SDK?

Developers
1

Dear Developers,

I see a lot of BOM extensions but none of them gives you the possibility to make your own layout of the columns exported to excel; I want to fit them to a specific import format from another application.
When you make an extension specifically for this; do you use the Ruby API or the Sketchup SDK ?

kind regards, Jan-Pieter

2

why not use the vba in excel to do this?

3

Hi PC moor,

Thanks for the answer; I think VBA won’t work. The situation is this:

  • I have a web application which imports xls files BUT with only in a specific format/layout of the columns;
  • the idea is to create/export from Sketchup in one click a spreadsheet in the same format that can be directly imported in the web application;
  • next to that you need to couple it to your model (a building)

kind regards, Jan-Pieter

4

.xls is the old binary proprietary Microsoft format.

Nowadays most are using the newer XML text format .xlsx,
… does your web application take these XML spreadsheets ?

Ruby has a standard library (REXML) to read and write XML files:
http://ruby-doc.org/stdlib-2.2.4/libdoc/rexml/rdoc/index.html

Can it take CSV table files ?

SketchUp Pro’s Generate Report can export comma separate values files, which can also be imported to LayOut as tables.

Ruby has a standard library class (CSV) that can read and write CSV files:
http://ruby-doc.org/stdlib-2.2.4/libdoc/csv/rdoc/index.html

You have two options in Sketchup:

I’ve answered the Ruby API above.

For the SketchUp SDK you might need to seek out third party C libraries to read and write CSV, XLS or XLSX (XML) files. The SDK has some examples of writing out to an XML file.
However, currently the SDK is standalone for reading and writing directly to SKP files. It is not easliy hooked into the “live” SketchUp application process.

5

On Windows you can use WinOLE.

def self.send_to_excel_book(book_name)
  
  #connect to the workbook
  begin
    xl = WIN32OLE.connect("Excel.Application")
  rescue
    return
  end
  
  bk = nil
  for bknum in 1..xl.workbooks.count
    if xl.workbooks.item(bknum).name == book_name 
      bk = xl.workbooks.item(bknum)
      break
    end     
  end
  for shtnum in 1..bk.worksheets.count
    if bk.worksheets.item(shtnum).name == "Quotation"
      sht = bk.worksheets.item(shtnum)
      break
    end
  end
  unless bk and sht
    xl.ole_free
    xl = nil
    GC.start
    return
  end
  
  #send to pictures to the workbook
  path = bk.path
  fpath1 = path + "\\images\\" + bk.name + '(1).jpg'
  fpath2 = path + "\\images\\" + bk.name + '(2).jpg'  
  pic1 = @bcpath + 'sub programs/view1.jpg'
  pic2 = @bcpath + 'sub programs/view2.jpg'
  if File.file?(pic1) then FileUtils.cp_r(pic1, fpath1) end 
  if File.file?(pic2) then FileUtils.cp_r(pic2, fpath2) end 
  fpath11 = fpath1.gsub('/','\\')
  fpath21 = fpath2.gsub('/','\\')
  sht.Shapes.AddPicture(fpath11,0,-1,339.5,186,409.0313,251.7116)
  sht.Shapes.AddPicture(fpath21,0,-1,339.5,438,409.0313,251.7116)
  
  #show the workbook/sheet and bring it to the front
  sht.activate
  xl.application.WindowState = -4143
  w = WIN32OLE.new("WScript.Shell")
  w.AppActivate(xl.activewindow.Caption)
  w.ole_free
  xl.ole_free
  xl = nil
  GC.start
end
6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.