Hide keyboard shortcuts

Hot-keys on this page

r m x p   toggle line displays

j k   next/prev highlighted chunk

0   (zero) top of page

1   (one) first highlighted chunk

1# Copyright (c) 2010-2020 openpyxl 

2 

3from copy import copy 

4 

5from openpyxl.descriptors import Strict 

6from openpyxl.descriptors import MinMax, Sequence 

7from openpyxl.descriptors.serialisable import Serialisable 

8 

9from openpyxl.utils import ( 

10 range_boundaries, 

11 range_to_tuple, 

12 get_column_letter, 

13 quote_sheetname, 

14) 

15 

16 

17class CellRange(Serialisable): 

18 """ 

19 Represents a range in a sheet: title and coordinates. 

20 

21 This object is used to perform operations on ranges, like: 

22 

23 - shift, expand or shrink 

24 - union/intersection with another sheet range, 

25 

26 We can check whether a range is: 

27 

28 - equal or not equal to another, 

29 - disjoint of another, 

30 - contained in another. 

31 

32 We can get: 

33 

34 - the size of a range. 

35 - the range bounds (vertices) 

36 - the coordinates, 

37 - the string representation, 

38 

39 """ 

40 

41 min_col = MinMax(min=1, max=18278, expected_type=int) 

42 min_row = MinMax(min=1, max=1048576, expected_type=int) 

43 max_col = MinMax(min=1, max=18278, expected_type=int) 

44 max_row = MinMax(min=1, max=1048576, expected_type=int) 

45 

46 

47 def __init__(self, range_string=None, min_col=None, min_row=None, 

48 max_col=None, max_row=None, title=None): 

49 if range_string is not None: 

50 if "!" in range_string: 

51 title, (min_col, min_row, max_col, max_row) = range_to_tuple(range_string) 

52 else: 

53 min_col, min_row, max_col, max_row = range_boundaries(range_string) 

54 

55 self.min_col = min_col 

56 self.min_row = min_row 

57 self.max_col = max_col 

58 self.max_row = max_row 

59 self.title = title 

60 

61 if min_col > max_col: 

62 fmt = "{max_col} must be greater than {min_col}" 

63 raise ValueError(fmt.format(min_col=min_col, max_col=max_col)) 

64 if min_row > max_row: 

65 fmt = "{max_row} must be greater than {min_row}" 

66 raise ValueError(fmt.format(min_row=min_row, max_row=max_row)) 

67 

68 

69 @property 

70 def bounds(self): 

71 """ 

72 Vertices of the range as a tuple 

73 """ 

74 return self.min_col, self.min_row, self.max_col, self.max_row 

75 

76 

77 @property 

78 def coord(self): 

79 """ 

80 Excel-style representation of the range 

81 """ 

82 fmt = "{min_col}{min_row}:{max_col}{max_row}" 

83 if (self.min_col == self.max_col 

84 and self.min_row == self.max_row): 

85 fmt = "{min_col}{min_row}" 

86 

87 return fmt.format( 

88 min_col=get_column_letter(self.min_col), 

89 min_row=self.min_row, 

90 max_col=get_column_letter(self.max_col), 

91 max_row=self.max_row 

92 ) 

93 

94 @property 

95 def rows(self): 

96 """ 

97 Return cell coordinates as rows 

98 """ 

99 for row in range(self.min_row, self.max_row+1): 

100 yield [(row, col) for col in range(self.min_col, self.max_col+1)] 

101 

102 

103 @property 

104 def cols(self): 

105 """ 

106 Return cell coordinates as columns 

107 """ 

108 for col in range(self.min_col, self.max_col+1): 

109 yield [(row, col) for row in range(self.min_row, self.max_row+1)] 

110 

111 

112 @property 

113 def cells(self): 

114 from itertools import product 

115 return product(range(self.min_row, self.max_row+1), range(self.min_col, self.max_col+1)) 

116 

117 

118 def _check_title(self, other): 

119 """ 

120 Check whether comparisons between ranges are possible. 

121 Cannot compare ranges from different worksheets 

122 Skip if the range passed in has no title. 

123 """ 

