这是一个拆分excel发邮件的半成品,因为换API方式所以记录下。
Gui, main:Add, button, x0 y0 w100 h20 gaddtab, 添加表格数据源 gui, main:add, Button, x100 y0 w100 h20, 设置收件人信息 gui, main:add, Button, x200 y0 w100 h20, 设置发件人信息 gui, main:add, Button, x300 y0 w100 h20, 开始发送 gui, main:add, Progress, x0 y20 w600 h20 vprg, 10 Gui, main:Add, ListView, x0 y40 w600 h300 vlv, 文件|Sheet|拆分依据字段|输出文件名前缀|目标Sheet Gui, main:Add, StatusBar, , 等待用户操作 Gui, main:show, , Excel数据拆分发邮件 Gui, main:Default LV_ModifyCol(1,150) LV_ModifyCol(2,120) LV_ModifyCol(3,120) LV_ModifyCol(4,120) LV_ModifyCol(5,120) SB_SetParts(400,200) return addtab: FileSelectFile, file, , , 选择一个表格, Excel文件(*.xls;*.xlsx) IfExist % file { SplitPath, file, , , , name SB_SetText("读取文件:" file) a := new excel() a.open(file) sheet := a.sheets() for k,v in sheet { SB_SetText("等待用户设置数据源") isguideok := false Gui, guide:Destroy Gui, guide:add, text, x0 y0 w100 h20, % "文件:" Gui, guide:add, text, x0 y20 w100 h20, % "Sheet:" Gui, guide:add, text, x100 y0 w300 h20, % guidefile:=file Gui, guide:add, text, x100 y20 w300 h20, % guidesheet:=v f = field := a.fields(A_index) for x,y in field f .= f ? "|" y : y Gui, guide:add, text, x0 y40 w100 h20, 拆分依据字段: Gui, guide:add, DropDownList, x100 y40 w300 vguideddl, % f Gui, guide:add, text, x0 y60 w100 h20, 输出文件名前缀: Gui, guide:add, Edit, x100 y60 w300 h20 vguideprefix, % name Gui, guide:add, text, x0 y80 w100 h20, 目标Sheet: Gui, guide:add, Edit, x100 y80 w300 h20 vguidedstsheet, % v Gui, guide:add, Button, x0 y100 w200 h20 gconfirm, 确认 Gui, guide:add, Button, x200 y100 w200 h20 gjump, 跳过 Gui, guide:Show, , % "数据源导入向导——" name while(!isguideok) Sleep, 100 Gui, guide:Destroy SB_SetText("数据源设置完毕") } a.close() } return confirm: Gui, guide:Submit, NoHide if !guideddl { MsgBox, 4112, 错误, 拆分依据字段不能为空! return } Gui, main:Default LV_Add("",guidefile,guidesheet,guideddl,guideprefix,guidedstsheet) isguideok := true return jump: isguideok := true return guideGuiClose: return mainGuiClose: ExitApp class excel { ;static conn __New() ;新建 { this.conn:= ComObjCreate("Excel.Application") this.conn.Visible := false ;false } open(file) ;打开文件 { IfExist % file this.conn.Workbooks.Open(file) } close() ;关闭文件 { this.conn.Workbooks.close() } sheets() ;获取所有Sheet { s := [] loop % this.conn.ActiveWorkbook.Sheets.Count s.insert(this.conn.ActiveWorkbook.Sheets(A_index).Name) return s } fields(sheet) ;获取指定sheet的字段 sheet为id或者具体名称 { c := [] loop % this.conn.ActiveWorkbook.Sheets(sheet).Columns.Count { try { x := this.conn.ActiveWorkbook.Sheets(sheet).Cells(1,A_index).Value if !x break c.insert(x) } catch e break } return c /* col_id := ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"] loop % this.conn.ActiveWorkbook.Sheets(sheet).Columns.Count { col := col_id[floor(A_index/26)] col_id[mod(A_index,26)] try { x:=excel.ActiveWorkbook.Sheets(sheet).Range(col "1").Value if !x break colname.insert(x) } catch e break } */ } setfields(sheet,fields) ;设置字段 即第一列信息 { return this.insertrow(sheet,1,fields) } setformat(sheet,range,format) ;设置某一区域的单元格格式 { this.conn.ActiveWorkbook.Sheets(sheet).Range(range).NumberFormat := format } insertrow(sheet,rowid,values) ;插入一列 { for k,v in values this.conn.ActiveWorkbook.Sheets(sheet).Cells(rowid,A_index).Value := v } }
60 queries in 2.389 seconds |