How do I write data to excel file without changing the format cells in orginal file using python?

Question

I am using xlwt and xlrd with Python to get some data from something and writing it into an xls file, but I need write data to excel file, just the data, without changing the format cells in (original file)

Here's the code for that : ***

from xlrd import open_workbook
from xlwt import easyxf
from xlutils.copy import copy

data = [['2008','2009'],['Jan','Feb','Feb'],[100,200,300]]


rb = open_workbook('this1.xls')
rs = rb.sheet_by_index(0)
wb = copy(rb)
ws = wb.get_sheet(0)
# I want write data to excel file, just the data, without changing the format cells in (original file)
for i,row in enumerate(data):
      for j,cell in enumerate(row):
         ws.write(j,i,cell)

score 2 · Answer 1 · answered Jan 08 '16 at 20:59

You could try the openpyxl module which allows gives you the load_workbook option for using existing excel documents and keeping the formatting. I was using xlrd and xlwt but when I found that xlutils was not supported in python 3.5, and there was no other ways to add to a spreadsheet with those modules, I switched to openpyxl. Keep in mind though if you do switch over, openpyxl only supports .xlsx documents, so you would have to switch your document to be an .xlsx file. If you would like to read more about openpyxl take a look at this website: https://automatetheboringstuff.com/chapter12/ You will find a lot of good information on how to use openpyxl. Hopefully this helps!

score 2 · Answer 2 · answered Jan 08 '16 at 21:22

Writing to a cell removes its style information, but you can preserve it and reassign it after the write. I've done this in the past with the undocumented internal rows and _Row__cells data structures:

def write_with_style(ws, row, col, value):
    if ws.rows[row]._Row__cells[col]:
        old_xf_idx = ws.rows[row]._Row__cells[col].xf_idx
        ws.write(row, col, value)
        ws.rows[row]._Row__cells[col].xf_idx = old_xf_idx
    else:
        ws.write(row, col, value)

Obviously this is vulnerable to version incompatibilities, since it uses internal data structures.

How do I write data to excel file without changing the format cells in orginal file using python?

2 Answers2