Styjun

Are all Ringwraiths called Nazgûl in LotR?

Android Material and appcompat Manifest merger failed in react-native or ExpoKit

Causes of High CHTs

How does DC work with natural 20?

Heavily limited premature compiler translates text into excecutable python code

Intuition for the role of diffeomorphisms

How to execute a command when ALL of the players are close enough

Is it illegal to withhold someone's passport and green card in California?

Primes and SemiPrimes in Binary

Greeting with "Ho"

What does it mean to not be able to take the derivative of a function multiple times?

What is the oldest commercial MS-DOS program that can run on modern versions of Windows without third-party software?

Why is it easier to balance a non-moving bike standing up than sitting down?

Creating a histogram using custom data

Confusion over 220 and 230 volt outlets

Count All Possible Unique Combinations of Letters in a Word

King or Queen-Which piece is which?

Music theory behind A chord in the key of G

Do I have any obligations to my PhD supervisor's requests after I have graduated?

Did the CIA blow up a Siberian pipeline in 1982?

Constitutionality of U.S. Democratic Presidential Candidate's Supreme Court Suggestion

UK - Working without a contract. I resign and guy wants to sue me

Dates on degrees don’t make sense – will people care?

Should I include an appendix for inessential, yet related worldbuilding to my story?

How to edit Excel (xlsx and xlsm) in python

How do I check whether a file exists without exceptions?Calling an external command in PythonWhat are metaclasses in Python?How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?Finding the index of an item given a list containing it in PythonHow can I safely create a nested directory?Does Python have a ternary conditional operator?How to get the current time in PythonHow can I make a time delay in Python?Does Python have a string 'contains' substring method?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

-2

I am very new to Python and this is my first project in python.

What I am doing is...
1. Retrieved the data from Sql server
2. Put the data in predefined excel template (specific worksheet).
3. If is there any data in this sheet then it should be replaced and only column name should remain in the sheet.
3. Another sheet in excel template contains a Pivot representation of data from step 2.
4. I need to refresh this pivot with new data from sheet1.
5. no of row in sheet1 can be changed depends on data from database.

I am fine with Step1 but unable oto perform excel operations.

I tried openpyxl but not able to much understand of it.
https://openpyxl.readthedocs.io/en/stable/

code:

from openpyxl import load_workbook
wb2 = load_workbook('CnA_Rec.xlsx')
print (wb2.sheetnames)

rawsheet = wb2.get_sheet_by_name('RawData')

print (rawsheet.cell_range)

Error with above code:

AttributeError: 'Worksheet' object has no attribute 'cell_range'

I can access individual cell but not range.
I need to select current range and replace it will new data.

ref link: https://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.cell_range.html

Can any one point me to some online example for the same or any sample code for this.

python excel python-3.x openpyxl

share|improve this question

edited Mar 25 at 10:23

Abhash786

asked Mar 25 at 7:37

Abhash786Abhash786

4481030

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Does this help? xlsxwriter.readthedocs.io
  
  – Xenobiologist
  Mar 25 at 7:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  xlsxwriter works with new files only. It doesn't support editing existing one. Let me know if I am wrong with my understanding...
  
  – Abhash786
  Mar 25 at 8:27
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Oh yes, you are right. What about the other modules? Did you test them?
  
  – Xenobiologist
  Mar 25 at 8:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tried openpyxl. It seems promising but unable to find any example for this.
  
  – Abhash786
  Mar 25 at 8:46
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you have to use Python? Autoit is able to your tasks very easily.
  
  – Xenobiologist
  Mar 25 at 8:48
  

  
  
  
  

 | 
show 4 more comments

-2

I am very new to Python and this is my first project in python.

What I am doing is...
1. Retrieved the data from Sql server
2. Put the data in predefined excel template (specific worksheet).
3. If is there any data in this sheet then it should be replaced and only column name should remain in the sheet.
3. Another sheet in excel template contains a Pivot representation of data from step 2.
4. I need to refresh this pivot with new data from sheet1.
5. no of row in sheet1 can be changed depends on data from database.

I am fine with Step1 but unable oto perform excel operations.

I tried openpyxl but not able to much understand of it.
https://openpyxl.readthedocs.io/en/stable/

