public property let createtype(byval val)
if val <> 1 and val <> 2 then
createtype_ = 1
else
createtype_ = val
end if
end property
public property let data(byval val)
if not isarray(val) then
inerr("表数据设置有误")
end if
exceldata = val
end property
public property get savepath()
savepath = savepath_
end property
public property get usedtime()
usedtime = usedtime_
end property
public property let sheetname(byval val)
if not isarray(val) then
if val = "" then
inerr("表名设置有误")
end if
titlefirstline = true
else
redim titlefirstline(ubound(val))
dim ik_
for ik_ = 0 to ubound(val)
titlefirstline(ik_) = true
next
end if
sheetname_ = val
end property
public property let sheettitle(byval val)
if not isarray(val) then
if val = "" then
inerr("表标题设置有误")
end if
end if
sheettitle_ = val
end property
rem 检查数据
private sub checkdata()
if savepath_ = "" then inerr("保存路径不能为空")
if not isarray(sheetname_) then
if sheetname_ = "" then inerr("表名不能为空")
end if
if createtype_ = 2 then
if not isarray(exceldata) then
inerr("数据载入错误,或者未载入")
end if
exit sub
end if
if isarray(sheetname_) then
if not isarray(sheettitle_) then
if sheettitle_ <> "" then inerr("表标题设置有误,与表名不对应")
end if
end if
if not isarray(exceldata) then
inerr("表数据载入有误")
end if
if isarray(sheetname_) then
if getarraydim(exceldata) <> 1 then inerr("表数据载入有误,数据格式错误,维度应该为一")
else
if getarraydim(exceldata) <> 2 then inerr("表数据载入有误,数据格式错误,维度应该为二")
end if
end sub
rem 生成excel
public function create()
call checkdata()
if not isnull(readpath_) then
excelapp.workbooks.open(readpath_)
else
excelapp.workbooks.add
end if
set excelbook = excelapp.activeworkbook
set excelsheets = excelbook.worksheets
if createtype_ = 2 then
dim ih_
for ih_ = 0 to ubound(exceldata)
call setsheets(exceldata(ih_), ih_)
next
excelbook.saveas savepath_
usedtime_ = formatnumber((timer - usedtime_)*1000, 3)
exit fun
ction
end if
if isarray(sheetname_) then
dim ik_
for ik_ = 0 to ubound(exceldata)
call createsheets(exceldata(ik_), ik_)
next
else
call createsheets(exceldata, -1)
end if
excelbook.saveas savepath_
usedtime_ = formatnumber((timer - usedtime_)*1000, 3)
end function
private sub createsheets(byval data_, dataid_)
dim spreadsheet
dim tempsheettitle
dim temptitlefirstline
if dataid_<>-1 then
if dataid_ > excelsheets.count - 1 then
excelsheets.add()
set spreadsheet = excelbook.sheets(1)
else
set spreadsheet = excelbook.sheets(dataid_ + 1)
end if
if isarray(sheettitle_) then
tempsheettitle = sheettitle_(dataid_)
else
tempsheettitle = ""
end if
temptitlefirstline = titlefirstline(dataid_)
spreadsheet.name = sheetname_(dataid_)
else
set spreadsheet = excelbook.sheets(1)
spreadsheet.name = sheetname_
tempsheettitle = sheettitle_
temptitlefirstline = titlefirstline
end if
dim line_ : line_ = 1
dim rownum_ : rownum_ = ubound(data_, 1) + 1
dim lastcols_
if tempsheettitle <> "" then
'spreadsheet.columns(1).shrinktofit=true '设定是否自动适应表格单元大小(单元格宽不变)
lastcols_ = getcolname(ubound(data_, 2) + 1)
with spreadsheet.cells(1, 1)
.value = tempsheettitle
'设置excel表里的字体
.font.bold = true '单元格字体加粗
.font.italic = false '单元格字体倾斜
.font.size = 20 '设置单元格字号
.font.name="宋体" '设置单元格字体
'.font.colorindex=2 '设置单元格文字的颜色