problem reading one excel file and writing a new excel file

Issue #46 resolved
Brad Zoltick
created an issue

Hi,

I am having problems reading the values from one excel file and writing the values to another excel file. Here I am just writing out the same values but the intent to process the values before writing them to a new excel file. Can you tell me what I am doing wrong? I can read the values but when I try to write them out, the file is empty. {{{

!python

!/usr/bin/env python

from openpyxl.reader.excel import load_workbook from openpyxl.workbook import Workbook

xls_old = 'old.xlsx' xls_new = 'new.xlsx'

wb2 = Workbook() ws2 = wb2.create_sheet()

wb1 = load_workbook(xls_old) name = wb1.get_sheet_names()[0] ws2.title = name

ws1 = wb1.get_sheet_by_name(name)

for i, row in enumerate(ws1.rows): for j, cell in enumerate(row): sty = ws1.get_style(cell.address) ws2.cell(cell.address).value = cell.value # how to set the style for ws2.cell ? wb2.save(xls_new) }}}

Comments (17)

  1. Eric Gazoni

    hi, can you please re-post the script, but this time using the "code" markup button in the toolbar please (white scroll picture with <> on it) ? All the formatting is lost and I can't tell what is python code and what are your comments :)

  2. CharlieC

    The error has changed but this problem seems to persist.

    >>> wb.save("pyxl2.xlsx")
    Traceback (most recent call last):
      File "<stdin>", line 1, in <module>
      File "openpyxl/workbook.py", line 255, in save
        save_workbook(self, filename)
      File "openpyxl/writer/excel.py", line 190, in save_workbook
        writer.save(filename)
      File "openpyxl/writer/excel.py", line 173, in save
        self.write_data(archive)
      File "openpyxl/writer/excel.py", line 101, in write_data
        self._write_worksheets(archive, shared_string_table, self.style_writer)
      File "openpyxl/writer/excel.py", line 131, in _write_worksheets
        style_writer.get_style_by_hash()))
      File "openpyxl/writer/worksheet.py", line 96, in write_worksheet
        write_worksheet_cols(doc, worksheet, style_table)
      File "openpyxl/writer/worksheet.py", line 207, in write_worksheet_cols
        col_def['style'] = str(style_table[hash(columndimension.style_index)])
    KeyError: -8937945243006069197
    
  3. Bartev Vartanian

    I'd like to reopen this issue. I'm trying to read an existing xlsm file and write it out. I've tried writing to the same file, and also to a new filename, but after saving, the new file cannot be opened by excel (I'm running Excel Mac 2011). I'm running

    Python 2.7.6 :: Anaconda 1.9.1 (x86_64)

    MacBook Pro 2.3GHz, Intel Core i7

    OS X 10.9.2

    openpyxl 1.8.5 installed via pip

    #!/usr/bin/env python
    
    import openpyxl as px
    from openpyxl import Workbook
    from openpyxl import load_workbook
    
    # Open excel file
    macrofname='test_macro.xlsm'
    wb = load_workbook(macrofname)
    # wb.save('newfile.xlsm')
    wb.save(macrofname)
    

    Before running the script,

    32407 Mar 25 13:30 test_macro.xlsm
    

    After running the script,

    5977 Mar 25 15:17 test_macro.xlsm
    

    The only reason I'm using macros is because my pivot table seems to be destroyed when opening and then saving a file with a pivot table in it. If anybody has a suggestion on how to save the pivot table from being destroyed, that would be appreciated.

    import openpyxl as px
    from openpyxl import load_workbook
    
    filename = 'wbk1.xlsx'
    wb = load_workbook(filename=filename)
    wb.save(filename)
    

    I can't seem to load a file, but the 2 files I used were very simple. The macro just put a few values on the current sheet.

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
        ActiveCell.FormulaR1C1 = "a"
        Range("A2").Select
        ActiveCell.FormulaR1C1 = "b"
        Range("A3").Select
        ActiveCell.FormulaR1C1 = "c"
        Range("A4").Select
        ActiveCell.FormulaR1C1 = "d"
        Range("A5").Select
        ActiveCell.FormulaR1C1 = "e"
    End Sub
    ,
    

    The 2nd file was a few columns of data and a simple pivot table.

    Thanks

  4. Log in to comment