Quoting an answer from Scott Ming, which works with workbook containing multiple sheets:
Here is a python script getsheets.py (mirror), you should install pandas
and xlrd
before you use it.
Run this:
pip3 install pandas xlrd # or `pip install pandas xlrd`
How does it works?
$ python3 getsheets.py -h
Usage: getsheets.py [OPTIONS] INPUTFILE
Convert a Excel file with multiple sheets to several file with one sheet.
Examples:
getsheets filename
getsheets filename -f csv
Options:
-f, --format [xlsx|csv] Default xlsx.
-h, --help Show this message and exit.
Convert to several xlsx:
$ python3 getsheets.py goods_temp.xlsx
Sheet.xlsx Done!
Sheet1.xlsx Done!
All Done!
Convert to several csv:
$ python3 getsheets.py goods_temp.xlsx -f csv
Sheet.csv Done!
Sheet1.csv Done!
All Done!
getsheets.py
:
# -*- coding: utf-8 -*-
import click
import os
import pandas as pd
def file_split(file):
s = file.split('.')
name = '.'.join(s[:-1]) # get directory name
return name
def getsheets(inputfile, fileformat):
name = file_split(inputfile)
try:
os.makedirs(name)
except:
pass
df1 = pd.ExcelFile(inputfile)
for x in df1.sheet_names:
print(x + '.' + fileformat, 'Done!')
df2 = pd.read_excel(inputfile, sheetname=x)
filename = os.path.join(name, x + '.' + fileformat)
if fileformat == 'csv':
df2.to_csv(filename, index=False)
else:
df2.to_excel(filename, index=False)
print('\nAll Done!')
CONTEXT_SETTINGS = dict(help_option_names=['-h', '--help'])
@click.command(context_settings=CONTEXT_SETTINGS)
@click.argument('inputfile')
@click.option('-f', '--format', type=click.Choice([
'xlsx', 'csv']), default='xlsx', help='Default xlsx.')
def cli(inputfile, format):
'''Convert a Excel file with multiple sheets to several file with one sheet.
Examples:
\b
getsheets filename
\b
getsheets filename -f csv
'''
if format == 'csv':
getsheets(inputfile, 'csv')
else:
getsheets(inputfile, 'xlsx')
cli()