def __init__(self, parent = None,
editable = True,
nullable = True,
field_name = 'date',
**kwargs):
CustomEditor.__init__(self, parent)
self.setObjectName( field_name )
self.date_format = local_date_format()
self.line_edit = DecoratedLineEdit()
self.line_edit.set_minimum_width( len( self.date_format ) )
self.line_edit.set_background_text( QtCore.QDate(2000,1,1).toString(self.date_format) )
# The order of creation of this widgets and their parenting
# seems very sensitive under windows and creates system crashes
# so don't change this without extensive testing on windows
special_date_menu = QtGui.QMenu(self)
calendar_widget_action = QtGui.QWidgetAction(special_date_menu)
self.calendar_widget = QtGui.QCalendarWidget(special_date_menu)
self.calendar_widget.activated.connect(self.calendar_widget_activated)
self.calendar_widget.clicked.connect(self.calendar_widget_activated)
calendar_widget_action.setDefaultWidget(self.calendar_widget)
self.calendar_action_trigger.connect( special_date_menu.hide )
special_date_menu.addAction(calendar_widget_action)
special_date_menu.addAction(_('Today'))
special_date_menu.addAction(_('Far future'))
self.special_date = QtGui.QToolButton(self)
self.special_date.setIcon( self.special_date_icon.getQIcon() )
self.special_date.setAutoRaise(True)
self.special_date.setToolTip(_('Calendar and special dates'))
self.special_date.setMenu(special_date_menu)
self.special_date.setPopupMode(QtGui.QToolButton.InstantPopup)
self.special_date.setFixedHeight(self.get_height())
self.special_date.setFocusPolicy(Qt.ClickFocus)
# end of sensitive part
if nullable:
special_date_menu.addAction(_('Clear'))
self.hlayout = QtGui.QHBoxLayout()
self.hlayout.addWidget(self.line_edit)
self.hlayout.addWidget(self.special_date)
self.hlayout.setContentsMargins(0, 0, 0, 0)
self.hlayout.setSpacing(0)
self.hlayout.setAlignment(Qt.AlignRight|Qt.AlignVCenter)
self.setContentsMargins(0, 0, 0, 0)
self.setLayout(self.hlayout)
self.minimum = datetime.date.min
self.maximum = datetime.date.max
self.setFocusProxy(self.line_edit)
self.line_edit.editingFinished.connect( self.line_edit_finished )
self.line_edit.textEdited.connect(self.text_edited)
special_date_menu.triggered.connect(self.set_special_date)
def __init__(self,
parent=None,
editable=True,
nullable=True,
**kwargs):
CustomEditor.__init__(self, parent)
self.date_format = local_date_format()
self.line_edit = DecoratedLineEdit()
self.line_edit.set_background_text( QtCore.QDate(2000,1,1).toString(self.date_format) )
# The order of creation of this widgets and their parenting
# seems very sensitive under windows and creates system crashes
# so don't change this without extensive testing on windows
special_date_menu = QtGui.QMenu(self)
calendar_widget_action = QtGui.QWidgetAction(special_date_menu)
self.calendar_widget = QtGui.QCalendarWidget(special_date_menu)
self.calendar_widget.activated.connect(self.calendar_widget_activated)
self.calendar_widget.clicked.connect(self.calendar_widget_activated)
calendar_widget_action.setDefaultWidget(self.calendar_widget)
self.calendar_action_trigger.connect( special_date_menu.hide )
special_date_menu.addAction(calendar_widget_action)
special_date_menu.addAction(_('Today'))
special_date_menu.addAction(_('Far future'))
self.special_date = QtGui.QToolButton(self)
self.special_date.setIcon( self.special_date_icon.getQIcon() )
self.special_date.setAutoRaise(True)
self.special_date.setToolTip(_('Calendar and special dates'))
self.special_date.setMenu(special_date_menu)
self.special_date.setPopupMode(QtGui.QToolButton.InstantPopup)
self.special_date.setFixedHeight(self.get_height())
self.special_date.setFocusPolicy(Qt.ClickFocus)
# end of sensitive part
if nullable:
special_date_menu.addAction(_('Clear'))
self.hlayout = QtGui.QHBoxLayout()
self.hlayout.addWidget(self.line_edit)
self.hlayout.addWidget(self.special_date)
self.hlayout.setContentsMargins(0, 0, 0, 0)
self.hlayout.setMargin(0)
self.hlayout.setSpacing(0)
self.hlayout.setAlignment(Qt.AlignRight|Qt.AlignVCenter)
self.setContentsMargins(0, 0, 0, 0)
self.setLayout(self.hlayout)
self.minimum = datetime.date.min
self.maximum = datetime.date.max
self.setFocusProxy(self.line_edit)
self.line_edit.editingFinished.connect( self.line_edit_finished )
self.line_edit.textEdited.connect(self.text_edited)
special_date_menu.triggered.connect(self.set_special_date)
def __init__( self, filename ):
import xlrd
# assume a single sheet xls doc
workbook = xlrd.open_workbook( filename,
formatting_info = True )
self.xf_list = workbook.xf_list
self.datemode = workbook.datemode
self.format_map = workbook.format_map
self.sheet = workbook.sheets()[0]
self.current_row = 0
self.rows = self.sheet.nrows
self.date_format = local_date_format()
self.locale = QtCore.QLocale()
def __init__(self, filename):
import xlrd
try:
workbook = xlrd.open_workbook(filename, formatting_info=True)
except NotImplementedError:
# xlsx does not yet support formatting info
workbook = xlrd.open_workbook(filename)
self.xf_list = workbook.xf_list
self.datemode = workbook.datemode
self.format_map = workbook.format_map
self.sheets = workbook.sheets()
self.sheet = self.sheets[0]
self.current_row = 0
self.date_format = local_date_format()
self.locale = QtCore.QLocale()
def __init__(self, parent=None, **kwargs):
CustomDelegate.__init__(self, parent, **kwargs)
self.date_format = local_date_format()
self._width = self._font_metrics.averageCharWidth() * (len(self.date_format) + 2) + (camelot_small_icon_width*2)
def model_run( self, model_context ):
from decimal import Decimal
from xlwt import Font, Borders, XFStyle, Pattern, Workbook
from camelot.view.utils import ( local_date_format,
local_datetime_format,
local_time_format )
from camelot.view import action_steps
#
# setup worksheet
#
yield action_steps.UpdateProgress( text = _('Create worksheet') )
admin = model_context.admin
workbook = Workbook()
worksheet = workbook.add_sheet('Sheet1')
#
# keep a global cache of styles, since the number of styles that
# can be used is limited.
#
styles = dict()
freeze = lambda d:tuple(sorted(d.iteritems()))
def get_style( font_specs=dict(),
border_specs = dict(),
pattern = None,
num_format_str = None, ):
style_key = ( freeze(font_specs),
freeze(border_specs),
pattern,
num_format_str )
try:
return styles[style_key]
except KeyError:
style = XFStyle()
style.font = Font()
for key, value in font_specs.items():
setattr( style.font, key, value )
style.borders = Borders()
for key, value in border_specs.items():
setattr( style.borders, key, value )
if pattern:
style.pattern = pattern
if num_format_str:
style.num_format_str = num_format_str
styles[ style_key ] = style
return style
#
# write style
#
title_style = get_style( dict( font_name = self.font_name,
bold = True,
height = 240 ) )
worksheet.write( 0, 0, admin.get_verbose_name_plural(), title_style )
#
# create some patterns and formats
#
date_format = local_date_format()
datetime_format = local_datetime_format()
time_format = local_time_format()
header_pattern = Pattern()
header_pattern.pattern = Pattern.SOLID_PATTERN
header_pattern.pattern_fore_colour = 0x16
#
# write headers
#
columns = admin.get_columns()
field_names = []
for i, (name, field_attributes) in enumerate( columns ):
verbose_name = unicode( field_attributes.get( 'name', name ) )
field_names.append( name )
font_specs = dict( font_name = self.font_name,
bold = True,
height = 200 )
border_specs = dict( top = 0x01 )
name = unicode( name )
if i == 0:
border_specs[ 'left' ] = 0x01
elif i == len( columns ) - 1:
border_specs[ 'right' ] = 0x01
header_style = get_style( font_specs, border_specs, header_pattern )
worksheet.write( 2, i, verbose_name, header_style)
if len( name ) < 8:
worksheet.col( i ).width = 8 * 375
else:
worksheet.col( i ).width = len( verbose_name ) * 375
#
# write data
#
offset = 3
for j, obj in enumerate( model_context.get_collection( yield_per = 100 ) ):
dynamic_attributes = admin.get_dynamic_field_attributes( obj,
field_names )
row = offset + j
if j % 100 == 0:
yield action_steps.UpdateProgress( j, model_context.collection_count )
for i, ((_name, attributes), delta_attributes) in enumerate( zip( columns, dynamic_attributes ) ):
attributes.update( delta_attributes )
value = attributes['getter']( obj )
format_string = '0'
if value != None:
if isinstance( value, Decimal ):
value = float( str( value ) )
if isinstance( value, (unicode, str) ):
if attributes.get( 'translate_content', False ) == True:
value = ugettext( value )
# handle fields of type code
elif isinstance( value, list ):
value = u'.'.join(value)
elif isinstance( value, float ):
precision = attributes.get( 'precision', 2 )
format_string = '0.' + '0'*precision
elif isinstance( value, int ):
format_string = '0'
elif isinstance( value, datetime.date ):
format_string = date_format
elif isinstance( value, datetime.datetime ):
format_string = datetime_format
elif isinstance( value, datetime.time ):
format_string = time_format
else:
value = unicode( value )
else:
# empty cells should be filled as well, to get the
# borders right
value = ''
font_specs = dict( font_name = self.font_name, height = 200 )
border_specs = dict()
if i == 0:
border_specs[ 'left' ] = 0x01
elif i == len( columns ) - 1:
border_specs[ 'right' ] = 0x01
if (row - offset + 1) == model_context.collection_count:
border_specs[ 'bottom' ] = 0x01
style = get_style( font_specs,
border_specs,
None,
format_string )
worksheet.write( row, i, value, style )
min_width = len( unicode( value ) ) * 300
worksheet.col( i ).width = max( min_width, worksheet.col( i ).width )
yield action_steps.UpdateProgress( text = _('Saving file') )
filename = action_steps.OpenFile.create_temporary_file( '.xls' )
workbook.save( filename )
yield action_steps.UpdateProgress( text = _('Opening file') )
yield action_steps.OpenFile( filename )
def model_run(self, model_context):
from decimal import Decimal
from xlwt import Font, Borders, XFStyle, Pattern, Workbook
from camelot.view.import_utils import (RowData, ColumnMapping,
ColumnSelectionAdmin)
from camelot.view.utils import (local_date_format,
local_datetime_format,
local_time_format)
from camelot.view import action_steps
#
# Select the columns that need to be exported
#
admin = model_context.admin
all_fields = admin.get_all_fields_and_attributes()
field_choices = [(f, entity_fa['name'])
for f, entity_fa in all_fields.items()]
row_data = RowData(1, [None] * len(all_fields))
mapping = ColumnMapping(len(all_fields), [row_data], admin,
[field for field, _fa in admin.get_columns()])
mapping_admin = ColumnSelectionAdmin(len(all_fields), admin,
field_choices)
yield action_steps.ChangeObject(mapping, mapping_admin)
columns = []
for i in range(len(all_fields)):
field = getattr(mapping, 'column_%i_field' % i)
if field != None:
columns.append((field, all_fields[field]))
#
# setup worksheet
#
yield action_steps.UpdateProgress(text=_('Create worksheet'))
workbook = Workbook()
worksheet = workbook.add_sheet('Sheet1')
#
# keep a global cache of styles, since the number of styles that
# can be used is limited.
#
styles = dict()
freeze = lambda d: tuple(sorted(d.iteritems()))
def get_style(
font_specs=dict(),
border_specs=dict(),
pattern=None,
num_format_str=None,
):
style_key = (freeze(font_specs), freeze(border_specs), pattern,
num_format_str)
try:
return styles[style_key]
except KeyError:
style = XFStyle()
style.font = Font()
for key, value in font_specs.items():
setattr(style.font, key, value)
style.borders = Borders()
for key, value in border_specs.items():
setattr(style.borders, key, value)
if pattern:
style.pattern = pattern
if num_format_str:
style.num_format_str = num_format_str
styles[style_key] = style
return style
#
# write style
#
title_style = get_style(
dict(font_name=self.font_name, bold=True, height=240))
worksheet.write(0, 0, admin.get_verbose_name_plural(), title_style)
#
# create some patterns and formats
#
date_format = local_date_format()
datetime_format = local_datetime_format()
time_format = local_time_format()
header_pattern = Pattern()
header_pattern.pattern = Pattern.SOLID_PATTERN
header_pattern.pattern_fore_colour = 0x16
#
# write headers
#
field_names = []
for i, (name, field_attributes) in enumerate(columns):
verbose_name = unicode(field_attributes.get('name', name))
field_names.append(name)
font_specs = dict(font_name=self.font_name, bold=True, height=200)
border_specs = dict(top=0x01)
name = unicode(name)
if i == 0:
border_specs['left'] = 0x01
elif i == len(columns) - 1:
border_specs['right'] = 0x01
header_style = get_style(font_specs, border_specs, header_pattern)
worksheet.write(2, i, verbose_name, header_style)
if len(name) < 8:
worksheet.col(i).width = 8 * 375
else:
worksheet.col(i).width = len(verbose_name) * 375
#
# write data
#
offset = 3
for j, obj in enumerate(model_context.get_collection(yield_per=100)):
dynamic_attributes = admin.get_dynamic_field_attributes(
obj, field_names)
row = offset + j
if j % 100 == 0:
yield action_steps.UpdateProgress(
j, model_context.collection_count)
for i, ((_name, attributes), delta_attributes) in enumerate(
zip(columns, dynamic_attributes)):
attributes.update(delta_attributes)
value = attributes['getter'](obj)
format_string = '0'
if value != None:
if isinstance(value, Decimal):
value = float(str(value))
if isinstance(value, (unicode, str)):
if attributes.get('translate_content', False) == True:
value = ugettext(value)
# handle fields of type code
elif isinstance(value, list):
value = u'.'.join(value)
elif isinstance(value, float):
precision = attributes.get('precision', 2)
format_string = '0.' + '0' * precision
elif isinstance(value, int):
format_string = '0'
elif isinstance(value, datetime.date):
format_string = date_format
elif isinstance(value, datetime.datetime):
format_string = datetime_format
elif isinstance(value, datetime.time):
format_string = time_format
else:
value = unicode(value)
else:
# empty cells should be filled as well, to get the
# borders right
value = ''
font_specs = dict(font_name=self.font_name, height=200)
border_specs = dict()
if i == 0:
border_specs['left'] = 0x01
elif i == len(columns) - 1:
border_specs['right'] = 0x01
if (row - offset + 1) == model_context.collection_count:
border_specs['bottom'] = 0x01
style = get_style(font_specs, border_specs, None,
format_string)
worksheet.write(row, i, value, style)
min_width = len(unicode(value)) * 300
worksheet.col(i).width = max(min_width, worksheet.col(i).width)
yield action_steps.UpdateProgress(text=_('Saving file'))
filename = action_steps.OpenFile.create_temporary_file('.xls')
workbook.save(filename)
yield action_steps.UpdateProgress(text=_('Opening file'))
yield action_steps.OpenFile(filename)
def write_data_to_excel(filename, title, headerList, data_list):
"""
@param filename: the file to which to save the exported excel
@param title: title to put in the first row of the genarated excel file
@param headerList: list of header definitions
@param data_list: list or generator with the row data
"""
LOGGER.debug(u'write data to excel : %s'%title)
w = Workbook()
ws = w.add_sheet('Sheet1')
## Writing Title
ws.write(0, 0, title, titleStyle) # Writing Title
ws.col(0).width = len(title) * 400 # Setting cell width
## Writing Header
myDataTypeDict = {} # dictionary of datatype, {columnnumber, Datatype}
myPrecisionDict = {} # dictionary of precision , {columnnumber, Precision}
myLengthDict = {} # dictionary of length , {columnnumber, length}
myFormatDict = {} # dictionary of dateformat , {columnnumber, format}
header_delegates = {}
number_of_columns = len(headerList)
for n,desc in enumerate(headerList):
lst = desc[1]
header_delegates[n] = lst['delegate']
if n==0:
ws.write(2, n, unicode(lst['name']), topleftCellStyle)
elif n==len(headerList)-1:
ws.write(2, n, unicode(lst['name']), toprightCellStyle)
else:
ws.write(2, n, unicode(lst['name']), headerStyle)
if len(unicode(lst['name'])) < 8:
ws.col(n).width = 8 * 375
else:
ws.col(n).width = len(unicode(lst['name'])) * 375
myDataTypeDict[ n ] = lst["python_type"]
if lst["python_type"] == float:
myPrecisionDict [ n ] = lst["precision"] #Populating precision dictionary
elif lst["python_type"] == datetime.date or issubclass(lst['delegate'], delegates.DateDelegate):
myFormatDict [ n ] = lst.get('format', local_date_format()) #Populating date Format dictionary
myDataTypeDict[ n ] = datetime.date
elif lst["python_type"] == datetime.datetime or issubclass(lst['delegate'], delegates.DateTimeDelegate):
myDataTypeDict[ n ] = datetime.datetime
myFormatDict [ n ] = lst["format"] #Populating date Format dictionary
elif lst["python_type"] == str:
if 'length' in lst:
myLengthDict [ n ] = lst["length"] #Populating Column Length dictionary
## Writing Data
row = 3
valueAddedInSize = 0
formatStr = '0'
for data in data_list: # iterating the data_list, having dictionary
cellStyle.num_format_str = '0'
for column, val in enumerate( data ): #for i in dictCounter:
valueAddedInSize = 0
if val != None:
# this is to handle fields of type code
if isinstance(val, list):
val = '.'.join(val)
if not isinstance(val,(str,unicode,int,float,datetime.datetime,datetime.time,datetime.date,
ExcelFormula.Formula) ):
val = unicode(val)
if myDataTypeDict.has_key(column) == True:
if myLengthDict.get(column) != None:
if len(val) > myLengthDict[ column ]:
val = val[0:myLengthDict[ column ]]
elif myDataTypeDict[ column ] == str:
formatStr = '0'
elif myDataTypeDict[ column ] == int:
formatStr = '0'
elif myDataTypeDict[ column ] == float:
formatStr = '0.'
for _j in range( 0 , myPrecisionDict[ column ]):
formatStr += '0'
valueAddedInSize = len(formatStr) # To fit the cell width + 1 (of dot(.))
elif myDataTypeDict[ column ] == datetime.date or isinstance(header_delegates[column], delegates.DateDelegate):
formatStr = myFormatDict[column]
val = datetime.datetime( day = val.day, year = val.year, month = val.month)
elif myDataTypeDict[ column ] == datetime.datetime:
formatStr = myFormatDict[column]
elif myDataTypeDict[ column ] == bool:
formatStr = '0'
else:
formatStr = '0'
cellStyle.num_format_str = formatStr
bottomCellStyle.num_format_str = formatStr
rightCellStyle.num_format_str = formatStr
bottomrightCellStyle.num_format_str = formatStr
leftCellStyle.num_format_str = formatStr
bottomleftCellStyle.num_format_str = formatStr
elif val == None:
val = ' '
if row - 2 == len(data_list):
#we re at the bottom row
if column == 0:
ws.write(row , column, val , bottomleftCellStyle)
elif column == number_of_columns - 1:
ws.write(row , column, val , bottomrightCellStyle)
else:
ws.write(row , column, val , bottomCellStyle)
else:
if column == 0:
ws.write(row , column, val , leftCellStyle)
elif column == number_of_columns - 1:
ws.write(row , column, val , rightCellStyle)
else:
ws.write(row , column, val , cellStyle)
if ws.col(column).width < (len(unicode( val )) )* 300:
ws.col(column).width = (len(unicode( val )) + valueAddedInSize )* 300
column = column + 1
row = row + 1
w.save(filename)
