#! /tools/cfr/bin/python
 
###############################################################
#####  Merge CSV files to Excel XLSX with Python ##############
##### by gudonghua(at)gmail.com @ Mar. 2018      ##############
###############################################################
 
import os
import sys
import csv
import glob
import time
 
sys.path.append("/home/shdi/bin/pymodule")
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':  12,
      'font_name':  "Arial Narrow",
  })
 
  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
    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)):
            #ws.write(row_count,col,row[col])
            worksheet.write(row_count, col, row[col],fmt_plain)
        row_count +=1
 
  workbook.close()
  print "xlsx file saved: %s" % xlsxfile
  return
 
if __name__ == "__main__":
 
  if len(sys.argv) != 2:
    print "Usage:"
    print "\t%s <csvdir>" % sys.argv[0]
    sys.exit(0)
 
  csvdir = sys.argv[1]
  savefile = time.strftime("merge_%Y%m%d.xlsx")
  merge_csv2xlsx(csvdir, savefile)
  print("\n\nCVS merged file saved to %s" % savefile)