86355_fr-FR

liliconcom

JMP peut-il prendre en charge le formatage Excel via JSL

Tous,
En parcourant plusieurs messages différents dans la communauté sur le sujet, je crois connaître la réponse à ma question. Cependant, je vais quand même aller de l'avant et résumer le problème et voir si quelqu'un d'autre a emprunté cette voie.

Énoncé du problème : Générer un classeur Excel dans un modèle préalablement accepté et reconnu

Pas pour le débat : Pourquoi Excel est-il le format de sortie choisi

En passant par les posts suivants :

1. https://community.jmp.com/t5/Discussions/Excel-Table-Formatting/mp/33650#M19957
2. https://community.jmp.com/t5/Martin-Demel-s-Blog/JMP-and-Excel-A-Never-ending-story-Part-2-cont/ba-p/35941 (et tous les articles de blog pertinents sur le sujet)
3. https://community.jmp.com/t5/Discovery-Summit-2016/Working-With-Excel-the-Advanced-Edition/ta-p/23969

Naturellement, l'accent est mis sur la manière d'importer et d'exporter des données entre Excel et JMP. Cependant, aucun d'entre eux ne parle vraiment de préservation/personnalisation du format des données exportées depuis JMP et Excel. Que cela nous plaise ou non, Excel existera toujours et il y aura un besoin continu de présenter nos résultats via Excel (au moins, chaque utilisateur ne peut pas avoir de licence JMP).

 

Compte tenu de ce contexte, j'ai un modèle Excel et je voudrais modifier certaines données dans le modèle Excel en utilisant JSL de préférence. Il s'agit de la dernière étape d'une très grande application que j'ai dans JMP. Idéalement, tout ce dont j'ai besoin, c'est que JMP modifie les données du modèle et enregistre le classeur. Malheureusement, comme vous pouvez vous y attendre à la minute où j'ouvre le fichier dans JMP, le formatage tombe à pic et me laisse avec un format brouillé qui n'est pas vraiment présentable à l'utilisateur.

Donc,
1. Existe-t-il un moyen d'utiliser JSL et non VBA pour conserver la mise en forme dans un modèle Excel et modifier les données dans le fichier ?
2. En supposant que la réponse à ma question précédente est "Non", j'ai suivi la voie de l'utilisation du package "XLConnect" dans R. Cependant, cela se heurte également à des problèmes. Je ne m'attends pas à ce que la communauté JMP réponde aux questions sur les raisons pour lesquelles le package R ne donne pas les résultats. Si quelqu'un est intéressé à aider, le code R est fourni ci-dessous.Vous trouverez également ci-joint un exemple de modèle sur lequel j'expérimente

# Clear Console and screen 
cat("\014") # Clear Console 
rm(list = ls(all = TRUE)) # Clear Workspace
options(java.parameters = "- Xmx1024m")

# Load desired packages
suppressPackageStartupMessages(library(XLConnect))

# Define FilePaths 
FolderPath <- "C:\\Users\\Temp\\Desktop"
TemplateFilePath <- paste(FolderPath, "LoanTemplate.xlsx", sep = "\\")
OutputFilePath <- paste(FolderPath, "MyLoan.xlsx", sep = "\\")
OutputFilePath1 <- paste(FolderPath, "MyLoan1.xlsx", sep = "\\")
OutputFilePath2 <- paste(FolderPath, "MyLoan2.xlsx", sep = "\\")

# Read Data 
TemplateData <- readWorksheetFromFile(TemplateFilePath, sheet = 'Loan Schedule', header = T)
MyData <- TemplateData
MyData[[4]][2] <- 10000

# Set Style Action 
setStyleAction(wb, XLC$"STYLE_ACTION.NONE")

# Trial 1
writeWorksheet(OutputFilePath, MyData, 'Loan Schedule', styleAction = XLC$STYLE_ACTION.XLCONNECT, clearSheets = TRUE)

# Trial 2 
writeWorksheetToFile(OutputFilePath1, MyData, 'Loan Schedule', styleAction = XLC$STYLE_ACTION.XLCONNECT, clearSheets = FALSE) 

#Trial 3 
writeWorksheetToFile(OutputFilePath2, MyData, 'Loan Schedule')

 

Re : JMP peut-il prendre en charge le formatage Excel via JSL

@uday_guntupalli il existe une alternative à l'édition XML pour éditer Excel et enregistrer la mise en forme et les fonctions : utilisez les fonctions JMP pour traiter Excel comme une base de données. Je n'ai fait cela qu'avec des feuilles ayant une disposition de base de données, c'est-à-dire que les colonnes sont des champs et que les lignes sont des enregistrements.

 

Je ne suis pas un expert SQL, mais vous pouvez trouver de la documentation sur la mise à jour de nombreux enregistrements de base de données avec une table. Je vais donner un exemple simple.

 

Ci-dessous, une image d'un simple fichier Excel avec deux feuilles et avec une mise en forme.Le fichier s'appelle fournisseurs.xlsx et la feuille à mettre à jour est nommée Fournisseurs . Le livre Compagnon JSL, 2e édition , documente trois méthodes ODBC JMP : Ouvrir la base de données() , Nouvelle requête SQL() et un les auteurs ont nommé une méthode en 3 étapes.

 

