Chapter 14 – Working with CSV Files and JSON Data

非计算机出身，最近一直在看《Automate the Boring Stuff with Python》，为了能够很好的理解和学习，一直坚持在做文后的练习题。但一个人学习实在是太累太难，准备将自己的做的练习题记录下来，希望得到高手的指导，来拓展个人解决问题的思路、提升代码质量。

传送门：
Chapter 12 – Working with Excel Spreadsheets
Chapter 13 – Working with PDF and Word Documents
Chapter 15 – Keeping Time, Scheduling Tasks, and Launching Programs
Chapter 17 – Manipulating Images
Chapter 18 – Controlling the Keyboard and Mouse with GUI Automation

Excel-to-CSV Converter

Excel can save a spreadsheet to a CSV file with a few mouse clicks, but if you had to convert hundreds of Excel files to CSVs, it would take hours of clicking. Using the openpyxl module from Chapter 12, write a program that reads all the Excel files in the current working directory and outputs them as CSV files.

A single Excel file might contain multiple sheets; you’ll have to create one CSV file per sheet. The filenames of the CSV files should be <excel filename>_<sheet title>.csv, where <excel filename> is the filename of the Excel file without the file extension (for example, 'spam_data', not 'spam_data.xlsx') and <sheet title> is the string from the Worksheet object’s title variable.

代码如下：

#! python3
# excelConvertToCsv.py

import openpyxl,csv,os

os.makedirs('excelToCsv',exist_ok=True)

for excelFilename in os.listdir('.'):
    if not excelFilename.endswith('.xlsx'):
        continue  # Skip non-xlsx files, load the workbook object.
    print('Loading the excel file from ' + str(excelFilename) + '...')
    wb = openpyxl.load_workbook(excelFilename)
    
    # Loop through every sheet in the workbook.
    for sheetName in wb.sheetnames:
        sheet = wb[sheetName]     
        # Create the CSV filename from the Excel filename and sheet title.
        csvFilename = str(excelFilename)[:-6] + '_' + str(sheet.title) + '.csv'
        # Create the csv.Writer object for this CSV file.
        csvFileObj = open(os.path.join('excelToCsv',csvFilename),'w',newline='')
        csvWriter = csv.writer(csvFileObj)

        # Loop through every row in the sheet.
        '''
        for rowNum in range(1,sheet.max_row+1):
            rowData = [] 
            # Loop through each cell in the row.
            for colNum in range(1, sheet.max_column+1):
                sheetTxt = sheet.cell(row= rowNum,column=colNum).value
                 
                rowData.append(sheetTxt)                    
                csvWriter.writerow(rowData)
                '''
        for row in sheet.iter_rows():
            rowData = []
            for cell in row:
                rowData.append(cell.value)
                csvWriter.writerow(rowData)
        csvFileObj.close()

print('Done')