def write(self, writer, sheet_name='Sheet1', startrow=0,
startcol=0, freeze_panes=None, engine=None):
"""
writer : string or ExcelWriter object
File path or existing ExcelWriter
sheet_name : string, default 'Sheet1'
Name of sheet which will contain DataFrame
startrow :
upper left cell row to dump data frame
startcol :
upper left cell column to dump data frame
freeze_panes : tuple of integer (length 2), default None
Specifies the one-based bottommost row and rightmost column that
is to be frozen
engine : string, default None
write engine to use if writer is a path - you can also set this
via the options ``io.excel.xlsx.writer``, ``io.excel.xls.writer``,
and ``io.excel.xlsm.writer``.
"""
from pandas.io.excel import ExcelWriter
from pandas.io.common import _stringify_path
if isinstance(writer, ExcelWriter):
need_save = False
else:
writer = ExcelWriter(_stringify_path(writer), engine=engine)
need_save = True
formatted_cells = self.get_formatted_cells()
writer.write_cells(formatted_cells, sheet_name,
startrow=startrow, startcol=startcol,
freeze_panes=freeze_panes)
if need_save:
writer.save()
def test_sheets(self):
_skip_if_no_xlrd()
ext = self.ext
path = '__tmp_to_excel_from_excel_sheets__.' + ext
with ensure_clean(path) as path:
self.frame['A'][:5] = nan
self.frame.to_excel(path, 'test1')
self.frame.to_excel(path, 'test1', cols=['A', 'B'])
self.frame.to_excel(path, 'test1', header=False)
self.frame.to_excel(path, 'test1', index=False)
# Test writing to separate sheets
writer = ExcelWriter(path)
self.frame.to_excel(writer, 'test1')
self.tsframe.to_excel(writer, 'test2')
writer.save()
reader = ExcelFile(path)
recons = reader.parse('test1', index_col=0)
tm.assert_frame_equal(self.frame, recons)
recons = reader.parse('test2', index_col=0)
tm.assert_frame_equal(self.tsframe, recons)
np.testing.assert_equal(2, len(reader.sheet_names))
np.testing.assert_equal('test1', reader.sheet_names[0])
np.testing.assert_equal('test2', reader.sheet_names[1])
def save_resutls_to_excel(all_text_results, segments_results, excel_path='res.xlsx'):
import pandas
from collections import defaultdict
all_segment_metric_results = defaultdict(lambda: [])
for metric_cls in metric_classes:
metric_results_during_segments = \
[filter(lambda metric: isinstance(metric, metric_cls), seg_result)[0].get_results()
for seg_result in segments_results]
for result in metric_results_during_segments:
for key, value in result.items():
all_segment_metric_results[key].append(value)
all_metric_results_series = {}
for key, values in all_segment_metric_results.items():
all_metric_results_series[key] = pandas.Series(values, index=range(1, len(values) + 1))
df = pandas.DataFrame(all_metric_results_series)
all_text_metric_results = defaultdict(lambda: [])
for metric in all_text_results:
result = metric.get_results()
for key, value in result.items():
all_text_metric_results[key] = [value]
df2 = pandas.DataFrame(all_text_metric_results)
from pandas.io.excel import ExcelWriter
excel = ExcelWriter(excel_path)
df.to_excel(excel, 'segments')
df2.to_excel(excel, 'all')
excel.save()
def __init__(self, db_filename = "fbo_solicitations.xlsx",
report_prefix = "report",
sol_sheet_name = "solicitations",
filtered_sheet_name = "filtered_solicitations",
index_column = "sponsor_number",
report_only_new = True):
'''
Constructor
'''
if(not os.path.isfile(db_filename)):
#generate a blank writable excel sheet from scratch
field_names = [field_name for field_name in Opportunity.fields]
field_names.remove("filtered")
writer = ExcelWriter(db_filename)
sol_df = pd.DataFrame(columns = field_names)
filtered_df = pd.DataFrame(columns = field_names)
sol_df.to_excel(writer,sol_sheet_name)
filtered_df.to_excel(writer,filtered_sheet_name)
writer.save()
writer.close()
self.report_filename = (report_prefix + "_"
+ str(datetime.today())[:19]
.replace(":","_").replace(" ","[") + "].xlsx")
#kept for posterity, in case only the date component is needed and we don't care about overwrites
#self.report_filename = report_prefix + "_" + str(date.today())
self.db_filename = db_filename
self.sol_sheet_name = sol_sheet_name
self.filtered_sheet_name = filtered_sheet_name
self.sol_df = pd.read_excel(db_filename,sol_sheet_name, index_col = index_column)
self.filtered_df = pd.read_excel(db_filename,filtered_sheet_name, index_col = index_column)
self.usaved_sol_counter = 0
self.sol_counter = 0
self.added_items = set()
def dump_summary_to_excel(output_filename):
# Save to XLSX
store = HDFStore('_data_/ProteinDataStore.h5')
data_summary = store['DataBases_Summary']
writer = ExcelWriter(output_filename + '.xlsx', engine='xlsxwriter')
data_summary.to_excel(writer, 'DataBases_Summary', index=True)
writer.save()
def test_sheets(self):
_skip_if_no_xlrd()
ext = self.ext
path = '__tmp_to_excel_from_excel_sheets__.' + ext
with ensure_clean(path) as path:
self.frame['A'][:5] = nan
self.frame.to_excel(path, 'test1')
self.frame.to_excel(path, 'test1', cols=['A', 'B'])
self.frame.to_excel(path, 'test1', header=False)
self.frame.to_excel(path, 'test1', index=False)
# Test writing to separate sheets
writer = ExcelWriter(path)
self.frame.to_excel(writer, 'test1')
self.tsframe.to_excel(writer, 'test2')
writer.save()
reader = ExcelFile(path)
recons = reader.parse('test1', index_col=0)
tm.assert_frame_equal(self.frame, recons)
recons = reader.parse('test2', index_col=0)
tm.assert_frame_equal(self.tsframe, recons)
np.testing.assert_equal(2, len(reader.sheet_names))
np.testing.assert_equal('test1', reader.sheet_names[0])
np.testing.assert_equal('test2', reader.sheet_names[1])
def test_sheets(self, frame, tsframe, path):
# freq doesnt round-trip
index = pd.DatetimeIndex(np.asarray(tsframe.index), freq=None)
tsframe.index = index
frame = frame.copy()
frame["A"][:5] = np.nan
frame.to_excel(path, "test1")
frame.to_excel(path, "test1", columns=["A", "B"])
frame.to_excel(path, "test1", header=False)
frame.to_excel(path, "test1", index=False)
# Test writing to separate sheets
writer = ExcelWriter(path)
frame.to_excel(writer, "test1")
tsframe.to_excel(writer, "test2")
writer.save()
reader = ExcelFile(path)
recons = pd.read_excel(reader, "test1", index_col=0)
tm.assert_frame_equal(frame, recons)
recons = pd.read_excel(reader, "test2", index_col=0)
tm.assert_frame_equal(tsframe, recons)
assert 2 == len(reader.sheet_names)
assert "test1" == reader.sheet_names[0]
assert "test2" == reader.sheet_names[1]
def __init__(self, db_filename = "moore_grants_database.xlsx",
report_prefix = "report",
sheet_name = "grants",
index_column = "url",
report_only_new = True):
'''
Constructor
'''
if(not os.path.isfile(db_filename)):
#generate a blank writable excel sheet from scratch
field_names = [field_name for field_name in Grant.fields]
writer = ExcelWriter(db_filename)
profile_dataframe = pd.DataFrame(columns = field_names)
profile_dataframe.to_excel(writer,sheet_name)
writer.save()
writer.close()
self.report_filename = (report_prefix + "_"
+ str(datetime.today())[:19]
.replace(":","_").replace(" ","[") + "].xlsx")
# kept for posterity, in case only the date component is needed and
# we don't care about overwrites
# self.report_filename = report_prefix + "_" + str(date.today())
self.db_filename = db_filename
self.sheet_name = sheet_name
self.dataframe = pd.read_excel(db_filename,sheet_name, index_col = index_column)
self.usaved_sol_counter = 0
self.added_counter = 0
self.added_items = set()
self.index_column = index_column
def write(self, writer, sheet_name='Sheet1', startrow=0,
startcol=0, freeze_panes=None, engine=None):
"""
writer : string or ExcelWriter object
File path or existing ExcelWriter
sheet_name : string, default 'Sheet1'
Name of sheet which will contain DataFrame
startrow :
upper left cell row to dump data frame
startcol :
upper left cell column to dump data frame
freeze_panes : tuple of integer (length 2), default None
Specifies the one-based bottommost row and rightmost column that
is to be frozen
engine : string, default None
write engine to use if writer is a path - you can also set this
via the options ``io.excel.xlsx.writer``, ``io.excel.xls.writer``,
and ``io.excel.xlsm.writer``.
"""
from pandas.io.excel import ExcelWriter
from pandas.io.common import _stringify_path
if isinstance(writer, ExcelWriter):
need_save = False
else:
writer = ExcelWriter(_stringify_path(writer), engine=engine)
need_save = True
formatted_cells = self.get_formatted_cells()
writer.write_cells(formatted_cells, sheet_name,
startrow=startrow, startcol=startcol,
freeze_panes=freeze_panes)
if need_save:
writer.save()
def to_excel(self, excel_writer, sheet_name='Sheet1', na_rep='',
cell_styles=None, # new argument
float_format=None, columns=None, header=True, index=True,
index_label=None, startrow=0, startcol=0, engine=None,
merge_cells=True, encoding=None, inf_rep='inf', verbose=True):
"""
Extended function that adds support for "cell_styles" argument
"""
from pandas.io.excel import ExcelWriter
need_save = False
if encoding is None:
encoding = 'ascii'
if isinstance(excel_writer, compat.string_types):
excel_writer = ExcelWriter(excel_writer, engine=engine)
need_save = True
# use the extended formatter class and pass the cell_styles argument
formatter = ExcelFormatterStyler(self, na_rep=na_rep, cols=columns,
header=header,
cell_styles=cell_styles, # new argument
float_format=float_format, index=index,
index_label=index_label,
merge_cells=merge_cells,
inf_rep=inf_rep)
formatted_cells = formatter.get_formatted_cells()
excel_writer.write_cells(formatted_cells, sheet_name,
startrow=startrow, startcol=startcol)
if need_save:
excel_writer.save()
def dfs2Excel(dfs,sheetnames,filename):
'''
将一些列DataFrame保存到Excel中
'''
excelFile = ExcelWriter(filename)
for (i,df) in enumerate(dfs) :
df.to_excel(excelFile,sheetnames[i])
excelFile.save()
def write(
self,
writer,
sheet_name="Sheet1",
startrow=0,
startcol=0,
freeze_panes=None,
engine=None,
):
"""
writer : string or ExcelWriter object
File path or existing ExcelWriter
sheet_name : string, default 'Sheet1'
Name of sheet which will contain DataFrame
startrow :
upper left cell row to dump data frame
startcol :
upper left cell column to dump data frame
freeze_panes : tuple of integer (length 2), default None
Specifies the one-based bottommost row and rightmost column that
is to be frozen
engine : string, default None
write engine to use if writer is a path - you can also set this
via the options ``io.excel.xlsx.writer``, ``io.excel.xls.writer``,
and ``io.excel.xlsm.writer``.
"""
from pandas.io.excel import ExcelWriter
num_rows, num_cols = self.df.shape
if num_rows > self.max_rows or num_cols > self.max_cols:
raise ValueError(
f"This sheet is too large! Your sheet size is: {num_rows}, {num_cols} "
f"Max sheet size is: {self.max_rows}, {self.max_cols}"
)
if isinstance(writer, ExcelWriter):
need_save = False
else:
# pandas\io\formats\excel.py:808: error: Cannot instantiate
# abstract class 'ExcelWriter' with abstract attributes 'engine',
# 'save', 'supported_extensions' and 'write_cells' [abstract]
writer = ExcelWriter( # type: ignore[abstract]
stringify_path(writer), engine=engine
)
need_save = True
formatted_cells = self.get_formatted_cells()
writer.write_cells(
formatted_cells,
sheet_name,
startrow=startrow,
startcol=startcol,
freeze_panes=freeze_panes,
)
if need_save:
writer.save()
def colorful_dump_summary_to_excel(output_filename, range_label='L1:U36229'):
# < -2 dark green
# -2 to -1 light green
# -1 to 1 yellow
# 1 to 2 Orange
# > 2 red
store = HDFStore('_data_/ProteinDataStore.h5')
data_summary = store['DataBases_Summary']
writer = ExcelWriter(output_filename + '.xlsx', engine='xlsxwriter')
data_summary.to_excel(writer, 'DataBases_Summary', index=True)
workbook = writer.book
worksheet = writer.sheets['DataBases_Summary']
# using pallete http://www.colourlovers.com/palette/3687876/
blue = workbook.add_format({'bg_color': '#69D2E7', 'font_color': '#000000'})
coral = workbook.add_format({'bg_color': '#A7DBD8', 'font_color': '#000000'})
yellow = workbook.add_format({'bg_color': '#EAE319', 'font_color': '#000000'})
orange = workbook.add_format({'bg_color': '#FA6900', 'font_color': '#000000'})
red = workbook.add_format({'bg_color': '#E2434B', 'font_color': '#000000'})
# empty = workbook.add_format({'bg_color': '#FFFFFF', 'font_color': '#000000'})
#
# worksheet.conditional_format(range_label, {'type': 'text',
# 'criteria': 'begins with',
# 'value': '.',
# 'format': empty})
worksheet.conditional_format(range_label, {'type': 'cell',
'criteria': '<',
'value': -2,
'format': blue})
worksheet.conditional_format(range_label, {'type': 'cell',
'criteria': 'between',
'minimum': -2,
'maximum': -1,
'format': coral})
worksheet.conditional_format(range_label, {'type': 'cell',
'criteria': 'between',
'minimum': -1,
'maximum': 1,
'format': yellow})
worksheet.conditional_format(range_label, {'type': 'cell',
'criteria': 'between',
'minimum': 1,
'maximum': 2,
'format': orange})
worksheet.conditional_format(range_label, {'type': 'cell',
'criteria': '>',
'value': 2,
'format': red})
writer.save()
store.close()
def save_all(self):
'''
Dumps all solicitations in both databases to an excel file
'''
print "\n\n======== Saving {:s} ========".format(self.sheet_name)
writer = ExcelWriter(self.db_filename)
self.dataframe.to_excel(writer,self.sheet_name,merge_cells=False)
writer.save()
writer.close()
print "======== Done saving. ========\n"
def excel_format():
'''format in memory'''
START = time.strftime('%y%m%d-%H%M%S')
OUTFILE = 'TEST' + START + '.xlsx'
writer = ExcelWriter(OUTFILE)
DF1D.to_excel(writer)
writer.save()
wb = writer.book
auto_width(wb['Sheet1'])
wb.save(writer.path)
def write(
self,
writer,
sheet_name="Sheet1",
startrow=0,
startcol=0,
freeze_panes=None,
engine=None,
):
"""
writer : string or ExcelWriter object
File path or existing ExcelWriter
sheet_name : string, default 'Sheet1'
Name of sheet which will contain DataFrame
startrow :
upper left cell row to dump data frame
startcol :
upper left cell column to dump data frame
freeze_panes : tuple of integer (length 2), default None
Specifies the one-based bottommost row and rightmost column that
is to be frozen
engine : string, default None
write engine to use if writer is a path - you can also set this
via the options ``io.excel.xlsx.writer``, ``io.excel.xls.writer``,
and ``io.excel.xlsm.writer``.
"""
from pandas.io.excel import ExcelWriter
from pandas.io.common import _stringify_path
num_rows, num_cols = self.df.shape
if num_rows > self.max_rows or num_cols > self.max_cols:
raise ValueError(
"This sheet is too large! Your sheet size is: "
+ "{}, {} ".format(num_rows, num_cols)
+ "Max sheet size is: {}, {}".format(self.max_rows, self.max_cols)
)
if isinstance(writer, ExcelWriter):
need_save = False
else:
writer = ExcelWriter(_stringify_path(writer), engine=engine)
need_save = True
formatted_cells = self.get_formatted_cells()
writer.write_cells(
formatted_cells,
sheet_name,
startrow=startrow,
startcol=startcol,
freeze_panes=freeze_panes,
)
if need_save:
writer.save()
def test_bytes_io(self, engine):
# see gh-7074
bio = BytesIO()
df = DataFrame(np.random.randn(10, 2))
# Pass engine explicitly, as there is no file path to infer from.
writer = ExcelWriter(bio, engine=engine)
df.to_excel(writer)
writer.save()
bio.seek(0)
reread_df = pd.read_excel(bio, index_col=0)
tm.assert_frame_equal(df, reread_df)
def save_all(self):
'''
Dumps all solicitations in both databases to an excel file,
into two separate spreadsheets: one for filtered items, the other
for the remaining (relevant) items
'''
print "\n\n======== Saving solicitations... ========"
writer = ExcelWriter(self.db_filename)
self.sol_df.to_excel(writer,self.sol_sheet_name,merge_cells=False)
self.filtered_df.to_excel(writer,self.filtered_sheet_name,merge_cells=False)
writer.save()
writer.close()
print "======== Done saving. ========\n"
def test_bytes_io(self, engine, ext):
# see gh-7074
bio = BytesIO()
df = DataFrame(np.random.randn(10, 2))
# Pass engine explicitly, as there is no file path to infer from.
writer = ExcelWriter(bio, engine=engine)
df.to_excel(writer)
writer.save()
bio.seek(0)
reread_df = pd.read_excel(bio, index_col=0)
tm.assert_frame_equal(df, reread_df)
def to_excel(self, path, na_rep='', engine=None, **kwargs):
"""
Write each DataFrame in Panel to a separate excel sheet
Parameters
----------
path : string or ExcelWriter object
File path or existing ExcelWriter
na_rep : string, default ''
Missing data representation
engine : string, default None
write engine to use - you can also set this via the options
``io.excel.xlsx.writer``, ``io.excel.xls.writer``, and
``io.excel.xlsm.writer``.
Other Parameters
----------------
float_format : string, default None
Format string for floating point numbers
cols : sequence, optional
Columns to write
header : boolean or list of string, default True
Write out column names. If a list of string is given it is
assumed to be aliases for the column names
index : boolean, default True
Write row names (index)
index_label : string or sequence, default None
Column label for index column(s) if desired. If None is given, and
`header` and `index` are True, then the index names are used. A
sequence should be given if the DataFrame uses MultiIndex.
startow : upper left cell row to dump data frame
startcol : upper left cell column to dump data frame
Notes
-----
Keyword arguments (and na_rep) are passed to the ``to_excel`` method
for each DataFrame written.
"""
from pandas.io.excel import ExcelWriter
if isinstance(path, compat.string_types):
writer = ExcelWriter(path, engine=engine)
else:
writer = path
kwargs['na_rep'] = na_rep
for item, df in compat.iteritems(self):
name = str(item)
df.to_excel(writer, name, **kwargs)
writer.save()
def to_excel(self, path, na_rep='', engine=None, **kwargs):
"""
Write each DataFrame in Panel to a separate excel sheet
Parameters
----------
path : string or ExcelWriter object
File path or existing ExcelWriter
na_rep : string, default ''
Missing data representation
engine : string, default None
write engine to use - you can also set this via the options
``io.excel.xlsx.writer``, ``io.excel.xls.writer``, and
``io.excel.xlsm.writer``.
Other Parameters
----------------
float_format : string, default None
Format string for floating point numbers
cols : sequence, optional
Columns to write
header : boolean or list of string, default True
Write out column names. If a list of string is given it is
assumed to be aliases for the column names
index : boolean, default True
Write row names (index)
index_label : string or sequence, default None
Column label for index column(s) if desired. If None is given, and
`header` and `index` are True, then the index names are used. A
sequence should be given if the DataFrame uses MultiIndex.
startow : upper left cell row to dump data frame
startcol : upper left cell column to dump data frame
Notes
-----
Keyword arguments (and na_rep) are passed to the ``to_excel`` method
for each DataFrame written.
"""
from pandas.io.excel import ExcelWriter
if isinstance(path, compat.string_types):
writer = ExcelWriter(path, engine=engine)
else:
writer = path
kwargs['na_rep'] = na_rep
for item, df in compat.iteritems(self):
name = str(item)
df.to_excel(writer, name, **kwargs)
writer.save()
def save_all(self):
'''
Dumps all solicitations in both databases to an excel file,
into two separate spreadsheets: one for filtered items, the other
for the remaining (relevant) items
'''
print "\n\n======== Saving solicitations... ========"
writer = ExcelWriter(self.db_filename)
self.sol_df.to_excel(writer, self.sol_sheet_name, merge_cells=False)
self.filtered_df.to_excel(writer,
self.filtered_sheet_name,
merge_cells=False)
writer.save()
writer.close()
print "======== Done saving. ========\n"
def to_excel(self, path, na_rep=''):
"""
Write each DataFrame in Panel to a separate excel sheet
Parameters
----------
excel_writer : string or ExcelWriter object
File path or existing ExcelWriter
na_rep : string, default ''
Missing data representation
"""
from pandas.io.excel import ExcelWriter
writer = ExcelWriter(path)
for item, df in compat.iteritems(self):
name = str(item)
df.to_excel(writer, name, na_rep=na_rep)
writer.save()
def to_excel(self, path, na_rep=''):
"""
Write each DataFrame in Panel to a separate excel sheet
Parameters
----------
excel_writer : string or ExcelWriter object
File path or existing ExcelWriter
na_rep : string, default ''
Missing data representation
"""
from pandas.io.excel import ExcelWriter
writer = ExcelWriter(path)
for item, df in compat.iteritems(self):
name = str(item)
df.to_excel(writer, name, na_rep=na_rep)
writer.save()
def generate_report(self):
'''
Generates a separate excel report, consisting of non-award-type notices
that are not yet overdue
'''
print "\n\n======== Generating report... ========"
df = self.dataframe.copy()
ix = pd.Series([(True if ix in self.added_items else False )
for ix in df.index ],
index=df.index)
report_df = df[ix == True]
writer = ExcelWriter(self.report_filename)
report_df.to_excel(writer,self.sheet_name,merge_cells=False)
writer.save()
writer.close()
print "======== Report Generated as " + self.report_filename + " ========\n"
def test_column_format(ext):
# Test that column formats are applied to cells. Test for issue #9167.
# Applicable to xlsxwriter only.
with warnings.catch_warnings():
# Ignore the openpyxl lxml warning.
warnings.simplefilter("ignore")
openpyxl = pytest.importorskip("openpyxl")
with tm.ensure_clean(ext) as path:
frame = DataFrame({"A": [123456, 123456], "B": [123456, 123456]})
writer = ExcelWriter(path)
frame.to_excel(writer)
# Add a number format to col B and ensure it is applied to cells.
num_format = "#,##0"
write_workbook = writer.book
write_worksheet = write_workbook.worksheets()[0]
col_format = write_workbook.add_format({"num_format": num_format})
write_worksheet.set_column("B:B", None, col_format)
writer.save()
read_workbook = openpyxl.load_workbook(path)
try:
read_worksheet = read_workbook["Sheet1"]
except TypeError:
# compat
read_worksheet = read_workbook.get_sheet_by_name(name="Sheet1")
# Get the number format from the cell.
try:
cell = read_worksheet["B2"]
except TypeError:
# compat
cell = read_worksheet.cell("B2")
try:
read_num_format = cell.number_format
except AttributeError:
read_num_format = cell.style.number_format._format_code
assert read_num_format == num_format
def test_column_format(self, ext):
# Test that column formats are applied to cells. Test for issue #9167.
# Applicable to xlsxwriter only.
with warnings.catch_warnings():
# Ignore the openpyxl lxml warning.
warnings.simplefilter("ignore")
import openpyxl
with ensure_clean(ext) as path:
frame = DataFrame({'A': [123456, 123456], 'B': [123456, 123456]})
writer = ExcelWriter(path)
frame.to_excel(writer)
# Add a number format to col B and ensure it is applied to cells.
num_format = '#,##0'
write_workbook = writer.book
write_worksheet = write_workbook.worksheets()[0]
col_format = write_workbook.add_format({'num_format': num_format})
write_worksheet.set_column('B:B', None, col_format)
writer.save()
read_workbook = openpyxl.load_workbook(path)
try:
read_worksheet = read_workbook['Sheet1']
except TypeError:
# compat
read_worksheet = read_workbook.get_sheet_by_name(name='Sheet1')
# Get the number format from the cell.
try:
cell = read_worksheet['B2']
except TypeError:
# compat
cell = read_worksheet.cell('B2')
try:
read_num_format = cell.number_format
except Exception:
read_num_format = cell.style.number_format._format_code
assert read_num_format == num_format
def generate_report(self):
'''
Generates a separate excel report, consisting of non-award-type notices
that are not yet overdue
'''
print "\n\n======== Generating report... ========"
today = datetime.today()
df = self.sol_df.copy()
df["new"] = pd.Series([(True if ix in self.added_items else False )
for ix in df.index ],
index=df.index)
report_df = df[(df["proposal_due_date"] >= today) | (df["proposal_due_date"] == None)]
writer = ExcelWriter(self.report_filename)
report_df.to_excel(writer,self.sol_sheet_name,merge_cells=False)
writer.save()
writer.close()
print "======== Report Generated as " + self.report_filename + " ========\n"
def generate_report(self):
'''
Generates a separate excel report, consisting of non-award-type notices
that are not yet overdue
'''
print "\n\n======== Generating report... ========"
today = datetime.today()
df = self.sol_df.copy()
df["new"] = pd.Series([(1 if ix in self.added_items else 0 )
for ix in df.index ],
index=df.index)
df["dd"] = [datetime.strptime(dt, "%m/%d/%Y") for dt in df["deadline_date"].values]
report_df = df[(df["dd"] >= today)
& (df["announcement_type"] != "Award")]
writer = ExcelWriter(self.report_filename)
report_df.to_excel(writer,self.sol_sheet_name,merge_cells=False)
writer.save()
writer.close()
print "======== Report Generated as " + self.report_filename + " ========\n"
def test_write_append_mode(ext, mode, expected):
df = DataFrame([1], columns=["baz"])
with tm.ensure_clean(ext) as f:
wb = openpyxl.Workbook()
wb.worksheets[0].title = "foo"
wb.worksheets[0]["A1"].value = "foo"
wb.create_sheet("bar")
wb.worksheets[1]["A1"].value = "bar"
wb.save(f)
writer = ExcelWriter(f, engine="openpyxl", mode=mode)
df.to_excel(writer, sheet_name="baz", index=False)
writer.save()
wb2 = openpyxl.load_workbook(f)
result = [sheet.title for sheet in wb2.worksheets]
assert result == expected
for index, cell_value in enumerate(expected):
assert wb2.worksheets[index]["A1"].value == cell_value
def __init__(self,
db_filename="fbo_solicitations.xlsx",
report_prefix="report",
sol_sheet_name="solicitations",
filtered_sheet_name="filtered_solicitations",
index_column="sponsor_number",
report_only_new=True):
'''
Constructor
'''
if (not os.path.isfile(db_filename)):
#generate a blank writable excel sheet from scratch
field_names = [field_name for field_name in Opportunity.fields]
field_names.remove("filtered")
writer = ExcelWriter(db_filename)
sol_df = pd.DataFrame(columns=field_names)
filtered_df = pd.DataFrame(columns=field_names)
sol_df.to_excel(writer, sol_sheet_name)
filtered_df.to_excel(writer, filtered_sheet_name)
writer.save()
writer.close()
self.report_filename = (
report_prefix + "_" +
str(datetime.today())[:19].replace(":", "_").replace(" ", "[") +
"].xlsx")
#kept for posterity, in case only the date component is needed and we don't care about overwrites
#self.report_filename = report_prefix + "_" + str(date.today())
self.db_filename = db_filename
self.sol_sheet_name = sol_sheet_name
self.filtered_sheet_name = filtered_sheet_name
self.sol_df = pd.read_excel(db_filename,
sol_sheet_name,
index_col=index_column)
self.filtered_df = pd.read_excel(db_filename,
filtered_sheet_name,
index_col=index_column)
self.usaved_sol_counter = 0
self.sol_counter = 0
self.added_items = set()
def test_sheets(self, engine, ext, frame, tsframe):
frame = frame.copy()
frame['A'][:5] = nan
frame.to_excel(self.path, 'test1')
frame.to_excel(self.path, 'test1', columns=['A', 'B'])
frame.to_excel(self.path, 'test1', header=False)
frame.to_excel(self.path, 'test1', index=False)
# Test writing to separate sheets
writer = ExcelWriter(self.path)
frame.to_excel(writer, 'test1')
tsframe.to_excel(writer, 'test2')
writer.save()
reader = ExcelFile(self.path)
recons = pd.read_excel(reader, 'test1', index_col=0)
tm.assert_frame_equal(frame, recons)
recons = pd.read_excel(reader, 'test2', index_col=0)
tm.assert_frame_equal(tsframe, recons)
assert 2 == len(reader.sheet_names)
assert 'test1' == reader.sheet_names[0]
assert 'test2' == reader.sheet_names[1]
def test_write_append_mode(self, ext, mode, expected):
import openpyxl
df = DataFrame([1], columns=['baz'])
with ensure_clean(ext) as f:
wb = openpyxl.Workbook()
wb.worksheets[0].title = 'foo'
wb.worksheets[0]['A1'].value = 'foo'
wb.create_sheet('bar')
wb.worksheets[1]['A1'].value = 'bar'
wb.save(f)
writer = ExcelWriter(f, engine='openpyxl', mode=mode)
df.to_excel(writer, sheet_name='baz', index=False)
writer.save()
wb2 = openpyxl.load_workbook(f)
result = [sheet.title for sheet in wb2.worksheets]
assert result == expected
for index, cell_value in enumerate(expected):
assert wb2.worksheets[index]['A1'].value == cell_value
def test_sheets(self, engine, ext, frame, tsframe):
frame = frame.copy()
frame['A'][:5] = nan
frame.to_excel(self.path, 'test1')
frame.to_excel(self.path, 'test1', columns=['A', 'B'])
frame.to_excel(self.path, 'test1', header=False)
frame.to_excel(self.path, 'test1', index=False)
# Test writing to separate sheets
writer = ExcelWriter(self.path)
frame.to_excel(writer, 'test1')
tsframe.to_excel(writer, 'test2')
writer.save()
reader = ExcelFile(self.path)
recons = pd.read_excel(reader, 'test1', index_col=0)
tm.assert_frame_equal(frame, recons)
recons = pd.read_excel(reader, 'test2', index_col=0)
tm.assert_frame_equal(tsframe, recons)
assert 2 == len(reader.sheet_names)
assert 'test1' == reader.sheet_names[0]
assert 'test2' == reader.sheet_names[1]
def test_sheets(self, engine, ext, frame, tsframe):
frame = frame.copy()
frame["A"][:5] = nan
frame.to_excel(self.path, "test1")
frame.to_excel(self.path, "test1", columns=["A", "B"])
frame.to_excel(self.path, "test1", header=False)
frame.to_excel(self.path, "test1", index=False)
# Test writing to separate sheets
writer = ExcelWriter(self.path)
frame.to_excel(writer, "test1")
tsframe.to_excel(writer, "test2")
writer.save()
reader = ExcelFile(self.path)
recons = pd.read_excel(reader, "test1", index_col=0)
tm.assert_frame_equal(frame, recons)
recons = pd.read_excel(reader, "test2", index_col=0)
tm.assert_frame_equal(tsframe, recons)
assert 2 == len(reader.sheet_names)
assert "test1" == reader.sheet_names[0]
assert "test2" == reader.sheet_names[1]
def __init__(self, db_filename = "nsf_solicitations.xlsx",
report_prefix = "report",
sol_sheet_name = "solicitations",
filtered_sheet_name = "filtered_solicitations",
index_column = "pims_id"):
'''
Constructor
'''
if(not os.path.isfile(db_filename)):
#generate a blank writable excel sheet from scratch
field_names = [field_name for field_name in NsfSolicitation.fields]
field_names.remove("filtered")
writer = ExcelWriter(db_filename)
sol_df = pd.DataFrame(columns = field_names)
filtered_df = pd.DataFrame(columns = field_names)
sol_df.to_excel(writer,sol_sheet_name)
filtered_df.to_excel(writer,filtered_sheet_name)
writer.save()
writer.close()
self.report_filename = (report_prefix + "_"
+ str(datetime.today())[:19]
.replace(":","_").replace(" ","[") + "].xlsx")
self.db_filename = db_filename
self.sol_sheet_name = sol_sheet_name
self.filtered_sheet_name = filtered_sheet_name
self.sol_df = pd.read_excel(db_filename,sol_sheet_name, index_col = index_column)
self.filtered_df = pd.read_excel(db_filename,filtered_sheet_name, index_col = index_column)
self.usaved_sol_counter = 0
self.sol_counter = 0
self.added_items = set()
self.solicitation_numbers = set()
for sn in self.sol_df["solicitation_number"].values:
self.solicitation_numbers.add(sn)
self.index_column = index_column
def _check_extension_sheets(self, ext):
path = "__tmp_to_excel_from_excel_sheets__." + ext
with ensure_clean(path) as path:
self.frame["A"][:5] = nan
self.frame.to_excel(path, "test1")
self.frame.to_excel(path, "test1", cols=["A", "B"])
self.frame.to_excel(path, "test1", header=False)
self.frame.to_excel(path, "test1", index=False)
# Test writing to separate sheets
writer = ExcelWriter(path)
self.frame.to_excel(writer, "test1")
self.tsframe.to_excel(writer, "test2")
writer.save()
reader = ExcelFile(path)
recons = reader.parse("test1", index_col=0)
tm.assert_frame_equal(self.frame, recons)
recons = reader.parse("test2", index_col=0)
tm.assert_frame_equal(self.tsframe, recons)
np.testing.assert_equal(2, len(reader.sheet_names))
np.testing.assert_equal("test1", reader.sheet_names[0])
np.testing.assert_equal("test2", reader.sheet_names[1])
def generate_report(self):
'''
Generates a separate excel report, consisting of non-award-type notices
that are not yet overdue
'''
print "\n\n======== Generating report... ========"
today = datetime.today()
df = self.sol_df.copy()
df["new"] = pd.Series([(1 if ix in self.added_items else 0)
for ix in df.index],
index=df.index)
df["dd"] = [
datetime.strptime(dt, "%m/%d/%Y")
for dt in df["deadline_date"].values
]
report_df = df[(df["dd"] >= today)
& (df["announcement_type"] != "Award")]
writer = ExcelWriter(self.report_filename)
report_df.to_excel(writer, self.sol_sheet_name, merge_cells=False)
writer.save()
writer.close()
print "======== Report Generated as " + self.report_filename + " ========\n"
def test_styler_to_excel(engine):
def style(df):
# XXX: RGB colors not supported in xlwt
return DataFrame(
[['font-weight: bold', '', ''], ['', 'color: blue', ''],
['', '', 'text-decoration: underline'],
['border-style: solid', '', ''], ['', 'font-style: italic', ''],
['', '', 'text-align: right'], ['background-color: red', '', ''],
['number-format: 0%', '', ''], ['', '', ''], ['', '', ''],
['', '', '']],
index=df.index,
columns=df.columns)
def assert_equal_style(cell1, cell2, engine):
if engine in ['xlsxwriter', 'openpyxl']:
pytest.xfail(reason=("GH25351: failing on some attribute "
"comparisons in {}".format(engine)))
# XXX: should find a better way to check equality
assert cell1.alignment.__dict__ == cell2.alignment.__dict__
assert cell1.border.__dict__ == cell2.border.__dict__
assert cell1.fill.__dict__ == cell2.fill.__dict__
assert cell1.font.__dict__ == cell2.font.__dict__
assert cell1.number_format == cell2.number_format
assert cell1.protection.__dict__ == cell2.protection.__dict__
def custom_converter(css):
# use bold iff there is custom style attached to the cell
if css.strip(' \n;'):
return {'font': {'bold': True}}
return {}
pytest.importorskip('jinja2')
pytest.importorskip(engine)
# Prepare spreadsheets
df = DataFrame(np.random.randn(11, 3))
with ensure_clean('.xlsx' if engine != 'xlwt' else '.xls') as path:
writer = ExcelWriter(path, engine=engine)
df.to_excel(writer, sheet_name='frame')
df.style.to_excel(writer, sheet_name='unstyled')
styled = df.style.apply(style, axis=None)
styled.to_excel(writer, sheet_name='styled')
ExcelFormatter(styled, style_converter=custom_converter).write(
writer, sheet_name='custom')
writer.save()
if engine not in ('openpyxl', 'xlsxwriter'):
# For other engines, we only smoke test
return
openpyxl = pytest.importorskip('openpyxl')
wb = openpyxl.load_workbook(path)
# (1) compare DataFrame.to_excel and Styler.to_excel when unstyled
n_cells = 0
for col1, col2 in zip(wb['frame'].columns, wb['unstyled'].columns):
assert len(col1) == len(col2)
for cell1, cell2 in zip(col1, col2):
assert cell1.value == cell2.value
assert_equal_style(cell1, cell2, engine)
n_cells += 1
# ensure iteration actually happened:
assert n_cells == (11 + 1) * (3 + 1)
# (2) check styling with default converter
# XXX: openpyxl (as at 2.4) prefixes colors with 00, xlsxwriter with FF
alpha = '00' if engine == 'openpyxl' else 'FF'
n_cells = 0
for col1, col2 in zip(wb['frame'].columns, wb['styled'].columns):
assert len(col1) == len(col2)
for cell1, cell2 in zip(col1, col2):
ref = '%s%d' % (cell2.column, cell2.row)
# XXX: this isn't as strong a test as ideal; we should
# confirm that differences are exclusive
if ref == 'B2':
assert not cell1.font.bold
assert cell2.font.bold
elif ref == 'C3':
assert cell1.font.color.rgb != cell2.font.color.rgb
assert cell2.font.color.rgb == alpha + '0000FF'
elif ref == 'D4':
# This fails with engine=xlsxwriter due to
# https://bitbucket.org/openpyxl/openpyxl/issues/800
if engine == 'xlsxwriter' \
and (LooseVersion(openpyxl.__version__) <
LooseVersion('2.4.6')):
pass
else:
assert cell1.font.underline != cell2.font.underline
assert cell2.font.underline == 'single'
elif ref == 'B5':
assert not cell1.border.left.style
assert (cell2.border.top.style == cell2.border.right.style
== cell2.border.bottom.style ==
cell2.border.left.style == 'medium')
elif ref == 'C6':
assert not cell1.font.italic
assert cell2.font.italic
elif ref == 'D7':
assert (cell1.alignment.horizontal !=
cell2.alignment.horizontal)
assert cell2.alignment.horizontal == 'right'
elif ref == 'B8':
assert cell1.fill.fgColor.rgb != cell2.fill.fgColor.rgb
assert cell1.fill.patternType != cell2.fill.patternType
assert cell2.fill.fgColor.rgb == alpha + 'FF0000'
assert cell2.fill.patternType == 'solid'
elif ref == 'B9':
assert cell1.number_format == 'General'
assert cell2.number_format == '0%'
else:
assert_equal_style(cell1, cell2, engine)
assert cell1.value == cell2.value
n_cells += 1
assert n_cells == (11 + 1) * (3 + 1)
# (3) check styling with custom converter
n_cells = 0
for col1, col2 in zip(wb['frame'].columns, wb['custom'].columns):
assert len(col1) == len(col2)
for cell1, cell2 in zip(col1, col2):
ref = '%s%d' % (cell2.column, cell2.row)
if ref in ('B2', 'C3', 'D4', 'B5', 'C6', 'D7', 'B8', 'B9'):
assert not cell1.font.bold
assert cell2.font.bold
else:
assert_equal_style(cell1, cell2, engine)
assert cell1.value == cell2.value
n_cells += 1
assert n_cells == (11 + 1) * (3 + 1)
standardized = get_val(row.standardized)
encoding = get_val(row.encoding)
if not downloaded and len(downloaded) < 1:
downloaded, encoding = spider.start_single(
row.uri, Path.joinpath(folder_download, downloaded))
task_content.loc[index, C.REQ_DOWNLOAD] = downloaded.name
task_content.loc[index, C.REQ_ENCODING] = encoding
else:
downloaded = folder_download.joinpath(downloaded)
if not standardized and len(standardized) < 1:
standardized = std.parse_one(downloaded=downloaded,
from_lang=src_language,
to_lang=dst_language,
encoding=encoding)
task_content.loc[index,
C.REQ_STANDARDIZED] = standardized.name
except Exception as e:
exc_type, exc_val, _ = sys.exc_info()
if type(e) == r.exceptions.ConnectionError:
lg.error(f'{dir(e)}')
lg.error(f'errno:{e.errno} - strerror:{e.strerror}')
task_content.loc[
index,
C.REQ_ERROR] = f'{type(e)}:{e.errno}:{e.strerror}'
lg.error(f"Exception occurred: {exc_type}\n", exc_info=True)
ew = ExcelWriter(str(pth))
task_content.to_excel(ew, index=False, encoding='utf-8')
ew.save()
ew.close()
lg.info(f'** finished file:{pth}')
def write(
self,
writer,
sheet_name="Sheet1",
startrow=0,
startcol=0,
freeze_panes=None,
engine=None,
storage_options: StorageOptions = None,
):
"""
writer : path-like, file-like, or ExcelWriter object
File path or existing ExcelWriter
sheet_name : string, default 'Sheet1'
Name of sheet which will contain DataFrame
startrow :
upper left cell row to dump data frame
startcol :
upper left cell column to dump data frame
freeze_panes : tuple of integer (length 2), default None
Specifies the one-based bottommost row and rightmost column that
is to be frozen
engine : string, default None
write engine to use if writer is a path - you can also set this
via the options ``io.excel.xlsx.writer``, ``io.excel.xls.writer``,
and ``io.excel.xlsm.writer``.
storage_options : dict, optional
Extra options that make sense for a particular storage connection, e.g.
host, port, username, password, etc., if using a URL that will
be parsed by ``fsspec``, e.g., starting "s3://", "gcs://".
.. versionadded:: 1.2.0
"""
from pandas.io.excel import ExcelWriter
num_rows, num_cols = self.df.shape
if num_rows > self.max_rows or num_cols > self.max_cols:
raise ValueError(
f"This sheet is too large! Your sheet size is: {num_rows}, {num_cols} "
f"Max sheet size is: {self.max_rows}, {self.max_cols}")
if isinstance(writer, ExcelWriter):
need_save = False
else:
# pandas\io\formats\excel.py:808: error: Cannot instantiate
# abstract class 'ExcelWriter' with abstract attributes 'engine',
# 'save', 'supported_extensions' and 'write_cells' [abstract]
writer = ExcelWriter( # type: ignore[abstract]
writer,
engine=engine,
storage_options=storage_options)
need_save = True
formatted_cells = self.get_formatted_cells()
writer.write_cells(
formatted_cells,
sheet_name,
startrow=startrow,
startcol=startcol,
freeze_panes=freeze_panes,
)
if need_save:
writer.save()
#%% 生成行名和坐标的对应关系表
luc = list(uppercase)
columns = Series((luc + [i+j for i in luc for j in luc])[:256],range(1,257))
def cellName(nRow,nCol):
return columns[nCol]+str(nRow)
#%% 生成一份测试excel数据文件
filename = r'c:\test1.xls'
sheetname = 'sheet1'
data = DataFrame(
{'a':range(10), 'b':range(10,20), 'c':range(20,30),'d':range(30,40)},
index=list(uppercase)[:10]
)
datafile = ExcelWriter(filename)
data.to_excel(datafile,sheetname)
datafile.save()
#%% 使用VBA将其数据文件打开
application = win32com.client.Dispatch('Excel.Application')
application.Visible = True
application.DisplayAlerts = False
workbook = application.Workbooks.Open(filename)
sheets = workbook.Sheets
sheet = sheets.Item(sheetname)
#%% 获取数据范围
nRow = sheet.UsedRange.Rows.Count
nCol = sheet.UsedRange.Columns.Count
# 为嵌入式图表计算
chartObjectXCells = 10
chartObjectYCells = 25
parser.add_argument('infiles',
type=str,
nargs='+',
metavar='FILE',
help='input text file')
parser.add_argument('-o',
'--outfile',
type=str,
required=True,
help='output Excel file name')
parser.add_argument('-d',
'--delimiter',
type=str,
required=False,
help='field separater in input files')
parser.add_argument('--header',
type=str,
required=False,
default='infer',
help='row number to use as header')
args = parser.parse_args()
import pandas as pd
from pandas.io.excel import ExcelWriter
excel = ExcelWriter(args.outfile)
for infile in args.infiles:
t = pd.read_table(infile, header=args.header)
t.to_excel(excel, sheet_name=os.path.basename(infile), index=False)
excel.save()
os.chdir(path)
os.environ.setdefault("DJANGO_SETTINGS_MODULE", settings)
#%%
from django_pandas.io import read_frame
from data.models import ModelDepthMarketData
from pandas.io.excel import ExcelWriter
qs = ModelDepthMarketData.objects.all()
df = read_frame(qs)
df = df[['InstrumentID', 'AskPrice1','AskVolume1','BidPrice1','BidVolume1','TradingDay','UpdateTime','UpdateMillisec','Volume','Turnover']]
writer = ExcelWriter('/tmp/output.xls')
df.to_excel(writer)
#%% 待处理
writer.save()
#%%
InstrumentIDList = list(df.groupby('InstrumentID').count().index)
InstrumentIDList.sort()
InstrumentIDList
#%%
InstrumentData = {}
for i in InstrumentIDList:
InstrumentData[i] = df[df.InstrumentID==i].reset_index()
InstrumentData[i][i] = InstrumentData[i].BidPrice1
InstrumentData[i]= InstrumentData[i][[i,'TradingDay','UpdateTime','UpdateMillisec']]
InstrumentData[InstrumentData.keys()[0]].head()
#%%
def test_styler_to_excel(engine):
def style(df):
# XXX: RGB colors not supported in xlwt
return DataFrame([['font-weight: bold', '', ''],
['', 'color: blue', ''],
['', '', 'text-decoration: underline'],
['border-style: solid', '', ''],
['', 'font-style: italic', ''],
['', '', 'text-align: right'],
['background-color: red', '', ''],
['number-format: 0%', '', ''],
['', '', ''],
['', '', ''],
['', '', '']],
index=df.index, columns=df.columns)
def assert_equal_style(cell1, cell2, engine):
if engine in ['xlsxwriter', 'openpyxl']:
pytest.xfail(reason=("GH25351: failing on some attribute "
"comparisons in {}".format(engine)))
# XXX: should find a better way to check equality
assert cell1.alignment.__dict__ == cell2.alignment.__dict__
assert cell1.border.__dict__ == cell2.border.__dict__
assert cell1.fill.__dict__ == cell2.fill.__dict__
assert cell1.font.__dict__ == cell2.font.__dict__
assert cell1.number_format == cell2.number_format
assert cell1.protection.__dict__ == cell2.protection.__dict__
def custom_converter(css):
# use bold iff there is custom style attached to the cell
if css.strip(' \n;'):
return {'font': {'bold': True}}
return {}
pytest.importorskip('jinja2')
pytest.importorskip(engine)
# Prepare spreadsheets
df = DataFrame(np.random.randn(11, 3))
with ensure_clean('.xlsx' if engine != 'xlwt' else '.xls') as path:
writer = ExcelWriter(path, engine=engine)
df.to_excel(writer, sheet_name='frame')
df.style.to_excel(writer, sheet_name='unstyled')
styled = df.style.apply(style, axis=None)
styled.to_excel(writer, sheet_name='styled')
ExcelFormatter(styled, style_converter=custom_converter).write(
writer, sheet_name='custom')
writer.save()
if engine not in ('openpyxl', 'xlsxwriter'):
# For other engines, we only smoke test
return
openpyxl = pytest.importorskip('openpyxl')
wb = openpyxl.load_workbook(path)
# (1) compare DataFrame.to_excel and Styler.to_excel when unstyled
n_cells = 0
for col1, col2 in zip(wb['frame'].columns,
wb['unstyled'].columns):
assert len(col1) == len(col2)
for cell1, cell2 in zip(col1, col2):
assert cell1.value == cell2.value
assert_equal_style(cell1, cell2, engine)
n_cells += 1
# ensure iteration actually happened:
assert n_cells == (11 + 1) * (3 + 1)
# (2) check styling with default converter
# XXX: openpyxl (as at 2.4) prefixes colors with 00, xlsxwriter with FF
alpha = '00' if engine == 'openpyxl' else 'FF'
n_cells = 0
for col1, col2 in zip(wb['frame'].columns,
wb['styled'].columns):
assert len(col1) == len(col2)
for cell1, cell2 in zip(col1, col2):
ref = '%s%d' % (cell2.column, cell2.row)
# XXX: this isn't as strong a test as ideal; we should
# confirm that differences are exclusive
if ref == 'B2':
assert not cell1.font.bold
assert cell2.font.bold
elif ref == 'C3':
assert cell1.font.color.rgb != cell2.font.color.rgb
assert cell2.font.color.rgb == alpha + '0000FF'
elif ref == 'D4':
# This fails with engine=xlsxwriter due to
# https://bitbucket.org/openpyxl/openpyxl/issues/800
if engine == 'xlsxwriter' \
and (LooseVersion(openpyxl.__version__) <
LooseVersion('2.4.6')):
pass
else:
assert cell1.font.underline != cell2.font.underline
assert cell2.font.underline == 'single'
elif ref == 'B5':
assert not cell1.border.left.style
assert (cell2.border.top.style ==
cell2.border.right.style ==
cell2.border.bottom.style ==
cell2.border.left.style ==
'medium')
elif ref == 'C6':
assert not cell1.font.italic
assert cell2.font.italic
elif ref == 'D7':
assert (cell1.alignment.horizontal !=
cell2.alignment.horizontal)
assert cell2.alignment.horizontal == 'right'
elif ref == 'B8':
assert cell1.fill.fgColor.rgb != cell2.fill.fgColor.rgb
assert cell1.fill.patternType != cell2.fill.patternType
assert cell2.fill.fgColor.rgb == alpha + 'FF0000'
assert cell2.fill.patternType == 'solid'
elif ref == 'B9':
assert cell1.number_format == 'General'
assert cell2.number_format == '0%'
else:
assert_equal_style(cell1, cell2, engine)
assert cell1.value == cell2.value
n_cells += 1
assert n_cells == (11 + 1) * (3 + 1)
# (3) check styling with custom converter
n_cells = 0
for col1, col2 in zip(wb['frame'].columns,
wb['custom'].columns):
assert len(col1) == len(col2)
for cell1, cell2 in zip(col1, col2):
ref = '%s%d' % (cell2.column, cell2.row)
if ref in ('B2', 'C3', 'D4', 'B5', 'C6', 'D7', 'B8', 'B9'):
assert not cell1.font.bold
assert cell2.font.bold
else:
assert_equal_style(cell1, cell2, engine)
assert cell1.value == cell2.value
n_cells += 1
assert n_cells == (11 + 1) * (3 + 1)
def test_styler_to_excel(engine):
def style(df):
# TODO: RGB colors not supported in xlwt
return DataFrame(
[
["font-weight: bold", "", ""],
["", "color: blue", ""],
["", "", "text-decoration: underline"],
["border-style: solid", "", ""],
["", "font-style: italic", ""],
["", "", "text-align: right"],
["background-color: red", "", ""],
["number-format: 0%", "", ""],
["", "", ""],
["", "", ""],
["", "", ""],
],
index=df.index,
columns=df.columns,
)
def assert_equal_style(cell1, cell2, engine):
if engine in ["xlsxwriter", "openpyxl"]:
pytest.xfail(reason=(
f"GH25351: failing on some attribute comparisons in {engine}"))
# TODO: should find a better way to check equality
assert cell1.alignment.__dict__ == cell2.alignment.__dict__
assert cell1.border.__dict__ == cell2.border.__dict__
assert cell1.fill.__dict__ == cell2.fill.__dict__
assert cell1.font.__dict__ == cell2.font.__dict__
assert cell1.number_format == cell2.number_format
assert cell1.protection.__dict__ == cell2.protection.__dict__
def custom_converter(css):
# use bold iff there is custom style attached to the cell
if css.strip(" \n;"):
return {"font": {"bold": True}}
return {}
pytest.importorskip("jinja2")
pytest.importorskip(engine)
# Prepare spreadsheets
df = DataFrame(np.random.randn(11, 3))
with tm.ensure_clean(".xlsx" if engine != "xlwt" else ".xls") as path:
writer = ExcelWriter(path, engine=engine)
df.to_excel(writer, sheet_name="frame")
df.style.to_excel(writer, sheet_name="unstyled")
styled = df.style.apply(style, axis=None)
styled.to_excel(writer, sheet_name="styled")
ExcelFormatter(styled, style_converter=custom_converter).write(
writer, sheet_name="custom")
writer.save()
if engine not in ("openpyxl", "xlsxwriter"):
# For other engines, we only smoke test
return
openpyxl = pytest.importorskip("openpyxl")
wb = openpyxl.load_workbook(path)
# (1) compare DataFrame.to_excel and Styler.to_excel when unstyled
n_cells = 0
for col1, col2 in zip(wb["frame"].columns, wb["unstyled"].columns):
assert len(col1) == len(col2)
for cell1, cell2 in zip(col1, col2):
assert cell1.value == cell2.value
assert_equal_style(cell1, cell2, engine)
n_cells += 1
# ensure iteration actually happened:
assert n_cells == (11 + 1) * (3 + 1)
# (2) check styling with default converter
# TODO: openpyxl (as at 2.4) prefixes colors with 00, xlsxwriter with FF
alpha = "00" if engine == "openpyxl" else "FF"
n_cells = 0
for col1, col2 in zip(wb["frame"].columns, wb["styled"].columns):
assert len(col1) == len(col2)
for cell1, cell2 in zip(col1, col2):
ref = f"{cell2.column}{cell2.row:d}"
# TODO: this isn't as strong a test as ideal; we should
# confirm that differences are exclusive
if ref == "B2":
assert not cell1.font.bold
assert cell2.font.bold
elif ref == "C3":
assert cell1.font.color.rgb != cell2.font.color.rgb
assert cell2.font.color.rgb == alpha + "0000FF"
elif ref == "D4":
assert cell1.font.underline != cell2.font.underline
assert cell2.font.underline == "single"
elif ref == "B5":
assert not cell1.border.left.style
assert (cell2.border.top.style == cell2.border.right.style
== cell2.border.bottom.style ==
cell2.border.left.style == "medium")
elif ref == "C6":
assert not cell1.font.italic
assert cell2.font.italic
elif ref == "D7":
assert cell1.alignment.horizontal != cell2.alignment.horizontal
assert cell2.alignment.horizontal == "right"
elif ref == "B8":
assert cell1.fill.fgColor.rgb != cell2.fill.fgColor.rgb
assert cell1.fill.patternType != cell2.fill.patternType
assert cell2.fill.fgColor.rgb == alpha + "FF0000"
assert cell2.fill.patternType == "solid"
elif ref == "B9":
assert cell1.number_format == "General"
assert cell2.number_format == "0%"
else:
assert_equal_style(cell1, cell2, engine)
assert cell1.value == cell2.value
n_cells += 1
assert n_cells == (11 + 1) * (3 + 1)
# (3) check styling with custom converter
n_cells = 0
for col1, col2 in zip(wb["frame"].columns, wb["custom"].columns):
assert len(col1) == len(col2)
for cell1, cell2 in zip(col1, col2):
ref = f"{cell2.column}{cell2.row:d}"
if ref in ("B2", "C3", "D4", "B5", "C6", "D7", "B8", "B9"):
assert not cell1.font.bold
assert cell2.font.bold
else:
assert_equal_style(cell1, cell2, engine)
assert cell1.value == cell2.value
n_cells += 1
assert n_cells == (11 + 1) * (3 + 1)
#newdftran = newdftran.T
#newdftran.columns = timecols
#newdftran = newdftran.T
#new_colsname = newdftran.columns
#infodict = {'w0': u'上海','w1':u'北京','w2':u'广州','w3':u'成都','w4':u'楼程程','w5':u'窝客','w6':u'妈妈帮','w888888888':u'自然','t0':u'快速预诊','t2':u'点名预诊','t888888888':u'无','d0':u'客服接诊','d1':u'妇产科','d2':u'儿科','d3':u'内科','d888888888':u'无','r1':u'问题过于简单','r2':u'相同提问人','r3':u'一个来回','r4':u'重复问题','r5':u'疑似录音','r6':u'长时间没有回复','r888888888':u'无', 'r0':u'正常订单'}
#translatecolnames = []
#for i in new_colsname:
# separate_w = i.split('w')
# separate_w_t = separate_w[1].split('t')
# separate_w_t_d = separate_w_t[1].split('d')
# separate_w_t_d_r = separate_w_t_d[1].split('r')
# promotionway = 'w' + separate_w_t[0]
# order_type = 't' + separate_w_t_d[0]
# order_department = 'd' + separate_w_t_d_r[0]
# reason = 'r' + separate_w_t_d_r[1]
#
# translatecolname = infodict[promotionway] + infodict[order_type] + infodict[order_department] + infodict[reason]
# translatecolnames.append(translatecolname)
#newdftran.columns = translatecolnames
newdftran = newdftran[newdftran.columns[(newdftran != 0).any()]]
excelname = newdftran.index[0] + '-' + newdftran.index[-1]
excelname = datetime.datetime.now().strftime('%Y%m%d')
excelname += '上周低质量推广员订单数据.xls'
writer = ExcelWriter(excelname)
newdftran.to_excel(writer, 'sheet1')
writer.save()
print 'ok'
#print os.listdir(os.getcwd())
#os.remove(excelname)
#os.remove(excelfilename)
