python-files:csv2xlsx
| 后一修订版 | 前一修订版 | ||
|
python-files:csv2xlsx [2018/04/04 03:55] admin 创建 |
python-files:csv2xlsx [2019/10/06 03:40] (当前版本) admin |
||
|---|---|---|---|
| 行 1: | 行 1: | ||
| ====== 使用Python合并多个CSV文件为Excel xlsx文件 ====== | ====== 使用Python合并多个CSV文件为Excel xlsx文件 ====== | ||
| - | <code> | + | 工作中经常会生成csv文件，不同的csv文件生成excel的xlsx文件会比较方便，和同事分享也容易。 |
| - | #! /tools/cfr/bin/python | + | 就用python写了个小脚本，来合并csv文件为xlsx。 |
| + | |||
| + | 更新： @2019-10-6 | ||
| + | 增加判断数据是否为数值，使用数值会使合并后的excel操作更加灵活，方便。 | ||
| + | |||
| + | |||
| + | <code python> | ||
| + | |||
| + | #! /tools/python/2.7.6/bin/python | ||
| + | ############################################################# | ||
| ############################################################# | ############################################################# | ||
| ##### Extract Report Constraints Vilations ############## | ##### Extract Report Constraints Vilations ############## | ||
| - | ##### by donghua.gu@broadcom.com @ Mar. 2018 ############## | + | ############################################################# |
| ############################################################# | ############################################################# | ||
| + | import re | ||
| import os | import os | ||
| import sys | import sys | ||
| import csv | import csv | ||
| import glob | import glob | ||
| + | import gzip | ||
| import time | import time | ||
| - | sys.path.append("/home/shdi/bin/pymodule") | + | sys.path.append("/data/nishome/donghua.gu/scripts_gdh/pym") |
| + | |||
| + | import xlwt | ||
| import xlsxwriter | import xlsxwriter | ||
| 行 22: | 行 35: | ||
| workbook = xlsxwriter.Workbook(xlsxfile) | workbook = xlsxwriter.Workbook(xlsxfile) | ||
| fmt_plain = workbook.add_format({ | fmt_plain = workbook.add_format({ | ||
| - | 'font_size': 12, | + | 'font_size': 11, |
| 'font_name': "Arial Narrow", | 'font_name': "Arial Narrow", | ||
| }) | }) | ||
| - | for filename in glob.glob("%s/*.csv" % csv_dir): | + | ws_names = [] |
| + | for filename in glob.glob("./%s/*.csv" % csv_dir): | ||
| print " procsss %s" % filename | print " procsss %s" % filename | ||
| (f_path, f_name) = os.path.split(filename) | (f_path, f_name) = os.path.split(filename) | ||
| (f_short_name, f_extension) = os.path.splitext(f_name) | (f_short_name, f_extension) = os.path.splitext(f_name) | ||
| - | sheet_name = f_short_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) | worksheet = workbook.add_worksheet(sheet_name) | ||
| + | |||
| spamReader = csv.reader(open(filename, 'rb'), delimiter=',',quotechar='"') | spamReader = csv.reader(open(filename, 'rb'), delimiter=',',quotechar='"') | ||
| row_count = 0 | row_count = 0 | ||
| for row in spamReader: | for row in spamReader: | ||
| - | for col in range(len(row)): | + | for col in range(len(row)): |
| - | #ws.write(row_count,col,row[col]) | + | value = row[col] |
| - | worksheet.write(row_count, col, row[col],fmt_plain) | + | try: |
| - | row_count +=1 | + | 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() | workbook.close() | ||
| print "xlsx file saved: %s" % xlsxfile | print "xlsx file saved: %s" % xlsxfile | ||
| return | return | ||
| + | |||
| if __name__ == "__main__": | if __name__ == "__main__": | ||
| if len(sys.argv) != 2: | if len(sys.argv) != 2: | ||
| - | print "Usage:" | + | print "\nUsage:" |
| - | print "\t%s <csvdir>" % sys.argv[0] | + | print "\t%s <csv dir>\n" % sys.argv[0] |
| sys.exit(0) | sys.exit(0) | ||
| - | csvdir = sys.argv[1] | + | tag = time.strftime("%Y%m%d-%H%M%S") |
| - | savefile = time.strftime("merge_%Y%m%d.xlsx") | + | rpt_dir = sys.argv[1] |
| - | merge_csv2xlsx(csvdir, savefile) | + | #xlsxfile = "%s/merge_%s.xlsx" % (rpt_dir, tag) |
| - | print("\n\nCVS merged file saved to %s" % savefile) | + | xlsxfile = "merge_%s.xlsx" % (tag) |
| + | merge_csv2xlsx(rpt_dir, xlsxfile) | ||
| + | |||
| </code> | </code> | ||
python-files/csv2xlsx.1522814159.txt.gz · 最后更改: 2018/04/04 03:55 由 admin
除额外注明的地方外，本维基上的内容按下列许可协议发布： CC Attribution-Share Alike 4.0 International
