Modifying Excel document using python and openpyxl

0 votes

Using the Openpyxl package, I'm attempting to delete a sheet from an Excel document, but the problematic Excel document contains links to other documents. When I access the Excel document after the application has run, I am informed that the file has been destroyed, notably because the original links that were in the Excel document have been removed.

The code I use is seen below:

def import_data(source_location, xplan_export, workbook):

    os.chdir(source_location)


    read_csv = pd.read_csv(xplan_export, encoding='latin-1')
    # print(read_csv)

    vworkbook= openpyxl.load_workbook(workbook, read_only=False)

    sheet = vworkbook['Xplan_Export']


    # clear Xplan Export sheet in  workbook of interest
    for row in sheet.iter_rows():
        for cell in row:
            cell.value = None
    
    
    vworkbook.save(workbook)

I would like to get around this to ensure that the original links remain in the output of the functions above.

Feb 16, 2023 in Others by narikkadan
• 63,720 points
801 views

1 answer to this question.

0 votes

The style will be set to Hyperlink if the excel document has links.  Here are more specifics on how to set this. Hence, both the data and the formatting need to be deleted. By inserting this additional line directly beneath the field, I was able to remove both the data (the URL text) and the hyperlink. value equals "None." Remove the data and then enter this (inside the loop).

cell.style = 'Normal'

This will correctly work and remove ALL formatting. I hope you find what you're looking for here.

answered Feb 16, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

How to create and download excel document using asp.net

First, download the Open XML Format SDK 2.0. It comes ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,720 points
2,671 views
0 votes
1 answer

Calculating the mean and std on excel file using python

Use pandas to do this: import pandas as pd df = ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
• 63,720 points
1,941 views
0 votes
1 answer

How to convert data from txt files to Excel files using python

Hi , there are few steps to ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
13,634 views
0 votes
1 answer

Openpyxl password protect excel file python

Please refer to the documentation here. Workbooks can be ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,720 points
7,430 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 4,204 views
0 votes
1 answer
0 votes
1 answer

How to detect value in change in excel using python?

You might keep a txt file with ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,010 points
521 views
0 votes
1 answer

How to split columns into two sub columns under their parent column using python in excel

Use str.split: df[['pre', 'post']] = df['Column A'].str.split(r'\s*-->\s*', expand=True) print(df) # Output ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,010 points
1,486 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP