Imports System.Data.SqlClient
Imports System.Xml
Imports ADODB
Public Function RExecuteSQL(ByVal SQL As String, ByVal MsgString As String) As ADODB.Recordset
'此代码执行数据更改及查询数据
'ExecuteSQL为函数名
'SQL为查询语句
'Msgstring为返回信息
Dim ConnectString As String
ConnectString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=Rainworm;Initial Catalog=Xinji;Data Source=WORM"
Dim conn As Connection
Dim Rst As ADODB.Recordset
Dim sTokens() As String
Try
sTokens = Split(SQL)
conn = New Connection()
conn.Open(ConnectString)
If InStr("INSERT,DELETE,UPDATE", UCase$(sTokens(0))) Then
conn.Execute(SQL)
MsgString = sTokens(0) & "成功"
Else
Rst = New ADODB.Recordset()
Rst.CursorLocation = CursorLocationEnum.adUseClient
Rst.Open(Trim$(SQL), conn, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly)
'rst.MoveLast
RExecuteSQL = Rst
MsgString = "查询到" & Rst.RecordCount & "条记录"
End If
Catch
UnhandledExceptionHandler()
End Try
End Function
这个就不用说了,经典的VB6数据SQL语句。
#Region "DataToExcel"
'此导出函数有三参数:(注,默认Datagrid有其styles,且都为Textbox)
'1为你要导出的DataGrid
'2为在Excel中的开始行
'3为Datagrid的数据源查询语句。若有,直接用Adodb直接导出,若无,则用Datagrid1的DataView列出
Public Function DataGridToExcel(ByRef Data As DataGrid, ByVal PutBeginRow As Integer, Optional ByVal PutSql As String = "None") As Excel.Application
Dim myView As DataView = CType(Data.DataSource, DataView)
Cursor.Current = Cursors.WaitCursor
Dim xlApp As New Excel.Application()
Dim xlBook As Excel.Workbook = xlApp.Workbooks.Add
Dim xlSheet As Excel.Worksheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
Dim intRow As Integer
Dim intCol As Integer
Dim BeginRow As Integer = PutBeginRow
Dim RowCount As Integer = myView.Count
Dim ColCount As Integer = myView.Table.Columns.Count
'确定表头为DataGrid的caption
xlSheet.Cells(1, 1) = Data.CaptionText
xlSheet.Cells(1, 1).font.size = 14
xlSheet.Cells(1, 1).font.bold = True
xlSheet.Cells(1, 1).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, ColCount)).Merge()
xlApp.Range(xlSheet.Cells(BeginRow, 1), xlSheet.Cells(BeginRow + RowCount, ColCount + 1)).Select()
xlApp.Selection.font.size = 10
'确定Excel列标题为datagrid的列标题,且样式与列宽=datagrid
With myView
For intCol = 1 To ColCount
With Data.TableStyles(myView.Table.TableName).GridColumnStyles.Item(intCol - 1)
xlSheet.Cells(BeginRow, intCol) = .HeaderText
xlSheet.Columns(intCol).columnwidth = .Width / 7.5
Select Case .Alignment
Case HorizontalAlignment.Center
xlSheet.Columns(intCol).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
Case HorizontalAlignment.Left
xlSheet.Columns(intCol).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
Case HorizontalAlignment.Right
xlSheet.Columns(intCol).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
End Select
xlSheet.Cells(BeginRow, intCol).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
xlSheet.Cells(BeginRow, intCol).font.bold = True
End With
If TypeOf (Data.TableStyles(0).GridColumnStyles.Item(intCol - 1)) Is DataGridTextBoxColumn Then
Dim myDataGridColumn As DataGridTextBoxColumn
myDataGridColumn = Data.TableStyles(0).GridColumnStyles.Item(intCol - 1)
If myDataGridColumn.Format = "" Then
xlSheet.Columns(intCol).NumberFormatLocal = "@"
Else
xlSheet.Columns(intCol).NumberFormatLocal = myDataGridColumn.Format
End If
End If
Next
End With
'导出数据
If PutSql = "None" Then
With myView
For intRow = 1 To RowCount
For intCol = 1 To myView.Table.Columns.Count
xlSheet.Cells(intRow + BeginRow, intCol) = .Item(intRow - 1).Item(intCol - 1)
Next
Next
End With
Else
Dim myRst As New ADODB.Recordset()
Dim MsgText As String
myRst = RExecuteSQL(PutSql, MsgText)
xlSheet.Range(xlSheet.Cells(BeginRow + 1, 1), xlSheet.Cells(BeginRow + 1, 1)).CopyFromRecordset(myRst)
myRst = Nothing
End If
'设格
With xlApp
.Range(.Cells(BeginRow, 1), .Cells(BeginRow + RowCount, ColCount)).Select()
With .Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Selection.Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlThin
End With
xlApp.Visible = True
'设打印格式
With xlApp.ActiveSheet.PageSetup
.LeftMargin = .Application.InchesToPoints(0.354330708661417)
.RightMargin = .Application.InchesToPoints(0.354330708661417)
.TopMargin = .Application.InchesToPoints(0.393700787401575)
.BottomMargin = .Application.InchesToPoints(0.393700787401575)
.HeaderMargin = .Application.InchesToPoints(0.511811023622047)
.FooterMargin = .Application.InchesToPoints(0.511811023622047)
.CenterHorizontally = True
.Orientation = Excel.XlPageOrientation.xlPortrait
.PaperSize = Excel.XlPaperSize.xlPaperA4
.FirstPageNumber = Excel.XlPageBreak.xlPageBreakAutomatic
.Order = Excel.XlOrder.xlDownThenOver
.BlackAndWhite = False
.RightHeader = _
"第&""Times New Roman,常规"" &P &""宋体,常规""页,共&""Times New Roman,常规"" &N &""宋体,常规""页"
.PrintTitleRows = "$1:$" & BeginRow - 1
.Zoom = 95
End With
'xlApp.ActiveWindow.SelectedSheets.PrintPreview()
End With
Cursor.Current = Cursors.Default
Return xlApp
End Function
#End Region
如何把DataGrid内容导出到Excel
最新推荐文章于 2023-04-11 00:06:54 发布