Dicas para baladas e penteados!: BatchFile and JScript to read Excel File

I was asked to create a simple script to read a single Excel (.xlsx) file and create a new table in some specific form. So I created a simple BatchFile (.bat/.cmd) to select the xlsx file and call a JScript to make the new table in .csv.

This code was used to read a spreadsheet of 371x2282 cells and convert it to a comma-separated file of 17040 rows by 4 columns. It took about 15 minutes to do a simples validation e about 20 minutes more do collect the data and format the new file.

Terrible performance, I know. But it make the job done! Maybe I could change the logic a bit to make it faster, I really don't know why it was so slow. Maybe cause ActiveX Office are not a good interface? Fast to develop, heavy to run? Maybe the whole format should be ignored, and be preferred a small pure text format that would be raised fully to memory... over 30 minutes to complete was a really long time!

(Yeah, crei um Batch (.cmd) para converter xlsx em csv usando JScript!)

ActiveEXelBatch.cmd (destaque feito por hilite.me, style emacs)

@echo off & mode 85, 50
@echo.
@echo.
@set "_ext=xlsx"
@echo. Buscando arquivos com a extensao %_ext%.
@echo.
@echo. Este script esta configurado para escolher
@echo. automaticamente um arquivo. No caso o ultimo 
@echo. arquivo em ordem alfabetica no diretorio da
@echo. execucao com a extensao %_ext%. 
@echo. 
@echo. Prefira mover os arquivos do script e o arquivo
@echo. do Excel para uma pasta exclusiva. Este eh o 
@echo. metodo mais seguro. 
@echo. 
@echo. Por favor, feche todas as intancias do EXCEL agora!
@echo. 
@for %%f in (*.%_ext%) do set "_src=%%f"
@echo. Arquivo encontrado: %_src%
@echo. 
@echo. Se o arquivo encontrado for o arquivo esperado,
@echo. apenas de enter na pergunta abaixo. Caso 
@echo. contrario, digite o nome completo com extensao.
@echo. 
@set /P _src=Outro Arquivo? Qual : 
@echo. 
@echo. 
@echo. O script possui dois passos: validacao e conversao.
@echo. Cada passo demorou por volta de 15 min em meu note.
@echo. Minha CPU: Intel Core i3-380M, 2.5Ghz (1st gen).
@echo.
@echo. Caso o arquivo nao passe na validacao, o script
@echo. sera encerrado, mostrando uma lista de erros.
@echo. 
@echo. A validacao eh absurdamente simples e so procura
@echo. por valores diferentes de 0 e 1 pela tabela.
@echo. 
@echo. Caso o arquivo passe na validacao a conversao eh
@echo. feita para um arquivo de texto puro em formato.csv.
@echo. 
@echo. Arquivos .csv podem facilmente serem importados
@echo. pelo Excel. Porem, nao sao indicados para arquivos
@echo. que contenham ";" (ponto e virgula) no interior de
@echo. suas celulas pois estes sao utilizados como 
@echo. separador tabular dentro do Excel. 
@echo. 
@echo. Por favor, feche todas as intancias do EXCEL agora!
@echo. Por favor, aguarde. Deve demorar de 30 a 45 min.
@echo.    (ou seja umas duas musicas do Pink Floyd!)
@echo. 
@echo. 
@cscript.exe /nologo ActivExel.js "%~dp0" "%_src%"
@echo.
@echo.
@echo. Forcando o fechamento do Excel caso nao tenha
@echo. sido fechado durante a execucao do scrpit. E,
@echo. por fim, fechando o script em 10 segundos!
@echo.
@echo. Talvez me encontre em:
@echo.      https://ofernandofilo.blogspot.com
@echo. 
@echo. Se me encontrar, sinta-se livre para tirar duvidas.
@echo. Ou mesmo pedir simples alteracoes no script. Nunca
@echo. fui programador. Mas para coisas pequenas e simples
@echo. talvez eu seja capaz de ajudar!
@echo.
@echo.
@taskkill /IM excel.exe /t /f
@echo.
@timeout /nobreak /t 10
@echo.
@exit /b
:: Sources:
:: https://ss64.com/nt/syntax.html
:: https://www.dostips.com
:: http://www.robvanderwoude.com/batchfiles.php
:: http://www.commandlinefu.com/commands/browse
:: https://technet.microsoft.com/en-us//library/bb490890.aspx
:: https://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/batch.mspx?mfr=true
:: https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-R2-and-2012/cc754340(v%3dws.11)
:: http://steve-jansen.github.io/guides/windows-batch-scripting/
:: https://www.tutorialspoint.com/batch_script/index.htm
:: https://www.bleepingcomputer.com/tutorials/windows-command-prompt-introduction/
:: http://archive.fo/XXRbo
:: https://www.computerhope.com/batch.htm
:: https://www.csie.ntu.edu.tw/~r92092/ref/win32/win32scripting.html
:: https://commandwindows.com
::
:: Tools:
:: https://docs.microsoft.com/en-us/sysinternals/downloads/sysinternals-suite
:: http://www.nirsoft.net/utils/index.html#commandline_utils
:: http://www.joeware.net/freetools/index.htm
:: https://conemu.github.io
:: https://ipmsg.org/tools/fastcopy.html.en
:: https://eternallybored.org/misc/netcat/
:: https://joncraton.org/blog/46/netcat-for-windows/
:: https://www.runtime.org/shadow-copy.htm
:: http://www.pc-tools.net/win32/
:: http://www.horstmuc.de/wcon.htm
:: https://www.cygwin.com
:: http://www.mingw.org
:: http://www.westmesatech.com/index.html
:: http://www.netikka.net/tsneti/info/tscmd.php
:: http://gnuwin32.sourceforge.net
:: https://sourceforge.net/projects/unxutils/
:: https://github.com/bmatzelle/gow/releases
:: https://www.gnu.org/software/m68hc11/m68hc11_tools_win.html
:: http://ss64.net/westlake/
:: https://www.pdflabs.com/tools/pdftk-server/
:: https://www.imagemagick.org/script/command-line-tools.php

