Python excel and python
1. openpyxl read
This module is used for 95% of the time. Currently, the excel processing module is only maintained.
1. workBook
workBook=openpyxl.load_workbook('path(.xlsx)').encode('gbk')print workBook
2. sheet
SheetList = workBook. get_sheet_names () Get the names of all sheet and save it as the print sheetList in the List format.
3. cell
(1) sheet1 = workBook. get_sheet_by_name ('test1') Get a specific sheet
(2) rowsData = sheet1.rows obtains all rows and returns an iterator.
Rows and columns start from 1, not from 0.
For I in rowsData: # print I # print type (I) tuples for j in I: print j. coordinate (the table below for each cell), j. value (cell value), print the content of all cells
(3) colsData = sheet1.columns get all columns and return the iterator
(4) update a cell
J. value = U' re-assign 'workbook. save (path (previously operated path ))
4. cell positioning
Two methods:
(1) c1 = sheet1.cell (coordinate = 'b2 ')
print c1.value
(2) c2 = sheet1 ['b2'] dictionary format: Use the key value to find the value
print c2.value
(3) c1 = sheet1.cell (row = 2, column = 3) the third column in the second row
print c1.value
(4) slice to get cells in a region
Area = sheet1 ['c2': 'd7'] to obtain the nested tuples for I in area: # print ifor j in I: pass # print jprint j. coordinate, j. value = j. coordinateprint
2. Write Cells
1. Write a workBook
workBook=openpyxl.Workbook()
2. sheet
Sheet1 = workBook. create_sheet (title = 'sheet1', index = 0) index = 0 locate the sheet
3. cell
Sheet1 ['b6 ']. value = u 'testb6'
WorkBook. save (path)
4. Write Data in the entire row below the empty line
sheet1.append([1,2,3,4,5,6,7])
3. excel-related style operations
import openpyxlfrom openpyxl.styles import PatternFill,Alignment,Font,colorsworkBook=openpyxl.Workbook()sheet1=workBook.create_sheet('test')
1. Merge cells in two ways
sheet1.merge_cells(range_string='A2:G2')sheet1.merge_cells(start_row=2,start_column='A',end_row=2,end_column='G')
2. Set the typographical style: Alignment Mode
al=Alignment(horizontal='center') horizontal:left,center,rightsheet1['A2'].alignment=al
3. Set the background color.
fill=PatternFill(patternType='solid',fgColor=colors.BLUE)sheet1['A2'].fill=fill
4. Font color
sheet1[A2].valuefont=Font(colors=colors.WHITE,size=14)sheet1['A2'].font=font
Summary
The above section describes how to use python to Operate excel. I hope it will help you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!