Excel数据拆分发邮件

;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
}