Python - Automating Tasks - Working with CSV Files and JSON DATA(Excel-to-CSV Converter)

Automate the Boring Stuff with Python

開発環境

• OS X El Capitan - Apple (OS)
• Emacs (Text Editor)
• Python 3.5 (プログラミング言語)

Automate the Boring Stuff with Python (Al Sweigart (著)、No Starch Press)のPart 2.(Automating Tasks)、Chapter 14.(Working with CSV Files and JSON DATA)、Practice Projects(Excel-to-CSV Converter)(No. 8214)を取り組んでみる。

Practice Projects(Excel-to-CSV Converter)(No. 8214)

コード(Emacs)

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import os
import sys
import openpyxl
import csv

path = '.'
if len(sys.argv) > 1:
    path = sys.argv[1]

for excel_file in os.listdir(path):
    if (not os.path.isfile(excel_file)) or not excel_file.endswith('.xlsx'):
        continue
    print('{0}: Writing to csv...'.format(excel_file))
    wb = openpyxl.load_workbook(excel_file)
    excel_filename = excel_file[:-5]
    for sheet_name in wb.get_sheet_names():
        sheet = wb.get_sheet_by_name(sheet_name)
        csv_filename = '{0}_{1}.csv'.format(excel_filename, sheet_name)
        with open(csv_filename, 'w', newline='') as f:
            writer = csv.writer(f)
            for row_num in range(1, sheet.max_row + 1):
                row_data = []
                for col_num in range(1, sheet.max_column + 1):
                    row_data.append(
                        sheet.cell(row=row_num, column=col_num).value
                    )
                writer.writerow(row_data)

入出力結果(Terminal, IPython)

ls *.xlsx
dimensions.xlsx* merged.xlsx*  styles.xlsx*
duesRecords.xlsx* sampleChart.xlsx*
example.xlsx*  styled.xlsx*
$ ls *.csv
ls: *.csv: No such file or directory
$ ./spreadsheet2csv.py 
dimensions.xlsx: Writing to csv...
duesRecords.xlsx: Writing to csv...
example.xlsx: Writing to csv...
merged.xlsx: Writing to csv...
sampleChart.xlsx: Writing to csv...
styled.xlsx: Writing to csv...
styles.xlsx: Writing to csv...
$ ls *.xlsx
dimensions.xlsx* merged.xlsx*  styles.xlsx*
duesRecords.xlsx* sampleChart.xlsx*
example.xlsx*  styled.xlsx*
$ ls *.csv
dimensions_Sheet.csv example_Sheet2.csv sampleChart_Sheet.csv
duesRecords_Sheet1.csv example_Sheet3.csv styled_Sheet.csv
example_Sheet1.csv merged_Sheet.csv styles_Sheet.csv
$