CODEWITHSUNDEEP
pythonpandasxlsxwriter
user9128740
user9128740

Reputation:

XlsxWriter write data frame starting from specific cell

I'm using xlsxwriter to create an Excel file. Currently, I'm doing something like this:

import datetime
import pandas as pd
import xlsxwriter

workbook = xlsxwriter.Workbook('Test.xlsx')
worksheet = workbook.add_worksheet()

datetime_format = workbook.add_format({
    'font_size': 12
})
worksheet.write('C2', datetime.datetime.now().strftime("%Y-%m-%d %H:%M"), datetime_format)

# Rest removed for brevity. I simply have many stuff that 
# I manually write to make the design as I want.

workbook.close()

writer = pd.ExcelWriter('Test.xlsx', engine='xlsxwriter')
result.to_excel(writer, startrow=6, startcol=3) # Start from D7.
writer.save()

Now, this seems to rewrite everything that I previously wrote manually using xlsxwriter. Also, I noticed that xlsxwriter does not have support by default to write data frames, am I right? How can I combine xlsxwriter and pandas to manually write some stuff to do Excel sheet, and then later to write all the data frame stuff starting from the specific cell?

Upvotes: 0

Views: 1587

Answers (1)

Teddy
Teddy

Reputation: 645

You need to shift things around. I would also recommend to read the XlsWritter documentation on how to use it with pandas.

writer = pd.ExcelWriter('Test.xlsx', engine='xlsxwriter')

workbook = writer.book

result.to_excel(writer, sheet_name='Sheet1', index=False, startrow=6, startcol=3) # Start from D7.

worksheet = writer.sheets['Sheet1']

datetime_format = workbook.add_format({
    'font_size': 12
})
worksheet.write('C2', datetime.datetime.now().strftime("%Y-%m-%d %H:%M"), datetime_format)

writer.save()

First, we create our writer object using pd.ExcelWriter() and passing xlswriter as the engine. Once we have our writer we can access our workbook using the .book class vriable. At this time, we can write our data into our workbook using df.to_excel(). We will use the sheet_name argument of to_excel to create and name our worksheet. At this point we can acess our worksheet using the .sheets class variable and passing our sheetname as a key - in our case Sheet1.

From there you can write additional values to your sheet with or without using a pandas object - as shown in the code below.

Upvotes: 1

Related Questions