📄 listview控件里的数据导出到excel_mysky.htm
字号:
开始列数。 <BR>Dim intVisibleColumns() As Integer '
表头资料。 </P>
<P>Dim itm As ListItem </P>
<P>Dim lngResults As Long ' 回传数值。 </P>
<P>Dim objExcel As Excel.Application <BR>Dim objWorkbook As
Excel.Workbook <BR>Dim objWorksheet As
Excel.Worksheet <BR>Dim objRange As Excel.Range </P>
<P>Dim strArray() As String ' 表身资料。 <BR>Dim
strFileExtensionType As String ' 延伸档名。 </P>
<P>'------------------------------------------------ <BR>' A0
侦测作业。 <BR>'------------------------------------------------ <BR>'
判断 listview 是否有资料。 <BR>If lvw.SelectedItem Is Nothing
Then <BR>MsgBox "表格没有任何资料。", vbOKOnly vbInformation,
"汇出失败" <BR>GoTo ExitFunction <BR>End
If </P>
<P>' 询问使用者汇出范围。 <BR>lngResults = MsgBox("只汇出选择列数之资料？",
vbYesNoCancel vbQuestion vbDefaultButton2, "汇出选择列数") </P>
<P>If lngResults = vbCancel Then <BR>GoTo
ExitFunction <BR>End If </P>
<P>Screen.MousePointer = vbHourglass </P>
<P>' 测试是否本机是否安装 Excel。 <BR>On Error Resume
Next <BR>Set objExcel = New Excel.Application </P>
<P>If Err.Number > 0 Then <BR>MsgBox "本机未安装 MS Excel 。",
vbOKOnly vbCritical, "加载 Excel 失败" <BR>GoTo
ExitFunction <BR>End If </P>
<P>'------------------------------------------------ <BR>' B0
Excel
相关设定作业。 <BR>'------------------------------------------------ <BR>On
Error GoTo ExportToExcel_EH </P>
<P>' 不让使用者操作。 <BR>objExcel.Interactive = False </P>
<P>' 背后作业。 <BR>If objExcel.Visible = False
Then <BR>objExcel.Visible = True <BR>End
If </P>
<P>' 窗口最大化。 <BR>objExcel.WindowState =
xlMaximized </P>
<P>' 设定 Wokkbook 对象。 <BR>Set objWorkbook =
objExcel.Workbooks.Add </P>
<P>' 设定 Worksheet 对象，指向 Sheet 1。 <BR>Set objWorksheet =
objWorkbook.Sheets(1) </P>
<P>' 设定 Range 对象，指向 Row 1。 <BR>Set objRange =
objWorksheet.Rows(1) </P>
<P>' 设定表头字号、粗体。 <BR>objRange.Font.Size =
9 <BR>objRange.Font.Bold = True </P>
<P>'------------------------------------------------ <BR>' C0
Excel
表头部份相关设定作业。 <BR>'------------------------------------------------ <BR>For
intColCnt = 1 To lvw.ColumnHeaders.Count </P>
<P>If lvw.ColumnHeaders(intColCnt).Width <> 0 Then </P>
<P>intColumns = intColumns 1 </P>
<P>ReDim Preserve intVisibleColumns(1 To intColumns) </P>
<P>intVisibleColumns(intColumns) = intColCnt </P>
<P>objRange.Cells(1, intColumns) =
lvw.ColumnHeaders(intColCnt).Text </P>
<P>With objWorksheet.Columns(intColumns) </P>
<P>Select Case LCase(lvw.ColumnHeaders(intColCnt).Tag) <BR>Case
"string", "" <BR>.NumberFormat = "@" <BR>Case
"number" <BR>.NumberFormat =
"#,##0.00_);(#,##0.00)" <BR>.HorizontalAlignment =
xlRight <BR>Case "date" <BR>.NumberFormat =
"yyyy/mm/dd" <BR>.HorizontalAlignment =
xlRight <BR>End Select </P>
<P>End With </P>
<P>End If <BR>Next intColCnt </P>
<P>'------------------------------------------------ <BR>' D0
取得 listview
数据，置入数组。 <BR>'------------------------------------------------ <BR><BR><BR>ReDim
strArray(1 To lvw.ListItems.Count, 1 To intColumns) </P>
<P>intStartRow = 2 </P>
<P>For Each itm In lvw.ListItems </P>
<P>If lngResults = vbNo Or itm.Selected Then </P>
<P>intRowCnt = intRowCnt 1 </P>
<P>For intColCnt = 1 To intColumns </P>
<P>If intVisibleColumns(intColCnt) = 1
Then <BR>strArray(intRowCnt, 1) =
itm.Text <BR>Else <BR>strArray(intRowCnt, intColCnt)
= itm.SubItems(intVisibleColumns(intColCnt) - 1) <BR>End
If <BR>Next intColCnt <BR>End If <BR>Next
itm </P>
<P>'------------------------------------------------ <BR>' E0
数组数据，置入
Excel。 <BR>'------------------------------------------------ <BR>With
objWorksheet <BR>.Range(.Cells(2, 1), .Cells(2 intRowCnt - 1,
intColumns)) = strArray <BR>End With </P>
<P>objWorksheet.Columns.AutoFit </P>
<P>'------------------------------------------------ <BR>' F0
取得延伸档名。 <BR>' 参阅 Excel 说明里的「Microsoft Excel
提供的档案格式转换器」 <BR>'------------------------------------------------ <BR>Select
Case FileFormat <BR>Case
xlSYLK <BR>strFileExtensionType = "slk" <BR>Case
xlWKS <BR>strFileExtensionType = "wks" <BR>Case
xlWK1, xlWK1ALL, xlWK1FMT <BR>strFileExtensionType =
"wk1" <BR>Case xlCSV, xlCSVMac,
xlCSVWindows <BR>strFileExtensionType =
"csv" <BR>Case xlDBF2, xlDBF3,
xlDBF4 <BR>strFileExtensionType = "dbf" <BR>Case
xlWorkbookNormal, xlExcel2FarEast, xlExcel3, xlExcel4, xlExcel4Workbook,
xlExcel5, xlExcel7, xlExcel9795 <BR>strFileExtensionType =
"xls" <BR>Case xlHtml <BR>strFileExtensionType =
"htm" <BR>Case xlTextMac, xlTextWindows, xlUnicodeText,
xlCurrentPlatformText <BR>strFileExtensionType =
"txt" <BR>Case
xlTextPrinter <BR>strFileExtensionType =
"prn" <BR>Case Else <BR>strFileExtensionType =
"dat" <BR>End Select </P>
<P>'------------------------------------------------ <BR>' G0
另存盘案。 <BR>'------------------------------------------------ <BR>If
InStr(1, strFileName, ".") = 0 Then </P>
<P>' 组合文件名称。 <BR>strFileName = strFileName & "." &
strFileExtensionType </P>
<P>' 另存盘案。 <BR>objWorksheet.SaveAs strFileName,
FileFormat </P>
<P>End If </P>
<P>'------------------------------------------------ <BR>' Z0
结束作业。 <BR>'------------------------------------------------ <BR>'
关闭</P></DIV></TD></TR></TBODY></TABLE><BR>
<DIV class=opt><A title=查看该分类中所有文章
href="http://hi.baidu.com/gallonchai/blog/category/Vb">类别：Vb</A> | <A
title=将此文章添加到百度搜藏 onclick="return addToFavor();"
href="http://cang.baidu.com/do/add" target=_blank>添加到搜藏</A> | 浏览(<SPAN
id=result></SPAN>) | <A
href="http://hi.baidu.com/gallonchai/blog/item/10b7a6d3d518f8dda9ec9ac2.html#send">评论</A> (0)
<SCRIPT language=javascript>/*<![CDATA[*/var pre = [true,'使ComboBox的选项内容显示完全', '使ComboBox的选项内容显示完全','/gallonchai/blog/item/0a9ea336f30699330b55a953.html'];var post = [true,'实现VB与EXCEL的无缝连接','实现VB与EXCEL的无缝连接', '/gallonchai/blog/item/71550fb57813ffcc37d3ca0f.html'];if(pre[0] || post[0]){ document.write('<div style="height:5px;line-height:5px;"> </div><div id="in_nav">'); if(pre[0]){ document.write('上一篇：<a href="' + pre[3] + '" title="' + pre[1] + '">' + pre[2] + '</a> '); } if(post[0]){ document.write('下一篇：<a href="' + post[3] + '" title="' + post[1] + '">' + post[2] + '</a>'); } document.write('</div>');}/*]]>*/</SCRIPT>
</DIV>
<DIV class=line></DIV>
<STYLE type=text/css>#in_related_doc A {
TEXT-DECORATION: none
}
</STYLE>
<DIV id=in_related_tmp></DIV>
<SCRIPT language=javascript type=text/javascript>/*<![CDATA[*/function HI_MOD_IN_RELATED_DOC_CALLBACK(arg){ if(arg.length <= 1) return false; var hasMore = arg[0]; var D=function(A,B){A[A.length]=B;} if(arg.length % 2 == 0) D(arg, ["","","",""]); var html = ['<div id="in_related_doc"><div class="tit">相关文章：</div>']; D(html, '<table cellpadding="0" cellspacing="3" border="0">'); for(var i = 1, j = arg.length; i < j; i += 2){ D(html, '<tr>'); D(html, '<td width="15px"><a style="font-size:25px" >•</a></td><td><a href="http://hi.baidu.com/' + arg[i][3] + '/blog/item/' + arg[i][2] + '.html" target="_blank" title="' + arg[i][0] + '">' + arg[i][1] + '</a>'); D(html, new Array(10).join('\u3000')); D(html, '</td>'); if(arg[i + 1][0] != "") D(html, '<td width="15px"><a style="font-size:25px" >•</a></td><td><a href="http://hi.baidu.com/' + arg[i + 1][3] + '/blog/item/' + arg[i + 1][2] + '.html" target="_blank" title="' + arg[i + 1][0] + '">' + arg[i + 1][1] + '</a></td>'); else D(html, '<td> </td><td> </td>'); D(html, '</tr>'); } if(hasMore) D(html, '<tr><td colspan="4"><a target="_blank" href="/sys/search?pageno=1&type=7&sort=1&word=listview%BF%D8%BC%FE%C0%EF%B5%C4%CA%FD%BE%DD%B5%BC%B3%F6%B5%BDexcel&item=10b7a6d3d518f8dda9ec9ac2">更多>></a></td></tr>'); D(html, '</table></div><div class="line"> </div>'); var div = document.getElementById('in_related_tmp'); if(div){ div.innerHTML = html.join(''); while(div.firstChild){ div.parentNode.insertBefore(div.firstChild, div); } div.parentNode.removeChild(div); } window.setTimeout("tracker_init('in_related_doc')",100);}if(RelatedDocData == -1){ // not supported xhr var script = document.createElement('script'); script.type = 'text/javascript'; script.src = '/sys/search?type=8&word=listview%BF%D8%BC%FE%C0%EF%B5%C4%CA%FD%BE%DD%B5%BC%B3%F6%B5%BDexcel&item=10b7a6d3d518f8dda9ec9ac2&t=' + new Date().getTime(); document.getElementsByTagName('HEAD')[0].appendChild(script);}else if(RelatedDocData == null){ GetAndEval = true;}else{ eval(RelatedDocData);}/*]]>*/</SCRIPT>
<DIV id=in_reader>
<DIV class=tit>最近读者：</DIV>
<SCRIPT> var g_spAnnony=true;var g_read=[{}];g_read.length=g_read.length-1;var _rh1="";var _rh2="";function wrreader(){ _rh1 += '<table width="100%" ><tr>'; _rh2+='<tr>'; if(g_spAnnony){ _rh1+='<td align="center" width="10%" ><img border="0" width="55" height="55" src="http://img.baidu.com/hi/img/portraitn.jpg"></td>'; _rh2+='<td> </td>'; if(g_read.length>0){ _rh1+='<td align="left" width="12%">'; }else{ _rh1+='<td align="left" width="100%">'; } _rh1+="<a href='http://passport.baidu.com/?login&tpl=sp&tpl_reg=sp&u="+myref+"' target='_self'>登录</a>后，您就出现在这里。</td>"; _rh2+='<td> </td>' } if(g_read.length==0){ if(!g_spAnnony){ _rh1+='<td align=left width="100%">最近还没有登录用户看过这篇文章……</td>'; _rh2+='<td> </td>'; } }else{ for(i=0,len=g_read.length;i<len;i++){ _rh1+='<td align="center" valign="bottom" width="10%" class="user"><a href="/'+g_read[i][0]+'" target="_blank"><img border="0" src="http://himg.baidu.com/sys/portraitn/item/'+g_read[i][1]+'.jpg"></a></td>'; _rh2+='<td align="center" valign="top" class="user"><a href="/'+g_read[i][0]+'" target="_blank">'+g_read[i][2]+'</a></td>'; } } _rh1+='<td width="100%"></td></tr>'; _rh2+='<td></td></tr></table>'; document.write(_rh1+_rh2);}wrreader();</SCRIPT>
</DIV>
<DIV class=line></DIV>
<SCRIPT language=JavaScript>allkey=allkey+"268962d15d40ed389b5027b9_10b7a6d3d518f8dda9ec9ac2_";</SCRIPT>
<DIV id=in_comment><A name=comment></A>
<DIV class=tit>网友评论：</DIV>
<SCRIPT>function writecmt(type,id,cmtname,cmturl,portraitId){ var html1=""; if(type==1){ html1="<a href='"+cmturl+"' target='_blank' title='"+cmturl+"'><img border='0' src='http://himg.baidu.com/sys/portraitn/item/"+portraitId+".jpg'><br>"+cmtname+"</a>"; }else{ if(cmtname=="" || cmtname=="匿名网友"){ if(cmturl==""){ html1="<a>匿名网友</a>"; }else{ html1="<a href='"+cmturl+"' target='_blank' title='"+cmturl+"'>"+cmtname+"</a>"; } }else{ if(cmturl==""){ html1="<div class='f14' style='display:inline'>网友:<a>"+cmtname+"</a></div>"; }else{ html1="<div class='f14' style='display:inline'>网友:<a href='"+cmturl+"' target='_blank' title='"+cmturl+"'>"+cmtname+"</a></div>"; } } } document.write(html1);}</SCRIPT>
<DIV id=page></DIV></DIV>
<DIV id=in_send><A name=send></A>
<FORM id=popFormSubmit name=form1 onsubmit="return checkcmtform()"
action=/gallonchai/commit method=post><INPUT type=hidden value=8 name=ct> <INPUT
type=hidden value=1 name=cm> <INPUT type=hidden value=10b7a6d3d518f8dda9ec9ac2
name=spBlogID>
<SCRIPT language=JavaScript> document.write("<input type='hidden' name='spRefURL' value='"+window.location.href+"'>");</SCRIPT>
<DIV class=tit>发表评论：</DIV>
<TABLE cellSpacing=5 cellPadding=0 width=620 border=0>
<TBODY>
<TR>
<TD class=f14>姓 名：</TD>
<TD><INPUT id=spBlogCmtor style="WIDTH: 220px" onfocus=hidErr(1);
tabIndex=1 maxLength=49 onchange="checkname('spBlogCmtor')"
name=spBlogCmtor>
<SCRIPT>document.write(" <a href='http://passport.baidu.com/?reg&tpl=sp&return_method=get&skip_ok=1&u=http://hi.baidu.com/sys/reg/' target='_blank'>注册</a>");document.write(" | <a href='http://passport.baidu.com/?login&tpl=sp&tpl_reg=sp&u="+myref+"'>登录</a>");</SCRIPT>
<DIV id=nmerror style="DISPLAY: none">*姓名最长为50字节</DIV></TD></TR>
<TR id=1_err style="DISPLAY: none">
<TD> </TD>
<TD>
<DIV class=error id=1_err_con></DIV></TD></TR>
<TR>
<TD class=f14>网址或邮箱：</TD>
<TD><INPUT id=spBlogCmtURL style="WIDTH: 360px" onfocus=hidErr(2);
tabIndex=2 maxLength=128 onchange="checkeandu('spBlogCmtURL')"
name=spBlogCmtURL> (选填)</TD>
<SCRIPT>G("spBlogCmtor").value="";G("spBlogCmtURL").value="";</SCRIPT>
</TR>
<TR id=2_err style="DISPLAY: none">
<TD> </TD>
<TD>
<DIV class=error id=2_err_con></DIV></TD></TR>
<TR>
<TD class=f14 vAlign=top>内 容：</TD>
<TD><TEXTAREA id=spBlogCmtText style="WIDTH: 520px; HEIGHT: 155px" onfocus=hidErr(3); tabIndex=3 name=spBlogCmtText></TEXTAREA>
<SCRIPT>G("spBlogCmtor").value=G("spBlogCmtor").defaultValue;G("spBlogCmtText").value="";</SCRIPT>
</TD></TR>
<TR id=3_err style="DISPLAY: none">
<TD> </TD>
<TD>
<DIV class=error id=3_err_con></DIV></TD></TR>
<TR id=vercode>
<TD class=f14 vAlign=top>验证码：</TD>
<TD vAlign=top><INPUT type=hidden
value=D1E4A896E036141346A2310311808FE62E2CC5A57FF7F0A4CBC15F17D16275A1D35AB0978B978AAAD5AB88481252611D72868F9761D51399968BBD2D30C6B105
name=spVcode> <INPUT id=spVerifyKey onfocus=f_focus() tabIndex=4
maxLength=4 size=6 name=spVerifyKey autocomplete="off"><BR>
<SCRIPT type=text/javascript>/*<![CDATA[*/var imgsrc="http://hiup.baidu.com/cgi-bin/genimg?D1E4A896E036141346A2310311808FE62E2CC5A57FF7F0A4CBC15F17D16275A1D35AB0978B978AAAD5AB88481252611D72868F9761D51399968BBD2D30C6B105";function f_focus(){ if(G('yanzheng').style.display=="none" ){ G('verifypic').src=imgsrc; G('yanzheng').style.display="block"; }}function newverifypic(){ G("verifypic").src = imgsrc +"&t="+ Math.random(); return false;}/*]]>*/</SCRIPT>
<DIV id=yanzheng style="DISPLAY: none"><IMG id=verifypic height=40
width=120><WBR><A title=看不清左边的字符 onfocus=this.blur();
onclick="return newverifypic();"
href="http://hi.baidu.com/gallonchai/blog/item/10b7a6d3d518f8dda9ec9ac2.html#">看不清?</A>
</DIV></TD></TR>
<TR>
<TD class=f14 vAlign=top> </TD>
<TD class=f14 vAlign=top><INPUT id=btn_ok tabIndex=5 type=submit value=发表评论 name=btn_ok></TD></TR></TBODY></TABLE></FORM></DIV><BR></DIV>
<TABLE height=8 cellSpacing=0 cellPadding=0 width="100%" border=0>
<TBODY>
<TR>
<TD class=modbl width=7> </TD>
<TD class=modbc> </TD>
<TD class=modbr width=7> </TD></TR></TBODY></TABLE></DIV></DIV></DIV></DIV>
<SCRIPT language=javascript><!--var hstr="/gallonchai/brwstat?key1=1";document.write("<script src='"+hstr+"&key2="+allkey+"'><\/script>");//--></SCRIPT>
<BR>
<CENTER>
<DIV id=ft>©2008 Baidu</DIV></CENTER>
<SCRIPT>if(document.getElementById("m_blog")){ var imgarray = document.getElementById("m_blog").getElementsByTagName('img'); var imgw = document.getElementById("m_blog").offsetWidth; imgw =imgw-40; for(var i=0; i<imgarray.length; i++){ if(imgarray[i].className=="blogimg" && imgarray[i].width>=imgw) imgarray[i].width=imgw; }}// Fix ff bugsvar blog_text = document.getElementById('blog_text');blog_text.innerHTML = blog_text.innerHTML.replace(/href\s*=\s*("|')?(\.\.\/\.\.\/)/gi,"href=$1../$2");</SCRIPT>
</CENTER><IMG style="DISPLAY: none" src=""> </BODY></HTML>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -