def export(self, workbook, gwells_zip, worksheet_name, cursor):
logger.info('exporting {}'.format(worksheet_name))
worksheet = workbook.create_sheet(worksheet_name)
csv_file = '{}.csv'.format(worksheet_name)
if os.path.exists(csv_file):
os.remove(csv_file)
with open(csv_file, 'w') as csvfile:
csvwriter = csv.writer(csvfile, dialect='excel')
values = []
cells = []
# Write the headings
for index, field in enumerate(cursor.description):
if isinstance(field, tuple):
fieldName = field[0]
else:
fieldName = field.name
values.append(fieldName)
cell = WriteOnlyCell(worksheet, value=fieldName)
cell.font = Font(bold=True)
cells.append(cell)
columns = len(values)
for index, value in enumerate(values):
worksheet.column_dimensions[get_column_letter(index+1)].width = len(value) + 2
worksheet.append(cells)
csvwriter.writerow(values)
# Write the values
row_index = 0
for row, record in enumerate(cursor.fetchall()):
values = []
num_values = 0
for col, value in enumerate(record):
if not (value == "" or value is None):
num_values += 1
if type(value) is str:
# There are lots of non-printable characters in the source data that can cause
# issues in the export, so we have to clear them out.
v = ''.join([s for s in value if s in string.printable])
# We can't have something starting with an = sign,
# it would be interpreted as a formula in excel.
if v.startswith('='):
v = '\'{}'.format(v)
values.append(v)
else:
values.append(value)
if num_values > 1:
# We always have a well_tag_number, but if that's all we have, then just skip this record
row_index += 1
csvwriter.writerow(values)
worksheet.append(values)
filter_reference = 'A1:{}{}'.format(get_column_letter(columns), row_index+1)
worksheet.auto_filter.ref = filter_reference
gwells_zip.write(csv_file)
if os.path.exists(csv_file):
os.remove(csv_file)
def to_cell(self, ws):
if self.bg is None and self.w is None:
return self.val
else:
cell = WriteOnlyCell(ws, self.val)
if self.bg is not None:
cell.fill = self.bg
return cell
def save_xlsx_template(file_path, data, header=None, col_id=None):
"""将输入保存到 Excel 文件中。使用文件模板
全部保存为文本。
Args:
file_path (str): xlsx 文件的路径
data (list[list]): 要保存的数据，二维
header (list): 第一行
col_id (list[int]): data 中列号到 xlsx 中列号的映射
"""
if len(data) <= 0:
return save_xlsx(file_path, data, header, col_id)
cd = os.path.dirname(os.path.abspath(__file__))
num_col = len(data[0])
if num_col == 9:
shutil.copy(os.path.join(cd, 'data/prototype_list.xlsx'), file_path)
elif num_col == 12:
shutil.copy(os.path.join(cd, 'data/prototype_pair.xlsx'), file_path)
elif num_col == 8:
shutil.copy(os.path.join(cd, 'data/prototype_ui.xlsx'), file_path)
else:
return save_xlsx(file_path, data, header, col_id)
if col_id is None:
col_id = list(range(0, num_col))
max_col_id = max(col_id)
workbook = openpyxl.load_workbook(file_path)
sheet = workbook['sheet 1']
# 格式
fonts = [copy.copy(sheet.cell(row=1, column=i + 1).font) for i in col_id]
fills = [copy.copy(sheet.cell(row=1, column=i + 1).fill) for i in col_id]
alignments = [copy.copy(sheet.cell(row=1, column=i + 1).alignment) for i in col_id]
number_formats = [sheet.cell(row=1, column=i + 1).number_format for i in col_id]
# 写入内容
row_id = 1
for row in data:
row_cells = ['', ] * (max_col_id + 1)
for j in range(0, num_col):
cell = WriteOnlyCell(sheet, value=str(row[j]))
cell.font = fonts[j]
cell.fill = fills[j]
cell.alignment = alignments[j]
cell.number_format = number_formats[j]
row_cells[col_id[j]] = cell
sheet.append(row_cells)
row_id += 1
workbook.save(file_path)
def _write_row(self, sheet_index, row):
from couchexport.export import FormattedRow
sheet = self.tables[sheet_index]
# Source: http://stackoverflow.com/questions/1707890/fast-way-to-filter-illegal-xml-unicode-chars-in-python
dirty_chars = re.compile(
'[\x00-\x08\x0b-\x1f\x7f-\x84\x86-\x9f\ud800-\udfff\ufdd0-\ufddf\ufffe-\uffff]'
)
def get_write_value(value):
if isinstance(value, six.integer_types + (float, )):
return value
if isinstance(value, bytes):
value = value.decode('utf-8')
elif value is not None:
value = six.text_type(value)
else:
value = ''
return dirty_chars.sub('?', value)
write_values = [get_write_value(val) for val in row]
cells = [WriteOnlyCell(sheet, val) for val in write_values]
if self.format_as_text:
for cell in cells:
cell.number_format = numbers.FORMAT_TEXT
if isinstance(row, FormattedRow):
for hyperlink_column_index in row.hyperlink_column_indices:
cells[hyperlink_column_index].hyperlink = cells[
hyperlink_column_index].value
cells[hyperlink_column_index].style = 'Hyperlink'
sheet.append(cells)
def test_openpyxl_lxml():
book = Workbook(write_only=True)
sheet1 = book.create_sheet("Sheet1")
for r in range(NUM_ROWS):
row = [DATA[(r * c) % len(DATA)] for c in range(NUM_COLS)]
cells = [WriteOnlyCell(sheet1, value=v) for v in row]
sheet1.append(cells)
book.save("_tests/test.xlsx")
def save_xlsx_template(file_path, data, header=None, col_id=None):
"""将输入保存到 Excel 文件中。使用文件模板
全部保存为文本。
Args:
file_path (str): xlsx 文件的路径
data (list[list]): 要保存的数据，二维
header (list): 第一行
col_id (list[int]): data 中列号到 xlsx 中列号的映射
"""
if len(data) <= 0:
return save_xlsx(file_path, data, header, col_id)
cd = os.path.dirname(os.path.abspath(__file__))
num_col = len(data[0])
if num_col == 9:
shutil.copy(os.path.join(cd, 'data/prototype_list.xlsx'), file_path)
elif num_col == 12:
shutil.copy(os.path.join(cd, 'data/prototype_pair.xlsx'), file_path)
elif num_col == 8:
shutil.copy(os.path.join(cd, 'data/prototype_ui.xlsx'), file_path)
else:
return save_xlsx(file_path, data, header, col_id)
if col_id is None:
col_id = list(range(0, num_col))
max_col_id = max(col_id)
workbook = openpyxl.load_workbook(file_path)
sheet = workbook['sheet 1']
# 格式
fonts = [copy.copy(sheet.cell(row=1, column=i + 1).font) for i in col_id]
fills = [copy.copy(sheet.cell(row=1, column=i + 1).fill) for i in col_id]
alignments = [
copy.copy(sheet.cell(row=1, column=i + 1).alignment) for i in col_id
]
number_formats = [
sheet.cell(row=1, column=i + 1).number_format for i in col_id
]
# 写入内容
row_id = 1
for row in data:
row_cells = [
'',
] * (max_col_id + 1)
for j in range(0, num_col):
cell = WriteOnlyCell(sheet, value=str(row[j]))
cell.font = fonts[j]
cell.fill = fills[j]
cell.alignment = alignments[j]
cell.number_format = number_formats[j]
row_cells[col_id[j]] = cell
sheet.append(row_cells)
row_id += 1
workbook.save(file_path)
def _write_row(self, sheet_index, row):
sheet = self.tables[sheet_index]
# Source: http://stackoverflow.com/questions/1707890/fast-way-to-filter-illegal-xml-unicode-chars-in-python
dirty_chars = re.compile(
'[\x00-\x08\x0b-\x1f\x7f-\x84\x86-\x9f\ud800-\udfff\ufdd0-\ufddf\ufffe-\uffff]'
)
def get_write_value(value):
if isinstance(value, six.integer_types + (float,)):
return value
if isinstance(value, six.binary_type):
value = six.text_type(value, encoding="utf-8")
elif value is not None:
value = six.text_type(value)
else:
value = ''
return dirty_chars.sub('?', value)
cells = [WriteOnlyCell(sheet, get_write_value(val)) for val in row]
if self.format_as_text:
for cell in cells:
cell.number_format = numbers.FORMAT_TEXT
sheet.append(cells)
from openpyxl import *
from openpyxl.worksheet.write_only import WriteOnlyCell
from openpyxl.styles import Font
workBook = Workbook(write_only=True)
workSheet = workBook.create_sheet()
cell = WriteOnlyCell(workSheet, value="Some data")
cell.font = Font(name='Arial', size=25)
workSheet.append([cell, 2, None])
workBook.save('myexcel.xlsx')
def write_xls(filename, tind_results, include_unchanged, all):
# Create some things we reuse below.
bold_style = Font(bold=True, underline="single")
hyperlink_style = Font(underline='single', color='0563C1')
error_style = Font(color='aa2222')
# Create a sheet in a new workbook and give it a distinctive style.
wb = openpyxl.Workbook(write_only=True)
sheet = wb.create_sheet()
sheet.title = 'Results'
sheet.sheet_properties.tabColor = 'f7ba0b'
# Set the widths of the different columngs to something more convenient.
column = get_column_letter(1)
sheet.column_dimensions[column].width = 15
for idx in range(2, _NUM_URLS * 2 + 2):
column = get_column_letter(idx)
sheet.column_dimensions[column].width = 80
# Set the headings and format them a little bit.
cell1 = WriteOnlyCell(sheet, value='TIND Identifier')
cell1.font = bold_style
row = [cell1]
for i in range(1, _NUM_URLS + 1):
cell = WriteOnlyCell(sheet, value='Original URL #{}'.format(i))
cell.font = bold_style
row.append(cell)
cell = WriteOnlyCell(sheet, value='Final URL #{}'.format(i))
cell.font = bold_style
row.append(cell)
# Write the header row.
sheet.append(row)
# Now create the data rows.
try:
for row_number, item in enumerate(tind_results, 2):
if not item:
if __debug__: log('no data -- stopping')
break
if not item.url_data and not all:
if __debug__:
log('no URLs for {} -- not saving'.format(item.id))
continue
if not contains_changed_urls(
item.url_data) and not (include_unchanged or all):
if __debug__:
log('URLs unchanged for {} -- skipping'.format(item.id))
continue
if __debug__: log('writing row {}'.format(row_number))
cell = WriteOnlyCell(sheet, value=item.id)
cell.value = hyperlink(tind_entry_url(item.id), item.id)
cell.font = hyperlink_style
row = [cell]
for url_data in item.url_data:
cell = WriteOnlyCell(sheet, value=url_data.original)
cell.value = hyperlink(url_data.original)
cell.font = hyperlink_style
row.append(cell)
if url_data.error:
cell = WriteOnlyCell(sheet,
value='(error: {})'.format(
url_data.error))
cell.font = error_style
else:
cell = WriteOnlyCell(sheet, value=url_data.final)
cell.value = hyperlink(url_data.final or '')
cell.font = hyperlink_style
row.append(cell)
sheet.append(row)
except KeyboardInterrupt:
msg('Interrupted -- closing "{}" and exiting'.format(filename))
except Exception:
raise
finally:
wb.save(filename=filename)
def append_row(self, sheet, values):
row = []
for value in values:
cell = WriteOnlyCell(sheet, value=self.prepare_value(value))
row.append(cell)
sheet.append(row)
#! python3
from openpyxl import Workbook
from openpyxl.comments import Comment
from openpyxl.worksheet.write_only import WriteOnlyCell
wb = Workbook(write_only=True)
ws = wb.create_sheet("i2o_thumbnails")
# columns dimensions
ws.column_dimensions["A"].width = 35
ws.column_dimensions["B"].width = 75
ws.column_dimensions["C"].width = 75
# headers
head_col1 = WriteOnlyCell(ws, value="isogeo_uuid")
head_col2 = WriteOnlyCell(ws, value="isogeo_title_slugged")
head_col3 = WriteOnlyCell(ws, value="img_abs_path")
# comments
comment = Comment(text="Do not modify worksheet structure", author="Isogeo")
head_col1.comment = head_col2.comment = head_col3.comment = comment
# styling
head_col1.style = head_col2.style = head_col3.style = "Headline 2"
# insert
ws.append((head_col1, head_col2, head_col3))
# realist fake
ws.append(("c4b7ad9732454beca1ab3ec1958ffa50", "title-slugged",
"resources/table.svg"))
def run():
# 创建句柄
wb = Workbook()
ws_w = wb.active
fpath = '.'
i = 1
files = []
# 获取fpath下所有的excel文件
for fname in os.listdir(fpath):
if fname.endswith(".xlsx"):
files.append(fname)
# 按照最后更改时间排序，一开始用getctime但是mac上有问题（一个文件下载然后复制到别的文件夹的时候，ctime是复制的时间），于是改成getmtime
files.sort(key=lambda x: os.path.getmtime(x))
for fname in files:
print(fname)
wb_r = load_workbook(filename=fname)
ws_r = wb_r.worksheets[0]
j = 1
for row in ws_r.rows:
new_cells = []
if i == 1: # 第一个文件，特殊处理，
for cell in row:
new_cell = WriteOnlyCell(ws_w, value=cell.value)
if cell.has_style:
new_cell.font = copy(cell.font)
new_cell.border = copy(cell.border)
new_cell.fill = copy(cell.fill)
new_cell.number_format = copy(cell.number_format)
new_cell.protection = copy(cell.protection)
new_cell.alignment = copy(cell.alignment)
new_cells.append(new_cell)
if j == 1:
ws_w.title = ws_r.title
ws_w.column_dimensions = ws_r.column_dimensions
ws_w.append(new_cells)
else: # 其他文件
if j >= 4:
for cell in row:
new_cell = WriteOnlyCell(ws_w, value=cell.value)
if cell.column == "A":
new_cell.value = (i - 1) * 10 + int(cell.value)
if cell.has_style:
new_cell.font = copy(cell.font)
new_cell.border = copy(cell.border)
new_cell.fill = copy(cell.fill)
new_cell.number_format = copy(cell.number_format)
new_cell.protection = copy(cell.protection)
new_cell.alignment = copy(cell.alignment)
new_cells.append(new_cell)
ws_w.append(new_cells)
j += 1
i += 1
today = time.strftime('%Y%m%d', time.localtime(time.time()))
wb.save('new_file_%s.xlsx' % today)