|
|
发表于 2018-3-26 16:17:03
|
显示全部楼层
145行出错，将楼主的代码略微改动了一下，就可以了：
- import win.ui;
- import fsys.dlg;
- import com.excel;
- //import console;
- /*DSG{{*/
- mainForm = win.form(text="excel拆分";right=600;bottom=400)
- mainForm.add()
- /*}}*/
- import win.wizard;
- wiz = win.wizard( text = "excel拆分向导" )
- wiz.add("选择excel表格").loader = function (hwnd) {
- var form = win.form( hwnd = hwnd);
- form.add(
- edit={cls="edit";left=0;top=0;right=350;bottom=26;edge=1;z=2};
- button={cls="button";text="选择";left=560;top=0;right=650;bottom=26;z=1};
- checkbox={ bottom=75;right=100;left=0;top=60;z=2;text="显示拆分过程";cls="checkbox" }
- static={cls="static";text="注意：勾选显示拆分表格过程，程序操作过程中，不能进行其他操作。";left=0;top=100;right=400;bottom=120;color=255;transparent=1;z=3}
- )
- form.button.oncommand = function(id,event){
- var path = fsys.dlg.open("excel文件|*.xls;*.xlsx||");
- if(#path) form.edit.text = path;
- }
- form.onNext = function(){
- if(!#form.edit.text){
- form.msgboxErr("请先选择文件","操作错误")
- return false;
- }
- }
- return form;
- }
- wiz.add("数据前10行预览").loader = function (hwnd) {
- excel = com.excel();
- excel.Visible = false;
- if(wiz.items[1].checkbox.checked){
- excel.Visible = true;
- }
-
- book = excel.WorkBooks.Open(io.fullpath(wiz.items[1].edit.text));
- sheet = excel.ActiveWorkbook.Sheets(1);
- cols = sheet.UsedRange.Columns.count; //总列数
- rows = sheet.UsedRange.Rows.count; //总行数
-
- r1 = {};
- for(i=1;10;1){
- r1[ i ] = {};
- for(j=1;cols;1){
- r1[ i ][j] = sheet.Cells(i,j).getValue2();
- }
- }
-
- r2 = {};
- for(i=rows;rows-10;-1){ //前10行预览,表中数据小于10行会报错
- r2[ i ] = {};
- for(j=1;cols;1){
- r2[ i ][j] = sheet.Cells(i,j).getValue2();
- }
- }
-
- var form = win.form( hwnd = hwnd);
- form.add(
- static={cls="static";text="请输入标题所占行数:";left=0;top=4;right=150;bottom=26;transparent=1;z=2}
- edit={cls="edit";left=190;top=0;right=230;bottom=20;edge=1;z=2};
- static2={cls="static";text="，请输入拆分依据所在列数:";left=260;top=4;right=440;bottom=26;transparent=1;z=2}
- edit2={cls="edit";left=510;top=0;right=550;bottom=20;edge=1;z=2};
- listview={cls="listview";left=0;top=42;right=720;bottom=248;asel=false;dl=1;bgcolor=00000000;dr=1;edge=1;font=LOGFONT( name='SimSun' );fullRow=1;gridLines=1;msel=false;z=1;hscroll=1};
- )
- form.listview.insertColumn(" ",40,,0x0/*_LVCFMT_LEFT*/)
- for(j=1;cols;1){
- form.listview.insertColumn(tostring(j) ++ "列",100,,0x0/*_LVCFMT_LEFT*/)
- }
- for(i=1;10;1){
- line = {};
- table.push(line,tostring(i) ++ "行");
- for(j=1;cols;1){
- if(r1[ i ][j] == null){
- r1[ i ][j] = " ";
- }
- table.push(line,tostring(r1[ i ][j]));
- }
- form.listview.addItem(line);
- }
-
- form.onNext = function(){
- if(!#form.edit.text or !#form.edit2.text){
- form.msgboxErr("请输入行列数","操作错误")
- return false;
- }
- if(tonumber(form.edit.text) <= 0 or tonumber(form.edit2.text) <= 0){
- form.msgboxErr("大哥别开玩笑了，最起码输入个正数","操作错误")
- return false;
- }
- }
- return form;
- }
- wiz.add("数据最后几行预览").loader = function (hwnd) {
- var form = win.form( hwnd = hwnd);
- form.add(
- static={cls="static";text="请输入注脚所在行数(如：合计行。如果没有不用输入):";left=0;top=4;right=380;bottom=26;transparent=1;z=2}
- edit={cls="edit";left=480;top=0;right=520;bottom=20;edge=1;z=2};
- listview={cls="listview";left=0;top=42;right=720;bottom=248;asel=false;dl=1;bgcolor=00000000;dr=1;edge=1;font=LOGFONT( name='SimSun' );fullRow=1;gridLines=1;msel=false;z=1;hscroll=1};
- )
- form.listview.insertColumn(" ",40,,0x0/*_LVCFMT_LEFT*/)
- for(j=1;cols;1){
- form.listview.insertColumn(tostring(j) ++ "列",100,,0x0/*_LVCFMT_LEFT*/)
- }
- for(i=rows-10;rows;1){
- line = {};
- table.push(line,tostring(i) ++ "行");
- for(j=1;cols;1){
- if(r2[ i ][j] == null){
- r2[ i ][j] = " ";
- }
- table.push(line,tostring(r2[ i ][j]));
- }
- form.listview.addItem(line);
- }
- return form;
- }
- wiz.add("请选择拆分文件保持位置").loader = function (hwnd) {
- var form = win.form( hwnd = hwnd);
- form.add(
- edit={cls="edit";left=0;top=0;right=350;bottom=26;edge=1;z=2};
- button={cls="button";text="选择";left=560;top=0;right=650;bottom=26;z=1};
- static={cls="static";text="如果不选择保存路径，拆分文件默认保存在原文件所在路径。";left=0;top=50;right=380;bottom=80;transparent=1;z=3}
- static2={cls="static";text="点击完成后，请耐心等待，完成后会跳出完成对话框。";left=240;top=280;right=600;bottom=310;transparent=1;z=4}
- )
- form.button.oncommand = function(id,event){
- var path = fsys.dlg.opendir();
- if(#path) form.edit.text = path;
- }
- return form;
- }
- wiz.onFinish = function () {
- var title = tonumber(wiz.items[2].edit.text);
- var fliter = tonumber(wiz.items[2].edit2.text);
- var tail = tonumber(wiz.items[3].edit.text);
-
- if( tail ){
- rowsData = tail-1;
- }
- else { //如果未填写注脚所在行数
- rowsData = rows;
- }
-
- //创建拆分依据列的table
- var r1 = {};
-
- for(i=title+1;rowsData;1){
- k = sheet.Cells(i,fliter).getValue();
- r1[k] = {};
- }
-
- for(i=title+1;rowsData;1){
- k1 = sheet.Cells(i,fliter).getValue();
- for(k2 in r1){
- if(k1 == k2){
- table.push(r1[k2],i);
- }
- }
- }
-
- tpath = io.splitpath(wiz.items[1].edit.text);
-
- for(k in r1){
- var book2 = excel.WorkBooks.Add();
- var sheet2 = excel.ActiveWorkbook.Sheets(1);
- var titlerow = "1:"++ title;
- sheet.UsedRange.Rows(titlerow).copy();
- sheet2.cells(1,1).PasteSpecial(xlPasteColumnWidths);
- var r = title + 1;
- for(name,value in r1[k]){
- sheet.UsedRange.Rows(value).copy();
- sheet2.cells(r,1).PasteSpecial(xlPasteColumnWidths);
- r = r + 1;
- }
-
- if( tail ){
- var tailrow = tail ++ ":" ++ rows;
- sheet.UsedRange.Rows(tailrow).copy();
- sheet2.cells(r,1).PasteSpecial();
- }
- newpath = tpath.dir ++ tpath.name ++ "(" ++ k ++ ").xlsx";
- if(#wiz.items[4].edit.text) newpath = io.fullpath(wiz.items[4].edit.text ++ "" ++ tpath.name ++ "(" ++ k ++ ").xlsx");
- excel.ActiveWorkBook.SaveAs(newpath);
- excel.ActiveWorkBook.close();
- }
-
- excel.ActiveWorkbook.close();
-
- excel.Quit();
- excel = null;
- collectgarbage("collect");
-
- win.msgbox("拆分完成","ok");
- }
- wiz.doModal(mainForm);
- win.loopMessage();
|
|
谢谢分享