124 if not isinstance(other, CellRange): 

125 raise TypeError(repr(type(other))) 

126 

127 if other.title and self.title != other.title: 

128 raise ValueError("Cannot work with ranges from different worksheets") 

129 

130 

131 def __repr__(self): 

132 fmt = u"<{cls} {coord}>" 

133 if self.title: 

134 fmt = u"<{cls} {title!r}!{coord}>" 

135 return fmt.format(cls=self.__class__.__name__, title=self.title, coord=self.coord) 

136 

137 

138 def __str__(self): 

139 fmt = "{coord}" 

140 title = self.title 

141 if title: 

142 fmt = u"{title}!{coord}" 

143 title = quote_sheetname(title) 

144 return fmt.format(title=title, coord=self.coord) 

145 

146 

147 def __copy__(self): 

148 return self.__class__(min_col=self.min_col, min_row=self.min_row, 

149 max_col=self.max_col, max_row=self.max_row, 

150 title=self.title) 

151 

152 

153 def shift(self, col_shift=0, row_shift=0): 

154 """ 

155 Shift the focus of the range according to the shift values (*col_shift*, *row_shift*). 

156 

157 :type col_shift: int 

158 :param col_shift: number of columns to be moved by, can be negative 

159 :type row_shift: int 

160 :param row_shift: number of rows to be moved by, can be negative 

161 :raise: :class:`ValueError` if any row or column index < 1 

162 """ 

163 

164 if (self.min_col + col_shift <= 0 

165 or self.min_row + row_shift <= 0): 

166 raise ValueError("Invalid shift value: col_shift={0}, row_shift={1}".format(col_shift, row_shift)) 

167 self.min_col += col_shift 

168 self.min_row += row_shift 

169 self.max_col += col_shift 

170 self.max_row += row_shift 

171 

172 

173 def __ne__(self, other): 

174 """ 

175 Test whether the ranges are not equal. 

176 

177 :type other: openpyxl.worksheet.cell_range.CellRange 

178 :param other: Other sheet range 

179 :return: ``True`` if *range* != *other*. 

180 """ 

181 try: 

182 self._check_title(other) 

183 except ValueError: 

184 return True 

185 

186 return ( 

187 other.min_row != self.min_row 

188 or self.max_row != other.max_row 

189 or other.min_col != self.min_col 

190 or self.max_col != other.max_col 

191 ) 

192 

193 

194 def __eq__(self, other): 

195 """ 

196 Test whether the ranges are equal. 

197 

198 :type other: openpyxl.worksheet.cell_range.CellRange 

199 :param other: Other sheet range 

200 :return: ``True`` if *range* == *other*. 

201 """ 

202 return not self.__ne__(other) 

203 

204 

205 def issubset(self, other): 

206 """ 

207 Test whether every cell in this range is also in *other*. 

208 

209 :type other: openpyxl.worksheet.cell_range.CellRange 

210 :param other: Other sheet range 

211 :return: ``True`` if *range* <= *other*. 

212 """ 

213 self._check_title(other) 

214 

215 return ( 

216 (other.min_row <= self.min_row <= self.max_row <= other.max_row) 

217 and 

218 (other.min_col <= self.min_col <= self.max_col <= other.max_col) 

219 ) 

220 

221 __le__ = issubset 

222 

223 

224 def __lt__(self, other): 

225 """ 

226 Test whether *other* contains every cell of this range, and more. 

227 

228 :type other: openpyxl.worksheet.cell_range.CellRange 

229 :param other: Other sheet range 

230 :return: ``True`` if *range* < *other*. 

231 """ 

232 return self.__le__(other) and self.__ne__(other) 

233 

234 

235 def issuperset(self, other): 

236 """ 

237 Test whether every cell in *other* is in this range. 

238 

239 :type other: openpyxl.worksheet.cell_range.CellRange 

240 :param other: Other sheet range 

241 :return: ``True`` if *range* >= *other* (or *other* in *range*). 

242 """ 

