python操作Excel(xlrd和xlwt)
import xlrd
import xlwt
Path_RD = "E:\\Python_Projects\\2-PythonExcel\\读Excel文件.xls"
Path_WT = "2-PythonExcel\\写Excel文件.xls"
Data_RD = xlrd.open_workbook(Path_RD)
Table1_RD = Data_RD.sheets()[0]
Table2_RD = Data_RD.sheet_by_index(1)
Table3_RD = Data_RD.sheet_by_name("Sheet3")
Table_Name = Data_RD.sheet_names()[0]
print("Table_Name = ", Table_Name)
Sheets_Names_List = Data_RD.sheet_names()
print("Sheets_Names_List = ", Sheets_Names_List)
Result1 = Data_RD.sheet_loaded(1)
Result2 = Data_RD.sheet_loaded("Sheet2")
print("Result1 = ", Result1, "Result2 = ", Result2)
nrows = Table1_RD.nrows
print("nrows = ", nrows)
Rows_List1 = Table1_RD.row(0)
print("Rows_List1 = ", Rows_List1)
print("Rows_List1[0].value", Rows_List1[0].value)
for list_Text in Rows_List1:
print("list_Text = ", list_Text)
Rows_List2 = Table1_RD.row_slice(1)
print("Rows_List2 = ", Rows_List2)
Rows_List3 = Table1_RD.row_types(3, start_colx=0, end_colx=None)
print("Rows_List3 = ", Rows_List3)
Rows_Len1 = Table1_RD.row_len(1)
print("Rows_Len4 = ", Rows_Len1)
Table1_ncols = Table1_RD.ncols
print("Table1_ncols = ", Table1_ncols)
Col_List1 = Table1_RD.col(0, start_rowx=0, end_rowx=None)
print("Col_List1 = ", Col_List1)
Col_List2 = Table1_RD.col_slice(1, start_rowx=0, end_rowx=None)
print("Col_List2 = ", Col_List2)
Col_List3 = Table1_RD.col_types(2, start_rowx=0, end_rowx=None)
print("Col_List3 =", Col_List3)
Col_List4 = Table1_RD.col_values(3, start_rowx=0, end_rowx=None)
print("Col_List4 = ", Col_List4)
Cell1 = Table1_RD.cell(2, 2)
print("Cell1 = ", Cell1)
Cell1_Type = Table1_RD.cell_type(2, 2)
print("Cell_Type = ", Cell1_Type)
Cell1_Value = Table1_RD.cell_value(2, 2)
print("Cell_Value = ", Cell1_Value)
xlsx1 = xlrd.open_workbook("2-PythonExcel\\读Excel文件.xls")
table1 = xlsx1.sheet_by_index(0)
value1 = table1.cell_value(2, 1)
print("第3行2列值为", value1)
nrows1 = table1.nrows
print("表格一共有", nrows1, "行")
name_list1 = table1.col_values(2, start_rowx=1, end_rowx=None)
print("第4列所有的值:", name_list1)
new_workbook = xlwt.Workbook(encoding="ascii")
new_sheet = new_workbook.add_sheet("new_sheet")
style1 = xlwt.XFStyle()
font = xlwt.Font()
font.name = "New Roman"
font.bold = True
font.underline = True
font.italic = True
style1.font = font
new_sheet.col(0).width = 256 * 20
style2 = xlwt.easyxf("font:height 360;")
row = new_sheet.row(0)
row.set_style(style2)
new_sheet.write_merge(2, 3, 0, 3, "备注:Python操作Excel")
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left_colour = 0x40
borders.right_colour = 0x40
borders.top_colour = 0x40
borders.bottom_colour = 0x40
style3 = xlwt.XFStyle()
style3.borders = borders
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 2
style4 = xlwt.XFStyle()
style4.pattern = pattern
style5 = xlwt.XFStyle()
alignment = xlwt.Alignment()
alignment.horz = 0x02
alignment.vert = 0x01
style5.alignment = alignment
new_sheet.write(0, 0, "编号", style1)
new_sheet.write(1, 0, "001", style2)
new_sheet.write(4, 0, "Test1", style3)
new_sheet.write(4, 1, "Test2", style4)
new_sheet.write(4, 2, "Test3", style5)
new_workbook.save("new_workbook_1.xls")