python csv转excel只剩一行是怎么回事_使用python将csv转换为xls

怼烎@ 2022-10-22 11:48 130阅读 0赞

使用python将csv转换为xls

我试图循环通过使用这个Python脚本的目录中的CSV文件将其转换为XLS，但到目前为止我无法实现。 会appreaciate任何帮助。 这个代码的操作说，他一次只写一个spreadhset(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|-ln: Split output into files of n lines or less each" print "--sep|-sc \[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()

我不推荐使用pandas，而是推荐使用pandas。 这是一个很好的Python库来处理数据。 首先查看pandas.read\_csv (返回一个pandas.DataFrame )，然后是pandas.DataFrame.to\_excel 。

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

有很多方法可以将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()

希望它的工作..