解決使用openpyxl時(shí)遇到的坑

from openpyxl import Workbook
import datetime

wb = Workbook()
ws = wb.active 
ws['A1'] = 42 
ws.append([1,2,3]) 
ws['A2'] = datetime.datetime.now()
wb.save('test.xlsx')

import pymysql
import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://user:password@ip:port/database",encoding='utf-8')

sql = """SELECT catalog_1 as '目錄一',catalog_2 as '目錄二',catagory as '目錄三',
    region as '區(qū)域',year as '年份',data as '數(shù)據(jù)',unit as '單位' from table
    where catalog_1 = "農(nóng)業(yè)" limit 100
"""
df = pd.read_sql_query(sql, con=engine)


# writer = pd.ExcelWriter(r'C:\Users\Administrator\Desktop\test.xlsx')
# df.to_excel(writer)
# writer.save()

df.to_csv(r'C:\Users\Administrator\Desktop\test.csv',index=False)
# 經(jīng)過(guò)驗(yàn)證, 此種方法是行得通的

import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
df1=pd.DataFrame([[1,4],[2,5],[3,6]] ,index=['a','b','c'],columns=['a','b'])
df2=pd.DataFrame([[1,4],[2,5],[3,6],[7,8]] ,index=['d','e','f','g'],columns=['a','b'])
wb=Workbook()
ws=wb.active #打開(kāi)工作表
#把df1寫(xiě)入工作表
for row in dataframe_to_rows(df1):
 ws.append(row)
#換行
ws.append([])
#把df2寫(xiě)入工作表
for row in dataframe_to_rows(df2):
 ws.append(row)
wb.save('text.xlsx')

import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
df1=pd.DataFrame([[1,4],[2,5],[3,6]] ,index=['a','b','c'],columns=['a','b'])
df2=pd.DataFrame([[1,4],[2,5],[3,6],[7,8]] ,index=['d','e','f','g'],columns=['a','b'])
wb=Workbook()
ws=wb.active #打開(kāi)工作表
df1.index.name='code1'
df2.index.name='code2'
#把df1寫(xiě)入工作表
for row in dataframe_to_rows(df1.reset_index(),index=False):
 ws.append(row)
#換行
ws.append([])
#把df2寫(xiě)入工作表
for row in dataframe_to_rows(df2.reset_index(),index=False):
 ws.append(row)
wb.save('text.xlsx')