189 8069 5689
下面这段代码，是我用来计算每个月存500元进银行，连续30年，最后连本带利能有多少钱。这里面涉及复利计算。界面中右边的文本框用来输出每一次计算的结果。
辽中ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为成都创新互联的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：028-86922220（备注：SSL证书合作）期待与您的合作！
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim nianxian As Integer '年限变量
Dim dingcun As Integer '定存变量
Dim fuli_big As Long '大复利
Dim fuli_small As Long '小复利
Dim i As Integer '循环变量
Dim DATAstring As String '数据字符串
nianxian = Val(年限_TextBox.Text)
dingcun = Val(定存_TextBox.Text)
DATAstring = ""
For i = 1 To nianxian
fuli_small = dingcun * (1 + 0.1875)
dingcun = fuli_small
fuli_big = fuli_big + fuli_small
DATAstring = DATAstring + "[" + Trim(Str(i)) + "]" + Str(fuli_big) + Chr(13) + Chr(10)
'DATAstring = DATAstring + "[" + Trim(Str(i)) + "]" + Str(fuli_small) + Chr(13) + Chr(10)
Next
'fuli_big = fuli_small
TextBox1.Text = DATAstring
结果_TextBox.Text = Str(fuli_big) + "元"
End Sub
使用 PrintDocument 控件的 Print() 方法可以打印指定对象中的内容，参考代码如下：
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
PrintDocument1.Print()
End Sub
Private Sub PrintDocument1_PrintPage(ByVal sender As System.Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDocument1.PrintPage
Dim bm As New Bitmap(Me.DataGridView1.Width, Me.DataGridView1.Height)
DataGridView1.DrawToBitmap(bm, New Rectangle(0, 0, Me.DataGridView1.Width, Me.DataGridView1.Height))
e.Graphics.DrawImage(bm, 0, 0)
End Sub
刚好写了个Helper类，你试验一下DataTable2Exce(这个方法代码如下：
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Public MustInherit Class ExcelHelper
Private Shared Function buildConnStr(excelFilePath As String) As String
Dim excelFileInfo As New System.IO.FileInfo(excelFilePath)
Dim constr As String
If excelFileInfo.Extension = ".xlsx" Then
constr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'", excelFilePath)
Else
constr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'", excelFilePath)
End If
Return constr
End Function
'将datatable导入到excel
Public Shared Function DataTable2Excel(dt As DataTable, excelFilePath As String) As Boolean
If File.Exists(excelFilePath) Then
Throw New Exception("该文件已经存在！")
End If
If dt.TableName.Trim.Length = 0 Or dt.TableName.ToLower = "table" Then
dt.TableName = "Sheet1"
End If
Dim colCount As Integer = dt.Columns.Count
Dim pa(colCount - 1) As OleDb.OleDbParameter
Dim tableStructStr As String = "Create Table " dt.TableName "("
Dim connString As String = buildConnStr(excelFilePath)
Dim objconn As New OleDbConnection(connString)
Dim objcmd As New OleDbCommand
objcmd.Connection = objconn
Dim dataTypeList As New ArrayList
dataTypeList.Add("System.Decimal")
dataTypeList.Add("System.Double")
dataTypeList.Add("System.Int16")
dataTypeList.Add("System.Int32")
dataTypeList.Add("System.Int64")
dataTypeList.Add("System.Single")
Dim i As Integer = 0
For Each col As DataColumn In dt.Columns
If dataTypeList.IndexOf(col.GetType.ToString) 0 Then
pa(i) = New OleDbParameter("@" col.ColumnName, OleDbType.Double)
objcmd.Parameters.Add(pa(i))
If i + 1 = colCount Then
tableStructStr += col.ColumnName + " double)"
Else
tableStructStr += col.ColumnName + " double,"
End If
Else
pa(i) = New OleDbParameter("@" col.ColumnName, OleDbType.VarChar)
objcmd.Parameters.Add(pa(i))
If i + 1 = colCount Then
tableStructStr += col.ColumnName + " VarChar)"
Else
tableStructStr += col.ColumnName + " VarChar,"
End If
End If
i += 1
Next
Try
objcmd.CommandText = tableStructStr
If objconn.State = ConnectionState.Closed Then objconn.Open()
objcmd.ExecuteNonQuery()
Catch ex As Exception
Throw ex
End Try
Dim InsertSql_1 As String = "Insert into " + dt.TableName + " ("
Dim InsertSql_2 As String = " Values ("
Dim InsertSql As String = ""
For colID As Integer = 0 To colCount - 1 Step 1
If colID + 1 = colCount Then
InsertSql_1 += dt.Columns(colID).ColumnName ")"
InsertSql_2 += "@" + dt.Columns(colID).ColumnName + ")"
Else
InsertSql_1 += dt.Columns(colID).ColumnName + ","
InsertSql_2 += "@" + dt.Columns(colID).ColumnName + ","
End If
Next
InsertSql = InsertSql_1 + InsertSql_2
For rowID As Integer = 0 To dt.Rows.Count - 1 Step 1
For colID = 0 To dt.Columns.Count - 1
If pa(colID).DbType = DbType.Double And dt.Rows(rowID)(colID).ToString.Trim = "" Then
pa(colID).Value = 0
Else
pa(colID).Value = dt.Rows(rowID)(colID).ToString.Trim
End If
Next
Try
objcmd.CommandText = InsertSql
objcmd.ExecuteNonQuery()
Catch ex As Exception
Throw ex
End Try
Next
Try
If objconn.State = ConnectionState.Open Then objconn.Close()
Catch exp As Exception
Throw exp
End Try
Return True
End Function
' 获取Excel文件数据表列表Sheets
Public Shared Function GetExcelTables(ExcelFileName As String) As ArrayList
'Dim sheets As New List(Of String)
'conn.Open()
'Dim dt As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
'For Each r In dt.Rows
' sheets.Add(r("TABLE_NAME"))
'Next
'conn.Close()
'Return sheets
Dim dt As DataTable
If Not File.Exists(ExcelFileName) Then
Throw New Exception("指定的Excel文件不存在")
Return Nothing
End If
Dim tableList As New ArrayList
Using conn As OleDbConnection = New OleDbConnection(buildConnStr(ExcelFileName))
Try
conn.Open()
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
Catch ex As Exception
Throw ex
End Try
For i As Integer = 0 To dt.Rows.Count - 1
Dim tableName As String = dt.Rows(i)(2).ToString.Trim.TrimEnd("$")
If tableList.IndexOf(tableName) 0 Then tableList.Add(tableName)
Next
End Using
Return tableList
End Function
'将Excel文件导出至DataTable(第一行作为表头)
Public Shared Function InputFromExcel(ExcelFileName As String, TableName As String) As DataTable
If Not File.Exists(ExcelFileName) Then
Throw New Exception("指定的Excel文件不存在")
End If
Dim tableList As ArrayList = GetExcelTables(ExcelFileName)
If tableList.IndexOf(TableName) 0 Then
TableName = tableList(0).ToString.Trim
End If
Dim dt As New DataTable
Dim conn As New OleDbConnection(buildConnStr(ExcelFileName))
Dim cmd As New OleDbCommand("select * from [" TableName "$]", conn) '调试是否需要$
Dim adapter As New OleDbDataAdapter(cmd)
Try
If conn.State = ConnectionState.Closed Then conn.Open()
adapter.Fill(dt)
Catch ex As Exception
Throw ex
Finally
If conn.State = ConnectionState.Open Then conn.Close()
End Try
Return dt
End Function
'查询excel文件中的一个数据
Public Shared Function ReadOneDataFromExcel(ExcelFileName As String, TableName As String, sql As String) As Object
If Not File.Exists(ExcelFileName) Then
Throw New Exception("指定的Excel文件不存在")
End If
Dim tableList As ArrayList = GetExcelTables(ExcelFileName)
If tableList.IndexOf(TableName) 0 Then
TableName = tableList(0).ToString.Trim
End If
Dim dt As New DataTable
Dim conn As New OleDbConnection(buildConnStr(ExcelFileName))
Dim cmd As New OleDbCommand(sql, conn) '调试是否需要$
Dim ret As Object
Try
If conn.State = ConnectionState.Closed Then conn.Open()
ret = cmd.ExecuteScalar()
Catch ex As Exception
Throw ex
Finally
If conn.State = ConnectionState.Open Then conn.Close()
End Try
Return ret
End Function
'获取Excel文件指定数据表的数据列表columnNames
Public Shared Function GetExcelTableColumns(ExcelFileName As String, TableName As String) As ArrayList
Dim dt As DataTable
If Not File.Exists(ExcelFileName) Then
Throw New Exception("指定的Excel文件不存在")
Return Nothing
End If
Dim ColList As New ArrayList
Using conn As OleDbConnection = New OleDbConnection(buildConnStr(ExcelFileName))
Try
conn.Open()
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, TableName, Nothing})
Catch ex As Exception
Throw ex
End Try
For i As Integer = 0 To dt.Rows.Count - 1
Dim ColName = dt.Rows(i)("Column_Name").ToString().Trim()
ColList.Add(ColName)
Next
End Using
Return ColList
End Function
End Class