I am trying to send results of a test to an Excel sheet. In all reality, it is written... poorly. For each piece of data I send to the excel sheet, I attach the style to that command, thus adding up the XF count passed 4094. For example, I have the following method that writes to the excel sheet. I want the text color of the row containing the status value "PASSED" to be green, and red for a status value of "FAILED". Also, I want all text to be bold and have a height of 240:
def writetolog(self, method, status, error, note): if status == "PASSED": col = xlwt.easyxf('font: colour green, bold 1, height 240') elif status == "FAILED": col = xlwt.easyxf('font: colour red, bold 1, height 240') timestamp = str(datetime.now().strftime("%y-%m-%d %H:%M:%S.%f"))[:-4] timesave = str(self.starttime.strftime("%y-%m-%d_%H-%M")) try: self.sheet.write(self.row, 0, status, col) self.sheet.write(self.row, 1, method, col) self.sheet.write(self.row, 2, note, col) self.sheet.write(self.row, 3, str(error), col) self.sheet.write(self.row, 4, timestamp, col) self.wkbk.save('Log/Logfile_%s.xls' % timesave) except Exception as e: print "something went wrong in the write to log method" print e finally: self.row += 1
This means that everytime I call this method (which is very frequently), it sends in a new style (only, it's just about always the same). I need a method of doing this without sending in the same format over and over again.
A similar question appeared here: <a href="https://stackoverflow.com/questions/17130516/xlwt-set-style-making-error-more-than-4094-xfs-styles" rel="nofollow">xlwt set style making error: More than 4094 XFs (styles)</a>
While it has an answer, it is for compressing the data, which is not what I am aiming to do. A comment on the answer says that you should not repeatedly call xlwt.easyxf with the same formats, so I'm trying to figure out how to do it without the constant use of it.
So, how should I write to the excel sheet with the following criteria without constantly sending a new style to excel:
- If the status value is "PASSED", the text color of the entire row is green - If the status value is "FAILED", the text color of the entire row is red - All data is bold - All data is height 240
There's due to be a way to set conditional formatting of the entire file via xlwt?Answer1:
Wanted to add this as a comment but I do not have enough reputation. I have been battling with a similar conditional formatting problem and setting the style_compression = 2 as recommended in that other question solved it for me (no, it doesn't compress the data only the styles), however I can see that even with a relatively small spreadsheet there is now a noticeable processing delay.
Otherwise, you have to define all your styles in advance (i.e. outside of the loop) which can be a bit of a hassle if there is only one formatting parameter different here and there.