3

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!

1stYrTechStudent
  • 33
  • 2
  • 6
  • Does this answer your question? [How do I convert a csv file to xlsb using Python?](https://stackoverflow.com/questions/24159542/how-do-i-convert-a-csv-file-to-xlsb-using-python) – Let's try Aug 25 '20 at 08:18
  • 2
    @Let'stry: I'll say probably not(there are minor error reported for above solution). Is there any other more concise and or cleaner way to do it (similar to how I am reading it). Actually, in my case, I am trying to write processed pandas dataframe as an .xlsb output without losing any functionality(formulas, etc) in .xlsb files. I am being extra cautious as these files will go to production. – 1stYrTechStudent Aug 25 '20 at 13:28

2 Answers2

0

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

Rahul Raj Purohit
  • 152
  • 10
-2

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])
Pramod Parab
  • 13
  • 1