code:

from openpyxl import load_workbook
wb2 = load_workbook('CnA_Rec.xlsx')
print (wb2.sheetnames)

rawsheet = wb2.get_sheet_by_name('RawData')

print (rawsheet.cell_range)

Error with above code:

AttributeError: 'Worksheet' object has no attribute 'cell_range'

I can access individual cell but not range.
I need to select current range and replace it will new data.

ref link: https://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.cell_range.html

Can any one point me to some online example for the same or any sample code for this.

python excel python-3.x openpyxl

share|improve this question

edited Mar 25 at 10:23

Abhash786

asked Mar 25 at 7:37

Abhash786Abhash786

4481030

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Does this help? xlsxwriter.readthedocs.io
  
  – Xenobiologist
  Mar 25 at 7:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  xlsxwriter works with new files only. It doesn't support editing existing one. Let me know if I am wrong with my understanding...
  
  – Abhash786
  Mar 25 at 8:27
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Oh yes, you are right. What about the other modules? Did you test them?
  
  – Xenobiologist
  Mar 25 at 8:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tried openpyxl. It seems promising but unable to find any example for this.
  
  – Abhash786
  Mar 25 at 8:46
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you have to use Python? Autoit is able to your tasks very easily.
  
  – Xenobiologist
  Mar 25 at 8:48
  

  
  
  
  

 | 
show 4 more comments

I am very new to Python and this is my first project in python.

What I am doing is...
1. Retrieved the data from Sql server
2. Put the data in predefined excel template (specific worksheet).
3. If is there any data in this sheet then it should be replaced and only column name should remain in the sheet.
3. Another sheet in excel template contains a Pivot representation of data from step 2.
4. I need to refresh this pivot with new data from sheet1.
5. no of row in sheet1 can be changed depends on data from database.

I am fine with Step1 but unable oto perform excel operations.

I tried openpyxl but not able to much understand of it.
https://openpyxl.readthedocs.io/en/stable/

code:

from openpyxl import load_workbook
wb2 = load_workbook('CnA_Rec.xlsx')
print (wb2.sheetnames)

rawsheet = wb2.get_sheet_by_name('RawData')

print (rawsheet.cell_range)

Error with above code:

AttributeError: 'Worksheet' object has no attribute 'cell_range'

I can access individual cell but not range.
I need to select current range and replace it will new data.

ref link: https://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.cell_range.html

Can any one point me to some online example for the same or any sample code for this.

python excel python-3.x openpyxl

share|improve this question

edited Mar 25 at 10:23

Abhash786

asked Mar 25 at 7:37

Abhash786Abhash786

4481030

from openpyxl import load_workbook
wb2 = load_workbook('CnA_Rec.xlsx')
print (wb2.sheetnames)

rawsheet = wb2.get_sheet_by_name('RawData')

print (rawsheet.cell_range)

AttributeError: 'Worksheet' object has no attribute 'cell_range'

share|improve this question

edited Mar 25 at 10:23

Abhash786

asked Mar 25 at 7:37

Abhash786Abhash786

4481030

share|improve this question

edited Mar 25 at 10:23

Abhash786

asked Mar 25 at 7:37

Abhash786Abhash786

4481030

asked Mar 25 at 7:37

Abhash786Abhash786

4481030

asked Mar 25 at 7:37

Abhash786Abhash786

4481030

1 Answer
1

active

oldest

votes

0

So, then let go for it with openpyxl. Where is your problem? This is a very basic start. We can change this script during the process.

import openpyxl

wb = openpyxl.load_workbook('hello_world.xlsx')
# do magic with openpyxl here and save
ws = wb.worksheets[0]
ws.cell(row=1, column=3).value = 'Hello' # example
ws.cell(row=2, column=3).value = 'World' # example

for i in range(2,20):
 ws.cell(row=i,column=1).value = 'Row:' + str(i)

data = [ws.cell(row=i,column=1).value for i in range(1,11)]
print(data)

wb.save('hello_world.xlsx')

share|improve this answer

edited Mar 25 at 11:09

answered Mar 25 at 10:06

XenobiologistXenobiologist

