current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I need to change the sheet in an excel workbook, as many times as the code runs..Suppose my python scripts runs the first time and data gets saved in sheet A, next time when some application runs my script data should be saved in sheet B.Sheet A should be as it is in that workbook.. Is it posible ? If yes ,How? Here is my code: 

#!/usr/bin/env python
import subprocess
import xlwt
process=subprocess.Popen('Test_Project.exe',stdout=subprocess.PIPE)
out,err = process.communicate()
wb=xlwt.Workbook()
sheet=wb.add_sheet('Sheet_A') #next time it should save in Sheet_B
row = 0
for line in out.split('\n'): 
    for i,wrd in enumerate(line.split()):    
        if not wrd.startswith("***"):
            print wrd   
        sheet.write(row,i,wrd)
    row=row+1

wb.save('DDS.xls')

Any help is appreciated...

share|improve this question
    
This seems to be fairly simple logic: find the last non-empty worksheet and use the next one. What are you having trouble with? –  Daniel Roseman May 22 '13 at 9:09
    
Correct me if I'm wrong: you want to prepend the data into another sheet of an existing workbook several times? –  alecxe May 22 '13 at 9:13
    
@alecxe I dont want to prepend the data.Its just that I have to read an application and store its data into an excel sheet.Then, when the application has got a different data ,I have to read it again and store into another sheet, so that I can compare both sheets later. –  Pragya May 22 '13 at 10:31
    
Yes, the key point is that it's the same workbook. In that case, the answer is: you cannot edit an existing workbook with xlwt. First, you should open it via xlrd, read the existing data, rewrite it and add another data portion to the different sheet using xlwt. –  alecxe May 22 '13 at 10:34
    
If I'm still correctly understand the problem, there are several options: use different workbooks for every new portion of data (instead of sheets) or collect the data somewhere in the database and write it to the excel sheet before comparing. –  alecxe May 22 '13 at 10:38

1 Answer 1

up vote 1 down vote

I would recommend using openpyxl. It can read and write xlsx files. If needed, you can always convert them to xls with Excel or Open/LibreOffice, assuming you have only one big file at the end.

This script creates a new Excel file if none exists and adds a new sheet every time it is run. I use the index + 1 as the sheet name (title) starting with 1. The numerical index starts at 0. You will end up with a file that has sheets named 1, 2, 3 etc. Every time you write your data into the last sheet.

import os

from openpyxl import Workbook
from openpyxl.reader.excel import load_workbook

file_name = 'test.xlsx'

if os.path.exists(file_name):
    wb = load_workbook(file_name)
    last_sheet = wb.worksheets[-1]
    index = int(last_sheet.title)
    ws = wb.create_sheet(index)
    ws.title = str(index + 1)
else:
    wb = Workbook()
    ws = wb.worksheets[0]
    ws.title = '1'
ws.cell('A2').value= 'new_value'
wb.save(file_name)
share|improve this answer
    
@Pragya Any success with this solution? –  Mike Müller Jun 4 '13 at 2:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.