100分,在线等急用:如何通过office web components组件中的spreadsheet或excel工作表组件sheet将某个excel文件中的内容显示在vb窗口中 如何通过office web components组件中的spreadsheet或excel工作表组件sheet将某个excel文件中的内容显示在vb窗口中 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 是整个文件的显示,当中可能有非记录集,如图表等,我的思路是将excel文件的sheets复制到控件中,我现在犯愁的是代码如何实现,还望高手指点,先谢了 你先装offic XP,这样也能把excel文件另存为xml格式,用owc10的xmlurl属性就能显示excel表了,但不能显示图表,你可以考虑用图表控件来显示图表。 to koko897(明亮的天空)首先谢谢您的参与您的方法我试了一下,如果excel文件中有图,那么在转换的过程会提示你xml文件不能包含图。所以不能显示整个excel文件的内容。今天上午我和同事折腾了半天,结果只复制了单元格的内容,图表还是不能复制。请问您能不能试一下我的思路现在有点对spreadsheet失望了,连个普通的excel文件竟不能显示?!!!!! 用ole不行吗 Set xlApp = CreateObject("Excel.Application") xlApp.Workbooks.Open App.Path & basename Set xlBook = xlApp.Workbooks(1) Set xlSheet = xlBook.Worksheets(1) xlSheet.Rows(2).RowHeight = 14.25 xlSheet.Rows(2).WrapText = False Clipboard.Clear With xlSheet .Cells.Clear .PageSetup.Orientation = xlPortrait .Range(.Cells(1, 2), .Cells(1, 3)).Merge .Range(.Cells(1, 5), .Cells(1, 6)).Merge .Range(.Cells(1, 2), .Cells(1, 3)).Font.ColorIndex = 53 .Range(.Cells(1, 5), .Cells(1, 6)).Font.ColorIndex = 53 .Range(.Cells(3, 2), .Cells(3, 3)).Font.ColorIndex = 53 .Range(.Cells(3, 5), .Cells(3, 6)).Font.ColorIndex = 53 .Range(.Cells(3, 2), .Cells(3, 3)).Merge .Range(.Cells(3, 5), .Cells(3, 6)).Merge .Range(.Cells(9, 1), .Cells(9, 3)).Merge .Range(.Cells(9, 4), .Cells(9, 6)).Merge .Range(.Cells(10, 1), .Cells(10, 2)).Merge .Range(.Cells(10, 4), .Cells(10, 5)).Merge .Range(.Cells(1, 1), .Cells(10 , 6)).HorizontalAlignment = xlCenter .Range(.Cells(1, 1), .Cells(10 , 6)).VerticalAlignment = xlCenter .Range(.Cells(2, 1), .Cells(9, 6)).Borders.LineStyle = 1 .Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlEdgeLeft).LineStyle = xlNone .Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlEdgeTop).LineStyle = xlNone .Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlEdgeBottom).LineStyle = xlNone .Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlEdgeRight).LineStyle = xlNone .Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlInsideVertical).LineStyle = xlNone .Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlInsideHorizontal).LineStyle = xlNone .Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlEdgeLeft).LineStyle = xlNone .Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlEdgeTop).LineStyle = xlNone .Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlEdgeBottom).LineStyle = xlNone .Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlEdgeRight).LineStyle = xlNone .Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlInsideVertical).LineStyle = xlNone .Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlInsideHorizontal).LineStyle = xlNone .Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlEdgeLeft).Weight = xlThin .Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlEdgeTop).Weight = xlThin .Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlEdgeBottom).Weight = xlThin .Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlEdgeRight).Weight = xlThin .Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlEdgeLeft).Weight = xlThin .Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlEdgeTop).Weight = xlThin .Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlEdgeBottom).Weight = xlThin .Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlEdgeRight).Weight = xlThin .Range(.Cells(5, 2), .Cells(8, 3)).Font.ColorIndex = 53 .Range(.Cells(5, 5), .Cells(8, 6)).Font.ColorIndex = 53 .Range(.Cells(10, 1), .Cells(10, 6)).Borders.LineStyle = 1 .Range(.Cells(10, 1), .Cells(10, 6)).Font.ColorIndex = 53 ' 设表格边框样式 End With xlSheet.Columns(1).ColumnWidth = 12 xlSheet.Columns(2).ColumnWidth = 12 xlSheet.Columns(3).ColumnWidth = 12 xlSheet.Columns(4).ColumnWidth = 12 xlSheet.Columns(5).ColumnWidth = 12 xlSheet.Columns(6).ColumnWidth = 16.13 xlSheet.Cells(1, 1).Value = "分析时段:" xlSheet.Cells(1, 4).Value = "到:" xlSheet.Cells(2, 1).Value = "监测点编号" xlSheet.Cells(2, 3).Value = "监测点名称" xlSheet.Cells(2, 5).Value = "所在线路" xlSheet.Cells(3, 1).Value = "配电容量(KVA)" xlSheet.Cells(3, 4).Value = "运行时间(分)" xlSheet.Cells(4, 1).Value = "负载率分布" xlSheet.Cells(4, 2).Value = "时间(分)" xlSheet.Cells(4, 3).Value = "所占比例" xlSheet.Cells(4, 4).Value = "负载率分布" xlSheet.Cells(4, 5).Value = "时间(分)" xlSheet.Cells(4, 6).Value = "所占比例" xlSheet.Cells(5, 1).Value = "停电或等于零" xlSheet.Cells(6, 1).Value = "10% - 20%" xlSheet.Cells(7, 1).Value = "50% - 80%" xlSheet.Cells(8, 1).Value = "大于100%" xlSheet.Cells(5, 4).Value = "0% - 10%" xlSheet.Cells(6, 4).Value = "20% - 50%" xlSheet.Cells(7, 4).Value = "80% - 100%" xlSheet.Cells(9, 1).Value = "负载率最大值及出现时间" xlSheet.Cells(9, 4).Value = "负载率最小值及出现时间" xlSheet.Cells(1, 2).Value = Format(CXdate1, "yyyy-mm") xlSheet.Cells(1, 5).Value = Format(CXdate2, "yyyy-mm") xlSheet.Cells(1, 2).Font.ColorIndex = 5 xlSheet.Cells(1, 5).Font.ColorIndex = 5 xlSheet.Cells(2, 2).Font.ColorIndex = 5 xlSheet.Cells(2, 4).Font.ColorIndex = 5 xlSheet.Cells(2, 6).Font.ColorIndex = 5 xlSheet.Cells(10 + datecount, 1).Value = "制表:" xlSheet.Cells(10 + datecount, 2).Value = "当前用户" xlSheet.Cells(10 + datecount, 3).Value = "审核:" xlSheet.Cells(10 + datecount, 5).Value = "制表日期:" xlSheet.Cells(10 + datecount, 6).Value = Format(Now, "yyyy-mm-dd) xlApp.DisplayAlerts = False xlBook.Save 'd = True xlApp.Quit OLE1.CreateLink App.Path & basename Set xlBook = Nothingend sub直接考代码 非常感谢startvb(共同学习)的回答如果用ole,如果excel文件很大,当前窗口不能完全显示,怎样在ole控件中设定滚动条呢? 图表是不可能用spreadsheet显示的,但可以用pivot(可能写错了,就是透视表控件)和chart(图表控件)来显示,如果你一定要把图表放在excel文件里估计是没招了,其实可以通过程序直接从spreadsheet控件中的数据来生成图表啊。 Private Sub Form_Resize()Picture1.Move 0, Toolbar1.Height, frmmain.ScaleWidth, frmmain.ScaleHeight - StatusBar1.Height - Toolbar1.HeightHScroll1.Move 0, frmmain.ScaleHeight - StatusBar1.Height - HScroll1.Height, frmmain.ScaleWidth - VScroll1.WidthVScroll1.Move frmmain.ScaleWidth - VScroll1.Width, Toolbar1.Height,VScroll1.Width, frmmain.ScaleHeight - Toolbar1.Height - StatusBar1.Heightend subPrivate Sub HScroll1_Change()Picture1.Left = -HScroll1.ValueEnd SubPrivate Sub VScroll1_Change() Picture1.Top = -VScroll1.ValueEnd Sub 非常感谢诸位的参与我现在正用vb做类似报表的程序,其功能大体这样:窗口的左端显示上传到服务器上的文件列表(excel文件),右端显示左端选中的excel文件的具体内容,也就是用控件显示了。而上传到服务器上的excel文件当中有可能包含图,所以我必须把这种情况考虑进去,照各位的意思,我如果要把包含图的excel文件在窗体上完整显示出来,得用ole控件了? 领导说最好不用ole,我该咋办呢? 用 Webbrowser 控件嘛~~! 求MshFlexGrid1选中行,高亮(反显)的方法 如何判断自动分行 关于datagrid的问题! 问一个很难的Tab或者SSTab控件的问题:如何修改Tab页的标题? 交叉报表 我怎么能删除IE存在临时文件夹的文件?(在线等!!!急!) 【求救】关于在内存DC中绘图的问题。参与均给分!!!!! 编译为本机代码和P代码有什么区别? [求助]用VB+SQL的智能题库 请问VB能用到DelPhi制作的DLL文件吗,如何使用? 怎样判断一个子符串在Label中的显示长度? 问个简单的问题
首先谢谢您的参与您的方法我试了一下,如果excel文件中有图,那么在转换的过程会提示你xml文件不能包含图。所以不能显示整个excel文件的内容。今天上午我和同事折腾了半天,结果只复制了单元格的内容,图表还是不能复制。请问您能不能试一下我的思路现在有点对spreadsheet失望了,连个普通的excel文件竟不能显示?!!!!!
Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open App.Path & basename
Set xlBook = xlApp.Workbooks(1)
Set xlSheet = xlBook.Worksheets(1)
xlSheet.Rows(2).RowHeight = 14.25
xlSheet.Rows(2).WrapText = False
Clipboard.Clear
With xlSheet
.Cells.Clear
.PageSetup.Orientation = xlPortrait
.Range(.Cells(1, 2), .Cells(1, 3)).Merge
.Range(.Cells(1, 5), .Cells(1, 6)).Merge
.Range(.Cells(1, 2), .Cells(1, 3)).Font.ColorIndex = 53
.Range(.Cells(1, 5), .Cells(1, 6)).Font.ColorIndex = 53
.Range(.Cells(3, 2), .Cells(3, 3)).Font.ColorIndex = 53
.Range(.Cells(3, 5), .Cells(3, 6)).Font.ColorIndex = 53
.Range(.Cells(3, 2), .Cells(3, 3)).Merge
.Range(.Cells(3, 5), .Cells(3, 6)).Merge
.Range(.Cells(9, 1), .Cells(9, 3)).Merge
.Range(.Cells(9, 4), .Cells(9, 6)).Merge
.Range(.Cells(10, 1), .Cells(10, 2)).Merge
.Range(.Cells(10, 4), .Cells(10, 5)).Merge
.Range(.Cells(1, 1), .Cells(10 , 6)).HorizontalAlignment = xlCenter
.Range(.Cells(1, 1), .Cells(10 , 6)).VerticalAlignment = xlCenter
.Range(.Cells(2, 1), .Cells(9, 6)).Borders.LineStyle = 1
.Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlEdgeLeft).LineStyle = xlNone
.Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlEdgeTop).LineStyle = xlNone
.Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlEdgeBottom).LineStyle = xlNone
.Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlEdgeRight).LineStyle = xlNone
.Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlInsideVertical).LineStyle = xlNone
.Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlInsideHorizontal).LineStyle = xlNone
.Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlEdgeLeft).LineStyle = xlNone
.Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlEdgeTop).LineStyle = xlNone
.Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlEdgeBottom).LineStyle = xlNone
.Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlEdgeRight).LineStyle = xlNone
.Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlInsideVertical).LineStyle = xlNone
.Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlInsideHorizontal).LineStyle = xlNone
.Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlEdgeLeft).Weight = xlThin
.Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlEdgeTop).Weight = xlThin
.Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlEdgeBottom).Weight = xlThin
.Range(.Cells(5, 2), .Cells(8, 3)).Borders(xlEdgeRight).Weight = xlThin
.Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlEdgeLeft).Weight = xlThin
.Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlEdgeTop).Weight = xlThin
.Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlEdgeBottom).Weight = xlThin
.Range(.Cells(5, 5), .Cells(8, 6)).Borders(xlEdgeRight).Weight = xlThin
.Range(.Cells(5, 2), .Cells(8, 3)).Font.ColorIndex = 53
.Range(.Cells(5, 5), .Cells(8, 6)).Font.ColorIndex = 53
.Range(.Cells(10, 1), .Cells(10, 6)).Borders.LineStyle = 1
.Range(.Cells(10, 1), .Cells(10, 6)).Font.ColorIndex = 53
' 设表格边框样式
End With
xlSheet.Columns(1).ColumnWidth = 12
xlSheet.Columns(2).ColumnWidth = 12
xlSheet.Columns(3).ColumnWidth = 12
xlSheet.Columns(4).ColumnWidth = 12
xlSheet.Columns(5).ColumnWidth = 12
xlSheet.Columns(6).ColumnWidth = 16.13
xlSheet.Cells(1, 1).Value = "分析时段:"
xlSheet.Cells(1, 4).Value = "到:"
xlSheet.Cells(2, 1).Value = "监测点编号"
xlSheet.Cells(2, 3).Value = "监测点名称"
xlSheet.Cells(2, 5).Value = "所在线路"
xlSheet.Cells(3, 1).Value = "配电容量(KVA)"
xlSheet.Cells(3, 4).Value = "运行时间(分)"
xlSheet.Cells(4, 1).Value = "负载率分布"
xlSheet.Cells(4, 2).Value = "时间(分)"
xlSheet.Cells(4, 3).Value = "所占比例"
xlSheet.Cells(4, 4).Value = "负载率分布"
xlSheet.Cells(4, 5).Value = "时间(分)"
xlSheet.Cells(4, 6).Value = "所占比例"
xlSheet.Cells(5, 1).Value = "停电或等于零"
xlSheet.Cells(6, 1).Value = "10% - 20%"
xlSheet.Cells(7, 1).Value = "50% - 80%"
xlSheet.Cells(8, 1).Value = "大于100%"
xlSheet.Cells(5, 4).Value = "0% - 10%"
xlSheet.Cells(6, 4).Value = "20% - 50%"
xlSheet.Cells(7, 4).Value = "80% - 100%"
xlSheet.Cells(9, 1).Value = "负载率最大值及出现时间"
xlSheet.Cells(9, 4).Value = "负载率最小值及出现时间"
xlSheet.Cells(1, 2).Value = Format(CXdate1, "yyyy-mm")
xlSheet.Cells(1, 5).Value = Format(CXdate2, "yyyy-mm")
xlSheet.Cells(1, 2).Font.ColorIndex = 5
xlSheet.Cells(1, 5).Font.ColorIndex = 5
xlSheet.Cells(2, 2).Font.ColorIndex = 5
xlSheet.Cells(2, 4).Font.ColorIndex = 5
xlSheet.Cells(2, 6).Font.ColorIndex = 5
xlSheet.Cells(10 + datecount, 1).Value = "制表:"
xlSheet.Cells(10 + datecount, 2).Value = "当前用户"
xlSheet.Cells(10 + datecount, 3).Value = "审核:"
xlSheet.Cells(10 + datecount, 5).Value = "制表日期:"
xlSheet.Cells(10 + datecount, 6).Value = Format(Now, "yyyy-mm-dd)
xlApp.DisplayAlerts = False
xlBook.Save 'd = True
xlApp.Quit
OLE1.CreateLink App.Path & basename
Set xlBook = Nothing
end sub
直接考代码
如果用ole,
如果excel文件很大,当前窗口不能完全显示,怎样在ole控件中设定滚动条呢?
Picture1.Move 0, Toolbar1.Height, frmmain.ScaleWidth, frmmain.ScaleHeight - StatusBar1.Height - Toolbar1.Height
HScroll1.Move 0, frmmain.ScaleHeight - StatusBar1.Height - HScroll1.Height, frmmain.ScaleWidth - VScroll1.Width
VScroll1.Move frmmain.ScaleWidth - VScroll1.Width, Toolbar1.Height,VScroll1.Width, frmmain.ScaleHeight - Toolbar1.Height - StatusBar1.Height
end sub
Private Sub HScroll1_Change()
Picture1.Left = -HScroll1.Value
End Sub
Private Sub VScroll1_Change()
Picture1.Top = -VScroll1.Value
End Sub
我现在正用vb做类似报表的程序,其功能大体这样:
窗口的左端显示上传到服务器上的文件列表(excel文件),右端显示左端选中的excel文件的具体内容,也就是用控件显示了。
而上传到服务器上的excel文件当中有可能包含图,所以我必须把这种情况考虑进去,照各位的意思,我如果要把包含图的excel文件在窗体上完整显示出来,得用ole控件了?