listview导出到excel问题,第一列保存不了,求高手
这个是数据库的字段,表名是 tslbb
然后做了一个listview把里面所有级数是3级的显示到一个listview
代码如下
Private Sub addToListView(pageCode As Integer)
Dim cursor As Long
Dim listrow As Integer
ListView1.ListItems.Clear
'-添加数据
Set rst = Nothing
rst.Open "select * from tslbb where 级别 = '3级'", con, adOpenKeyset
rst.MoveFirst
cursor = 1
listrow = 1
Do While Not rst.EOF
If cursor > (pageCode - 1) * 10 Then '因为做了翻页,所以有这行代码
ListView1.ListItems.Add , , rst.Fields(0)
ListView1.ListItems.Item(listrow).SubItems(1) = IIf(IsNull(rst.Fields(1)), "", rst.Fields(1))
ListView1.ListItems.Item(listrow).SubItems(2) = IIf(IsNull(rst.Fields(2)), "", rst.Fields(2))
ListView1.ListItems.Item(listrow).SubItems(3) = IIf(IsNull(rst.Fields(3)), "", rst.Fields(3))
ListView1.ListItems.Item(listrow).SubItems(4) = IIf(IsNull(rst.Fields(4)), "", rst.Fields(4))
listrow = listrow + 1
End If
cursor = cursor + 1
If cursor > pageCode * 10 Then
rst.MoveLast
End If
rst.MoveNext
Loop
con.Close
End Sub
然后是将listview导出到excel的代码
Private Sub Savetoexcel_Click()
On Error Resume Next
Dim ex As Object
Dim exwbook As Object
Dim exsheet As Object
Set ex = CreateObject("Excel.Application")
Set exwbook = Nothing
Set exsheet = Nothing
Set exwbook = ex.Workbooks().Add
Set exsheet = exwbook.Worksheets("sheet1")
Dim i As Integer
ex.Range("B" & 0).Value = "本级编号" '设置表头
ex.Range("C" & 0).Value = "本级编号"
ex.Range("D" & 0).Value = "本级编号"
ex.Range("E" & 0).Value = "本级编号"
For i = 0 To Frmzujianshow.ListView1.ListItems.Count
ex.Range("B" & i + 1).Value = Frmzujianshow.ListView1.ListItems.Item(i).SubItems(1)
ex.Range("C" & i + 1).Value = Frmzujianshow.ListView1.ListItems.Item(i).SubItems(2)
ex.Range("D" & i + 1).Value = Frmzujianshow.ListView1.ListItems.Item(i).SubItems(3)
ex.Range("E" & i + 1).Value = Frmzujianshow.ListView1.ListItems.Item(i).SubItems(4)
Next i
exwbook.SaveAs "E:\组件列表.xls" '保存输入到*.xls
ex.Quit '退出excel
MsgBox ("导出组件列表到E盘成功!")
End Sub
可是最后excel的确实这种样子的
出现了几个问题 1.listview的第一列,就是级数那列没有保存到excel
2.表头赋值没有成功,就是
ex.Range("B" & 0).Value = "本级编号" '设置表头
ex.Range("C" & 0).Value = "本级编号"
ex.Range("D" & 0).Value = "本级编号"
ex.Range("E" & 0).Value = "本级编号"
这几段代码都没生效
3.excel中C列和D列的数值都少了一个0
大神,求给力,被这个搞了一个晚上,郁闷死了 listview Excel VB
[解决办法]
Set exsheet = exwbook.Worksheets("sheet1")
Dim i As Integer
ex.Range("B" & 0).Value = "本级编号" '设置表头
ex.Range("C" & 0).Value = "本级编号"
ex.Range("D" & 0).Value = "本级编号"
ex.Range("E" & 0).Value = "本级编号"
For i = 0 To Frmzujianshow.ListView1.ListItems.Count
ex.Range("B" & i + 1).Value = Frmzujianshow.ListView1.ListItems.Item(i).SubItems(1)
ex.Range("C" & i + 1).Value = Frmzujianshow.ListView1.ListItems.Item(i).SubItems(2)
ex.Range("D" & i + 1).Value = Frmzujianshow.ListView1.ListItems.Item(i).SubItems(3)
ex.Range("E" & i + 1).Value = Frmzujianshow.ListView1.ListItems.Item(i).SubItems(4)
Next i
===>
Set exsheet = exwbook.Worksheets("sheet1")
Dim i As Integer
exsheet.cells(1,1) = "本级编号" '设置表头
exsheet.cells(1,2) = "本级编号"
exsheet.cells(1,3) = "本级编号"
exsheet.cells(1,4) = "本级编号"
For i = 0 To Frmzujianshow.ListView1.ListItems.Count
exsheet.cells(i+2,1) = Frmzujianshow.ListView1.ListItems.Item(i).SubItems(1)
exsheet.cells(i+2,2) = Frmzujianshow.ListView1.ListItems.Item(i).SubItems(2)
exsheet.cells(i+2,3) = Frmzujianshow.ListView1.ListItems.Item(i).SubItems(3)
exsheet.cells(i+2,4) = Frmzujianshow.ListView1.ListItems.Item(i).SubItems(4)
Next i