ActivEXel.js (destaque feito por hilite.me, style emacs)

/**
 * To Run:
 *   cscript.exe /nologo ActivEXel.js "diretorio-raiz" "arquivo.ext"
 *   cscript.exe /nologo ActivEXel.js "%~dp0" "%_src%"
 *   wscript.exe ActivEXel.js "diretorio-raiz" "arquivo.ext"
 *
 * Sobre:
 *     Infelizmente, não posso produzir um código muito inteligente. 
 *   E portanto uma das exigências do código é que a planinha seja 
 *   populada à partir de A1. Como já é o arquivo modelo. E também 
 *   espera-se que a planinha esteja corretamente preenchida em todas
 *   as colunas com valores coerentes. Número onde é preciso ser 
 *   número e texto onde precisa ser texto. Espero por fim que os 
 *   números presentes sejam apenas 0 ou 1.
 *
 *     Qualquer mudança a este padrão tenderá a produzir um comportamento 
 *   anômalo do script.
 *
 * Licença: 
 *    Não há. Pode-se fazer qualquer coisa com ele, sem consulta prévia.
 *
 *    Tudo o que escrevo e digo não pertence 
 *     mais a mim depois de escrito e dito.
 */

// Entrada dos argumentos
var i = 0;
var j = 0;
var args = WScript.Arguments;
var fsobjetc = new ActiveXObject("Scripting.FileSystemObject");
var excelfile = fsobjetc.GetAbsolutePathName(WScript.Arguments(0)+WScript.Arguments(1));

// Configurações de Pesquisa Padrão
var stringout = "";
var excelsheetname = "";
var excelsheetnumber = 1;
var excelsheetrange = "A1";
var excel = new ActiveXObject("Excel.Application"); 
var book = excel.Workbooks.Open(excelfile);
var sheetnumber = book.Sheets.Item(excelsheetnumber); 
var rowCount = sheetnumber.UsedRange.Rows.Count;
var colCount = sheetnumber.UsedRange.Columns.Count;
var value = sheetnumber.Range(excelsheetrange);

