python 将txt文件转换为excel_Python学习笔记-Txt文件转Excel文件

Txt文件转Excel 2003文件(Excel 2003 一个工作表行数限制65536，列数限制256)

# -*- coding:utf-8 -*-

import os

import sys

import xlwt

import datetime

default_encoding = 'utf-8'

if sys.getdefaultencoding() != default_encoding:

print sys.getdefaultencoding()

reload(sys)

sys.setdefaultencoding(default_encoding)

if __name__=='__main__':

startTime = datetime.datetime.now()

if len(sys.argv)!=2:

sys.exit(1)

path=os.path.join(os.getcwd(), sys.argv[1])

if not os.path.exists(path):

print "ERROR: %s can not find" %path

sys.exit(1)

xlsxPath = os.path.join(os.path.dirname(path),

os.path.splitext(os.path.basename(path))[0] + '.xls')

workbook = xlwt.Workbook(encoding='utf-8')

BUFSIZE = 1024

EXCEL_ROWS = 65535

EXCEL_COLS = 256

FIELD_SEPARATOR = ','

with open(path, 'r') as f:

nrows, total_rows = 0, 0

lines = f.readlines(BUFSIZE)

while lines:

for line in lines:

if (nrows % EXCEL_ROWS == 0) :

wsheet = workbook.add_sheet('sheet' + str(total_rows), cell_overwrite_ok = True)

nrows = 0

values = line.split(FIELD_SEPARATOR)

cols_num = EXCEL_COLS if len(values) > EXCEL_COLS else len(values)

for ncol in xrange(cols_num):

wsheet.write(nrows, ncol, values[ncol])

nrows = nrows + 1

total_rows = total_rows + 1

lines = f.readlines(BUFSIZE)

workbook.save(xlsxPath)

endTime = datetime.datetime.now()

print "spend time %s seconds" %((endTime - startTime).seconds)

Txt文件转Excel 2007文件(Excel 2007一个工作表行数限制1048576，列数限制16384)

# -*- coding:utf-8 -*-

import os

import sys

import datetime

import xlsxwriter

default_encoding = 'utf-8'

if sys.getdefaultencoding() != default_encoding:

print sys.getdefaultencoding()

reload(sys)

sys.setdefaultencoding(default_encoding)

if __name__ == '__main__':

startTime = datetime.datetime.now()

if len(sys.argv)!=2:

sys.exit(1)

path=os.path.join(os.getcwd(), sys.argv[1])

if not os.path.exists(path):

print "ERROR: %s can not find" % path

sys.exit(1)

xlsxPath = os.path.join(os.path.dirname(path),

os.path.splitext(os.path.basename(path))[0] + '.xlsx')

workbook = xlsxwriter.Workbook(xlsxPath)

BUFSIZE = 1024

EXCEL_ROWS = 1040000

EXCEL_COLS = 16384

FIELD_SEPARATOR = ','

with open(path, 'r') as f:

nrows, total_rows, sheet_num = 0, 0, 0

lines = f.readlines(BUFSIZE)

while lines:

for line in lines:

if (total_rows % EXCEL_ROWS == 0) :

worksheet = workbook.add_worksheet(name = 'sheet' + str(sheet_num))

nrows = 0

sheet_num = sheet_num + 1

values = line.split(FIELD_SEPARATOR)

cols_num = EXCEL_COLS if len(values) > EXCEL_COLS else len(values)

for ncol in xrange(cols_num):

worksheet.write(nrows, ncol, values[ncol])

nrows = nrows + 1

total_rows = total_rows + 1

lines = f.readlines(BUFSIZE)

workbook.close()

endTime = datetime.datetime.now()

print "spend time %s seconds" % ((endTime - startTime).seconds)