Cette méthode peut être utilisée pour exécuter n'importe quelle instruction SQL valide.Vous trouverez ci-dessous le script permettant de modifier un enregistrement à l'aide du SQL MISE À JOUR syntaxe.

 

dbc = create database connection("DSN=Excel Files; DBQ=c:\temp\suppliers.xlsx");

Execute sql(dbc, "SELECT * FROM `suppliers$` WHERE  supplier_name = 'Google' ", "READIT"); //example extraction

Execute sql(dbc, "UPDATE `suppliers$`
   SET supplier_id = 150,
    supplier_name = 'Apple',
    city = 'Cupertino'
   WHERE supplier_name = 'Google'");
   
close database connection(dbc);

 

Before the SQL scriptBefore the SQL scriptAvant le script SQLAfter the SQL Update - red box drawnAfter the SQL Update - red box drawn Après la mise à jour SQL - boîte rouge dessinée

Notez que la mise en forme est inchangée.Je ne peux pas attester des performances d'une mise à jour de base de données avec une autre table, mais les mises à jour du système sont généralement assez rapides. Vous pouvez effectuer une recherche sur le Web pour trouver la syntaxe SQL appropriée. Notez que si vous êtes intéressé par cette méthode et que vous disposez de Microsoft Office 32 bits, il faut quelques étapes pour terminer la configuration unique de la connexion Machine File (ma connexion machine à Excel s'appelle Fichiers Excel ).

 

Ce n'est pas le forum pour discuter de tout cela. Nous (les auteurs) avons écrit un document supplémentaire si vous le demandez.

 

D'après votre dernier message, il semble que vous ayez une méthode R pour faire ce que vous voulez, donc je n'allais pas répondre, mais j'ai décidé pour la fermeture/les alternatives d'ajouter une autre méthode dans le mélange.

Re : JMP peut-il prendre en charge le formatage Excel via JSL

@ian_jmp ,
Dans l'exemple que vous avez partagé, pourriez-vous expliquer comment on pourrait éditer les valeurs en XML en utilisant JSL ?

 

 

Re : JMP peut-il prendre en charge le formatage Excel via JSL

Voici la version multi-feuilles, pour le post précédent. Remarque JMP 12 permet uniquement d'enregistrer un tableau dans un classeur Excel à feuille unique.JMP 13 a activé la fonction Créer un classeur Excel() mais il nécessitait une liste de noms de table JMP, pas les références de table. JMP 14 autorise soit une liste de noms de table ouverts, soit des références de table.

 

Voici comment cela fonctionne :

• enregistrer la pièce jointe fichier multi_tblFmt.txt vers un chemin où vous stockez des programmes que vous utilisez plusieurs fois, renommez-le avec l'extension .vbs
• Si vous utilisez JMP 12 ou une version ultérieure avec wscript (un PC n'est pas sûr d'un MAC), ouvrez le fichier JSL CreateExcelTable_Style.jsl et modifiez les noms de chemin du fichier .vbs et du fichier Excel enregistré (save_path), si nécessaire.

Il est intéressant de noter que le modèle Excel utilisé par JMP pour :

• JMP v12 et JMP v13, TableStyleMedium6 est une couleur Aqua et TableStyleMedium2 est Bleu.
• JMP v14 le TableStyleMedium6 est bleu.  

Modifiez le fichier .vbs avec le Bloc-notes pour spécifier le style que vous préférez. ( Dans Excel, Accueil > Format > Tableau ).

Remarque finale : Le fichier .vbs contient un bloc de code "IF" qui produit un message interactif. Si vous exécutez ce script de manière non interactive, ce bloc if doit être supprimé ou commenté.Voir le commentaire du fichier .vbs.

  

Re : JMP peut-il prendre en charge le formatage Excel via JSL

Cette réponse n'est pas tant pour @uday_guntupalli mais pour le blog au cas où cette discussion aurait des followers qui aimeraient pouvoir enregistrer un tableau JMP dans Excel avec un format plus agréable où les en-têtes de colonnes contiennent des filtres/slicers. La solution fournie ci-dessous ne fonctionne que sur le cas simple de l'enregistrement un Table JMP dans un fichier .xlsx.Le code VBScript aurait besoin de code supplémentaire pour trouver toutes les feuilles et créer un wrapper de boucle for à exécuter sur chaque feuille. Je ne suis pas doué pour VBS et j'ai demandé l'aide de ma meilleure moitié pour la syntaxe. S'il y a un intérêt, postez un commentaire et nous étendrons le VBSCript.

 

Voici comment cela fonctionne :

• enregistrer le fichier joint tblFmt.txt vers un chemin où vous stockez des programmes que vous utilisez plusieurs fois, renommez-le avec l'extension .vbs
• ouvrir le fichier JSL CreateExcelTable_Style.jsl et modifiez les noms de chemin pour .vbs et le fichier Excel enregistré, si nécessaire

Éléments à noter concernant le code vbs :

