python Of xlwings Module error ：pywintypes.com_error: (-2147352567, ' An accident happened .', (0, None, None, None, 0, -2147024809), None)
import pandas as pd
import xlwings as xw
app = xw.App(visible=False, add_book=False) # start-up excel Program
hlb = app.books.open(' Exchange rate table .xls') # Open read exchange rate book
hlb_b = hlb.sheets[0] # Read sheet
bz = hlb_b.range('A1').expand('down').value # Read the first column of currencies to form a list
hl = hlb_b.range('B1').expand('down').value # Read the second column of exchange rate to form a list
hlb.close() # Close workbook
print(f' The first stage {bz, hl}') # Output the first stage monitoring points ok
bz_hl = dict(zip(bz, hl)) # Component currency ： A dictionary of exchange rates
wb = app.books.open('fnd_gfm_930958.xls')
ws = wb.sheets[0]
sj = ws.range(1, 1).expand('right').value # Read the list of fields in the first row
hs = ws.range(1, 1).expand('down').rows.count
print(f' The third stage {sj, hs}') # Output the monitoring points of the third stage ok
hb = [' Issuing unit: ', ' Commitment bank ', ' Commitment currency '] # Define the two fields that need to be identified
a = sj.index(hb[0])+1 # Know the number of columns of the corresponding field in the worksheet
b = sj.index(hb[1])+1 # Commitment bank field
c = sj.index(hb[2])+1 # Commitment currency field
print(f' The fourth stage {a, b, c}') # Output the monitoring points of the fourth stage ok ** There will be a pause **
lst = [] # Column empty table
lst_bz = [] # Empty currency list
for i in range(1, hs): # Traverse two columns of fields to merge
sc1 = ws.range(i+1, a).value # Get the corresponding cell value
sc2 = ws.range(i+1, b).value
sc3 = ws.range(i+1, c).value
sc = r'{}-{}'.format(sc1, sc2) # Merge fields ： Issuing unit: - Commitment bank
lst += [sc] # Make up a list 【 Issuing unit: - Commitment bank 】
lst_bz += [sc3] # Make up the currency list
print(f' The fifth stage {lst}') # Output the monitoring points of the fifth stage ok
jg = list(set(lst)) # Filter out duplicate values , Form a non repeating list
jg_bz = list(set(lst_bz))
jg_bz = sorted(jg_bz) # Arrange the currencies in alphabetical order
print(f' Phase 6 {jg_bz}') # Output the monitoring points of the sixth stage ok
jg_gs = len(jg) # List the number of
jg_bz_gs = len(jg_bz)
print(f' The seventh stage {jg_gs}') # Output the monitoring points of the seventh stage ok
ws2 = wb.sheets.add(name=' Output results ', after=ws)
ws2.range(2, 1).value = [' Issuing unit: ', ' Commitment bank ', ' Amount in RMB '] # Write normal text
ws2.range(1, 2).value = ' Commitment currency '
for j in range(jg_gs): # Traverse the list 【 Issuing unit: - Commitment bank 】
sr = jg[j].split('-') # Split list fields ： Levying unit Commitment bank Two lists
print(f' The eighth stage {sr}') # Output the monitoring points of the eighth stage ok Then, the later ones will not run and report errors
ws2.range(j+3, 0).value = sr[0] # Worksheet input fields 1
ws2.range(j+3, 1).value = sr[1]
for j in range(jg_bz_gs): # Traverse currency list
ws2.range(1, j+4).value = jg_bz[j] # Input field
ws2.autofit('c') # Automatically adjust column width
data = pd.read_excel('fnd_gfm_930958.xls') # utilize pandas Module read excel data
print(data[' Commitment amount '].sum())
for x in jg_bz: # Traverse currency list
shuzi = jg_bz.index(x) # Find the number of digits in the currency list
try:
dy_hl = bz_hl[x] # In currency - The exchange rate dictionary finds the exchange rate value of the corresponding currency
ws2.range(2, shuzi+4).value = dy_hl # Write on the second line
except KeyError:
ws2.range(2, shuzi+4).value = ' ' # Fill in the second line currency
lst2 = []
for x in range(jg_gs): # Traverse the list 【 Issuing unit: - Commitment bank 】 Corresponding number of writing lines
for y in range(jg_bz_gs): # Traverse the currency list and write the corresponding number of columns
d = jg[x].split('-')
e = data[(data[' Issuing unit: '] == d[0]) & (data[' Commitment bank '] == d[1]) & (data[' Commitment currency '] == jg_bz[y])
& (data[' Dishonoured '] == 'N') & (data[' Financial remarks '].str.contains(' Purchasing foreign exchange '))] # Use filter criteria to filter data
f = e[' Commitment amount '].sum() # Simply sum the filtered data
g = e[' The amount of foreign exchange applied for '].sum() # Filter out the amount of foreign exchange used
lst2 += [f-g]
# print(f)
ws2.range(x+3, y+40).value = f-g
wb.save(" Results file .xls") # Save workbook
wb.close()
app.quit()
Baidu Guo , It is said that this kind of error report only occurs when the previous document is still open , But I am in a excel In the operation , Why would they
