[pyxl] Adding a sheet in already existing excel file

[Udai]

Hi,

I am new to Python and this group. Sorry if repost.

I have to write a code that will save the automated regression
results in a excel sheet on daily basis. I have use the xlwt package
to create the excel sheet and save the results in excel sheet. To save
the results in excel sheet I am using dictionary. Firstly I have
stored all the values in the dictionary and then use this dictionary
to store the results in the excel sheet.

# Create workbook and worksheet
wbk = xlwt.Workbook()
sheet = wbk.add_sheet(wBookName, cell_overwrite_ok=True)

# Create a font to use with the style
font = xlwt.Font()
font.bold = True

# Set the style's font to this new one you set up
style = xlwt.XFStyle()
style.font = font

sheet.write(0,0,'Platform', style)
sheet.write(0,1,'Debug', style)
sheet.write(0,2,'Release', style)

# Sorting the dictionary keys for better reporting
sorted_keys = resultDict.keys()
sorted_keys.sort()

row = {}
counter = 2

# Generating row and column dict with values as serial numbers
for key in sorted_keys:
tmpValueList = resultDict[key]
if tmpValueList[0] in row:
continue
else:
row[tmpValueList[0]] = counter
counter += 1

coloum = {'Debug': 1, 'Release': 2}

# Writing data into excel sheet
for key in sorted_keys:
dictValues = resultDict[key]
sheet.write(row[dictValues[0]],0,dictValues[0])

sheet.write(row[dictValues[0]],coloum[dictValues[1]],dictValues[2])

logFileName = distName+'_'+projectName+'.xls'
print ("Creating {excel} file at {path}" .format (excel =
logFileName, path = os.getcwd()))
wbk.save(logFileName)
return logFileName


The problem here is we need to run this script everyday and save the
results. But the code is overwriting the old excel file and creating
new excel file everyday. Hence the old data is lost.

Can u please let me know is there any way so that instead of creating
new file everyday we can append new excel sheet to already existing
file.

Thanks,
Udai

--
You received this message because you are subscribed to the Google Groups "python-excel" group.
To post to this group, send an email to python...@googlegroups.com.
To unsubscribe from this group, send email to python-excel...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/python-excel?hl=en-GB.

[Chris Curvey]

this is becoming a FAQ.  

Short answer -- you can't add a sheet to the same Excel file midstream.  You have to 

1) Read the ENTIRE existing file into memory. (Hint:  you need a loop that reads each sheet from your existing workbook and does an add_sheet for each sheet in the existing workbook.)

2) Add your brand new sheet to to the workbook in memory.

3) Write out the workbook.

--
Part of the problem since 1966!

[Jannis Syntychakis]

i have the same problem. What i did:

Python makes every day a new excel file. The name of the file is
"echo" + date and time at that moment. On that way i get every day a
new excel sheet without overwriting.
i also have one excel file in wich i have written a macro wich copies
the data of the 'echo' file (made by python) on a new excel sheet and
then it deletes the 'echo' file.

it 's not 100% automatically. the only i have to do is to open the
"head"excel file and klik on a button.

[Chris Withers]

Chris Curvey wrote:
> this is becoming a FAQ.
>
> Short answer -- you can't add a sheet to the same Excel file midstream.
> You have to
>
> 1) Read the ENTIRE existing file into memory. (Hint: you need a loop
> that reads each sheet from your existing workbook and does an add_sheet
> for each sheet in the existing workbook.)

better hint: you want xlutils.copy...

> 2) Add your brand new sheet to to the workbook in memory.
> 3) Write out the workbook.

...and then do this with xlwt.

cheers,

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[Chris Withers]

Jannis Syntychakis wrote:
> i have the same problem. What i did:

What did xlutils.copy not do that you required?

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[Jannis Syntychakis]

because i am really new in python. and i don't understand how to use
is :S

On 22 apr, 19:07, Chris Withers <ch...@simplistix.co.uk> wrote:
> Jannis Syntychakis wrote:
> > i have the same problem. What i did:
>
> What did xlutils.copy not do that you required?
>
> Chris
>
> --
> Simplistix - Content Management, Batch Processing & Python Consulting

>              -http://www.simplistix.co.uk

[John Machin]

On 22/04/2010 9:26 PM, Udai wrote:
> Hi,
>
> I am new to Python and this group. Sorry if repost.

Did you not read at least the postings of the last few days??

>
> I have to write a code that will save the automated regression
> results in a excel sheet on daily basis.

Why not use a database?

> I have use the xlwt package
> to create the excel sheet and save the results in excel sheet. To save
> the results in excel sheet I am using dictionary. Firstly I have
> stored all the values in the dictionary and then use this dictionary
> to store the results in the excel sheet.

A dictionary seems to be overkill ... isn't your data really just a
bunch of (timestamp, platform_id, debug_or_release_flag, something_else)
tuples?

> # Create workbook and worksheet
> wbk = xlwt.Workbook()
> sheet = wbk.add_sheet(wBookName, cell_overwrite_ok=True)

(1) Shouldn't that be wSheetName?

(2) It's not very efficient to use cell_overwrite_ok=True as a bludgeon
to overcome your evident requirement to eliminate duplicates and show
only the *last* result for each platform ... while ordering platforms by
*first* appearance (!) See suggested bludgeon avoidance technique below.

> # Create a font to use with the style
> font = xlwt.Font()
> font.bold = True

