Search code examples
pythonexport-to-excelxlsxwriter

How to determine last row populated for a certain column in xlsxwriter?

I want to insert 'A3' in the ws.write method call automatically by determining that 'A3' is the last row with a value in column 'A'. The method in xlxswriter below sums the values in cell A1 thru A3 and prints the sum in row 4, column A. How can I insert 'A3' automatically using xlsxriter? 

ws.write(4, 0, '=SUM(A1:A3)')
Solution

  • XlsxWriter doesn't provide the user with any tools to track the data written to the worksheet since that can be done more easily, and to the user's requirements, from within the Python program.

    If you are writing the data to the worksheet, from within a loop or similar, then you can track the maximum row number that you used and update the formula accordingly.

    Like this:

    import xlsxwriter

workbook  = xlsxwriter.Workbook('file.xlsx')
worksheet = workbook.add_worksheet()

data = [13, 24, 15]

for row_num, value in enumerate(data):
    worksheet.write(row_num, 0, value)

row_num += 1
worksheet.write(row_num, 0, '=SUM(A1:A{})'.format(row_num))

workbook.close()

    Output:

    enter image description here