def test_to_excel_styleconverter(self):
from pandas.io.excel import CellStyleConverter
try:
import xlwt
import openpyxl
except ImportError:
raise nose.SkipTest
hstyle = {
"font": {"bold": True},
"borders": {"top": "thin", "right": "thin", "bottom": "thin", "left": "thin"},
"alignment": {"horizontal": "center"},
}
xls_style = CellStyleConverter.to_xls(hstyle)
self.assertTrue(xls_style.font.bold)
self.assertEquals(xlwt.Borders.THIN, xls_style.borders.top)
self.assertEquals(xlwt.Borders.THIN, xls_style.borders.right)
self.assertEquals(xlwt.Borders.THIN, xls_style.borders.bottom)
self.assertEquals(xlwt.Borders.THIN, xls_style.borders.left)
self.assertEquals(xlwt.Alignment.HORZ_CENTER, xls_style.alignment.horz)
xlsx_style = CellStyleConverter.to_xlsx(hstyle)
self.assertTrue(xlsx_style.font.bold)
self.assertEquals(openpyxl.style.Border.BORDER_THIN, xlsx_style.borders.top.border_style)
self.assertEquals(openpyxl.style.Border.BORDER_THIN, xlsx_style.borders.right.border_style)
self.assertEquals(openpyxl.style.Border.BORDER_THIN, xlsx_style.borders.bottom.border_style)
self.assertEquals(openpyxl.style.Border.BORDER_THIN, xlsx_style.borders.left.border_style)
self.assertEquals(openpyxl.style.Alignment.HORIZONTAL_CENTER, xlsx_style.alignment.horizontal)
def test_to_excel_styleconverter(self):
from pandas.io.excel import CellStyleConverter
try:
import xlwt
import openpyxl
except ImportError:
raise nose.SkipTest
hstyle = {"font": {"bold": True},
"borders": {"top": "thin",
"right": "thin",
"bottom": "thin",
"left": "thin"},
"alignment": {"horizontal": "center"}}
xls_style = CellStyleConverter.to_xls(hstyle)
self.assertTrue(xls_style.font.bold)
self.assertEquals(xlwt.Borders.THIN, xls_style.borders.top)
self.assertEquals(xlwt.Borders.THIN, xls_style.borders.right)
self.assertEquals(xlwt.Borders.THIN, xls_style.borders.bottom)
self.assertEquals(xlwt.Borders.THIN, xls_style.borders.left)
self.assertEquals(xlwt.Alignment.HORZ_CENTER, xls_style.alignment.horz)
xlsx_style = CellStyleConverter.to_xlsx(hstyle)
self.assertTrue(xlsx_style.font.bold)
self.assertEquals(openpyxl.style.Border.BORDER_THIN,
xlsx_style.borders.top.border_style)
self.assertEquals(openpyxl.style.Border.BORDER_THIN,
xlsx_style.borders.right.border_style)
self.assertEquals(openpyxl.style.Border.BORDER_THIN,
xlsx_style.borders.bottom.border_style)
self.assertEquals(openpyxl.style.Border.BORDER_THIN,
xlsx_style.borders.left.border_style)
self.assertEquals(openpyxl.style.Alignment.HORIZONTAL_CENTER,
xlsx_style.alignment.horizontal)
import pandas as pd
import numpy as np
from XLpandas2 import XLtable, XLseries
from xlwt import Workbook, Worksheet, Style
from copy import deepcopy
from pandas.io.excel import CellStyleConverter
conv = CellStyleConverter()
hstyle_dict = {
"font": {"bold": True},
"border": {"top": "thin", "right": "thin", "bottom": "thin", "left": "thin"},
"pattern": {"pattern": "solid", "fore_colour": 26},
"align": {"horiz": "center"},
}
hstyle = conv.to_xls(hstyle_dict)
dstyle_dict = {"border": {"top": "hair", "right": "hair", "bottom": "hair", "left": "hair"}}
dstyle = conv.to_xls(dstyle_dict)
dstyle.num_format_str = "$#,##0.00"
df = pd.read_excel("excel.xlsx", "cases (2)", index_col=None, na_values=["NA"])
df1 = XLtable(df)
wb = Workbook()
ws_1 = wb.add_sheet("cases", cell_overwrite_ok=False)
ws_2 = wb.add_sheet("XLtable", cell_overwrite_ok=False)
df1.place_table(ws_2)
def getDf():
# ----------- help--------------------------------------------------
parser = OptionParser(usage="%prog [options] [datafile_1] [datafile_22]")
(options, args) = parser.parse_args()
if len(args) == 0:
print "=============================================================="
print "no datafiles are given, plot default sets"
defaultValue = yesOrNo("?")
if defaultValue == 'y':
args.append('excel.xlsx')
else:
print 'No argument give, exiting...'
sys.exit()
parser.print_help()
datafiles = {}
for arg in args:
figName = arg.split('.')[0]
datafiles[figName] = arg
pds = []
for key in datafiles.keys():
names = key.split('_')[1:]
try:
if re.match(names[0], 'U'):
names = ['Ux', 'Uy', 'Uz']
except IndexError:
pass
#names = [name + '_' + loc for name in names]
print names
pd = pandas.read_excel(datafiles[key],
sheetname="cases",
header=0,
skiprows=0)
pds.append(pd)
# plt.hold(True)
# pd.plot(marker = 'o')
# pd2.plot(marker = 's')
pd_conc = pandas.concat(pds)
#pd_conc['Ux'].plot(marker='s', legend=True)
#pd_conc['Uy'].plot(marker='s', legend=True)
#pd_conc['Uz'].plot(marker='s', legend=True)
# plt.savefig('%s.pdf' % ('-'.join([arg.split('.')[0] for arg in args])))
# plt.show()
conv = CellStyleConverter()
hstyle_dict = {"font": {"bold": True},
"border": {"top": "thin",
"right": "thin",
"bottom": "thin",
"left": "thin"},
"pattern": {"pattern": "solid",
"fore_colour": 26},
"align": {"horiz": "center"}}
hstyle = conv.to_xls(hstyle_dict)
wb = Workbook()
ws = wb.add_sheet('test', cell_overwrite_ok=True)
df = XLpandas2.XLtable(pd_conc)
df.place_table(ws, rstyle=hstyle)
[ws.row(i).write(0, i, stylefunc(i)) for i in range(100)]
df.change_colStyle(ws, 5, hstyle)
wb.save('test.xls')
return pd_conc
def stylefunc(i):
conv = CellStyleConverter()
styledict = {"pattern": {"pattern": "solid", "fore_colour": i}}
return conv.to_xls(styledict)