• Il applique l'Excel intégré TableStyleMedium6 au tableau de la première feuille qui produit des en-têtes bleu foncé, des rangées bleu clair et blanches alternées et des filtres de colonne (slicers).
• La police d'en-tête est remplacée par Arial Black, taille 12, gras
• La table est ajustée automatiquement à la colonne entière. Cela peut ne pas être utile dans tous les cas.

Ce code VBS peut être appelé dans n'importe quel programme JSL en modifiant les noms de chemin dans cet extrait JSL

dt << Save("c:/temp/Biggie Class.xlsx");

//command line syntax: wscript path\tblFmt.vbs 'path to excel workbook'

rp4 = RunProgram(Executable("wscript"),
    Options({"c:\temp\tblFmt.vbs", "\!"c:\temp\biggie class.xlsx\!""}),
    ReadFunction("text")
);

Remarque à l'intention des développeurs JMP ( @ian_jmp ) : J'ai essayé de faire de Book.xltx mon modèle Excel par défaut. Soit je n'ai pas réussi, soit JMP ne l'utilise pas. En cas de succès, mon code vbs aurait pu spécifier un style de tableau personnalisé.UN Liste de souhaits élément, peut être une extension de Create Excel Workbook() pour avoir des arguments facultatifs pour spécifier le fichier de modèle .xltx et le nom TableStyle qui appliquerait un style personnalisé à appliquer.Je me rends compte que cela entre dans les versions d'Excel, le problème de MicrosoftOffice 64 contre 32 bits, etc. Ce n'est pas haut sur ma liste, mais il semble y avoir de fréquentes demandes de tableau Excel.  

Re : JMP peut-il prendre en charge le formatage Excel via JSL

@gzmorgan0 ,
L'utilisation de VBA dans l'exemple simplifié semble raisonnable, mais dans mon énoncé de problème réel, j'aurais besoin de modifier plusieurs valeurs et je préférerais le faire sans VBA. J'ai pu faire fonctionner le code R et j'essaie de voir comment travailler avec @ian_jmp suggestion également. Cependant, merci de m'avoir fourni une autre option. Joyeux noël.

Re : JMP peut-il prendre en charge le formatage Excel via JSL

@uday_guntupalli vos besoins ne sont pas clairs.Si vous avez un modèle comme celui que vous avez fourni qui contient tous les codages et formules de macro et que vous n'avez besoin de modifier que quelques valeurs, vous pouvez apprendre quelques commandes vbscript et utiliser Exécuter le programme (voir ci-dessous).

 

Vous trouverez ci-joint un script qui enregistre le fichier JMP Big Class au format xslx et modifie le format sur une cellule, pour montrer comment vous pouvez le coder à l'aide de JSL.

 

Je ne suis pas un expert en schéma XML, donc l'approche de Ian pourrait être ce dont vous avez besoin.

JSL pour modifier le principal, le nombre de jours, le taux d'intérêt et le paiement supplémentaire.

Names Default to Here(1);

cmdList={"Dim xlApp",

"\[Set xlApp = CreateObject("Excel.Application")]\",
"xlApp.Visible = True",
"\[xlApp.Workbooks.Open ("C:\TEMP\Loan Template.xlsx")]\",
"'Change the values in cells e3-e5, e9", 
"\[xlApp.Range("E3").Select]\",
"\[xlApp.ActiveCell.Value = 10000]\",
"\[xlApp.Range("E4").Select]\",
"\[xlApp.ActiveCell.Value = .044]\",
"\[xlApp.Range("E5").Select]\",
"\[xlApp.ActiveCell.Value = 2]\",
"\[xlApp.Range("E9").Select]\",
"\[xlApp.ActiveCell.Value = 150]\",
"\[xlApp.ActiveWorkbook.SaveAs("C:\TEMP\Loan v2.xlsx")]\",
"xlApp.Quit"
 
};


vbsTxt = concat items(cmdList, "\!N"); //add new lines aka CR 

Save Text File( "c:\temp\jj2.vbs" , vbsTxt);


rp4 = RunProgram(Executable("wscript"),
    Options("c:\temp\jj2.vbs"),
    ReadFunction("text")
);

Caption( "JuMP has Control", Font( "Arial Black" ),  //u is added for spoken voice to say and not spelll JMP.
 Font Size( 16 ),
 Text Color( "red" ), Spoken(1)
);

wait(2);
Caption(Remove);

//look at the Excel file, the color coding is maintained for new payment schedule

 

Re : JMP peut-il prendre en charge le formatage Excel via JSL

Si vous rencontrez des problèmes avec R, vous pouvez envisager une autre route « JMP uniquement ». Je ne sais pas à quel point cela serait réellement viable.

Names Default To Here( 1 );
// .XLSX is a ZIP file
package = Open( "$DESKTOP/Loan Template.xlsx", zip );
// See what's in the package
dirlist = package << dir;
Print(dirlist);
// Get the XML
xml = package << read(dirlist[9]);
// Manipulate XML with JSL?
Print(xml);
// Update the package
package << Write(dirlist[9], xml );