def __init__(self, filename, sheetnumber, share1, month):
self.wb = open_workbook(filename, formatting_info=True)
# 0铁塔 1基站
self.s = self.wb.sheets()[sheetnumber]
self.share1 = Utils.col_by_name(share1)
self.share2 = self.share1 + 1
self.month = Utils.col_by_name(month)
print "Sheet:", self.s.name
def __init__(self, filename):
self.wb = open_workbook(filename, formatting_info=True)
self.s = self.wb.sheets()[1]
self.datecol = Utils.col_by_name('B') #日期
self.owner = Utils.col_by_name('M') #主建方
self.receiver = self.owner + 1 #共享方
self.sharetype = Utils.col_by_name('O') #共享/共建
self.TieTaType = Utils.col_by_name('I') #铁塔类型
self.height = Utils.col_by_name('J') #高度
print 'Sheet:',self.s.name
def write_total(sheet):
count_ages = 20
col = FIRST_COLUMN + 1
row = FIRST_ROW
count_do = get_institution(0).count()
count_school = get_institution(1).count()
count_row = get_institution().count() + 3
for i, age in enumerate(get_list_age()):
formula_do = 'SUM({}:{})'.format(
Utils.rowcol_to_cell(row - 1 + i, col + 2),
Utils.rowcol_to_cell(row - 1 + i, col + 1 + count_do)
)
formula_school = 'SUM({}:{})'.format(
Utils.rowcol_to_cell(row - 1 + i, col + 2 + count_do + 1),
Utils.rowcol_to_cell(row - 1 + i, col + 2 + count_do + count_school)
)
formula_total_row = '{} + {} + {}'.format(
Utils.rowcol_to_cell(row - 1 + i, col + 1),
Utils.rowcol_to_cell(row - 1 + i, col + 1 + count_do + 1),
Utils.rowcol_to_cell(row - 1 + i, col + 1 + count_do + 2 + count_school),
)
sheet.write(col + i, row - 1, Formula(formula_total_row), style_bold)
sheet.write(col + i, row, Formula(formula_do), style_bold)
sheet.write(col + i, row + count_do + 1, Formula(formula_school), style_bold)
for j in range(count_row + 1):
formula_total_col = 'SUM({}:{})'.format(
Utils.rowcol_to_cell(row - 1, col + j),
Utils.rowcol_to_cell(row - 1 + count_ages - 1, col + j)
)
sheet.write(col + count_ages, row - 1 + j, Formula(formula_total_col), style_bold)
def _get_range(A1range):
(begin_col,begin_row,end_col,end_row,begin_row_abs,begin_col_abs,end_row_abs,end_col_abs) = (None,None,None,None,False,False,False,False)
m = rx_range.match(A1range)
if m:
groups = m.groups()
if groups[0]:
(begin_row,begin_col,begin_row_abs,begin_col_abs) = Utils.cell_to_rowcol(groups[0])
if groups[1]:
(end_row,end_col,end_row_abs,end_col_abs) = Utils.cell_to_rowcol(groups[1])
else:
raise ValueError, "A1range mismatch (%s)" % A1range
return (begin_col,begin_row,end_col,end_row,begin_row_abs,begin_col_abs,end_row_abs,end_col_abs)
def CountAv(self):
numstyle = XFStyle()
numstyle.num_format_str = "0.0000"
if self.mode == 0:
i = 0
while i <= (self.finSize - self.startSize) / 100:
formula = 'AVERAGE(C1:%s)' % (Utils.rowcol_to_cell(
i, self.repeat + 1))
self.res.write(i, 2 + self.repeat, xlwt.Formula(formula),
numstyle)
i += 1
elif self.mode == 2:
formula = 'AVERAGE(C1:%s)' % (Utils.rowcol_to_cell(
0, self.repeat + 1))
self.res.write(0, 2 + self.repeat, xlwt.Formula(formula), numstyle)
def add_plates_sheet(self,sheetname,cell_overwrite_ok=False):
if not isinstance(sheetname, unicode_type):
sheetname = sheetname.decode(self.encoding)
if not Utils.valid_sheet_name(sheetname):
raise Exception("invalid worksheet name %r" % sheetname)
lower_name = sheetname.lower()
if lower_name in self._Workbook__worksheet_idx_from_name:
raise Exception("duplicate worksheet name %r" % sheetname)
self._Workbook__worksheet_idx_from_name[lower_name] = len(self._Workbook__worksheets)
self._Workbook__worksheets.append(PlatesSheet(sheetname,self,cell_overwrite_ok))
return self._Workbook__worksheets[-1]
def write_sitdsc(sitlineno=0, str_row=None, str_col=0, cross_col_no=2, csvlineno=None, height=None, style_head=None, style=None):
ws_sit.row(str_row).height_mismatch = 1
ws_sit.row(str_row).height = height
cont_list = lines[csvlineno].split(',')
ws_sit.write_merge(str_row, str_row, str_col, str_col + cross_col_no, cont_list[0], style_head)
for (i, content) in enumerate(cont_list[1:]):
if content == 'Non':
cellname = Utils.rowcol_to_cell(sitlineno, i + cross_col_no + 1)
ws_sit.write(str_row, i + cross_col_no + 1,
Formula("VLOOKUP(%s,sit_chn_dsc!$A$1:$B$1000,2,FALSE)" % cellname), style)
else:
ws_sit.write(str_row, i + cross_col_no + 1, content, style)
def write_sitdsc(sitlineno=0, str_row=None, str_col=0, cross_col_no=2, csvlineno=None, height=None, style_head=None, style=None):
ws_sit.row(str_row).height_mismatch = 1
ws_sit.row(str_row).height = height
cont_list = lines[csvlineno].split(',')
ws_sit.write_merge(str_row, str_row, str_col, str_col + cross_col_no, cont_list[0], style_head)
for (i, content) in enumerate(cont_list[1:]):
if content == 'Non':
cellname = Utils.rowcol_to_cell(sitlineno, i + cross_col_no + 1)
ws_sit.write(str_row, i + cross_col_no + 1,
Formula("VLOOKUP(%s,sit_chn_dsc!$A$1:$B$1000,2,FALSE)" % cellname), style)
else:
ws_sit.write(str_row, i + cross_col_no + 1, content, style)
def add_sheet(self, sheetname, cell_overwrite_ok=False):
from xlwt import Worksheet, Utils
if not isinstance(sheetname, str):
sheetname = sheetname.decode(self.encoding)
if not Utils.valid_sheet_name(sheetname):
raise Exception("invalid worksheet name %r" % sheetname)
lower_name = sheetname.lower()
if lower_name in self.__worksheet_idx_from_name:
raise Exception("duplicate worksheet name %r" % sheetname)
self.__worksheet_idx_from_name[lower_name] = len(self.__worksheets)
self.__worksheets.append(Worksheet(sheetname, self, cell_overwrite_ok))
return self.__worksheets[-1]
from xlwt import Utils
print Utils.col_by_name('AA')
print Utils.col_by_name('A')
print Utils.cell_to_rowcol('A1')
print Utils.cell_to_rowcol('$A$1')
print Utils.cell_to_rowcol2('A1')
print Utils.rowcol_to_cell(0,0)
print Utils.rowcol_to_cell(0,0,False,True)
print Utils.rowcol_to_cell(
row=0,col=0,row_abs=True,col_abs=True
)
print Utils.cellrange_to_rowcol_pair('1:3')
print Utils.cellrange_to_rowcol_pair('B:G')
print Utils.cellrange_to_rowcol_pair('A2:B7')
print Utils.cellrange_to_rowcol_pair('A1')
print Utils.valid_sheet_name('')
print Utils.valid_sheet_name("'quoted'")
print Utils.valid_sheet_name("O'hare")
print Utils.valid_sheet_name("X"*32)
print Utils.valid_sheet_name("[]:\\?/*\x00")
def tail_match(s,t):
s = re.sub(u'_高铁$', '', s)
t = re.sub(u'_高铁$', '', t)
s = re.sub(u'居委会$','', s)
t = re.sub(u'居委会$','', t)
return s[-1] == t[-1] and s[-2] == t[-2]
#WCDMA BASIC DATABASE
print 'loading wdb'
wdb = open_workbook("wdb.xlsx").sheet_by_index(0)
wdb_names = []
last_name = ""
for i in range(1, wdb.nrows):
new_name = wdb.cell(i,9).value
lac = wdb.cell(i, Utils.col_by_name('G') ).value
nodebid = int( wdb.cell(i, Utils.col_by_name('BF') ).value )
if new_name == last_name:
continue
new_record = {}
new_record["line_number"] = i
new_record["nodebid"] = nodebid
new_record["orig_string"] = new_name
new_record["lac"] = lac
last_name = new_name
new_name = re.sub(u'^泉州|\s','',new_name)
new_name = re.sub(u'\$.*$','',new_name)
new_name = re.sub(u'_BBU[0-9]?', '' , new_name)
new_name = re.sub(u'_TEST', '', new_name)
new_name = re.sub(u'_临时', '', new_name)
new_name = re.sub(u'_[0-9]+$','',new_name)
def excel_table(self):
items_info = self.items_info_all()
excel_table = []
temp_row = []
data_start_row = 2
data_start_col = 4
# 表头
for header_cn in HEADER_BEFORE_DATE:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, header_cn, StyleTypes.header, 1, 0))
for m, m_len in items_info['months'].items():
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, str(m) + u"月", StyleTypes.header, 0, m_len - 1))
for i in range(0, m_len - 1):
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
for header_cn in HEADER_AFTER_DATE:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, header_cn, StyleTypes.header, 1, 0))
excel_table.append(temp_row) # 第一行写完
temp_row = []
for i in range(0, len(HEADER_BEFORE_DATE)):
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
for d in items_info['dates']:
if d.isoweekday() in [6, 7]:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, d.day, StyleTypes.base_weekend))
else:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, d.day, StyleTypes.base))
for i in range(0, len(HEADER_AFTER_DATE)):
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
excel_table.append(temp_row) # 第二行写完
# 填表
temp_row = []
for v, sale_type_cn, sale_type_items in items_info['items']:
if not len(sale_type_items):
break
if sale_type_cn == u"配送":
item_type = StyleTypes.gift
item_weekend_type = StyleTypes.gift_weekend
else:
item_type = StyleTypes.base
item_weekend_type = StyleTypes.base_weekend
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, sale_type_cn, item_type, len(sale_type_items) - 1, 0))
index = 1
for item in sale_type_items:
if index != 1:
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, ITEM_STATUS_CN[item.item_status], item_type))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, item.position.name, item_type))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, item.position.standard_cn, item_type))
for i in range(0, len(items_info['dates'])):
d = items_info['dates'][i]
if d.isoweekday() in [6, 7]:
if item.schedule_by_date(d):
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.schedule_by_date(d).num, item_weekend_type))
else:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, " ", item_weekend_type))
else:
if item.schedule_by_date(d):
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.schedule_by_date(d).num, item_type))
else:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, " ", item_type))
formula = 'SUM(%s:%s)' % (
Utils.rowcol_to_cell(
data_start_row + len(excel_table) - 2, data_start_col),
Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type)) # 总预订量
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.position.price, item_type)) # 刊例单价
formula = '%s*%s' % (
Utils.rowcol_to_cell(
data_start_row + len(excel_table) - 2, len(temp_row) - 2),
Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type)) # 刊例总价
if sale_type_cn == u"配送":
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(DISCOUNT_GIFT) / 100, StyleTypes.gift_discount))
elif sale_type_cn == u"补量":
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(DISCOUNT_ADD) / 100, StyleTypes.discount))
else:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(self.discount) / 100, StyleTypes.discount))
formula = '%s*%s' % (
Utils.rowcol_to_cell(
data_start_row + len(excel_table) - 2, len(temp_row) - 2),
Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type)) # 净价
excel_table.append(temp_row)
index += 1
temp_row = []
# totle
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, "total", StyleTypes.base, 0, 3))
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
for i in range(0, len(items_info['dates']) + 1):
formula = 'SUM(%s:%s)' % (
Utils.rowcol_to_cell(data_start_row, data_start_col + i),
Utils.rowcol_to_cell(len(excel_table) - 1, data_start_col + i))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, "/", StyleTypes.base))
formula = 'SUM(%s:%s)' % (
Utils.rowcol_to_cell(data_start_row, len(temp_row)),
Utils.rowcol_to_cell(len(excel_table) - 1, len(temp_row)))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, "/", StyleTypes.base))
formula = 'SUM(%s:%s)' % (
Utils.rowcol_to_cell(data_start_row, len(temp_row)),
Utils.rowcol_to_cell(len(excel_table) - 1, len(temp_row)))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
excel_table.append(temp_row)
return excel_table
def prepare_each_day(wb,start_date,end_date):
sheet = wb.add_sheet('일별 Feed 개수(기간 내)')
sheet.write(0,0,'시작',easyxf('font: bold True, height 220;' 'alignment: horizontal center;'))
sheet.write(0,1,str(start_date))
sheet.write(1,0,'종료',easyxf('font: bold True, height 220;' 'alignment: horizontal center;'))
sheet.write(1,1,str(end_date))
header_style=easyxf( 'font: name Arial, bold True, height 220;' 'border: bottom thin;' 'pattern: pattern solid, fore_color light_green;')
header_style2=easyxf( 'font: name Arial, bold True, height 220;' 'border: top thin;' 'pattern: pattern solid, fore_color light_green;')
header_style3=easyxf( 'font: name Arial, bold True, height 220;' 'pattern: pattern solid, fore_color light_green;')
sheet.write(3,0,'시간',header_style3)
sheet.write(3,1,'사용자',header_style3)
sheet.write(4,0,'날짜',header_style)
user_profiles = UserProfile.objects.filter(is_deactivated=False)
row=4
col=0
for user_profile in user_profiles:
try:
user = user_profile.user
col+=1
if col != 1:
sheet.write(row-1,col,'',header_style3)
sheet.write(row,col,user.last_name,header_style)
except Exception as e:
print str(e)
pass
col+=1
sheet.write(row-1,col,'',header_style3)
sheet.write(row,col,'합계',header_style)
time = start_date
row=4
col=0
while time < end_date:
next_time = time + dt.timedelta(1) - dt.timedelta(seconds=1)
col=0
row+=1
sheet.write(row,col,time.strftime('%Y.%m.%d'),easyxf('font: bold False, height 220;', num_format_str='YYYY.MM.DD'))
row_total=0
for user_profile in user_profiles:
try:
user = user_profile.user
col+=1
count = Message.objects.filter(is_deleted=False, reg_date__range=(time,next_time), author=user).count()
row_total+=count
if count != 0:
sheet.write(row,col,count,font_style)
except Exception as e:
print str(e)
pass
col+=1
start = Utils.rowcol_to_cell(row,1)
end = Utils.rowcol_to_cell(row,col-1)
sheet.write(row,col,Formula("SUM("+start+":"+end+")"),font_style)
time = time + dt.timedelta(1)
row+=1
col=0
sheet.write(row,col,'합계',header_style2)
for user_profile in user_profiles:
try:
col+=1
start = Utils.rowcol_to_cell(5,col)
end = Utils.rowcol_to_cell(row-1,col)
count = Formula("SUM("+start+":"+end+")")
sheet.write(row,col,count,header_style2)
except Exception as e:
print str(e)
pass
col+=1
start = Utils.rowcol_to_cell(5,col)
end = Utils.rowcol_to_cell(row-1,col)
count = Formula("SUM("+start+":"+end+")")
sheet.write(row,col,count,header_style2)
from xlwt import Utils
print 'AA ->', Utils.col_by_name('AA')
print 'A ->', Utils.col_by_name('A')
print 'A1 ->', Utils.cell_to_rowcol('A1')
print '$A$1 ->', Utils.cell_to_rowcol('$A$1')
print 'A1 ->', Utils.cell_to_rowcol2('A1')
print(0, 0), '->', Utils.rowcol_to_cell(0, 0)
print(0, 0, False, True), '->',
print Utils.rowcol_to_cell(0, 0, False, True)
print(0, 0, True, True), '->',
print Utils.rowcol_to_cell(row=0, col=0, row_abs=True, col_abs=True)
print '1:3 ->', Utils.cellrange_to_rowcol_pair('1:3')
print 'B:G ->', Utils.cellrange_to_rowcol_pair('B:G')
print 'A2:B7 ->', Utils.cellrange_to_rowcol_pair('A2:B7')
print 'A1 ->', Utils.cellrange_to_rowcol_pair('A1')
print(0, 0, 100, 100), '->',
print Utils.rowcol_pair_to_cellrange(0, 0, 100, 100)
print(0, 0, 100, 100, True, False, False, False), '->',
print Utils.rowcol_pair_to_cellrange(row1=0,
col1=0,
row2=100,
col2=100,
row1_abs=True,
col1_abs=False,
row2_abs=False,
def export_events(events, out_file, user=None, encoding='utf-8'):
book = Workbook(encoding=encoding)
sheet1 = book.add_sheet('Events')
style_head = easyxf('font: bold true; borders: bottom thin')
style_italic = easyxf('font: italic true')
style_bold = easyxf('font: bold true')
sheet1.write(0, 0, 'Event', style_head)
#sheet1.col(0).width = 256 * 10 #doesn't work
#sheet1.col(0).width = 0x0d00 + 15 # doesn't work
sheet1.write(0, 1, 'Date', style_head)
sheet1.write(0, 2, 'Days', style_head)
sheet1.write(0, 3, 'Hours', style_head)
sheet1.write(0, 4, 'Description', style_head)
row = 1
days_spent = defaultdict(float)
hours_spent = defaultdict(float)
for event in events:
sheet1.write(row, 0, event['title'])
sheet1.write(row, 1, event['start'],
easyxf(num_format_str='YYYY-MM-DD'))
#sheet1.row(row).write(1, event['start'])
#sheet1.row(row).set_cell_date(1, event['start'])
if event['all_day']:
days = event['end'] - event['start']
sheet1.write(row, 2, days.days + 1)
sheet1.write(row, 3, '')
for tag in event['tags']:
days_spent[tag] += days.days + 1
if not event['tags']:
days_spent[''] += days.days + 1
else:
sheet1.write(row, 2, '')
hours = (event['end'] - event['start']).seconds / 3600.0
sheet1.write(row, 3, round(hours, 1))
for tag in event['tags']:
hours_spent[tag] += hours
if not event['tags']:
hours_spent[''] += hours
sheet1.write(row, 4, event['description'])
row += 1
if row > 1:
style_bold = easyxf('font: bold true')
days_cell_range = Utils.rowcol_pair_to_cellrange(row1=1,
col1=2,
row2=row - 1,
col2=2)
sheet1.write(row, 2, Formula('SUM(%s)' % days_cell_range), style_bold)
days_cell_range = Utils.rowcol_pair_to_cellrange(row1=1,
col1=3,
row2=row - 1,
col2=3)
sheet1.write(row, 3, Formula('SUM(%s)' % days_cell_range), style_bold)
## Now write the summations by tags
if '' in days_spent:
days_spent['Untagged'] = days_spent.pop('')
days_spent = days_spent.items()
days_spent.sort(lambda x, y: cmp(y[1], x[1]))
if '' in hours_spent:
hours_spent['Untagged'] = hours_spent.pop('')
hours_spent = hours_spent.items()
hours_spent.sort(lambda x, y: cmp(y[1], x[1]))
sheet2 = book.add_sheet('Report')
sheet2.write(0, 0, 'Days', style_head)
row = 1
if days_spent:
row += 1
sheet2.write(row, 0, 'Tag', style_bold)
sheet2.write(row, 1, 'Days', style_bold)
total = 0.0
for tag, days in days_spent:
row += 1
if tag == 'Untagged':
sheet2.write(row, 0, tag, style_italic)
else:
sheet2.write(row, 0, tag)
sheet2.write(row, 1, days)
total += days
row += 1
sheet2.write(row, 0, 'Total', style_bold)
sheet2.write(row, 1, total, style_bold)
row += 1
if hours_spent:
row += 1
sheet2.write(row, 0, 'Tag', style_bold)
sheet2.write(row, 1, 'Hours', style_bold)
total = 0.0
for tag, hours in hours_spent:
row += 1
if tag == 'Untagged':
sheet2.write(row, 0, tag, style_italic)
else:
sheet2.write(row, 0, tag)
sheet2.write(row, 1, hours)
total += hours
row += 1
sheet2.write(row, 0, 'Total', style_bold)
sheet2.write(row, 1, total, style_bold)
row += 1
book.save(out_file)
from xlwt import Utils
print 'AA ->',Utils.col_by_name('AA')
print 'A ->',Utils.col_by_name('A')
print 'A1 ->',Utils.cell_to_rowcol('A1')
print '$A$1 ->',Utils.cell_to_rowcol('$A$1')
print 'A1 ->',Utils.cell_to_rowcol2('A1')
print (0,0),'->',Utils.rowcol_to_cell(0,0)
print (0,0,False,True),'->',
print Utils.rowcol_to_cell(0,0,False,True)
print (0,0,True,True),'->',
print Utils.rowcol_to_cell(
row=0,col=0,row_abs=True,col_abs=True
)
print '1:3 ->',Utils.cellrange_to_rowcol_pair('1:3')
print 'B:G ->',Utils.cellrange_to_rowcol_pair('B:G')
print 'A2:B7 ->',Utils.cellrange_to_rowcol_pair('A2:B7')
print 'A1 ->',Utils.cellrange_to_rowcol_pair('A1')
print (0,0,100,100),'->',
print Utils.rowcol_pair_to_cellrange(0,0,100,100)
print (0,0,100,100,True,False,False,False),'->',
print Utils.rowcol_pair_to_cellrange(
row1=0,col1=0,row2=100,col2=100,
row1_abs=True,col1_abs=False,
row2_abs=False,col2_abs=True
)
from xlwt import Utils
print Utils.col_by_name('AA')
print Utils.col_by_name('A')
print Utils.cell_to_rowcol('A1')
print Utils.cell_to_rowcol('$A$1')
print Utils.cell_to_rowcol2('A1')
print Utils.rowcol_to_cell(0, 0)
print Utils.rowcol_to_cell(0, 0, False, True)
print Utils.rowcol_to_cell(row=0, col=0, row_abs=True, col_abs=True)
print Utils.cellrange_to_rowcol_pair('1:3')
print Utils.cellrange_to_rowcol_pair('B:G')
print Utils.cellrange_to_rowcol_pair('A2:B7')
print Utils.cellrange_to_rowcol_pair('A1')
print Utils.valid_sheet_name('')
print Utils.valid_sheet_name("'quoted'")
print Utils.valid_sheet_name("O'hare")
print Utils.valid_sheet_name("X" * 32)
print Utils.valid_sheet_name("[]:\\?/*\x00")
def export_events(events, out_file, user=None, encoding='utf-8'):
book = Workbook(encoding=encoding)
sheet1 = book.add_sheet('Events')
style_head = easyxf('font: bold true; borders: bottom thin')
style_italic = easyxf('font: italic true')
style_bold = easyxf('font: bold true')
sheet1.write(0,0,'Event', style_head)
#sheet1.col(0).width = 256 * 10 #doesn't work
#sheet1.col(0).width = 0x0d00 + 15 # doesn't work
sheet1.write(0,1,'Date', style_head)
sheet1.write(0,2,'Days', style_head)
sheet1.write(0,3,'Hours', style_head)
sheet1.write(0,4,'Description', style_head)
row = 1
days_spent = defaultdict(float)
hours_spent = defaultdict(float)
for event in events:
sheet1.write(row, 0, event['title'])
sheet1.write(row, 1, event['start'], easyxf(
num_format_str='YYYY-MM-DD'
))
#sheet1.row(row).write(1, event['start'])
#sheet1.row(row).set_cell_date(1, event['start'])
if event['all_day']:
days = event['end'] - event['start']
sheet1.write(row, 2, days.days + 1)
sheet1.write(row, 3, '')
for tag in event['tags']:
days_spent[tag] += days.days + 1
if not event['tags']:
days_spent[''] += days.days + 1
else:
sheet1.write(row, 2, '')
hours = (event['end'] - event['start']).seconds / 3600.0
sheet1.write(row, 3, round(hours,1))
for tag in event['tags']:
hours_spent[tag] += hours
if not event['tags']:
hours_spent[''] += hours
sheet1.write(row, 4, event['description'])
row += 1
if row > 1:
style_bold = easyxf('font: bold true')
days_cell_range = Utils.rowcol_pair_to_cellrange(row1=1, col1=2, row2=row - 1, col2=2)
sheet1.write(row, 2, Formula('SUM(%s)' % days_cell_range), style_bold)
days_cell_range = Utils.rowcol_pair_to_cellrange(row1=1, col1=3, row2=row - 1, col2=3)
sheet1.write(row, 3, Formula('SUM(%s)' % days_cell_range), style_bold)
## Now write the summations by tags
if '' in days_spent:
days_spent['Untagged'] = days_spent.pop('')
days_spent = days_spent.items()
days_spent.sort(lambda x,y: cmp(y[1], x[1]))
if '' in hours_spent:
hours_spent['Untagged'] = hours_spent.pop('')
hours_spent = hours_spent.items()
hours_spent.sort(lambda x,y: cmp(y[1], x[1]))
sheet2 = book.add_sheet('Report')
sheet2.write(0,0,'Days', style_head)
row = 1
if days_spent:
row += 1
sheet2.write(row,0, 'Tag', style_bold)
sheet2.write(row,1, 'Days', style_bold)
total = 0.0
for tag, days in days_spent:
row += 1
if tag == 'Untagged':
sheet2.write(row, 0, tag, style_italic)
else:
sheet2.write(row, 0, tag)
sheet2.write(row, 1, days)
total += days
row += 1
sheet2.write(row, 0, 'Total', style_bold)
sheet2.write(row, 1, total, style_bold)
row += 1
if hours_spent:
row += 1
sheet2.write(row,0, 'Tag', style_bold)
sheet2.write(row,1, 'Hours', style_bold)
total = 0.0
for tag, hours in hours_spent:
row += 1
if tag == 'Untagged':
sheet2.write(row, 0, tag, style_italic)
else:
sheet2.write(row, 0, tag)
sheet2.write(row, 1, hours)
total += hours
row += 1
sheet2.write(row, 0, 'Total', style_bold)
sheet2.write(row, 1, total, style_bold)
row += 1
book.save(out_file)
# -*- coding: UTF-8 -*-
#!/bin/python
import re
from xlrd import open_workbook
from xlwt import Utils
#WCDMA xiaoqu DATABASE
print 'loading xiaoqu'
wdb_book = open_workbook("xiaoqu.xlsx")
wdb = wdb_book.sheet_by_index(0)
wdb_names = []
for i in range(1, wdb.nrows):
new_id = wdb.cell(i, Utils.col_by_name('D') ).value
new_record = {}
new_record["line_number"] = i
new_record["nodebid"] = int(new_id)
wdb_names.append( new_record)
print `len(wdb_names)` + " xiaoqu"
#WCDMA automatch
print 'loading automatch'
wbbu_book = open_workbook("automatch.xls")
wbbu = wbbu_book.sheet_by_index(0)
wbbu_names = []
for i in range(1, wbbu.nrows):
new_id = wbbu.cell(i, Utils.col_by_name('C') ).value
new_record = {}
new_record["line_number"] = i
new_record["nodebid"] = int(new_id)
def excel_table(self):
items_info = self.items_info_all()
excel_table = []
temp_row = []
data_start_row = 2
data_start_col = 4
# 表头
for header_cn in HEADER_BEFORE_DATE:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, header_cn, StyleTypes.header, 1, 0))
for m, m_len in items_info['months'].items():
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, str(m) + u"月", StyleTypes.header, 0, m_len - 1))
for i in range(0, m_len - 1):
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
for header_cn in HEADER_AFTER_DATE:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, header_cn, StyleTypes.header, 1, 0))
excel_table.append(temp_row) # 第一行写完
temp_row = []
for i in range(0, len(HEADER_BEFORE_DATE)):
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
for d in items_info['dates']:
if d.isoweekday() in [6, 7]:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, d.day, StyleTypes.base_weekend))
else:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, d.day, StyleTypes.base))
for i in range(0, len(HEADER_AFTER_DATE)):
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
excel_table.append(temp_row) # 第二行写完
# 填表
temp_row = []
for v, sale_type_cn, sale_type_items in items_info['items']:
if not len(sale_type_items):
break
if sale_type_cn == u"配送":
item_type = StyleTypes.gift
item_weekend_type = StyleTypes.gift_weekend
else:
item_type = StyleTypes.base
item_weekend_type = StyleTypes.base_weekend
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, sale_type_cn, item_type, len(sale_type_items) - 1, 0))
index = 1
for item in sale_type_items:
if index != 1:
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, ITEM_STATUS_CN[item.item_status], item_type))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, item.position.name, item_type))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, item.position.standard_cn, item_type))
for i in range(0, len(items_info['dates'])):
d = items_info['dates'][i]
if d.isoweekday() in [6, 7]:
if item.schedule_by_date(d):
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.schedule_by_date(d).num, item_weekend_type))
else:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, " ", item_weekend_type))
else:
if item.schedule_by_date(d):
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.schedule_by_date(d).num, item_type))
else:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, " ", item_type))
formula = 'SUM(%s:%s)' % (
Utils.rowcol_to_cell(
data_start_row + len(excel_table) - 2, data_start_col),
Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type)) # 总预订量
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.position.price, item_type)) # 刊例单价
formula = '%s*%s' % (
Utils.rowcol_to_cell(
data_start_row + len(excel_table) - 2, len(temp_row) - 2),
Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type)) # 刊例总价
if sale_type_cn == u"配送":
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(DISCOUNT_GIFT) / 100, StyleTypes.gift_discount))
elif sale_type_cn == u"补量":
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(DISCOUNT_ADD) / 100, StyleTypes.discount))
else:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(self.discount) / 100, StyleTypes.discount))
formula = '%s*%s' % (
Utils.rowcol_to_cell(
data_start_row + len(excel_table) - 2, len(temp_row) - 2),
Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type)) # 净价
excel_table.append(temp_row)
index += 1
temp_row = []
# totle
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, "total", StyleTypes.base, 0, 3))
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
for i in range(0, len(items_info['dates']) + 1):
formula = 'SUM(%s:%s)' % (
Utils.rowcol_to_cell(data_start_row, data_start_col + i),
Utils.rowcol_to_cell(len(excel_table) - 1, data_start_col + i))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, "/", StyleTypes.base))
formula = 'SUM(%s:%s)' % (
Utils.rowcol_to_cell(data_start_row, len(temp_row)),
Utils.rowcol_to_cell(len(excel_table) - 1, len(temp_row)))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, "/", StyleTypes.base))
formula = 'SUM(%s:%s)' % (
Utils.rowcol_to_cell(data_start_row, len(temp_row)),
Utils.rowcol_to_cell(len(excel_table) - 1, len(temp_row)))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
excel_table.append(temp_row)
return excel_table