1,6281814

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks... I am able to access/update individual cell. I am facing issue with cell Range. Updated question with my code also.
  
  – Abhash786
  Mar 25 at 10:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Changed the code to show a "range read"
  
  – Xenobiologist
  Mar 25 at 11:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks... this seems like what I want...let me try and will get back to you....
  
  – Abhash786
  Mar 25 at 12:00
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55333121%2fhow-to-edit-excel-xlsx-and-xlsm-in-python%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

So, then let go for it with openpyxl. Where is your problem? This is a very basic start. We can change this script during the process.

import openpyxl

wb = openpyxl.load_workbook('hello_world.xlsx')
# do magic with openpyxl here and save
ws = wb.worksheets[0]
ws.cell(row=1, column=3).value = 'Hello' # example
ws.cell(row=2, column=3).value = 'World' # example

for i in range(2,20):
 ws.cell(row=i,column=1).value = 'Row:' + str(i)

data = [ws.cell(row=i,column=1).value for i in range(1,11)]
print(data)

wb.save('hello_world.xlsx')

share|improve this answer

edited Mar 25 at 11:09

answered Mar 25 at 10:06

XenobiologistXenobiologist

1,6281814

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks... I am able to access/update individual cell. I am facing issue with cell Range. Updated question with my code also.
  
  – Abhash786
  Mar 25 at 10:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Changed the code to show a "range read"
  
  – Xenobiologist
  Mar 25 at 11:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks... this seems like what I want...let me try and will get back to you....
  
  – Abhash786
  Mar 25 at 12:00
  

  
  
  
  

add a comment | 

0

So, then let go for it with openpyxl. Where is your problem? This is a very basic start. We can change this script during the process.

import openpyxl

wb = openpyxl.load_workbook('hello_world.xlsx')
# do magic with openpyxl here and save
ws = wb.worksheets[0]
ws.cell(row=1, column=3).value = 'Hello' # example
ws.cell(row=2, column=3).value = 'World' # example

for i in range(2,20):
 ws.cell(row=i,column=1).value = 'Row:' + str(i)

data = [ws.cell(row=i,column=1).value for i in range(1,11)]
print(data)

wb.save('hello_world.xlsx')

share|improve this answer

edited Mar 25 at 11:09

answered Mar 25 at 10:06

XenobiologistXenobiologist

1,6281814

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks... I am able to access/update individual cell. I am facing issue with cell Range. Updated question with my code also.
  
  – Abhash786
  Mar 25 at 10:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Changed the code to show a "range read"
  
  – Xenobiologist
  Mar 25 at 11:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks... this seems like what I want...let me try and will get back to you....
  
  – Abhash786
  Mar 25 at 12:00
  

  
  
  
  

add a comment | 

So, then let go for it with openpyxl. Where is your problem? This is a very basic start. We can change this script during the process.

import openpyxl

wb = openpyxl.load_workbook('hello_world.xlsx')
# do magic with openpyxl here and save
ws = wb.worksheets[0]
ws.cell(row=1, column=3).value = 'Hello' # example
ws.cell(row=2, column=3).value = 'World' # example

for i in range(2,20):
 ws.cell(row=i,column=1).value = 'Row:' + str(i)

data = [ws.cell(row=i,column=1).value for i in range(1,11)]
print(data)

wb.save('hello_world.xlsx')

share|improve this answer

edited Mar 25 at 11:09

answered Mar 25 at 10:06

XenobiologistXenobiologist

1,6281814

import openpyxl

wb = openpyxl.load_workbook('hello_world.xlsx')
# do magic with openpyxl here and save
ws = wb.worksheets[0]
ws.cell(row=1, column=3).value = 'Hello' # example
ws.cell(row=2, column=3).value = 'World' # example

for i in range(2,20):
 ws.cell(row=i,column=1).value = 'Row:' + str(i)

data = [ws.cell(row=i,column=1).value for i in range(1,11)]
print(data)

wb.save('hello_world.xlsx')

share|improve this answer

edited Mar 25 at 11:09

answered Mar 25 at 10:06

XenobiologistXenobiologist

1,6281814

answered Mar 25 at 10:06

XenobiologistXenobiologist

1,6281814

answered Mar 25 at 10:06

XenobiologistXenobiologist

1,6281814