{"id":2201,"date":"2024-05-26T15:57:35","date_gmt":"2024-05-26T22:57:35","guid":{"rendered":"https:\/\/gantovnik.com\/bio-tips\/?p=2201"},"modified":"2024-05-26T16:22:24","modified_gmt":"2024-05-26T23:22:24","slug":"428-python-script-to-open-excel-files-from-the-list-of-files-and-get-values-of-specific-cells-in-the-selected-sheet","status":"publish","type":"post","link":"https:\/\/gantovnik.com\/bio-tips\/2024\/05\/428-python-script-to-open-excel-files-from-the-list-of-files-and-get-values-of-specific-cells-in-the-selected-sheet\/","title":{"rendered":"#428 Python script to open Excel files from the list of files and get values of specific cells in the selected sheet"},"content":{"rendered":"<pre class=\"brush: python; title: ; notranslate\" title=\"\">\r\nimport xlwings as xw\r\n\r\npath = r&quot;D:\\projects\\wordpress\\ex428\\list_of_files.txt&quot;\r\n\r\nwith open(path, &#039;r&#039;) as fp:\r\n    filepaths = fp.readlines()\r\n\r\nsheet=&#039;Flight&#039;\r\nfor filepath in filepaths:\r\n    filepath=filepath.strip()\r\n    file = filepath\r\n    wb=xw.Book(file)\r\n    ws = wb.sheets&#x5B;sheet]\r\n    v1 = ws.range(&quot;F6&quot;).value\r\n    v2 = ws.range(&quot;F7&quot;).value\r\n    v3 = ws.range(&quot;F8&quot;).value\r\n    v4 = ws.range(&quot;F9&quot;).value\r\n    wb.app.quit()\r\n    vmax=max(v1,v2,v3,v4)\r\n    print(file)\r\n    print(&quot;vmax&quot;, vmax)\r\n<\/pre>\n<p>Output:<\/p>\n<pre class=\"brush: python; title: ; notranslate\" title=\"\">\r\nD:\\projects\\wordpress\\ex428\\ex428.xlsx\r\nvmax 16.0\r\nD:\\projects\\wordpress\\ex428\\ex428_1.xlsx\r\nvmax 16.0\r\nD:\\projects\\wordpress\\ex428\\ex428_2.xlsx\r\nvmax 16.0\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>import xlwings as xw path = r&quot;D:\\projects\\wordpress\\ex428\\list_of_files.txt&quot; with open(path, &#039;r&#039;) as fp: filepaths = fp.readlines() sheet=&#039;Flight&#039; for filepath in filepaths: filepath=filepath.strip() file = filepath wb=xw.Book(file) ws = wb.sheets&#x5B;sheet] v1 = ws.range(&quot;F6&quot;).value v2 = ws.range(&quot;F7&quot;).value v3 = ws.range(&quot;F8&quot;).value v4 = ws.range(&quot;F9&quot;).value wb.app.quit() vmax=max(v1,v2,v3,v4) print(file) print(&quot;vmax&quot;, vmax) Output: D:\\projects\\wordpress\\ex428\\ex428.xlsx vmax 16.0 D:\\projects\\wordpress\\ex428\\ex428_1.xlsx vmax 16.0 D:\\projects\\wordpress\\ex428\\ex428_2.xlsx vmax 16.0<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"nf_dc_page":"","_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","_lmt_disableupdate":"yes","_lmt_disable":"","jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[107,2],"tags":[108,3],"class_list":["post-2201","post","type-post","status-publish","format-standard","hentry","category-excel","category-python","tag-excel","tag-python"],"modified_by":"gantovnik","jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8bH0k-zv","jetpack_likes_enabled":true,"jetpack-related-posts":[{"id":203,"url":"https:\/\/gantovnik.com\/bio-tips\/2019\/01\/laplace-equation-2d\/","url_meta":{"origin":2201,"position":0},"title":"Laplace equation (2D)","author":"gantovnik","date":"2019-01-10","format":false,"excerpt":"import os import numpy as np import matplotlib.pyplot as plt import scipy.sparse as sp import matplotlib as mpl import scipy.sparse.linalg os.chdir(r'D:\\projects\\wordpress\\ex42') os.getcwd() N = 100 u0_t, u0_b = 5, -5 u0_l, u0_r = 3, -1 dx = 1. \/ (N+1) A_1d = (sp.eye(N, k=-1) + sp.eye(N, k=1) - 4 *\u2026","rel":"","context":"In &quot;python&quot;","block_context":{"text":"python","link":"https:\/\/gantovnik.com\/bio-tips\/category\/python\/"},"img":{"alt_text":"example42","src":"https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2019\/01\/example42.png?resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2019\/01\/example42.png?resize=350%2C200 1x, https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2019\/01\/example42.png?resize=525%2C300 1.5x"},"classes":[]},{"id":1770,"url":"https:\/\/gantovnik.com\/bio-tips\/2023\/01\/337-minimization-on-an-2d-quadratic-problem-using-the-gradient-descent-method-in-python\/","url_meta":{"origin":2201,"position":1},"title":"#337 Minimization on an 2D quadratic problem using the gradient descent method in python","author":"gantovnik","date":"2023-01-15","format":false,"excerpt":"ex337.py [code language=\"python\"] import numpy as np import matplotlib.pyplot as plt def get_next_point(a0, b0, gamma): # Get the next point $(a_1, b_1)$ starting from the current point $(a_0,b_0)$ # using exact line search. The close-form of $t_{exact}$ is # $\\dfrac{a_0^2+\\gamma^2 b_0^2}{a_0^2+\\gamma^3 b_0^2}$. r = gamma t = (a0**2 + r**2\u2026","rel":"","context":"In &quot;optimization&quot;","block_context":{"text":"optimization","link":"https:\/\/gantovnik.com\/bio-tips\/category\/optimization\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2023\/01\/ex337.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2023\/01\/ex337.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2023\/01\/ex337.png?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":579,"url":"https:\/\/gantovnik.com\/bio-tips\/2020\/06\/84\/","url_meta":{"origin":2201,"position":2},"title":"#84 Volcano plot for gene expression using R","author":"gantovnik","date":"2020-06-15","format":false,"excerpt":"[code language=\"python\"] library(\"readxl\") library(\"ggplot2\") #Best plots library(\"ggrepel\") #Avoid overlapping labels library(\"dplyr\") #set working directory where all files are located getwd() setwd(\"D:\\\\projects\\\\ex89\") getwd() df <- read_excel(\"data.xlsx\") i=3 k_array = c(2,4,6) label_array = c(\"DC\",\"SC\",\"PC\") k <- k_array[i] label <- label_array[i] df = df[c(1,k,k+1)] colnames(df) <- c(\"gene\",\"log_fc\",\"pvalue\") df <- df[- grep(\"NA\", df$gene),] #\u2026","rel":"","context":"In &quot;bioinformatics&quot;","block_context":{"text":"bioinformatics","link":"https:\/\/gantovnik.com\/bio-tips\/category\/bioinformatics\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2020\/06\/Volcanoplot_DC.jpeg?resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2020\/06\/Volcanoplot_DC.jpeg?resize=350%2C200 1x, https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2020\/06\/Volcanoplot_DC.jpeg?resize=525%2C300 1.5x, https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2020\/06\/Volcanoplot_DC.jpeg?resize=700%2C400 2x, https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2020\/06\/Volcanoplot_DC.jpeg?resize=1050%2C600 3x, https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2020\/06\/Volcanoplot_DC.jpeg?resize=1400%2C800 4x"},"classes":[]},{"id":10320,"url":"https:\/\/gantovnik.com\/bio-tips\/2025\/08\/462-sorting-a-dictionary-by-key-or-value-in-python\/","url_meta":{"origin":2201,"position":3},"title":"#462 Sorting a Dictionary by Key or Value in Python","author":"gantovnik","date":"2025-08-24","format":false,"excerpt":"In Python, dictionaries are unordered collections of key-value pairs. Sometimes, we need to sort a dictionary either by its keys or by its values. This can be done easily using the built-in sorted() function. # Example dictionary d = {'a': 3, 'c': 1, 'b': 2} # Sort dictionary items by\u2026","rel":"","context":"In &quot;python&quot;","block_context":{"text":"python","link":"https:\/\/gantovnik.com\/bio-tips\/category\/python\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2219,"url":"https:\/\/gantovnik.com\/bio-tips\/2024\/06\/431-hypermesh-python-script-to-create-nodes-using-coordinates-from-csv-file\/","url_meta":{"origin":2201,"position":4},"title":"#431 HyperMesh python script to create nodes using coordinates from csv file","author":"gantovnik","date":"2024-06-24","format":false,"excerpt":"Finally, I read how to write a script for HyperMesh in python. In this example, the script reads the coordinates of points from a cvs file. [code language=\"python\"] import os import hm import hm.entities as e from hwx import gui import csv model=hm.Model() os.chdir(r\"D:\\projects\\wordpress\\ex431\") with open('ex431_coordinates.csv', mode ='r') as file:\u2026","rel":"","context":"In &quot;HyperMesh&quot;","block_context":{"text":"HyperMesh","link":"https:\/\/gantovnik.com\/bio-tips\/category\/hypermesh\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2024\/06\/ex431.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1774,"url":"https:\/\/gantovnik.com\/bio-tips\/2023\/01\/338-minimization-using-newtons-method-on-the-non-quadratic-problem-in-python\/","url_meta":{"origin":2201,"position":5},"title":"#338 Minimization using Newton\u2019s method on the non-quadratic problem in python","author":"gantovnik","date":"2023-01-16","format":false,"excerpt":"ex338.py [code language=\"python\"] import numpy as np import math import matplotlib.pyplot as plt import warnings warnings.filterwarnings('ignore') def my_f(x): #$f(x_1, x_2) = e^{x_1+3x_2-0.1}+e^{x_1-3x_2-0.1}+e^{-x_1-0.1}$ x1 = x[0, 0] x2 = x[1, 0] return math.exp(x1+3*x2-0.1)+math.exp(x1-3*x2-0.1)+math.exp(-x1-0.1) def my_gradient_f(x): #$\\nabla f(x_1, x_2)$ x1 = x[0, 0] x2 = x[1, 0] gradient_1=1*math.exp(x1+3*x2-0.1)+1*math.exp(x1-3*x2-0.1)-math.exp(-x1-0.1) gradient_2=3*math.exp(x1+3*x2-0.1)-3*math.exp(x1-3*x2-0.1) return np.array([[gradient_1], [gradient_2]])\u2026","rel":"","context":"In &quot;optimization&quot;","block_context":{"text":"optimization","link":"https:\/\/gantovnik.com\/bio-tips\/category\/optimization\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2023\/01\/ex338.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2023\/01\/ex338.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2023\/01\/ex338.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2023\/01\/ex338.png?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2023\/01\/ex338.png?resize=1050%2C600&ssl=1 3x, https:\/\/i0.wp.com\/gantovnik.com\/bio-tips\/wp-content\/uploads\/2023\/01\/ex338.png?resize=1400%2C800&ssl=1 4x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/posts\/2201","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/comments?post=2201"}],"version-history":[{"count":1,"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/posts\/2201\/revisions"}],"predecessor-version":[{"id":2202,"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/posts\/2201\/revisions\/2202"}],"wp:attachment":[{"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/media?parent=2201"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/categories?post=2201"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/tags?post=2201"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}