I tried to find out how to streamline the work flow with Excel x Python ②

Purpose of this time

I tried to build a Python program that takes in Excel, edits it, and outputs Excel and CSV files.

Program flow

① Import sample.xlsx ② Edit ③ Output sample2.xlsx and sample.csv

The program is studded with comments, so please check that.

After that, it would be perfect if we could make the work more efficient by applying this and letting Python do the work.

Excel to import (sample.xlsx)

キャプチャ.JPG

program

file_edit.py


import pathlib
import openpyxl
import csv

#Get excel file
wb = openpyxl.load_workbook("./data/sample.xlsx")

#Get sheet "Name: Sheet1"
ws = wb["Sheet1"]

#Output data in cell A1 * Pattern 1
print(ws["A1"].value)  #Get by specifying cell A1

#Output data in cell A1 * Pattern 2
row1 = ws[1]           #Get the first line of sheet "Name: Sheet1"
print(row1[0].value)   #Get the 1st row and 1st column ⇒ As a result, cell A1

#Output all data
print("All data>")
for row in ws.rows:
	for cell in row:
	    print(cell.value)

#Rewrite data in cell A1
ws["A1"].value = "★ A1"

#Save the loaded Excel with a different name
#* In case of overwriting, specify the read file path
wb.save("./data/sample2.xlsx")

#Output CSV file
with open("./data/csvsample.csv", "w", encoding="utf_8_sig") as fp:
    writer = csv.writer(fp, lineterminator="\n")
    for row in ws.rows:
        writer.writerow([col.value + "csv" for col in row])

Execution result

キャプチャ.JPG

Output file (sample2.xlsx)

キャプチャ.JPG

Output file (sample.csv)

キャプチャ.JPG

Recommended Posts

I tried to find out how to streamline the work flow with Excel x Python ②
I tried to find out how to streamline the work flow with Excel x Python ④
I tried to find out how to streamline the work flow with Excel x Python ⑤
I tried to find out how to streamline the work flow with Excel x Python ①
I tried to find out how to streamline the work flow with Excel x Python ③
I tried to find out how to streamline the work flow with Excel × Python, my article summary ★
I tried to find the entropy of the image with python
I tried to simulate how the infection spreads with Python
I tried to find out if ReDoS is possible with Python
I tried to improve the efficiency of daily work with Python
Excel X Python The fastest way to work
I tried to streamline the standard role of new employees with Python
I tried to touch the CSV file with Python
I tried to solve the soma cube with python
I tried to solve the problem with Python Vol.1
I tried to find out the outline about Big Gorilla
I tried to find the average of the sequence with TensorFlow
I tried to put out the frequent word ranking of LINE talk with Python
I tried to divide the file into folders with Python
python beginners tried to find out
The 15th offline real-time I tried to solve the problem of how to write with python
How to write offline real time I tried to solve the problem of F02 with Python
I tried to solve the ant book beginner's edition with python
How to work with BigQuery in Python
I tried to find out the difference between A + = B and A = A + B in Python, so make a note
A story that didn't work when I tried to log in with the Python requests module
[Python] I tried to visualize the night on the Galactic Railroad with WordCloud!
I tried to refer to the fun rock-paper-scissors poi for beginners with Python
How to write offline real time I tried to solve E11 with python
Mayungo's Python Learning Episode 2: I tried to put out characters with variables
I tried to get the authentication code of Qiita API with Python.
I tried with the top 100 PyPI packages> I tried to graph the packages installed on Python
I tried to get the movie information of TMDb API with Python
How to write offline real time I tried to solve E12 with python
I tried "smoothing" the image with Python + OpenCV
I tried "differentiating" the image with Python + OpenCV
I tried to save the data with discord
[Python] How to read excel file with pandas
I tried to get CloudWatch data with Python
I tried to output LLVM IR with Python
I tried "binarizing" the image with Python + OpenCV
[Algorithm x Python] How to use the list
I tried to automate sushi making with python
I tried to open the latest data of the Excel file managed by date in the folder with Python
I tried to output the rpm list of SSH login destination to an Excel sheet with Python + openpyxl.
[Circuit x Python] How to find the transfer function of a circuit using Lcapy
I tried to find out as much as possible about the GIL that you should know if you are doing parallel processing with Python
When I tried to create a virtual environment with Python, it didn't work
I tried to easily visualize the tweets of JAWS DAYS 2017 with Python + ELK
I tried to automatically send the literature of the new coronavirus to LINE with Python
[Python & SQLite] I tried to analyze the expected value of a race with horses in the 1x win range ①
I tried to explain how to get the article content with MediaWiki API in an easy-to-understand manner with examples (Python 3)
I tried to graph the packages installed in Python
I tried to summarize how to use matplotlib of python
[Introduction to Python] How to iterate with the range function?
I tried to implement Minesweeper on terminal with python
I tried to get started with blender python script_Part 01
I tried to draw a route map with Python
[Python] How to specify the download location with youtube-dl
I tried to get started with blender python script_Part 02
I tried to implement an artificial perceptron with python