はじめに
こんにちは。風助です。
今回はOpenPyXLで複数のセルをまとめて指定する方法を４つ書いていきます。
ここで紹介する方法だけがすべてではありません。応用次第でいくらでもやり方はあると思います。
なんでもできるのがPythonの売りの一つだと思っています。
何か面白い方法を思いついたらぜひコメントしてもらえると嬉しいです。
では本題に入っていきましょう。
今回はワークブック、ワークシートは以下のように定義します。
>>>import openpyxl
>>>wb = openpyxl.Workbook()
>>>ws = wb['Sheet1']ただ、このワークシートはまっさらです。今回の投稿の通りには出力されない方法もあります。出力結果は参考程度にお考え下さい。
スライスを使う
まずはスライスを使ってセルを指定する方法。
OpenPyXLでは一番基本的な方法です。
>>>cells = ws['A1':'D4']これはセル2つを指定し、範囲を指定するやり方です。
セルを四角く囲んだ時の左上と右下のセルを指定します。
以下のようにセルの座標ではなく、列や行での指定も可能です。
>>>cells2 = ws['B':'D']
>>>cells3 = ws['C']
>>>cells4 = ws[8]
>>>cells5 = ws[4:9]アルファベットで指定すると列、数字で指定すると行での指定となります。
cells2はB列目からD列目まですべて
cells3はC列目すべて
cells4は８列目すべて
cells6は４列目から９列目まですべて のセルをそれぞれ指定します。
１つのセルを指定する方法の延長になります。
シンプルなので応用が利きそうです。
ちなみにこの方法ではセルの座標が返ってきます。
セルの値のみが欲しい場合は以下のようなコードになります。
>>>for cols in cells2:
for cell in cols:
print(cell.value)複数のセルを指定しているのでそれぞれをfor文で１つのセルにまで絞る必要があります。
.iter_rows()、.iter_cols()を使う
次は.iter_rows()と.iter_cols()について。
これはセルの情報を行ごとや列ごとに取得します。
>>>cells6 = ws.iter_rows(min_row=2, min_col=3, max_row=4, max_col=8)
>>>cells7 = ws.iter_cols(min_row=2, min_col=3, max_row=4, max_col=8)cells6もcells7もどちらも指定している範囲はC2～H4で同じです。
違いは出力の時の順番です。
cells6は行の順番に出力されます。
>>>for rows in cells6:
print(rows)
(<Cell 'Sheet1'.C2>, <Cell 'Sheet1'.D2>, <Cell 'Sheet1'.E2>, <Cell 'Sheet1'.F2>…)
(<Cell 'Sheet1'.C3>, <Cell 'Sheet1'.D3>, <Cell 'Sheet1'.E3>, <Cell 'Sheet1'.F3>…)
(<Cell 'Sheet1'.C4>, <Cell 'Sheet1'.D4>, <Cell 'Sheet1'.E4>, <Cell 'Sheet1'.F4>…)こんな感じです。
cells7は列ごとに出力します。
>>>for cols in cells7:
print(cols)
(<Cell 'Sheet1'.C2>, <Cell 'Sheet1'.C3>, <Cell 'Sheet1'.C4>…)
(<Cell 'Sheet1'.D2>, <Cell 'Sheet1'.D3>, <Cell 'Sheet1'.D4>…)
(<Cell 'Sheet1'.E2>, <Cell 'Sheet1'.E3>, <Cell 'Sheet1'.E4>…)
(<Cell 'Sheet1'.F2>, <Cell 'Sheet1'.F3>, <Cell 'Sheet1'.F4>…)
(<Cell 'Sheet1'.G2>, <Cell 'Sheet1'.G3>, <Cell 'Sheet1'.G4>…)
(<Cell 'Sheet1'.H2>, <Cell 'Sheet1'.H3>, <Cell 'Sheet1'.H4>…)こんな感じ。
それとこのメソッドはminの座標を指定しないと、デフォルトでA1のセルが指定されます。
maxの座標を指定しないと、ファイルのすべてのセルが選択されるような座標が指定されます。
この「すべてのセル」というのは少しややこしいのですが、今度詳しく書いた投稿をする予定なのでぜひ心待ちにしていてください。
ちなみにこのメソッドの引数にはvalues_onlyというものがあります。
この引数にTrueを与えると、セルの座標ではなく値が返っています。
それとこのメソッドは読み取り専用モードでは使えないので注意してください。
.rows、.columnsを使う
次は.rows、.columnsです。
このメソッドはどちらもすべてのセルを指定します。
違いは行ごとに抜き出すか、列ごとに抜き出すか。
>>>for rows in ws.rows:
print(rows)
(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>, <Cell 'Sheet1'.D1>)
(<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.D2>)
(<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>, <Cell 'Sheet1'.D3>)
(<Cell 'Sheet1'.A4>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.C4>, <Cell 'Sheet1'.D4>)
(<Cell 'Sheet1'.A5>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.C5>, <Cell 'Sheet1'.D5>)
……こちらは行ごとに抜き出します。
>>>for cols in ws.columns:
print(cols)
(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.A2>, <Cell 'Sheet1'.A3>, <Cell 'Sheet1'.A4>)
(<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.B4>)
(<Cell 'Sheet1'.C1>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.C3>, <Cell 'Sheet1'.C4>)
……こちらは列ごと。
このメソッドもiter_rows、.iter_colsと同じくvalues_onlyという引数があり、読み取りモードでは使えません。
for、while文を使う
ここからはOprnPyXLのメソッドではなくPythonの機能を使って複数のセルを指定する方法になります。
>>>for row in range(5, 10):
for col in range(7, 9):
print(ws.cell(row=row, column=col))このように「１つのセルを指定する」動きを繰り返して複数のセルを指定します。
range()以外でも可能なので応用の幅は広いです。
例えばリスト。
>>>list = ['A3', 'B2', 'C8', 'D3']
>>>for cell in list:
print(ws[cell])while文では以下のようなコードが書けます。こちらも応用次第です。
>>>row = 1
>>>while row <= 10:
print(ws.cell(row=row, column=3))
row = row + 1おわりに
今回は複数のセルを選択する方法を４つ紹介しました。
ただ初めに述べた通り、今回紹介したのは私が知っているものだけであり、方法はこれだけではありません。
Pythonはいくらでも応用が利きます。いろいろと試してみてください。
それではまた次回。さようなら。
コメント
Hi, Mahmud Hasan here from HopeTech-IT Solution. We are a professional IT solution service provider based in Bangladesh. As a IT solution service provider, We have Dedicated Team members who can provide all kind of WordPress related services like WordPress Design & Development, Theme Customization, Speed Optimization, Website Migration, On-Page SEO Setup, Malware Removal etc. We also provide Graphic Design and SEO Services at competitive prices with 100% Satisfaction Guarantee.
.
All our services have 30% discount till 15th November 2021.
.
Due to the previous Covid-19 pandemic situation and the impact of the lockdown, many companies have closed down in our country. At the moment we are also struggling to survive. We hope you will try to hire us if you need such a services. Of course, we won’t let you down. You can visit our official website to know the details and price of our services also visit our portfolio section to get an idea of our expertise.
.
Our Official Website:- https://hopetechitsolution.com
.
Best Regards,
Mahmud Hasan
Marketing Executive
WhatsApp: +8801614160365
Email: support@hopetechitsolution.com
Hi, I’m Mahmud. I would like to introduce you to one of my websites where you will find all kinds of WordPress & WooCommerce related Premium Tools absolutely free. That means you don’t have to spend money to buy your necessary WordPress Tools. Usually, all of our tools are bought from official developers with unlimited licenses and then our professional developer’s team creates an activated version of the product carefully for multiple domain usages.
.
So It’s guaranteed that the Plugins or Themes weren’t contained any kind of viruses or hidden malware. Our files are completely clean and secured. So you can use these files without any hesitation. Also, you can be sure to scan any file with VirusTotal.Com before using it.
.
Website Link: https://nulled4all.com
.
Thank You
Mahmud Ghazni
Learning to create a website is definitely important, but what’s more important is to learn it the right way, simply knowing about WordPress and Elementor is not going to be enough. You need to know more about website creation framework. For that reason you have to join multiple courses of Expert Instructors. But the problem is we can’t figure out which course is best for us. So we have come up with a solution called UpgradeYourSkills.Info
.
Here you can join WordPress-related multiple courses of World’s Best Instructors and It’s completely free for everyone. Basically we collect those best paid courses from various sources like Private Torrents and Forums etc then upload it to our Super-Fast Monster Servers. So that anyone can easily download and join these courses to improve their skills.
.
There are many other websites online that share paid courses. But we are the best of them which you can understand by using our website. The process of downloading the course files from our website is very simple. Give it a try, I hope you like it and Don’t forget to bookmark our website.
.
Website Link: https://upgradeyourskills.info
.
Note. If you are not interested in this subject, Please ignore and remove this comment. Our purpose was to reach you and we did. Now the rest up to you. Thank You.
We stumbled over here coming from a different website and thought I should check things out.
I like what I see so now i’m following you. Look forward to
looking over your web page for a second time.
My family members every time say that I am killing my time here at web, except I know I am getting familiarity every day by reading
thes pleasant articles.
https://guiainformatica.net/wiki/index.php?title=Mastering_The_Way_In_Which_Of_Mortgage_Broker_Is_Just_Not_An_Accident_-_It_s_An_Art
https://zoomwiki.blitwise.com/index.php/User:Guillermo49Z
https://kraftzone.tk/w/index.php?title=Seven_Efficient_Methods_To_Get_More_Out_Of_Mortgage_Broker_Folsom_Ca
https://www.greatafricanrailway.org/index.php/User:ShavonneSkeen
http://classicalmusicmp3freedownload.com/ja/index.php?title=Stop_Losing_Time_And_Begin_Mortgage_Broker
https://brassthistle.com/wiki/8_Suggestions_For_Mortgage_Broker
Hello! This is kind of off topic but I need
some advice from an established blog. Is it tough to set up your own blog?
I’m not very techincal but I can figure things out pretty fast.
I’m thinking about setting up my own but I’m not sure where to begin.
Do you have any points or suggestions? Thank you
Greetings! This is my first visit to your blog!
We are a collection of volunteers and starting a
new initiative in a community in the same
niche. Your blog provided us beneficial information to work
on. You have done a wonderful job!
My spouse and I stumbled over here different web address and thought I
might check things out. I like what I see so i am just following you.
Look forward to looking into your web page repeatedly.
http://elektromosautok.com/iframe/?url=http%3A%2F%2Fwww.merkadobee.com%2Fuser%2Fprofile%2F162028
http://www.liuzhoudiannao.com/wp-content/themes/begin/inc/go.php?url=http://market.cumbria4u.co.uk/audio/ten-incredible-hard-money-loan-example-examples.html
Are you writing the articles in your website yourself or you outsource them?
I am a blogger and having difficulty with content.
Other bloggers told me I should use an AI content writer,
they are actually pretty good. Here is a sample article some bloggers shared
with me. Please let me know what your opinion on it and should I
go ahead and use AI – https://sites.google.com/view/best-ai-content-writing-tools/home
Thank you for sharing your thoughts. I really appreciate
your efforts and I am waiting for your next post thanks once again.
https://class.xmap.africa/events/greatest-50-tips-for-hard-money-loan-example.html
https://sensualvip.com.ar/au-pair/4-tips-on-hard-money-loan-rates-you-can-t-afford-to-overlook.html
http://billing.headouph.co.zw/tv/which-arduous-money-lenders-in-vermont-offer-the-best-funding-options.html
http://35.194.51.251/index.php?title=Six_Stable_Reasons_To_Keep_Away_From_Hard_Money_Loan_Example
https://sensualvip.com.ar/part-time/warning-indicators-on-hard-money-lenders-rates-it-is-best-to-know.html
https://www.brandoodle.adityanamansingh.com/languages/questioning-how-to-make-your-best-hard-money-lenders-rock-read-this.html
Generally I do not read article on blogs, but I wish to say that this write-up very compelled
me to try and do so! Your writing style has been surprised me.
Thanks, very great article.
https://call.ebimarketing.com/apartment/best-hard-money-lenders-and-other-products.html
https://listaus.ukkos.org/kajaani/the-hard-money-loan-rates-that-wins-clients.html
https://lastc411.com/house/hard-money-loan-requirements-and-love-how-they-re-the-identical.html
https://ad.gedamarket.com/english/hard-money-loan-rates-do-you-actually-need-it-this-can-help-you-decide.html
https://listaus.ukkos.org/kajaani/take-heed-to-your-prospects-they-will-tell-you-all-about-hard-money-loan-rates.html
https://prosite.ws/english/what-are-hard-money-loan-rates.html
I think that what you published made a bunch of sense. However, what about this?
suppose you composed a catchier title? I mean, I don’t wish to tell you how to run your website,
but suppose you added a headline to maybe get a person’s attention? I mean OpenPyXLで複数セルを指定する方法４選 | 風助のプログラミング日記 is kinda
boring. You could peek at Yahoo’s home page and watch how
they write news titles to get viewers interested. You might try adding a video or a picture
or two to grab readers excited about everything’ve got
to say. Just my opinion, it might bring your website a little bit more interesting.
https://students-rooms.com/furniture/cool-little-hard-money-loan-calculator-instrument.html
https://call.ebimarketing.com/au-pair/learn-this-to-change-the-way-you-hard-money-loan-example.html
https://sensualvip.com.ar/audio/5-best-hard-money-lenders-for-2022-1.html
http://korjobs.com/HRForms/2072835
https://a1seodirectory.com/backlink-tools-websites/why-children-love-how-do-hard-money-loans-work.html
https://classifieds.miisbotswana.com/au-pair/discover-ways-to-hard-money-loan-calculator-persuasively-in-three-straightforward-steps.html
Thanks for sharing your thoughts about Python. Regards
https://listaus.ukkos.org/mikkeli/incomes-a-six-determine-revenue-from-hard-money-loan-example.html
https://listaus.ukkos.org/kaarina/hard-money-loan-rates-without-driving-your-self-crazy.html
https://class.xmap.africa/tv/four-must-haves-before-embarking-on-hard-money-lenders-rates.html
https://just-jobs.co.uk/apartment/seven-things-everyone-knows-about-30-year-hard-money-loans-that-you-do-not.html
https://prosite.ws/friendship/how-one-can-slap-down-a-hard-money-loans-for-beginners.html
https://call.ebimarketing.com/au-pair/hard-money-lenders-near-me-cheet-sheet.html
Saved as a favorite, I like your site!
https://a1seodirectory.com/backlink-tools-websites/listen-to-your-customers-they-may-inform-you-all-about-best-hard-money-lenders.html
https://sensualvip.com.ar/apartment/hard-money-loans-for-beginners-mindset-genius-concept.html
https://yclas.allincluded.ro/it/quick-article-reveals-the-undeniable-info-about-best-hard-money-lenders-and-the-way-it-could-possibly-have-an-effect-on-you.html
https://class.xmap.africa/apartment/what-could-hard-money-lenders-rates-do-to-make-you-switch.html
https://thesence.biz/slider/3556913
https://sensualvip.com.ar/apartment/the-best-way-to-make-your-product-the-ferrari-of-hard-money-lenders-for-beginners.html
Whats up very cool website!! Man .. Excellent .. Superb ..
I will bookmark your blog and take the feeds additionally?
I am glad to search out so many useful info here within the post, we’d
like work out more techniques on this regard, thank you for sharing.
. . . . .
https://call.ebimarketing.com/events/one-of-the-best-5-examples-of-hard-money-loan-requirements.html
http://urbanexplorationwiki.com/index.php/The_Key_Of_Best_Hard_Money_Lenders
https://portpavement.com/index.php/The_Ultimate_Secret_Of_Hard_Money_Lenders_For_Beginners
https://wikipublicpolicy.org/wiki/User:Jamel06E7115
https://worldkillers.com/index.php?title=User:SummerFagan79
http://shadowaccord.nwlarpers.org/index.php?title=How_5_Tales_Will_Change_The_Way_In_Which_You_Approach_30_Year_Hard_Money_Loans
Looking tackle to reading more. good article post.Really thank you! Awesome.
The other day, while I was at work, my sister stole my iphone and tested to see if it can survive a thirty foot drop,
just so she can be a youtube sensation. My iPad is now broken and she has 83 views.
I know this is totally off topic but I had to share it with someone!
my site Healthify Boost Blood Sugar Support
Greetings! This is my first comment here so I just wanted
to give a quick shout out and say I genuinely enjoy reading your articles.
Can you suggest any other blogs/websites/forums that cover the same subjects?
Thanks a ton!
My homepage … True Fast Keto Reviews
You can pick to use the spins on either Starburst, Aloha!
Also visit my homepage Leia
What’s Taking place i am new to this, I stumbled upon this I have found It absolutely helpful and
it has helped me out loads. I hope to give a contribution & aid different customers like its aided me.
Good job.
Good day! This post couldn’t be written any better! Reading this post
reminds me of my old room mate! He always kept chatting
about this. I will forward this post to him. Pretty sure he will have a good read.
Thanks for sharing!
Feel free to visit my site; Derma ProX Reviews
I am not really good with English but I come up this very easy to understand.
protruding ! incredibly good write taking
into account at what everything could be rebuild
theirs environment
Details:
reduce to eliminate pain
I’ve been surfing online more than 3 hours today, yet I
never found any interesting article like yours.
It’s pretty worth enough for me. In my opinion, if all website owners and bloggers made good content as you did, the internet will be
much more useful than ever before.
Feel free to surf to my web blog AJ Squar CBD Gummies
I think this site has got very excellent indited subject material content.
Look into my blog; Healthify Boost Blood Sugar Balance
As I web-site possessor I believe the content matter here is rattling fantastic , appreciate it for your hard work. You should keep it up forever! Good Luck.
I have read so many articles or reviews on the topic of the blogger lovers however this
post is in fact a pleasant article, keep it
up.
My web page :: Ketorganix Keto ACV
For hottest information you have to go to see web and on internet I found this web page as a best web site for newest updates.
Here is my web blog; Ketorganix Keto
I got this web site from my buddy who shared with me about this web page and at the moment this time I am visiting this site and reading very informative articles or reviews at
this time.
สล็อตแมชชีนเป็นเลิศในต้นแบบการพนันที่ได้รับความนิยมมากที่สุดในโลก
ใช้งานง่ายรวมทั้งการจ่ายเงินสามารถเป็นกอบเป็นกำ อย่างไรก็ดี สล็อตแมชชีนก็แตกง่ายด้วยเหมือนกัน
สล็อตแมชชีนถูกวางแบบมาเพื่อให้จ่ายเงินได้หลายครั้งที่สุด ซึ่งแสดงว่ามีการเสี่ยงที่จะถูกแฮ็ก แฮ็กเกอร์สามารถใส่เหรียญปลอมและเครื่องจะจ่ายเงินให้ผู้เล่น สิ่งนี้สามารถทำเป็นซ้ำๆซากๆซึ่งนำไปสู่การสูญเสียมากไม่น้อยเลยทีเดียว
สล็อตแมชชีนยังมีคุณลักษณะในตัวที่ช่วยทำให้เล่นซ้ำได้ ถ้าผู้เล่นอยากได้หยุดเล่น ให้กดปุ่ม “หยุด” แต่ ถ้าผู้เล่นกดปุ่ม “หมุน” เครื่องก็จะเล่นถัดไป นี้สามารถนำมาซึ่งการก่อให้เกิดการสูญเสียมากแม้ผู้เล่นไม่ระวัง
สล็อตแมชชีนยังมีคุณสมบัติในตัวที่ช่วยทำให้เล่นซ้ำได้ ถ้าผู้เล่นต้องการหยุดเล่น
ให้กดปุ่ม “หยุด” อย่างไรก็ดี ถ้าผู้เล่นกดปุ่ม
“หมุน” เครื่องก็จะเล่นต่อไป นี้สามารถทำให้เกิดการสูญเสียมากมายถ้าผู้เล่นไม่ระวัง
สล็อตแมชชีนยังเสี่ยงต่อการเช็ดกลักขโมยอีกด้วย ใครสักคน
Look at my homepage :: สล็อตแตกง่าย
Some genuinely fantastic articles on this website, thanks
for contribution.
Have a look at my blog post … Healthify Boost Blood Sugar Reviews
The very crux of your writing whilst sounding agreeable at first, did not really work perfectly with me personally after some time. Someplace within the sentences you managed to make me a believer unfortunately only for a while. I however have a problem with your jumps in logic and one might do nicely to help fill in those gaps. In the event that you actually can accomplish that, I will surely be fascinated.