How to write an Excel file to memory?

Question

I am writing a set of records in excel but I am not supposed to create a physical file. How do I write the records in excel in memory? If I do write it, how do I see the output of the records? I have tried:

import xlsxwriter
import io

filename= io.BytesIO()
workbook=xlsxwriter.Workbook(filename,{'in_memory': True})
worksheet=workbook.add_worksheet('sheet1')
worksheet.write(1,1,'Testing')
print(workbook.get_worksheet_by_name('sheet1'))
workbook.close()

What exactly is the problem with what you have (as far as writing the file to memory goes)? BTW, I think filename should be a string (and from the documentation it doesn't sound like you need to be using io.BytesIO anyway). — martineau
– martineau, Commented Mar 2, 2018 at 17:10
The issue i am facing is that i was able to write the contents from a database to an excel file, but i was told not to write t Ao a physical disk. I tried with io.stringIO() but no luck. The excel workbook has 10 sheets and i have to pipe it as a stream Any help will be appreciated. — Kumar Govind
– Kumar Govind, Commented Mar 3, 2018 at 17:51
Thank you Martineau. Yes your are right. I tweaked the code to look as follows and i was able to write in memory. with open("demo.xlsx", 'wb') as out: ## Open temporary file as bytes out.write(output.getvalue()) As mentioned by you, i had to supply a file name. — Kumar Govind
– Kumar Govind, Commented Mar 9, 2018 at 13:12
That line was to check whether i am able to write to memory or not. The actual code is: workbook = xlsxwriter.Workbook(output, {'in_memory': True}) The above code wrote the xlsx file into memory — Kumar Govind
– Kumar Govind, Commented Mar 9, 2018 at 13:53
I am trying to read a streamed xlsx file. The user inputs the data via user interface and then the data is passed to me via streaming in xlsx. How do i read it ? I couldn't find any documentation. cat text.xlsx | python myprogram.py How do i read this stream ? Any help would be appreciated. I am not sure whether openpyxl allows this read. I am using python3 — Kumar Govind
– Kumar Govind, Commented Mar 9, 2018 at 13:55

jmcnamara · Accepted Answer · 2022-08-05 09:28:43Z

3

From the XlsxWriter docs:

It is possible to write files to in-memory strings using BytesIO as follows:

    from io import BytesIO

    output = BytesIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet()

    worksheet.write('A1', 'Hello')
    workbook.close()


    xlsx_data = output.getvalue()

To avoid the use of any temporary files and keep the entire file in-memory use the in_memory constructor option shown above.

See also Example: Simple HTTP Server.

answered Aug 5, 2022 at 9:28

jmcnamara

42.1k7 gold badges98 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ksyrium · Accepted Answer · 2022-08-05 12:50:21Z

0

If it's okay for you to use xlwt (seems to only work with old .xls files), you can try something like this:

import io
from xlwt import Workbook

file_excel = io.BytesIO()
workbook = Workbook(encoding='utf-8')
sheet = workbook.add_sheet('sheet1')

sheet.write(0, 0, 'column1')
sheet.write(0, 1, 'column2')
sheet.write(0, 2, 'column3')

sheet.write(1, 0, 'Field11')
sheet.write(1, 1, 'Field2')
sheet.write(1, 2, 'Field3')

workbook.save(file_excel)

data = file_excel.getvalue()

Source: https://www.programcreek.com/python/example/6932/xlwt.Workbook.

edited Aug 5, 2022 at 12:50

answered Aug 5, 2022 at 8:45

ksyrium

1082 gold badges3 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to write an Excel file to memory?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related