Python Forum
Create a report using excel
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Create a report using excel
#1
I created a GUI to test some device, now i want to generate a report using excel sheet.
I created an excel sheet but always it overwrite on the present excel sheet. But my worries is i want to use same excel sheet and append the data.

If any body knows than plz helps how to overcome from these issue.
Reply
#2
How did you create the sheet? Show us your code.
I'm guessing you didn't open the file correctly.

If you do
fo= open("sheet", 'w')
it overwrites

but if you do
fo = open("sheet", 'a')
it appends to the end.

If you do
fo = open("sheet", 'a+')
it appends but if the file does not exist it will create it
Reply
#3
The code is mentioned below
    def out(self):
        global part,part2,part3,part4,TEST_PASS,TEST_FAIL,output1,output2,output3,output4,serial_no1,serial_no2,serial_no3,serial_no4
        output1 = self.clicked1()
        output2 = self.clicked2()
        output3 = self.clicked3()
        output4 = self.clicked4()
        showtime = strftime("%Y-%m-%d %H:%M:%S")
        serial_no1 = part.get()
        serial_no2 = part2.get()
        serial_no3 = part3.get()
        serial_no4 = part4.get()
        todays_date = str(datetime.datetime.now().strftime("%Y%m%d%H%M"))
        workbook = xlsxwriter.Workbook('Automation_Test_Report'+ todays_date + '.xlsx')
        worksheet = workbook.add_worksheet()
        bold = workbook.add_format({'bold': True})

        cell_format1 = workbook.add_format()
        cell_format1.set_pattern(1)  # This is optional when using a solid fill.
        cell_format1.set_bg_color('#00FF00')

        cell_format2 = workbook.add_format()
        cell_format2.set_pattern(1)  # This is optional when using a solid fill.
        cell_format2.set_bg_color('#FF0000')

        cell_format3 = workbook.add_format()
        cell_format3.set_pattern(1)  # This is optional when using a solid fill.
        cell_format3.set_bg_color('#FFFF00')

        cell_format4 = workbook.add_format()
        cell_format4.set_align('center')

        header_format = workbook.add_format({
            'bold': True,
            'text_wrap': True,
            'align': 'Center',
            'fg_color': '#D7E4BC',
            'border': 1})

        merge_format = workbook.add_format({
            'bold': 1,
            'border': 1,
            'align': 'center',
            'valign': 'vcenter',
            'fg_color': '#D7E4BC'})

        merge_format1 = workbook.add_format({
            'bold': 1,
            'border': 1,
            'align': 'center',
            'valign': 'vcenter',
            'fg_color': '#FF6600'})

        worksheet.set_column('A:B', 15)
        worksheet.set_column('C:D', 15)
        worksheet.set_column('E:F', 15)
        worksheet.set_column('G:H', 15)
        worksheet.set_column('I:J', 15)
        worksheet.set_column('K:L', 15)
        worksheet.set_column('M:N', 15)
        worksheet.merge_range('B1:N1', 'AUTOMATION TESTING - ITOM', merge_format1)
        worksheet.write('B3', 'DATE & TIME', bold)
        worksheet.write('C3', showtime)
        worksheet.write('B5', 'SERIAL NO', header_format)
        worksheet.merge_range('C5:D5', 'TEST CASE-1', merge_format)
        worksheet.merge_range('E5:F5', 'TEST CASE-2', merge_format)
        worksheet.merge_range('G5:H5', 'TEST CASE-3', merge_format)
        worksheet.merge_range('I5:J5', 'TEST CASE-4', merge_format)
        worksheet.merge_range('K5:L5', 'TEST CASE-5', merge_format)
        worksheet.merge_range('M5:N5', 'TEST CASE-6', merge_format)
        worksheet.write('B6', '', header_format)
        worksheet.write('C6', 'Value', header_format)
        worksheet.write('D6', 'Relay', header_format)
        worksheet.write('E6', 'Value', header_format)
        worksheet.write('F6', 'Analogue Output', header_format)
        worksheet.write('G6', 'Value', header_format)
        worksheet.write('H6', 'LED Testing', header_format)
        worksheet.write('I6', 'Value', header_format)
        worksheet.write('J6', 'Bluetooth',header_format)
        worksheet.write('K6', 'Value', header_format)
        worksheet.write('L6', 'Room Temperature', header_format)
        worksheet.write('M6', 'Value', header_format)
        worksheet.write('N6', 'Humidity', header_format)
        
        output_result=(
            [serial_no1,ch_data1,output1],
            [serial_no2,ch_data2,output2],
            [serial_no3,ch_data3,output3],
            [serial_no4,ch_data4,output4]
        )

        row = 7
        column = 1
        i = 0
        j = 0

        for i in range(4):
            for j in range(3):
                worksheet.write(row, column, output_result[i][j])
                #worksheet.write(row, col+1, Value)
                #worksheet.write(row, col+2, Relay)
                j=j+1
                column = column + 1
            row = row + 1
            column = 1
            j = 0
            i = i +1
        workbook.close()
This is my code where i'm creating a file using xlsxwriter. My worries is how to append the data in next row in same above file. One more thing due to date and time always it create multiple file for me.
Can you plz suggest how to create an excel file and append data in same file one by one
Reply
#4
I have not used xlsxwriter but looking at this https://xlsxwriter.readthedocs.io/tutorial01.html
I see a note
Quote:Note

XlsxWriter can only create new files. It cannot read or modify existing files.
Maybe this is your problem.
Reply


Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020