一个通用的datagrid导出excel打印的源函数
闲暇之余，写成函数，供新人研究学习
'power by：landlordh
'列宽默认为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("没有可供导入的数据!", msgboxstyle.information, "系统提示")
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 '数据列头开始行,（列头）
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
'设列宽,默认为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
'设置数据区第一列到第二列为居中
.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
