Write data into existing excel file and making summary table

Question

I have to write some data into existing xls file.(i should say that im working on unix and couldnt use windows)
I prefer work with python and have tried some libraries like xlwt, openpyxl, xlutils.

Its not working, cause there is some filter in my xls file. After rewriting this file filter is dissapearing. But i still need this filter.

Could some one tell me about options that i have. help, please!

Example:

from xlutils.copy import copy
from xlrd import open_workbook
from xlwt import easyxf
start_row=0
rb=open_workbook('file.xls')
r_sheet=rb.sheet_by_index(1)

wb=copy(rb)
w_sheet=wb.get_sheet(1)
for row_index in range(start_row, r_sheet.nrows):
    row=r_sheet.row_values(row_index)
    call_index=0
    for c_el in row:
        value=r_sheet.cell(row_index, call_index).value
        w_sheet.write(row_index, call_index, value)
        call_index+=1
wb.save('file.out.xls');

I also tried: import xlrd from openpyxl import Workbook import unicodedata

rb=xlrd.open_workbook('file.xls')
sheet=rb.sheet_by_index(0)
wb=Workbook()
ws1=wb.create_sheet("Results", 0)

for rownum in range(sheet.nrows):
    row=sheet.row_values(rownum)
    arr=[]
    for c_el in row:
        arr.append(c_el)
    ws1.append(arr)

ws2=wb.create_sheet("Common", 1)
sheet=rb.sheet_by_index(1)

for rownum in range(sheet.nrows):
    row=sheet.row_values(rownum)
    arr=[]
    for c_el in row:
        arr.append(c_el)

    ws2.append(arr)


ws2.auto_filter.ref=["A1:A15", "B1:B15"]
#ws['A1']=42

#ws.append([1,2,3])

wb.save('sample.xls')

The problem is still exist. Ok, ill try to find machine running on windows, but i have to admit something else: There is some rows like this:

enter image description here

Ive understood what i was doing wrong, but i still need help. First of all, i have one sheet that contains some values Second sheet contains summary table!!! If i try to copy this worksheet it did wrong.

So, the question is : how could i make summary table from first sheet?

Plz provide some code and what have you tried so far. Check here http://stackoverflow.com/help/mcve. Also check here, it may help you: http://stackoverflow.com/questions/2942889/reading-parsing-excel-xls-files-with-python — Nikos Tavoularis, Nov 21 '16 at 08:55

score 0 · Answer 1 · answered Nov 21 '16 at 09:06

0

Suppose your existing excel file has two columns (date and number). This is how you will append additional rows using openpyxl.

import openpyxl
import datetime

wb = openpyxl.load_workbook('existing_data_file.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')

a = sheet.get_highest_row()
sheet.cell(row=a,column=0).value=datetime.date.today()
sheet.cell(row=a,column=1).value=30378

wb.save('existing_data_file.xlsx')

answered Nov 21 '16 at 09:06

Mohammad Yusuf

16,554
10
50
78

openpyxl doesnt support xls. im getting an exception. – Ilya Gudov Nov 21 '16 at 10:32
You can pipe the XLS file through OpenOffice / LibreOffice in headless mode to do the conversion and then load the file in Python for processing. – Mohammad Yusuf Nov 21 '16 at 10:37
Read this: https://ask.libreoffice.org/en/question/2641/convert-to-command-line-parameter/ – Mohammad Yusuf Nov 21 '16 at 10:40
Read this one as well: http://stackoverflow.com/questions/9918646/how-to-convert-xls-to-xlsx – Mohammad Yusuf Nov 21 '16 at 10:42
it ll break filter in the book:( – Ilya Gudov Nov 21 '16 at 10:46
Ive tried as you told me: with convertation file into xlsx format and without it. It always breaks filters in the target file.:( – Ilya Gudov Nov 21 '16 at 11:38

score 0 · Answer 2 · answered Nov 21 '16 at 09:57

If you are on Windows, I would suggest you take a look at using the win32com.client approach. This allows you to interact with your spreadsheet using Excel itself. This will ensure that any existing filters, images, tables, macros etc should be preserved.

The following example opens an XLS file adds one entry and saves the whole workbook as a different XLS formatted file:

import win32com.client as win32
import os


excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(r'input.xls')
ws = wb.Worksheets(1)

# Write a value at A1
ws.Range("A1").Value = "Hello World"

excel.DisplayAlerts = False     # Allow file overwrite
wb.SaveAs(r'sample.xls', FileFormat=56)
excel.Application.Quit()

Note, make sure you add full paths to your input and output files.

Write data into existing excel file and making summary table

2 Answers2