243 self._check_title(other) 

244 

245 return ( 

246 (self.min_row <= other.min_row <= other.max_row <= self.max_row) 

247 and 

248 (self.min_col <= other.min_col <= other.max_col <= self.max_col) 

249 ) 

250 

251 __ge__ = issuperset 

252 

253 

254 def __contains__(self, coord): 

255 """ 

256 Check whether the range contains a particular cell coordinate 

257 """ 

258 cr = CellRange(coord) 

259 if cr.title is None: 

260 cr.title = self.title 

261 return self.issuperset(cr) 

262 

263 

264 def __gt__(self, other): 

265 """ 

266 Test whether this range contains every cell in *other*, and more. 

267 

268 :type other: openpyxl.worksheet.cell_range.CellRange 

269 :param other: Other sheet range 

270 :return: ``True`` if *range* > *other*. 

271 """ 

272 return self.__ge__(other) and self.__ne__(other) 

273 

274 

275 def isdisjoint(self, other): 

276 """ 

277 Return ``True`` if this range has no cell in common with *other*. 

278 Ranges are disjoint if and only if their intersection is the empty range. 

279 

280 :type other: openpyxl.worksheet.cell_range.CellRange 

281 :param other: Other sheet range. 

282 :return: ``True`` if the range has no cells in common with other. 

283 """ 

284 self._check_title(other) 

285 

286 # Sort by top-left vertex 

287 if self.bounds > other.bounds: 

288 self, other = other, self 

289 

290 return (self.max_col < other.min_col 

291 or self.max_row < other.min_row 

292 or other.max_row < self.min_row) 

293 

294 

295 def intersection(self, other): 

296 """ 

297 Return a new range with cells common to this range and *other* 

298 

299 :type other: openpyxl.worksheet.cell_range.CellRange 

300 :param other: Other sheet range. 

301 :return: the intersecting sheet range. 

302 :raise: :class:`ValueError` if the *other* range doesn't intersect 

303 with this range. 

304 """ 

305 if self.isdisjoint(other): 

306 raise ValueError("Range {0} doesn't intersect {0}".format(self, other)) 

307 

308 min_row = max(self.min_row, other.min_row) 

309 max_row = min(self.max_row, other.max_row) 

310 min_col = max(self.min_col, other.min_col) 

311 max_col = min(self.max_col, other.max_col) 

312 

313 return CellRange(min_col=min_col, min_row=min_row, max_col=max_col, 

314 max_row=max_row) 

315 

316 __and__ = intersection 

317 

318 

319 def union(self, other): 

320 """ 

321 Return the minimal superset of this range and *other*. This new range 

322 will contain all cells from this range, *other*, and any additional 

323 cells required to form a rectangular ``CellRange``. 

324 

325 :type other: openpyxl.worksheet.cell_range.CellRange 

326 :param other: Other sheet range. 

327 :return: a ``CellRange`` that is a superset of this and *other*. 

328 """ 

329 self._check_title(other) 

330 

331 min_row = min(self.min_row, other.min_row) 

332 max_row = max(self.max_row, other.max_row) 

333 min_col = min(self.min_col, other.min_col) 

334 max_col = max(self.max_col, other.max_col) 

335 return CellRange(min_col=min_col, min_row=min_row, max_col=max_col, 

336 max_row=max_row, title=self.title) 

337 

338 __or__ = union 

339 

340 

341 def __iter__(self): 

342 """ 

343 For use as a dictionary elsewhere in the library. 

344 """ 

345 for x in self.__attrs__: 

346 if x == "title": 

347 continue 

348 v = getattr(self, x) 

349 yield x, v 

350 

351 

352 def expand(self, right=0, down=0, left=0, up=0): 

353 """ 

354 Expand the range by the dimensions provided. 

355 

356 :type right: int 

357 :param right: expand range to the right by this number of cells 

358 :type down: int 

359 :param down: expand range down by this number of cells 

360 :type left: int 

361 :param left: expand range to the left by this number of cells 

362 :type up: int 

363 :param up: expand range up by this number of cells 

364 """ 

