import openpyxl as l
import time
# If there is a corresponding operation, just save it and save it to the hard disk. . . wb.save(file_result_path)
file_open_path = r'D:\T\Documents\MobileFile\Desktop\python learning\table\test_openpyxl.xlsx'
file_result_path = r'D:\T\Documents\MobileFile\Desktop\python learning\table\result.xlsx'
wb = l.load_workbook(file_open_path) # Specify the path and open the workbook
# ws = wb['Sheet1'] # Open the worksheet
ws = wb['sheet_yzy1'] #Open the worksheet
#------------------------------------------------ ----------------
# wb = l.Workbook() # Create a new empty workbook
# ws = wb['Sheet'] # 1_Get the worksheet according to the sheet name
# ws = wb.active # 1_Activated worksheet Get the worksheet
# wb.worksheets # Get all 'sheet objects'. . . You can then subscript to obtain a single sheet object
# wb.sheetnames #1_Get all 'sheet names' You can take the subscript to see a certain name.
# ws.title #1_Get a certain 'sheet name'. . Generally, title is used to modify the sheet name.
# wb.remove(wb['preSheet2']) # Delete the worksheet. . . The parameter is the sheet object
# new_ws = wb.create_sheet('yzy1',0) # Create a new worksheet: 1 (not required) Give the sheet a name and it will be automatically renamed to Sheet + number. . . 2 (not required) You can also specify the index, which is added at the end by default.
# new_ws = wb.copy_worksheet(wb['Sheet1']) # Create a copy of the table and return a new_ws with a different id. . The new sheet will be automatically renamed: 'Copy'
# cell = ws.cell(1, 2) # Get the cell object
# cell_data = ws.cell(1, 2).value # Not index to get the value of a certain cell, it can also be modified
# for loop can also set the step size
# cell.row, cell.column # Get the row and column where the cell is located, not the index
'''
2023.10.03
'''
# Get the cells within the range (especially important)
# cell_area = ws.iter_rows(min_row=1, max_row=3, min_col=1, max_col=3) # Minimum row, maximum row, minimum column, maximum column. If written in order, the parameters can be omitted (not index)
# for row in cell_area: # Get the data of each row
# # print(row)
# for cell in row: # The data of each cell in a row
# print(cell.value)
# -------
# print(ws.max_row) #Maximum number of rows
# print(ws.max_column) #Maximum number of columns
# ws.cell(r, 5).fill = l.styles.PatternFill(patternType='solid', fgColor='FFFF00') # Set the fill color for the cell
# ws.cell(r, 5).font = l.styles.Font(color='c00000') # Set the font color for the cell
# Example: Splicing of column C text and column D percentage
# for row in range(1, ws.max_row + 1):
# cell_data_C = ws.cell(row, 3).value # 2_specified cell_pure number, not index
# cell_data_D = ws.cell(row, 4).value # 2_specified cell_pure number, not index
#
# tmp = str(int(cell_data_D * 100)) + '%' # Because column D is a percentage, the obtained value is a decimal, which needs to be processed.
#
# data = cell_data_C + '(' + tmp + ')'
# ws.cell(row, 5, data) # 2_Specified cell_pure number, not index, data can be written directly
#-------------
'''
2023.10.04
'''
# Example: Read txt (data separated by spaces), and then write the data into excel.
txt_file = open(r'D:\T\Documents\MobileFile\Desktop\python learning\table\test_txt_excel.txt', mode='r',
encoding='utf-8') # r is read
lines = txt_file.readlines() # Read all lines
# Remove the '\\
' at the end, and the result will still be returned to lines (it is better to remove \\
, otherwise it will wrap when 'automatic line wrap' is turned on)
# for line in range(0, len(lines)):
# lines[line] = lines[line].split('\\
')[0]
#
# col_first = ws.max_column + 2 # One column apart from the original data
# for row in range(len(lines)):
# row_data = lines[row] # Get the data of each row
# cell_data = row_data.split(' ') # Get the space-split data list
# # print(cell_data)
# for col in range(len(cell_data)):
# ws.cell(row + 1, col_first + col, cell_data[col]) # Write data
# ---------
#Print out all sheet names:
# print(wb.sheetnames)
# for i in wb.sheetnames: #The loop can be a list
# print(i) #1_method
# print(wb.worksheets)
# for i in wb.worksheets:
# print(i.title) # 2_method
# print(str(i)[12:-2]) # 3_Method
# ----------
# Add pre before the names of all sheets
# for i in range(len(wb.worksheets)): # 1_method
# wb.worksheets[i].title = 'pre' + wb.worksheets[i].title
# for i in wb.worksheets: # 2_method
# i.title = 'pre' + i.title
# -------
# Delete the sheets whose beginning is not pre:
# for sheet_name in wb.sheetnames:
# # print(sheet_name)
# # if sheet_name.find('pre', 0, 4) != -1: # 1_if
# if sheet_name[:3] != 'pre': # 2_if
# wb.remove(wb[sheet_name]) # It is not possible to use worksheets, because after deleting the worksheet, it will change
# ---------------
'''
2023.10.05
'''
# Get the data of odd rows:
# row_area = ws.iter_rows(1, ws.max_row, 1, ws.max_column) # Get the cells of the area
# for row in row_area: # The entire odd-numbered row cell object of one row
# for cell in row:
# # print(cell.row)
# if cell.row % 2 == 1:
# print(cell.row, cell.column, cell.value)
# -------
# Get the data in column C, and then add the data to the last row.
# list_c = [] # Store data in a list, making it easier to operate
# row_area = ws.iter_rows(1, ws.max_row, 3, 3) # Get column C
# # print('row_area: ', row_area)
#
# for row in row_area: # Cell object of a row
# # print('row_data: ', row_data)
# for cell in row:
# # print('cell.value: ', cell.value)
# list_c.append(cell.value)
# # print(list_c)
# ws.append(list_c) # In the last row of the sheet, add data
# ------
'''
2023.10.06
'''
#'space' within the area, reassign. . . For cells <= 3, reassign values.
# for row in range(1, ws.max_row + 1):
# # print(row)
# for col in range(1, ws.max_column + 1):
# cell_data = ws.cell(row, col).value
# # print(cell_data)
#
# # if str(cell_data) == 'None': # 1_'None' is of NoneType type and needs to be converted to str
# # ws.cell(row, col, 'yzy')
#
# if cell_data == None: # 2_or simply None
# ws.cell(row, col, 'yzy')
#
# if cell_data != None and cell_data <= 3: # Need to deal with the None and str types first, and an error will be reported if directly involved in the calculation.
# ws.cell(row, col, str(ws.cell(row, col).value) + '(<= 3)')
# ----------
# Each row is accumulated. If it is greater than 5, write >5 in the last column.
# sum = 0
# row_area = ws.iter_rows(2, ws.max_row, 2, ws.max_column) # Get area cells
#
# for row in row_area:
# # print(row)
# for cell in row:
# # print(cell)
# sum + = cell.value
# if sum > 5:
# ws.cell(cell.row, cell.column + 1, '>5') # Get the row and column where the cell is located, not the index
# sum = 0
# ------
# Write the rows with different values in column B'Math' in multiple sheets: ...The function is similar to filtering
# row_area = ws.iter_rows(2, ws.max_row, 2, 2) # Get area cell_column B
# list_B = [] # Data type: cell value - the row number where this cell is located (storage double column data)
# for row in row_area: # Assign value to list_B
# for cell in row:
# # print(cell.value)
# list_B.append(str(cell.value) + '-' + str(cell.row)) # Get the row where the cell is located
#
# str_different_values = '' # See how many different values there are in column B 'math'.
# for i in list_B:
# values = i.split('-')[0]
# # print(type(values))
# if str_different_values.find(values) == -1:
# str_different_values + = values + ',' # If there is no data in str, add it to str.
# new_ws = wb.create_sheet() # Create as many sheets as there are different values and name them after the values.
# new_ws.title = 'math_' + str(values)
# # print('str: ' + str_different_values)
#
# for i in list_B: # Write data
# cell_value = int(i.split('-')[0]) # Value in cell
# row = int(i.split('-')[1]) # The row number of this cell
#
# sheet_name = 'math_' + str(cell_value)
# new_ws = wb[sheet_name] # Select the sheet to insert data into
# # print(new_ws)
#
# row_area = ws.iter_rows(row, row, 1, ws.max_column) # Get area cell_column B
#
# list_data = []
# for row in row_area: # Get the original table, the entire row of data
# for cell in row:
# list_data.append(cell.value)
# # print(list_data)
# new_ws.append(list_data) # In the last row of the sheet, write data
# ----------
#------------------------------------------------ ------------------
# Generation of EndTag:
update_time_EndTag = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()) # Format time
date_show_row_EndTag = ws.max_row + 2 # Row number of date display
ws.merge_cells(start_row=date_show_row_EndTag, end_row=date_show_row_EndTag, start_column=1, end_column=5) # Merge cells
fill_color_EndTag = l.styles.PatternFill(patternType='solid', fgColor='FFFF00') # Fill color
align_method_EndTag = l.styles.Alignment(horizontal='left', vertical='center') # Alignment
ws.cell(date_show_row_EndTag, 1, 'EndTag-last generation time:' + str(update_time_EndTag)) # Assign a value to the cell
ws.cell(date_show_row_EndTag, 1).fill = fill_color_EndTag # Set the fill color for the cell
ws.cell(date_show_row_EndTag, 1).alignment = align_method_EndTag # Set alignment for cells
wb.save(file_result_path)