python - 如何使用python从mysql表数据写入excel

Question

我正在尝试通过mysql表数据在Excel工作表中插入数据。

'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'

喜欢这个表格，并希望将这些数据填充到 Excel 工作表中。

Excel 格式为:

                                    2005-Y  2006-Y  2007-Y  2008-Y  2009-Y  2010-Y  2011-Y  2012-Y  2013-Y  2014-Y  2015-Y  2016-Y  2017-Y 2018-Y 2019-Y

Feedstock                 Naphtha   5   0   0   5   -   -   -   5   -   5   5   5   5 - -
Building Blocks (a)Olefine Ethylene 5   5   5   5   -   -   -   5   -   2.5 2.5 2.5 2.5 - -
                          Propylene 5   5   5   5   -   -   -   5   -   2.5 2.5 2.5 2.5 - - 
                          Butadiene 5   5   5   5   -   -   -   5   -   2.5 2.5 2.5 2.5 - -

我尝试过:

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')

我的代码:我尝试了这段代码，但这段代码插入了具有值的年份值，但我想插入2005-2019年的所有年份值。请参阅Excel格式，没有2009年、2010年、2011年、2013年、2018年、2019年的值，所以想要将上一年的值结转到当年。

Answer 1

我不知道您的数据库中有哪些数据。如果您按照下面的示例操作，您可以

import xlwt


cols = [
    ['Feedstock', 'None', 'Naphtha', '5.00000', '2005', 'Y'],
    ['Feedstock', 'None', 'Naphtha', '0.00000', '2006', 'Y'],
    ['Feedstock', 'None', 'Naphtha', '0.00000', '2007', 'Y'],
    ['Building Blocks', 'Olefins', 'Ethylene', '5.00000', '2005', 'Y'],
    ['Building Blocks', 'Olefins', 'Ethylene', '5.00000', '2006', 'Y'],
]

res = {}
for c1, c2, c3, c4, c5, c6 in cols:
    res_key = "{}-{}-{}".format(c1, c2, c3)
    year_key = "{}-{}".format(c5, c6)
    years = {"year": year_key, "value": c4}
    res.setdefault(res_key, []).append(years)

title = ["" for _ in range(3)]
_lst = [title]
is_first = True
for info, years in res.items():
    rows = [item if not item == "None" else "" for item in info.split("-")]
    for item in years:
        year = item["year"]
        value = int(float(item["value"]))
        rows.append(value)
        if is_first:
            title.append(year)
    is_first = False
    _lst.append(rows)

book = xlwt.Workbook(encoding='utf8')
sheet = book.add_sheet('untitled', cell_overwrite_ok=True)

for row, rowdata in enumerate(_lst):
    for col, val in enumerate(rowdata):
        sheet.write(row, col, val)
book.save("test.xls")