Databases
StackOverflow2013
Postsdbo
POStruggling a bit with openpyxl for Python 2.6
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POStruggling a bit with openpyxl for Python 2.6
    primarykey
    data
    text
    <p>Apologies if this has been asked elsewhere already.</p> <p>I've imported openpyxl (v.1.6.1) for Python (2.6 in this case). So far it works great, but what I'm trying to do I can't quite figure out.</p> <p>I'll list the xlsx file's structure I need to read first. It's essentially a standby roster which looks like this:</p> <p><code>A1 B1 C1 D1<br> YYYY/MM/DD System1 System2 System3<br> A2 B2 C2 D2<br> 2013/02/11 User A User B User C<br> A3 B3 C3 D3<br> 2013/02/12 User D User E User F<br> A4 B4 C4 D4<br> 2013/02/13 User G User H User I</code></p> <p>What I need to do is first scan all cells in A (except for row 1, so A2 to A-infinity), then depending on if I can find today's date in cells A2 to A-infinity, print the entire row associated with today's date as well as the system that the user is associated with (so in this case B3, C3 and D3 as well as B1, C1 and D1).</p> <p>I can paste what I have so far, but it's not much:</p> <pre><code>import openpyxl from openpyxl import load_workbook wb = load_workbook(filename = 'standby.xlsx', use_iterators = True) ws = wb.get_sheet_by_name(name = 'Sheet1') # ws is now an IterableWorksheet for row in ws.iter_rows(): # it brings a new method: iter_rows() for cell in row: print cell.internal_value </code></pre> <p>With a standard text file I'd normally I'd do something like this:</p> <pre><code>textfile = os.open('textfile', 'r') textfiler = textfile.readlines() for line in textfile: if "today's date" in line: print line </code></pre> <p>I'm just not sure how to do what I'd do with a textfile with an xlsx file using openpyxl. Can anyone please give me a hint how to go about this?</p> <p>I think what I need to do is scan everything under A (not sure how to do this), do a match for today's date (the date thing I think I can figure out), then print out the entire row (Bxxx, Cxxx, Dxxx etc.) if today's date has been found (not sure how to do this either).</p> <p>Hopefully I explained my problem OK, but if not, please let me know and I'll try again.</p> <p><strong>EDIT:</strong> Thanks to Glen, I think I'm a bit further than I was, bit still not quite there yet. Code I have so far:</p> <pre><code>import openpyxl from openpyxl import load_workbook def find_row(today, ws): for a in ws.iter_rows(): if today == a.internal_value: return (a) def main(): wb = load_workbook(filename = 'standby.xlsx', use_iterators = True) ws = wb.get_sheet_by_name(name = 'Sheet1') # ws is now an IterableWorksheet today = '2013-02-12 00:00:00' #whatever date format you're using row = find_row(today, ws) print row def test(): wb = load_workbook(filename = r'standby.xlsx') sheet_ranges = wb.get_sheet_by_name(name = 'Sheet1') print sheet_ranges.cell('A2607').value # D18 if __name__ == '__main__': main() </code></pre> <p>Error I'm stuck at now is "AttributeError: 'tuple' object has no attribute 'internal_value'" (still googling this one).</p> <p>The def find_row section looked like this previously:</p> <pre><code>def find_row(today, ws): for a in ws.rows(): if today == a.internal_value: return (a) </code></pre> <p>That served up the following: NotImplementedError: use 'iter_rows()' instead</p> <p><strong>EDIT #2:</strong> Thanks to Glen Swinfield's help and patience, I think I finally figured it out. Here's what the code looks like right now (please excuse if it looks a bit messy, turns out there are quite a lot of columns in the spreadsheet):<br> import datetime import openpyxl from openpyxl import load_workbook</p> <pre><code>def find_row(today, ws): for a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21,a22,a23,a24,a25,a26,a27,a28,a29,a30,a31,a32,a33,a34,a35,a36,a37,a38,a39,a40,a41,a42,a43,a44,a45,a46,a47,a48,a49,a50,a51,a52,a53,a54,a55,a56,a57,a58,a59,a60,a61,a62,a63,a64,a65,a66,a67,a68,a69,a70,a71,a72,a73,a74,a75,a76,a77,a78,a79,a80,a81,a82,a83,a84,a85,a86,a87,a88,a89,a90,a91,a92,a93 in ws.iter_rows(): if today == a1.internal_value: print(a1.internal_value,a2.internal_value,a3.internal_value,a4.internal_value,a5.internal_value,a6.internal_value,a7.internal_value,a8.internal_value,a9.internal_value,a10.internal_value,a11.internal_value,a12.internal_value,a13.internal_value,a14.internal_value,a15.internal_value,a16.internal_value,a17.internal_value,a18.internal_value,a19.internal_value,a20.internal_value,a21.internal_value,a22.internal_value,a23.internal_value,a24.internal_value,a25.internal_value,a26.internal_value,a27.internal_value,a28.internal_value,a29.internal_value,a30.internal_value,a31.internal_value,a32.internal_value,a33.internal_value,a34.internal_value,a35.internal_value,a36.internal_value,a37.internal_value,a38.internal_value,a39.internal_value,a40.internal_value,a41.internal_value,a42.internal_value,a43.internal_value,a44.internal_value,a45.internal_value,a46.internal_value,a47.internal_value,a48.internal_value,a49.internal_value,a50.internal_value,a51.internal_value,a52.internal_value,a53.internal_value,a54.internal_value,a55.internal_value,a56.internal_value,a57.internal_value,a58.internal_value,a59.internal_value,a60.internal_value,a61.internal_value,a62.internal_value,a63.internal_value,a64.internal_value,a65.internal_value,a66.internal_value,a67.internal_value,a68.internal_value,a69.internal_value,a70.internal_value,a71.internal_value,a72.internal_value,a73.internal_value,a74.internal_value,a75.internal_value,a76.internal_value,a77.internal_value,a78.internal_value,a79.internal_value,a80.internal_value,a81.internal_value,a82.internal_value,a83.internal_value,a84.internal_value,a85.internal_value,a86.internal_value,a87.internal_value,a88.internal_value,a89.internal_value,a90.internal_value,a91.internal_value,a92.internal_value,a93.internal_value) def main(): wb = load_workbook(filename = 'standby.xlsx', use_iterators = True) ws = wb.get_sheet_by_name(name = 'Sheet1') # ws is now an IterableWorksheet today = datetime.datetime(2013, 02, 12, 0, 0) #whatever date format you're using row = find_row(today, ws) def test(): wb = load_workbook(filename = r'standby.xlsx') sheet_ranges = wb.get_sheet_by_name(name = 'Sheet1') print sheet_ranges.cell('A2607').value # D18 if __name__ == '__main__': main() </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USkooper
    1. USkooper
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POStruggling a bit with openpyxl for Python 2.6
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POStruggling a bit with openpyxl for Python 2.6
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POStruggling a bit with openpyxl for Python 2.6
      1. This table or related slice is empty.
      1. VTDownMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload