def test_add_local_named_range(tmpdir): tmpdir.chdir() wb = Workbook() new_sheet = wb.create_sheet() named_range = NamedRange('test_nr', [(new_sheet, 'A1')]) named_range.scope = wb.get_index(new_sheet) wb.add_named_range(named_range) dest_filename = 'local_named_range_book.xlsx' wb.save(dest_filename)
def test_get_index(): wb = Workbook() new_sheet = wb.create_sheet(0) sheet_index = wb.get_index(new_sheet) eq_(sheet_index, 0)
def test_get_index(): wb = Workbook() new_sheet = wb.create_sheet(0) sheet_index = wb.get_index(new_sheet) assert sheet_index == 0
class ExcelWorkbook(object): def __init__(self): self.__thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) self.__greyTheme = Style(border=self.__thin_border, fill=PatternFill(fill_type='solid', start_color='FFD8D8D8')) self.__whiteTheme = Style(border=self.__thin_border, fill=PatternFill(fill_type='solid', start_color='FFFFFFFF')) self._repoToColumnTuples = [('B', 'chromium'), ('C', 'blink'), ('D', 'trace-viewer'), ('E', 'skia'), ('F', 'v8'), ('G', 'Total')] self._workbook = Workbook() self._author_col_width = helper.GetAuthorColumnWidth() self._data_col_width = 0 self._header_row_height = 30 self._author_data = None self._current_sheet = None def column_number_to_letter(self, col): return get_column_letter(col) def GenerateCompleteContributionsReport(self): self._ReadContributionsDataFromJson() self._GenerateTotalContributionSheet() self._GenerateYearlyContributionSheet() self._GenerateWeeklyContributionSheet(str(date.today().year)) self._workbook.save('weeklyReport.xlsx') def _ReadContributionsDataFromJson(self): with open('weeklyReport.json') as jsonFile: self._author_data = json.loads(jsonFile.read()) def _NextSheetIndex(self): return self._workbook.get_index(self._workbook.get_sheet_by_name('Sheet')) def _GenerateTotalContributionSheet(self): self._current_sheet = self._workbook.create_sheet(index=self._NextSheetIndex(), title='Total') self._PopulateContributions('total') self._CalculateContributionsSummation() self._BeautifyWorksheet() def _GenerateYearlyContributionSheet(self): for year in helper.GetContributionsReportingYearRange(): self._current_sheet = self._workbook.create_sheet(index=self._NextSheetIndex(), title=str(year) + ' Contributions') self._PopulateContributions('total', str(year)) self._CalculateContributionsSummation() self._BeautifyWorksheet() def _GenerateWeeklyContributionSheet(self, year): for repo in repositories: repoName = repo['name'] self._current_sheet = self._workbook.create_sheet(index=self._NextSheetIndex(), title='%s %s Weekly' % (year, repoName)) for week in xrange(52, 0, -1): (begin, end) = helper.GetDatesFromWeekNumber(int(year), week) key = 'W%02d %s to %s' % (week, begin, end) self._PopulateContributions(key, year, repoName) self._CalculateContributionsSummation() self._BeautifyWorksheet() def _GenerateWeeklyClosedContributionsSheet(self, year): # Patches which got closed during the week. self._current_sheet = self._workbook.create_sheet(index=self._NextSheetIndex(), title='Weekly closed contributions') self._PopulateWeeklyClosedContributionsDetails() self._BeautifyWorksheet() def _GenerateWeeklyOpenContributionsSheet(self, year): # Patches which are under review during the week. self._current_sheet = self._workbook.create_sheet(index=self._NextSheetIndex(), title='Weekly open contributions') self._PopulateWeeklyOpenContributionsDetails() self._BeautifyWorksheet() def _PopulateWeeklyClosedContributionsSheet(self): currentWeek = helper.GetCurrentWeek() key = currentWeek[0] + ' to ' + currentWeek[1] self._current_sheet['A1'] = 'Name' # self._current_sheet['A2'] def _PopulateContributions(self, key, year=None, repo=None): codeReviewSearchURL = """https://codereview.chromium.org/search?closed=1&owner=%s&reviewer=&cc=&repo_guid=&base=&project=&private=1&commit=1&created_before=&created_after=&modified_before=&modified_after=&order=&format=html&keys_only=False&with_messages=False&cursor=&limit=30""" self._CreateSheetHeaderRow(key) for i in xrange(len(self._author_data)): author = self._author_data[i] index = str(i + 2) self._current_sheet['A' + index] = author['name'] # TODO: Correct the hyperlink as per sheet being filled. if type(author['email']) == list: self._current_sheet['A' + index].hyperlink = codeReviewSearchURL % author['email'][0] else: self._current_sheet['A' + index].hyperlink = codeReviewSearchURL % author['email'] if 'contributions' not in author: continue contributions = author['contributions'] if key.startswith('W'): self._PopulateWeeklyContributions(contributions, index, key, year, repo) else: self._PopulateYearlyContributions(contributions, index, key, year) def _PopulateWeeklyContributions(self, contributions, rowIndex, key, year, repo): assert(year is not None and repo is not None) column = self.column_number_to_letter(self._current_sheet.get_highest_column()) if repo in contributions: if year in contributions[repo] and key in contributions[repo][year]: self._current_sheet[column + rowIndex] = contributions[repo][year][key] else: self._current_sheet[column + rowIndex] = 0 else: self._current_sheet[column + rowIndex] = 0 def _PopulateYearlyContributions(self, contributions, rowIndex, key, year=None): for (col, repo) in self._repoToColumnTuples: if repo in contributions: if year: if year in contributions[repo]: self._current_sheet[col + rowIndex] = contributions[repo][year][key] else: self._current_sheet[col + rowIndex] = 0 else: if key in contributions[repo]: self._current_sheet[col + rowIndex] = contributions[repo][key] else: self._current_sheet[col + rowIndex] = 0 else: self._current_sheet[col + rowIndex] = 0 self._current_sheet['G' + rowIndex] = '=sum(b%s:f%s)' % (rowIndex, rowIndex) def _CalculateContributionsSummation(self): totalAuthors = len(self._author_data) finalRowIndex = str(totalAuthors + 2) dataRange = '(%s2:%s' + str(totalAuthors + 1) + ')' self._current_sheet['A' + finalRowIndex] = 'Total' for col in xrange(2, self._current_sheet.get_highest_column() + 1): column = self.column_number_to_letter(col) self._current_sheet[column + finalRowIndex] = '=sum' + (dataRange % (column, column)) def _BeautifyWorksheet(self): sheet = self._current_sheet sheet.column_dimensions['A'].width = self._author_col_width sheet.row_dimensions[1].height = self._header_row_height sheet.row_dimensions[sheet.get_highest_row()].height = self._header_row_height lastRow = str(sheet.get_highest_row()) header = [ 'A1' ] footer = [ 'A' + lastRow ] for col in xrange(2, sheet.get_highest_column() + 1): column = self.column_number_to_letter(col) sheet.column_dimensions[column].width = self._data_col_width header.append(column + '1') footer.append(column + lastRow) for col in header: sheet[col].style = Style(alignment=Alignment(vertical='bottom', horizontal='center'), border=self.__thin_border, fill=PatternFill(fill_type='solid', start_color='FFFFFF99'), font=Font(bold=True)) for col in footer: sheet[col].style = Style(alignment=Alignment(vertical='bottom', horizontal='center'), border=self.__thin_border, fill=PatternFill(fill_type='solid', start_color='FFFFFF99'), font=Font(bold=True)) for col in xrange(1, sheet.get_highest_column() + 1): for row in xrange(2, sheet.get_highest_row() - 1): column = self.column_number_to_letter(col) cell = sheet[str(column + str(row))] if (row % 2) == 0: cell.style = self.__greyTheme else: cell.style = self.__whiteTheme def _CreateSheetHeaderRow(self, key): subkey = key.split(' ', 1) self._current_sheet['A1'] = 'Name' if subkey[0].startswith('W'): column = self.column_number_to_letter(self._current_sheet.get_highest_column() + 1) + '1' self._current_sheet[column] = subkey[0] self._current_sheet[column].comment = Comment(subkey[1], 'OSS') # TODO: Better way to determine width of weekly column? self._data_col_width = 5 else: for (col, repo) in self._repoToColumnTuples: self._current_sheet[col + '1'] = repo.title() self._data_col_width = max(self._data_col_width, len(repo) + 1)