;Thinkai@2015-10-27
;本脚本需要安装office 2007 如果需要在仅有2003条件下运行，请替换323行注释掉的连接字串并替换.xlsx后缀
Gui, main:Add, button, x0 y0 w100 h20 gaddtab, 添加表格数据源
gui, main:add, Button, x100 y0 w100 h20 gsetto, 设置收件人信息
gui, main:add, Button, x200 y0 w100 h20 gsetfrom, 设置发件人信息
gui, main:add, Button, x300 y0 w100 h20 gsend, 开始发送
gui, main:add, Button, x400 y0 w100 h20 grl, 重新开始
;gui, main:add, text, x0 y20 w100 h20, 发件人(可选)
;gui, main:add, Edit, x100 y20 w500 h20 vfrom
gui, main:add, text, x0 y40 w100 h20, 标题前缀
gui, main:add, Edit, x100 y40 w500 h20 vtitle
Gui, main:add,text, x0 y60 w600 h20, 正文 可用插入[delimiter]替换拆分字段信息
gui, main:add, Edit, x0 y80 w600 h100 vcontent
gui, main:add, Progress, x0 y180 w600 h20 vprg,
Gui, main:Add, ListView, x0 y200 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)
source := [] ;数据源数组
mailconfig:=[] ;发件人信息数组
IfExist config.ini ;读取发件人配置
{
IniRead, setfrom_from, config.ini, from, from
IniRead, setfrom_bcc, config.ini, from, bcc
IniRead, setfrom_smtp, config.ini, from, smtp
IniRead, setfrom_account, config.ini, from, account
IniRead, setfrom_pass, config.ini, from, pass
mailconfig.from:=setfrom_from,mailconfig.bcc:=setfrom_bcc,mailconfig.smtp:=setfrom_smtp,mailconfig.account:=setfrom_account,mailconfig.pass:=setfrom_pass
}
to_obj := []
IfExist, to.txt ;读取收件人名录
{
FileRead, str, to.txt
to_obj := getto(str)
}
return
addtab: ;添加数据源
FileSelectFile, file, , , 选择一个表格, Excel文件(*.xls;*.xlsx)
IfExist % file
{
SplitPath, file, , , , name ;获取文件名
source.insert({"name":name,"file":file,"sheets":{}})
SB_SetText("读取文件:" file)
idx := source.maxindex()
source[idx].db := new exceldb() ;创建excel adodb连接，获取数据表信息
source[idx].db.open(file)
SB_SetText("读取文件:" file " 完毕！")
sheet := source[idx].db.GetTableInfo()
for k,v in sheet ;弹出若干个向导页设置数据源
{
guide_fields := v
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, % guide_file:=file
Gui, guide:add, text, x100 y20 w300 h20, % guide_sheet:=k
f :="" ;,guide_sql:="CREATE TABLE [" k "] (" ;创建表语句 已废弃
for x,y in v
{
f .= f ? "|" y : y
;guide_sql .= y " TEXT,"
}
;StringTrimRight, guide_sql, guide_sql, 1
;guide_sql .= ")"
Gui, guide:add, text, x0 y40 w100 h20, 拆分依据字段：
Gui, guide:add, DropDownList, x100 y40 w300 vguide_ddl, % f
Gui, guide:add, text, x0 y60 w100 h20, 输出文件名前缀：
Gui, guide:add, Edit, x100 y60 w300 h20 vguide_prefix, % name
Gui, guide:add, text, x0 y80 w100 h20, 目标Sheet：
Gui, guide:add, Edit, x100 y80 w300 h20 vguide_dstsheet, % k
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("数据源设置完毕")
}
;source[idx].db.close() ;不关闭连接。特别是对于一些大的07文件，在后续拆分情况下避免消耗无谓时间
}
return
rl: ;重载
Reload
confirm: ;向导确认
Gui, guide:Submit, NoHide
if !guide_ddl
{
MsgBox, 4112, 错误, 拆分依据字段不能为空！
return
}
Gui, main:Default
LV_Add("",guide_file,guide_sheet,guide_ddl,guide_prefix,guide_dstsheet)
source[idx]["sheets"].insert({"fields":guide_fields,"sheet":guide_sheet,"delimiter":guide_ddl,"prefix":guide_prefix,"dstsheet":guide_dstsheet}) ;,"createsql":guide_sql
isguideok := true
return
jump: ;向导跳过
isguideok := true
return
setto: ;设置收件人
IfExist, to.txt
FileRead, to, to.txt
Gui, setto:Destroy
Gui, setto:Add, text, x0 y0 w600 h40, 在下方修改联系人信息。可从excel中粘贴过来，左边是拆分依据字段，右边联系人邮箱，多个用`"`,`"分隔`n例如：某某部门 xxx`,`"xxx`" (制表符分隔)
Gui, setto:Add, Edit, x0 y40 w600 h400 vsetto_to, % to
Gui, setto:Add, Button, x0 y440 w600 h20 gsetto_ok, 确定
Gui, setto:Show, , 设置收件人信息
return
setto_ok: ;设置收件人完毕
GuiControlGet, setto_to
FileDelete, to.txt
FileAppend, % setto_to, to.txt
to_obj := getto(setto_to)
Gui, setto:Destroy
return
setfrom: ;设置发件人
IfExist config.ini
{
IniRead, setfrom_from, config.ini, from, from
IniRead, setfrom_bcc, config.ini, from, bcc
IniRead, setfrom_smtp, config.ini, from, smtp
IniRead, setfrom_account, config.ini, from, account
IniRead, setfrom_pass, config.ini, from, pass
}
Gui, setfrom:Destroy
Gui, setfrom:add, text, x0 y0 w100 h20, 默认发件人*
Gui, setfrom:add, Edit, x100 y0 w300 h20 vsetfrom_from, % setfrom_from ? setfrom_from : "某某"
Gui, setfrom:add, text, x0 y20 w100 h20, 默认暗送人
Gui, setfrom:add, Edit, x100 y20 w300 h20 vsetfrom_bcc, % setfrom_bcc ? setfrom_bcc : "某某"
Gui, setfrom:add, text, x0 y40 w100 h20, 发件邮箱smtp*
Gui, setfrom:add, Edit, x100 y40 w300 h20 vsetfrom_smtp, % setfrom_smtp ? setfrom_smtp : "smtp.xxx.com"
Gui, setfrom:add, text, x0 y60 w100 h20, 发件邮箱账号*
Gui, setfrom:add, Edit, x100 y60 w300 h20 vsetfrom_account, % setfrom_account ? setfrom_account : "xxx@xxx.com"
Gui, setfrom:add, text, x0 y80 w100 h20, 发件邮箱密码*
Gui, setfrom:add, Edit, x100 y80 w300 h20 vsetfrom_pass, % setfrom_pass
Gui, setfrom:add, Button ,x0 y100 w400 h20 gsetfrom_ok, 确定
Gui, setfrom:Show, , 设置发件人信息
return
setfrom_ok: ;设置发件人完毕
Gui, setfrom:Submit, NoHide
Gui, setfrom:Destroy
IniWrite, % setfrom_from, config.ini, from, from
IniWrite, % setfrom_bcc, config.ini, from, bcc
IniWrite, % setfrom_smtp, config.ini, from, smtp
IniWrite, % setfrom_account, config.ini, from, account
IniWrite, % setfrom_pass, config.ini, from, pass
mailconfig.from:=setfrom_from,mailconfig.bcc:=setfrom_bcc,mailconfig.smtp:=setfrom_smtp,mailconfig.account:=setfrom_account,mailconfig.pass:=setfrom_pass
return
send: ;开始拆分发送
Gui, main:Submit, NoHide
SB_SetText("检查发件人信息")
if ((!mailconfig.from && !from) || !mailconfig.smtp || !mailconfig.account || !mailconfig.pass)
{
MsgBox, 4112, 错误, 发件人信息未配置完整，请设置！
return
}
GuiControl, main:, prg, 2
SB_SetText("检查收件人信息")
if (to_obj.maxindex()=0)
{
MsgBox, 4112, 错误, 收件人信息未配置，请设置！
return
}
GuiControl, main:, prg, 3
SB_SetText("检查标题信息")
if !title
{
MsgBox, 4112, 错误, 标题未填写，请填写！
return
}
GuiControl, main:, prg, 4
SB_SetText("创建临时目录")
FileCreateDir, %A_ScriptDir%tmp
FileDelete, %A_ScriptDir%tmp*.*
GuiControl, main:, prg, 5
SB_SetText("创建模板文件")
template := []
for a,b in source ;汇总要创建哪些模板文件 哪些sheet
{
sheets := b.sheets
for c,d in sheets
{
if !IsObject(template[d.prefix])
template[d.prefix] := []
if !template[d.prefix][d.dstsheet]
template[d.prefix][d.dstsheet] := {"file":b.file,"prefix":d.prefix,"fields":d.fields}
}
}
GuiControl, main:, prg, 8
for e,f in template ;创建模板文件
{
file := A_ScriptDir "tmp" e ".xlsx"
g := new excel() ;新建Excel对象
g.open(file)
for h,i in f
{
if A_Index > 3 ;如果超出默认的3个sheet
g.conn.ActiveWorkbook.Sheets.Add
g.conn.ActiveWorkbook.Sheets(A_index).Name := h ;重命名
g.setfields(A_index,i.fields) ;插入字段
g.save() ;保存
}
g.close()
}
GuiControl, main:, prg, 15
attach := []
SB_SetText("输出Excel")
for source_index,source_value in source ;遍历数据源
{
SB_SetText(source_value.name)
sheets := source_value.sheets ;遍历需要拆分的sheet
if (source_value.sheets.maxindex() > 0)
{
for sheet_index,sheet_value in sheets
{
SB_SetText(source_value.name "`t "sheet_value.sheet)
for delimiter in to_obj ;按拆分字段逐个拆分
{
;定义变量
if !IsObject(attach[delimiter])
attach[delimiter] := []
tmp_file := A_ScriptDir "tmp" guide_prefix "_" delimiter ".xlsx"
template_file := A_ScriptDir "tmp" guide_prefix ".xlsx"
FileCopy, % template_file, % tmp_file, 1
attach[delimiter].insert(tmp_file)
SB_SetText(delimiter,2)
tmp_result := source_value.db.GetTable("SELECT * FROM [" sheet_value.sheet "$] WHERE [" sheet_value.delimiter "]='" delimiter "';") ;读取拆分信息到数组
tmp_excel := new exceldb()
tmp_excel.open(tmp_file)
tmp_excel.conn.BeginTrans()
for row,vaules in tmp_result
{
tmp_str := ""
for k,v in vaules
tmp_str .= tmp_str ? ",'" v "'" : "'" v "'"
tmp_excel.conn.Execute("INSERT INTO [" sheet_value.dstsheet "$] VALUES (" tmp_str ")") ;插入语句
}
tmp_excel.conn.CommitTrans()
tmp_excel.close()
}
}
}
source_value.db.close()
}
GuiControl, main:, prg, 50
SB_SetText("发送邮件")
for delimiter,attaches in attach
{
tmp_title := title "_" delimiter
tmp_content := RegExReplace(content,"[delimiter]",delimiter)
tmp_mail := to_obj[delimiter]
SB_SetText("发送邮件:" tmp_title,2)
SB_SetText(delimiter,2)
Mail("",tmp_mail,tmp_title,tmp_content,attaches*)
}
GuiControl, main:, prg, 100
SB_SetText("发送完毕")
return
settoGuiClose:
Gui, setto:Destroy
return
setfromGuiClose:
Gui, setfrom:Destroy
return
guideGuiClose:
return
mainGuiClose:
ExitApp
getto(str){
o := []
Loop, Parse, str, `n, `r
{
IfInString, A_LoopField, `t
{
t := StrSplit(A_LoopField,"`t")
o[t[1]] := t[2]
}
}
return o
}
class exceldb
{
;static conn
__New() ;新建
{
this.conn:= ComObjCreate("ADODB.connection") ;初始化COM
}
open(file) ;打开文件
{
IfExist % file
this.conn.Open("Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" file) ;打开连接
;this.conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=Yes';Data Source=" file) ;打开连接 2003方式
}
close() ;关闭文件
{
this.conn.Close()
}
GetTableInfo() ;获取所有Sheet及字段信息
{
;通过OpenSchema方法获取表信息
rs := this.conn.OpenSchema(20) ;SchemaEnum 参考 http://www.w3school.com.cn/ado/app_schemaenum.asp
t := []
rs.MoveFirst()
while !rs.EOF
{
t_name := RegExReplace(rs.("TABLE_NAME").value,"$$","")
q := this.conn.Execute("select top 1 * from [" t_name "$]")
if (q.Fields(0).Name="F1" && q.Fields.Count=1) ;排除空表格
{
rs.MoveNext()
continue
}
t[t_name] := []
for field in q.Fields ;获取按顺序排列的字段
t[t_name].insert(field.Name)
q.close()
rs.MoveNext()
}
return t
}
GetTable(sql)
{
t := []
query := this.conn.Execute(sql)
if RegExMatch(sql,"i)^select*")
{
fetchedArray := query.GetRows() ;取出数据（二维数组）
colSize := fetchedArray.MaxIndex(1) + 1 ;列最大值 tips：从0开始 所以要+1
rowSize := fetchedArray.MaxIndex(2) + 1 ;行最大值 tips：从0开始 所以要+1
loop, % rowSize
{
i := (y := A_index) - 1
t[y] := []
loop, % colSize
{
j := (x := A_index) - 1
t[y][x] := fetchedArray[j,i] ;取出二维数组内值
}
}
query.Close()
return t
}
}
}
class excel
{
;static conn
__New() ;新建
{
this.conn:= ComObjCreate("Excel.Application")
this.conn.Visible := false ;false
}
open(file) ;打开文件
{
IfExist % file
this.conn.Workbooks.Open(file)
else
{
this.conn.Workbooks.Add
this.conn.ActiveWorkbook.SaveAs(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
}
save()
{
this.conn.ActiveWorkbook.Save()
}
}
;发邮件函数 参数 来自,发给,主题,html正文,附件若干
Mail(from,to,subject,content,attach*){
global mailconfig
NameSpace := "http://schemas.microsoft.com/cdo/configuration/"
Email := ComObjCreate("CDO.Message")
Email.From := mailconfig.from
Email.To := to
if mailconfig.bcc
Email.Bcc := mailconfig.bcc
Email.Subject := subject
;Email.Htmlbody := content
Email.Textbody := content
for k,v in attach
{
IfExist, % v
Email.AddAttachment(v)
}
Email.Configuration.Fields.Item(NameSpace "sendusing") := 2
Email.Configuration.Fields.Item(NameSpace "smtpserver") := mailconfig.smtp ;SMTP服务器地址
Email.Configuration.Fields.Item(NameSpace "smtpserverport") := 25
Email.Configuration.Fields.Item(NameSpace "smtpauthenticate") := 1
Email.Configuration.Fields.Item(NameSpace "sendusername") := mailconfig.account ;邮箱账号
Email.Configuration.Fields.Item(NameSpace "sendpassword") := mailconfig.pass ;邮箱密码
Email.Configuration.Fields.update
Email.Send
}
Excel数据拆分发邮件
人已捐赠
