3

Despite the various Python Excel-manipulation libraries and resources, I am unable to find a specific solution.

Right now, I have a template Excel file where a table exists. I would like to write a Python program in order to populate this table. Is this possible with any of the existing Excel libraries? The template Excel file has a sheet with an empty table (1st screenshot). I want to programmatically populate the table with some data from elsewhere. e.g.

Initial Excel empty table

data = [("TS0001", "1.0 Administration", "Root", "vdouk", "15/09/19", 8.0, "example 1"),
        ("TS0002", "1.0 Administration", "Root", "vdouk", "16/09/19", 3.0, "example 2"),
        ("TS0003", "4.0 Simulations", "Root", "vdouk", "16/09/19", 5.0, "example 3")]

Thus, I want finally to look like the 2nd screenshot. The total entries and subtotals are computed automatically from appropriate excel cell functions (already defined). Finally, note that this table is referenced from other parts of the workbook, meaning that it is a named data source (seen in the Excel name manager), so defining a new table will cause problems elsewhere. Can someone direct me to the most appropriate solution?

Final table example

Here's the working logic.

  1. Open an existing excel file.
  2. Do not change any formatting.
  3. Do not delete any existing data.
  4. Update the excel file with new rows:
    1. Insert all new rows just below the header row.
    2. Make sure this puts new rows as part of the existing table.
  5. Save and close the excel file.

Update

Right now, I am using openpyxl. The workaround I do is to edit the template .xlsx and add empty rows to the table. Then I write as many rows as my data tuples are by using openpyxl. The table totals seem to work, however some other excel macros seem to be breaking.

Vagos Duke
  • 331
  • 2
  • 11
  • 1
    The only way to guarantee that the fidelity (of **all** features) of an existing Excel workbook is preserved is to open it with Excel itself. You can automate this through various means. If you want to stick to Python as the scripting language, then my recommendation is to use [xlwings](https://www.xlwings.org/). – John Y Sep 17 '19 at 21:21
  • Right now, I am using openpyxl. The workaround I do is to edit the template .xlsx and add 10K empty rows to the table. Then I write as many rows as my data tuples are by using openpyxl. The table totals seem to work, however some other excel macros seem to be breaking. I will have to look a bit more into it. I might as well use xlwings. I wonder if win32com interface would solve this. – Vagos Duke Sep 18 '19 at 12:36
  • 1
    If you are comfortable using win32com directly, that's a perfectly good option. xlwings uses win32com itself, when run on Windows. – John Y Sep 18 '19 at 17:10

3 Answers3

1

You may not update excel file with any existing Python modules but you can override the existing file with Openpyxl module.

With help of Openpyxl module you can read existing worksheet in memory append your new content and save it back to the same file:

from openpyxl import load_workbook
#Open an xlsx 
wb = load_workbook(filename = 'data.xlsx')
#Get the current Active Sheet
ws = wb.get_active_sheet()
#ws = wb.get_sheet_by_name("Sheet1")
# Get the row and column index in which you are going to write
ws.cell(1,1).value = 'your_value'
# save() will override existing file 
wb.save('data.xlsx')
Tomerikoo
  • 18,379
  • 16
  • 47
  • 61
Sachin
  • 219
  • 1
  • 4
0

Maybe you can use xltpl for this - A python module to generate xls/x files from a xls/x template.

Use your excel file as the template to generate a new file.

hyperzy
  • 11
  • 1
-1

You can use Python module xlsxwriter. e.g

import xlsxwriter

workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello world')
workbook.close()
powlo
  • 2,538
  • 3
  • 28
  • 38
Sachin
  • 219
  • 1
  • 4
  • 4
    XlsxWriter doesn't work with existing files, which is one of the requirements here. – jmcnamara Sep 16 '19 at 09:55
  • There are multiple python modules for handling Excel workbooks but unfortunately non of them supports update of existing workbook. One of the module is Openpyxl but it actually overwrites existing one. So you have to follow below steps, 1. Use xlrd module to read the existing file in memory 2. Read file and create a data structure in memory 3. Write it to the new excel file (with same file name ) and save it. Or use Openpyxl modules save() method to override file. – Sachin Sep 17 '19 at 06:27
  • 1
    Thank you for your suggestion @Sachin. However, the real issue here is not the save location (or overwriting). It is acceptable that the file is saved elsewhere. The problem is how to edit the Excel file in order to preserve and correctly update internal structures without breaking anything, and more specifically, the table within. – Vagos Duke Sep 17 '19 at 08:23