I tried to create a Python script to get the value of a cell in Microsoft Excel
1. Install MSYS2
Download the latest version of MSYS2 from https://www.msys2.org/ and install it on Windows 10.
2. Install Python and openpyxl
Start "SYS2 64bit"-"MSYS2 MinGW 64-bit" from the start menu and execute the following.
# pacman -Qe
# yes | pacman -S mingw-w64-x86_64-python mingw-w64-x86_64-python-openpyxl
# pacman -Qe
3. Script creation
excellsp.py
#!/usr/bin/env python3
import sys
import getopt
import openpyxl
def usage():
print('usage: ' + sys.argv[0] + \
' [ -h -q quotation -s separator ] workbook worksheet [celladdr...]', \
file=sys.stderr)
try:
opts,argv = getopt.getopt(sys.argv[1:], "hq:s:")
except getopt.GetoptError as err:
print(err)
usage()
sys.exit(1)
separator = '\n'
quotation = ''
for opt,optarg in opts:
if opt == "-h":
usage()
print("Pass2")
sys.exit()
elif opt == '-q':
quotation = optarg
elif opt == '-s':
separator = optarg
else:
print("Pass1")
usage()
sys.exit(1)
if len(argv) < 2:
usage()
sys.exit(1)
wb = openpyxl.load_workbook(argv[0])
ws = wb[argv[1]]
del argv[:2]
separator = separator.replace('\\t', '\t').replace('\\n', '\n')
output = ''
for arg in argv:
value = str(ws[arg].value);
if quotation != '':
value = quotation + value.replace(quotation, quotation + quotation) + \
quotation;
if output != '':
output += separator
output += value
print(output)
4. Create a test book
Create a workbook "Book1.xlsx" with the following sheet "Sheet1" in Microsoft Excel.
| A | B | C | D | E |
|---|---|---|---|---|
| 1 | a1 | b1 | c1 | d1 |
| 2 | a2 | b2 | c2 | d2 |
| 3 | a3 | b3 | c3 | d"3 |
| 4 | a4 | b4 | c4 | d4 |
5. Run the script
# chmod a+x excellsp.py
# ./excellsp.py -h
usage: ./excellsp.py [ -h -q quotation -s separator ] workbook worksheet [celladdr...]
# ./excellsp.py Book1.xlsx Sheet1 A1 C2 D3 E4
a1
c2
d"3
e4
# ./excellsp.py -s '\t' Book1.xlsx Sheet1 A1 C2 D3 E4
a1 c2 d"3 e4
# ./excellsp.py -q '"' -s '\t' Book1.xlsx Sheet1 A1 C2 D3 E4
"a1" "c2" "d""3" "e4"
Recommended Posts