一個通用的datagrid導(dǎo)出excel打印的源函數(shù)
閑暇之余，寫成函數(shù)，供新人研究學(xué)習(xí)
'power by：landlordh
'列寬默認(rèn)為datagird的tablestyles(0)列寬的五分之一
'g2e(dg1)
public function g2e(byval dg as datagrid)
dim dt as new datatable
try
dt = ctype(dg.datasource, datatable)
catch ex as exception
msgbox(ex.message)
exit function
end try
dim total_col as integer = dt.columns.count
dim total_row as integer = dt.rows.count
if total_col < 1 or total_row < 1 then
msgbox("沒有可供導(dǎo)入的數(shù)據(jù)!", msgboxstyle.information, "系統(tǒng)提示")
exit function
end if
'killexcel()
'要先在引用中添加excel組件
dim xlapp as new excel.application
dim xlbook as excel.workbook
dim xlsheet as excel.worksheet
try
gc.collect()
xlbook = xlapp.workbooks().add
xlsheet = xlbook.worksheets("sheet1")
xlapp.visible = true
try
with xlsheet.pagesetup
.rightmargin = 1
.leftmargin = 1
.centerhorizontally = true
.centerheader = "&24 報表"
.rightfooter = "&p of &n"
end with
catch ex as exception
msgbox(ex.tostring)
exit function
end try
dim col as integer
dim row as integer
dim st_row as integer = 5 '數(shù)據(jù)列頭開始行,（列頭）
dim truecol as integer = 0
for col = 0 to total_col - 1
if dg.tablestyles.item(0).gridcolumnstyles.item(col).width > 0 then truecol += 1
next
dim titlearray(4, 0) as object
dim headerarray(0, truecol - 1) as object
dim dataarray(total_row - 1, truecol - 1) as object
titlearray(0, 0) = "to:"
titlearray(1, 0) = "form:"
titlearray(2, 0) = ""
titlearray(3, 0) = ""
xlsheet.range("a1").resize(4, 1).value = titlearray
dim i as integer = 0
for col = 0 to total_col - 1
if dg.tablestyles.item(0).gridcolumnstyles.item(col).width > 0 then
i += 1
headerarray(0, i - 1) = dt.columns(col).columnname
'設(shè)列寬,默認(rèn)為datagird列寬的五分之一
xlsheet.cells(st_row, i).columnwidth = dg.tablestyles.item(0).gridcolumnstyles.item(col).width / 5
end if
next
xlsheet.range("a" & st_row).resize(st_row, truecol).value = headerarray
for row = 0 to total_row - 1
i = 0
for col = 0 to total_col - 1
if dg.tablestyles.item(0).gridcolumnstyles.item(col).width > 0 then
i += 1
dataarray(row, i - 1) = dt.rows(row).item(col)
end if
next
next
xlsheet.range("a" & st_row + 1).resize(total_row, truecol).value = dataarray
with xlsheet
.range(.cells(st_row, 1), .cells(st_row, truecol)).font.bold = true
.range(.cells(st_row, 1), .cells(st_row, truecol)).horizontalalignment = 3
.range(.cells(st_row, 1), .cells(total_row + st_row, truecol)).borders.linestyle = 1
'設(shè)置數(shù)據(jù)區(qū)第一列到第二列為居中
.range(.cells(st_row, 1), .cells(total_row + st_row, 2)).horizontalalignment = 3
end with
xlapp.activeworkbook.printpreview()
catch ex as exception
xlsheet = nothing
xlapp.displayalerts = false
xlbook.runautomacros(excel.xlrunautomacro.xlautoclose)
xlbook.close()
xlbook = nothing
xlapp.quit()
xlapp.displayalerts = true
xlapp = nothing
gc.collect()
msgbox(ex.tostring)
exit function
end try
xlsheet = nothing
xlapp.displayalerts = false
xlbook.runautomacros(excel.xlrunautomacro.xlautoclose)
xlbook.close()
xlbook = nothing
xlapp.quit()
xlapp.displayalerts = true
xlapp = nothing
gc.collect()
end function
