Changing formats of contents in columns in an exis

Question

I want to change the format of the contents in an Excel workbook.

Environment: Win 7; Python 2.76

I want to change the columns A, B and C to the desired format. What I have is:

from openpyxl import Workbook
from openpyxl import load_workbook

wb = load_workbook('c:\\oldfile.xls')
ws = wb.active
d = ws.cell(column = 0) # or 1, 2, 3
d.style = Style(font=Font(bold=False),
                borders=Borders(left=Border(border_style='none'),
                                right=Border(border_style='none'),
                                top=Border(border_style='none'),
                                bottom=Border(border_style='none')),
                color=Color(Color.RED))


wb.save('c:\\oldfile.xls')

Obviously the way indicating the columns is wrong. so my questions are:

1. How to change the formats of the contents in a whole column?
2. As “openpyxl” only deals with formats of .xlsx and .xlsm, how can I have the formats changed if the original files are .xls (with converting the files into .xlsx)?

Thanks.

---

this is using 'easyxf' however it formats all the contents

---

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

old_file = open_workbook('c:\\oldfile.xls',formatting_info=True)
old_sheet = old_file.sheet_by_index(0)

new_file = copy(old_file)
new_sheet = new_file.get_sheet(0)

style = easyxf('font: bold false;'
               'borders: left no_line, right no_line, top no_line, bottom no_line;'
               'font: color red')

row_data = []

for row_index in range(old_sheet.nrows):
    rows = old_sheet.row_values(row_index)
    row_data.append(rows)

for row_index, row in enumerate(row_data):

   for col_index, cell_value in enumerate(row):
       new_sheet.write(row_index, col_index, cell_value, style)

new_file.save('c:\\newfile.xls')  
#and to use os.rename and remove to make it looked like only worked on 1 file

Answer 1

well, found actually just indicated the columns need to be formatted in the writing, it can be done.

putting them together for it may be useful:

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

old_file = open_workbook('c:\\oldfile.xls',formatting_info=True)
old_sheet = old_file.sheet_by_index(0)

new_file = copy(old_file)
new_sheet = new_file.get_sheet(0)

style = easyxf('font: bold true;'
               'borders: left no_line, right no_line, top no_line, bottom no_line;'
               'font: color red')

row_data = []

for row_index in range(old_sheet.nrows):
    rows = old_sheet.row_values(row_index)
    row_data.append(rows)

for row_index, row in enumerate(row_data):
    for col_index, cell_value in enumerate(row):

        # indicate the columns want to be formatted
        if col_index == 0 or col_index == 1:
            new_sheet.write(row_index, col_index, cell_value, style)
        else:
            new_sheet.write(row_index, col_index, cell_value)

new_file.save('c:\\newfile.xls')  

Answer 2

I don't know of any Python utility that will convert .xls files to .xlsx. You could simply combine the two libraries: read with xlrd and write with openpyxl or xlsxwriter. openpyxl does support formatting entire columns and rows with the ColumnDimension and RowDimension objects.