如何将Excel工作表另存为CSV [英] How to save an Excel worksheet as CSV

问题描述

我想编写一个Python脚本，该脚本在Excel电子表格中读取并将其某些工作表另存为CSV文件.

I want to write a Python script that reads in an Excel spreadsheet and saves some of its worksheets as CSV files.

我该怎么做?

我发现第三方模块可用于从Python读取和写入Excel文件，但到目前为止据我所知，它们只能以Excel(即* .xls)格式保存文件.如果我在这里错了，那么一些示例代码将展示如何使用这些模块执行我想做的事情.

I have found third-party modules for reading and writing Excel files from Python, but as far as I can tell, they can only save files in Excel (i.e. *.xls) format. If I'm wrong here, some example code showing how to do what I'm trying to do with these modules would be appreciated.

我还遇到了一个我不太了解的解决方案，但似乎是Windows特有的，因此无论如何也无济于事，因为我想在Unix中进行此操作.无论如何，对我来说还不清楚是否可以扩展此解决方案以执行我想做的事情，即使在Windows下也是如此.

I also came across one solution that I can't quite understand, but seems to be Windows-specific, and therefore would not help me anyway, since I want to do this in Unix. At any rate, it's not clear to me that this solution can be extended to do what I want to do, even under Windows.

推荐答案

使用这两个库的最基本示例逐行描述:

The most basic examples using the two libraries described line by line:

1. 打开xls工作簿
2. 参考第一个电子表格
3. 打开二进制文件以写入目标csv文件
4. 创建默认的csv编写器对象
5. 遍历第一个电子表格的所有行
6. 将行转储到csv

---

import xlrd
import csv

with xlrd.open_workbook('a_file.xls') as wb:
    sh = wb.sheet_by_index(0)  # or wb.sheet_by_name('name_of_the_sheet_here')
    with open('a_file.csv', 'wb') as f:   # open('a_file.csv', 'w', newline="") for python 3
        c = csv.writer(f)
        for r in range(sh.nrows):
            c.writerow(sh.row_values(r))

---

import openpyxl
import csv

wb = openpyxl.load_workbook('test.xlsx')
sh = wb.get_active_sheet()
with open('test.csv', 'wb') as f:  # open('test.csv', 'w', newline="") for python 3
    c = csv.writer(f)
    for r in sh.rows:
        c.writerow([cell.value for cell in r])

这篇关于如何将Excel工作表另存为CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！