python - Using openpyxl to copy from one workbook to another results in error when saving -

-

i trying append values 1 sheet row row new workbook. code works when run on small test file, when run on target file returns error when saving.

here code:

from openpyxl import load_workbook openpyxl import workbook  wb = load_workbook(filename='rm activity-pricing report - 2014-05-31.xlsm',keep_vba=false, data_only=true) ws_ottawa = wb.get_sheet_by_name('ottawa')  wb2 = workbook() ws2 = wb2.create_sheet()  row in ws_ottawa.iter_rows():         ws2.append(row)  wb2.save('new_big_file.xlsx')

the output error in spyder (python 3.5) is:

traceback (most recent call last):  file "<ipython-input-22-171ffbcd4891>", line 1, in <module> runfile('z:/revenue management report/extractpromodata.py', wdir='z:/revenue management report')  file "c:\anaconda3-64\lib\site-packages\spyderlib\widgets\externalshell\sitecustomize.py", line 699, in runfile execfile(filename, namespace)  file "c:\anaconda3-64\lib\site-packages\spyderlib\widgets\externalshell\sitecustomize.py", line 88, in execfile exec(compile(open(filename, 'rb').read(), filename, 'exec'), namespace)  file "z:/revenue management report/extractpromodata.py", line 35, in <module> wb2.save('new_big_file4.xlsx')  file "c:\anaconda3-64\lib\site-packages\openpyxl\workbook\workbook.py", line 298, in save save_workbook(self, filename)  file "c:\anaconda3-64\lib\site-packages\openpyxl\writer\excel.py", line 198, in save_workbook writer.save(filename, as_template=as_template)  file "c:\anaconda3-64\lib\site-packages\openpyxl\writer\excel.py", line 181, in save self.write_data(archive, as_template=as_template)  file "c:\anaconda3-64\lib\site-packages\openpyxl\writer\excel.py", line 87, in write_data self._write_worksheets(archive)  file "c:\anaconda3-64\lib\site-packages\openpyxl\writer\excel.py", line 114, in _write_worksheets write_worksheet(sheet, self.workbook.shared_strings,  file "c:\anaconda3-64\lib\site-packages\openpyxl\writer\worksheet.py", line 233, in write_worksheet write_rows(xf, worksheet)  file "c:\anaconda3-64\lib\site-packages\openpyxl\writer\lxml_worksheet.py", line 59, in write_rows if cell.value none , not cell.has_style:  file "c:\anaconda3-64\lib\site-packages\openpyxl\cell\cell.py", line 306, in value if value not none , self.is_date:  file "c:\anaconda3-64\lib\site-packages\openpyxl\cell\cell.py", line 351, in is_date if self.data_type == "n" , self.number_format != "general":  file "c:\anaconda3-64\lib\site-packages\openpyxl\styles\styleable.py", line 49, in __get__ return coll[idx - 164] indexerror: list index out of range

i not error when use code on smaller test .xlsx file.

possible reasons problem suspect are:

1)input file .xlsm

2)input file has columns ci

3)input file password protected (but since error in saving not seem should issue)

taking account charlie said, work-around openpyxl import load_workbook openpyxl import workbook

 wb = load_workbook(filename='rm activity-pricing report - 2014-5-31.xlsm',keep_vba=false, data_only=true)#,guess_types=true)   ws_ottawa = wb.get_sheet_by_name('ottawa')  wb2 = workbook() ws2 = wb2.create_sheet() counter = 0 new_rows = [] rrow in ws_ottawa.iter_rows():     new_rows.append([])     cell in rrow:         new_rows[counter].append(cell.value)     counter +=1 wrow in new_rows:      ws2.append(wrow)  wb2.save('new_big_file4.xlsx')   print("all done")

you quite cannot trying do. unfortunately, way data stored within file formats means relevant information not stored cell using reference workbook object. these differ workbook workbook why see errors when saving: number format want use doesn't exist in new file.


Comments

Post a Comment

Popular posts from this blog

ruby - Trying to change last to "x"s to 23 -

-
i have been playing around irb , have string looks this: irb(main):072:0> puts ["[\"4354 5432 5432 xxxxx\", \"6547 6547 8543 xxxxx\", \"2344 6543 6674 xxxxx\", \"2346 6236 7543 xxxxx\", \"1273 5585 5587 xxxxx\"]"] => nil irb(main):073:0> what want gsub last 2 "x" s of each 17 digit combination, 23 . what i've tried far (i've been using rubular): a.gsub(/\d[x]/,"23") <= grabs "x" s i'm close a.gsub(/\w[x{2}]/,"23") <= grabs "x" s grabs two digits each combination. is there easier way i'm not understanding? a.gsub(/\d{4} \d{4} \d{4} xxx\kxx/, '23') \d{4} = 4 digits what \k drop matched far. can use don't replace entire number, still able validate have in desired format.
Read more

jquery - Clone last and append item to closest class -

-
this bare-bones code on page: <div class="list-container"> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> </div> <p><button class="add-list-item">add</button></p> <div class="list-container"> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> </div> <p><button class="add-list-item">add</button></p> <di
Read more

c - Unrecognised emulation mode: elf_i386 on MinGW32 -

-
i'm trying make kernel, , cannot link c output assembly. ld . i'm getting error: unrecognized emulation mode: elf_i386 i'm using windows 10 professional mingw32 , msys. code using: link.ld /* * link.ld */ output_format(elf32-i386) entry(start) sections { . = 0x100000; .text : { *(.text) } .data : { *(.data) } .bss : { *(.bss) } } kernel.c /* * kernel.c */ void kmain(void) { const char *str = "my first kernel"; char *vidptr = (char*)0xb8000; //video mem begins here. unsigned int = 0; unsigned int j = 0; /* loops clears screen * there 25 lines each of 80 columns; each element takes 2 bytes */ while(j < 80 * 25 * 2) { /* blank character */ vidptr[j] = ' '; /* attribute-byte - light grey on black screen */ vidptr[j+1] = 0x07; j = j + 2; } j = 0; /* loop writes string video memory */ while(str[j] != '\0') { /
Read more