86355_es-ES

liliconcom

¿Puede JMP admitir el formato de Excel a través de JSL?

Todo,
Al revisar varias publicaciones diferentes en la comunidad sobre el tema, creo que sé la respuesta a mi pregunta. Sin embargo, seguiré adelante y resumiré el problema y veré si alguien más ha seguido este camino.

Planteamiento del problema : Genere un libro de trabajo de Excel en una plantilla previamente aceptada y reconocida

No para debate : ¿Por qué Excel es el formato de salida elegido?

Al pasar por las siguientes publicaciones:

1. https://community.jmp.com/t5/Discusiones/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 (y todas las publicaciones de blog relevantes sobre el tema)
3. https://community.jmp.com/t5/Discovery-Summit-2016/Working-With-Excel-the-Advanced-Edition/ta-p/23969

Es comprensible que se preste mucha atención a cómo importar y exportar datos entre Excel y JMP. Sin embargo, ninguno de ellos habla realmente de preservar/personalizar el formato de los datos que se exportan desde JMP y Excel. Nos guste o no, Excel seguirá estando presente y habrá una necesidad continua de presentar nuestros resultados a través de Excel (menos de lo cual es que todos los usuarios no pueden tener una licencia de JMP).

 

Teniendo en cuenta esos antecedentes, tengo una plantilla de Excel y me gustaría editar algunos datos en la plantilla de Excel usando JSL preferiblemente. Este es el último paso de una aplicación realmente grande que tengo en JMP. Idealmente, todo lo que necesito es que JMP edite los datos en la plantilla y guarde el libro de trabajo. Desafortunadamente, como es de esperar, en el momento en que abro el archivo en JMP, el formato falla y me deja con un formato distorsionado que no es realmente presentable para el usuario.

Entonces,
1. ¿Hay alguna manera de usar JSL, no VBA, para conservar el formato en una plantilla de Excel y editar los datos en el archivo?
2. Suponiendo que la respuesta a mi pregunta anterior es "No", he tomado el camino de usar el paquete "XLConnect" en R. Sin embargo, eso también se está metiendo en problemas. No espero que la comunidad de JMP aborde preguntas sobre por qué el paquete R no está dando los resultados. Si alguien está interesado en ayudar, el código R se proporciona a continuación.También se adjunta una plantilla de ejemplo en la que estoy experimentando

# 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: ¿Puede JMP admitir el formato de Excel a través de JSL?

@uday_guntupalli existe una alternativa a la edición de XML para editar Excel y guardar el formato y las funciones: use funciones JMP para tratar Excel como una base de datos. Solo he hecho esto con hojas que tienen diseño de base de datos, es decir, las columnas son campos y las filas son registros.

 

No soy un experto en SQL, pero puede encontrar documentación sobre cómo actualizar muchos registros de bases de datos con una tabla. Daré un ejemplo simple.

 

A continuación se muestra una imagen de un archivo de Excel simple con dos hojas y con formato.el archivo se llama proveedores.xlsx y la hoja a actualizar se llama proveedores . El libro Compañero de JSL, 2.ª edición , documenta tres métodos JMP ODBC: Abrir base de datos() , Nueva consulta SQL() y uno que los autores llamaron un método de 3 pasos.

 

Este método se puede utilizar para ejecutar cualquier instrucción SQL válida.A continuación se muestra el script para cambiar un registro usando el SQL ACTUALIZAR sintaxis.

 

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 scriptAntes del script SQLAfter the SQL Update - red box drawnAfter the SQL Update - red box drawn Después de la actualización de SQL: cuadro rojo dibujado

Tenga en cuenta que el formato no se modifica.No puedo dar fe del rendimiento de una ACTUALIZACIÓN de base de datos con otra tabla, pero las actualizaciones del sistema suelen ser bastante rápidas. Puede realizar una búsqueda en la web de la sintaxis SQL adecuada. Tenga en cuenta que si está interesado en este método y tiene Microsoft Office de 32 bits, se necesitan un par de pasos para completar la configuración única de la conexión del archivo de la máquina (la conexión de mi máquina a Excel se llama Archivos de Excel ).

 

Este no es el foro para discutir todo eso. Nosotros (los autores) escribimos un documento complementario si lo solicita.

 

Desde su última publicación, parece que tiene un método R para hacer lo que quiere, así que no iba a responder, pero decidí cerrar/alternativas para agregar otro método a la mezcla.

Re: ¿Puede JMP admitir el formato de Excel a través de JSL?

@ian_jmp ,
En el ejemplo que ha compartido, ¿podría explicar cómo se pueden editar los valores en XML usando JSL?

 

 

Re: ¿Puede JMP admitir el formato de Excel a través de JSL?

Aquí está la versión de varias hojas, para la publicación anterior. Nota JMP 12 solo permite guardar una tabla en un libro de trabajo de Excel de una sola hoja.JMP 13 habilitó la función Crear libro de Excel() pero requería una lista de nombres de tablas JMP, no las referencias de tablas. JMP 14 permite una lista de nombres de tablas abiertas o referencias de tablas.

 

