利用python获取excel中所有下拉选（drop down）中的值，在百度搜了一下午，都没找到合适的方法，还是google靠谱给力，很快就找到解决办法了，分享一下，供有需要的同行参考。
日期: 2020 年 5 月 22 日
Python操作Excel
Python使用openpyxl-3.0.3复制Excel的一行内容并且插入到下一行，包含公式，下拉框，示例代码如下：
insert_rows.py
Python
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 |
# coding=utf-8 # openpyxl-3.0.3 from openpyxl.reader.excel import load_workbook from openpyxl.cell.cell import TYPE_FORMULA from openpyxl.worksheet.worksheet import Worksheet from openpyxl.utils import get_column_letter from openpyxl.worksheet.cell_range import CellRange import re import copy def insert_rows(self, row_idx, cnt, above=False, copy_style=True, copy_merged_columns=True, fill_formulae=True): """Inserts new (empty) rows into worksheet at specified row index. :param row_idx: Row index specifying where to insert new rows. :param cnt: Number of rows to insert. :param above: Set True to insert rows above specified row index. :param copy_style: Set True if new rows should copy style of immediately above row. :param fill_formulae: Set True if new rows should take on formula from immediately above row, filled with references new to rows. Usage: * insert_rows(2, 10, above=True, copy_style=False) """ CELL_RE = re.compile("(?P<col>\$?[A-Z]+)(?P<row>\$?\d+)") row_idx = row_idx - 1 if above else row_idx def replace(m): row = m.group('row') prefix = "$" if row.find("$") != -1 else "" row = int(row.replace("$", "")) row += cnt if row > row_idx else 0 return m.group('col') + prefix + str(row) # First, we shift all cells down cnt rows... old_cells = set() old_fas = set() new_cells = dict() new_fas = dict() for c in self._cells.values(): old_coor = c.coordinate # Shift all references to anything below row_idx if c.data_type == TYPE_FORMULA: c.value = CELL_RE.sub( replace, c.value ) # Here, we need to properly update the formula references to reflect new row indices if old_coor in self.formula_attributes and 'ref' in self.formula_attributes[old_coor]: self.formula_attributes[old_coor]['ref'] = CELL_RE.sub( replace, self.formula_attributes[old_coor]['ref'] ) # Do the magic to set up our actual shift if c.row > row_idx: old_coor = c.coordinate old_cells.add((c.row, c.column)) c.row += cnt new_cells[(c.row, c.column)] = c if old_coor in self.formula_attributes: old_fas.add(old_coor) fa = copy.copy(self.formula_attributes[old_coor]) new_fas[c.coordinate] = fa for coor in old_cells: del self._cells[coor] self._cells.update(new_cells) for fa in old_fas: del self.formula_attributes[fa] self.formula_attributes.update(new_fas) # Next, we need to shift all the Row Dimensions below our new rows down by cnt... # CHANGED: for row in range(len(self.row_dimensions) - 1 + cnt, row_idx + cnt, -1): for row in range(list(self.row_dimensions)[-1] + cnt, row_idx + cnt, -1): new_rd = copy.copy(self.row_dimensions[row - cnt]) new_rd.index = row self.row_dimensions[row] = new_rd del self.row_dimensions[row - cnt] # Now, create our new rows, with all the pretty cells # CHANGED: row_idx += 1 new_row_idx = row_idx + 1 for row in range(new_row_idx, new_row_idx + cnt): # Create a Row Dimension for our new row new_rd = copy.copy(self.row_dimensions[row-1]) new_rd.index = row self.row_dimensions[row] = new_rd # CHANGED: for col in range(1,self.max_column): for col in range(self.max_column): col = col + 1 cell = self.cell(row=row, column=col) source = self.cell(row=row_idx, column=col) if copy_style: cell.number_format = copy.copy(source.number_format) cell.font = copy.copy(source.font) cell.alignment = copy.copy(source.alignment) cell.border = copy.copy(source.border) cell.fill = copy.copy(source.fill) if fill_formulae and TYPE_FORMULA == source.data_type : s_coor = source.coordinate if s_coor in self.formula_attributes and 'ref' not in self.formula_attributes[s_coor]: fa = copy.copy(self.formula_attributes[s_coor]) self.formula_attributes[cell.coordinate] = fa #print("Copying formula from cell %s%d to %s%d"%(col,row-1,col,row)) cell.value = re.sub( "(\$?[A-Z]{1,3}\$?)%d" % (row_idx), lambda m: m.group(1) + str(row), source.value ) cell.data_type = TYPE_FORMULA # Check for Merged Cell Ranges that need to be expanded to contain new cells for cr in self.merged_cells.ranges: min_col, min_row, max_col, max_row = cr.bounds if min_row <= row_idx and max_row > row_idx: if max_row + cnt >= CellRange.max_row.max: cr.expand(down = CellRange.max_row.max - max_row) else: cr.expand(down = cnt) elif min_row > row_idx: if max_row + cnt >= CellRange.max_row.max: cr.expand(down = CellRange.max_row.max - max_row) else: cr.expand(down = cnt) cr.shrink(top = cnt) # Merge columns of the new rows in the same way row above does if copy_merged_columns: bounds = [] for cr in self.merged_cells.ranges: if cr.max_row == cr.min_row == row_idx: bounds.append((cr.min_col, cr.max_col)) for (min_col, max_col) in bounds: for row in range(new_row_idx, new_row_idx + cnt): newCellRange = get_column_letter(min_col) + str(row) + ":" + get_column_letter(max_col) + str(row) self.merge_cells(newCellRange) # update dataValidation validations = self.data_validations.dataValidation for val in validations: for cr in val.cells: min_col, min_row, max_col, max_row = cr.bounds if min_row <= row_idx and max_row >= row_idx: if max_row + cnt >= CellRange.max_row.max: cr.expand(down = CellRange.max_row.max - max_row) else: cr.expand(down = cnt) elif min_row > row_idx: if max_row + cnt >= CellRange.max_row.max: cr.expand(down = CellRange.max_row.max - max_row) else: cr.expand(down = cnt) cr.shrink(top = cnt) # update conditional_formatting tow steps # first get all conditional_formatting need to update cond_fmts = self.conditional_formatting upd_cfs = [] for cf in cond_fmts: for cr in cf.cells: min_col, min_row, max_col, max_row = cr.bounds if min_row <= row_idx and max_row >= row_idx: upd_cfs.append(cf) break elif min_row > row_idx: upd_cfs.append(cf) break # second update conditional_formatting for cf in upd_cfs: rules = cond_fmts[cf] del cond_fmts[cf.cells] for cr in cf.cells: min_col, min_row, max_col, max_row = cr.bounds if min_row <= row_idx and max_row >= row_idx: if max_row + cnt >= CellRange.max_row.max: cr.expand(down = CellRange.max_row.max - max_row) else: cr.expand(down = cnt) elif min_row > row_idx: if max_row + cnt >= CellRange.max_row.max: cr.expand(down = CellRange.max_row.max - max_row) else: cr.expand(down = cnt) cr.shrink(top = cnt) for r in rules: cond_fmts[cf] = r Worksheet.insert_rows = insert_rows if __name__ == "__main__": # 注意，keep_vba=True打开的Excel文档，保存之后，MS Office 无法打开 wb = load_workbook(filename='example.xlsx', read_only=False, keep_vba=False, data_only=False, keep_links=True) ws = wb.active ws.insert_rows(6, 4, above=True, copy_style=True) wb.save('new_document.xlsx') wb.close() |
参考链接
- l Sample code for inserting rows in an openpyxl worksheet. This code is not supported
- Insert new row(blank row) at a specific row number.
- 插上翅膀，让Excel飞起来——xlwings（一）
- python对excel的公式支持情况概括
- Python开发 之 Python3读写Excel文件（较全）
- openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files
- python读取excel,获得下拉选中所有选项
- Python - openpyxl 读写操作Excel
- Python操作Excel插入删除行
- 使用Python中的openpyxl将行插入Excel电子表格
- xlwings Python for Excel
- Python-Excel 模块哪家强？
- snippets openpyx Sample code for inserting rows in an openpyxl worksheet. This code is not supported
- "We found a problem with some content in copy.xlsx" Openpyxl 3.0.3, open and save with no changes to file
- Cell borders lost on save
- 放弃openpyxl，拥抱xlwings（python操作xls）
- Openpyxl and Hidden/Unhidden Excel Worksheets
- 利用OpenXml SDK改变Excel样式(如单元格填充色)