// Listagem das Informações Recebidas:
WSH.echo("BREVE RESUMO DAS INFORMACOES RECEBIDAS");
WSH.echo("Num de Parametros: "+args.length + " (o esperado sao 2)");
for (i = 0; i < args.length; i++) {
  WSH.echo("Parametros / Args: "+args.Item(i));   
}
WSH.echo("Arquivo do Excel.: "+excelfile);
WSH.echo("Versao Excel Ins.: "+excel.Version);
WSH.echo("Celula A1 p/teste: "+value);
WSH.echo("Qtd. de Linhas...: "+rowCount);
WSH.echo("Qtd. de Colunas..: "+colCount);

// Simples Validação da Tabela
stringout = "";
for(i = 2; i <= rowCount; i++){
  for(j = 3; j <= colCount; j++){
    if ( (sheetnumber.Cells(i, j).Value != "0") && (sheetnumber.Cells(i, j).Value != "1")) {
      stringout = stringout + "("+ sheetnumber.Cells(i,1).Value + ", ";
      stringout = stringout + sheetnumber.Cells(i,2).Value + ", ";
      stringout = stringout + sheetnumber.Cells(1,j).Value + ", ";
      stringout = stringout + sheetnumber.Cells(i,j).Value + ")\n";
      stringout = stringout + "Linha: " + i + ", Coluna: " + j;
      stringout = stringout + ", Valor: " +sheetnumber.Cells(i, j).Value + "\n\n";
    }
  }
}

if (stringout != ""){
  WSH.echo("Listagem de Erros: \n\n"+stringout);
  // Se erros forem encontrados, a string estará preenchida,
  // o script fechará, informando os erros.
  book.Close();
  excel.Application.Quit();
  excel.Quit();
  excel = null;
  WScript.Sleep(100);
  CollectGarbage();
  WScript.Echo("\n Erros foram encontrados!\n");
  WScript.Echo(" Por favor, corrija-os e execute o script novamente!\n");
  WScript.Echo(" Tecle enter para fechar este script. ");
  WScript.StdIn.ReadLine();
  WScript.Quit();
}

// Início do Código
WSH.echo("Listagem de Erros: Nenhum Erro Simples!\n\n");
WSH.echo("Por favor, aguarde pelo menos mais 20 min...\n\n");
stringout = "";
stringout = "Ordem num;Família;Espécies;Local\n";
var ordem = 1;
for(i = 2; i <= rowCount; i++){
  for(j = 3; j <= colCount; j++){
    if ( sheetnumber.Cells(i, j).Value == "1") {
      stringout = stringout + ordem + ";" + sheetnumber.Cells(i,1).Value + ";";
      stringout = stringout + sheetnumber.Cells(i,2).Value + ";";
      stringout = stringout + sheetnumber.Cells(1,j).Value + "\n";
      ordem++;
    }
  }
}
var fso = new ActiveXObject("Scripting.FileSystemObject");
var filecsv = fso.CreateTextFile(WScript.Arguments(0)+"ActivEXel.csv", true);
filecsv.WriteLine(stringout);
filecsv.Close();
WSH.echo("Conversao realizada! Arquivo gerado: ActivEXel.csv\n\n");

// Rotina de Fechamento do Arquivo
book.Close();
excel.Application.Quit();
excel.Quit();
excel = null;
WScript.Sleep(100);
CollectGarbage();
WScript.Echo(" Tecle enter para fechar este script. ");
WScript.StdIn.ReadLine();
WScript.Quit();
// Nos meus testes, excel.exe só fecha após a conclusão do script.
// E nunca através de qualquer outro comando. 