Así es como funciona:

• guarda el adjunto archivo multi_tblFmt.txt a una ruta donde almacena programas que usa muchas veces, cámbiele el nombre para que tenga la extensión .vbs
• Si está utilizando JMP 12 o posterior con wscript (una PC que no está segura acerca de una MAC), abra el archivo JSL CreateExcelTable_Style.jsl y cambie los nombres de ruta para el archivo .vbs y el archivo de Excel guardado (save_path), si es necesario.

Es interesante notar que la plantilla de Excel que utiliza JMP para:

• JMP v12 y JMP v13, TableStyleMedium6 es un color Aqua y TableStyleMedium2 es azul.
• JMP v14 TableStyleMedium6 es azul.  

Edite el archivo .vbs con el Bloc de notas para especificar el estilo que prefiera. (En Excel, Inicio >Formato>Tabla).

Nota final: el archivo .vbs contiene un bloque de código "IF" que genera un mensaje interactivo. Si ejecuta este script de forma no interactiva, este bloque debe eliminarse o comentarse.Consulte el comentario del archivo .vbs.

  

Re: ¿Puede JMP admitir el formato de Excel a través de JSL?

Esta respuesta no es tanto para @uday_guntupalli pero para el blog en caso de que esta discusión tenga seguidores a quienes les gustaría poder guardar una tabla JMP en Excel con un formato más agradable donde los encabezados de columna contienen filtros/segmentadores. La solución proporcionada a continuación solo funciona en el caso simple de guardar uno Tabla JMP en un archivo .xlsx.El código VBScript necesitaría código adicional para encontrar todas las hojas y crear un contenedor de bucle for para ejecutar en cada hoja. No soy bueno en VBS y pedí ayuda de sintaxis a mi media naranja. Si hay interés, publique un comentario y ampliaremos el VBSScript.

 

Así es como funciona:

• guardar el archivo adjunto tblFmt.txt a una ruta donde almacena programas que usa muchas veces, cámbiele el nombre para que tenga la extensión .vbs
• abre el archivo JSL CreateExcelTable_Style.jsl y cambie los nombres de ruta para .vbs y el archivo de Excel guardado, si es necesario

Elementos a tener en cuenta con respecto al código vbs:

• Aplica el Excel incorporado TableStyleMedium6 a la tabla en la primera hoja que produce encabezados de color azul oscuro, filas alternas de color azul claro y blanco, y filtros de columna (segmentadores).
• La fuente del encabezado se cambia a Arial Black, tamaño 12, negrita
• La tabla se ajusta automáticamente a EntireColumn. Esto podría no ser útil en todos los casos.

Este código VBS se puede llamar en cualquier programa JSL modificando los nombres de ruta en este fragmento de código 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")
);

Nota para los desarrolladores de JMP ( @ian_jmp Traté de hacer Book.xltx mi plantilla de Excel predeterminada. O no tuve éxito, o JMP no lo usa. Si tiene éxito, mi código vbs podría haber especificado un estilo de tabla personalizado.A Lista de deseos elemento, podría ser una extensión de Create Excel Workbook() para tener argumentos opcionales para especificar el archivo de plantilla .xltx y el nombre TableStyle que aplicaría un estilo personalizado.Me doy cuenta de que se mete en las versiones de Excel, el problema de Microsoft Office de 64 vs. 32 bits, etc. Esto no ocupa un lugar destacado en mi lista, pero parece haber solicitudes frecuentes de tablas de Excel.  

Re: ¿Puede JMP admitir el formato de Excel a través de JSL?

@gzmorgan0 ,
El uso de VBA en el ejemplo simplificado parece razonable, sin embargo, en mi declaración del problema real, necesitaría modificar varios valores y preferiría hacerlo sin VBA. Pude hacer funcionar el código R y estoy tratando de ver cómo trabajar con @ian_jmp sugerencia también. Sin embargo, gracias por darme una opción más. Feliz navidad.

Re: ¿Puede JMP admitir el formato de Excel a través de JSL?

@uday_guntupalli sus necesidades no están claras.Si tiene una plantilla como la que proporcionó que tiene todas las fórmulas y la codificación de macros y solo necesita cambiar algunos valores, puede aprender algunos comandos de vbscript y usar Ejecutar programa (consulte a continuación).

 

Se adjunta una secuencia de comandos que guarda el archivo JMP Big Class como xslx y cambia el formato en una celda, para demostrar cómo puede codificar esto usando JSL.

 

No soy un experto en esquemas XML, por lo que el enfoque de Ian podría ser lo que necesita.

JSL para cambiar el Principal, número de días, tasa de interés y pago extra.

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: ¿Puede JMP admitir el formato de Excel a través de JSL?

Si tiene problemas con R, puede considerar una ruta alternativa 'solo JMP'. No estoy seguro de cuán viable sería esto en realidad.

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 );