Get the tutorial, read the section on easyxf

>
> # Set the style's font to this new one you set up
> style = xlwt.XFStyle()
> style.font = font
>
> sheet.write(0,0,'Platform', style)
> sheet.write(0,1,'Debug', style)
> sheet.write(0,2,'Release', style)
>
> # Sorting the dictionary keys for better reporting
> sorted_keys = resultDict.keys()
> sorted_keys.sort()
>

=== replace all of this ...

> row = {}
> counter = 2
>
> # Generating row and column dict with values as serial numbers
> for key in sorted_keys:
> tmpValueList = resultDict[key]
> if tmpValueList[0] in row:
> continue
> else:
> row[tmpValueList[0]] = counter
> counter += 1
>
> coloum = {'Debug': 1, 'Release': 2}
>
> # Writing data into excel sheet
> for key in sorted_keys:
> dictValues = resultDict[key]
> sheet.write(row[dictValues[0]],0,dictValues[0])
>
> sheet.write(row[dictValues[0]],coloum[dictValues[1]],dictValues[2])

INDENTATION ERROR

=== with this:

row = {} # map platform -> row offset
row_data = [] # capture latest info for each platform
for timestamp in sorted_keys:
values = resultDict[timestamp]
platform = values[0]
if platform not in row:
row[platform] = len(row_data)
row_data.append(values)
else:
# replace earlier info with latest info
row_data[platform] = values
column = {'Debug': 1, 'Release': 2}

# In case you are short of memory, or just want to assert
# that you know what you are doing:
del row, resultDict, sorted_keys

for row_index, values in enumerate(row_data):
sheet.write(row_index+2, 0, values[0])
sheet.write(row_index+2, column[values[1]], values[2])

> logFileName = distName+'_'+projectName+'.xls'
> print ("Creating {excel} file at {path}" .format (excel =
> logFileName, path = os.getcwd()))
> wbk.save(logFileName)
> return logFileName

> The problem here is we need to run this script everyday and save the
> results. But the code is overwriting the old excel file and creating
> new excel file everyday. Hence the old data is lost.

> Can u please let me know is there any way so that instead of creating
> new file everyday we can append new excel sheet to already existing
> file.

Short answer: no. Long answer: read previous postings on this topic and
answers by others to your question.

HTH,
John

[Chris Withers]

Jannis Syntychakis wrote:
> because i am really new in python. and i don't understand how to use
> is :S

RTFM much?

https://secure.simplistix.co.uk/svn/xlutils/tags/1.4.1/xlutils/docs/copy.txt

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting

- http://www.simplistix.co.uk

[Jannis Syntychakis]

thanks

On Apr 23, 9:13 am, Chris Withers <ch...@simplistix.co.uk> wrote:
> Jannis Syntychakis wrote:
> > because i am really new in python. and i don't understand how to use
> > is :S
>
> RTFM much?
>

> https://secure.simplistix.co.uk/svn/xlutils/tags/1.4.1/xlutils/docs/c...

>
> Chris
>
> --
> Simplistix - Content Management, Batch Processing & Python Consulting

>              -http://www.simplistix.co.uk

[Udai]

Thanks

> You received this message because you are subscribed to theGoogleGroups "python-excel" group.

[Steve Romanow]

On 4/23/2010 3:13 AM, Chris Withers wrote:
> Jannis Syntychakis wrote:
>> because i am really new in python. and i don't understand how to use
>> is :S
>
> RTFM much?
>
> https://secure.simplistix.co.uk/svn/xlutils/tags/1.4.1/xlutils/docs/copy.txt
>
>
> Chris
>

I think another point to make Jannis, dont take it personal, but the
same form 3-4 questions get asked every week. This is a programming api.

[Jannis Syntychakis]

maybe a good idea to put this question to the FAQ post.

On Apr 23, 2:21 pm, Steve Romanow <slestak...@gmail.com> wrote:
> On 4/23/2010 3:13 AM, Chris Withers wrote:> Jannis Syntychakis wrote:
> >> because i am really new in python. and i don't understand how to use
> >> is :S
>
> > RTFM much?
>

> >https://secure.simplistix.co.uk/svn/xlutils/tags/1.4.1/xlutils/docs/c...

[Steve Romanow]

On 4/23/2010 8:53 AM, Jannis Syntychakis wrote:
> maybe a good idea to put this question to the FAQ post.
>
> On Apr 23, 2:21 pm, Steve Romanow<slestak...@gmail.com> wrote:
>
>> On 4/23/2010 3:13 AM, Chris Withers wrote:> Jannis Syntychakis wrote:
>>
>>>> because i am really new in python. and i don't understand how to use
>>>> is :S
>>>>
>>
>>> RTFM much?
>>>
>>
>>> https://secure.simplistix.co.uk/svn/xlutils/tags/1.4.1/xlutils/docs/c...
>>>
>>
>>> Chris
>>>
>> I think another point to make Jannis, dont take it personal, but the
>> same form 3-4 questions get asked every week. This is a programming api.
>>
>> --
>> You received this message because you are subscribed to the Google Groups "python-excel" group.
>> To post to this group, send an email to python...@googlegroups.com.
>> To unsubscribe from this group, send email to python-excel...@googlegroups.com.
>> For more options, visit this group athttp://groups.google.com/group/python-excel?hl=en-GB.
>>
>

The ML is also searchable. It is a self maintaining FAQ.