Variable sent to formula xlsxwriter

[pip7...@gmail.com]

Hi

I'v managed to use pandas and xlsxwriter to get data to seperate worksheets in a workbook.

However, in my for loop below I have a variable called "length" which gives me the amount of rows that will be passed to each worksheet.

How can I pass that variable to a hard coded line in my code below - eg

worksheet.write_formula(1, 14, '=SUM(E2:E1000)',format)  - so, E1000 needs to look at my "length" variable.

Regards

...................

for i in dd:

    df=d

    length = len(df)

    df = df[(df.iloc[:,2] == i)] #selects unique evalid for each sheet

    df.to_excel(writer,i) # i is sheet tab name

    worksheet.set_tab_color('#FF9900')

    worksheet = writer.sheets[i]  

    worksheet.set_column('B:B', 90, None)

    worksheet.set_column('C:C',18, None)

    worksheet.freeze_panes(16, 0)

    worksheet.conditional_format('E2:E1000',  {'type':     'blanks',

                                               'format': format3})

    worksheet.conditional_format('E2:E1000',  {'type':     'cell',

                                              'criteria': '>=',

                                              'value':   0,

                                              'format':  format1})

    worksheet.conditional_format('E2:E1000',  {'type':     'cell',

                                               'criteria': '<',

                                               'value':  0,

                                               'format': format2})    

    # calculation

    worksheet.set_column('O:O',22, None)

    worksheet.write('O1', 'TOTAL_ROR_MTD', format)

    worksheet.write_formula(1, 14, '=SUM(E2:E1000)',format)  

    #

    #Hyperlink

    worksheet.set_column('R:R',18, None)

    string = 'Home'

    worksheet.write_url('O15', 'http://???????????', url_format, string)

    #

    #INSERT IMAGE

    worksheet.insert_image('O12', Z:\Development Only\Picture1.png')

    #

    #FILTERS

    worksheet.autofilter(15, 0, length, 4) 

    #

    #print(length)

    # CHART CODE

    chart = workbook.add_chart({'type': 'line'})

    chart.set_chartarea({

    'border': {'none': True},

    'fill':   {'color': 'gray'}

    })

    chart.set_plotarea({

    'border': {'color': 'red', 'width': 2, 'dash_type': 'dash'},

    'fill':   {'color': '#FFFFC2'}})

    length = len(df) 

    chart.add_series({'values': [i,0,4,length,4],'gap': 1,'line': {'color': 'red'} })

    worksheet.insert_chart('G2', chart)

    chart.set_title ({'name': 'Results of sample analysis'})

    chart.set_x_axis({'name': 'Line_Number'})

    chart.set_y_axis({'name': 'AMTD'})

    chart.set_legend({'none': True})

    print(i)

[pip7...@gmail.com]

It's ok - I've got it .

worksheet.write_formula(1, 14, '=SUM(E2:E%d)' % length,format) 

Regards

[Adrian Klaver]

On 10/23/2015 06:20 AM, pip7...@gmail.com wrote:
> It's ok - I've got it .
>
> worksheet.write_formula(1, 14, '=SUM(E2:E%d)' % length,format)

If it where me I would use the utility functions found here:

http://xlsxwriter.readthedocs.org/working_with_cell_notation.html#cell-notation

to do the Row-column <--> A1 conversions to a coordinate string.

> --
> You received this message because you are subscribed to the Google
> Groups "python-excel" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to python-excel...@googlegroups.com
> <mailto:python-excel...@googlegroups.com>.
> To post to this group, send email to python...@googlegroups.com
> <mailto:python...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/python-excel.
> For more options, visit https://groups.google.com/d/optout.


--
Adrian Klaver
adrian...@aklaver.com