使用python将csv转换为xls

Question

我试图通过使用此Python脚本将目录中的CSV文件循环转换为XLS，但到目前为止我无法实现太多。会appreaciate任何帮助。这段代码的操作说，他一次只编写一个扩展集（1个文件），但我相信它可以被一些python程序员的经验所改变。

#!/usr/local/bin/python 
# Tool to convert CSV files (with configurable delimiter and text wrap 
# character) to Excel spreadsheets. 
import string 
import sys 
import getopt 
import re 
import os 
import os.path 
import csv 
from pyExcelerator import * 

def usage(): 
    """ Display the usage """ 
    print "Usage:" + sys.argv[0] + " [OPTIONS] csvfile" 
    print "OPTIONS:" 
    print "--title|-t: If set, the first line is the title line" 
    print "--lines|-l n: Split output into files of n lines or less each" 
    print "--sep|-s c [def:,] : The character to use for field delimiter" 
    print "--output|o : output file name/pattern" 
    print "--help|h : print this information" 
    sys.exit(2) 

def openExcelSheet(outputFileName): 
    """ Opens a reference to an Excel WorkBook and Worksheet objects """ 
    workbook = Workbook() 
    worksheet = workbook.add_sheet("Sheet 1") 
    return workbook, worksheet 

def writeExcelHeader(worksheet, titleCols): 
    """ Write the header line into the worksheet """ 
    cno = 0 
    for titleCol in titleCols: 
    worksheet.write(0, cno, titleCol) 
    cno = cno + 1 

def writeExcelRow(worksheet, lno, columns): 
    """ Write a non-header row into the worksheet """ 
    cno = 0 
    for column in columns: 
    worksheet.write(lno, cno, column) 
    cno = cno + 1 

def closeExcelSheet(workbook, outputFileName): 
    """ Saves the in-memory WorkBook object into the specified file """ 
    workbook.save(outputFileName) 

def getDefaultOutputFileName(inputFileName): 
    """ Returns the name of the default output file based on the value 
     of the input file. The default output file is always created in 
     the current working directory. This can be overriden using the 
     -o or --output option to explicitly specify an output file """ 
    baseName = os.path.basename(inputFileName) 
    rootName = os.path.splitext(baseName)[0] 
    return string.join([rootName, "xls"], '.') 

def renameOutputFile(outputFileName, fno): 
    """ Renames the output file name by appending the current file number 
     to it """ 
    dirName, baseName = os.path.split(outputFileName) 
    rootName, extName = os.path.splitext(baseName) 
    backupFileBaseName = string.join([string.join([rootName, str(fno)], '-'), extName], '') 
    backupFileName = os.path.join(dirName, backupFileBaseName) 
    try: 
    os.rename(outputFileName, backupFileName) 
    except OSError: 
    print "Error renaming output file:", outputFileName, "to", backupFileName, "...aborting" 
    sys.exit(-1) 

def validateOpts(opts): 
    """ Returns option values specified, or the default if none """ 
    titlePresent = False 
    linesPerFile = -1 
    outputFileName = "" 
    sepChar = "," 
    for option, argval in opts: 
    if (option in ("-t", "--title")): 
     titlePresent = True 
    if (option in ("-l", "--lines")): 
     linesPerFile = int(argval) 
    if (option in ("-s", "--sep")): 
     sepChar = argval 
    if (option in ("-o", "--output")): 
     outputFileName = argval 
    if (option in ("-h", "--help")): 
     usage() 
    return titlePresent, linesPerFile, sepChar, outputFileName 

def main(): 
    """ This is how we are called """ 
    try: 
    opts,args = getopt.getopt(sys.argv[1:], "tl:s:o:h", ["title", "lines=", "sep=", "output=", "help"]) 
    except getopt.GetoptError: 
    usage() 
    if (len(args) != 1): 
    usage() 
    inputFileName = args[0] 
    try: 
    inputFile = open(inputFileName, 'r') 
    except IOError: 
    print "File not found:", inputFileName, "...aborting" 
    sys.exit(-1) 
    titlePresent, linesPerFile, sepChar, outputFileName = validateOpts(opts) 
    if (outputFileName == ""): 
    outputFileName = getDefaultOutputFileName(inputFileName) 
    workbook, worksheet = openExcelSheet(outputFileName) 
    fno = 0 
    lno = 0 
    titleCols = [] 
    reader = csv.reader(inputFile, delimiter=sepChar) 
    for line in reader: 
    if (lno == 0 and titlePresent): 
     if (len(titleCols) == 0): 
     titleCols = line 
     writeExcelHeader(worksheet, titleCols) 
    else: 
     writeExcelRow(worksheet, lno, line) 
    lno = lno + 1 
    if (linesPerFile != -1 and lno >= linesPerFile): 
     closeExcelSheet(workbook, outputFileName) 
     renameOutputFile(outputFileName, fno) 
     fno = fno + 1 
     lno = 0 
     workbook, worksheet = openExcelSheet(outputFileName) 
    inputFile.close() 
    closeExcelSheet(workbook, outputFileName) 
    if (fno > 0): 
    renameOutputFile(outputFileName, fno) 

if __name__ == "__main__": 
    main() 

Answer 1

有许多方法来转换csv到xls以及在python xlsx（和我也同意@ ti7的怀疑的话），但我想包括令人印象深刻的途径之一（的确非常方便）通过使用xlsxwriter模块来完成。你也可以寻找openpyxl

import os 
import glob 
import csv 
from xlsxwriter.workbook import Workbook 


for csvfile in glob.glob(os.path.join('.', '*.csv')): 
workbook = Workbook(csvfile[:-4] + '.xlsx') 
worksheet = workbook.add_worksheet() 
with open(csvfile, 'rt', encoding='utf8') as f: 
    reader = csv.reader(f) 
    for r, row in enumerate(reader): 
     for c, col in enumerate(row): 
      worksheet.write(r, c, col) 
workbook.close() 

希望工程..

Answer 2

而是重新发明轮子的，我推荐使用熊猫。这是一个很棒的Python库，用于处理数据。首先查看pandas.read_csv（返回pandas.DataFrame），然后pandas.DataFrame.to_excel。

当你在它的时候，也从标准库中查找​​。