Ask Your Question

how do I export all sheets from a spreadsheet ? [closed]

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

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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-29 23:58:10.604796


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


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

3 Answers

Sort by » oldest newest most voted

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

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
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
  sURL = "file:///home/doug/Documents/test_macro" & sheets.ElementNames(i) & ".csv"
  ThisComponent.storeToURL(sURL, fileProps())
  i = i + 1
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

answered 2020-01-24 12:27:51 +0100

Erel Segal-Halevi gravatar image

I used this: and it worked fine

edit flag offensive delete link more

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

emagar gravatar image

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

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

Question Tools

1 follower


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

Seen: 7,432 times

Last updated: Jan 24 '20