private void processExcel2(String filename, String macro) {
try {
//temporary folders --> only for test
Path inputFolder = Files.createDirectories(Paths.get("\\UnitTest\\input"));
Path outputFolder = Files.createDirectories(Paths.get("\\UnitTest\\output"));
File f = new File(filename);
String script = f.getName().replace("xlsm", "vbs");
List<String> command = new ArrayList<>();
command.add("Option Explicit\r\n");
command.add("On Error Resume Next\r\n");
command.add("ExcelMacroIntegration\r\n");
command.add("Sub ExcelMacroIntegration()\r\n");
command.add("Dim exitCode");
command.add("Dim inputFolder");
command.add("Dim outputFolder");
command.add("inputFolder = \"" + inputFolder.toString() + "\"");
command.add("outputFolder = \"" + outputFolder.toString() + "\"");
command.add("Dim xlApp");
command.add("Dim XlBook");
command.add("Set xlApp = CreateObject(\"Excel.Application\")");
command.add("Set xlBook = xlApp.Workbooks.Open(\"" + filename + "\", 0, True)");
command.add("xlApp.Visible = True");
command.add("xlApp.Run \"" + macro + "\", inputFolder, outputFolder, exitCode");
command.add("xlApp.Quit\r\n");
// VBS needs to set excel to null if vbs exit code not zero?
command.add("Set xlBook = Nothing");
command.add("Set xlApp = Nothing\r\n");
command.add("wScript.Quit(exitCode)\r\n");
command.add("End Sub");
Path scriptPath = Paths.get(inputFolder.toString() + File.separator + script);
Files.write(scriptPath, command, StandardCharsets.UTF_8);
// this is the workaround for platform-dependent getRuntime().exec(...)
Process p = Runtime.getRuntime().exec(new String[]{"WScript", scriptPath.toString()});
List<String> result = new ArrayList<>();
p.waitFor();
assertEquals("Windows Scripting Host invocation failed", p.exitValue(), 0);
BufferedReader reader = new BufferedReader(new InputStreamReader(p.getInputStream()));
String line = reader.readLine();
while (line != null) {
System.out.println(line);
line = reader.readLine();
}
} catch (Exception e) {
throw new RuntimeException("VBS invocation failed", e);
}
//delete folders
try {
Files.delete(inputFolder);
} catch (NoSuchFileException x) {
System.err.format("%s: no such" + " file or directory%n", inputFolder);
} catch (DirectoryNotEmptyException x) {
System.err.format("%s not empty%n", inputFolder);
} catch (IOException x) {
// File permission problems are caught here.
System.err.println(x);
}
try {
Files.delete(outputFolder);
} catch (NoSuchFileException x) {
System.err.format("%s: no such" + " file or directory%n", outputFolder);
} catch (DirectoryNotEmptyException x) {
System.err.format("%s not empty%n", outputFolder);
} catch (IOException x) {
// File permission problems are caught here.
System.err.println(x);
}
}
@Test
public void validateExcelTest()
{
//change path for your local repo
processExcel2(localPath, "CreateAfile");
}
}
Mam taki kod ktory nie dziala.
- Generalnie co chce tutaj zmodyfikować to stworzyc tymczasowe foldery input i output a nastepnie je wykasowac po zakonczeniu kodu.
- Jeśli dodaje slash do Files.createDirectories(Paths.get("\UnitTest\input**\"**) manualnie na koncu to java mi obcina i tak ten koncowy slash. A ja potrzebuje przekazac do Excela z dodatkowym slashem.
Jak dodać to Path inputfolder slash na koncu ?
Dziekuje za pomoc,
Jacek