回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p>我正在尝试用mysql表数据在Excel表中插入数据。在</p>
<pre><code>'Feedstock', 'None', 'Naphtha', '5.00000', '2005', 'Y'
'Feedstock', 'None', 'Naphtha', '0.00000', '2006', 'Y'
'Feedstock', 'None', 'Naphtha', '0.00000', '2007', 'Y'
'Feedstock', 'None', 'Naphtha', '5.00000', '2008', 'Y'
'Feedstock', 'None', 'Naphtha', '5.00000', '2012', 'Y'
'Feedstock', 'None', 'Naphtha', '5.00000', '2014', 'Y'
'Feedstock', 'None', 'Naphtha', '5.00000', '2015', 'Y'
'Feedstock', 'None', 'Naphtha', '5.00000', '2016', 'Y'
'Feedstock', 'None', 'Naphtha', '5.00000', '2017', 'Y'
'Building Blocks', 'Olefins', 'Ethylene', '5.00000', '2005', 'Y'
'Building Blocks', 'Olefins', 'Ethylene', '5.00000', '2006', 'Y'
</code></pre>
<p>喜欢这个表格,想把这些数据填写到excel表格中。在</p>
<p>excel格式为:</p>
^{pr2}$
<p>我试过了:</p>
<pre><code>import pymysql
from xlsxwriter.workbook import Workbook
from openpyxl import load_workbook
import openpyxl
from openpyxl.styles import Alignment, Font
sqlconn = ".............."
cursor = sqlconn.cursor()
wb = load_workbook('cpm_auto.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
max_col = (sheet.max_column)
max_row = (sheet.max_row)
for row in range(3,max_row):
for col in range(4,max_col):
periods = sheet.cell(row = 1,column = col).value
period = periods.replace('-Y','')
val = sheet.cell(row = row,column = 3).value
if val == 'Naphtha':
query = "select Value from CPMAI where Product = '%s' and Year = '%s'" %(val,period)
result = cursor.execute(query)
if result > 0:
values = cursor.fetchone()
if len(values) > 0:
prev_value = values[0]
print(prev_value)
sheet.cell(row = row,column = col).value = values[0]
else:
sheet.cell(row=row, column=col).value = prev_value
break
wb.save('cpm_auto.xlsx')
</code></pre>
<p>我的代码:
我尝试了这个代码,但是这个代码插入了有值的年份的值,但是我想插入2005-2019年的全年值。请参阅excel格式2009、2010、2011、2013、2018、2019年没有值,因此希望将上一年的值结转到当年。在</p>