Cross-platform Generate xlsx Document

[Neil_Burkholder]

Since this thread is closed I’m creating a new one.

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

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

In my extension I currently create spreadsheets using WinOLE.
There are several reasons I’d like to find an alternative.

1. Excel needs to be installed for this to work.
2. It only works on windows.
3. It is slow because Excel needs to be started.

In c# I use ClosedXML which is realy fast, but only works on windows.

GitHub - ClosedXML/ClosedXML: ClosedXML is a .NET library for reading,...

I’ve done some searching but it seems most of the methods use gems, and installing gems in SketchUp is not something I want to deal with (unless someone can show a fool proof way of delivering them with an extension).

All of these require installing gems.

GitHub - openxml/openxml-package: A Ruby implementation of OpenXmlPackage...

Parsing XLS and XLSX (MS Excel) files with Ruby?

Does anyone know of a way to do this in SketchUp Ruby?

[DanRathbun]

I thought Ken was writing something ? @kengey

[voquochai]

I use WriteXLSX

[kengey]

@DanRathbun

I thought Ken was writing something ? @kengey

Yes. I did.
If you only need to write xlsx file I would suggest having a look at writexlsx.

Regarding c#. That nowadays is cross platform.

[voquochai]

The WriteXLSX gem working on macOS and Win. I have used it in several of my projects. You can check it!

[kengey]

@Neil_Burkholder

In c# I use ClosedXML which is realy fast, but only works on windows.

You might wanr to have a look here if c# is your preference.

GitHub - dotnet/Open-XML-SDK: Open XML SDK by Microsoft

And, as I stated here above, c# is cross platform nowadays. I run net core apps on windows, osx, linux and even raspbian on raspberry pi.

[Neil_Burkholder]

@voquochai

I use WriteXLSX

@kengey

If you only need to write xlsx file I would suggest having a look at writexlsx.

That looks promising. To use I just need to copy the rb files from the lib folder to my extension?

[Neil_Burkholder]

@kengey

You might wanr to have a look here if c# is your preference.

The only C# I’ve ever done is WPF which is windows only, and a bit of UWP in Windows IoT for my Raspberry pi. I’ve know that C# was more available cross platform the last while by don’t have any experience with it. Would it be possible to create a cross platform C# library that could be called from Ruby? I did a little searching but wasn’t able to find any 1,2,3 tutorials. I’m looking at write_xlsx right now.

[Neil_Burkholder]

I’m getting hung up installing write_xlsx.

The write_xlsx/zip_file_util.srb has a require to some missing file.

require 'zip/zipfilesystem'

[kengey]

Yeah, that is a dependency that also has to be copied over.

[kengey]

@Neil_Burkholder

Would it be possible to create a cross platform C# library that could be called from Ruby?

yes, I essentially do that in Extension | SketchUp Extension Warehouse

[Neil_Burkholder]

@kengey

Yeah, that is a dependency that also has to be copied over.

Can you shed a little light on this? I copied the zip rb files from here:

I had to change the write_xlsx/zip_file_utils.rb from Zip::ZipFile to Zip::File

But now I’m still getting an error when closing the workbook. Do I have the wrong Zip library?

[Neil_Burkholder]

@Neil_Burkholder

Do I have the wrong Zip library?

I had to use an older version of the zip library.

GitHub - rubyzip/rubyzip at 0-9-x-stable

[DanRathbun]

FYI, with regard to RubyZip.

I had repackaged it into a SketchupExtension archive.
I have them on my disk, but do not find that I’ve posted them here before. (Possibly at SketchUcation?)

Anyway, I RBZ packaged RubyZip versions 1.1.6 and 1.2.0, PM me if you would like one or both.

But generally we’ve discussed it in the past …

@DanRathbun

It is a gem.

Gem::install('rubyzip')
require "zip"

Documentation:

http://www.rubydoc.info/github/rubyzip/rubyzip

Example:

begin
  require 'zip'
rescue
  Gem::install('rubyzip')
  require 'zip'
end

def zip_my_files(
  export_dir = "C:/some/path/to/export/files",
  zip_dir  = "C:/some/path/to/export/",
  zip_name = "export.zip"
)
  zip_path = File.join( zip_dir, zip_name )

  Zip::File.open(zip_path, Zip::File::CREATE) {|zipfile|
    Dir::chdir(export_dir) {
      files = Dir["*.*"]
      files.each do |file|
        zipfile.add( file, File.expand_path(file) )
      end
    }
  }
