def DFtoExcel(df, FolderName, FileName):
write_df = df.loc[:, ["FileName", "hyperlink", "Sheet Name"]]
# Path Cell_Search_By_Key
MainFolder = "C:\\Cell_Search_By_Key"
FolderPath = os.path.join(MainFolder, FolderName)
if not os.path.exists(FolderPath):
os.makedirs(FolderPath)
os.chdir(FolderPath)
ExcelName = "%s.xlsx" % FileName
writer = ExcelWriter(ExcelName)
write_df.to_excel(writer, "Result", index=False)
writer.save()
# turn path into hyperlink
Excel_Path = os.path.join(FolderPath, ExcelName)
wb = Workbook(Excel_Path)
# wb = Workbook.caller()
checkArr = Range("B2").vertical.value
i = 2
for check in checkArr:
RangeName = "B%d" % (i)
displayRange = "A%d" % (i)
address = Range(RangeName).value
display_name = Range(displayRange).value
i += 1
try:
Range(RangeName).add_hyperlink(address, text_to_display=address)
except:
pass
wb.save()
wb.close()
return "FINISH"
class TestWorkbook:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1)
self.wb.activate('Sheet1')
def tearDown(self):
self.wb.close()
def test_clear_content_active_sheet(self):
Range('G10').value = 22
self.wb.clear_contents()
cell = Range('G10').value
assert_equal(cell, None)
def test_clear_active_sheet(self):
Range('G10').value = 22
self.wb.clear()
cell = Range('G10').value
assert_equal(cell, None)
def test_clear_content(self):
Range('Sheet2', 'G10').value = 22
self.wb.clear_contents('Sheet2')
cell = Range('Sheet2', 'G10').value
assert_equal(cell, None)
def test_clear(self):
Range('Sheet2', 'G10').value = 22
self.wb.clear('Sheet2')
cell = Range('Sheet2', 'G10').value
assert_equal(cell, None)
def build_addins():
# transform code for addin use
with open(os.path.join(par_dir, "xlwings", "xlwings.bas"), "r") as vba_module, open(
os.path.join(this_dir, "xlwings_addin.bas"), "w"
) as vba_addin:
content = vba_module.read().replace("ThisWorkbook", "ActiveWorkbook")
content = content.replace('Attribute VB_Name = "xlwings"', 'Attribute VB_Name = "xlwings_addin"')
vba_addin.write(content)
# create addin workbook
wb = Workbook()
# remove unneeded sheets
for sh in list(wb.xl_workbook.Sheets)[1:]:
sh.Delete()
# rename vbproject
wb.xl_workbook.VBProject.Name = "xlwings"
# import modules
wb.xl_workbook.VBProject.VBComponents.Import(os.path.join(this_dir, "xlwings_addin.bas"))
# save to xla and xlam
wb.xl_workbook.IsAddin = True
wb.xl_workbook.Application.DisplayAlerts = False
# wb.xl_workbook.SaveAs(os.path.join(this_dir, "xlwings.xla"), FileFormat.xlAddIn)
wb.xl_workbook.SaveAs(os.path.join(this_dir, "xlwings.xlam"), FileFormat.xlOpenXMLAddIn)
wb.xl_workbook.Application.DisplayAlerts = True
# clean up
wb.close()
os.remove(os.path.join(this_dir, "xlwings_addin.bas"))
class TestApplication:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1, app_visible=False)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_screen_updating(self):
self.wb.application.screen_updating = False
assert_equal(self.wb.application.screen_updating, False)
self.wb.application.screen_updating = True
assert_equal(self.wb.application.screen_updating, True)
def test_calculation(self):
Range('A1').value = 2
Range('B1').formula = '=A1 * 2'
self.wb.application.calculation = Calculation.xlCalculationManual
Range('A1').value = 4
assert_equal(Range('B1').value, 4)
self.wb.application.calculation = Calculation.xlCalculationAutomatic
assert_equal(Range('B1').value, 8)
def build_addins():
# transform code for addin use
with open(os.path.join(par_dir, "xlwings", "xlwings.bas"), "r") as vba_module, \
open(os.path.join(this_dir, "xlwings_addin.bas"), "w") as vba_addin:
content = vba_module.read().replace("ThisWorkbook", "ActiveWorkbook")
content = content.replace('Attribute VB_Name = "xlwings"', 'Attribute VB_Name = "xlwings_addin"')
vba_addin.write(content)
# create addin workbook
wb = Workbook()
# remove unneeded sheets
for sh in list(wb.xl_workbook.Sheets)[1:]:
sh.Delete()
# rename vbproject
wb.xl_workbook.VBProject.Name = "xlwings"
# import modules
wb.xl_workbook.VBProject.VBComponents.Import(os.path.join(this_dir, "xlwings_addin.bas"))
# save to xla and xlam
wb.xl_workbook.IsAddin = True
wb.xl_workbook.Application.DisplayAlerts = False
# wb.xl_workbook.SaveAs(os.path.join(this_dir, "xlwings.xla"), FileFormat.xlAddIn)
wb.xl_workbook.SaveAs(os.path.join(this_dir, "xlwings.xlam"), FileFormat.xlOpenXMLAddIn)
wb.xl_workbook.Application.DisplayAlerts = True
# clean up
wb.close()
os.remove(os.path.join(this_dir, 'xlwings_addin.bas'))
def test_two_wkb(self):
wb2 = Workbook(app_visible=False, app_target=APP_TARGET)
pic1 = Picture.add(sheet=1, name='pic1', filename=os.path.join(this_dir, 'sample_picture.png'))
pic2 = Picture.add(sheet=1, name='pic1', filename=os.path.join(this_dir, 'sample_picture.png'), wkb=self.wb)
assert_equal(pic1.name, 'pic1')
assert_equal(pic2.name, 'pic1')
wb2.close()
def tableau_data():
Workbook.caller()
save_path = str(dr_pivot_path())
save_path = save_path[:save_path.rindex('\\')]
ddr_data = dr.raw_pivot()
d = dr.tableau_campaign_data(ddr_data)
s = search.merge_data()
tableau = d.append(s)
tableau['Quarter'] = qquarter()
if Range('merged', 'A1').value is None:
chunk_df(tableau, 'merged', 'A1')
# If data is already present in the tab, the two data sets are merged together and then copied into the data tab.
else:
past_data = pd.read_excel(dr_pacing_path(), 'merged', index_col=None)
past_data = past_data[past_data['Campaign'] != 'Search']
appended_data = past_data.append(tableau)
Sheet('merged').clear()
chunk_df(appended_data, 'merged', 'A1')
#Range('Sheet3', 'AT1').value = pd.to_datetime(ddr_data['Date'].max()) + datetime.timedelta(days= 1)
wb = Workbook()
Sheet('Sheet1').name = 'DDR Data'
chunk_df(ddr_data, 'DDR Data', 'A1')
wb.save(save_path + '\\' + 'DR_Raw_Data.xlsx')
wb.close()
class TestApplication:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1, app_visible=False, app_target=APP_TARGET)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_screen_updating(self):
Application(wkb=self.wb).screen_updating = False
assert_equal(Application(wkb=self.wb).screen_updating, False)
Application(wkb=self.wb).screen_updating = True
assert_equal(Application(wkb=self.wb).screen_updating, True)
def test_calculation(self):
Range('A1').value = 2
Range('B1').formula = '=A1 * 2'
app = Application(wkb=self.wb)
app.calculation = Calculation.xlCalculationManual
Range('A1').value = 4
assert_equal(Range('B1').value, 4)
app.calculation = Calculation.xlCalculationAutomatic
app.calculate(
) # This is needed on Mac Excel 2016 but not on Mac Excel 2011 (changed behaviour)
assert_equal(Range('B1').value, 8)
Range('A1').value = 2
assert_equal(Range('B1').value, 4)
class TestApplication:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1, app_visible=False, app_target=APP_TARGET)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_screen_updating(self):
Application(wkb=self.wb).screen_updating = False
assert_equal(Application(wkb=self.wb).screen_updating, False)
Application(wkb=self.wb).screen_updating = True
assert_equal(Application(wkb=self.wb).screen_updating, True)
def test_calculation(self):
Range('A1').value = 2
Range('B1').formula = '=A1 * 2'
app = Application(wkb=self.wb)
app.calculation = Calculation.xlCalculationManual
Range('A1').value = 4
assert_equal(Range('B1').value, 4)
app.calculation = Calculation.xlCalculationAutomatic
app.calculate() # This is needed on Mac Excel 2016 but not on Mac Excel 2011 (changed behaviour)
assert_equal(Range('B1').value, 8)
Range('A1').value = 2
assert_equal(Range('B1').value, 4)
def test_unicode_path(self):
# pip3 seems to struggle with unicode filenames
src = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'unicode_path.xlsx')
dst = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'ünicödé_päth.xlsx')
shutil.move(src, dst)
wb = Workbook(os.path.join(os.path.dirname(os.path.abspath(__file__)), 'ünicödé_päth.xlsx'), app_target=APP_TARGET)
Range('A1').value = 1
wb.close()
shutil.move(dst, src)
def test_get_set_named_range(self):
wb = Workbook()
Range('A1').name = 'test1'
assert_equal(Range('A1').name, 'test1')
Range('A2:B4').name = 'test2'
assert_equal(Range('A2:B4').name, 'test2')
wb.close()
def test_get_set_named_range(self):
wb = Workbook()
Range('A1').name = 'test1'
assert_equal(Range('A1').name, 'test1')
Range('A2:B4').name = 'test2'
assert_equal(Range('A2:B4').name, 'test2')
wb.close()
def test_unicode_path(self):
# pip3 seems to struggle with unicode filenames
src = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'unicode_path.xlsx')
dst = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'ünicödé_päth.xlsx')
shutil.move(src, dst)
wb = Workbook(os.path.join(os.path.dirname(os.path.abspath(__file__)), 'ünicödé_päth.xlsx'), app_visible=False, app_target=APP_TARGET)
Range('A1').value = 1
wb.close()
shutil.move(dst, src)
def output_forecasts(pacing_data):
pacing_data['Week'] = pacing_data['Date'].apply(lambda x: main.monday_week_start(x))
pacing_data = pd.pivot_table(pacing_data, index= ['Site', 'Tactic', 'Metric'],
columns= ['Week'], values= 'value', aggfunc= np.sum).reset_index()
wb = Workbook(main.dr_pacing_path())
Sheet('forecast_data').clear_contents()
Range('forecast_data', 'A1', index= False).value = pacing_data
wb.save()
wb.close()
def test_unicode_path(self):
# pip3 seems to struggle with unicode filenames
src = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'unicode_path.xlsx')
if sys.platform.startswith('darwin') and os.path.isdir(os.path.expanduser("~") + '/Library/Containers/com.microsoft.Excel/Data/'):
dst = os.path.join(os.path.expanduser("~") + '/Library/Containers/com.microsoft.Excel/Data/',
'ünicödé_päth.xlsx')
else:
dst = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'ünicödé_päth.xlsx')
shutil.copy(src, dst)
wb = Workbook(dst, app_visible=False, app_target=APP_TARGET)
Range('A1').value = 1
wb.close()
os.remove(dst)
def test_reference_two_unsaved_wb(self):
"""Covers GH Issue #63"""
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
wb2 = Workbook(app_visible=False, app_target=APP_TARGET)
Range('A1').value = 2. # wb2
Range('A1', wkb=wb1).value = 1. # wb1
assert_equal(Range('A1').value, 2.)
assert_equal(Range('A1', wkb=wb1).value, 1.)
wb1.close()
wb2.close()
def test_reference_two_unsaved_wb(self):
"""Covers GH Issue #63"""
wb1 = Workbook()
wb2 = Workbook()
Range('A1').value = 2. # wb2
Range('A1', wkb=wb1).value = 1. # wb1
assert_equal(Range('A1').value, 2.)
assert_equal(Range('A1', wkb=wb1).value, 1.)
wb1.close()
wb2.close()
def test_reference_two_unsaved_wb(self):
"""Covers GH Issue #63"""
wb1 = Workbook()
wb2 = Workbook()
Range('A1').value = 2. # wb2
Range('A1', wkb=wb1).value = 1. # wb1
assert_equal(Range('A1').value, 2.)
assert_equal(Range('A1', wkb=wb1).value, 1.)
wb1.close()
wb2.close()
def test_reference_two_unsaved_wb(self):
"""Covers GH Issue #63"""
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
wb2 = Workbook(app_visible=False, app_target=APP_TARGET)
Range('A1').value = 2. # wb2
Range('A1', wkb=wb1).value = 1. # wb1
assert_equal(Range('A1').value, 2.)
assert_equal(Range('A1', wkb=wb1).value, 1.)
wb1.close()
wb2.close()
def test_save_path(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, 'TestFile.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save(target_file_path)
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False, app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_save_path(self):
cwd = os.getcwd()
wb1 = Workbook()
target_file_path = os.path.join(cwd, 'TestFile.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save(target_file_path)
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_save_path(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, 'TestFile.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save(target_file_path)
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False, app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_save_naked(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False)
target_file_path = os.path.join(cwd, wb1.name + '.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save()
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
class TestSheet:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_activate(self):
Sheet('Sheet2').activate()
assert_equal(Sheet.active().name, 'Sheet2')
Sheet(3).activate()
assert_equal(Sheet.active().index, 3)
def test_name(self):
Sheet(1).name = 'NewName'
assert_equal(Sheet(1).name, 'NewName')
def test_index(self):
assert_equal(Sheet('Sheet1').index, 1)
def test_clear_content_active_sheet(self):
Range('G10').value = 22
Sheet.active().clear_contents()
cell = Range('G10').value
assert_equal(cell, None)
def test_clear_active_sheet(self):
Range('G10').value = 22
Sheet.active().clear()
cell = Range('G10').value
assert_equal(cell, None)
def test_clear_content(self):
Range('Sheet2', 'G10').value = 22
Sheet('Sheet2').clear_contents()
cell = Range('Sheet2', 'G10').value
assert_equal(cell, None)
def test_clear(self):
Range('Sheet2', 'G10').value = 22
Sheet('Sheet2').clear()
cell = Range('Sheet2', 'G10').value
assert_equal(cell, None)
class TestChart:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'test_chart_1.xlsx')
self.wb = Workbook(xl_file1, app_visible=False, app_target=APP_TARGET)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_add_keywords(self):
name = 'My Chart'
chart_type = ChartType.xlLine
Range('A1').value = chart_data
chart = Chart.add(chart_type=chart_type,
name=name,
source_data=Range('A1').table)
chart_actual = Chart(name)
name_actual = chart_actual.name
chart_type_actual = chart_actual.chart_type
assert_equal(name, name_actual)
if sys.platform.startswith('win'):
assert_equal(chart_type, chart_type_actual)
else:
assert_equal(kw.line_chart, chart_type_actual)
def test_add_properties(self):
name = 'My Chart'
chart_type = ChartType.xlLine
Range('Sheet2', 'A1').value = chart_data
chart = Chart.add('Sheet2')
chart.chart_type = chart_type
chart.name = name
chart.set_source_data(Range('Sheet2', 'A1').table)
chart_actual = Chart('Sheet2', name)
name_actual = chart_actual.name
chart_type_actual = chart_actual.chart_type
assert_equal(name, name_actual)
if sys.platform.startswith('win'):
assert_equal(chart_type, chart_type_actual)
else:
assert_equal(kw.line_chart, chart_type_actual)
def test_save_naked(self):
if sys.platform.startswith('darwin'):
os.chdir(os.path.expanduser("~") + '/Library/Containers/com.microsoft.Excel/Data/')
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, wb1.name + '.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save()
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False, app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
class TestWorkbook:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_name(self):
assert_equal(self.wb.name, 'test_workbook_1.xlsx')
def test_active_sheet(self):
assert_equal(self.wb.active_sheet.name, 'Sheet1')
def test_current(self):
assert_equal(self.wb.xl_workbook, Workbook.current().xl_workbook)
def test_set_current(self):
wb2 = Workbook()
assert_equal(Workbook.current().xl_workbook, wb2.xl_workbook)
self.wb.set_current()
assert_equal(Workbook.current().xl_workbook, self.wb.xl_workbook)
wb2.close()
def test_get_selection(self):
Range('A1').value = 1000
assert_equal(self.wb.get_selection().value, 1000)
def test_reference_two_unsaved_wb(self):
"""Covers GH Issue #63"""
wb1 = Workbook()
wb2 = Workbook()
Range('A1').value = 2. # wb2
Range('A1', wkb=wb1).value = 1. # wb1
assert_equal(Range('A1').value, 2.)
assert_equal(Range('A1', wkb=wb1).value, 1.)
wb1.close()
wb2.close()
def test_add_wkb(self):
# test use of add with wkb argument
# Connect to an alternative test file and make Sheet1 the active sheet
xl_file = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_range_1.xlsx')
wb_2nd = Workbook(xl_file, app_visible=False, app_target=APP_TARGET)
n_before = [sh.name for sh in Sheet.all(wkb=wb_2nd)]
Sheet.add(name="default", wkb=wb_2nd)
Sheet.add(name="after1", after=1, wkb=wb_2nd)
Sheet.add(name="before1", before=1, wkb=wb_2nd)
n_after = [sh.name for sh in Sheet.all(wkb=wb_2nd)]
n_before.append("default")
n_before.insert(1, "after1")
n_before.insert(0, "before1")
assert_equal(n_before, n_after)
wb_2nd.close()
def getIndex(workbook, index_header, indexes, shifters):
vol_header = "<volatility surface_type=\"FIXED\" type=\"PARAMS_INTERPOLATED\" " + \
"value=\"VOL_PARAM_SABR\" vol_nature=\"NORMAL\">\n" + \
"<interpolation extrapolate=\"true\" flat_extrapolation=\"true\" " + \
"left_side_derivatives=\"false\" magnitude=\"VOL\" method=\"LINEAR\"/>\n" + \
"<maturities_defaults type=\"SABR_NORMAL\"/>\n<maturities>\n"
item_sep = "<maturity value=\"{date}\">\n<parameters>\n"
item = "<parameter name=\"{param_name}\" value=\"{param_value}\" />\n"
item_padding = "</parameters>\n</maturity>\n"
index_padding = "</maturities>\n</volatility>\n</marketdata_index>\n"
lowerK = 0.0
upperK = 10
result = ""
wb = Workbook(workbook)
wb.set_current()
for index, shifter in zip(indexes, shifters):
result += index_header.format(index_name=index, tenor_shifter=shifter)
result += vol_header
rawFrame = Range(index, "CalibratedModelParams").value
for line in filter(lambda x: any(x), rawFrame[1:]):
date = line[0]
if isinstance(date, dt.datetime):
date = datetime_to_xldate(date)
result += item_sep.format(date = int(date))
result += item.format(param_name = "alpha", param_value=line[1])
result += item.format(param_name = "beta", param_value=line[2])
result += item.format(param_name = "rho", param_value=line[3])
result += item.format(param_name = "nu", param_value=line[4])
result += item.format(param_name = "lower_k", param_value=lowerK)
result += item.format(param_name = "upper_k", param_value=upperK)
result += item.format(param_name = "displacement", param_value=line[5])
result += item_padding
result += index_padding
wb.close()
return result
class TestChart:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_chart_1.xlsx')
self.wb = Workbook(xl_file1, app_visible=False, app_target=APP_TARGET)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_add_keywords(self):
name = 'My Chart'
chart_type = ChartType.xlLine
Range('A1').value = chart_data
chart = Chart.add(chart_type=chart_type, name=name, source_data=Range('A1').table)
chart_actual = Chart(name)
name_actual = chart_actual.name
chart_type_actual = chart_actual.chart_type
assert_equal(name, name_actual)
if sys.platform.startswith('win'):
assert_equal(chart_type, chart_type_actual)
else:
assert_equal(kw.line_chart, chart_type_actual)
def test_add_properties(self):
name = 'My Chart'
chart_type = ChartType.xlLine
Range('Sheet2', 'A1').value = chart_data
chart = Chart.add('Sheet2')
chart.chart_type = chart_type
chart.name = name
chart.set_source_data(Range('Sheet2', 'A1').table)
chart_actual = Chart('Sheet2', name)
name_actual = chart_actual.name
chart_type_actual = chart_actual.chart_type
assert_equal(name, name_actual)
if sys.platform.startswith('win'):
assert_equal(chart_type, chart_type_actual)
else:
assert_equal(kw.line_chart, chart_type_actual)
def pacing():
a = '2015-09-28'
b = '2015-10-04'
c = '2015-10-05'
d = '2015-12-27'
e = '2015-12-28'
f = '2015-12-31'
date_rng = pd.date_range(main.quarter_start(), periods=92, freq='D')
date_df = pd.DataFrame(date_rng, columns=['Date'])
pacing_wb = Workbook(main.dr_pacing_path())
pacing_wb.set_current()
Application(wkb=pacing_wb).xl_app.Run('Clean_Pacing_Data')
pacing_data = pd.DataFrame(Range('Q4 DDR Pacing by sub-tactic', 'Z1').table.value,
columns=Range('Q4 DDR Pacing by sub-tactic', 'Z1').horizontal.value)
pacing_data.drop(0, inplace=True)
Range('Q4 DDR Pacing by sub-tactic', 'Z1').table.clear_contents()
pacing_wb.close()
pacing_data.set_index('Week', inplace=True)
pacing_data = pacing_data.resample('1D', fill_method='pad')
pacing_data.fillna(method='ffill', inplace= True)
pacing_data.reset_index(inplace=True)
pacing_data.rename(columns={'Week': 'Date'}, inplace=True)
pacing_data = pd.merge(pacing_data, date_df, how='right', left_on='Date', right_on='Date')
pacing_data.fillna(method='ffill', inplace=True)
pacing_data.ix[a:b] = pacing_data.ix[a:b] / 4
pacing_data.ix[c:d] = pacing_data.ix[c:d] / 7
pacing_data.ix[e:f] = pacing_data.ix[e:f] / 4
pacing_data.set_index('Date', inplace= True)
return pacing_data
def test_save_path(self):
if sys.platform.startswith('darwin'):
folder = os.path.expanduser("~") + '/Library/Containers/com.microsoft.Excel/Data/'
if os.path.isdir(folder):
os.chdir(folder)
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, 'TestFile.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save(target_file_path)
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False, app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
class TestChart:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'test_chart_1.xlsx')
self.wb = Workbook(xl_file1)
self.wb.activate('Sheet1')
def tearDown(self):
self.wb.close()
def test_add_keywords(self):
name = 'My Chart'
chart_type = ChartType.xlLine
Range('A1').value = chart_data
chart = Chart().add(chart_type=chart_type,
name=name,
source_data=Range('A1').table)
chart_actual = Chart(name)
name_actual = chart_actual.name
chart_type_actual = chart_actual.chart_type
assert_equal(name, name_actual)
assert_equal(chart_type, chart_type_actual)
def test_add_properties(self):
name = 'My Chart'
chart_type = ChartType.xlLine
Range('Sheet2', 'A1').value = chart_data
chart = Chart().add('Sheet2')
chart.chart_type = chart_type
chart.name = name
chart.set_source_data(Range('Sheet2', 'A1').table)
chart_actual = Chart('Sheet2', name)
name_actual = chart_actual.name
chart_type_actual = chart_actual.chart_type
assert_equal(name, name_actual)
assert_equal(chart_type, chart_type_actual)
def get_spot_purchase_values(year):
ROOT_DIR = os.path.dirname(os.getcwd())
if year < 2005:
raise ValueError('Data only goes back to 2005')
elif year < 2015:
wb = Workbook(os.path.join(ROOT_DIR,
'results/MomPop{0}.xlsm'.format(year)),
app_visible=False)
else:
wb = Workbook(os.path.join(
ROOT_DIR, 'results/notgrapefruit{0}.xlsm'.format(year)),
app_visible=False)
raw_price_df = pd.DataFrame(np.array(
Range('grove', 'C5:N10', atleast_2d=True).value),
columns=Range('grove', 'C4:N4').value,
index=Range('grove', 'B5:B10').value)
exchange_rate_df = pd.DataFrame(np.array(
Range('grove', 'C14:N15', atleast_2d=True).value),
columns=Range('grove', 'C13:N13').value,
index=Range('grove', 'B14:B15').value)
quantity_mat_weekly = np.array(
Range('grove', 'C38:AX43', atleast_2d=True).value)
# The matrix retrieved has values for each week. We want an average weekly
# quantity for the given month.
quantity_mat_av = np.zeros((6, 12))
for i in xrange(0, 12):
this_month = quantity_mat_weekly[:, (4 * i):(4 * i + 4)]
quantity_mat_av[:, i] = np.mean(this_month, 1)
quantity_df = pd.DataFrame(quantity_mat_av,
index=Range('grove', 'B38:B43').value,
columns=Range('grove', 'C4:N4').value)
wb.close()
return (raw_price_df, exchange_rate_df, quantity_df)
class TestChart:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_chart_1.xlsx')
self.wb = Workbook(xl_file1)
self.wb.activate('Sheet1')
def tearDown(self):
self.wb.close()
def test_add_keywords(self):
name = 'My Chart'
chart_type = ChartType.xlLine
Range('A1').value = chart_data
chart = Chart().add(chart_type=chart_type, name=name, source_data=Range('A1').table)
chart_actual = Chart(name)
name_actual = chart_actual.name
chart_type_actual = chart_actual.chart_type
assert_equal(name, name_actual)
assert_equal(chart_type, chart_type_actual)
def test_add_properties(self):
name = 'My Chart'
chart_type = ChartType.xlLine
Range('Sheet2', 'A1').value = chart_data
chart = Chart().add('Sheet2')
chart.chart_type = chart_type
chart.name = name
chart.set_source_data(Range('Sheet2', 'A1').table)
chart_actual = Chart('Sheet2', name)
name_actual = chart_actual.name
chart_type_actual = chart_actual.chart_type
assert_equal(name, name_actual)
assert_equal(chart_type, chart_type_actual)
def run_concatenation(directory=None):
import re, os
from xlwings import Workbook, Range, Application
wb = Workbook(app_visible=False)
lastRow = 3
newlastRow = lastRow
lastColumn = 6
if directory is None:
directory = os.getcwd()
for file in os.listdir(directory):
findling = re.search(r'.*Data.*[0-9]{2,3}.*.xls$', file)
if findling:
wb2 = Workbook(os.path.abspath(findling.group()), \
app_visible=False)
addedRows = Range((4, 1), wkb = wb2).table.last_cell.row - 3
newlastRow = lastRow + addedRows
Range((lastRow+1, lastColumn+1), wkb = wb).value = \
findling.group()
Range((lastRow+1, 1), wkb = wb).table.value = \
Range((4, 1), (newlastRow, lastColumn), wkb = wb2).value
lastRow = newlastRow
wb2.close()
wb.save(os.path.join(os.getcwd(), 'Output.xlsx'))
wb.close()
class TestSheet:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1, app_visible=False, app_target=APP_TARGET)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_activate(self):
Sheet('Sheet2').activate()
assert_equal(Sheet.active().name, 'Sheet2')
Sheet(3).activate()
assert_equal(Sheet.active().index, 3)
def test_name(self):
Sheet(1).name = 'NewName'
assert_equal(Sheet(1).name, 'NewName')
def test_index(self):
assert_equal(Sheet('Sheet1').index, 1)
def test_clear_content_active_sheet(self):
Range('G10').value = 22
Sheet.active().clear_contents()
cell = Range('G10').value
assert_equal(cell, None)
def test_clear_active_sheet(self):
Range('G10').value = 22
Sheet.active().clear()
cell = Range('G10').value
assert_equal(cell, None)
def test_clear_content(self):
Range('Sheet2', 'G10').value = 22
Sheet('Sheet2').clear_contents()
cell = Range('Sheet2', 'G10').value
assert_equal(cell, None)
def test_clear(self):
Range('Sheet2', 'G10').value = 22
Sheet('Sheet2').clear()
cell = Range('Sheet2', 'G10').value
assert_equal(cell, None)
def test_autofit(self):
Range('Sheet1', 'A1:D4').value = 'test_string'
Sheet('Sheet1').autofit()
Sheet('Sheet1').autofit('r')
Sheet('Sheet1').autofit('c')
Sheet('Sheet1').autofit('rows')
Sheet('Sheet1').autofit('columns')
def test_add_before(self):
new_sheet = Sheet.add(before='Sheet1')
assert_equal(Sheet(1).name, new_sheet.name)
def test_add_after(self):
Sheet.add(after=Sheet.count())
assert_equal(Sheet(Sheet.count()).name, Sheet.active().name)
Sheet.add(after=1)
assert_equal(Sheet(2).name, Sheet.active().name)
def test_add_default(self):
# TODO: test call without args properly
Sheet.add()
def test_add_named(self):
Sheet.add('test', before=1)
assert_equal(Sheet(1).name, 'test')
def test_count(self):
count = Sheet.count()
assert_equal(count, 3)
def test_all(self):
all_names = [i.name for i in Sheet.all()]
assert_equal(all_names, ['Sheet1', 'Sheet2', 'Sheet3'])
# Prepare Third Sheet
sheet3 = pd.read_csv(file_iv, delim_whitespace=True)
Sheet.add('csv_iv',after='csv_ir')
Range('csv_iv','A1',index=False).value = sheet3
Range('csv_iv','A:ZZ').autofit('c')
# Save WorkSheet
xlspath = xls_dir + "reference_view_balance_" + datetime + ".xlsx"
wb.save(xlspath)
wb.close()
# Empty Files
open( file_itogo,"w").close()
open( file_ir,"w").close()
open( file_iv,"w").close()
else:
print ( "Check File Size, It shouldn't be empty")
else:
print ("Check All files exists ")
ROJ_betas = [None] * len(YEARS)
FCOJ_betas = [None] * len(YEARS)
for i, year in enumerate(YEARS):
init_wb = Workbook(os.path.join(
ROOT_DIR, 'decisions/notgrapefruit{0}_init.xlsm'.format(year)),
app_visible=False)
ORA_init_prices = np.array(
Range('pricing', 'D6:O12', atleast_2d=True).value)
POJ_init_prices = np.array(
Range('pricing', 'D15:O21', atleast_2d=True).value)
ROJ_init_prices = np.array(
Range('pricing', 'D24:O30', atleast_2d=True).value)
FCOJ_init_prices = np.array(
Range('pricing', 'D33:O39', atleast_2d=True).value)
init_wb.close()
tuned_wb = Workbook(os.path.join(
ROOT_DIR, 'decisions/notgrapefruit{0}.xlsm'.format(year)),
app_visible=False)
ORA_tuned_prices = np.array(
Range('pricing', 'D6:O12', atleast_2d=True).value)
POJ_tuned_prices = np.array(
Range('pricing', 'D15:O21', atleast_2d=True).value)
ROJ_tuned_prices = np.array(
Range('pricing', 'D24:O30', atleast_2d=True).value)
FCOJ_tuned_prices = np.array(
Range('pricing', 'D33:O39', atleast_2d=True).value)
ORA_betas[i] = ORA_tuned_prices - ORA_init_prices
POJ_betas[i] = POJ_tuned_prices - POJ_init_prices
def publishers(dr):
cd, t2t, fbx, search, pros, aal = data_transform.dr_placement_types()
week = week_of(dr)
# Publisher Performance
pub_dr = dr[(dr['Campaign'] == 'DR') & (dr['Date'] >= main.quarter_start())]
pub_dr = pub_dr.groupby(['Site', 'Placement Messaging Type', 'Week', 'Date'])
pub_dr = pd.DataFrame(pub_dr.sum()).reset_index()
#pub_dr = pub_dr[(pub_dr['NTC Media Cost'] != 0)]
pub_dr['Tactic'] = np.where((pub_dr['Placement Messaging Type'].str.contains(cd) == True) &
(pub_dr['Placement Messaging Type'].str.contains(fbx) != True), 'C/D Remessaging',
np.where(pub_dr['Placement Messaging Type'].str.contains(fbx) == True,
'FBX Remessaging', pub_dr['Placement Messaging Type']))
# Quarter
q_dr = pub_dr[pub_dr['Date'] >= main.quarter_start()]
q_dr = q_dr.groupby(['Site', 'Tactic'])
q_dr = pd.DataFrame(q_dr.sum()).reset_index()
q_dr = goals(q_dr)
# Last Week
last_week = pub_dr[pub_dr['Week'] == pub_dr['Week'].max()]
last_week = last_week.groupby(['Site', 'Tactic'])
last_week = pd.DataFrame(last_week.sum()).reset_index()
last_week.rename(columns={'NET Media Cost': week}, inplace= True)
# Publishers Overall
sites = q_dr.groupby('Site')
sites = pd.DataFrame(sites.sum()).reset_index()
sites['CPGA'] = sites['NET Media Cost'] / sites['Total GAs']
# Brand Remessaging
br = dr[dr['Campaign'] == 'Brand Remessaging']
br_quarter = br[br['Date'] >= main.quarter_start()]
br_lw = br[br['Week'] == br['Week'].max()]
br_quarter = br_quarter.groupby('Site')
br_quarter = pd.DataFrame(br_quarter.sum().reset_index())
br_quarter['Traffic Yield'] = br_quarter['Total Traffic Actions'].astype(float) / \
br_quarter['Impressions'].astype(float)
br_lw = br_lw.groupby('Site')
br_lw = pd.DataFrame(br_lw.sum().reset_index())
pacing_wb = Workbook(main.dr_pacing_path())
pacing_wb.set_current()
Range('Publisher Performance', 'A13', index=False, header=False).value = q_dr[
['Site', 'Tactic', 'Total GAs']]
Range('Publisher Performance', 'E13', index= False, header= False).value = q_dr['NET Media Cost']
Range('Publisher Performance', 'D13', index= False, header= False).value = last_week[week]
Range('Publisher Performance', 'D12').value = week
Range('Publisher Performance', 'A32', index= False, header= False).value = sites[['Site', 'Total GAs', 'CPGA']]
Range('Publisher Performance', 'C7').value = pub_dr['Week'].max().strftime('%m/%d/%Y')
Range('Publisher Performance', 'B45', index= False, header= False).value = \
br_quarter[['Traffic Yield', 'Impressions', 'Total Traffic Actions']]
Range('Publisher Performance', 'G45', index= False, header= False).value = br_quarter['NET Media Cost']
Range('Publisher Performance', 'F45', index= False, header= False).value = br_lw['NET Media Cost']
Range('Publisher Performance', 'F44').value = week
pacing_wb.save()
pacing_wb.close()
class TestWorkbook:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_name(self):
assert_equal(self.wb.name, 'test_workbook_1.xlsx')
def test_active_sheet(self):
assert_equal(self.wb.active_sheet.name, 'Sheet1')
def test_current(self):
assert_equal(self.wb.xl_workbook, Workbook.current().xl_workbook)
def test_set_current(self):
wb2 = Workbook()
assert_equal(Workbook.current().xl_workbook, wb2.xl_workbook)
self.wb.set_current()
assert_equal(Workbook.current().xl_workbook, self.wb.xl_workbook)
wb2.close()
def test_get_selection(self):
Range('A1').value = 1000
assert_equal(self.wb.get_selection().value, 1000)
def test_reference_two_unsaved_wb(self):
"""Covers GH Issue #63"""
wb1 = Workbook()
wb2 = Workbook()
Range('A1').value = 2. # wb2
Range('A1', wkb=wb1).value = 1. # wb1
assert_equal(Range('A1').value, 2.)
assert_equal(Range('A1', wkb=wb1).value, 1.)
wb1.close()
wb2.close()
def test_save_naked(self):
cwd = os.getcwd()
wb1 = Workbook()
target_file_path = os.path.join(cwd, wb1.name + '.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save()
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_save_path(self):
cwd = os.getcwd()
wb1 = Workbook()
target_file_path = os.path.join(cwd, 'TestFile.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save(target_file_path)
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
class TestSheet:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1, app_visible=False, app_target=APP_TARGET)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_activate(self):
Sheet('Sheet2').activate()
assert_equal(Sheet.active().name, 'Sheet2')
Sheet(3).activate()
assert_equal(Sheet.active().index, 3)
def test_name(self):
Sheet(1).name = 'NewName'
assert_equal(Sheet(1).name, 'NewName')
def test_index(self):
assert_equal(Sheet('Sheet1').index, 1)
def test_clear_content_active_sheet(self):
Range('G10').value = 22
Sheet.active().clear_contents()
cell = Range('G10').value
assert_equal(cell, None)
def test_clear_active_sheet(self):
Range('G10').value = 22
Sheet.active().clear()
cell = Range('G10').value
assert_equal(cell, None)
def test_clear_content(self):
Range('Sheet2', 'G10').value = 22
Sheet('Sheet2').clear_contents()
cell = Range('Sheet2', 'G10').value
assert_equal(cell, None)
def test_clear(self):
Range('Sheet2', 'G10').value = 22
Sheet('Sheet2').clear()
cell = Range('Sheet2', 'G10').value
assert_equal(cell, None)
def test_autofit(self):
Range('Sheet1', 'A1:D4').value = 'test_string'
Sheet('Sheet1').autofit()
Sheet('Sheet1').autofit('r')
Sheet('Sheet1').autofit('c')
Sheet('Sheet1').autofit('rows')
Sheet('Sheet1').autofit('columns')
def test_add_before(self):
new_sheet = Sheet.add(before='Sheet1')
assert_equal(Sheet(1).name, new_sheet.name)
def test_add_after(self):
Sheet.add(after=Sheet.count())
assert_equal(Sheet(Sheet.count()).name, Sheet.active().name)
Sheet.add(after=1)
assert_equal(Sheet(2).name, Sheet.active().name)
def test_add_default(self):
# TODO: test call without args properly
Sheet.add()
def test_add_named(self):
Sheet.add('test', before=1)
assert_equal(Sheet(1).name, 'test')
@raises(Exception)
def test_add_name_already_taken(self):
Sheet.add('Sheet1')
def test_count(self):
count = Sheet.count()
assert_equal(count, 3)
def test_all(self):
all_names = [i.name for i in Sheet.all()]
assert_equal(all_names, ['Sheet1', 'Sheet2', 'Sheet3'])
def test_set_current(self):
wb2 = Workbook(app_visible=False, app_target=APP_TARGET)
assert_equal(Workbook.current().xl_workbook, wb2.xl_workbook)
self.wb.set_current()
assert_equal(Workbook.current().xl_workbook, self.wb.xl_workbook)
wb2.close()
def test_set_current(self):
wb2 = Workbook(app_visible=False, app_target=APP_TARGET)
assert_equal(Workbook.current().xl_workbook, wb2.xl_workbook)
self.wb.set_current()
assert_equal(Workbook.current().xl_workbook, self.wb.xl_workbook)
wb2.close()
class TestRange:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_range_1.xlsx')
self.wb = Workbook(xl_file1, app_visible=False, app_target=APP_TARGET)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_cell(self):
params = [('A1', 22),
((1,1), 22),
('A1', 22.2222),
((1,1), 22.2222),
('A1', 'Test String'),
((1,1), 'Test String'),
('A1', 'éöà'),
((1,1), 'éöà'),
('A2', test_date_1),
((2,1), test_date_1),
('A3', test_date_2),
((3,1), test_date_2)]
for param in params:
yield self.check_cell, param[0], param[1]
def check_cell(self, address, value):
# Active Sheet
Range(address).value = value
cell = Range(address).value
assert_equal(cell, value)
# SheetName
Range('Sheet2', address).value = value
cell = Range('Sheet2', address).value
assert_equal(cell, value)
# SheetIndex
Range(3, address).value = value
cell = Range(3, address).value
assert_equal(cell, value)
def test_range_address(self):
""" Style: Range('A1:C3') """
address = 'C1:E3'
# Active Sheet
Range(address[:2]).value = data # assign to starting cell only
cells = Range(address).value
assert_equal(cells, data)
# Sheetname
Range('Sheet2', address).value = data
cells = Range('Sheet2', address).value
assert_equal(cells, data)
# Sheetindex
Range(3, address).value = data
cells = Range(3, address).value
assert_equal(cells, data)
def test_range_index(self):
""" Style: Range((1,1), (3,3)) """
index1 = (1,3)
index2 = (3,5)
# Active Sheet
Range(index1, index2).value = data
cells = Range(index1, index2).value
assert_equal(cells, data)
# Sheetname
Range('Sheet2', index1, index2).value = data
cells = Range('Sheet2', index1, index2).value
assert_equal(cells, data)
# Sheetindex
Range(3, index1, index2).value = data
cells = Range(3, index1, index2).value
assert_equal(cells, data)
def test_named_range_value(self):
value = 22.222
# Active Sheet
Range('cell_sheet1').value = value
cells = Range('cell_sheet1').value
assert_equal(cells, value)
Range('range_sheet1').value = data
cells = Range('range_sheet1').value
assert_equal(cells, data)
# Sheetname
Range('Sheet2', 'cell_sheet2').value = value
cells = Range('Sheet2', 'cell_sheet2').value
assert_equal(cells, value)
Range('Sheet2', 'range_sheet2').value = data
cells = Range('Sheet2', 'range_sheet2').value
assert_equal(cells, data)
# Sheetindex
Range(3, 'cell_sheet3').value = value
cells = Range(3, 'cell_sheet3').value
assert_equal(cells, value)
Range(3, 'range_sheet3').value = data
cells = Range(3, 'range_sheet3').value
assert_equal(cells, data)
def test_array(self):
_skip_if_no_numpy()
# 1d array
Range('Sheet6', 'A1').value = array_1d
cells = Range('Sheet6', 'A1:D1', asarray=True).value
assert_array_equal(cells, array_1d)
# 2d array
Range('Sheet6', 'A4').value = array_2d
cells = Range('Sheet6', 'A4', asarray=True).table.value
assert_array_equal(cells, array_2d)
# 1d array (atleast_2d)
Range('Sheet6', 'A10').value = array_1d
cells = Range('Sheet6', 'A10:D10', asarray=True, atleast_2d=True).value
assert_array_equal(cells, np.atleast_2d(array_1d))
# 2d array (atleast_2d)
Range('Sheet6', 'A12').value = array_2d
cells = Range('Sheet6', 'A12', asarray=True, atleast_2d=True).table.value
assert_array_equal(cells, array_2d)
def sheet_ref(self):
Range(Sheet(1), 'A20').value = 123
assert_equal(Range(1, 'A20').value, 123)
Range(Sheet(1), (2,2), (4,4)).value = 321
assert_equal(Range(1, (2,2)).value, 321)
def test_vertical(self):
Range('Sheet4', 'A10').value = data
if sys.platform.startswith('win') and self.wb.xl_app.Version == '14.0':
Range('Sheet4', 'A12:B12').xl_range.NumberFormat = 'dd/mm/yyyy' # Hack for Excel 2010 bug, see GH #43
cells = Range('Sheet4', 'A10').vertical.value
assert_equal(cells, [row[0] for row in data])
def test_horizontal(self):
Range('Sheet4', 'A20').value = data
cells = Range('Sheet4', 'A20').horizontal.value
assert_equal(cells, data[0])
def test_table(self):
Range('Sheet4', 'A1').value = data
if sys.platform.startswith('win') and self.wb.xl_app.Version == '14.0':
Range('Sheet4', 'A3:B3').xl_range.NumberFormat = 'dd/mm/yyyy' # Hack for Excel 2010 bug, see GH #43
cells = Range('Sheet4', 'A1').table.value
assert_equal(cells, data)
def test_list(self):
# 1d List Row
Range('Sheet4', 'A27').value = list_row_1d
cells = Range('Sheet4', 'A27:C27').value
assert_equal(list_row_1d, cells)
# 2d List Row
Range('Sheet4', 'A29').value = list_row_2d
cells = Range('Sheet4', 'A29:C29', atleast_2d=True).value
assert_equal(list_row_2d, cells)
# 1d List Col
Range('Sheet4', 'A31').value = list_col
cells = Range('Sheet4', 'A31:A33').value
assert_equal([i[0] for i in list_col], cells)
# 2d List Col
cells = Range('Sheet4', 'A31:A33', atleast_2d=True).value
assert_equal(list_col, cells)
def test_is_cell(self):
assert_equal(Range('A1').is_cell(), True)
assert_equal(Range('A1:B1').is_cell(), False)
assert_equal(Range('A1:A2').is_cell(), False)
assert_equal(Range('A1:B2').is_cell(), False)
def test_is_row(self):
assert_equal(Range('A1').is_row(), False)
assert_equal(Range('A1:B1').is_row(), True)
assert_equal(Range('A1:A2').is_row(), False)
assert_equal(Range('A1:B2').is_row(), False)
def test_is_column(self):
assert_equal(Range('A1').is_column(), False)
assert_equal(Range('A1:B1').is_column(), False)
assert_equal(Range('A1:A2').is_column(), True)
assert_equal(Range('A1:B2').is_column(), False)
def test_is_table(self):
assert_equal(Range('A1').is_table(), False)
assert_equal(Range('A1:B1').is_table(), False)
assert_equal(Range('A1:A2').is_table(), False)
assert_equal(Range('A1:B2').is_table(), True)
def test_formula(self):
Range('A1').formula = '=SUM(A2:A10)'
assert_equal(Range('A1').formula, '=SUM(A2:A10)')
def test_current_region(self):
values = [[1.,2.],[3.,4.]]
Range('A20').value = values
assert_equal(Range('B21').current_region.value, values)
def test_clear_content(self):
Range('Sheet4', 'G1').value = 22
Range('Sheet4', 'G1').clear_contents()
cell = Range('Sheet4', 'G1').value
assert_equal(cell, None)
def test_clear(self):
Range('Sheet4', 'G1').value = 22
Range('Sheet4', 'G1').clear()
cell = Range('Sheet4', 'G1').value
assert_equal(cell, None)
def test_dataframe_1(self):
_skip_if_no_pandas()
df_expected = df_1
Range('Sheet5', 'A1').value = df_expected
cells = Range('Sheet5', 'B1:C5').value
df_result = DataFrame(cells[1:], columns=cells[0])
assert_frame_equal(df_expected, df_result)
def test_dataframe_2(self):
""" Covers GH Issue #31"""
_skip_if_no_pandas()
df_expected = df_2
Range('Sheet5', 'A9').value = df_expected
cells = Range('Sheet5', 'B9:B15').value
df_result = DataFrame(cells[1:], columns=[cells[0]])
assert_frame_equal(df_expected, df_result)
def test_dataframe_multiindex(self):
_skip_if_no_pandas()
df_expected = df_multiindex
Range('Sheet5', 'A20').value = df_expected
cells = Range('Sheet5', 'D20').table.value
multiindex = Range('Sheet5', 'A20:C28').value
ix = pd.MultiIndex.from_tuples(multiindex[1:], names=multiindex[0])
df_result = DataFrame(cells[1:], columns=cells[0], index=ix)
assert_frame_equal(df_expected, df_result)
def test_dataframe_multiheader(self):
_skip_if_no_pandas()
df_expected = df_multiheader
Range('Sheet5', 'A52').value = df_expected
cells = Range('Sheet5', 'B52').table.value
df_result = DataFrame(cells[2:], columns=pd.MultiIndex.from_arrays(cells[:2]))
assert_frame_equal(df_expected, df_result)
def test_dataframe_dateindex(self):
_skip_if_no_pandas()
df_expected = df_dateindex
Range('Sheet5', 'A100').value = df_expected
if sys.platform.startswith('win') and self.wb.xl_app.Version == '14.0':
Range('Sheet5', 'A100').vertical.xl_range.NumberFormat = 'dd/mm/yyyy' # Hack for Excel 2010 bug, see GH #43
cells = Range('Sheet5', 'B100').table.value
index = Range('Sheet5', 'A101').vertical.value
df_result = DataFrame(cells[1:], index=index, columns=cells[0])
assert_frame_equal(df_expected, df_result)
def test_series_1(self):
_skip_if_no_pandas()
series_expected = series_1
Range('Sheet5', 'A32').value = series_expected
cells = Range('Sheet5', 'B32:B37').value
series_result = Series(cells)
assert_series_equal(series_expected, series_result)
def test_timeseries_1(self):
_skip_if_no_pandas()
series_expected = timeseries_1
Range('Sheet5', 'A40').value = series_expected
if sys.platform.startswith('win') and self.wb.xl_app.Version == '14.0':
Range('Sheet5', 'A40').vertical.xl_range.NumberFormat = 'dd/mm/yyyy' # Hack for Excel 2010 bug, see GH #43
cells = Range('Sheet5', 'B40:B49').value
date_index = Range('Sheet5', 'A40:A49').value
series_result = Series(cells, index=date_index)
assert_series_equal(series_expected, series_result)
def test_none(self):
""" Covers GH Issue #16"""
# None
Range('Sheet1', 'A7').value = None
assert_equal(None, Range('Sheet1', 'A7').value)
# List
Range('Sheet1', 'A7').value = [None, None]
assert_equal(None, Range('Sheet1', 'A7').horizontal.value)
def test_scalar_nan(self):
"""Covers GH Issue #15"""
_skip_if_no_numpy()
Range('Sheet1', 'A20').value = np.nan
assert_equal(None, Range('Sheet1', 'A20').value)
def test_atleast_2d_scalar(self):
"""Covers GH Issue #53a"""
Range('Sheet1', 'A50').value = 23
result = Range('Sheet1', 'A50', atleast_2d=True).value
assert_equal([[23]], result)
def test_atleast_2d_scalar_as_array(self):
"""Covers GH Issue #53b"""
_skip_if_no_numpy()
Range('Sheet1', 'A50').value = 23
result = Range('Sheet1', 'A50', atleast_2d=True, asarray=True).value
assert_equal(np.array([[23]]), result)
def test_autofit_range(self):
# TODO: compare col/row widths before/after - not implemented yet
Range('Sheet1', 'A1:D4').value = 'test_string'
Range('Sheet1', 'A1:D4').autofit()
Range('Sheet1', 'A1:D4').autofit('r')
Range('Sheet1', 'A1:D4').autofit('c')
Range('Sheet1', 'A1:D4').autofit('rows')
Range('Sheet1', 'A1:D4').autofit('columns')
def test_autofit_col(self):
# TODO: compare col/row widths before/after - not implemented yet
Range('Sheet1', 'A1:D4').value = 'test_string'
Range('Sheet1', 'A:D').autofit()
Range('Sheet1', 'A:D').autofit('r')
Range('Sheet1', 'A:D').autofit('c')
Range('Sheet1', 'A:D').autofit('rows')
Range('Sheet1', 'A:D').autofit('columns')
def test_autofit_row(self):
# TODO: compare col/row widths before/after - not implemented yet
Range('Sheet1', 'A1:D4').value = 'test_string'
Range('Sheet1', '1:1000000').autofit()
Range('Sheet1', '1:1000000').autofit('r')
Range('Sheet1', '1:1000000').autofit('c')
Range('Sheet1', '1:1000000').autofit('rows')
Range('Sheet1', '1:1000000').autofit('columns')
def test_number_format_cell(self):
format_string = "mm/dd/yy;@"
Range('Sheet1', 'A1').number_format = format_string
result = Range('Sheet1', 'A1').number_format
assert_equal(format_string, result)
def test_number_format_range(self):
format_string = "mm/dd/yy;@"
Range('Sheet1', 'A1:D4').number_format = format_string
result = Range('Sheet1', 'A1:D4').number_format
assert_equal(format_string, result)
def test_get_address(self):
res = Range((1,1),(3,3)).get_address()
assert_equal(res, '$A$1:$C$3')
res = Range((1,1),(3,3)).get_address(False)
assert_equal(res, '$A1:$C3')
res = Range((1,1),(3,3)).get_address(True, False)
assert_equal(res, 'A$1:C$3')
res = Range((1,1),(3,3)).get_address(False, False)
assert_equal(res, 'A1:C3')
res = Range((1,1),(3,3)).get_address(include_sheetname=True)
assert_equal(res, 'Sheet1!$A$1:$C$3')
res = Range('Sheet2', (1,1),(3,3)).get_address(include_sheetname=True)
assert_equal(res, 'Sheet2!$A$1:$C$3')
res = Range((1,1),(3,3)).get_address(external=True)
assert_equal(res, '[test_range_1.xlsx]Sheet1!$A$1:$C$3')
def test_hyperlink(self):
address = 'www.xlwings.org'
# Naked address
Range('A1').add_hyperlink(address)
assert_equal(Range('A1').value, address)
hyperlink = Range('A1').hyperlink
if not hyperlink.endswith('/'):
hyperlink += '/'
assert_equal(hyperlink, 'http://' + address + '/')
# Address + FriendlyName
Range('A2').add_hyperlink(address, 'test_link')
assert_equal(Range('A2').value, 'test_link')
hyperlink = Range('A2').hyperlink
if not hyperlink.endswith('/'):
hyperlink += '/'
assert_equal(hyperlink, 'http://' + address + '/')
def test_hyperlink_formula(self):
Range('B10').formula = '=HYPERLINK("http://xlwings.org", "xlwings")'
assert_equal(Range('B10').hyperlink, 'http://xlwings.org')
def test_color(self):
rgb = (30, 100, 200)
Range('A1').color = rgb
assert_equal(rgb, Range('A1').color)
Range('A2').color = RgbColor.rgbAqua
assert_equal((0, 255, 255), Range('A2').color)
Range('A2').color = None
assert_equal(Range('A2').color, None)
Range('A1:D4').color = rgb
assert_equal(rgb, Range('A1:D4').color)
def test_size(self):
assert_equal(Range('A1:C4').size, 12)
def test_shape(self):
assert_equal(Range('A1:C4').shape, (4, 3))
def test_len(self):
assert_equal(len(Range('A1:C4')), 4)
def test_iterator(self):
Range('A20').value = [[1., 2.], [3., 4.]]
l = []
for i in Range('A20:B21'):
l.append(i.value)
assert_equal(l, [1., 2., 3., 4.])
Range('Sheet2', 'A20').value = [[1., 2.], [3., 4.]]
l = []
for i in Range('Sheet2', 'A20:B21'):
l.append(i.value)
assert_equal(l, [1., 2., 3., 4.])
def test_resize(self):
r = Range('A1').resize(4, 5)
assert_equal(r.shape, (4, 5))
r = Range('A1').resize(row_size=4)
assert_equal(r.shape, (4, 1))
r = Range('A1:B4').resize(column_size=5)
assert_equal(r.shape, (1, 5))
def test_offset(self):
o = Range('A1:B3').offset(3, 4)
assert_equal(o.get_address(), '$E$4:$F$6')
o = Range('A1:B3').offset(row_offset=3)
assert_equal(o.get_address(), '$A$4:$B$6')
o = Range('A1:B3').offset(column_offset=4)
assert_equal(o.get_address(), '$E$1:$F$3')
def test_date(self):
date_1 = date(2000, 12, 3)
Range('X1').value = date_1
date_2 = Range('X1').value
assert_equal(date_1, date(date_2.year, date_2.month, date_2.day))
def test_row(self):
assert_equal(Range('B3:F5').row, 3)
def test_column(self):
assert_equal(Range('B3:F5').column, 2)
def test_last_cell(self):
assert_equal(Range('B3:F5').last_cell.row, 5)
assert_equal(Range('B3:F5').last_cell.column, 6)
def test_get_set_named_range(self):
wb = Workbook()
Range('A1').name = 'test1'
assert_equal(Range('A1').name, 'test1')
Range('A2:B4').name = 'test2'
assert_equal(Range('A2:B4').name, 'test2')
wb.close()
class TestWorkbook:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1, app_visible=False, app_target=APP_TARGET)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_name(self):
assert_equal(self.wb.name, 'test_workbook_1.xlsx')
def test_active_sheet(self):
assert_equal(self.wb.active_sheet.name, 'Sheet1')
def test_current(self):
assert_equal(self.wb.xl_workbook, Workbook.current().xl_workbook)
def test_set_current(self):
wb2 = Workbook(app_visible=False, app_target=APP_TARGET)
assert_equal(Workbook.current().xl_workbook, wb2.xl_workbook)
self.wb.set_current()
assert_equal(Workbook.current().xl_workbook, self.wb.xl_workbook)
wb2.close()
def test_get_selection(self):
Range('A1').value = 1000
assert_equal(self.wb.get_selection().value, 1000)
def test_reference_two_unsaved_wb(self):
"""Covers GH Issue #63"""
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
wb2 = Workbook(app_visible=False, app_target=APP_TARGET)
Range('A1').value = 2. # wb2
Range('A1', wkb=wb1).value = 1. # wb1
assert_equal(Range('A1').value, 2.)
assert_equal(Range('A1', wkb=wb1).value, 1.)
wb1.close()
wb2.close()
def test_save_naked(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, wb1.name + '.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save()
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path,
app_visible=False,
app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_save_path(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, 'TestFile.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save(target_file_path)
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path,
app_visible=False,
app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_mock_caller(self):
_skip_if_not_default_xl()
Workbook.set_mock_caller(
os.path.join(os.path.dirname(os.path.abspath(__file__)),
'test_workbook_1.xlsx'))
wb = Workbook.caller()
Range('A1', wkb=wb).value = 333
assert_equal(Range('A1', wkb=wb).value, 333)
def test_unicode_path(self):
# pip3 seems to struggle with unicode filenames
src = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'unicode_path.xlsx')
dst = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'ünicödé_päth.xlsx')
shutil.move(src, dst)
wb = Workbook(os.path.join(os.path.dirname(os.path.abspath(__file__)),
'ünicödé_päth.xlsx'),
app_target=APP_TARGET)
Range('A1').value = 1
wb.close()
shutil.move(dst, src)
def unsaved_workbook_reference(self):
wb = Workbook(app_visible=False, app_target=APP_TARGET)
Range('B2').value = 123
wb2 = Workbook(wb.name, app_visible=False, app_target=APP_TARGET)
assert_equal(Range('B2', wkb=wb2).value, 123)
def test_set_current(self):
wb2 = Workbook()
assert_equal(Workbook.current().xl_workbook, wb2.xl_workbook)
self.wb.set_current()
assert_equal(Workbook.current().xl_workbook, self.wb.xl_workbook)
wb2.close()
def test_unsaved_workbook_reference(self):
wb = Workbook(app_visible=False, app_target=APP_TARGET)
Range('B2').value = 123
wb2 = Workbook(wb.name, app_visible=False, app_target=APP_TARGET)
assert_equal(Range('B2', wkb=wb2).value, 123)
wb2.close()
class TestWorkbook:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1, app_visible=False, app_target=APP_TARGET)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_name(self):
assert_equal(self.wb.name, 'test_workbook_1.xlsx')
def test_active_sheet(self):
assert_equal(self.wb.active_sheet.name, 'Sheet1')
def test_current(self):
assert_equal(self.wb.xl_workbook, Workbook.current().xl_workbook)
def test_set_current(self):
wb2 = Workbook(app_visible=False, app_target=APP_TARGET)
assert_equal(Workbook.current().xl_workbook, wb2.xl_workbook)
self.wb.set_current()
assert_equal(Workbook.current().xl_workbook, self.wb.xl_workbook)
wb2.close()
def test_get_selection(self):
Range('A1').value = 1000
assert_equal(self.wb.get_selection().value, 1000)
def test_reference_two_unsaved_wb(self):
"""Covers GH Issue #63"""
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
wb2 = Workbook(app_visible=False, app_target=APP_TARGET)
Range('A1').value = 2. # wb2
Range('A1', wkb=wb1).value = 1. # wb1
assert_equal(Range('A1').value, 2.)
assert_equal(Range('A1', wkb=wb1).value, 1.)
wb1.close()
wb2.close()
def test_save_naked(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, wb1.name + '.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save()
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False, app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_save_path(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, 'TestFile.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save(target_file_path)
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False, app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_mock_caller(self):
_skip_if_not_default_xl()
Workbook.set_mock_caller(os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx'))
wb = Workbook.caller()
Range('A1', wkb=wb).value = 333
assert_equal(Range('A1', wkb=wb).value, 333)
def test_unicode_path(self):
# pip3 seems to struggle with unicode filenames
src = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'unicode_path.xlsx')
dst = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'ünicödé_päth.xlsx')
shutil.move(src, dst)
wb = Workbook(os.path.join(os.path.dirname(os.path.abspath(__file__)), 'ünicödé_päth.xlsx'), app_target=APP_TARGET)
Range('A1').value = 1
wb.close()
shutil.move(dst, src)
def unsaved_workbook_reference(self):
wb = Workbook(app_visible=False, app_target=APP_TARGET)
Range('B2').value = 123
wb2 = Workbook(wb.name, app_visible=False, app_target=APP_TARGET)
assert_equal(Range('B2', wkb=wb2).value, 123)
class TestRange:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'test_range_1.xlsx')
self.wb = Workbook(xl_file1, app_visible=False, app_target=APP_TARGET)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_cell(self):
params = [('A1', 22), ((1, 1), 22), ('A1', 22.2222), ((1, 1), 22.2222),
('A1', 'Test String'), ((1, 1), 'Test String'),
('A1', 'éöà'), ((1, 1), 'éöà'), ('A2', test_date_1),
((2, 1), test_date_1), ('A3', test_date_2),
((3, 1), test_date_2)]
for param in params:
yield self.check_cell, param[0], param[1]
def check_cell(self, address, value):
# Active Sheet
Range(address).value = value
cell = Range(address).value
assert_equal(cell, value)
# SheetName
Range('Sheet2', address).value = value
cell = Range('Sheet2', address).value
assert_equal(cell, value)
# SheetIndex
Range(3, address).value = value
cell = Range(3, address).value
assert_equal(cell, value)
def test_range_address(self):
""" Style: Range('A1:C3') """
address = 'C1:E3'
# Active Sheet
Range(address[:2]).value = data # assign to starting cell only
cells = Range(address).value
assert_equal(cells, data)
# Sheetname
Range('Sheet2', address).value = data
cells = Range('Sheet2', address).value
assert_equal(cells, data)
# Sheetindex
Range(3, address).value = data
cells = Range(3, address).value
assert_equal(cells, data)
def test_range_index(self):
""" Style: Range((1,1), (3,3)) """
index1 = (1, 3)
index2 = (3, 5)
# Active Sheet
Range(index1, index2).value = data
cells = Range(index1, index2).value
assert_equal(cells, data)
# Sheetname
Range('Sheet2', index1, index2).value = data
cells = Range('Sheet2', index1, index2).value
assert_equal(cells, data)
# Sheetindex
Range(3, index1, index2).value = data
cells = Range(3, index1, index2).value
assert_equal(cells, data)
def test_named_range(self):
value = 22.222
# Active Sheet
Range('cell_sheet1').value = value
cells = Range('cell_sheet1').value
assert_equal(cells, value)
Range('range_sheet1').value = data
cells = Range('range_sheet1').value
assert_equal(cells, data)
# Sheetname
Range('Sheet2', 'cell_sheet2').value = value
cells = Range('Sheet2', 'cell_sheet2').value
assert_equal(cells, value)
Range('Sheet2', 'range_sheet2').value = data
cells = Range('Sheet2', 'range_sheet2').value
assert_equal(cells, data)
# Sheetindex
Range(3, 'cell_sheet3').value = value
cells = Range(3, 'cell_sheet3').value
assert_equal(cells, value)
Range(3, 'range_sheet3').value = data
cells = Range(3, 'range_sheet3').value
assert_equal(cells, data)
def test_array(self):
_skip_if_no_numpy()
# 1d array
Range('Sheet6', 'A1').value = array_1d
cells = Range('Sheet6', 'A1:D1', asarray=True).value
assert_array_equal(cells, array_1d)
# 2d array
Range('Sheet6', 'A4').value = array_2d
cells = Range('Sheet6', 'A4', asarray=True).table.value
assert_array_equal(cells, array_2d)
# 1d array (atleast_2d)
Range('Sheet6', 'A10').value = array_1d
cells = Range('Sheet6', 'A10:D10', asarray=True, atleast_2d=True).value
assert_array_equal(cells, np.atleast_2d(array_1d))
# 2d array (atleast_2d)
Range('Sheet6', 'A12').value = array_2d
cells = Range('Sheet6', 'A12', asarray=True,
atleast_2d=True).table.value
assert_array_equal(cells, array_2d)
def test_vertical(self):
Range('Sheet4', 'A10').value = data
if sys.platform.startswith('win') and self.wb.xl_app.Version == '14.0':
Range(
'Sheet4', 'A12:B12'
).xl_range.NumberFormat = 'dd/mm/yyyy' # Hack for Excel 2010 bug, see GH #43
cells = Range('Sheet4', 'A10').vertical.value
assert_equal(cells, [row[0] for row in data])
def test_horizontal(self):
Range('Sheet4', 'A20').value = data
cells = Range('Sheet4', 'A20').horizontal.value
assert_equal(cells, data[0])
def test_table(self):
Range('Sheet4', 'A1').value = data
if sys.platform.startswith('win') and self.wb.xl_app.Version == '14.0':
Range(
'Sheet4', 'A3:B3'
).xl_range.NumberFormat = 'dd/mm/yyyy' # Hack for Excel 2010 bug, see GH #43
cells = Range('Sheet4', 'A1').table.value
assert_equal(cells, data)
def test_list(self):
# 1d List Row
Range('Sheet4', 'A27').value = list_row_1d
cells = Range('Sheet4', 'A27:C27').value
assert_equal(list_row_1d, cells)
# 2d List Row
Range('Sheet4', 'A29').value = list_row_2d
cells = Range('Sheet4', 'A29:C29', atleast_2d=True).value
assert_equal(list_row_2d, cells)
# 1d List Col
Range('Sheet4', 'A31').value = list_col
cells = Range('Sheet4', 'A31:A33').value
assert_equal([i[0] for i in list_col], cells)
# 2d List Col
cells = Range('Sheet4', 'A31:A33', atleast_2d=True).value
assert_equal(list_col, cells)
def test_is_cell(self):
assert_equal(Range('A1').is_cell(), True)
assert_equal(Range('A1:B1').is_cell(), False)
assert_equal(Range('A1:A2').is_cell(), False)
assert_equal(Range('A1:B2').is_cell(), False)
def test_is_row(self):
assert_equal(Range('A1').is_row(), False)
assert_equal(Range('A1:B1').is_row(), True)
assert_equal(Range('A1:A2').is_row(), False)
assert_equal(Range('A1:B2').is_row(), False)
def test_is_column(self):
assert_equal(Range('A1').is_column(), False)
assert_equal(Range('A1:B1').is_column(), False)
assert_equal(Range('A1:A2').is_column(), True)
assert_equal(Range('A1:B2').is_column(), False)
def test_is_table(self):
assert_equal(Range('A1').is_table(), False)
assert_equal(Range('A1:B1').is_table(), False)
assert_equal(Range('A1:A2').is_table(), False)
assert_equal(Range('A1:B2').is_table(), True)
def test_formula(self):
Range('A1').formula = '=SUM(A2:A10)'
assert_equal(Range('A1').formula, '=SUM(A2:A10)')
def test_current_region(self):
values = [[1., 2.], [3., 4.]]
Range('A20').value = values
assert_equal(Range('B21').current_region.value, values)
def test_clear_content(self):
Range('Sheet4', 'G1').value = 22
Range('Sheet4', 'G1').clear_contents()
cell = Range('Sheet4', 'G1').value
assert_equal(cell, None)
def test_clear(self):
Range('Sheet4', 'G1').value = 22
Range('Sheet4', 'G1').clear()
cell = Range('Sheet4', 'G1').value
assert_equal(cell, None)
def test_dataframe_1(self):
_skip_if_no_pandas()
df_expected = df_1
Range('Sheet5', 'A1').value = df_expected
cells = Range('Sheet5', 'B1:C5').value
df_result = DataFrame(cells[1:], columns=cells[0])
assert_frame_equal(df_expected, df_result)
def test_dataframe_2(self):
""" Covers GH Issue #31"""
_skip_if_no_pandas()
df_expected = df_2
Range('Sheet5', 'A9').value = df_expected
cells = Range('Sheet5', 'B9:B15').value
df_result = DataFrame(cells[1:], columns=[cells[0]])
assert_frame_equal(df_expected, df_result)
def test_dataframe_multiindex(self):
_skip_if_no_pandas()
df_expected = df_multiindex
Range('Sheet5', 'A20').value = df_expected
cells = Range('Sheet5', 'D20').table.value
multiindex = Range('Sheet5', 'A20:C28').value
ix = pd.MultiIndex.from_tuples(multiindex[1:], names=multiindex[0])
df_result = DataFrame(cells[1:], columns=cells[0], index=ix)
assert_frame_equal(df_expected, df_result)
def test_dataframe_multiheader(self):
_skip_if_no_pandas()
df_expected = df_multiheader
Range('Sheet5', 'A52').value = df_expected
cells = Range('Sheet5', 'B52').table.value
df_result = DataFrame(cells[2:],
columns=pd.MultiIndex.from_arrays(cells[:2]))
assert_frame_equal(df_expected, df_result)
def test_dataframe_dateindex(self):
_skip_if_no_pandas()
df_expected = df_dateindex
Range('Sheet5', 'A100').value = df_expected
if sys.platform.startswith('win') and self.wb.xl_app.Version == '14.0':
Range(
'Sheet5', 'A100'
).vertical.xl_range.NumberFormat = 'dd/mm/yyyy' # Hack for Excel 2010 bug, see GH #43
cells = Range('Sheet5', 'B100').table.value
index = Range('Sheet5', 'A101').vertical.value
df_result = DataFrame(cells[1:], index=index, columns=cells[0])
assert_frame_equal(df_expected, df_result)
def test_series_1(self):
_skip_if_no_pandas()
series_expected = series_1
Range('Sheet5', 'A32').value = series_expected
cells = Range('Sheet5', 'B32:B37').value
series_result = Series(cells)
assert_series_equal(series_expected, series_result)
def test_timeseries_1(self):
_skip_if_no_pandas()
series_expected = timeseries_1
Range('Sheet5', 'A40').value = series_expected
if sys.platform.startswith('win') and self.wb.xl_app.Version == '14.0':
Range(
'Sheet5', 'A40'
).vertical.xl_range.NumberFormat = 'dd/mm/yyyy' # Hack for Excel 2010 bug, see GH #43
cells = Range('Sheet5', 'B40:B49').value
date_index = Range('Sheet5', 'A40:A49').value
series_result = Series(cells, index=date_index)
assert_series_equal(series_expected, series_result)
def test_none(self):
""" Covers GH Issue #16"""
# None
Range('Sheet1', 'A7').value = None
assert_equal(None, Range('Sheet1', 'A7').value)
# List
Range('Sheet1', 'A7').value = [None, None]
assert_equal(None, Range('Sheet1', 'A7').horizontal.value)
def test_scalar_nan(self):
"""Covers GH Issue #15"""
_skip_if_no_numpy()
Range('Sheet1', 'A20').value = np.nan
assert_equal(None, Range('Sheet1', 'A20').value)
def test_atleast_2d_scalar(self):
"""Covers GH Issue #53a"""
Range('Sheet1', 'A50').value = 23
result = Range('Sheet1', 'A50', atleast_2d=True).value
assert_equal([[23]], result)
def test_atleast_2d_scalar_as_array(self):
"""Covers GH Issue #53b"""
_skip_if_no_numpy()
Range('Sheet1', 'A50').value = 23
result = Range('Sheet1', 'A50', atleast_2d=True, asarray=True).value
assert_equal(np.array([[23]]), result)
def test_autofit_range(self):
# TODO: compare col/row widths before/after - not implemented yet
Range('Sheet1', 'A1:D4').value = 'test_string'
Range('Sheet1', 'A1:D4').autofit()
Range('Sheet1', 'A1:D4').autofit('r')
Range('Sheet1', 'A1:D4').autofit('c')
Range('Sheet1', 'A1:D4').autofit('rows')
Range('Sheet1', 'A1:D4').autofit('columns')
def test_autofit_col(self):
# TODO: compare col/row widths before/after - not implemented yet
Range('Sheet1', 'A1:D4').value = 'test_string'
Range('Sheet1', 'A:D').autofit()
Range('Sheet1', 'A:D').autofit('r')
Range('Sheet1', 'A:D').autofit('c')
Range('Sheet1', 'A:D').autofit('rows')
Range('Sheet1', 'A:D').autofit('columns')
def test_autofit_row(self):
# TODO: compare col/row widths before/after - not implemented yet
Range('Sheet1', 'A1:D4').value = 'test_string'
Range('Sheet1', '1:1000000').autofit()
Range('Sheet1', '1:1000000').autofit('r')
Range('Sheet1', '1:1000000').autofit('c')
Range('Sheet1', '1:1000000').autofit('rows')
Range('Sheet1', '1:1000000').autofit('columns')
def test_number_format_cell(self):
format_string = "mm/dd/yy;@"
Range('Sheet1', 'A1').number_format = format_string
result = Range('Sheet1', 'A1').number_format
assert_equal(format_string, result)
def test_number_format_range(self):
format_string = "mm/dd/yy;@"
Range('Sheet1', 'A1:D4').number_format = format_string
result = Range('Sheet1', 'A1:D4').number_format
assert_equal(format_string, result)
def test_get_address(self):
res = Range((1, 1), (3, 3)).get_address()
assert_equal(res, '$A$1:$C$3')
res = Range((1, 1), (3, 3)).get_address(False)
assert_equal(res, '$A1:$C3')
res = Range((1, 1), (3, 3)).get_address(True, False)
assert_equal(res, 'A$1:C$3')
res = Range((1, 1), (3, 3)).get_address(False, False)
assert_equal(res, 'A1:C3')
res = Range((1, 1), (3, 3)).get_address(include_sheetname=True)
assert_equal(res, 'Sheet1!$A$1:$C$3')
res = Range('Sheet2', (1, 1),
(3, 3)).get_address(include_sheetname=True)
assert_equal(res, 'Sheet2!$A$1:$C$3')
res = Range((1, 1), (3, 3)).get_address(external=True)
assert_equal(res, '[test_range_1.xlsx]Sheet1!$A$1:$C$3')
def test_hyperlink(self):
address = 'www.xlwings.org'
# Naked address
Range('A1').add_hyperlink(address)
assert_equal(Range('A1').value, address)
hyperlink = Range('A1').hyperlink
if not hyperlink.endswith('/'):
hyperlink += '/'
assert_equal(hyperlink, 'http://' + address + '/')
# Address + FriendlyName
Range('A2').add_hyperlink(address, 'test_link')
assert_equal(Range('A2').value, 'test_link')
hyperlink = Range('A2').hyperlink
if not hyperlink.endswith('/'):
hyperlink += '/'
assert_equal(hyperlink, 'http://' + address + '/')
def test_hyperlink_formula(self):
Range('B10').formula = '=HYPERLINK("http://xlwings.org", "xlwings")'
assert_equal(Range('B10').hyperlink, 'http://xlwings.org')
def test_color(self):
rgb = (30, 100, 200)
Range('A1').color = rgb
assert_equal(rgb, Range('A1').color)
Range('A2').color = RgbColor.rgbAqua
assert_equal((0, 255, 255), Range('A2').color)
Range('A2').color = None
assert_equal(Range('A2').color, None)
Range('A1:D4').color = rgb
assert_equal(rgb, Range('A1:D4').color)
def test_size(self):
assert_equal(Range('A1:C4').size, 12)
def test_shape(self):
assert_equal(Range('A1:C4').shape, (4, 3))
def test_len(self):
assert_equal(len(Range('A1:C4')), 4)
def test_iterator(self):
Range('A20').value = [[1., 2.], [3., 4.]]
l = []
for i in Range('A20:B21'):
l.append(i.value)
assert_equal(l, [1., 2., 3., 4.])
Range('Sheet2', 'A20').value = [[1., 2.], [3., 4.]]
l = []
for i in Range('Sheet2', 'A20:B21'):
l.append(i.value)
assert_equal(l, [1., 2., 3., 4.])
def test_resize(self):
r = Range('A1').resize(4, 5)
assert_equal(r.shape, (4, 5))
r = Range('A1').resize(row_size=4)
assert_equal(r.shape, (4, 1))
r = Range('A1:B4').resize(column_size=5)
assert_equal(r.shape, (1, 5))
def test_offset(self):
o = Range('A1:B3').offset(3, 4)
assert_equal(o.get_address(), '$E$4:$F$6')
o = Range('A1:B3').offset(row_offset=3)
assert_equal(o.get_address(), '$A$4:$B$6')
o = Range('A1:B3').offset(column_offset=4)
assert_equal(o.get_address(), '$E$1:$F$3')
def test_date(self):
date_1 = date(2000, 12, 3)
Range('X1').value = date_1
date_2 = Range('X1').value
assert_equal(date_1, date(date_2.year, date_2.month, date_2.day))
def test_row(self):
assert_equal(Range('B3:F5').row, 3)
def test_column(self):
assert_equal(Range('B3:F5').column, 2)
def test_last_cell(self):
assert_equal(Range('B3:F5').last_cell.row, 5)
assert_equal(Range('B3:F5').last_cell.column, 6)
class ExcelTemplateRenderer(object):
"""
A class to render an Excel template using data stored
in a database and given a specification file.
Parameters
----------
db_engine : object
the :class:`sqlalchemy.engine.Engine` object used to
connect and query the database.
template_name : str
path to the `xlsx` template file.
spec_filename : str
path to the specification file (yaml format).
output_dirname : str
path to the directory where to store the
generated excel file(s) (i.e., the rendered template(s)).
jinja_env : object or None
allow to provide a :class:`jinja2.Environment` object,
helpful when using custom filters in the specification file
(optional).
Notes
-----
The specification file (yaml format) consists of a list
of render blocks. TODO: documentation on the format.
"""
def __init__(self,
db_engine,
template_name,
spec_filename,
output_dirname,
jinja_env=None):
self.db_engine = db_engine
self.template_name = template_name
self.spec_filename = spec_filename
self.output_dirname = os.path.abspath(output_dirname)
if jinja_env is None:
self.jinja_env = jinja2.Environment()
else:
self.jinja_env = jinja_env
with open(self.spec_filename, 'r', encoding='utf-8') as f:
self.render_blocks = yaml.load(f)
os.makedirs(self.output_dirname, exist_ok=True)
logger.info("output directory is %s", self.output_dirname)
def open_template_as_current_wkb(self):
self.wkb = Workbook(os.path.abspath(self.template_name),
app_visible=False)
self.wkb.set_current()
def save_current_wkb(self, filename):
filepath = os.path.join(self.output_dirname, filename)
self.wkb.save(filepath)
logger.info("created %s", filepath)
def save_current_wkb_as_pdf(self, filename, worksheet_name):
filepath = os.path.join(self.output_dirname, filename)
try:
ws = Sheet(worksheet_name, wkb=self.wkb)
ws.xl_sheet.ExportAsFixedFormat(0, filepath)
logger.info("created %s", filepath)
except Exception as e:
logger.error("failed to export pdf")
logger.error("detailled error: %s - %s", e.__class__.__name__,
str(e))
def close_current_wkb(self):
self.wkb.close()
def insert_one_series(self, series, cell_specification):
"""
Populate the current workbook given a single
:class=:`pandas.Series` object.
"""
if not len(series):
return
# contiguous cells
#TODO: (use vertical and horizontal properties of xlwings)
# non-contiguous user-defined cells
for cs in cell_specification.get('cells', []):
ws = cs.get('worksheet') or Sheet.active(self.wkb).name
content = self.jinja_env.from_string(
cs['content']).render(**series)
logger.debug("insert content '%s' at cell '%s' in sheet '%s'",
content, cs['cell'], ws)
Range(ws, cs['cell']).value = content
def insert_one_dataframe(self, df, cell_specification):
"""
Populate the current workbook given a single
:class=:`pandas.DataFrame` object.
"""
if not len(df):
return
index = cell_specification.get('index', False)
header = cell_specification.get('header', False)
top_left_cell = cell_specification.get('top_left_cell', 'A0')
logger.debug(
"insert %d by %d rows/cols dataframe "
"at cell '%s' in sheet '%s'", len(df), len(df.columns),
str(top_left_cell),
Sheet.active(self.wkb).name)
Range(top_left_cell, index=index, header=header).value = df
def apply_render_block(self, render_block, query_context=None, **kwargs):
"""
Apply a single render block in the specification file.
- `query_context` (mappable or None) is a context used when
rendering the database query with jinja2 (optional).
- **kwargs is used to overwrite any key/value pair
in the render block.
"""
# override render_block key/val with kwargs
render_block.update(kwargs)
logger.info("processing render block '%s'",
render_block.get('name', '<unamed>'))
# query the DB into a pandas DataFrame
if query_context is None:
query_context = dict()
query_template = self.jinja_env.from_string(
render_block['query'].strip())
query = query_template.render(**query_context)
logger.debug("rendered query: \n'''\n%s\n'''", query)
df = pd.read_sql(query, self.db_engine)
logger.debug("query returned %d record(s)", len(df))
# TODO: calculate extra columns and add it to the DataFrame
# activate worksheet if provided
ws_name = render_block['cell_specification'].get('worksheet') or None
if ws_name is not None:
ws2reactivate_name = Sheet.active(self.wkb).name
Sheet(ws_name, wkb=self.wkb).activate()
# apply the render_block, apply recusively included blocks,
# and save the rendered workbook(s) if needed
apply_by_row = render_block.get('apply_by_row', False)
save_as = render_block.get('save_as', None)
if apply_by_row and save_as is not None:
logger.info("%d file(s) to generate", len(df))
if apply_by_row:
for row, pseries in df.iterrows():
self.insert_one_series(pseries,
render_block['cell_specification'])
for item in render_block.get('include', []):
if isinstance(item, dict):
block_name = item.pop('render_block')
override_vars = item
else:
block_name = item
override_vars = {}
block = [
b for b in self.render_blocks
if b['name'] == block_name
][0]
self.apply_render_block(block,
query_context=pseries,
**override_vars)
if save_as is not None:
tpl = save_as['filename']
filename = self.jinja_env.from_string(tpl).render(
**pseries)
self.save_current_wkb(filename)
# save to pdf
if save_as.get('export_pdf', False):
filename_pdf = os.path.splitext(filename)[0] + '.pdf'
if ws_name is None:
logger.error(
"(export to pdf) no worksheet specified")
else:
self.save_current_wkb_as_pdf(filename_pdf, ws_name)
# re-open the template, re-activate the worksheet
self.close_current_wkb()
self.open_template_as_current_wkb()
if ws_name is not None:
Sheet(ws_name, wkb=self.wkb).activate()
else:
self.insert_one_dataframe(df, render_block['cell_specification'])
# TODO: include and save_as in this case
# re-activate former worksheet if needed
if ws_name is not None:
Sheet(ws2reactivate_name, wkb=self.wkb).activate()
def render(self):
"""Main render method."""
self.open_template_as_current_wkb()
save_render_blocks = [
block for block in self.render_blocks if 'save_as' in block.keys()
]
for block in save_render_blocks:
self.apply_render_block(block)
self.wkb.close()
year = str(yearCell[-4:])
# extract pricing across products
ORA_price_array = np.array(Range('pricing', 'D6:O12', atleast_2d=True).value)
POJ_price_array = np.array(Range('pricing', 'D15:O21', atleast_2d=True).value)
ROJ_price_array = np.array(Range('pricing', 'D24:O30', atleast_2d=True).value)
FCOJ_price_array = np.array(Range('pricing', 'D33:O39', atleast_2d=True).value)
# extract sales across products
ORA_sales_array = np.array(Range('ORA', 'D109:O115', atleast_2d=True).value)
POJ_sales_array = np.array(Range('POJ', 'D109:O115', atleast_2d=True).value)
ROJ_sales_array = np.array(Range('ROJ', 'D109:O115', atleast_2d=True).value)
FCOJ_sales_array = np.array(Range('FCOJ', 'D109:O115', atleast_2d=True).value)
# close excel workbook
wb.close()
# ORA
ORA_data = {'Sales': ORA_sales_array.ravel(),
'Price': ORA_price_array.ravel(),
'Region': [region for region in regions for j in xrange(0, len(months))],
'Product': ['ORA'] * len(regions) * len(months),
'Year': [str(year)] * len(regions) * len(months),
'Month': months * len(regions),
'Source': source * len(regions) * len(months)}
ORA_dataFrame = pd.DataFrame(ORA_data, columns=['Sales', 'Price', 'Region', 'Product','Year', 'Month', 'Source'])
# POJ
POJ_data = {'Sales': POJ_sales_array.ravel(),
'Price': POJ_price_array.ravel(),
class TestRange:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_range_1.xlsx')
self.wb = Workbook(xl_file1)
self.wb.activate('Sheet1')
def tearDown(self):
self.wb.close()
def test_cell(self):
params = [('A1', 22),
((1,1), 22),
('A1', 22.2222),
((1,1), 22.2222),
('A1', 'Test String'),
((1,1), 'Test String'),
('A1', 'éöà'),
((1,1), 'éöà'),
('A2', test_date_1),
((2,1), test_date_1),
('A3', test_date_2),
((3,1), test_date_2)]
for param in params:
yield self.check_cell, param[0], param[1]
def check_cell(self, address, value):
# Active Sheet
Range(address).value = value
cell = Range(address).value
assert_equal(cell, value)
# SheetName
Range('Sheet2', address).value = value
cell = Range('Sheet2', address).value
assert_equal(cell, value)
# SheetIndex
Range(3, address).value = value
cell = Range(3, address).value
assert_equal(cell, value)
def test_range_address(self):
""" Style: Range('A1:C3') """
address = 'C1:E3'
# Active Sheet
Range(address[:2]).value = data # assign to starting cell only
cells = Range(address).value
assert_equal(cells, data)
# Sheetname
Range('Sheet2', address).value = data
cells = Range('Sheet2', address).value
assert_equal(cells, data)
# Sheetindex
Range(3, address).value = data
cells = Range(3, address).value
assert_equal(cells, data)
def test_range_index(self):
""" Style: Range((1,1), (3,3)) """
index1 = (1,3)
index2 = (3,5)
# Active Sheet
Range(index1, index2).value = data
cells = Range(index1, index2).value
assert_equal(cells, data)
# Sheetname
Range('Sheet2', index1, index2).value = data
cells = Range('Sheet2', index1, index2).value
assert_equal(cells, data)
# Sheetindex
Range(3, index1, index2).value = data
cells = Range(3, index1, index2).value
assert_equal(cells, data)
def test_named_range(self):
value = 22.222
# Active Sheet
Range('cell_sheet1').value = value
cells = Range('cell_sheet1').value
assert_equal(cells, value)
Range('range_sheet1').value = data
cells = Range('range_sheet1').value
assert_equal(cells, data)
# Sheetname
Range('Sheet2', 'cell_sheet2').value = value
cells = Range('Sheet2', 'cell_sheet2').value
assert_equal(cells, value)
Range('Sheet2', 'range_sheet2').value = data
cells = Range('Sheet2', 'range_sheet2').value
assert_equal(cells, data)
# Sheetindex
Range(3, 'cell_sheet3').value = value
cells = Range(3, 'cell_sheet3').value
assert_equal(cells, value)
Range(3, 'range_sheet3').value = data
cells = Range(3, 'range_sheet3').value
assert_equal(cells, data)
def test_array(self):
_skip_if_no_numpy()
# 1d array
Range('Sheet6', 'A1').value = array_1d
cells = Range('Sheet6', 'A1:D1', asarray=True).value
assert_array_equal(cells, array_1d)
# 2d array
Range('Sheet6', 'A4').value = array_2d
cells = Range('Sheet6', 'A4', asarray=True).table.value
assert_array_equal(cells, array_2d)
# 1d array (atleast_2d)
Range('Sheet6', 'A10').value = array_1d
cells = Range('Sheet6', 'A10:D10', asarray=True, atleast_2d=True).value
assert_array_equal(cells, np.atleast_2d(array_1d))
# 2d array (atleast_2d)
Range('Sheet6', 'A12').value = array_2d
cells = Range('Sheet6', 'A12', asarray=True, atleast_2d=True).table.value
assert_array_equal(cells, array_2d)
def test_vertical(self):
Range('Sheet4', 'A10').value = data
cells = Range('Sheet4', 'A10').vertical.value
assert_equal(cells, [row[0] for row in data])
def test_horizontal(self):
Range('Sheet4', 'A20').value = data
cells = Range('Sheet4', 'A20').horizontal.value
assert_equal(cells, data[0])
def test_table(self):
Range('Sheet4', 'A1').value = data
cells = Range('Sheet4', 'A1').table.value
assert_equal(cells, data)
def test_list(self):
# 1d List Row
Range('Sheet4', 'A27').value = list_row_1d
cells = Range('Sheet4', 'A27:C27').value
assert_equal(list_row_1d, cells)
# 2d List Row
Range('Sheet4', 'A29').value = list_row_2d
cells = Range('Sheet4', 'A29:C29', atleast_2d=True).value
assert_equal(list_row_2d, cells)
# 1d List Col
Range('Sheet4', 'A31').value = list_col
cells = Range('Sheet4', 'A31:A33').value
assert_equal([i[0] for i in list_col], cells)
# 2d List Col
cells = Range('Sheet4', 'A31:A33', atleast_2d=True).value
assert_equal(list_col, cells)
def test_clear_content(self):
Range('Sheet4', 'G1').value = 22
Range('Sheet4', 'G1').clear_contents()
cell = Range('Sheet4', 'G1').value
assert_equal(cell, None)
def test_clear(self):
Range('Sheet4', 'G1').value = 22
Range('Sheet4', 'G1').clear()
cell = Range('Sheet4', 'G1').value
assert_equal(cell, None)
def test_dataframe_1(self):
_skip_if_no_pandas()
df_expected = df_1
Range('Sheet5', 'A1').value = df_expected
cells = Range('Sheet5', 'B1:C5').value
df_result = DataFrame(cells[1:], columns=cells[0])
assert_frame_equal(df_expected, df_result)
def test_dataframe_2(self):
""" Covers GH Issue #31"""
_skip_if_no_pandas()
df_expected = df_2
Range('Sheet5', 'A9').value = df_expected
cells = Range('Sheet5', 'B9:B15').value
df_result = DataFrame(cells[1:], columns=[cells[0]])
assert_frame_equal(df_expected, df_result)
def test_dataframe_multiindex(self):
_skip_if_no_pandas()
df_expected = df_multiindex
Range('Sheet5', 'A20').value = df_expected
cells = Range('Sheet5', 'D20').table.value
multiindex = Range('Sheet5', 'A20:C28').value
ix = pd.MultiIndex.from_tuples(multiindex[1:], names=multiindex[0])
df_result = DataFrame(cells[1:], columns=cells[0], index=ix)
assert_frame_equal(df_expected, df_result)
def test_dataframe_multiheader(self):
_skip_if_no_pandas()
df_expected = df_multiheader
Range('Sheet5', 'A52').value = df_expected
cells = Range('Sheet5', 'B52').table.value
df_result = DataFrame(cells[2:], columns=pd.MultiIndex.from_arrays(cells[:2]))
assert_frame_equal(df_expected, df_result)
def test_dataframe_dateindex(self):
_skip_if_no_pandas()
df_expected = df_dateindex
Range('Sheet5', 'A100').value = df_expected
cells = Range('Sheet5', 'B100').table.value
index = Range('Sheet5', 'A101').vertical.value
df_result = DataFrame(cells[1:], index=index, columns=cells[0])
assert_frame_equal(df_expected, df_result)
def test_series_1(self):
_skip_if_no_pandas()
series_expected = series_1
Range('Sheet5', 'A32').value = series_expected
cells = Range('Sheet5', 'B32:B37').value
series_result = Series(cells)
assert_series_equal(series_expected, series_result)
def test_timeseries_1(self):
_skip_if_no_pandas()
series_expected = timeseries_1
Range('Sheet5', 'A40').value = series_expected
cells = Range('Sheet5', 'B40:B49').value
date_index = Range('Sheet5', 'A40:A49').value
series_result = Series(cells, index=date_index)
assert_series_equal(series_expected, series_result)
def test_none(self):
""" Covers GH Issue #16"""
# None
Range('Sheet1', 'A7').value = None
assert_equal(None, Range('Sheet1', 'A7').value)
# List
Range('Sheet1', 'A7').value = [None, None]
assert_equal(None, Range('Sheet1', 'A7').horizontal.value)
def test_scalar_nan(self):
"""Covers GH Issue #15"""
_skip_if_no_numpy()
Range('Sheet1', 'A20').value = np.nan
assert_equal(None, Range('Sheet1', 'A20').value)
