0

I have one big excel，including several sheets. Now I need to save every sheet in one excel. Now, I finish and some cells which have formulas have value in the new excel. But I find one new problem,how can I save every sheets which keeps the original style (format) such as red background?I checked the former question, but still get no answer. Editing workbooks with rich text in openpyxl

from openpyxl import load_workbook,Workbook
wb = load_workbook("test11.xlsx",data_only=True)
sheetnames = wb.sheetnames
for name in sheetnames:
    ws = wb.get_sheet_by_name(name)
    print(ws)
    wb2 = Workbook()
    ws2 = wb2.active
    for i,row in enumerate(ws.iter_rows()):
        for j,cell in enumerate(row):
            ws2.cell(row=i+1, column=j+1, value=cell.value)
            ws2.title = name
    wb2.save(name + ".xlsx")
Hong
  • 263
  • 2
  • 8
  • What does the reference to rich text have to do with your question? There are lots of examples of how to copy formatting between cells. – Charlie Clark Feb 18 '22 at 07:57

2 Answers2

1

Every cell in openpyxl has a .style attribute that you can call and set. Your code would be this:

from openpyxl import load_workbook,Workbook
wb = load_workbook("test11.xlsx",data_only=True)
sheetnames = wb.sheetnames
for name in sheetnames:
    ws = wb.get_sheet_by_name(name)
    print(ws)
    wb2 = Workbook()
    ws2 = wb2.active
    for i,row in enumerate(ws.iter_rows()):
        for j,cell in enumerate(row):
            c = ws2.cell(row=i+1, column=j+1, value=cell.value)
            c.style = cell.style
            ws2.title = name
    wb2.save(name + ".xlsx")
Timmy Diehl
  • 409
  • 3
  • 16
  • I used your code,but have error. ValueError: 常规(normal) is not a known style – Hong Feb 18 '22 at 03:59
  • I just tested it out with my own excel document, and it worked. Can you show me a portion of your excel document? – Timmy Diehl Feb 18 '22 at 04:08
  • File "D:\personal file\Liu\untitled1.py", line 19, in c.style = cell.style File "d:\ProgramData\Anaconda3\lib\site-packages\openpyxl\styles\styleable.py", line 85, in __set__ raise ValueError("{0} is not a known style".format(value)) ValueError: 常规 is not a known style – Hong Feb 18 '22 at 05:39
  • I run your code, and get the above error. – Hong Feb 18 '22 at 05:40
  • Then you will need to copy the style between workbooks. – Charlie Clark Feb 18 '22 at 07:56
0

You could consider the following option instead. Basically this code makes a copy of the original xlsx file and deletes the unwanted sheets before saving with the sheet name. Since it is a copy of the original it should retain all the styling etc of each sheet.

from openpyxl import load_workbook


sheetnames = load_workbook('test11.xlsx').sheetnames

for name in sheetnames:
    wb = load_workbook("test11.xlsx")
    print(wb[name])

    for delsheet in sheetnames:
        if delsheet != name:
            del wb[delsheet]

    wb.calculation.calcMode = 'auto' # set formula calculation to auto
    wb.save(name + ".xlsx")
moken
  • 3,227
  • 8
  • 13
  • 23
  • Since many cells in my sheet have formulas, use your code these cells will be display as the formula,not the actual number(after calculating). – Hong Feb 21 '22 at 08:58
  • The cells are blank but contain the formulas right, if you hit F9 to refresh the sheet the cells will then update (if the source data is available). It's possible the 'Workbook Calculation' has been set to Manual when saved. It can be changed back to Automatic, go to Options --> Formulas --> Calculation options. I have updated the code to set the calc mode to auto before saving so if the calc was being set to manual that change should ensure it is back to automatic. – moken Feb 21 '22 at 13:12