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):
_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 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 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 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 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 dfs2Excel(dfs,sheetnames,filename):
'''
将一些列DataFrame保存到Excel中
'''
excelFile = ExcelWriter(filename)
for (i,df) in enumerate(dfs) :
df.to_excel(excelFile,sheetnames[i])
excelFile.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 test_ExcelWriter_dispatch(self):
with tm.assertRaisesRegexp(ValueError, 'No engine'):
writer = ExcelWriter('nothing')
_skip_if_no_openpyxl()
writer = ExcelWriter('apple.xlsx')
tm.assert_isinstance(writer, _OpenpyxlWriter)
_skip_if_no_xlwt()
writer = ExcelWriter('apple.xls')
tm.assert_isinstance(writer, _XlwtWriter)
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 test_engine_kwargs(ext, nan_inf_to_errors):
# GH 42286
engine_kwargs = {"options": {"nan_inf_to_errors": nan_inf_to_errors}}
with tm.ensure_clean(ext) as f:
with ExcelWriter(f, engine="xlsxwriter",
engine_kwargs=engine_kwargs) as writer:
assert writer.book.nan_inf_to_errors == nan_inf_to_errors
def test_append_overlay_startrow_startcol(ext, startrow, startcol, greeting,
goodbye):
df1 = DataFrame({
"greeting": ["hello", "world"],
"goodbye": ["goodbye", "people"]
})
df2 = DataFrame(["poop"])
with tm.ensure_clean(ext) as f:
df1.to_excel(f, engine="openpyxl", sheet_name="poo", index=False)
with ExcelWriter(f,
engine="openpyxl",
mode="a",
if_sheet_exists="overlay") as writer:
# use startrow+1 because we don't have a header
df2.to_excel(
writer,
index=False,
header=False,
startrow=startrow + 1,
startcol=startcol,
sheet_name="poo",
)
result = pd.read_excel(f, sheet_name="poo", engine="openpyxl")
expected = DataFrame({"greeting": greeting, "goodbye": goodbye})
tm.assert_frame_equal(result, expected)
def exportXlsx(dataFrame):
#Write Excel file
excelWriteFile = "dewe2excel_" + "_" + datetime.datetime.now().strftime(
'%Y%m%d_%H%M%S') + ".xlsx"
with ExcelWriter(excelWriteFile) as xlsxWriter:
dataFrame.between_time(intervalStart,
intervalStop).to_excel(xlsxWriter)
def test_deprecated_attr(ext, attr):
# GH#45572
with tm.ensure_clean(ext) as path:
with ExcelWriter(path, engine="xlwt") as writer:
msg = f"{attr} is not part of the public API"
with tm.assert_produces_warning(FutureWarning, match=msg):
getattr(writer, attr)
def ftth_output_combine():
with open(str(out_folder / "FTTH Interval.csv"), mode='w') as file:
ftth_file_writer = csv.writer(file, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
ftth_file_writer.writerow(
['Interval', 'CallOffered', 'CallsHandled', 'ASA', 'AHT', 'AvgTalkTime', 'AvgHoldTime', 'AbanRate',
'SLPercentage'])
i = hr
while (i < 24):
ftth_final_interval = str(i) + ":00-" + str(i + 1) + ":00"
ftth_file_writer.writerow([ftth_final_interval, "", "", " ", "", "", "", "", "" ])
i += 1
ftth_total_Df = pd.read_csv(str(out_folder / Ftth_total_name))
#print("ftth total df " total_Df)
shutil.copyfile(str(out_folder / Ftth_Header_file), str(out_folder / FtthFinal_filename))
ftth_interval_df = pd.read_csv(str(out_folder / "FTTH Interval.csv"), error_bad_lines=False)
print("interval" + ftth_interval_df)
with open(str(out_folder / FtthFinal_filename ), 'a')as append_interval:
ftth_interval_df.to_csv(append_interval, index=False, header=False)
with open(str(out_folder / FtthFinal_filename), 'a')as append_final:
ftth_total_Df.to_csv(append_final, index=False, header=False)
with ExcelWriter(str(out_folder / ftth_excel_name))as ew:
pd.read_csv(str(out_folder / FtthFinal_filename), error_bad_lines=False).to_excel(ew, sheet_name="FTTH MIS report", index=False)
print("\nAutomation done for FTTH MIS Report" + ftth_excel_name)
def test_if_sheet_exists_raises(self, ext):
# GH 40230
msg = "if_sheet_exists is only valid in append mode (mode='a')"
with tm.ensure_clean(ext) as f:
with pytest.raises(ValueError, match=re.escape(msg)):
ExcelWriter(f, if_sheet_exists="replace")
def test_set_column_names_in_parameter(self, ext):
# GH 12870 : pass down column names associated with
# keyword argument names
refdf = pd.DataFrame([[1, 'foo'], [2, 'bar'], [3, 'baz']],
columns=['a', 'b'])
with ensure_clean(ext) as pth:
with ExcelWriter(pth) as writer:
refdf.to_excel(writer,
'Data_no_head',
header=False,
index=False)
refdf.to_excel(writer, 'Data_with_head', index=False)
refdf.columns = ['A', 'B']
with ExcelFile(pth) as reader:
xlsdf_no_head = pd.read_excel(reader,
'Data_no_head',
header=None,
names=['A', 'B'])
xlsdf_with_head = pd.read_excel(reader,
'Data_with_head',
index_col=None,
names=['A', 'B'])
tm.assert_frame_equal(xlsdf_no_head, refdf)
tm.assert_frame_equal(xlsdf_with_head, refdf)
def test_register_writer(self):
# some awkward mocking to test out dispatch and such actually works
called_save = []
called_write_cells = []
class DummyClass(ExcelWriter):
called_save = False
called_write_cells = False
supported_extensions = ["xlsx", "xls"]
engine = "dummy"
def save(self):
called_save.append(True)
def write_cells(self, *args, **kwargs):
called_write_cells.append(True)
def check_called(func):
func()
assert len(called_save) >= 1
assert len(called_write_cells) >= 1
del called_save[:]
del called_write_cells[:]
with pd.option_context("io.excel.xlsx.writer", "dummy"):
register_writer(DummyClass)
writer = ExcelWriter("something.xlsx")
assert isinstance(writer, DummyClass)
df = tm.makeCustomDataframe(1, 1)
check_called(lambda: df.to_excel("something.xlsx"))
check_called(lambda: df.to_excel("something.xls", engine="dummy"))
def test_book_and_sheets_consistent(ext):
# GH#45687 - Ensure sheets is updated if user modifies book
with tm.ensure_clean(ext) as f:
with ExcelWriter(f, engine="openpyxl") as writer:
assert writer.sheets == {}
sheet = writer.book.create_sheet("test_name", 0)
assert writer.sheets == {"test_name": sheet}
def test_engine_kwargs(ext, nan_inf_to_errors):
# GH 42286
# odswriter doesn't utilize engine_kwargs, nothing to check except that it works
engine_kwargs = {"options": {"nan_inf_to_errors": nan_inf_to_errors}}
with tm.ensure_clean(ext) as f:
with ExcelWriter(f, engine="odf", engine_kwargs=engine_kwargs) as _:
pass
def test_set_column_names_in_parameter(self, ext):
# GH 12870 : pass down column names associated with
# keyword argument names
refdf = pd.DataFrame([[1, "foo"], [2, "bar"], [3, "baz"]],
columns=["a", "b"])
with tm.ensure_clean(ext) as pth:
with ExcelWriter(pth) as writer:
refdf.to_excel(writer,
"Data_no_head",
header=False,
index=False)
refdf.to_excel(writer, "Data_with_head", index=False)
refdf.columns = ["A", "B"]
with ExcelFile(pth) as reader:
xlsdf_no_head = pd.read_excel(reader,
"Data_no_head",
header=None,
names=["A", "B"])
xlsdf_with_head = pd.read_excel(reader,
"Data_with_head",
index_col=None,
names=["A", "B"])
tm.assert_frame_equal(xlsdf_no_head, refdf)
tm.assert_frame_equal(xlsdf_with_head, refdf)
def test_ExcelWriter_dispatch(self):
with tm.assertRaisesRegexp(ValueError, 'No engine'):
ExcelWriter('nothing')
try:
import xlsxwriter
writer_klass = _XlsxWriter
except ImportError:
_skip_if_no_openpyxl()
writer_klass = _OpenpyxlWriter
writer = ExcelWriter('apple.xlsx')
tm.assert_isinstance(writer, writer_klass)
_skip_if_no_xlwt()
writer = ExcelWriter('apple.xls')
tm.assert_isinstance(writer, _XlwtWriter)
def from_csv_to_excel(csv, id):
"""Makes an excel stylesheet out of a CSV (For user tests)."""
try:
with ExcelWriter(id + '.xlsx') as ew:
pd.read_csv(csv).to_excel(ew, sheet_name="sheet", index=None)
except:
pass
def test_styler_to_excel_basic(engine, css, attrs, expected):
pytest.importorskip(engine)
df = DataFrame(np.random.randn(1, 1))
styler = df.style.applymap(lambda x: css)
with tm.ensure_clean(".xlsx") as path:
with ExcelWriter(path, engine=engine) as writer:
df.to_excel(writer, sheet_name="dataframe")
styler.to_excel(writer, sheet_name="styled")
openpyxl = pytest.importorskip(
"openpyxl") # test loading only with openpyxl
with contextlib.closing(openpyxl.load_workbook(path)) as wb:
# test unstyled data cell does not have expected styles
# test styled cell has expected styles
u_cell, s_cell = wb["dataframe"].cell(2,
2), wb["styled"].cell(2, 2)
for attr in attrs:
u_cell, s_cell = getattr(u_cell, attr, None), getattr(s_cell, attr)
if isinstance(expected, dict):
assert u_cell is None or u_cell != expected[engine]
assert s_cell == expected[engine]
else:
assert u_cell is None or u_cell != expected
assert s_cell == expected
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 test_kwargs(ext, nan_inf_to_errors):
# GH 42286
kwargs = {"options": {"nan_inf_to_errors": nan_inf_to_errors}}
with tm.ensure_clean(ext) as f:
msg = re.escape("Use of **kwargs is deprecated")
with tm.assert_produces_warning(FutureWarning, match=msg):
with ExcelWriter(f, engine="xlsxwriter", **kwargs) as writer:
assert writer.book.nan_inf_to_errors == nan_inf_to_errors
def test_engine_kwargs(ext, write_only):
# GH 42286
# xlwt doesn't utilize kwargs, only test that supplying a engine_kwarg works
engine_kwargs = {"write_only": write_only}
with tm.ensure_clean(ext) as f:
with ExcelWriter(f, engine="openpyxl", engine_kwargs=engine_kwargs) as writer:
# xlwt won't allow us to close without writing something
DataFrame().to_excel(writer)
def test_book_and_sheets_consistent(ext):
# GH#45687 - Ensure sheets is updated if user modifies book
with tm.ensure_clean(ext) as f:
with ExcelWriter(f) as writer:
assert writer.sheets == {}
table = odf.table.Table(name="test_name")
writer.book.spreadsheet.addElement(table)
assert writer.sheets == {"test_name": table}
def test_ExcelWriter_dispatch(self, klass, ext):
with tm.ensure_clean(ext) as path:
writer = ExcelWriter(path)
if ext == ".xlsx" and td.safe_import("xlsxwriter"):
# xlsxwriter has preference over openpyxl if both installed
assert isinstance(writer, _XlsxWriter)
else:
assert isinstance(writer, klass)
def test_excel_writer_empty_frame(self, engine, ext):
# GH#45793
with tm.ensure_clean(ext) as path:
with ExcelWriter(path, engine=engine) as writer:
DataFrame().to_excel(writer)
result = pd.read_excel(path)
expected = DataFrame()
tm.assert_frame_equal(result, expected)
def test_engine_kwargs_write(ext, iso_dates):
# GH 42286 GH 43445
engine_kwargs = {"iso_dates": iso_dates}
with tm.ensure_clean(ext) as f:
with ExcelWriter(f, engine="openpyxl",
engine_kwargs=engine_kwargs) as writer:
assert writer.book.iso_dates == iso_dates
# ExcelWriter won't allow us to close without writing something
DataFrame().to_excel(writer)
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_engine_kwargs(ext, engine_kwargs):
# GH 42286
# GH 43445
# test for error: OpenDocumentSpreadsheet does not accept any arguments
with tm.ensure_clean(ext) as f:
if engine_kwargs is not None:
error = re.escape(
"OpenDocumentSpreadsheet() got an unexpected keyword argument 'kwarg'"
)
with pytest.raises(
TypeError,
match=error,
):
ExcelWriter(f, engine="odf", engine_kwargs=engine_kwargs)
else:
with ExcelWriter(f, engine="odf",
engine_kwargs=engine_kwargs) as _:
pass
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_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 _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 __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 test_excel_date_datetime_format(self, engine, ext):
# see gh-4133
#
# Excel output format strings
df = DataFrame([[date(2014, 1, 31),
date(1999, 9, 24)],
[datetime(1998, 5, 26, 23, 33, 4),
datetime(2014, 2, 28, 13, 5, 13)]],
index=["DATE", "DATETIME"], columns=["X", "Y"])
df_expected = DataFrame([[datetime(2014, 1, 31),
datetime(1999, 9, 24)],
[datetime(1998, 5, 26, 23, 33, 4),
datetime(2014, 2, 28, 13, 5, 13)]],
index=["DATE", "DATETIME"], columns=["X", "Y"])
with ensure_clean(ext) as filename2:
writer1 = ExcelWriter(self.path)
writer2 = ExcelWriter(filename2,
date_format="DD.MM.YYYY",
datetime_format="DD.MM.YYYY HH-MM-SS")
df.to_excel(writer1, "test1")
df.to_excel(writer2, "test1")
writer1.close()
writer2.close()
reader1 = ExcelFile(self.path)
reader2 = ExcelFile(filename2)
rs1 = pd.read_excel(reader1, "test1", index_col=0)
rs2 = pd.read_excel(reader2, "test1", index_col=0)
tm.assert_frame_equal(rs1, rs2)
# Since the reader returns a datetime object for dates,
# we need to use df_expected to check the result.
tm.assert_frame_equal(rs2, df_expected)
def test_excel_date_datetime_format(self):
_skip_if_no_xlrd()
df = DataFrame([[date(2014, 1, 31),
date(1999, 9, 24)],
[datetime(1998, 5, 26, 23, 33, 4),
datetime(2014, 2, 28, 13, 5, 13)]],
index=['DATE', 'DATETIME'], columns=['X', 'Y'])
df_expected = DataFrame([[datetime(2014, 1, 31),
datetime(1999, 9, 24)],
[datetime(1998, 5, 26, 23, 33, 4),
datetime(2014, 2, 28, 13, 5, 13)]],
index=['DATE', 'DATETIME'], columns=['X', 'Y'])
with ensure_clean(self.ext) as filename1:
with ensure_clean(self.ext) as filename2:
writer1 = ExcelWriter(filename1)
writer2 = ExcelWriter(filename2,
date_format='DD.MM.YYYY',
datetime_format='DD.MM.YYYY HH-MM-SS')
df.to_excel(writer1, 'test1')
df.to_excel(writer2, 'test1')
writer1.close()
writer2.close()
reader1 = ExcelFile(filename1)
reader2 = ExcelFile(filename2)
rs1 = reader1.parse('test1', index_col=None)
rs2 = reader2.parse('test1', index_col=None)
tm.assert_frame_equal(rs1, rs2)
# since the reader returns a datetime object for dates, we need
# to use df_expected to check the result
tm.assert_frame_equal(rs2, df_expected)
import sys, os
path = r'/home/duhan/github/CTPTrader' # 项目位置
#path = r'/home/wx/pitchersite' # 项目位置
settings = "CTPTrader.settings"
sys.path.append(path)
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']]
for c in dir(mspp.constants) : g[c] = getattr(mspp.constants, c)
for c in dir(msxl.constants) : g[c] = getattr(msxl.constants, c)
#%% 生成行名和坐标的对应关系表
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
# 为嵌入式图表计算
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)
