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_excel_date_datetime_format(self, engine, ext, path):
# 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 tm.ensure_clean(ext) as filename2:
writer1 = ExcelWriter(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(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_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 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 __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 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 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 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_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_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 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 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_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 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_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 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 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 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 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_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_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_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_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_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_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_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_kwargs(ext, nan_inf_to_errors):
# GH 42286
# odswriter doesn't utilize kwargs, nothing to check except that it works
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="odf", **kwargs) as _:
pass
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 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)
