def main(reference_order_ids):
wb = pyExcelerator.Workbook()
ws = wb.add_sheet('final capture')
ws.write(0,0,)
PG_CODES = {'hdfc-card': 'HDPC',
'hdfc-emi3': 'HDP3',
'hdfc-emi6': 'HDP6',
'hdfc-emi9': 'HDP9',
'cc_avenue': 'CCAV'
}
approved_payments = []
for reference_order_id in reference_order_ids:
orders = Order.objects.filter(reference_order_id=reference_order_id)
for order in orders:
approved_payments.extend(order.paymentattempt_set.filter(status='approved'))
row = 0 # row counter
for approved_payment in approved_payments:
ws.write(row, ord('A')-ord('A'), approved_payment.order.reference_order_id)
ws.write(row, ord('D')-ord('A'), approved_payment.order.reference_order_id)
ws.write(row, ord('F')-ord('A'), PG_CODES.get(approved_payment.gateway+approved_payment.emi_plan))
ws.write(row, ord('N')-ord('A'), str(approved_payment.amount))
ws.write(row, ord('O')-ord('A'), approved_payment.transaction_id)
row += 1
# Safely create a tempfile and close the file descriptor to save it as
# empty file. This makes a persistent file on disk. We need to close the
# descriptor, to be on the safer side because pyExcelerator works with
# file's name and not file descriptor.
fd, path = tempfile.mkstemp('.xls', 'PAYMENT_CAPTURE_', '/tmp/')
os.close(fd)
# Save Workbook to file
wb.save(path)
print "order infomation is saved at: %s" % path
def write_execl(dict, all_value):
# dict 需要写入文件的数据列表
#all_value 所有出网访问总和
# 创建workbook和sheet对象
wb = pyExcelerator.Workbook()
ws = wb.add_sheet(u'第一页')
ws.write(0, 0, u'域名')
ws.write(0, 1, u'访问次数')
ws.write(0, 2, u'域名占比')
item = 0
# 只写入TOP100
while item < 100:
# while item<100:
row = 0
while row < 2:
# print dict[item][row]
ws.write(item + 1, row, dict[item][row])
row += 1
ws.write(item + 1, 2, format(dict[item][1] / float(all_value),
".2%")) # j计算百分比占比
item += 1
wb.save('top100.xls')
print "汇总数据写入top100.xls文件完成！"
print "stoptime:" + str(time.clock())
def __init__(self, encoding=None):
super(XLSformatter, self).__init__()
self._encoding = encoding or i18n.get_site_encoding()
self._book = xl.Workbook()
self._sheet = self._book.add_sheet("Sheet 1")
self._row = 0
self._size = 0
def convertToSingleFile(fileDir, targetDir):
destDir = genDestDir(targetDir)
for _, dirnames, _ in os.walk(fileDir):
valuesDirs = [di for di in dirnames if di.startswith("values")]
for dirname in valuesDirs:
for _, _, filenames in os.walk(fileDir + '/' + dirname):
xmlFiles = [fi for fi in filenames if fi.endswith(".xml")]
for xmlfile in xmlFiles:
fileName = xmlfile.replace(".xml", "")
filePath = destDir + "/" + fileName + ".xls"
if not os.path.exists(filePath):
workbook = pyExcelerator.Workbook()
ws = workbook.add_sheet(fileName)
index = 0
for dirname in dirnames:
if index == 0:
ws.write(0, 0, 'keyName')
countryCode = getCountryCode(dirname)
ws.write(0, index + 1, countryCode)
path = fileDir + '/' + dirname + '/' + xmlfile
(keys, values) = XmlFileUtil.getKeysAndValues(path)
for x in range(len(keys)):
key = keys[x]
value = values[x]
if (index == 0):
ws.write(x + 1, 0, key)
ws.write(x + 1, 1, value)
else:
ws.write(x + 1, index + 1, value)
index += 1
workbook.save(filePath)
print "Convert %s successfully! you can see xls file in %s" % (fileDir,
destDir)
def writeExcel(self, headers, value, path):
mydoc = xl.Workbook()
#Add a worksheet
mysheet = mydoc.add_sheet("anagrafiche")
header_font = xl.Font() #make a font object
header_font.bold = True
header_font.underline = True
#font needs to be style actually
replies = {}
header_style = xl.XFStyle()
header_style.font = header_font
xl.UnicodeUtils.DEFAULT_ENCODING = 'cp1251'
for col, values in enumerate(headers):
mysheet.write(0, col, values, header_style)
for row in enumerate(value):
Id = row[1].data(1).toInt()[0]
if not replies.has_key(Id):
replies[Id] = ""
pv = self.db.getPvById(Id)
packet = self.profile.getData(Id)
for col in range(0, len(packet)):
mysheet.write(row[0] + 1, col, unicode(packet[col]))
if str(path) != "":
mydoc.save(path)
infoString = "%d anagrafiche esportate correttamente" % len(
replies)
d = QtGui.QMainWindow()
QtGui.QMessageBox.information(d, "Info", infoString)
def convertToMultipleFiles(stringsDir, targetDir):
destDir = targetDir + "/strings-files-to-xls_" + \
time.strftime("%Y%m%d_%H%M%S")
if not os.path.exists(destDir):
os.makedirs(destDir)
for _, dirnames, _ in os.walk(stringsDir):
lprojDirs = [di for di in dirnames if di.endswith(".lproj")]
for dirname in lprojDirs:
workbook = pyExcelerator.Workbook()
for _, _, filenames in os.walk(stringsDir + '/' + dirname):
stringsFiles = [
fi for fi in filenames if fi.endswith(".strings")
]
for stringfile in stringsFiles:
ws = workbook.add_sheet(stringfile)
path = stringsDir + dirname + '/' + stringfile
(keys, values) = StringsFileUtil.getKeysAndValues(path)
for keyIndex in range(len(keys)):
key = keys[keyIndex]
value = values[keyIndex]
ws.write(keyIndex, 0, key)
ws.write(keyIndex, 1, value)
filePath = destDir + "/" + dirname.replace(".lproj", "") + ".xls"
workbook.save(filePath)
print "Convert %s successfully! you can see xls file in %s" % (stringsDir,
destDir)
def outExcel(host, user , password ,sql ,table_str ):
conn = db.MyDBConnect( host, user , password )
reData = conn.selectData(sql)
##excel操作
w = pyExcelerator.Workbook()
ws = w.add_sheet('data')
table_str2 = table_str.split(',')
i = 0
for str in table_str2:
ws.write(0,i,str)
i = i +1
num = 0
for re in reData:
num = num + 1
for k in range(1,len(re)+1):
ws.write(num,k-1,re[k-1])
print '\033[1;31;40m'
print '*' * 50
print '-----------生成完成( 路径 ： 在哪个目录执行 sudo yunyinexcel就在哪个地方取 )---------------'
print '*' * 50
print '\033[0m'
timenow = time.strftime('%Y-%m-%d_%H-%M-%S',time.localtime(time.time()))
xslname = user+timenow+'.xls'
w.save( xslname )
def mac2count(file_path, database_addr):
""" mac地址统计 """
xl = xlrd.open_workbook(file_path)
mac_list = []
for s in xrange(len(xl.sheets())):
sheet = xl.sheet_by_index(s)
mac_list, start_time, end_time = get_sheet(sheet)
# 用户表
mongo_user = MongoFive(database_addr[0],
EXT.BASEUSER_USER,
host=database_addr[1])
user_querys = {EXT.KEY_MAC: {EXT.MONGO_IN: mac_list}}
user_list = mongo_user.distince_filter(EXT.KEY_MONGO_ID, user_querys)
# 需要写入的excel表
file_path = '/'.join(file_path.split("/")[:-1])
file_path = ''.join([file_path, "/mac"])
if not os.path.isdir(file_path):
os.mkdir(file_path)
w = pyExcelerator.Workbook()
ws = w.add_sheet("sheet1")
ws.write(0, 0, u'uid')
ws.write(0, 1, u'角色id')
ws.write(0, 2, u'角色名')
ws.write(0, 3, u'等级')
ws.write(0, 4, u'VIP')
ws.write(0, 5, u'登录次数')
ws.write(0, 6, u'登录详细')
# 通用uid查找出player信息
player_info(user_list, database_addr, start_time, end_time, ws, w,
file_path)
def ExportExcel(self, event=None):
""" Export Excel """
titre = _(u"Destinataires")
# Vérifie qu"il y a des adresses mails saisies
if len(self.donnees) == 0 :
dlg = wx.MessageDialog(self, _(u"La liste des destinataires est vide !"), "Erreur", wx.OK| wx.ICON_EXCLAMATION)
dlg.ShowModal()
dlg.Destroy()
return
# Demande à l'utilisateur le nom de fichier et le répertoire de destination
nomFichier = "ExportExcel_%s.xls" % datetime.datetime.now().strftime("%Y%m%d%H%M%S")
wildcard = "Fichier Excel (*.xls)|*.xls|" \
"All files (*.*)|*.*"
sp = wx.StandardPaths.Get()
cheminDefaut = sp.GetDocumentsDir()
dlg = wx.FileDialog(
None, message = _(u"Veuillez sélectionner le répertoire de destination et le nom du fichier"), defaultDir=cheminDefaut,
defaultFile = nomFichier,
wildcard = wildcard,
style = wx.FD_SAVE
)
dlg.SetFilterIndex(0)
if dlg.ShowModal() == wx.ID_OK:
cheminFichier = dlg.GetPath()
dlg.Destroy()
else:
dlg.Destroy()
return
# Le fichier de destination existe déjà :
if os.path.isfile(cheminFichier) == True :
dlg = wx.MessageDialog(None, _(u"Un fichier portant ce nom existe déjà. \n\nVoulez-vous le remplacer ?"), "Attention !", wx.YES_NO | wx.NO_DEFAULT | wx.ICON_EXCLAMATION)
if dlg.ShowModal() == wx.ID_NO :
return False
dlg.Destroy()
else:
dlg.Destroy()
# Export
import pyExcelerator
# Création d'un classeur
wb = pyExcelerator.Workbook()
# Création d'une feuille
ws1 = wb.add_sheet(titre)
# Remplissage de la feuille
x = 0
for track in self.donnees :
ws1.write(x, 0, track.adresse)
x += 1
# Finalisation du fichier xls
wb.save(cheminFichier)
# Confirmation de création du fichier et demande d'ouverture directe dans Excel
txtMessage = _(u"Le fichier Excel a été créé avec succès. Souhaitez-vous l'ouvrir dès maintenant ?")
dlgConfirm = wx.MessageDialog(None, txtMessage, _(u"Confirmation"), wx.YES_NO|wx.NO_DEFAULT|wx.ICON_QUESTION)
reponse = dlgConfirm.ShowModal()
dlgConfirm.Destroy()
if reponse == wx.ID_NO:
return
else:
FonctionsPerso.LanceFichierExterne(cheminFichier)
def output_excel(results):
work_book = excel.Workbook()
doc_sheet = work_book.add_sheet("sheet1")
for rowID, row in enumerate(results):
for cellID, cell in enumerate(row):
doc_sheet.write(rowID, cellID, cell)
doc_sheet.col(0).width = 0x0d00 + 5000
work_book.save('cleaned.xls')
def download_report(self, request, queryset):
wb = pyExcelerator.Workbook()
ws_1 = wb.add_sheet('subscribed')
ws_2 = wb.add_sheet('email_unsubscribed')
ws_3 = wb.add_sheet('mobile_unsubscribed')
row = 0
ws_1.write(row, 0, 'EMAIL')
ws_1.write(row, 1, 'PHONE')
ws_1.write(row, 2, 'SOURCE')
ws_1.write(row, 3, 'EMAIL ALERT')
ws_1.write(row, 4, 'SMS ALERT')
ws_2.write(row, 0, 'EMAIL')
ws_2.write(row, 1, 'SOURCE')
ws_2.write(row, 2, 'EMAIL ALERT')
ws_3.write(row, 0, 'MOBILE')
ws_3.write(row, 1, 'SOURCE')
ws_3.write(row, 2, 'SMS ALERT')
row += 1
row1, row2, row3 = 1, 1, 1
for qs in queryset:
is_subscribed_any = False
try:
if qs.email_alert_on.email:
if qs.is_email_alert:
ws_1.write(row1, 0, unicode(qs.email_alert_on.email))
ws_1.write(row1, 3, unicode(qs.is_email_alert))
is_subscribed_any = True
else:
ws_2.write(row2, 0, unicode(qs.email_alert_on.email))
ws_2.write(row2, 1, unicode(qs.source))
ws_2.write(row2, 2, unicode(qs.is_email_alert))
row2 += 1
except:
pass
try:
if qs.sms_alert_on.phone:
if qs.is_sms_alert:
ws_1.write(row1, 1, unicode(qs.sms_alert_on.phone))
ws_1.write(row1, 4, unicode(qs.is_sms_alert))
is_subscribed_any = True
else:
ws_3.write(row3, 0, unicode(qs.sms_alert_on.phone))
ws_3.write(row3, 1, unicode(qs.source))
ws_3.write(row3, 2, unicode(qs.is_sms_alert))
row3 += 1
except:
pass
if is_subscribed_any:
ws_1.write(row1, 2, unicode(qs.source))
row1 += 1
filename = '/tmp/emailreport'
wb.save(filename)
response = HttpResponse(file(filename).read(), mimetype='text/xls')
response['Content-Disposition'] = 'attachment; filename=report.xls'
return response
def write(self, records):
wb = pyExcelerator.Workbook()
ws0 = wb.add_sheet('0')
for x, record in enumerate(records):
for y, v in enumerate(record):
# write value to a specific coordinate
try:
ws0.write(x, y, v)
except Exception, e:
print 'Failed to write', v
print e
def save_excel_file(excel_header, excel_data):
workBookDocument = pyExcelerator.Workbook()
docSheet1 = workBookDocument.add_sheet("sheet1")
#Create a font object *j
myFont = pyExcelerator.Font()
# Change the font
myFont.name = 'Times New Roman'
# Make the font bold, underlined and italic
myFont.bold = True
myFont.underline = True
# the font should be transformed to style *
myFontStyle = pyExcelerator.XFStyle()
myFontStyle.font = myFont
# if you wish to apply a specific style to a specific row you can use the following command
docSheet1.row(0).set_style(myFontStyle)
# docSheet1.write(0,column, key,myFontStyle)
for i in range(len(excel_header)):
if type(excel_header[i]).__name__ in ["date", "datetime"]:
entry = str(excel_header[i].day) + '-' + str(
excel_header[i].month) + '-' + str(excel_header[i].year)
else:
entry = str(excel_header[i])
docSheet1.write(0, i, entry, myFontStyle)
row = 0
for list in excel_data:
row = row + 1
for i in range(len(list)):
if list[i] is not None:
if type(list[i]).__name__ in ["date", "datetime"]:
entry = str(list[i].day) + '-' + str(
list[i].month) + '-' + str(list[i].year)
docSheet1.write(row, i, entry)
elif type(list[i]).__name__ in ["unicode"]:
entry = smart_unicode(list[i],
encoding='utf-8',
strings_only=False,
errors='strict')
# entry = unicode(list[i]).encode("iso-8859-1")
docSheet1.write(row, i, entry)
else:
docSheet1.write(row, i, str(list[i]))
filename = "report.xls"
response = HttpResponse(mimetype="application/vnd.ms-excel")
response['Content-Disposition'] = 'attachment; filename=%s' % filename
workBookDocument.save(response)
return response
def write(cwd, list1, table_title, name):
wb = xlwd.Workbook()
xlwd.UnicodeUtils.DEFAULT_ENCODING = 'cp932'
ws = wb.add_sheet('sheet')
for i in range(len(table_title)):
ws.write(0, i, table_title[i])
for r in range(len(list1)):
temp_item = list1[r]
for c in range(len(temp_item)):
temp = temp_item[c]
ws.write(r + 1, c, temp)
wb.save(cwd + '\\' + name)
def email_excel_file(excel_header, excel_data):
workBookDocument = pyExcelerator.Workbook()
docSheet1 = workBookDocument.add_sheet("sheet1")
#Create a font object *j
myFont = pyExcelerator.Font()
# Change the font
myFont.name = 'Times New Roman'
# Make the font bold, underlined and italic
myFont.bold = True
myFont.underline = True
# the font should be transformed to style *
myFontStyle = pyExcelerator.XFStyle()
myFontStyle.font = myFont
# if you wish to apply a specific style to a specific row you can use the following command
docSheet1.row(0).set_style(myFontStyle)
# docSheet1.write(0,column, key,myFontStyle)
for i in range(len(excel_header)):
if type(excel_header[i]).__name__ in ["date", "datetime"]:
entry = str(excel_header[i].day) + '-' + str(
excel_header[i].month) + '-' + str(excel_header[i].year)
else:
entry = str(excel_header[i])
docSheet1.write(0, i, entry, myFontStyle)
row = 0
for list in excel_data:
row = row + 1
for i in range(len(list)):
if list[i] is not None:
if type(list[i]).__name__ in ["date", "datetime"]:
entry = str(list[i].day) + '-' + str(
list[i].month) + '-' + str(list[i].year)
docSheet1.write(row, i, entry)
else:
docSheet1.write(row, i, str(list[i]))
email_subject = "Future Bazaar Feedbacks"
email_body = "Please find attached the list of feedbacks received today."
email_from = "Chaupaati Bazaar<*****@*****.**>"
email_to = "*****@*****.**"
email_bcc = ""
mail_obj = EmailMessage(email_subject, email_body, email_from,
email_to.split(','), email_bcc.split(','), None)
mail_obj.attach('filename.xls', workBookDocument, 'text/xls')
mail_obj.send()
def excel_file(self, dados, filename):
try:
#Open new workbook
mydoc = xl.Workbook()
for item in dados:
#Add a worksheet
mysheet = mydoc.add_sheet(item.get('title'))
#write headers
header_font = xl.Font() #make a font object
header_font.bold = True
header_font.underline = True
#font needs to be style actually
header_style = xl.XFStyle()
header_style.font = header_font
headers = item.get('head')
values = item.get('values')
for col, value in enumerate(headers):
mysheet.write(0, col, value, header_style)
#write values and highlight those that match my criteria
highlighted_row_font = xl.Font(
) #no real highlighting available?
highlighted_row_font.bold = True
highlighted_row_font.colour_index = 2 #2 is red,
highlighted_row_style = xl.XFStyle()
highlighted_row_style.font = highlighted_row_font
for row_num, row_values in enumerate(values):
row_num += 1 #start at row 1
if row_values[1] == 'Manatee':
for col, value in enumerate(row_values):
#make Manatee's (sp) red
mysheet.write(row_num, col, value,
highlighted_row_style)
else:
for col, value in enumerate(row_values):
#normal row
mysheet.write(row_num, col, value)
#save file
mydoc.save(filename)
return True
except:
return False
def __create_spreadsheet_file(datos, data_file):
"""
Crea una hoja de calculo con formato EXCEL, con los datos pasados
@param datos: Una lista con las filas de datos que contendra el archivo
@type datos: list
@param data_file: El archivo donde se guardaran los datos en excel
@type data_file: string
@rtype: void
"""
w = pyExcelerator.Workbook()
ws = w.add_sheet('Datos Monitorizados')
for i in range(len(datos)):
for j in range(len(datos[i])):
ws.write(i, j, datos[i][j])
w.save(data_file + ".xls")
def xml2xls_single(self, xls_dir, input_file_path):
# type: (str, str) -> object
if not xls_dir or not os.path.exists(xls_dir):
return Constant.Error(Constant.ERROR_DIR_NOT_EXIST, "excel dir")
if not input_file_path or not os.path.exists(input_file_path):
return Constant.Error(Constant.ERROR_XML_FILE_NOT_EXIST)
xlsPath = os.path.join(xls_dir, Constant.Config.export_excel_name)
workbook = pyExcelerator.Workbook()
ws = workbook.add_sheet('Sheet1')
# row col content
ws.write(0, 0, Constant.Config.keyTitle)
dic = XMLParse.get_value_and_key(input_file_path)
writeDict(ws, dic, 1, 0, None, True)
workbook.save(xlsPath)
return Constant.Error(Constant.SUCCESS)
def convert(response,
encoding='utf-8',
title_sheet='Sheet 1',
content_attr='content',
csv_kwargs=None):
csv_kwargs = csv_kwargs or {}
workbook = pyExcelerator.Workbook()
worksheet = workbook.add_sheet(title_sheet)
lno = 0
content = get_content(response)
reader = csv.reader(content, **csv_kwargs)
for line in reader:
write_row(worksheet, lno, line, encoding=encoding)
lno = lno + 1
setattr(response, 'content_attr', workbook.get_biff_data())
def startConvert(options):
directory = options.filesDirectory
targetFilePath = options.targetFilePath
if directory is not None:
index = 0
if targetFilePath is not None:
workbook = pyExcelerator.Workbook()
ws = workbook.add_sheet('Localizable.strings')
for parent, dirnames, filenames in os.walk(directory):
for dirname in dirnames:
# KeyName & CountryCode
if index == 0:
ws.write(0, 0, 'keyName')
conturyCode = 'en'
dirSplit = dirname.split('values-')
if len(dirSplit) > 1:
conturyCode = dirSplit[1]
ws.write(0, index + 1, conturyCode)
# Key & Value
path = directory + '/' + dirname + '/strings.xml'
(keys, values) = StringsXmlFileUtil.getKeysAndValues(path)
for x in range(len(keys)):
key = keys[x]
value = values[x]
if (index == 0):
ws.write(x + 1, 0, key)
ws.write(x + 1, 1, value)
else:
ws.write(x + 1, index + 1, value)
index += 1
filePath = targetFilePath + "/Localizable.xls"
workbook.save(filePath)
print "Convert successfully! you can see xls file in %s" % (
filePath)
else:
print "Target file path can not be empty! try -h for help."
else:
print "StringsXml files directory can not be empty! try -h for help."
def startConvert(sourceFolder, xlsFolder, apptype):
if sourceFolder is not None:
if xlsFolder is not None:
workbook = pyExcelerator.Workbook()
ws = workbook.add_sheet('Localizable.strings')
# init keyName and standard language value (en)
stKeys, stValues = _getStandardKeyValuesListFrom(
sourceFolder, apptype)
if len(stKeys) <= 0:
Log.error('The language files is empty')
return
ws.write(0, 0, 'keyName')
ws.write(0, 1, 'en')
for row in range(len(stKeys)):
ws.write(row + 1, 0, stKeys[row])
ws.write(row + 1, 1, stValues[row])
# append other language value
index = 2
for parent, dirnames, filenames in os.walk(sourceFolder):
for dirname in dirnames:
conturyCode = _getCountryCode(dirname, apptype)
if conturyCode == 'en':
continue
ws.write(0, index, conturyCode)
otherLanguage = _getKeyValuesDictFrom(
os.path.join(sourceFolder, dirname), apptype)
for row in range(len(stKeys)):
if stKeys[row] in otherLanguage:
ws.write(row + 1, index,
otherLanguage[stKeys[row]])
index += 1
filePath = os.path.join(xlsFolder, "Localizable.xls")
workbook.save(filePath)
Log.info("Convert successfully! you can see xls file in %s" %
(filePath))
else:
Log.error("xls folder path can not be empty! try -h for help.")
else:
Log.error(
"strings or xml files filder can not be empty! try -h for help.")
def export_csv(self):
fileDoc = xl.Workbook()
sheet1 = fileDoc.add_sheet("Informe presencias")
sheet1 = self.export_profit(sheet1)
fname = 'Informe_presencias' + '.xls'
file = StringIO.StringIO()
fileDoc.save(file)
fileDocFin = base64.encodestring(file.getvalue())
vals = {'csv_file': fileDocFin, 'csv_fname': fname}
wiz = self.env['wiz.export.csv'].create(vals)
return {
'type': 'ir.actions.act_window',
'res_model': 'wiz.export.csv',
'view_type': 'form',
'view_mode': 'form',
'res_id': wiz.id,
'target': 'new',
}
def convertToSingleFile(file_dir, target_dir):
dest_dir = genDestDir(target_dir)
default_key_values = {}
for _, dir_names, _ in os.walk(file_dir):
if "values" in dir_names:
dir_names.remove("values")
dir_names.insert(0, "values")
values_dirs = [di for di in dir_names if di.startswith("values")]
for dir_name in values_dirs:
for _, _, filenames in os.walk(os.path.join(file_dir, dir_name)):
xml_file = "strings.xml"
file_name = xml_file.replace(".xml", "")
file_path = os.path.join(dest_dir, file_name + ".xls")
if not os.path.exists(file_path):
workbook = pyExcelerator.Workbook()
ws = workbook.add_sheet(file_name)
index = 0
for dir_name in dir_names:
path = os.path.join(file_dir, dir_name, xml_file)
if os.path.exists(path):
if index == 0:
ws.write(0, 0, 'keyName')
country_code = getCountryCode(dir_name)
ws.write(0, index + 1, country_code)
(keys, values,
keyValues) = XmlFileUtil.getKeysAndValues(path)
x = 0
if dir_name == "values":
default_key_values = keyValues
for key, value in default_key_values.items():
rel_value = keyValues[
key] if key in keyValues else ""
if index == 0:
ws.write(x + 1, 0, key)
ws.write(x + 1, 1, rel_value)
else:
ws.write(x + 1, index + 1, rel_value)
x += 1
index += 1
workbook.save(file_path)
print "Convert %s successfully! you can see xls file in %s" % (file_dir,
dest_dir)
def merge(self):
'''Merge data
Master method to perform Populate, Merge, Export and Import operations
across all locales.
User updates in the form of locale files should be
placed below a subdirectory ``locale-rev``, in directories
corresponding to the name of the target locale. Update
files may be deleted after merging.
User updates in the form of (revised) exported CSV
files should be placed below a subdirectory ``locale-csv-in``,
in directories corresponding to the name of the target
locale. Update files may be deleted after merging.
After every merge, updated CSV files for all locales
is available for circulation under ``locale-csv-out``.
'''
for locale in os.listdir('chrome/locale'):
if locale == '.git' or locale == '.svn' or locale == 'en-US':
continue
print "Normalizing locale files for: %s" % locale
for filename in self.files:
data = self.processFile(locale, filename)
self.output(locale, filename, data)
for locale in os.listdir('chrome/locale'):
if locale == '.git' or locale == '.svn':
continue
self.wb = pyExcelerator.Workbook()
self.masterData = {}
for filename in self.files:
# Extract from existing locale
self.masterData[filename] = self.extract(locale, filename, template='chrome/locale/%s/zotero/%s')
# Overwrite anything contained in update files
self.masterData[filename].update(self.extract(locale, filename, template='locale-rev/%s/zotero/%s', cautious=True))
self.masterData[filename].update(self.extract_csv(locale, filename))
self.masterData[filename].update(self.extract_xls(locale, filename))
data = self.processFile(locale, filename, force=True)
self.output(locale, filename, data, export=True)
self.wb.save('locale-xls-out/%s.xls' % locale)
def convertToSingleFile(stringsDir, targetDir):
destDir = targetDir + "/strings-files-to-xls_" + \
time.strftime("%Y%m%d_%H%M%S")
if not os.path.exists(destDir):
os.makedirs(destDir)
# Create xls sheet
for _, dirnames, _ in os.walk(stringsDir):
lprojDirs = [di for di in dirnames if di.endswith(".lproj")]
for dirname in lprojDirs:
for _, _, filenames in os.walk(stringsDir + '/' + dirname):
stringsFiles = [
fi for fi in filenames if fi.endswith(".strings")
]
for stringfile in stringsFiles:
fileName = stringfile.replace(".strings", "")
filePath = destDir + "/" + fileName + ".xls"
if not os.path.exists(filePath):
workbook = pyExcelerator.Workbook()
ws = workbook.add_sheet(fileName)
index = 0
for dirname in dirnames:
if index == 0:
ws.write(0, 0, 'keyName')
countryCode = dirname.replace(".lproj", "")
ws.write(0, index + 1, countryCode)
path = stringsDir + '/' + dirname + '/' + stringfile
(keys,
values) = StringsFileUtil.getKeysAndValues(path)
for x in range(len(keys)):
key = keys[x]
value = values[x]
if (index == 0):
ws.write(x + 1, 0, key)
ws.write(x + 1, 1, value)
else:
ws.write(x + 1, index + 1, value)
index += 1
workbook.save(filePath)
print "Convert %s successfully! you can see xls file in %s" % (stringsDir,
destDir)
def startConvert(options):
directory = options.filesDirectory
targetFilePath = options.targetFilePath
if directory is not None:
if targetFilePath is not None:
destinyDir = targetFilePath + "/strings-files-to-xls_" + time.strftime(
"%Y%m%d_%H%M%S")
if not os.path.exists(destinyDir):
os.makedirs(destinyDir)
for parent, dirnames, unusedfilenames in os.walk(directory):
lprogDirs = [di for di in dirnames if di.endswith(".lproj")]
for dirname in lprogDirs:
workbook = pyExcelerator.Workbook()
for parent, dirnames2, filenames in os.walk(directory +
'/' + dirname):
stringFiles = [
fi for fi in filenames if fi.endswith(".strings")
]
for stringfile in stringFiles:
ws = workbook.add_sheet(stringfile)
# Key & Value
path = directory + dirname + '/' + stringfile
(keys, values
) = LocalizableStringsFileUtil.getKeysAndValues(
path)
for keyIndex in range(len(keys)):
key = keys[keyIndex]
value = values[keyIndex]
ws.write(keyIndex, 0, key)
ws.write(keyIndex, 1, value)
filePath = destinyDir + "/" + dirname.replace(
".lproj", "") + ".xls"
workbook.save(filePath)
print "Convert %s successfully! you can see xls file in %s" % (
directory, filePath)
else:
print "Target file path can not be empty! try -h for help."
else:
print "Localizable.strings files directory can not be empty! try -h for help."
def startConvert(options):
directory = options.filesDirectory
targetFilePath = options.targetFilePath
if directory is not None:
if targetFilePath is not None:
workbook = pyExcelerator.Workbook()
for dirname in os.listdir(directory):
if not dirname.startswith('.'):
# print directory + dirname
addOneSheet(directory, dirname, workbook)
filePath = targetFilePath + "/Localizable_Android.xls"
workbook.save(filePath)
print "Convert successfully! you can see xls file in %s" % (
filePath)
else:
print "Target file path can not be empty! try -h for help."
else:
print "StringsXml files directory can not be empty! try -h for help."
def writeToOneExcel(dirName='', toExcelName=''):
filelists = dir2list(dirName)
# i 局部变量，控制写入的行
i = 0
#创建一个excel工作簿
w = pyExcelerator.Workbook()
#为工作簿添加sheet1工作表
ws = w.add_sheet('Sheet1')
#控制不读取目录中最后一个文件（具体描述见注意事项）
for afile in filelists[0:-1]:
lists = readOneExcel(afile)
print("%s" % time.ctime())
print("read file %s ..." % afile)
#根据单元格坐标写入
for x in range(len(lists)):
ws.write(i, x, lists[x])
i = i + 1
print("write file %s to new.xls...\n" % afile)
#每写入一行保存一次
w.save(toExcelName)
time.sleep(2)
print("end ...")
def convertToMultipleFiles(fileDir, targetDir):
destDir = genDestDir(targetDir)
for _, dirnames, _ in os.walk(fileDir):
valuesDirs = [di for di in dirnames if di.startswith("values")]
for dirname in valuesDirs:
workbook = pyExcelerator.Workbook()
for _, _, filenames in os.walk(fileDir + '/' + dirname):
xmlFiles = [fi for fi in filenames if fi.endswith(".xml")]
for xmlfile in xmlFiles:
ws = workbook.add_sheet(xmlfile)
path = fileDir + '/' + dirname + '/' + xmlfile
(keys, values) = XmlFileUtil.getKeysAndValues(path)
for keyIndex in range(len(keys)):
key = keys[keyIndex]
value = values[keyIndex]
ws.write(keyIndex, 0, key)
ws.write(keyIndex, 1, value)
filePath = destDir + "/" + getCountryCode(dirname) + ".xls"
workbook.save(filePath)
print "Convert %s successfully! you can see xls file in %s" % (fileDir,
destDir)
def export(options, filename):
directory = options.filesDirectory
targetDirectory = options.targetDirectory
index = 0
workbook = pyExcelerator.Workbook()
ws = workbook.add_sheet(filename) # ('*.strings')
for parent, dirnames, filenames in os.walk(directory):
for dirname in dirnames:
# 排除非多语言资源目录
if ".lproj" not in dirname:
continue
if "Base.lproj" in dirname:
continue
# KeyName & CountryCode
if index == 0:
ws.write(0, 0, 'keyName')
conturyCode = dirname.split('.')[0]
ws.write(0, index + 1, conturyCode)
# Key & Value
path = directory + '/' + dirname + '/' + filename # '/*.strings'
(keys, values) = LocalizableStringsFileUtil.getKeysAndValues(path)
for x in range(len(keys)):
key = keys[x]
value = values[x]
if (index == 0):
ws.write(x + 1, 0, key)
ws.write(x + 1, 1, value)
else:
ws.write(x + 1, index + 1, value)
index += 1
xlsName = filename.replace(".strings", ".xls")
filePath = targetDirectory + '/' + xlsName # "/*.xls"
workbook.save(filePath)
print "Convert xls file in %s" % (filePath)