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)