0

My Excel file does not have a header, I am trying to update some cells in the spreadsheet then write it to another file. A Sample file:

   A          B          C          D          E
1  Title of file in text form
2  This file contains calculations that are used...
3  in verifying values...
4
5  Vre        Vim        Complex    Vl
6                        0          0.0000

Rows 1-3 contains text which are in the A column only (B-E are empty). A6 and B6 are data I'm entering from my code. C6 and D6 are formulas and will get updated when A6 and B6 data exist. Using openpyxl, I'm able to do everything I want but I get the warning:

C:\Python27\lib\site-packages\openpyxl\worksheet\header_footer.py:49: 
UserWarning: Cannot parse header or footer so it will be ignored
  warn("""Cannot parse header or footer so it will be ignored""")

My code to open the 1st excel file looks like:

wb = load_workbook(filename = tempfilename)
ws = wb.active

# Update cells with data
ws['A9'] = Calc_Vre
ws['B9'] = Calc_Vim

Writing the data to the 2nd excel file:

wb.save(myfile)

I tried using pandas, but I'm not updating the correct cells and the first line of the input excel is not written to the output excel.

import pandas as pd
idf = pd.read_excel(tempfilename, headers=False)
idf.at[5,0] = Calc_Vre

# write to 2nd excel file
odf = pd.ExcelWriter(myfile, engine='xlsxwriter')
idf.to_excel(odf,index = False);
odf.save()

The output looks like:

   A          B          C          D          E
1  This file contains calculations that are used...
2  in verifying values...
3
4  Vre        Vim        Complex    Vl
5                        0          0.0000     5.2934
6

I wanted the 5.2934 to be in cell A5, not E5 and the first line was not written to the output file. when I used 

idf = pd.read_excel(tempfilename, headers=True)

I get:

   A          B          C          D          E
1  Title of fiUnnamed:1  Unnamed:2  Unnamed:3  Unnamed:4
2  This file contains calculations that are used...

So, I either need to use openpyxl and somehow, suppress or not get the warning, or use pandas but get the correct cells and the first line back.
Any suggestions?

C. Recker
  • 21
  • 3
  • 1
    `openpyxl` isn't Excel and you can't expect it to behave exactly as Excel does. The message *cannot parse* isn't an error: it's a warning, that tells you there are things about the input file that it can't handle. You can ignore it if you choose, for example, if headers and footers don't matter to you. There is no need to "somehow suppress" the warning. Or you can fix your Excel file so that it doesn't have features `openpyxl` can't handle. If you want behaviour that is exactly what Excel would do, use `xlwings` or `win32com`. They talk to Excel via COM and get it to do the actual work. – BoarGules Jan 30 '19 at 19:10
  • 1
    It's probably worth noting that headers in XLSX files and Pandas are two different things. In XLSX files they refer to headers and footers when printed, in Pandas they refer to column headings. – Charlie Clark Jan 31 '19 at 09:40
  • @BoarGules, thank you. I used xlwings and it worked, however, the excel files that I opened and saved in python, gets open in windows. I'm using python 2.7.10. I opened the file using `wb = xw.Book(tempfilename)` and saved using `wb.save(myfile)`. Both the files I opened and saved open in windows excel. How can I prevent excel to open these files? – C. Recker Jan 31 '19 at 17:51
  • I found the answer, this kills the excel app after the file is saved. [I cannot close Excel 2016 after executing a xlwings function](https://stackoverflow.com/questions/50831402/kill-xlwings-python-process-on-workbook-close-when-using-optimized-connection). It's not the cleanest solution since the excel file appears then disappears, but it works. – C. Recker Jan 31 '19 at 22:08
  • Excel will run no matter what. That is what `xlwings` does: it starts Excel to do what you want with the file. But if you don't want to see it, start it up as invisible: `myapp = xlwings.App(visible=False, ... )` – BoarGules Feb 01 '19 at 08:28
  • Thanks again. I'm learning a lot. I added the following code `wb = xw.Workbook(tempfilename, app_visible=False) xs = wb.sheets['Sheet1'] app = xw.apps(visible=False)` which works for the new excel files that I saved from being visible `wb.save(myfile)` but my first excel file that I opened is still visible. When I moved the `app = xw.apps(visible=False)` above the 2 previous lines, I get the following error: TypeError: __call__() got an unexpected keyword argument 'visible'. – C. Recker Feb 01 '19 at 16:48

0 Answers0