Python calls Xlrd&xlsxwirter

2. #coding =utf-8
4. #/usr/bin/env python
6. Import xlsxwriter,xlrd
8. Import sys,os.path
12. fname = ' zm6.xlsx '
14. If not os.path.isfile (fname):
16. print u ' file path does not exist '
18. sys.exit ()
20. data = Xlrd.open_workbook (fname) # open fname file
22. Data.sheet_names () # Get the name of all sheet in the XLS file
24. Table = Data.sheet_by_index (0) # get an xls file by index No. 0 sheet
26. nrows = table.nrows # Gets the row number of the table sheet
28. Ncols = Table.ncols # Gets the total number of columns in the table worksheet
32. Workbook = Xlsxwriter. Workbook (' zm6.xlsx ') #创建一个excel文件
34. Worksheet = Workbook.add_worksheet () #创建一个工作表对象
36. Worksheet.set_column (0,ncols,22) #设定列的宽度为22像素
40. #border: border, align: alignment, bg_color: background color, font_size: font size, bold: font Bold
42. top = Workbook.add_format ({' border ': 1, ' align ': ' center ', ' bg_color ': ' cccccc ', ' font_size ': ' bold ':True})
44. Green = Workbook.add_format ({' border ': 1, ' align ': ' center ', ' bg_color ': ' green ', ' font_size ': 12})
46. Yellow = Workbook.add_format ({' border ': 1, ' Bg_color ': ' yellow ', ' font_size ': 12})
48. Red = Workbook.add_format ({' border ': 1, ' align ': ' center ', ' bg_color ': ' red ', ' font_size ': 12})
50. blank = Workbook.add_format ({' Border ': 1})
54. For i in xrange (nrows):
56. Worksheet.set_row (i,22) #设定第i行单元格属性, height 22 pixels, row index starting from 0
58. for J in xrange (ncols):
60. Cell_value = Table.cell_value (i,j,) #获取第i行中第j列的值
62. if i = = 0:
64. Format = Top
66. elif i = = 3 or i = = 6:
68. format = Blank
70. else:
72. if J = = 0 or J = = 2:
74. Format = Yellow
76. elif J = = 1:
78. format = Red
80. elif J = = 3:
82. Format = Green
84. Green.set_num_format (' yyyy-mm-dd ') #设置时间格式
86. Worksheet. Write (i,j,cell_value,format) #把获取到的值写入文件对应的行列
88. Format.set_align (' vcenter ') #设置单元格垂直对齐
90. Workbook. Close ()

Python calls Xlrd&xlsxwirter