/**
 * Source: Batch File to read excel
 * https://www.dostips.com/forum/viewtopic.php?t=4628
 *
 * Source: Help removing date in strings from workbooks using jscript
 * https://www.reddit.com/r/excel/comments/38j33q/help_removing_date_in_strings_from_workbooks/
 *
 * Source: BUG: Excel Does Not Shut Down After Calling the Quit Method When Automating from JScript
 * https://support.microsoft.com/en-us/help/266088/bug-excel-does-not-shut-down-after-calling-the-quit-method-when-automa
 *
 * Source: FileSystemObject Object
 * https://msdn.microsoft.com/en-us/library/z9ty6h50(v=vs.84).aspx
 *
 * Using JavaScript to Open Excel and Word Files in HTML
 * http://www.kavoir.com/2009/01/using-javascript-to-open-excel-and-word-files-in-html.html
 *
 * Run method for excel macro in javascript
 * https://www.codeproject.com/Questions/168103/Run-method-for-excel-macro-in-javascript
 *
 * To run scripts using the command-line-based script host (Cscript.exe)
 * https://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/wsh_runfromcommandprompt.mspx?mfr=true
 *
 * To run scripts using the Windows-based script host (Wscript.exe)
 * https://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/wsh_runfromwindowsbasedhost.mspx?mfr=true
 *
 * Difference between wscript and cscript
 * https://stackoverflow.com/questions/8678441/difference-between-wscript-and-cscript
 *
 * Capturing output from WshShell.Exec using Windows Script Host
 * https://stackoverflow.com/questions/2075886/capturing-output-from-wshshell-exec-using-windows-script-host/
 *
 * JScript User's Guide (Windows Scripting - JScript)
 * https://msdn.microsoft.com/en-us/library/4yyeyb0a%28v=vs.84%29.aspx
 *
 * JScript and VBScript
 * https://msdn.microsoft.com/library/d1et7k7c%28v=vs.84%29.aspx
 *
 * WSH 5.6 Documentation
 * http://download.microsoft.com/download/winscript56/Install/5.6/W982KMeXP/EN-US/scrdoc56en.exe
 * 
 * Reference (Windows Script Host)
 * https://msdn.microsoft.com/library/98591fh7.aspx
 * 
 * JScript JScript User's Guide
 * http://soliton.ae.gatech.edu/classes/ae6382/documents/MS_scripting/JScript.pdf
 * 
 * JScript Tutorial - of Peter Gottlieb
 * http://www.petergottlieb.com/docs/spect/DOASIS/jscript_tutorial.pdf
 * 
 * Excel 2010 Developer Reference
 * https://msdn.microsoft.com/en-us/library/office/ff846392(v=office.14).aspx 
 * 
 * Learn About Scripting for HTML Applications (HTAs)
 * https://technet.microsoft.com/en-us/scriptcenter/dd742317.aspx
 * 
 * HTML Applications
 * https://msdn.microsoft.com/en-us/library/ms536471.aspx
 * 
 * Microsoft Script Center
 * https://technet.microsoft.com/en-us/scriptcenter/default.aspx
 * 
 * Hey, Scripting Guy! Blog 
 * https://blogs.technet.microsoft.com/heyscriptingguy/
 *
 * TechNet Magazine: Hey, Scripting Guy!
 * https://technet.microsoft.com/en-us/library/cc135880.aspx
 *
 * DevGuru Windows Script Host Quick Reference Guide
 * http://www.devguru.com/content/technologies/wsh/home.html
 *
 * Windows Batch Scripting
 * https://en.wikibooks.org/wiki/Windows_Batch_Scripting
 *
 * Simple HTA Template to Run Utility on File
 * https://helloacm.com/simple-hta-template-to-run-utility-on-file/
 *
 * Which is Faster? VBScript or JScript on WSH – A Performance Comparison using Sieve of Eratosthenes
 * https://codingforspeed.com/which-is-faster-vbscript-or-jscript-on-wsh-a-performance-comparison-using-sieve-of-eratosthenes/
 *
 * Microsoft Script Center
 * https://technet.microsoft.com/en-us/scriptcenter/default.aspx
 *
 */