365 self.min_col -= left 

366 self.min_row -= up 

367 self.max_col += right 

368 self.max_row += down 

369 

370 

371 def shrink(self, right=0, bottom=0, left=0, top=0): 

372 """ 

373 Shrink the range by the dimensions provided. 

374 

375 :type right: int 

376 :param right: shrink range from the right by this number of cells 

377 :type down: int 

378 :param down: shrink range from the top by this number of cells 

379 :type left: int 

380 :param left: shrink range from the left by this number of cells 

381 :type up: int 

382 :param up: shrink range from the bottown by this number of cells 

383 """ 

384 self.min_col += left 

385 self.min_row += top 

386 self.max_col -= right 

387 self.max_row -= bottom 

388 

389 

390 @property 

391 def size(self): 

392 """ Return the size of the range as a dictionary of rows and columns. """ 

393 cols = self.max_col + 1 - self.min_col 

394 rows = self.max_row + 1 - self.min_row 

395 return {'columns':cols, 'rows':rows} 

396 

397 

398 @property 

399 def top(self): 

400 """A list of cell coordinates that comprise the top of the range""" 

401 return [(self.min_row, col) for col in range(self.min_col, self.max_col+1)] 

402 

403 

404 @property 

405 def bottom(self): 

406 """A list of cell coordinates that comprise the bottom of the range""" 

407 return [(self.max_row, col) for col in range(self.min_col, self.max_col+1)] 

408 

409 

410 @property 

411 def left(self): 

412 """A list of cell coordinates that comprise the left-side of the range""" 

413 return [(row, self.min_col) for row in range(self.min_row, self.max_row+1)] 

414 

415 

416 @property 

417 def right(self): 

418 """A list of cell coordinates that comprise the right-side of the range""" 

419 return [(row, self.max_col) for row in range(self.min_row, self.max_row+1)] 

420 

421 

422class MultiCellRange(Strict): 

423 

424 

425 ranges = Sequence(expected_type=CellRange) 

426 

427 

428 def __init__(self, ranges=()): 

429 if isinstance(ranges, str): 

430 ranges = [CellRange(r) for r in ranges.split()] 

431 self.ranges = ranges 

432 

433 

434 def __contains__(self, coord): 

435 if isinstance(coord, str): 

436 coord = CellRange(coord) 

437 for r in self.ranges: 

438 if coord <= r: 

439 return True 

440 return False 

441 

442 

443 def __repr__(self): 

444 ranges = " ".join([str(r) for r in self.ranges]) 

445 return "<{0} [{1}]>".format(self.__class__.__name__, ranges) 

446 

447 

448 def __str__(self): 

449 ranges = u" ".join([str(r) for r in self.ranges]) 

450 return ranges 

451 

452 __str__ = __str__ 

453 

454 

455 def add(self, coord): 

456 """ 

457 Add a cell coordinate or CellRange 

458 """ 

459 cr = coord 

460 if isinstance(coord, str): 

461 cr = CellRange(coord) 

462 elif not isinstance(coord, CellRange): 

463 raise ValueError("You can only add CellRanges") 

464 if cr not in self: 

465 self.ranges.append(cr) 

466 

467 

468 def __iadd__(self, coord): 

469 self.add(coord) 

470 return self 

471 

472 

473 def __eq__(self, other): 

474 if isinstance(other, str): 

475 other = self.__class__(other) 

476 return self.ranges == other.ranges 

477 

478 

479 def __ne__(self, other): 

480 return not self == other 

481 

482 

483 def __bool__(self): 

484 return bool(self.ranges) 

485 

486 

487 

488 def remove(self, coord): 

489 if not isinstance(coord, CellRange): 

490 coord = CellRange(coord) 

491 self.ranges.remove(coord) 

492 

493 

494 def __iter__(self): 

495 for cr in self.ranges: 

496 yield cr 

497 

498 

499 def __copy__(self): 

500 n = MultiCellRange() 

501 

502 for r in self.ranges: 

503 n.ranges.append(copy(r)) 

504 return n