Excel removes cells with the error message "unreadable content"

Issue #830 closed
Tekkno created an issue

Hi,

I am trying to edit cells in a loop. One of these cells contains '=HYPERLINK("http://url";"Link")'.

   for row in ws.iter_rows('F2:F150'):
        for cell in row:
            if cell.value:
                value = '=HYPERLINK("http://google.de";"google")'
                cell.value = value

When I try to open the worksheet, excel just throws an not meaningful error message.

Comments (12)

  1. Tekkno reporter

    The whole(shortened code):

    class Crawler(threading.Thread):
        def __init__(self, url, config):
            threading.Thread.__init__(self)
            self.url = url
            self.config = config
    
        def run(self):
            source = requests.get(self.url, verify='WEB_CA.crt')
            soup = BeautifulSoup(source.text, "lxml")
            data = soup.find_all("td")
            title = soup.find_all("title")
            date_regexp = '\d{2}\.\d{2}\.\d{4}'
            edit_line = re.findall(self.config['DEFAULT']['edit_regexp'], str(data))
            publish_line = re.findall(self.config['DEFAULT']['publish_regexp'],str(data))
            create_line = re.findall(self.config['DEFAULT']['create_regexp'],str(data))
            creator = re.findall(self.config['DEFAULT']['creator_regexp'],str(data))
            unapproved = re.findall(self.config['DEFAULT']['unapproved_regexp'],str(data))
            document_status = re.findall(self.config['DEFAULT']['status_regexp'],str(data))
            title = re.findall(self.config['DEFAULT']['title_regexp'],str(title))
            check_flag = 0
            self.result = []
    
    
            self.summary =  [create_date,edit_date,publish_date,creator,status,hyper_url]
    
            if check_flag == 2:
                revision_check = self.check_time(publish_date, edit_date)
                if revision_check == True:
                    self.result = [create_date, edit_date, publish_date,\
                    creator,status,hyper_url]
                    return self.result, self.summary
            elif check_flag == 3:
                self.result = [create_date, edit_date, publish_date,\
                creator,status,hyper_url]
                return self.result, hyper_url
    
        def check_time(self, publish_date, edit_date):
            publish_date = publish_date.split('.')
            edit_date = edit_date.split('.')
            if publish_date < edit_date:
                return True
            else:
                return False
        def create_hype(self, url, title):
            hyperlink = '"'+str(url)+'"'+';'+'"'+str(title[0])+'"'
            #hyperlink = '=HYPERLINK("'+str(url)+'";"'+str(title[0])+'")'
            #hyperlink = "'=HYPERLINK("'+str('+url+')+'";"'+'+title[0]+'+'")'"
            #hyperlink = title[0]
            #hyperlink = str(url)
            return hyperlink
    
    
    def main():
        start_time = datetime.now()
        today_date = date.today()
        result_file = 'iso_review_'+str(today_date)+'.xlsx'
        config = configparser.ConfigParser()
        config.read('config.ini')
        parser = argparse.ArgumentParser()
        parser.add_argument("-u", "-U", "--url", help="URL")
        threads = []
        results = []
        summary = []
        input_file = ('pages.txt')
        wb = Workbook()
        ws = wb.active
        ws.title = "Approvement required"
        ws2 = wb.create_sheet("Summary")
        header = ['Creation Date', 'Edit Date', 'Approval Date',\
        'Autor','Status','URL']
        ws.append(header)
        ws2.append(header)
        f = open(input_file, 'r')
        #f = [sys.argv[1]]
        for url in f:
            url = url[:-1]
            new_thread = Crawler(url, config)
            threads.append(new_thread)
            new_thread.daemon = True
            new_thread.start()
        for t in threads:
            t.join()
            if t.result:
                results.append(t.result)
            if t.summary:
                summary.append(t.summary)
        for result in results:
            ws.append(result)
        for sumline in summary:
            ws2.append(sumline)
        f.close()
        header_style = Font(bold=True)
        ws.auto_filter.ref = "A1:F1"
        ws2.auto_filter.ref = "A1:F1"
        """
        for row in ws.iter_rows('F2:F150'):
            for cell in row:
                if cell.value:
                    #value = "=HYPERLINK("+str(cell.value)+")"
                    value = '=HYPERLINK("http://google.de";"google")'
                    cell.value = value
        """
        ws['F2'].value = '=HYPERLINK("http://google.de";"google")'
        #for te in test2:
        #    print(te)
        wb.save(result_file)
        #sendMail(result_file)
        end_time = datetime.now()
        run_time = end_time - start_time
        print("Runtime: " + str(run_time))
    
  2. CharlieC

    Well, Excel's braindead approach to localisation is really at fault. You can also, of course, just set hyperlinks for the cells, though I don't really know the difference.

  3. Tekkno reporter
    def create_hype(self, url, title):
        hyperlink = '=HYPERLINK("'+url+'","'+title[0]+'")'
        return hyperlink
    

    this works perfectly for me! :)

  4. Tekkno reporter

    This won't work for me because I get the data in another thread and append the data to a list.

  5. Log in to comment