Write output in xlsb file format (Excel binary file format) using pandas and pyxlsb

Question

I've read a lot of stackoverflow and other threads where it's been mentioned how to read excel binary file.

Reference: Read XLSB File in Pandas Python

import pandas as pd
df = pd.read_excel('path_to_file.xlsb', engine='pyxlsb')

However, I can not find any solution on how to write it back as .xlsb file after processing using pandas? Can anyone please suggest a workable solution for this using python?

Any help is much appreciated!

Answer 1

you can read binary file with open_workbook under pyxlsb. Please find below the code:

import pandas as pd
from pyxlsb import open_workbook
path=r'D:\path_to_file.xlsb'
df2=[]
with open_workbook(path) as wb:
    with wb.get_sheet(1) as sheet:
       for row in sheet.rows():
           df2.append([item.v for item in row])
data= pd.DataFrame(df2[1:], columns=df2[0])

Answer 2

I haven't been able to find any solution to write into xlsb files or create xlsb files using python.

But maybe one work around is to save your file as xlsx using any of the many available libraries to do that (such as pandas, xlsxwriter, openpyxl) and then converting that file into a xlsb using xlsb-converter. https://github.com/gibz104/xlsb-converter

CAUTION: This repository uses WIN32COM, which is why this script only supports Windows