.Net DataGridView 导出到Execl
来源:互联网 发布:任子行网络待遇 编辑:程序博客网 时间:2024/05/18 09:55
将DataGridView 中的数据导出到Execl,可根据DataGridVeiw 的可见的列动态显示.
代码如下:
Public Sub PrintOutStoreNumDetail(ByVal Grid As DataGridView)
Try
Dim xlApp As New Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlWorksheet As Excel.Worksheet
Dim Rowindex As Int16, Colindex As Integer ''获取datagridview的所有行和列
Dim Arrary As New ArrayList '''取得可见列的索引值
Dim ColCount As Integer = 0
Dim ColNum As Integer = 0
xlWorkbook = xlApp.Workbooks.Add()
xlWorksheet = xlWorkbook.Worksheets("sheet1")
Rowindex = Grid.Rows.Count
For i As Integer = 0 To Grid.Columns.Count - 1
If Grid.Columns(i).Visible = True Then
Arrary.Add(i)
ColCount += 1
Colindex += 1
End If
Next
For i As Integer = 0 To Arrary.Count - 1
xlApp.Cells(1, i + 1) = Grid.Columns(Arrary(i)).HeaderText
Next
For i As Int16 = 0 To Grid.Rows.Count - 1
For j As Integer = 0 To Arrary.Count - 1
If Grid.Columns(Arrary(j)).ValueType.Name Is System.Type.GetType("System.String").Name Then
xlApp.Cells(2 + i, j + 1) = "'" & Grid.Item(Arrary(j), i).Value.ToString
ElseIf Grid.Columns(Arrary(j)).ValueType.Name Is System.Type.GetType("System.Decimal").Name Then
xlWorksheet.Columns(j + 1).NumberFormatLocal = "0.00_ "
xlApp.Cells(2 + i, j + 1) = Convert.ToDecimal(Grid.Item(Arrary(j), i).Value.ToString)
ColNum = Arrary(j)
ElseIf Grid.Columns(Arrary(j)).ValueType.Name Is System.Type.GetType("System.Int32").Name Then
xlApp.Cells(2 + i, j + 1) = Grid.Item(Arrary(j), i).Value.ToString
Else
xlApp.Cells(2 + i, j + 1) = "'" & Grid.Item(Arrary(j), i).Value.ToString
End If
Next
Next
Dim mTable As DataTable = CType(Grid.DataSource, DataTable)
Dim SumCount As Double = mTable.Compute("sum(" & Grid.Columns(ColNum).Name & ")", "") ''此处加合计
'' showProgressInfo(Grid)
With xlWorksheet
.PageSetup.TopMargin = 120 ''距顶部的距离
.Range(.Cells(1, 1), .Cells(Rowindex + 1, Colindex)).Font.Size = 13 ''设置填充数据的字体大小
.Range(.Cells(1, 1), .Cells(Rowindex + 1, Colindex)).RowHeight = 25 ''设定行高
.Range(.Cells(1, 1), .Cells(1, Colindex)).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter ''设定标题对齐方式
.Range(.Cells(1, 1), .Cells(1, Colindex)).Select()
.Range(.Cells(1, 1), .Cells(1, Colindex)).Font.Name = "黑体"
.Range(.Cells(1, 1), .Cells(1, Colindex)).Font.Bold = True
.Range(.Cells(1, 1), .Cells(Rowindex + 1, Colindex)).Borders.LineStyle = 1
.Columns.EntireColumn.AutoFit()
'.Range(.Cells(Rowindex + 7, 2), .Cells(Rowindex + 7, Colindex)).Merge(False) ''合并单元格
End With
With xlWorksheet.PageSetup
.CenterHeader = "&""宋体,Bold""&22" & "公司名称" & Chr(10) & "&""宋体,Bold""&16" & Chr(10)
.LeftFooter = "制表人:" & "_________________"
.CenterFooter = "制表日期:"
.RightFooter = "第&P页 共&N页"
End With
xlApp.Visible = True
'' If index = 0 Then
''Else
'' xlWorksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
'' xlApp.Worksheets.PrintPreview() ''false 表示可直接显示打印界面
'' End If
Catch ex As Exception
MsgBox("PrintOutStoreNumDetail:" + ex.ToString, MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, Msg.CompanyName)
End Try
End Sub
Try
Dim xlApp As New Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlWorksheet As Excel.Worksheet
Dim Rowindex As Int16, Colindex As Integer ''获取datagridview的所有行和列
Dim Arrary As New ArrayList '''取得可见列的索引值
Dim ColCount As Integer = 0
Dim ColNum As Integer = 0
xlWorkbook = xlApp.Workbooks.Add()
xlWorksheet = xlWorkbook.Worksheets("sheet1")
Rowindex = Grid.Rows.Count
For i As Integer = 0 To Grid.Columns.Count - 1
If Grid.Columns(i).Visible = True Then
Arrary.Add(i)
ColCount += 1
Colindex += 1
End If
Next
For i As Integer = 0 To Arrary.Count - 1
xlApp.Cells(1, i + 1) = Grid.Columns(Arrary(i)).HeaderText
Next
For i As Int16 = 0 To Grid.Rows.Count - 1
For j As Integer = 0 To Arrary.Count - 1
If Grid.Columns(Arrary(j)).ValueType.Name Is System.Type.GetType("System.String").Name Then
xlApp.Cells(2 + i, j + 1) = "'" & Grid.Item(Arrary(j), i).Value.ToString
ElseIf Grid.Columns(Arrary(j)).ValueType.Name Is System.Type.GetType("System.Decimal").Name Then
xlWorksheet.Columns(j + 1).NumberFormatLocal = "0.00_ "
xlApp.Cells(2 + i, j + 1) = Convert.ToDecimal(Grid.Item(Arrary(j), i).Value.ToString)
ColNum = Arrary(j)
ElseIf Grid.Columns(Arrary(j)).ValueType.Name Is System.Type.GetType("System.Int32").Name Then
xlApp.Cells(2 + i, j + 1) = Grid.Item(Arrary(j), i).Value.ToString
Else
xlApp.Cells(2 + i, j + 1) = "'" & Grid.Item(Arrary(j), i).Value.ToString
End If
Next
Next
Dim mTable As DataTable = CType(Grid.DataSource, DataTable)
Dim SumCount As Double = mTable.Compute("sum(" & Grid.Columns(ColNum).Name & ")", "") ''此处加合计
'' showProgressInfo(Grid)
With xlWorksheet
.PageSetup.TopMargin = 120 ''距顶部的距离
.Range(.Cells(1, 1), .Cells(Rowindex + 1, Colindex)).Font.Size = 13 ''设置填充数据的字体大小
.Range(.Cells(1, 1), .Cells(Rowindex + 1, Colindex)).RowHeight = 25 ''设定行高
.Range(.Cells(1, 1), .Cells(1, Colindex)).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter ''设定标题对齐方式
.Range(.Cells(1, 1), .Cells(1, Colindex)).Select()
.Range(.Cells(1, 1), .Cells(1, Colindex)).Font.Name = "黑体"
.Range(.Cells(1, 1), .Cells(1, Colindex)).Font.Bold = True
.Range(.Cells(1, 1), .Cells(Rowindex + 1, Colindex)).Borders.LineStyle = 1
.Columns.EntireColumn.AutoFit()
'.Range(.Cells(Rowindex + 7, 2), .Cells(Rowindex + 7, Colindex)).Merge(False) ''合并单元格
End With
With xlWorksheet.PageSetup
.CenterHeader = "&""宋体,Bold""&22" & "公司名称" & Chr(10) & "&""宋体,Bold""&16" & Chr(10)
.LeftFooter = "制表人:" & "_________________"
.CenterFooter = "制表日期:"
.RightFooter = "第&P页 共&N页"
End With
xlApp.Visible = True
'' If index = 0 Then
''Else
'' xlWorksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
'' xlApp.Worksheets.PrintPreview() ''false 表示可直接显示打印界面
'' End If
Catch ex As Exception
MsgBox("PrintOutStoreNumDetail:" + ex.ToString, MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, Msg.CompanyName)
End Try
End Sub
其中,Grid参数可换成其他类型,index=1可直接显示Excel的打印界面.
- .Net DataGridView 导出到Execl
- Asp.Net 将数据导出到Execl
- 把datagridview导出到execl表格中、datagridview添加下拉列表框
- asp.net导出数据到execl并保存到本地
- 数据导出到execl
- datatable 导出到Execl
- 数据导出到execl
- vb.net datagridview导出到excel表
- asp.net C# 将数据导出到Execl汇总
- Datatable导出到Execl中
- java导出execl到本地
- asp.net中导出Execl
- dataGridView复制内容到Execl 乱码
- asp.net导出数据到execl并保存到本地 不需要调用Office组件
- DataGridView导出到Excel
- DataGridView导出到Excel
- DataGridView导出到Excel
- DataGridView导出到Excel
- 老公的公司的饭价涨价了!
- 傲游浏览器(Maxthon)2.0.3RC发布
- image与byte数组的转换
- 如何用VC 编译 早期的c++工程
- C#中有关string和byte[]转换的问题
- .Net DataGridView 导出到Execl
- MySQL时间函数tips
- 介绍一篇关于session的好文章,写的很详细
- Shell中ANSI控制码的说明
- ajax 相关
- Apache+PHP+MySQL建立数据库驱动的动态网站
- 路在何方?
- 零碎的东西
- 重建sessionFactory时应该注意的事情