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_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)
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)
