有人能用openpyxl對excel實作行列轉換嗎？這個問題為啥用openpyxl實作老報錯。有人能幫忙寫一個嗎？我是先load_workbook()建立一個Workbook物件，建一個sheet物件，讀入資料。然后再Workbook()一個物件，建立一個Sheet物件，讀入。一切順利。但最后一步，第2個Workbook物件用save方法時就報錯了。
報錯如下。：
Traceback (most recent call last):
File "C:\Users\Administrator.SC-201901230905\PycharmProjects\test\venv\lib\site-packages\openpyxl\utils\cell.py", line 110, in get_column_letter
return _STRING_COL_CACHE[idx]
KeyError: 23758
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "C:/Users/Administrator.SC-201901230905/PycharmProjects/test/test.py", line 33, in <module>
wb2.save('tt.xlsx')
File "C:\Users\Administrator.SC-201901230905\PycharmProjects\test\venv\lib\site-packages\openpyxl\workbook\workbook.py", line 392, in save
save_workbook(self, filename)
File "C:\Users\Administrator.SC-201901230905\PycharmProjects\test\venv\lib\site-packages\openpyxl\writer\excel.py", line 293, in save_workbook
writer.save()
File "C:\Users\Administrator.SC-201901230905\PycharmProjects\test\venv\lib\site-packages\openpyxl\writer\excel.py", line 275, in save
self.write_data()
File "C:\Users\Administrator.SC-201901230905\PycharmProjects\test\venv\lib\site-packages\openpyxl\writer\excel.py", line 75, in write_data
self._write_worksheets()
File "C:\Users\Administrator.SC-201901230905\PycharmProjects\test\venv\lib\site-packages\openpyxl\writer\excel.py", line 215, in _write_worksheets
self.write_worksheet(ws)
File "C:\Users\Administrator.SC-201901230905\PycharmProjects\test\venv\lib\site-packages\openpyxl\writer\excel.py", line 200, in write_worksheet
writer.write()
File "C:\Users\Administrator.SC-201901230905\PycharmProjects\test\venv\lib\site-packages\openpyxl\worksheet\_writer.py", line 357, in write
self.write_top()
File "C:\Users\Administrator.SC-201901230905\PycharmProjects\test\venv\lib\site-packages\openpyxl\worksheet\_writer.py", line 99, in write_top
self.write_dimensions()
File "C:\Users\Administrator.SC-201901230905\PycharmProjects\test\venv\lib\site-packages\openpyxl\worksheet\_writer.py", line 69, in write_dimensions
dim = SheetDimension(ref())
File "C:\Users\Administrator.SC-201901230905\PycharmProjects\test\venv\lib\site-packages\openpyxl\worksheet\worksheet.py", line 396, in calculate_dimension
return f"{get_column_letter(min_col)}{min_row}:{get_column_letter(max_col)}{max_row}"
File "C:\Users\Administrator.SC-201901230905\PycharmProjects\test\venv\lib\site-packages\openpyxl\utils\cell.py", line 112, in get_column_letter
raise ValueError("Invalid column index {0}".format(idx))
ValueError: Invalid column index 23758
Error in atexit._run_exitfuncs:
Traceback (most recent call last):
File "C:\Users\Administrator.SC-201901230905\PycharmProjects\test\venv\lib\site-packages\openpyxl\worksheet\_writer.py", line 32, in _openpyxl_shutdown
os.remove(path)
PermissionError: [WinError 32] 另一個程式正在使用此檔案，行程無法訪問。: 'C:\\Users\\ADMINI~1.SC-\\AppData\\Local\\Temp\\openpyxl.2zfq0dk0'
uj5u.com熱心網友回復：
代碼如下：問題的關鍵在于報錯了，邏輯覺得沒啥問題啊import openpyxl, os, sys
os.chdir(r'C:\Users\Administrator.SC-201901230905\Desktop')
wb = openpyxl.load_workbook('110.xlsx', data_only=False)
wb2 = openpyxl.Workbook()
sheet = wb.active
sheet2 = sheet.active_cell
# 從原始表中讀入資料
sheetDate = []
rows = sheet.rows
rows = [x for x in rows]
for k in range(len(rows)):
sheetDate_row = []
for v in range(len(rows[0])):
sheetDate_row.append(sheet.cell(row=k+1,column=v+1).value)
sheetDate.append(sheetDate_row)
# 行列轉換，并把資料都存在了anewData
anewData = []
for k in range(len(sheetDate[0])):
anewData_row = []
for v in range(len(sheetDate)):
anewData_row.append(sheetDate[v][k])
anewData.append(anewData_row)
# 從串列把資料寫入到sheet2
for k in range(len(anewData)):
for v in range(len(anewData[0])):
sheet2.cell(row=k+1,column=v+1).value = anewData[k][v]
wb.save('tt.xlsx') #明顯是這行報錯了
uj5u.com熱心網友回復：
你的新檔案不是 wb2 嗎？為啥又保存一次 wb 呢？
另外， 程式結束要有
wb2.close()
wb.close()
不然可能導致記憶體里的 excel實體越來越多。
uj5u.com熱心網友回復：
代碼應該是這個。import openpyxl, os, sys
os.chdir(r'C:\Users\Administrator.SC-201901230905\Desktop')
wb = openpyxl.load_workbook('110.xlsx', data_only=False)
wb2 = openpyxl.Workbook()
sheet = wb.active
sheet2 = wb2.active
# 從原始表中讀入資料
sheetDate = []
rows = sheet.rows
rows = [x for x in rows]
for k in range(len(rows)):
sheetDate_row = []
for v in range(len(rows[0])):
sheetDate_row.append(sheet.cell(row=k+1,column=v+1).value)
sheetDate.append(sheetDate_row)
# 行列轉換，并把資料都存在了anewData
anewData = []
for k in range(len(sheetDate[0])):
anewData_row = []
for v in range(len(sheetDate)):
anewData_row.append(sheetDate[v][k])
anewData.append(anewData_row)
# 從串列把資料寫入到sheet2
for k in range(len(anewData)):
for v in range(len(anewData[0])):
sheet2.cell(row=k+1,column=v+1).value = anewData[k][v]
wb2.save('tt.xlsx') #明顯是這行報錯了
uj5u.com熱心網友回復：
樓上貼的代碼， 我這邊運行正常。Python 3.7.4
openpyxl 3.0.0
uj5u.com熱心網友回復：
我這邊不是虛擬環境。我看你的是虛擬環境，可能和虛擬環境的權限有關。
uj5u.com熱心網友回復：
行列轉換用.T()就行吧uj5u.com熱心網友回復：
建議使用pandas讀完之后，在DataFrame進行行轉列，然后再寫入。轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/16582.html
上一篇：[求助]kotlin報錯