/**
  var ExcelApp = new ActiveXObject("Excel.Application");  
  var ExcelSheet = new ActiveXObject("Excel.Sheet"); 

  // Make Excel visible through the Application object.  
  ExcelSheet.Application.Visible = true;  

  // Place some text in the first cell of the sheet.  
  ExcelSheet.ActiveSheet.Cells(1,1).Value = "This is column A, row 1";  

  // Save the sheet.  
  ExcelSheet.SaveAs("C:\\TEST.XLS");  

  // Close Excel with the Quit method on the Application object.  
  ExcelSheet.Application.Quit(); 

  function GetAppVersion() {
    var XLApp = new ActiveXObject("Excel.Application", "MyServer");
    return(XLApp.Version);
  }  
  see: https://docs.microsoft.com/pt-br/scripting/javascript/reference/activexobject-object-javascript 
  see: https://msdn.microsoft.com/en-us/library/7sw4ddf8(v=vs.84).aspx
**/
/**
  <!-- MyJob.wsf -->
  <job id="IncludeExample">
    <script language="JScript" src="MyLib1.js"/>
    <script language="JScript" src="MyLib2.js"/>
    <script language="JScript">
      WScript.Echo(myLib1.foo());
      WScript.Echo(myLib2.bar());
    </script>
  </job>
  see: https://stackoverflow.com/questions/3054321/how-to-reference-a-jscript-file-from-another-one
**/

/**
  function removeDate(){
    var xl = Sys.OleObject("Excel.Application"); // Application
    var wb = xl.Workbook.Open("C:\temp\test.xlsx"); // Workbook
    var ws = wb.Worksheets("yourSheetName"); // Worksheet
    var rowCount = ws.UsedRange.Rows.Count; // row count
    var colCount = ws.UsedRange.Columns.Count; // column count

    // loop through every cell in used range
    for(i = 1; rowCount; i++){
      for(j = 1; colCount; j++{
        // if cell isn't blank
        if(ws.Cells(i, j).Value != ""){
            // insert regex here to find date if present
            // replace if found
        }
      }
    }
  }
  see: https://www.reddit.com/r/excel/comments/38j33q/help_removing_date_in_strings_from_workbooks/
**/
 
/**
  var ExcelApp = new ActiveXObject("Excel.Application");
  var ExcelSheet = new ActiveXObject("Excel.Sheet");

  // Make Excel visible through the Application object.
  ExcelSheet.Application.Visible = true;

  // Place some text in the first cell of the sheet.
  ExcelSheet.ActiveSheet.Cells(1,1).Value = "This is column A, row 1";
  WScript.Sleep(15000);

  // Close Excel with the Quit method on the Application object.
  ExcelSheet.Application.Quit(); 
  
  see: https://msdn.microsoft.com/en-us/library/7sw4ddf8(v=vs.84).aspx
**/

/**
  var excel=new ActiveXObject("Excel.Application"); 
  //excel.Visible = true;
  //var book=excel.Workbooks.Open("modelo.xlsx"); 
  var book=excel.Workbooks.Open("C:\\Users\\Fernando\\Desktop\\Tabular\\modelo.xlsx"); 
  var sheet=book.Sheets.Item(1); 
  var value=sheet.Range("A1");
  WSH.echo(value);
**/

/**
  var folder=argumentos.Item(0);
  var file=argumentos.Item(1);
  WSH.echo("Add.P.: "+folder+file);
**/

/**
  for(i = 1; i <= 3; i++){
    for(j = 1; j <= 3; j++){
      stringout = stringout + " " +sheetnumber.Cells(i, j).Value;
    }
    WSH.echo("Achei: "+stringout);
    stringout = "";
  }
**/

/** 
  var sheetname=book.Worksheets(excelsheetname);
**/

/**
  WSH.echo("Tabela.: \n\n"+stringout);
**/

Download both codes:
https://drive.google.com/open?id=1if3QZATT_FJ-yR2GJTtf1SmNfEDcbPrc

Some resources for BatchScripting:
https://ss64.com/nt/
https://www.dostips.com
http://www.robvanderwoude.com/batchfiles.php
https://en.wikibooks.org/wiki/Windows_Batch_Scripting

Some resources for JScript:
JScript User's Guide (Windows Scripting - JScript)
JScript Tutorial - of Peter Gottlieb
JScript JScript User's Guide
WSH 5.6 Documentation

Bonus: CodingForSpeed!
Which is Faster? VBScript or JScript on WSH

cheers

Dicas para baladas e penteados!

2018-01-20

BatchFile and JScript to read Excel File

Nenhum comentário:

Postar um comentário