Home Original page

Copying a row from a range of Excel files to another

MRAB python at mrabarnett.plus.com
Wed Jun 26 15:07:23 EDT 2019 
On 2019-06-26 13:15, Cecil Westerhof wrote:
> Cecil Westerhof <Cecil at decebal.nl> writes:
> 
>> I was asked to copy a certain line from about 300 Excel lines to a new
>> Excel file. That is not something I would like to do by hand and I
>> immediately thought: that should be possible with Python.
>>
>> And it is. I was surprised how fast I could write that with openpyxl.
>> My first try was not very neat, but a proof of concept. Then by
>> looking better at the possibilities I could get much cleaner code. But
>> I am still not completely happy. At the moment I have the following
>> code:
>>     wb_out = Workbook()
>>     for filepath in filepathArr:
>>         current_row = []
>>         wb_in       = load_workbook(filepath)
>>         for cell in wb_in.active[src_row]:
>>             current_row.append(cell.value)
>>         wb_out.active.append(current_row)
>>         wb_in.close()
>>     wb_out.save(report_start + datetime.now().strftime('%Y-%m-%d') + report_end)
>>     wb_out.close()
>>
>> I could not find a way to copy a row from one workbook to another.
>> That is why I put the row in current_row and do an append. Am I
>> overlooking something, or is that really the way to do this?
>>
>>
>> I am not used to writing GUI programs. (I have to learn tkinter also.)
>> What is the best way to handle potential errors? It could go wrong on
>> line 1, 4, 5, 7, 8, 9 and 10. Should I catch every exception alone, or
>> all together, or something in between?
> 
> I rewrote it like:
>      wb_in  = None
>      wb_out = None
>      try:
>          wb_out = Workbook()
>          for filepath in filepathArr:
>              current_row = []
>              wb_in       = load_workbook(filepath)
>              for cell in wb_in.active[src_row]:
>                  current_row.append(cell.value)
>              wb_out.active.append(current_row)
>              wb_in.close()
>          wb_out.save(report_start + datetime.now().strftime('%Y-%m-%d') + report_end)
>          wb_out.close()
>          messagebox.showinfo(info_str, created_report)
>      except Exception as err:
>          if wb_in:
>              wb_in.close()
>          if wb_out:

Missing ():
>              wb_close
>          messagebox.showerror(error_str,
>                               error_generate + '\n\n\n\n' + str(err))
> 
> Is it necessary to close the workbooks to circumvent a resource leak?
> Is it a problem when a workbook is closed two times? If so I need to
> make sure that this is not possible.
> 
Does Workbook support the 'with' statement?

If it does, then that's the best way of doing it.

(Untested)

     with Workbook() as wb_out:
         for filepath in filepathArr:
             current_row = []

             with load_workbook(filepath) as wb_in:
                 for cell in wb_in.active[src_row]:
                     current_row.append(cell.value)

                 wb_out.active.append(current_row)

         wb_out.save(report_start + datetime.now().strftime('%Y-%m-%d') 
+ report_end)
More information about the Python-list mailing list