def calculate(solution, printed=False):
if solution is None:
return 0
prof = 0
index = 0
stop = False
label = []
for i in range(len(solution)):
if solution[index] == _unused_node:
return -9999
cost = _distance[index][solution[index]] * _travel_cost
prof += (_nodes[solution[index]][2] - cost)
index = solution[index]
label.append(solution[index])
if printed:
print(" FROM %d ---> %d COST %d PROF %d" %
(index, solution[index], cost, prof))
if stop:
break
if solution[index] == 0:
stop = True
for i in range(len(solution)):
if solution[i] not in label:
#prof -= _nodes[solution[i]][2]
solution[i] = _unused_node
return prof
def import_user(self, request, obj, change):
wb = load_workbook(filename=obj.YDUserFile.path)
ws = wb.get_sheet_names()
ws = wb.get_sheet_by_name(ws[0])
headers = ['version', 'attr', 'value', 'addr']
lists = []
users = request.user
for row in range(2, 5):
r = {}
for col in range(1, len(headers) + 1):
key = headers[col - 1]
r[key] = ws.cell(row=row, column=col).value
lists.append(r)
sqllist = []
for cell in lists:
revision = cell['version']
prop = cell['attr']
value = cell['value']
repo = cell['addr']
sql = CameraDevice(revision=revision,
prop=prop,
value=value,
repo=repo,
editor=users)
sqllist.append(sql)
CameraDevice.object.bulk_create(sqllist)
def _cells_by_row(self, min_col, min_row, max_col, max_row):
"""
The source worksheet file may have columns or rows missing.
Missing cells will be created.
"""
if max_col is not None:
empty_row = tuple(EMPTY_CELL
for column in range(min_col, max_col + 1))
else:
empty_row = []
row_counter = min_row
if self._iter_parse is None:
p = iterparse(self.xml_source,
tag=[ROW_TAG],
remove_blank_text=True)
self._iter_parse = {
int(element.get("r", -1)): (_event, element)
for _event, element in p if element.tag == ROW_TAG
}
for row_id in range(min_row, max_row + 1):
result_obj = self._iter_parse.get(row_id)
# some rows are missing
if result_obj is None:
yield empty_row
_event, element = result_obj
yield tuple(
self._get_row(element, min_col, max_col, row_counter=row_id))
def _cells_by_row(self, min_col, min_row, max_col, max_row, values_only=False):
for row in range(min_row, max_row + 1):
cells = (self.cell(row=row, column=column) for column in range(min_col, max_col + 1))
if values_only:
yield tuple(cell.value for cell in cells)
else:
yield tuple(cells)
def listproj():
global wb
wb = Workbook()
PO_Project_Col = findcolumn(po, 'Project')
po_data = po.get_sheet_by_name('data')
end_row = int(po_data.get_highest_row())
req_data = req.get_sheet_by_name('data')
end_row_req = int(req_data.get_highest_row())
global Projects
Projects = []
for i in range(2, end_row):
if str(po_data.cell(column = PO_Info[10], row = i).value) != 'Yes':
Projects.append(str(po_data.cell(row = i, column = PO_Project_Col).value))
for j in range(2, end_row_req):
Projects.append(str(req_data.cell(row = j, column = REQ_Info[0]).value))
Projects = list(set(filter(None, Projects)))
Projects.sort(key=natural_keys)
Size_Project_List = len(Projects)
p = {}
for i in range(0, Size_Project_List):
z= i + 1
p['proj_sheet_%02d' % z ] = wb.create_sheet(title = Projects[i])
sheetName = 'Sheet'
ws = wb.get_sheet_by_name(sheetName)
wb.remove_sheet(ws)
def get_squared_range(self, min_col, min_row, max_col, max_row):
"""
The source worksheet file may have columns or rows missing.
Missing cells will be created.
"""
if max_col is not None:
empty_row = tuple(EMPTY_CELL
for column in range(min_col, max_col + 1))
else:
empty_row = []
row_counter = min_row
p = iterparse(self.xml_source, tag=[ROW_TAG], remove_blank_text=True)
for _event, element in p:
if element.tag == ROW_TAG:
row_id = int(element.get("r"))
# got all the rows we need
if max_row is not None and row_id > max_row:
break
# some rows are missing
for row_counter in range(row_counter, row_id):
yield empty_row
# return cells from a row
if min_row <= row_id:
yield tuple(self._get_row(element, min_col, max_col))
row_counter += 1
if element.tag in (CELL_TAG, VALUE_TAG, FORMULA_TAG):
# sub-elements of rows should be skipped as handled within a cell
continue
element.clear()
def unmerge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
""" Remove merge on a cell range. Range is a cell range (e.g. A1:E1) """
if not range_string:
if start_row is None or start_column is None or end_row is None or end_column is None:
msg = "You have to provide a value either for "\
"'coordinate' or for 'start_row', 'start_column', 'end_row' *and* 'end_column'"
raise InsufficientCoordinatesException(msg)
else:
range_string = '%s%s:%s%s' % (get_column_letter(start_column + 1), start_row + 1, get_column_letter(end_column + 1), end_row + 1)
elif len(range_string.split(':')) != 2:
msg = "Range must be a cell range (e.g. A1:E1)"
raise InsufficientCoordinatesException(msg)
else:
range_string = range_string.replace('$', '')
if range_string in self._merged_cells:
self._merged_cells.remove(range_string)
min_col, min_row = coordinate_from_string(range_string.split(':')[0])
max_col, max_row = coordinate_from_string(range_string.split(':')[1])
min_col = column_index_from_string(min_col)
max_col = column_index_from_string(max_col)
# Mark cell as unmerged
for col in range(min_col, max_col + 1):
for row in range(min_row, max_row + 1):
if not (row == min_row and col == min_col):
self._get_cell('%s%s' % (get_column_letter(col), row)).merged = False
else:
msg = 'Cell range %s not known as merged.' % range_string
raise InsufficientCoordinatesException(msg)
def run_xlsxwriter_style_cheating():
try:
import xlsxwriter.workbook
except ImportError:
raise Exception('XlsxWriter not installled')
if not formatData:
generate_format_data()
stime = time.clock()
wb = xlsxwriter.workbook.Workbook(get_output_path('test_xlsxwriter_style.xlsx'), {'constant_memory': True})
ws = wb.add_worksheet()
cell_formats = []
for i in range(16):
cell_format = wb.add_format()
if i & BOLD:
cell_format.set_bold()
if i & ITALIC:
cell_format.set_italic()
if i & UNDERLINE:
cell_format.set_underline()
if i & RED_BG:
cell_format.set_bg_color('red')
cell_formats.append(cell_format)
for row in range(ROWS):
for col in range(COLUMNS):
ws.write_number(row, col, 1, cell_formats[formatData[row][col]])
wb.close()
elapsed = time.clock() - stime
print("xlsxwriter style cheating, %s, %s, %s" % (ROWS, COLUMNS, elapsed))
return elapsed
def render_ws(ws):
# adjust width of the column
for i in range(ws.max_column):
ws.column_dimensions[get_column_letter(i + 1)].width = 20
# adjust height of the row
for i in range(ws.max_row):
# Get value of specific cells with openpyxl
val = ws["{}{}".format(get_column_letter(5), i + 1)].value
if i == 0:
ws.row_dimensions[i + 1].height = 23
elif val is not None:
if len(val.split('\n')) <= 10:
ws.row_dimensions[i + 1].height = 18 * len(val.split('\n'))
else:
ws.row_dimensions[i + 1].height = 18 * 10
else:
ws.row_dimensions[i + 1].height = 18
# set border, used "iter_rows"
for row in ws.iter_rows("A1:E17"):
for i in range(ws.max_column):
row[i].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
def appendSSEStocks(self, lastStockNumber):
wb = Workbook() # load_workbook(filename=self.filePath)
sheet = wb.active
# 写标题
for i in range(self.__indexName.__len__()):
_ = sheet.cell(column=i + 1, row=1, value=self.__indexName[i])
row = sheet.max_row + 1
for i in range(self.startStockNumber, lastStockNumber + 1):
a = AchieveSSEStockInfo(i)
sleep(1)
# print a.getStatus()
if not a.getStatus():
continue
for j in range(a.__public__.__len__()):
m = a.__public__[j]
f = getattr(a, m)
print m
print f()
_ = sheet.cell(column=j + 1, row=row, value="%s" % f())
row = row + 1
# 每获取一个上市公司完整信息就写入xlsx，避免占用过大内存
wb.save(filename=self.filePath)
def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
""" Set merge on a cell range. Range is a cell range (e.g. A1:E1) """
if not range_string and not all((start_row, start_column, end_row, end_column)):
msg = "You have to provide a value either for 'coordinate' or for\
'start_row', 'start_column', 'end_row' *and* 'end_column'"
raise ValueError(msg)
elif not range_string:
range_string = '%s%s:%s%s' % (get_column_letter(start_column),
start_row,
get_column_letter(end_column),
end_row)
elif ":" not in range_string:
if COORD_RE.match(range_string):
return # Single cell, do nothing
raise ValueError("Range must be a cell range (e.g. A1:E1)")
else:
range_string = range_string.replace('$', '')
if range_string not in self._merged_cells:
self._merged_cells.append(range_string)
min_col, min_row, max_col, max_row = range_boundaries(range_string)
rows = range(min_row, max_row+1)
cols = range(min_col, max_col+1)
cells = product(rows, cols)
# all but the top-left cell are removed
for c in islice(cells, 1, None):
if c in self._cells:
del self._cells[c]
def inventory():
inv_data = inv.get_sheet_by_name('data')
end_row_inv = int(inv_data.get_highest_row())
for x in range(0, len(Projects)):
ws = wb.get_sheet_by_name(Projects[x])
end_row_test = int(ws.get_highest_row())
for j in range(2, end_row_test + 1):
item_list = []
for k in range(2, end_row_inv + 1):
if ws.cell(column = (PO_Titles.index('Part Number') + 1), row = j).value == inv_data.cell(column = INV_Info[0], row = k).value and inv_data.cell(column = INV_Info[4], row = k).value > 0:
item_list.extend([k])
if len(item_list) > 0:
item_on_hand = []
item_locations = []
item_projects = []
Locations = "%s:%s = %d, "
for p in range (0, len(item_list)):
item_on_hand.extend([int(inv_data.cell(column = INV_Info[4], row = item_list[p]).value)])
item_locations.extend([str(inv_data.cell(column = INV_Info[3], row = item_list[p]).value)])
item_projects.extend([str(inv_data.cell(column = INV_Info[2], row = item_list[p]).value)])
ws.cell(column = (PO_Titles.index('On Hand') + 1), row = j).value = sum(item_on_hand)
Locations_total = Locations*len(item_list)
Locations_fixed = Locations_total[:-2]
locations_and_qty = [None]*(len(item_on_hand) + len(item_locations) + len(item_projects))
locations_and_qty[::3] = item_projects
locations_and_qty[1::3] = item_locations
locations_and_qty[2::3] = item_on_hand
ws.cell(column = (PO_Titles.index('Locations') + 1), row = j).value = Locations_fixed % tuple(locations_and_qty)
else:
ws.cell(column = (PO_Titles.index('On Hand') + 1), row = j).value = int(0)
def run_pyexcelerate_style_fastest():
wb = Workbook()
stime = time.clock()
ws = wb.new_sheet('Test 1')
bold = Style(font=Font(bold=True))
italic = Style(font=Font(italic=True))
underline = Style(font=Font(underline=True))
red = Style(fill=Fill(background=Color(255,0,0,0)))
for row in range(ROWS):
for col in range(COLUMNS):
ws.set_cell_value(row + 1, col + 1, 1)
style = Style()
if formatData[row][col] & BOLD:
style.font.bold = True
if formatData[row][col] & ITALIC:
style.font.italic = True
if formatData[row][col] & UNDERLINE:
style.font.underline = True
if formatData[row][col] & RED_BG:
style.fill.background = Color(255, 0, 0)
ws.set_cell_style(row + 1, col + 1, style)
wb.save(get_output_path('test_pyexcelerate_style_fastest.xlsx'))
elapsed = time.clock() - stime
print("pyexcelerate style fastest, %s, %s, %s" % (ROWS, COLUMNS, elapsed))
return elapsed
def _get_row(self, element, min_col=1, max_col=None):
"""Return cells from a particular row"""
col_counter = min_col
for cell in safe_iterator(element, CELL_TAG):
coordinate = cell.get('r')
row, column = coordinate_to_tuple(coordinate)
if max_col is not None and column > max_col:
break
if min_col <= column:
if col_counter < column:
for col_counter in range(max(col_counter, min_col), column):
# pad row with missing cells
yield EMPTY_CELL
data_type = cell.get('t', 'n')
style_id = int(cell.get('s', 0))
formula = cell.findtext(FORMULA_TAG)
value = cell.find(VALUE_TAG)
if value is not None:
value = value.text
if formula is not None:
if not self.parent.data_only:
data_type = 'f'
value = "=%s" % formula
yield ReadOnlyCell(self, row, column,
value, data_type, style_id)
col_counter = column + 1
if max_col is not None:
for _ in range(col_counter, max_col+1):
yield EMPTY_CELL
def anchor(self):
""" returns the expected position of a cell in pixels from the top-left
of the sheet. For example, A1 anchor should be (0,0).
:rtype: tuple(int, int)
"""
left_columns = (column_index_from_string(self.column) - 1)
column_dimensions = self.parent.column_dimensions
left_anchor = 0
default_width = points_to_pixels(DEFAULT_COLUMN_WIDTH)
for col_idx in range(left_columns):
letter = get_column_letter(col_idx + 1)
if letter in column_dimensions:
cdw = column_dimensions.get(letter).width or default_width
if cdw > 0:
left_anchor += points_to_pixels(cdw)
continue
left_anchor += default_width
row_dimensions = self.parent.row_dimensions
top_anchor = 0
top_rows = (self.row - 1)
default_height = points_to_pixels(DEFAULT_ROW_HEIGHT)
for row_idx in range(1, top_rows + 1):
if row_idx in row_dimensions:
rdh = row_dimensions[row_idx].height or default_height
if rdh > 0:
top_anchor += points_to_pixels(rdh)
continue
top_anchor += default_height
return (left_anchor, top_anchor)
def getCoord(lines, flag):
#求竖线的横坐标
if flag == "col":
lines_x = np.sort(lines[:,:,0], axis=None)
list_x = list(lines_x)
#合并距离相近的点
for i in range(len(list_x) - 1):
if (list_x[i] - list_x[i + 1]) ** 2 <= (img_w/12)**2:
list_x[i + 1] = list_x[i]
list_x = list(set(list_x))#去重
list_x.sort()#排序
return list_x
#求横线的纵坐标
elif flag == "row":
lines_y = np.sort(lines[:,:,1], axis=None)
list_y = list(lines_y)
# 合并距离相近的点
for i in range(len(list_y) - 1):
if (list_y[i] - list_y[i + 1]) ** 2 <= (img_h/8)**2:
list_y[i + 1] = list_y[i]
list_y = list(set(list_y)) # 去重
list_y.sort() # 排序
return list_y
def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
""" Set merge on a cell range. Range is a cell range (e.g. A1:E1) """
if not range_string and not all((start_row, start_column, end_row, end_column)):
msg = "You have to provide a value either for 'coordinate' or for\
'start_row', 'start_column', 'end_row' *and* 'end_column'"
raise ValueError(msg)
elif not range_string:
range_string = '%s%s:%s%s' % (get_column_letter(start_column),
start_row,
get_column_letter(end_column),
end_row)
elif ":" not in range_string:
if COORD_RE.match(range_string):
return # Single cell, do nothing
raise ValueError("Range must be a cell range (e.g. A1:E1)")
else:
range_string = range_string.replace('$', '')
if range_string not in self._merged_cells:
self._merged_cells.append(range_string)
min_col, min_row, max_col, max_row = range_boundaries(range_string)
rows = range(min_row, max_row+1)
cols = range(min_col, max_col+1)
cells = product(rows, cols)
# all but the top-left cell are removed
for c in islice(cells, 1, None):
if c in self._cells:
del self._cells[c]
def run_openpyxl_style():
try:
import openpyxl
except ImportError:
raise Exception('openpyxl not installled')
stime = time.clock()
from openpyxl.compat import range
from openpyxl.styles import Font, Style, PatternFill, Color, colors, fills
red_fill = PatternFill(fill_type='solid', fgColor=Color(colors.RED), bgColor=Color(colors.RED))
empty_fill = PatternFill()
wb = openpyxl.workbook.Workbook(optimized_write=True)
ws = wb.create_sheet()
ws.title = 'Test 1'
for row in range(ROWS):
_row = []
for col in range(COLUMNS):
cell = {'value':1}
font = {}
fill = empty_fill
if formatData[row][col] & BOLD:
font['bold'] = True
if formatData[row][col] & ITALIC:
font['italic'] = True
if formatData[row][col]& UNDERLINE:
font['underline'] = 'single'
if formatData[row][col] & RED_BG:
fill = red_fill
cell['style'] = Style(font=Font(**font), fill=fill)
_row.append(cell)
ws.append(_row)
wb.save(get_output_path('test_openpyxl_style.xlsx'))
elapsed = time.clock() - stime
print("openpyxl optimised style, %s, %s, %s" % (ROWS, COLUMNS, elapsed))
return elapsed
def import_excel(user, filepath):
wb = load_workbook(filepath)
ws = wb.get_sheet_names()
ws = wb.get_sheet_by_name(ws[0])
headers = [
'TRAN_CODE', 'REMARK1', 'REMARK2', 'REMARK3', 'REMARK4', 'BODYXML'
]
lists = []
for row in range(4, 5):
r = {}
for col in range(1, len(headers) + 1):
key = headers[col - 1]
r[key] = ws.cell(row=row, column=col).value
lists.append(r)
sqllist = []
for cell in lists:
# for header in headers:
TRAN_CODE = cell['TRAN_CODE']
REMARK1 = cell['REMARK1']
REMARK2 = cell['REMARK2']
REMARK3 = cell['REMARK3']
REMARK4 = cell['REMARK4']
BODYXML = cell['BODYXML']
sql = ForeignDataTestCase(TRAN_CODE=TRAN_CODE,
REMARK1=REMARK1,
REMARK2=REMARK2,
REMARK3=REMARK3,
REMARK4=REMARK4,
BODYXML=BODYXML,
editor=user)
sqllist.append(sql)
ForeignDataTestCase.objects.bulk_create(sqllist)
def _cells_by_col(self, min_col, min_row, max_col, max_row):
"""
Get cells by column
"""
for column in range(min_col, max_col+1):
yield tuple(self.cell(row=row, column=column)
for row in range(min_row, max_row+1))
def get_squared_range(self, min_col, min_row, max_col, max_row):
"""
The source worksheet file may have columns or rows missing.
Missing cells will be created.
"""
if max_col is not None:
empty_row = tuple(EMPTY_CELL for column in range(min_col, max_col + 1))
else:
empty_row = []
row_counter = min_row
p = iterparse(self.xml_source, tag=[ROW_TAG], remove_blank_text=True)
for _event, element in p:
if element.tag == ROW_TAG:
row_id = int(element.get("r", row_counter))
# got all the rows we need
if max_row is not None and row_id > max_row:
break
# some rows are missing
for row_counter in range(row_counter, row_id):
row_counter += 1
yield empty_row
# return cells from a row
if min_row <= row_id:
yield tuple(self._get_row(element, min_col, max_col, row_counter=row_counter))
row_counter += 1
element.clear()
def run_pyexcelerate_style_cheating():
wb = Workbook()
stime = time.clock()
ws = wb.new_sheet('Test 1')
cell_formats = []
for i in range(16):
cell_format = Style()
if i & BOLD:
cell_format.font.bold = True
if i & ITALIC:
cell_format.font.italic = True
if i & UNDERLINE:
cell_format.font.underline = True
if i & RED_BG:
cell_format.fill.background = Color(255, 0, 0)
cell_formats.append(cell_format)
for row in range(ROWS):
for col in range(COLUMNS):
ws.set_cell_value(row + 1, col + 1, 1)
ws.set_cell_style(row + 1, col + 1, cell_formats[formatData[row][col]])
wb.save(get_output_path('test_pyexcelerate_style_fastest.xlsx'))
elapsed = time.clock() - stime
print("pyexcelerate style cheating, %s, %s, %s" % (ROWS, COLUMNS, elapsed))
return elapsed
def test_dump_string_table():
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
letters = [get_column_letter(x + 1) for x in range(10)]
for row in range(5):
ws.append(['%s%d' % (letter, row + 1) for letter in letters])
table = list(wb.shared_strings)
assert table == [
'A1',
'B1',
'C1',
'D1',
'E1',
'F1',
'G1',
'H1',
'I1',
'J1',
'A2',
'B2',
'C2',
'D2',
'E2',
'F2',
'G2',
'H2',
'I2',
'J2',
'A3',
'B3',
'C3',
'D3',
'E3',
'F3',
'G3',
'H3',
'I3',
'J3',
'A4',
'B4',
'C4',
'D4',
'E4',
'F4',
'G4',
'H4',
'I4',
'J4',
'A5',
'B5',
'C5',
'D5',
'E5',
'F5',
'G5',
'H5',
'I5',
'J5',
]
def get_squared_range(self, min_col, min_row, max_col, max_row):
"""
The source worksheet file may have columns or rows missing.
Missing cells will be created.
"""
if max_col is not None:
expected_columns = [get_column_letter(ci) for ci in range(min_col, max_col)]
else:
expected_columns = []
row_counter = min_row
# get cells row by row
for row, cells in groupby(self.get_cells(min_row, min_col,
max_row, max_col),
operator.attrgetter('row')):
full_row = []
if row_counter < row:
# Rows requested before those in the worksheet
for gap_row in range(row_counter, row):
yield tuple(EMPTY_CELL for column in expected_columns)
row_counter = row
if expected_columns:
retrieved_columns = dict([(c.column, c) for c in cells])
for column in expected_columns:
if column in retrieved_columns:
cell = retrieved_columns[column]
full_row.append(cell)
else:
# create missing cell
full_row.append(EMPTY_CELL)
else:
full_row = tuple(cells)
row_counter = row + 1
yield tuple(full_row)
def run_xlsxwriter_style():
try:
import xlsxwriter.workbook
except ImportError:
raise Exception('XlsxWriter not installled')
stime = time.clock()
wb = xlsxwriter.workbook.Workbook(get_output_path('test_xlsxwriter_style.xlsx'), {'constant_memory': True})
ws = wb.add_worksheet()
for row in range(ROWS):
for col in range(COLUMNS):
format = wb.add_format()
if formatData[row][col] & BOLD:
format.set_bold()
if formatData[row][col] & ITALIC:
format.set_italic()
if formatData[row][col] & UNDERLINE:
format.set_underline()
if formatData[row][col] & RED_BG:
format.set_bg_color('red')
ws.write_number(row, col, 1, format)
wb.close()
elapsed = time.clock() - stime
print("xlsxwriter style, %s, %s, %s" % (ROWS, COLUMNS, elapsed))
return elapsed
def record(self, data):
from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.utils import get_column_letter
wb = Workbook()
dest_filename = 'empty_book.xlsx'
ws1 = wb.active
ws1.title = "range names"
for row in range(1, 40):
ws1.append([1,2,3,4,5])
ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 3.14
ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
for col in range(27, 54):
_ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
print(ws3['AA10'].value)
wb.save(filename = dest_filename)
def engine():
"""
Collect and collate the engines within the site
"""
ws9 = wb.create_sheet(title='Engines')
engine_cols = [
'customProperties', 'listenerPorts', 'autosaveInterval',
'documentTimeout', 'documentDirectory', 'workingSetSizeLoPct',
'workingSetSizeHiPct', 'cpuThrottlePercentage', 'maxCoreMaskPersisted',
'maxCoreMask', 'maxCoreMaskHiPersisted', 'maxCoreMaskHi',
'objectTimeLimitSec', 'exportTimeLimitSec', 'reloadTimeLimitSec',
'hyperCubeMemoryLimit', 'exportMemoryLimit', 'reloadMemoryLimit',
'hostname', 'name'
]
enginenodes = get_qlik_sense.get_engine()
num_of_engines = len(enginenodes)
r = 1
for item in engine_cols:
ws9.cell(row=1, column=r).value = item
r += 1
for item in range(num_of_engines):
r = 1
for metric in range(len(enginenodes[item])):
ws9.cell(row=item + 2, column=r).value = str(
(enginenodes[item][metric]))
r += 1
def get_cid():
output_bool = False
counter = 0
sys_num = ""
cid = sheet['F' + str((curr_row - final_pair_count))].value
if isinstance(cid, int):
cid = str(cid)
print(type(cid))
for char in range(0, 3):
sys_num += cid[char]
else:
for char in range(0, len(cid)):
if cid[char].isdigit() and counter < 3:
sys_num += cid[char]
counter += 1
if len(cid) > 3 and cid[char - counter] == "L" and cid[
char + 1].isdigit() and counter == 3:
sys_num = sys_num[1:] + cid[char + 1]
sys_num = "Sys " + sys_num
for val in range(int(output_curr_row),
int(output_curr_row + (final_pair_count / 2))):
if len(sys_num) != 7 and output_bool == False:
logs.write("Check Sys number for this pair.\n")
output_bool = True
newSheet['P' + str(val)].value = sys_num
def virtual_proxy():
ws12 = wb.create_sheet(title='Virtual Proxy')
virtualproxy_cols = [
'description', 'prefix', 'authenticationModuleRedirectUri',
'sessionModuleBaseUri', 'loadBalancingModuleBaseUri',
'authenticationMethod', 'headerAuthenticationMode',
'headerAuthenticationHeaderName',
'headerAuthenticationStaticUserDirectory',
'headerAuthenticationDynamicUserDirectory', 'anonymousAccessMode',
'windowsAuthenticationEnabledDevicePattern', 'sessionCookieHeaderName',
'sessionCookieDomain', 'additionalResponseHeaders',
'sessionInactivityTimeout', 'extendedSecurityEnvironment',
'websocketCrossOriginWhiteList', 'defaultVirtualProxy', 'tags',
'samlMetadataIdP', 'samlHostUri', 'samlEntityId',
'samlAttributeUserId', 'samlAttributeUserDirectory',
'samlAttributeSigningAlgorithm', 'samlAttributeMap',
'magicLinkHostUri', 'magicLinkFriendlyName', 'name'
]
virtualproxynodes = get_qlik_sense.get_virtualproxy()
num_of_virtualproxys = len(virtualproxynodes)
r = 1
for item in virtualproxy_cols:
ws12.cell(row=1, column=r).value = item
r += 1
for item in range(num_of_virtualproxys):
r = 1
for metric in range(len(virtualproxynodes[item])):
ws12.cell(row=item + 2, column=r).value = str(
(virtualproxynodes[item][metric]))
r += 1
def _cells_by_col(self, min_col, min_row, max_col, max_row):
"""
Get cells by column
"""
for column in range(min_col, max_col+1):
yield tuple(self.cell(row=row, column=column)
for row in range(min_row, max_row+1))
def OutputGrid(Folder, Rows, Cols, BurntCells_Set, Sim, spotting, verbose,
cellsize):
if spotting == True:
Folder = os.path.join(Folder, "ForestGridsSpotting")
else:
Folder = os.path.join(Folder, "ForestGrids")
if not os.path.exists(Folder):
os.makedirs(Folder)
filed = open(os.path.join(Folder, "ForestGrid" + str(Sim) + ".csv"), "w")
filed.write("ncols " + str(Cols) + "\n")
filed.write("nrows " + str(Rows) + "\n")
filed.write("xllcorner 457900\n")
filed.write("yllcorner 5716800\n")
filed.write("cellsize " + str(cellsize) + "\n")
filed.write("NODATA_value -9999\n")
NcellAux = 1
for r in range(1, Rows + 1):
for c in range(1, Cols + 1):
if NcellAux in BurntCells_Set and c < Cols:
filed.write("1 ")
if NcellAux not in BurntCells_Set and c < Cols:
filed.write("0 ")
if NcellAux in BurntCells_Set and c == Cols:
filed.write("1\n")
if NcellAux not in BurntCells_Set and c == Cols:
filed.write("0\n")
NcellAux += 1
def get_squared_range(self, min_col, min_row, max_col, max_row):
"""
The source worksheet file may have columns or rows missing.
Missing cells will be created.
"""
if max_col is not None:
expected_columns = [get_column_letter(ci) for ci in range(min_col, max_col + 1)]
else:
expected_columns = []
row_counter = min_row
# get cells row by row
for row, cells in groupby(self.get_cells(min_row, min_col,
max_row, max_col),
operator.attrgetter('row')):
full_row = []
if row_counter < row:
# Rows requested before those in the worksheet
for gap_row in range(row_counter, row):
yield tuple(EMPTY_CELL for column in expected_columns)
row_counter = row
if expected_columns:
retrieved_columns = dict([(c.column, c) for c in cells])
for column in expected_columns:
if column in retrieved_columns:
cell = retrieved_columns[column]
full_row.append(cell)
else:
# create missing cell
full_row.append(EMPTY_CELL)
else:
full_row = tuple(cells)
row_counter = row + 1
yield tuple(full_row)
def exportResolve(fileName):
if not os.path.exists("output"):
os.makedirs("output")
disPattern = re.compile("(\xa0){0,}(?P<dis>[0-9/]+).*")
savePath = "output/" + fileName
b = Workbook()
s = b.active
resolve = dbm.getAllResolve()
for case in resolve:
case['Disposition Date'] = disPattern.match(case['Disposition']).group(2)
dataCounts = len(resolve)
if dataCounts == 0:
return False
keys = sorted(resolve[0].keys())
keySize = len(keys)
# Write column head
for col_index in range(1, keySize + 1):
s["%s1"%get_column_letter(col_index)] = keys[col_index - 1]
# Write data from second row
i = 0
for row_index in range(2, dataCounts + 2):
# Reduce index calculation when access a data
data = resolve[i]
for col_index in range(1, keySize + 1):
s["%s%s"%(get_column_letter(col_index), row_index)] = \
data[keys[col_index - 1]]
i += 1
b.save(filename = savePath)
return True
def get_squared_range(self, min_col, min_row, max_col, max_row):
"""
The source worksheet file may have columns or rows missing.
Missing cells will be created.
"""
if max_col is not None:
empty_row = tuple(EMPTY_CELL for column in range(min_col, max_col + 1))
else:
expected_columns = []
row_counter = min_row
p = iterparse(self.xml_source, tag=[ROW_TAG], remove_blank_text=True)
for _event, element in p:
if element.tag == ROW_TAG:
row_id = int(element.get("r"))
# got all the rows we need
if max_row is not None and row_id > max_row:
break
# some rows are missing
for row_counter in range(row_counter, row_id):
yield empty_row
# return cells from a row
if min_row <= row_id:
yield tuple(self._get_row(element, min_col, max_col))
row_counter += 1
if element.tag in (CELL_TAG, VALUE_TAG, FORMULA_TAG):
# sub-elements of rows should be skipped as handled within a cell
continue
element.clear()
def anchor(self):
""" returns the expected position of a cell in pixels from the top-left
of the sheet. For example, A1 anchor should be (0,0).
:type: tuple(int, int)
"""
left_columns = (column_index_from_string(self.column) - 1)
column_dimensions = self.parent.column_dimensions
left_anchor = 0
default_width = points_to_pixels(DEFAULT_COLUMN_WIDTH)
for col_idx in range(left_columns):
letter = get_column_letter(col_idx + 1)
if letter in column_dimensions:
cdw = column_dimensions.get(letter).width or default_width
if cdw > 0:
left_anchor += points_to_pixels(cdw)
continue
left_anchor += default_width
row_dimensions = self.parent.row_dimensions
top_anchor = 0
top_rows = (self.row - 1)
default_height = points_to_pixels(DEFAULT_ROW_HEIGHT)
for row_idx in range(1, top_rows + 1):
if row_idx in row_dimensions:
rdh = row_dimensions[row_idx].height or default_height
if rdh > 0:
top_anchor += points_to_pixels(rdh)
continue
top_anchor += default_height
return (left_anchor, top_anchor)
def top_align_cells():
#Iterate in the 2D array of the sheet skipping top row and far right col (Thus the indexes)
for j in range(1, sheet.max_column - 1):
for k in range(2, sheet.max_row + 1):
#Set the alignment for cells in the given range above k is row; j is col
sheet.cell(row=k, column=j).alignment = sheet.cell(
row=k, column=j).alignment.copy(horizontal='left',
vertical='top')
def xlsx_example():
# print("make_xlsx_example")
wb = Workbook()
dest_filename = 'xlsx_example.xlsx'
ws1 = wb.active
ws1.title = "Sheet example"
for row in range(1, 20):
ws1.append(range(600))
wb.save(filename=dest_filename)
def cells_from_range(range_string):
"""
Get individual addresses for every cell in a range.
Yields one row at a time.
"""
min_col, min_row, max_col, max_row = range_boundaries(range_string)
for row in range(min_row, max_row+1):
yield tuple('%s%d' % (get_column_letter(col), row)
for col in range(min_col, max_col+1))
def write_data(table): wb = Workbook() ws = wb.active ws.title = "Result" dest_filename = 'Rota.xlsx' for i in range(len(table)): for j in range(len(table[i])): ws.cell(row=i+1,column=j+1,value=table[i][j]) wb.save(filename = dest_filename)
def set_course_list(self,stu): stu_table = stu.get_table() for i in range(len(stu_table)): for j in range(len(stu_table[i])): if stu_table[i][j] == None: for course in self.__course_list: if course.get_x() == i and course.get_y() == j: course.add_student(stu) break
def create_table(ws,
column_names,
columns,
title=None,
bold_first_column=True,
selected_rows=None,
selected_columns=None):
if selected_rows is None:
selected_rows = []
if selected_columns is None:
selected_columns = []
bold_ft = Font(bold=True)
fill = PatternFill(fill_type='solid',
start_color='FF27E85B',
end_color='FF27E85B')
#prepare data
formated_columns = []
for column in columns:
formated_column = []
for value in column:
if isinstance(value, int):
formated_column.append("{:,}".format(value))
elif isinstance(value, float):
formated_column.append("%.4f" % value)
else:
formated_column.append(value)
formated_columns.append(formated_column)
if title:
ws.append([title])
ws.cell(row=ws.max_row, column=1).font = bold_ft
ws.append(column_names)
for i in range(len(column_names)):
ws.cell(row=ws.max_row, column=i + 1).font = bold_ft
if not formated_columns:
return
for i in range(len(formated_columns[0])):
ws.append([column[i] for column in formated_columns])
if bold_first_column:
ws.cell(row=ws.max_row, column=1).font = bold_ft
if i in selected_rows:
for j in range(len(formated_columns)):
ws.cell(row=ws.max_row, column=j + 1).fill = fill
for column_ind in selected_columns:
ws.cell(row=ws.max_row, column=column_ind + 1).fill = fill
ws.append([])
def calculate_distance(location):
distances = []
for i in range(len(location)):
for j in range(len(location)):
x_distance = abs(location[i][0] - location[j][0])
y_distance = abs(location[i][1] - location[j][1])
distance = math.sqrt((x_distance * x_distance) +
(y_distance * y_distance))
distances.append(distance)
distances = np.reshape(distances, (len(location), len(location)))
return distances
def run_pyexcelerate_value_fast():
wb = Workbook()
stime = time.clock()
ws = wb.new_sheet('Test 1')
for row in range(ROWS):
for col in range(COLUMNS):
ws[row + 1][col + 1].value = 1
wb.save(get_output_path('test_pyexcelerate_value_fast.xlsx'))
elapsed = time.clock() - stime
print("pyexcelerate value fast, %s, %s, %s" % (ROWS, COLUMNS, elapsed))
return elapsed
def getMergedCellValue(sheet, irange, connector=''):
ret = []
min_col, min_row, max_col, max_row = range_boundaries(irange)
for row in range(min_row, max_row + 1):
for col in range(min_col, max_col + 1):
v = sheet.cell(row=row, column=col).value
if v is not None:
v = str(v).strip()
if len(v) == 0: continue
ret.append(str(v))
return connector.join(ret)
def auto_width():
for x in range(0, len(Projects)):
ws = wb.get_sheet_by_name(Projects[x])
end_column_ws = ws.get_highest_column()
end_row_ws = ws.get_highest_row()
column_widths = [0] * (end_column_ws)
for i in range (1, end_column_ws + 1):
for j in range(1, end_row_ws + 1):
if len(str((ws.cell(column = i, row = j)).value)) > column_widths[i - 1]:
column_widths[i-1] = len(str(ws.cell(column = i, row = j).value))
ws.column_dimensions[get_column_letter(i)].width = (column_widths[i - 1] + 3)
ws.column_dimensions[get_column_letter(PO_Titles.index('Receipt Date') + 1)].width = 12
def _getHeaderColumnfunc(InputDataSheetPath,InputsheetName='Sheet1'):
columnheader =[]
workbook = xlrd.open_workbook(InputDataSheetPath)
sheet = workbook.sheet_by_name(InputsheetName)
rows = sheet.nrows
print "Number of rows",rows
columns = sheet.ncols
for i in range(1):
for j in range(columns):
columnheader.append(sheet.cell(i,j).value)
return columnheader
workbook.release_resources()
def write(self,data):
""" Take a list of row and save into a file """
out = None
if(self.otype=='pkl'):
out = pickle.dumps(data)
with open(self.ofull_name,'wb') as f:
f.write(out)
if(self.otype=='xls'):
wb = Workbook()
ws = wb.active
ws.title = "range names"
for col_idx in range(0, len(data)):
col = get_column_letter(col_idx+1)
for row in range(0, len(data[col_idx])):
ws.cell('%s%s'%(col, row+1)).value = data[col_idx][row]
ws = wb.create_sheet()
ws.title = 'Pi'
ws['F5'] = 3.14
wb.save(filename = self.ofull_name)
if(self.otype=='docx'):
document = Document()
document.add_heading(self.ofull_name, 0)
table = document.add_table(rows=0, cols=len(data))
#hdr_cells = table.rows[0].cells
#hdr_cells[0].text = 'Qty'
#hdr_cells[1].text = 'Id'
#hdr_cells[2].text = 'Desc'
for row in data:
row_cells = table.add_row().cells
for col_idx in range(len(row)):
row_cells[col_idx].text = str(row[col_idx])
document.add_page_break()
document.save(self.ofull_name)
if(self.otype=='xls'):
pass
if(self.otype=='xls'):
pass
print 'Success:Write'
#test code..
#d =[['a','b','c','d'],['a','b','c','d'],['a','b','c','d'],['a','b','c','d']]
#b = BackUp('hello','docx')
#b.write(d)
#print b.read()
def writeWorkbook():
wb = Workbook()
dest_filename = r'pyxl_outfile.xlsx'
ws1 = wb.create_sheet()
ws1.title = 'pi'
ws1['A1'] = 3.54
ws2 = wb.create_sheet()
ws2.title = 'ranges'
for col_idx in range(1,10):
col = get_column_letter(col_idx)
for row in range(1,10):
ws2.cell('%s%s'%(col,row)).value = '%s%s' % (col,row)
wb.save(filename = dest_filename)
def _clean_merge_range(self, cr):
"""
Remove all but the top left-cell from a range of merged cells
"""
min_col, min_row, max_col, max_row = cr.bounds
rows = range(min_row, max_row+1)
cols = range(min_col, max_col+1)
cells = product(rows, cols)
for c in islice(cells, 1, None):
if c in self._cells:
del self._cells[c]
def create():
"""Create new xlsx file and write something in it."""
wb = Workbook()
ws = wb.active
ws.title = "data"
for col_idx in range(1, 40):
col = get_column_letter(col_idx)
for row in range(1, 600):
ws.cell('%s%s' % (col, row)).value = '%s%s' % (col, row)
ws = wb.create_sheet()
ws.title = 'Pi'
ws['F5'] = 3.14
wb.save(filename=filename)
def _clean_merge_range(self, cr):
"""
Remove all but the top left-cell from a range of merged cells
"""
min_col, min_row, max_col, max_row = cr.bounds
rows = range(min_row, max_row+1)
cols = range(min_col, max_col+1)
cells = product(rows, cols)
for c in islice(cells, 1, None):
if c in self._cells:
del self._cells[c]
def randomInitialize(self):
# Fill it randomly
for randCours in range(len(courses)):
for repT in range(courses[randCours].timesInWeek):
if len(courses[randCours].presentors) > 0:
randInst = randE(courses[randCours].presentors)
rs = randint(0, self.scheduleSize - 1)
while self.schedule[rs][0] != -1:
rs = randint(0, self.scheduleSize - 1)
self.schedule[rs] = (randInst, randCours)
self.fitnessCalculation()
def writeCsv(path):
with open(path,"w", newline='') as file:
writer = csv.writer(file, dialect='excel')
#写表头
header = ["CurveName", "RateType", "ReportingDate", "TermBase", "Term", "Rate"]
writer.writerows([header])
#写ABS数据
for i in range(2,7):
writer.writerows([["ABS", "SpotRate", text[1], "D", text[i], text[i+6] ]])
#写RMBS数据
for j in range(2,7):
writer.writerows([["RMBS", "SpotRate", text[1], "D", text[j], text[j+12] ]])
def calculate(solution):
if solution is None or len(solution) == 0:
return -999999
prof = 0
for i in range(len(solution)):
cost = _distance[solution[i]][solution[
(i + 1) % len(solution)]] * _travel_cost
prof += (_nodes[solution[(i + 1) % len(solution)]][2] - cost)
if _penality is True:
nodes = []
[nodes.append(x) for x in range(_n_cities) if x not in solution]
for i in nodes:
prof -= _nodes[i][2]
return prof
def __init__(self, table): __sta_x = 1 __sta_y = 1 for i in range(len(table)): for j in range(len(table[i])): if table[i][j].value == 'sta': __sta_x = i __sta_y = j break for i in range(__sta_x+1,len(table)): for j in range(__sta_y+1,len(table[i])): if table[i][j].value == 1: self.__course_list.append(Course(i-__sta_x,j-__sta_y)) self.duty_course_list.add((i-__sta_x,j-__sta_y))
def test_dump_string_table():
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
letters = [get_column_letter(x + 1) for x in range(10)]
for row in range(5):
ws.append(['%s%d' % (letter, row + 1) for letter in letters])
table = list(wb.shared_strings)
assert table == ['A1', 'B1', 'C1', 'D1', 'E1', 'F1', 'G1', 'H1', 'I1', 'J1',
'A2', 'B2', 'C2', 'D2', 'E2', 'F2', 'G2', 'H2', 'I2', 'J2',
'A3', 'B3', 'C3', 'D3', 'E3', 'F3', 'G3', 'H3', 'I3', 'J3',
'A4', 'B4', 'C4', 'D4', 'E4', 'F4', 'G4', 'H4', 'I4', 'J4',
'A5', 'B5', 'C5', 'D5', 'E5', 'F5', 'G5', 'H5', 'I5', 'J5',
]
def create_table(ws, column_names, columns, title=None, bold_first_column=True, selected_rows=None, selected_columns=None):
if selected_rows is None:
selected_rows = []
if selected_columns is None:
selected_columns = []
bold_ft = Font(bold=True)
fill = PatternFill(fill_type='solid',
start_color='FF27E85B',
end_color='FF27E85B')
#prepare data
formated_columns = []
for column in columns:
formated_column = []
for value in column:
if isinstance(value, int):
formated_column.append("{:,}".format(value))
elif isinstance(value, float):
formated_column.append("%.4f" % value)
else:
formated_column.append(value)
formated_columns.append(formated_column)
if title:
ws.append([title])
ws.cell(row=ws.max_row, column=1).font = bold_ft
ws.append(column_names)
for i in range(len(column_names)):
ws.cell(row=ws.max_row, column=i+1).font = bold_ft
if not formated_columns:
return
for i in range(len(formated_columns[0])):
ws.append([column[i] for column in formated_columns])
if bold_first_column:
ws.cell(row=ws.max_row, column=1).font = bold_ft
if i in selected_rows:
for j in range(len(formated_columns)):
ws.cell(row=ws.max_row, column=j+1).fill = fill
for column_ind in selected_columns:
ws.cell(row=ws.max_row, column=column_ind+1).fill = fill
ws.append([])
def write(self, data):
""" Take a list of row and save into a file """
out = None
if (self.otype == 'pkl'):
out = pickle.dumps(data)
with open(self.ofull_name, 'wb') as f:
f.write(out)
if (self.otype == 'xls'):
wb = Workbook()
ws = wb.active
ws.title = "range names"
for col_idx in range(0, len(data)):
col = get_column_letter(col_idx + 1)
for row in range(0, len(data[col_idx])):
ws.cell('%s%s' % (col, row + 1)).value = data[col_idx][row]
ws = wb.create_sheet()
ws.title = 'Pi'
ws['F5'] = 3.14
wb.save(filename=self.ofull_name)
if (self.otype == 'docx'):
document = Document()
document.add_heading(self.ofull_name, 0)
table = document.add_table(rows=0, cols=len(data))
#hdr_cells = table.rows[0].cells
#hdr_cells[0].text = 'Qty'
#hdr_cells[1].text = 'Id'
#hdr_cells[2].text = 'Desc'
for row in data:
row_cells = table.add_row().cells
for col_idx in range(len(row)):
row_cells[col_idx].text = str(row[col_idx])
document.add_page_break()
document.save(self.ofull_name)
if (self.otype == 'xls'):
pass
if (self.otype == 'xls'):
pass
print 'Success:Write'
#test code..
#d =[['a','b','c','d'],['a','b','c','d'],['a','b','c','d'],['a','b','c','d']]
#b = BackUp('hello','docx')
#b.write(d)
#print b.read()
def run_xlsxwriter_value():
try:
import xlsxwriter.workbook
except ImportError:
raise Exception('XlsxWriter not installled')
stime = time.clock()
wb = xlsxwriter.workbook.Workbook(get_output_path('test_xlsxwriter.xlsx'), {'constant_memory': True})
ws = wb.add_worksheet()
for row in range(ROWS):
for col in range(COLUMNS):
ws.write_number(row, col, 1)
wb.close()
elapsed = time.clock() - stime
print("xlsxwriter value, %s, %s, %s" % (ROWS, COLUMNS, elapsed))
return elapsed