批处理之家 - Powered by Discuz! Board

/*&cls&@echo off
if "%~1"=="" echo.请拖动要处理的csv文件到本程序图标上&pause&exit
if /i not "%~x1"==".csv" echo.非csv文件!&pause&exit
cd /d "%~dp1"
del "%~n1_new.xls" /f >nul 2>nul
echo.%~1| cscript -E:javascript -Nologo "%~0" &pause&exit
*/
//交换一行数据
function swap(lineNum,maps,sheet){
shell.Exec("cmd /c title 处理第" + lineNum +"行数据.");
var array,tmpStr="",firstCol=null
for(var key in maps){
if(firstCol == null)
firstCol = key;
tmpStr+=sheet.Cells(lineNum,maps[key]).Text == "0" ? "" : sheet.Cells(lineNum,maps[key]).Text;
tmpStr += ",";
}
for(var key in maps){
sheet.Cells(lineNum,maps[key]).Value = tmpStr.split(",")[maps[key]-firstCol];
}
}
//开始
var csvFile = WSH.StdIn.ReadLine();
var eObj = new ActiveXObject("Excel.Application");
var fObj = new ActiveXObject("Scripting.FileSystemObject");
var shell = new ActiveXObject("WScript.Shell");
eObj.Visible = false;
var excelA = eObj.Workbooks.Open(csvFile.replace(/\\/g,"\\\\"));
//复制到xls
WSH.Echo("正在复制csv数据到xls ...\n");
excelA.ActiveSheet.Copy();
excelB = eObj.Workbooks.Item(2);
excelA.Close();
//计算行列数
var totalLine = 0;
var totalCol = 0;
var f = fObj.OpenTextFile(csvFile,1);
while(!f.AtEndOfStream){
var curCol = f.ReadLine().split(",").length;
if(curCol > totalCol)totalCol = curCol;
totalLine += 1;
}
//自定义数据
//*************************************************
var maps={4:4,5:9,6:7,7:6,8:5,9:8,10:10}; //列交换数据 [第几列:换到第几列]
var leftTop = "B5",right = "L",bottom = totalLine; //加边框范围数据 [左上角,右边界,下边界]
//*************************************************
//添加边框
WSH.Echo("正在添加边框 [" + leftTop+ ":" + right + bottom + "] ...\n");
var cells = eObj.Range(leftTop+ ":" + right + bottom);
cells.Borders.LineStyle = 1;
cells.Borders.Weight = 2;
WSH.Echo("正在调整数据...\n");
//交换名称
swap(6,maps,excelB.ActiveSheet);
//交换数字
for(var i=8;i<totalLine;i++)
swap(i,maps,excelB.ActiveSheet);
//生成文件
excelB.SaveAs(fObj.GetParentFolderName(csvFile)+"\\"+fObj.GetBaseName(csvFile)+"_new.xls");
eObj.Quit();
shell.Exec("cmd /c title 处理完成!");
WSH.Echo("完成，输出文件: "+fObj.GetParentFolderName(csvFile)+"\\"+fObj.GetBaseName(csvFile)+"_new.xls");