CODEWITHSUNDEEP
pythonexcelpandasxlsxwriter
CHINTAN VADGAMA
CHINTAN VADGAMA

Reputation: 714

How to write data into multiple sheets of single workbook based on different conditions?

I am trying to categorize test cases into 4 categories:

  1. Manual-Smoke
  2. Manual-Regression
  3. Automated-Smoke
  4. Automated-Regression

I have a python code which will give me dictionary in this way:

  "ABC": {
    "id1": "name1",
    "id2": "name2",
    "id3": "name3"
  }

I have written following code using xlsxwriter but this iterates through all the sheets but it saves only last. In my excel WB, I only get one last sheet.

def createExcelSheetTC(self,testcase):
    workbook=xlsxwriter.Workbook('data.xlsx')
    self.format_excelWB(workbook)
    with xlsxwriter.Workbook('data.xlsx') as workbook:
        self.format_excelWB(workbook) ##Initialize formatting of WB
        if testcase == self.manualSmoke:
            ws_manualSmoke=workbook.add_worksheet('Manual-Smoke Cases')
            self.writeTCExcel(testcase,ws_manualSmoke)
        elif testcase == self.manualRegression:
            ws_manualRegression=workbook.add_worksheet('Manual-Regression Cases')
            self.writeTCExcel(testcase,ws_manualRegression)
        elif testcase == self.automatedSmoke:
            ws_autoSmoke=workbook.add_worksheet('Automated-Smoke Cases')
            self.writeTCExcel(testcase,ws_autoSmoke)
        elif testcase == self.automatedRegression:
            ws_autoRegression=workbook.add_worksheet('Automated-Regression Cases')
            self.writeTCExcel(testcase,ws_autoRegression)
    workbook.close()

def writeTCExcel(self,tc,worksheet):
    ##Logic to write testcase in EXCEL
    ## This is correct as per requirements


self.manualSmoke=self.get_test_cases(args*)
self.createExcelSheetTC(testcase=self.manualSmoke)

self.manualRegression=self.get_test_cases(args*)
self.createExcelSheetTC(testcase=self.manualRegression)   

self.automatedSmoke=self.get_test_cases(args*)
self.createExcelSheetTC(testcase=self.automatedSmoke)

self.automatedRegression=self.get_test_cases(args*)
self.createExcelSheetTC(testcase=self.automatedRegression)

Using the above code I get 'Automated-Regression' cases but not the other 3. It looks like it only saves at last & overwrite all other write ups.

Upvotes: 1

Views: 1285

Answers (2)

CHINTAN VADGAMA
CHINTAN VADGAMA

Reputation: 714

It got resolved by using the following method.

import xlsxwriter
d1 = {
      "ABC": {
    "key1": "val1",
    "key2": "val2",
    "key3": "val3"
  },
   "XYZ": {
    "key4": "val4",
    "key5": "val5",
    "key6": "val6"
  }
}
d2= {  "MNO": {
    "key7": "val7",
    "key8": "val8",
    "key9": "val9"
  }
}


def create_xls_writer(tc):
    workbook=xlsxwriter.Workbook('xlsxwriter.xlsx')
    print tc
    ws1=workbook.add_worksheet('abc')
    ws2=workbook.add_worksheet('xyz')

    worksheet = [ws1,ws2]

    for each_tc in tc:
        if each_tc==d1:
            writeTCEXcel(each_tc,ws1)
        elif each_tc==d2:
            writeTCEXcel(each_tc,ws2)

    workbook.close()

def writeTCEXcel(something,worksheet):
##Logic to write **Something**  in EXCEL



create_xls_writer([d1,d2])

Upvotes: 1

stovfl
stovfl

Reputation: 15513

You are overwritening your xlsx on every call to def createExcelSheetTC,so you see only the last.

move these outside def createExcelSheetTCand call it only once. 

workbook=xlsxwriter.Workbook('data.xlsx')
self.format_excelWB(workbook)
...
workbook.close()

Upvotes: 2

Related Questions