使用Python合并多个CSV文件为Excel xlsx文件

工作中经常会生成csv文件，不同的csv文件生成excel的xlsx文件会比较方便，和同事分享也容易。 就用python写了个小脚本，来合并csv文件为xlsx。

更新： @2019-10-6 增加判断数据是否为数值，使用数值会使合并后的excel操作更加灵活，方便。

#! /tools/python/2.7.6/bin/python
 
#############################################################
#############################################################
##### Extract Report Constraints Vilations     ##############
#############################################################
#############################################################
 
import re
import os
import sys
import csv
import glob
import gzip
import time
 
sys.path.append("/data/nishome/donghua.gu/scripts_gdh/pym")
 
import xlwt
import xlsxwriter
 
def merge_csv2xlsx(csv_dir, xlsxfile):
  # Create a new workbook and add a worksheet
  workbook = xlsxwriter.Workbook(xlsxfile)
  fmt_plain = workbook.add_format({
      'font_size':  11,
      'font_name':  "Arial Narrow",
  })
 
  ws_names = []
  for filename in glob.glob("./%s/*.csv" % csv_dir):
    print "  procsss %s" % filename
    (f_path, f_name) = os.path.split(filename)
    (f_short_name, f_extension) = os.path.splitext(f_name)
    sheet_name = f_short_name[:28]
    if sheet_name in ws_names:
      sheet_name = "%s-%d" % (len(ws_names))
    worksheet = workbook.add_worksheet(sheet_name)
 
    spamReader = csv.reader(open(filename, 'rb'), delimiter=',',quotechar='"')
    row_count = 0
    for row in spamReader:
      for col in range(len(row)):
        value = row[col]
        try:
          value = int(value)
        except:
          try:
            value = float(value)
          except:
            #print "    Value not number: %s" % row[col]
            pass
        worksheet.write(row_count, col, value, fmt_plain)
      row_count +=1
 
  workbook.close()
  print "xlsx file saved: %s" % xlsxfile
  return
 
 
if __name__ == "__main__":
 
  if len(sys.argv) != 2:
    print "\nUsage:"
    print "\t%s <csv dir>\n" % sys.argv[0]
    sys.exit(0)
 
  tag = time.strftime("%Y%m%d-%H%M%S")
  rpt_dir = sys.argv[1]
  #xlsxfile = "%s/merge_%s.xlsx" % (rpt_dir, tag)
  xlsxfile = "merge_%s.xlsx" % (tag)
  merge_csv2xlsx(rpt_dir, xlsxfile)