Ask Your Question
1

how do I export all sheets from a spreadsheet ?

asked 2015-03-03 15:37:30 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I have a bunch of .xls and .xlsx files

Each of them contains lots of sheets (tabs)

I'd like to use LibreOffice headless (or UnoConv maybe ?) to _programmatically_ export EVERY sheet in csv format

Currently I am using a hack written in Python.

edit retag flag offensive close merge delete

Comments

I've opened an enhancement bug report[¹] to have this feature!

[¹] https://bugs.documentfoundation.org/s...

piviul gravatar imagepiviul ( 2016-07-08 15:15:02 +0200 )edit

2 Answers

Sort by » oldest newest most voted
2

answered 2015-07-26 05:02:21 +0200

doug gravatar image

The solution is to iterate through the available sheets, making each active in sequence, and for each execute the storeToURL method while applying the filter "Text - txt - csv (StarCalc)". The following code example does this. Edit the file path to conform to your own situation:

REM  *****  BASIC  *****

Sub convertSheetsToCSVs
Dim fileProps(0) as new com.sun.star.beans.PropertyValue
sheets = ThisComponent.Sheets

fileProps(0).Name = "FilterName"
fileProps(0).Value = "Text - txt - csv (StarCalc)"

i = 0

Do While sheets.Count > i
  sheet = sheets.getByIndex(i)
  cntrllr = ThisComponent.CurrentController
  cntrllr.setActiveSheet(sheet)
  sURL = "file:///home/doug/Documents/test_macro" & sheets.ElementNames(i) & ".csv"
  ThisComponent.storeToURL(sURL, fileProps())
  i = i + 1
Loop
End Sub

(if this answers your question please accept the answer by clicking the check mark (image description) to the left)

edit flag offensive delete link more
0

answered 2018-09-20 07:42:51 +0200

emagar gravatar image

updated 2018-09-20 08:03:20 +0200

In @doug 's answer, be sure to set a location in your machine. To save output in your Documents folder:

sURL= "file:///home/your-machine-name/Documents/" & sheets.ElementNames(i) & ".csv"
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-03-03 15:37:30 +0200

Seen: 5,348 times

Last updated: Sep 20 '18