我正在使用Openpyxl庫來讀取xlsx檔案并提取一些內容并將更多字串添加到我的 txt 輸出檔案中。我目前使用的 excel 檔案包含名稱為 Summary 和 Employee 的作業表。我的以下代碼適用于我當前的 excel 檔案。現在的問題是我會使用相同的代碼來讀取另一個包含更多作業表的 Excel 檔案，這些作業表的作業表名稱我不確定。所以在我的代碼行ws = wb['Employee']中。作業表名稱將一直更改。但是，我確定的一件事是我不想從 sheet1 讀取任何資料。所有資料提取將從 sheet2 開始在所有xlsx檔案中進行。我不知道如何從這里開始，所以任何幫助將不勝感激。
提前感謝您的時間和努力！
代碼：
from openpyxl import load_workbook
data_file='\\test.xlsx'
# Load the entire workbook.
wb = load_workbook(data_file)
ws = wb['Employee'] #Manually adding sheet name here
mylines={"Column_name":[],"Column_Type":[]} #Getting 2 columns data from row 6
type_strs = {
'String': 'VARCHAR(256)',
'Numeric': 'NUMBER',
'Date': 'NUMBER(4,0)',
'Int': 'NUMBER'
}
for index, value in enumerate(mylines["Column_Type"]):
mylines["Column_Type"][index] = type_strs.get(value, value)
for i in range(6, ws.max_row 1):
name = ws.cell(row=i, column=1).value
name1=ws.cell(row=i, column=2).value
mylines["Column_name"].append(name) #Appending dictionary key "Column_name"
mylines["Column_Type"].append(name1) #Appending dictionay key "Column_type"
for index, value in enumerate(mylines["Column_Type"]):
mylines["Column_Type"][index] = type_strs.get(value, value)
theString = " "
for i in range(len(mylines['Column_name'])):
theString = mylines['Column_name'][i] " " mylines['Column_Type'][i]
if i < len(mylines['Column_name'])-1:
theString = ", "
outputFile = open('/output.txt', 'w') # Text file Output
outputFile.write("CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE({});".format(theString) "\n")
outputFile.close() #Closing file
根據 SO 用戶評論更新代碼：
from openpyxl import load_workbook
data_file='\\test.xlsx'
# Load the entire workbook.
wb = load_workbook(data_file)
#ws = wb['Employee'] #Manually adding sheet name here
mylines={"Column_name":[],"Column_Type":[]} #Getting 2 columns data from row 6
type_strs = {
'String': 'VARCHAR(256)',
'Numeric': 'NUMBER',
'Date': 'NUMBER(4,0)',
'Int': 'NUMBER'
}
for index, value in enumerate(mylines["Column_Type"]):
mylines["Column_Type"][index] = type_strs.get(value, value)
skip = True
for ws in wb.worksheets:
if skip == True:
skip = False
else:
for i in range(6, ws.max_row 1):
name = ws.cell(row=i, column=1).value
name1=ws.cell(row=i, column=2).value
mylines["Column_name"].append(name) #Appending dictionary key "Column_name"
mylines["Column_Type"].append(name1) #Appending dictionay key "Column_type"
for index, value in enumerate(mylines["Column_Type"]):
mylines["Column_Type"][index] = type_strs.get(value, value)
theString = " "
for i in range(len(mylines['Column_name'])):
theString = mylines['Column_name'][i] " " mylines['Column_Type'][i]
if i < len(mylines['Column_name'])-1:
theString = ", "
outputFile = open('/output.txt', 'w') # Text file Output
outputFile.write("CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE({});".format(theString) "\n")
outputFile.close() #Closing file
Excel 資料
<Sheet 1 Name -> Summary Sheet: Empty
<Sheet 2 Name -> Employee Sheet
File Name: Employee
Sheet Name: Employee
File Type: csv
Field Name Type
Name String
Salary Numeric
Date Date
Phone Int
<Sheet 3 Name-> Employee1 Sheet
File Name: Employee
Sheet Name: Employee1
File Type: csv
Field Name Type
Employee Name Date
Employee Salary Int
Employment Date Int
Office Phone Int
uj5u.com熱心網友回復：
要遍歷作業簿中的所有作業表并讀取其中的資料（第一個作業表除外，請洗掉ws = wb['Employee']
使用 for 回圈（在for i in range(5,...此之前插入
skip = True
for ws in wb.worksheets:
if skip == True:
skip = False
else:
for i in range(6, ws.max_row 1):
name = ws.cell(row=i, column=1).value
....
這將讀取每張紙并將資料附加到mylines，除了第一張紙
第二次更新 正如您在下面的評論中提到的，要使用新的 SQL 查詢添加新行，請進行這些額外的更改
- 向字典中添加另一個條目以指示新行，如下所示（小心確保在讀取特定作業表中的所有行之后執行行） 編輯字串格式，以便一旦看到 NewLine，該字串將寫入輸出檔案。請注意，NewFile 布林值將覆寫那里的任何檔案。之后將附加多行。
skip = True
for ws in wb.worksheets:
if skip == True:
skip = False
else:
for i in range(6, ws.max_row 1):
name = ws.cell(row=i, column=1).value
print(i, name)
name1=ws.cell(row=i, column=2).value
print(name1)
mylines["Column_name"].append(name) #Appending dictionary key "Column_name"
mylines["Column_Type"].append(name1) #Appending dictionay key "Column_type"
for index, value in enumerate(mylines["Column_Type"]):
mylines["Column_Type"][index] = type_strs.get(value, value)
mylines["Column_name"].append('NextLine')
mylines["Column_Type"].append('NextLine')
theString = " "
NewFile = True
for i in range(len(mylines['Column_name'])):
if(mylines['Column_name'][i] != 'NextLine'):
theString = mylines['Column_name'][i] " " mylines['Column_Type'][i]
theString = ", "
else:
theString = theString[:-2]
if NewFile:
NewFile = False
outputFile = open('output.txt', 'w') # Text file Output
print("New file ", theString)
else:
outputFile = open('output.txt', 'a')
print("Not new file ", theString)
outputFile.write("CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE({});".format(theString) "\n")
outputFile.close()
theString = " "
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/463583.html
標籤：Python python-3.x 擅长 哎呀