simple-spreadsheet-extractor

= Simple Spreadsheet Extractor

Authors:: Stuart Owen, Finn Bacall Version:: 0.17.0 Contact:: mailto:[e-mail] Licence:: BSD (See LICENCE or http://www.opensource.org/licenses/bsd-license.php) Copyright:: (c) 2010-2025 The University of Manchester, UK

{}[https://codeclimate.com/github/myGrid/simple-spreadsheet-extractor-gem]

{}[https://travis-ci.org/myGrid/simple-spreadsheet-extractor-gem]

== Synopsis

This is a simple gem that provides a facility to read an XLS or XLSX Excel spreadsheet document and produce an XML representation of its content.

CSV output can also be generated for a single sheet.

Internally it uses Apache POI, using the sister http://github.com/myGrid/simple-spreadsheet-extractor tool.

This is a simple tool developed for use within SysMO-DB[http://www.sysmo-db.org].

== Installation

Java 8 or above (JRE) is required.

gem install simple-spreadsheet-extractor

Note that Windows is no longer supported (since version 0.7.2.1)

== Usage

• require 'simple-spreadsheet-extractor'
• include the module SysMODB::SpreadsheetExtractor
• pass an IO object to the method spreedsheet_to_xml which responds with the XML for the contents of the spreadsheet. Alternatively use spreadsheet_to_csv for CSV.
  • you can now also pass in the filepath to the Excel file instead of an IO object
• if something goes wrong with the extraction then a SysMODB::SpreadsheetExtractionException will be thrown
• by default the JVM is allocated 512M of memory, you can override this by passing a string as the last argument. This will be passed to -Xmx in the java command.

e.g.

#examples/example.rb - takes path, i.e. ruby example.rb /tmp/spreadsheet.xls require 'rubygems' require 'simple-spreadsheet-extractor'

include SysMODB::SpreadsheetExtractor

path=ARGV.first

f=open path begin puts spreadsheet_to_xml(f) rescue SysMODB::SpreadsheetExtractionException=>e puts "Something went wrong #{e.message}" end

Formulas are evaluated placing the result in the XML produced for that cell, however the original formula is included as an attribute.

Row and column indexes start at 1, rather than 0, to keep consistent with namings of the cells in Excel.

An XSD schema for the XML is available in doc/schema-v1.xsd["tree/master/doc/schema-v1.xsd"]

The desired spreadsheet extractor jar can be specified by defining SPREADSHEET_EXTRACTOR_JAR_PATH in a config file (e.g. environment.rb)

CSV can be generated in a similar way, and also takes an optional sheet number. If the sheet number is missing then the first sheet is used.

Note that the sheet number for the first sheet is 1, and can either be a string or integer.

e.g.

puts spreadsheet_to_csv(f,"1")

== Example XML

<border-top>dotted 1pt</border-top> <border-bottom>dotted 1pt</border-bottom> <border-left>dotted 1pt</border-left> <border-right>dotted 1pt</border-right> <background-color>#ffcc99</background-color> <border-top>dotted 1pt</border-top> <border-bottom>dotted 1pt</border-bottom> <border-left>dotted 1pt</border-left> <border-right>dotted 1pt</border-right> <font-weight>bold</font-weight> <font-style>italics</font-style> <text-decoration>underline</text-decoration> <color>#2323dc</color> 13.0 654153.0 AA AB BA BB BC 547654.0 45465.0 14.0 2009-06-15T0:0:0+0100