Nerd For Tech
Published in

Nerd For Tech

Ruby on Rails: Using Tempfile and Fog-AWS Gem to read excel files saved in AWS S3

Image by WilliamsCreativity from Pixabay

Access file stored in AWS S3

gem 'fog-aws'

Initialize connection to AWS S3

s3 = Fog::Storage.new(
provider: 'AWS',
region: 'us-west-2',
aws_access_key_id: YOUR_S3_ACCESS_KEY,
aws_secret_access_key: YOUR_S3_SECRET_KEY
)
s3 = Fog::Storage.new(
provider: 'AWS',
region: 'us-west-2',
use_iam_profile: true
)

List all files and access files in AWS S3 bucket

s3_files = s3.directories.new(key: 'my-bucket').files
s3_file = s3.directories.new(key: 'my-bucket')
.files
.get('my_folder/test.xlsx')
<Fog::Storage::AWS::File
key="my_folder/test.xlsx",
cache_control=nil,
content_disposition=nil,
content_encoding=nil,
content_length=5488,
content_md5=nil,
content_type="binary/octet-stream",
etag="38eed92f509046d915583875dd45454f",
expires=nil,
last_modified=2021-07-13 03:41:48 +0000,
metadata={"x-amz-id-2"=>"XXX", "x-amz-request-id"=>"XXX"},
owner=nil,
storage_class=nil,
encryption=nil,
encryption_key=nil,
version=nil,
kms_key_id=nil
>

Create file URL and parse the file

s3 = Fog::Storage.new(
provider: 'AWS',
region: 'us-west-2',
use_iam_profile: true
)
s3_file = s3.directories.new(key: 'my-bucket')
.files
.get('my_folder/test.xlsx')
url = s3_file.url(Time.zone.now + 60)
s3_temp_file = URI.parse(url).open=>#<StringIO:0x0000555e74fe1438 @base_uri=#<URI::HTTPS https://my-bucket.s3-us-west-2.amazonaws.com/my_folder/test.xlsx?X-Amz-Expires=60&X-Amz-Date=20210713T151552Z&X-Amz-Security-Token=XXX#X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIARPKY2DTZXLCOCK65/20210713/us-west-2/s3/aws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=XXX>, @meta={"x-amz-id-2"=>"XXX", "x-amz-request-id"=>"4MNS2VFY3DT0JKAT", "date"=>"Tue, 13 Jul 2021 15:16:02 GMT", "last-modified"=>"Tue, 13 Jul 2021 03:41:48 GMT", "etag"=>"\"38eed92f509046d915583875dd45454f\"", "accept-ranges"=>"bytes", "content-type"=>"binary/octet-stream", "server"=>"AmazonS3", "content-length"=>"5488"}, @metas={"x-amz-id-2"=>["t+D6xAC6o1MrEwhHjyBFkPdQoBfvTmfQh4ytJ/lAheZ89R/u6yj78d6Secc0AuQhfUODdwedOIQ="], "x-amz-request-id"=>["4MNS2VFY3DT0JKAT"], "date"=>["Tue, 13 Jul 2021 15:16:02 GMT"], "last-modified"=>["Tue, 13 Jul 2021 03:41:48 GMT"], "etag"=>["\"38eed92f509046d915583875dd45454f\""], "accept-ranges"=>["bytes"], "content-type"=>["binary/octet-stream"], "server"=>["AmazonS3"], "content-length"=>["5488"]}, @status=["200", "OK"]>
s3_temp_file.read=>"PK\x03\x04\x14\x00\x00\x00\b\x00P\x1C\xEER\aAMb\x81\x00\x00\x00\xB1\x00\x00\x00\x10\x00\x00\x00docProps/app.xmlM\x8E=\v\x021\x10D\xFF\xCAq\xBD\xB7A\xC1Bb@\xD0R\xB0\xB2\x0F{\e/\x90dC\xB2B~\xBE9\xC1\x8Fn\x1Eo\x18F\xDF\ng*\xE2\xA9\x0E-\x86T\x8F\xE3\"\x92\x0F\x00\x15\x17\x8A\xB6N]\xA7n\x1C\x97h\xA5cy\x00;\xE7\x91\xCE\x8C\xCFHI`\xAB\xD4\x1E\xA8\t\xA5\x99\xE6M\xFE\x0E\x8EF\x9Fr\x0E\x1E\xADxN\xE6\xEA\xB1pe'\xC3\xA5!\x05\r\xFFrm\xDE\xA9\xD45\xEF&\xF5\x96\x1F\xD6\xF0;i^PK\x03\x04\x14\x00\x00\x00\b\x00P\x1C\xEERt\x80r,\xEF\x00\x00\x00+\x02\x00\x00\x11\x00\x00\x00docProps/core.xml\xCD\x92QK\xC30\x10\xC7\xBF\x8A\xE4\xBD\xBD\xA6\x15\x1D\xA1\xCB\x8BcO\n\x82\x03\xC5\xB7\x90\xDC\xB6`\x93\x86\xE4\xA4\xDD\xB77\xAD[\x87\xE8\a\xF01w\xFF\xFC\xEEwp\xAD\x0EB\xF7\x11\x9Fc\x1F0\x92\xC5t3\xBA\xCE'\xA1\xC3\x9A\x.....

Save Excel file as a temp file and open it

Create temp file

tempfile = Tempfile.new('foo')
=> #<File:/var/folders/57/m85kgny17n70l64v009zhcfhmyj0p9/T/foo20210715-83391-157to5v>
tempfile.write('hello world')
=> 11
Tempfile.create('foo') do |f|
f.write('hello world')
end

Set the temp file type and read the file

Tempfile.new(['foo', '.xlsx'], encoding: 'ascii-8bit')=> #<File:/var/folders/57/m85kgny17n70l64v009zhcfhmyj0p9/T/foo20210715-33983-e1vs8.xlsx>
tempfile = Tempfile.new('foo')
tempfile.write('hello world')
tempfile.read
=> ""
tempfile = Tempfile.new('foo')
tempfile.write('hello world')
tempfile.rewind
tempfile.read
=> "hello world"
s3_temp_file = URI.parse(url).open            
tempfile = Tempfile.new([test, '.xlsx'], encoding: 'ascii-8bit')
tempfile.write(s3_temp_file.read)
tempfile.rewind

Open the excel type tempfile(.xlsx)

xlsx = Roo::Spreadsheet.open(tempfile)
sheet = xlsx.sheet(0)

Reference

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
icelandcheng

Programming Skill learner and Sharer | Ruby on Rails | Golang | Vue.js | Web Map API