[文本处理] 关于批处理操作EXCLE的一些命令。

/*&cls
@echo off
cd /d "%~dp0"
where *.xls *.xlsx 2>nul|cscript -nologo -e:jscript "%~f0"
pause
exit
*/
var fso=new ActiveXObject('Scripting.FileSystemObject');
var excel=null;
try{
    excel=new ActiveXObject('Excel.Application');
}catch(e){WSH.echo('MS Excel is not installed');WSH.Quit();}
excel.DisplayAlerts=false;
excel.Visible=true;
while(!WSH.StdIn.AtEndOfStream){
    var line=WSH.StdIn.ReadLine();
    var xlsfile=fso.GetFile(line);
    if(xlsfile.Name.substring(0,2) != '~$'){
        WSH.echo(line);
        var wbook=excel.Workbooks.Open(xlsfile.Path);
        var wsheet=wbook.Worksheets(1);
        wsheet.Columns('B').Insert(-4159);
        wsheet.Range('B1').Value='时间';
        wsheet.Range('B2').Value=wsheet.Range('A2').Value;
        wsheet.Range('A2').Value=wsheet.Range('A1').Value;
        wsheet.Range('A1').Value='';
        wsheet.Range('I1').Value='账上余额';
        wbook.Save();
        wbook.Close();
    }
}
excel.Quit();
WSH.Quit();
复制代码

#&cls&@powershell -c "Get-Content '%~0' | Select-Object -Skip 1 | Out-String | Invoke-Expression" & pause&exit

$src_dir = './'; #源目录

$files = Get-ChildItem -Path './' -Filter '*.xls' -Recurse -File
foreach($file in $files)
{
    $xl=New-Object -ComObject Excel.Application
    $xl.Visible = $false
    $wb=$xl.workbooks.open($file.FullName)
    $ws = $wb.worksheets.Item(1)
    $ColumnSelect = $ws.Columns("B:B")
    $ColumnSelect.Insert()
    $ws.Cells.Item(1,2) ='时间'
    $ws.Cells.Item(2,2) =$ws.Cells.Item(2,1)
    $ws.Cells.Item(2,1) = $ws.Cells.Item(1,1)
    $ws.Cells.Item(1,1)=$null
    $ws.Cells.Item(1,9) = '账上余额'
    $wb.Save()
    $xl.Quit()
    while([System.Runtime.Interopservices.Marshal]::ReleaseComObject([System.__ComObject]$xl)){'released'| Out-Null}
}
复制代码