Writing to Existing Workbook Using Xlwt

Edit existing excel workbooks and sheets with xlrd and xlwt

As I wrote in the edits of the op, to edit existing excel documents you must use the xlutils module (Thanks Oliver)

Here is the proper way to do it:

#xlrd, xlutils and xlwt modules need to be installed.  
#Can be done via pip install <module>
from xlrd import open_workbook
from xlutils.copy import copy

rb = open_workbook("names.xls")
wb = copy(rb)

s = wb.get_sheet(0)
s.write(0,0,'A1')
wb.save('names.xls')

This replaces the contents of the cell located at a1 in the first sheet of "names.xls" with the text "a1", and then saves the document.

How to get Python script to write to existing sheet

sheet1 is never declared. Try changing it to

#import the writer
import xlwt
#import the reader
import xlrd
#open the sussex results spreadsheet
book = xlrd.open_workbook('sussex.xlsx')
#open the first sheet
first_sheet = book.sheet_by_index(0)
#print the values in the second column of the first sheet
print first_sheet.col_values(1)
#in cell 0,0 (first cell of the first row) write "NIF"
first_sheet.write(0, 6, "NIF")
#in cell 0,0 (first cell of the first row) write "Points scored"
first_sheet.write(0, 6, "Points scored")

edit: You could also use Pandas to read and write to Excel:

import pandas as pd
import numpy as np
#open the sussex results spreadsheet, first sheet is used automatically
df = pd.read_excel('sussex.xlsx')

#print the values in the second column of the first sheet
print(df.iloc[:,1])

#Create column 'NIF'
df['NIF'] = np.nan #I don't know what you want to do with this column, so I filled it with NaN's
#in cell 0,7 (first cell of the first row) write "Points scored"
df['Points scored'] = np.nan #I don't know what you want to do with this column, so I filled it with NaN's
<.... Do whatever calculations you want with NIF and Points scored ...> 
# Write output
df.to_excel('sussex.xlsx')

How do I write to an existing excel file using xlwt and keep the formatting?

You probably can't.
Microsoft created xlsx files for a reason: the classic xls format is a legacy binary file piling up hundreds, maybe thousands, of features, each reprented in differing ways (and the file format was not even openly documented back then, I don't know if it is now). So there is one app that can open a xls file and guarrantee to present what is there with all the features intended by the file creator: Excel. And the same Excel version that created the file, in that.

So, any open library that can write to xls will create the most basic files, with no formatting - and be lucky if it can parse out the content parts.

xlsx files on the other hand use conforming xml files internally, and even a program that does not care to know about the full specs can change information in the file and preserve formatting and other things simply by not touching anything it does not know about, and assembling a valid xml again.

That said, if you can't convert to xlsx, maybe the easier thing to do is use Python to drive Excel itself to make the changes for you, in an automated way.
The documentation for that is few and far apart, but that is possible by using pywin32 and the "COM" api - take a look here for a start: https://pbpython.com/windows-com.html

Another option is using LibreOffice - it can read and write xls files with formatting (though surely with losses), and is scriptable in Python. Unfortunatelly, the information on how to script LibreOffice using Python to do that is also hard to find, and the legacy option of using their "UNO" thing to enable interaction with Python makes its use complicated.

How to append to an existing excel sheet with XLWT in Python

After investigation today, (2014-2-18) I cannot see a way to read in a XLS file using xlwt. You can only write from fresh. I think it is better to use openpyxl. Here is a simple example:

from openpyxl import Workbook, load_workbook

wb = Workbook()
ws = wb.create_sheet()
ws.title = 'Pi'
ws.cell('F5').value = 3.14156265
wb.save(filename=r'C:\book2.xls')

# Re-opening the file:
wb_re_read = load_workbook(filename=r'C:\book2.xls')
sheet = wb_re_read.get_sheet_by_name('Pi')
print sheet.cell('F5').value

See other examples here: http://pythonhosted.org/openpyxl/usage.html (where this modified example is taken from)

Understanding for loop with xlwt

Hi try and understand that :D

import xlrd
import xlwt
from xlutils.copy import copy
import os

file_path = "test.xls"

rb = xlrd.open_workbook(file_path, formatting_info=True)
wb = copy(rb)
w_sheet = wb.get_sheet(0)
# For each sheet, do operation
for sheet in rb.sheets():
    # For each row in actual sheet, row += 1 at each operation
    for row in range(sheet.nrows - 1):
        # Set index for operation and result
        index = row + 2
        # Create operation
        operation = 'A' + str(index) + '-B' + str(index)
        # Use operation
        w_sheet.write(index-1, 2, xlwt.Formula(operation))
        # Print for you can understand what it do
        print ("index = " + str(index) + "  operation = " + operation)
# Save in file_path.out.xls
wb.save(file_path + '.out' + os.path.splitext(file_path)[-1])

---

Related Topics