昨天,其他问题都差不多能解决了,决定开始将系统完善,比如我的Excel导出开始没有实践,现在补全。
找到第一次开始敲机房时候总结的关于导出Excel表的方法用。结果又出错啦!
方法一
利用for循环,将datagrideview中的数据都循环出来
Dim MyExcel As New Microsoft.Office.Interop.Excel.Application()
MyExcel.Application.Workbooks.Add(True)
MyExcel.Visible = True
'将datagridview中的字段名导出
Dim m As Integer
For m = 0 To DataGridView.ColumnCount - 1
MyExcel.Cells(1, m + 1) = Me.DataGridView.Columns(m).HeaderText
Next m
'往excel表里添加数据
Dim i As Integer
For i = 0 To DataGridView.RowCount - 1
Dim j As Integer
For j = 0 To DataGridView.ColumnCount - 1
If Me.DataGridView(j, i).Value Is System.DBNull.Value Then
MyExcel.Cells(i + 2, j + 1) = ""
Else
MyExcel.Cells(i + 2, j + 1) = DataGridView(j, i).Value.ToString
End If
Next j
Next i
出现的问题就是如果用户在出现的Excel表不保存的条件下,系统就会异常。这个还是比较好的,只要加一条异常处理就可以。
方法二:
也是利用的for循环,但是其中的有个变量我没有搞懂
Dim i As Integer, u As Integer = 0, v As Integer = 0 '定义循环变量,行列变量
For i = 1 To DataGridView.Columns.Count '把表头写入Excel
xlbook.Worksheets(1).cells(1, i) = DataGridView.Columns(i - 1).HeaderCell.Value
Next
Dim str(DataGridView.Rows.Count - 1, DataGridView.Columns.Count - 1) '定义一个二维数
For u = 1 To DataGridView.Rows.Count '行循环
For v = 1 To DataGridView.Columns.Count '列循环
If DataGridView.Item(v - 1, u - 1).Value.GetType.ToString <> "Guid" Then
str(u - 1, v - 1) = DataGridView.Item(v - 1, u - 1).Value
Else
str(u - 1, v - 1) = DataGridView.Item(v - 1, u - 1).Value.ToString
End If
Next
Next
xlbook.Worksheets(1).range("A2").Resize(DataGridView.Rows.Count, DataGridView.Columns.Count).Value = str '把数组一起写入Excel
xlbook.Worksheets(1).Cells.EntireColumn.AutoFit() '自动调整Excel列
Dim row, col As Integer
For row = 0 To DataGridView.RowCount
For col = 0 To DataGridView.ColumnCount
xlsheet.Cells(row, col) = DataGridView(row, col)
Next
Next
方法三:
利用遍历foreach,将所有的数据读取出来
Dim xlapp As New Application
Dim xlbook As Workbook
Dim xlsheet As Worksheet
xlapp = CreateObject("Excel.Application")
xlbook = xlapp.Workbooks.Add(True) '添加新工作簿
xlsheet = xlbook.Worksheets(1) '第1个工作表的控制句柄
Dim rowindex, colindex As Integer
rowindex = 1 '行
colindex = 0 '列
xlsheet = xlapp.Worksheets("sheet1") '打开sheet1那页
Dim row As DataRow
Dim col As DataColumn
'Dim row As System.Windows.Forms.DataGridViewRow '定义row为表格的行
'Dim col As DataGridViewTextBoxColumn '定义col为表格的列
'把表格的每一列写到EXCEL去
For Each col In DataGridView.Columns
colindex = colindex + 1
xlapp.Cells(1, colindex) = col.HeaderText
Next
'把表格的每一行写到EXCEL去
For Each row In DataGridView.Rows
rowindex = rowindex + 1
colindex = 0
For Each col In DataGridView.Columns
colindex = colindex + 1
xlapp.Cells(rowindex, colindex) = row(col.ColumnName)
Next
Next
这个方法的错误是“datarow无法强制转换成datagridviewtextboxrow类型”,同理也“无法将datacolumn强制转换成datagridviewtextboxcolumn类型”,我调试很久,勉强将列的问题解决,但是下面的“xlapp.Cells(rowindex, colindex) = row(col.ColumnName)”行数据读取未能解决。如果有尝试的可以留言给我,或者等我以后更新。
方法四
根据方法三,我觉得还可以直接将datagridview中的数据一个表达式都出来,但是没有成功,同样需要项目做完以后继续探索了。
'Dim row, col As Integer
'For row = 0 To DataGridView.RowCount
' For col = 0 To DataGridView.ColumnCount
' xlsheet.Cells(row, col) = DataGridView(row, col)
' Next
'Next
所以,最后我就选择了第一种,其实第二种在其他人的文章上都有推荐,但是不知道为什么他们可以成功,我的就这么多错啊,唉……