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)