更改现有Excel工作簿中列的内容格式 [英] Changing formats of contents in columns in an existing Excel workbook
本文介绍了更改现有Excel工作簿中列的内容格式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想更改Excel工作簿中内容的格式.
I want to change the format of the contents in an Excel workbook.
环境:Win 7; Python 2.76
Environment: Win 7; Python 2.76
我想将A,B和C列更改为所需的格式.我所拥有的是:
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:
- 如何更改整列内容的格式?
- 由于"openpyxl"仅处理.xlsx和.xlsm格式,如果原始文件是.xls(将文件转换为.xlsx),如何更改格式?
谢谢.
这使用的是'easyxf',但是它会格式化所有内容
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
推荐答案
好吧,发现实际上只是指出在编写过程中需要对列进行格式化,这是可以做到的.
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')
这篇关于更改现有Excel工作簿中列的内容格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文