end

A little more on installing and catching errors for loading RubyZip here in …

• Keeping gems locally inside extension - #6 by DanRathbun

[Neil_Burkholder]

@DanRathbun

I had repackaged it into a SketchupExtension archive.

Disclaimer: I don’t know much about gems as my only Ruby experience in in SketchUp.

I’m assuming that only one version of a gem can be installed at a time? Since the write_xlsx gem requires RubyZip 0.9.xx, wouldn’t I be better off taking the source code of both gems and repackaging it in my own namespace? That would avoid conflict with extensions that rely on other versions.

[DanRathbun]

@Neil_Burkholder

I’m assuming that only one version of a gem can be installed at a time?

Yes, gems.

And I also just repacked them into an RBZ but did not purposefully put them into versioned submodules.

I did however package v1.2.0 into a CommuntiyLibraries namespace, so it would be separate from any toplevel installed gem.

@Neil_Burkholder

Since the write_xlsx gem requires RubyZip 0.9.xx, wouldn’t I be better off taking the source code of both gems and repackaging it in my own namespace?

This has been the norm when a particular version is required.

Probably why my experiments never gained any traction.

---

Although why it (write_xlsx) would require a specific older version is very strange.

The NoMethodError you show above may be due to the String#unpack1 method not being in Ruby 2.0.0. It only had String#unpack back then (and in some older SketchUp releases.)
Even Ruby 2.2.4 only had String#unpack.

So only SketchUp 2019 with Ruby 2.5.1 and higher will have the String#unpack1 method.

But what I find weird is that looking at RubyZip v1.2.0 code for "lib/zip/entry.rb" the read_zip_short() method does not call String#unpack1, … it calls the old String#unpack:

      def read_zip_short(io) # :nodoc:
        io.read(2).unpack('v')[0]
      end

Oh and the gemspec for write_xlsx has …

gem.add_runtime_dependency 'rubyzip', '>= 1.0.0'

Where did you see it needed to be exactly 0.9.xx ?

[DanRathbun]

Ah, okay I looked at the newest version RubyZip v2.3.0 on GitHub and it’s read zip methods are now using String#unpack1.

So it isn’t an issue with RubyZip nor write_xlsx, it is SketchUp’s packaged Ruby versions coming with SU2018 and earlier.

[Neil_Burkholder]

@DanRathbun

So it isn’t an issue with RubyZip nor write_xlsx, it is SketchUp’s packaged Ruby versions coming with SU2018 and earlier.

Right I tested this in 2018, although I’d like to maintain compatibility back to 2016.

@DanRathbun

Where did you see it needed to be exactly 0.9.xx ?

Here:

ruby on rails roo gem cannot load zip/zipfilesystem

Apparently 1.0 changed Zip::ZipFileSystem to Zip::FileSystem, and Zip::ZipFile to Zip::File
I tried changing the source of write_xlsx, but when I ran into the unpack1 issue I simply downloaded an older version and it worked.

[DanRathbun]

RubyZip 2.0.0 … 2.3.0 requires Ruby >= 2.4.x which means SketchUp >= 2019.0 (Ruby 2.5.1)

RubyZip 1.0.0 …1.3.0 requires Ruby >= 1.9.2 which means SketchUp >= 2014.0 (Ruby 2.0.0)

All older RubyZip versions require Ruby >= 1.8.7 which does not support SketchUp <= 2013 (Ruby 1.8.6)

[DanRathbun]

Also … according to the write_xlsx change log …

2013-09-07 v0.72.2
work well with rubyzip 1.0.0

2013-09-06 v0.72.1
specify rubyzip version : <1.0.0

So …

@Neil_Burkholder

Apparently 1.0 changed Zip::ZipFileSystem to Zip::FileSystem , and Zip::ZipFile to Zip::File

Yes RubyZip 1.0.0 simplified both some namespaces and files names. (“zip/zip_file.rb” became “zip/file.rb”, etc.)

But if you use write_xlsx greater than or equal v0.72.2 it should not be making old RubyZip calls.

The current write_xlsx version is 0.85.7. What version did you attempt to use ?