vb.net 操作excel

今天写的代码，一些要做保留，记录 Public Class ExportXlsClass ExportXls

Public Sub Printer()Sub Printer(ByVal pid As Int16, ByVal dbhlp As DBHelper, ByVal objds As ploy)

Dim sqlstr As String

sqlstr = "select * from Ploy where ployID=" & pid

dbhlp.FillDataTable(sqlstr, objds, "Ploy")

sqlstr = "select * from PForeign where ployID=" & pid

dbhlp.FillDataTable(sqlstr, objds, "PForeign")

Dim exl As New Excel.Application

exl.Visible = True

exl.DisplayAlerts = True

exl.WindowState = Excel.XlWindowState.xlMaximized

'Dim wb As Excel.Workbook = exl.Workbooks.Open(Application.StartupPath & "\print.xls")

'Dim ws As Excel.Worksheet = wb.Worksheets("prt")

Dim wb As Excel.Workbook = exl.Workbooks.Add()

Dim ws As Excel.Worksheet = wb.Worksheets.Item(1)

Dim Rng As Excel.Range

Rng = ws.Columns(1)

Rng.ColumnWidth = 16

Rng = ws.Columns(2)

Rng.ColumnWidth = 46

Rng = ws.Columns(3)

Rng.ColumnWidth = 16

Dim dr As ploy.PloyRow

dr = objds.Ploy.Rows(0)

Try

Rng = ws.Range("A1:A1", "C1:C1")

Rng.Cells.Merge()'合并单元格

Rng.Font.Name = "黑体"

Rng.Font.Size = "22"

Rng.HorizontalAlignment = -4108

Rng.VerticalAlignment = -4108

Rng.Value = dr.PSubject

Catch ex As Exception

End Try

Try

Rng = ws.Range("A2:A2", "C2:C2")

Rng.Cells.Merge()

Rng.Font.Name = "黑体"

Rng.Font.Size = "14"

Rng.HorizontalAlignment = -4108

Rng.VerticalAlignment = -4108

Rng.Value = "时间：" & dr.PTime

Catch ex As Exception

End Try

Try

Rng = ws.Range("A3:A3", "C3:C3")

Rng.Cells.Merge()

Rng.Font.Name = "黑体"

Rng.Font.Size = "14"

Rng.HorizontalAlignment = -4108

Rng.VerticalAlignment = -4108

Rng.Value = "地点：" & dr.Place

Catch ex As Exception

End Try

Dim drF As ploy.PForeignRow

Dim i, rngi As Integer

Dim rngs, cellB, cellE As String

rngi = 5 + objds.PForeign.Rows.Count

cellB = "A5:A5"

cellE = "C" & rngi.ToString & ":C" & rngi.ToString

Rng = ws.Range(cellB, cellE)

Rng.Font.Name = "仿宋_GB2312"

Rng.Font.Size = "16"

Rng.Borders.LineStyle = 1

Rng.HorizontalAlignment = -4108

Rng.VerticalAlignment = -4108

Rng = ws.Range("A5")

Rng.Value = "领馆"

Rng = ws.Range("B5")

Rng.Value = "出席人员"

Rng = ws.Range("C5")

Rng.Value = "职衔"

rngi = 6

For i = 0 To objds.PForeign.Rows.Count - 1

drF = objds.PForeign.Rows(i)

Rng = ws.Range("A" & rngi.ToString)

Rng.Value = drF.PConsulate

Rng = ws.Range("B" & rngi.ToString)

Rng.Value = drF.PName

Rng = ws.Range("C" & rngi.ToString)

Rng.Value = drF.PRank

rngi = rngi + 1

Next

'wb.Save()

'If Not exl Is Nothing Then

' exl.Workbooks.Close()

' exl.Quit()

' System.Runtime.InteropServices.Marshal.ReleaseComObject(exl)

'End If

'If Not ws Is Nothing Then

' System.Runtime.InteropServices.Marshal.ReleaseComObject(ws)

'End If

'ws = Nothing

'exl = Nothing

'GC.Collect()

End Sub

End Class