vb.net 导出为excel及邮件群发

VB 前端之家
前端之家收集整理的这篇文章主要介绍了vb.net 导出为excel及邮件群发，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

在一个服装销售软件中,要将每天的销售额通过发邮件的形式提交到老板那里.

于是:1,提取每天销售及退货情况.

2,自动导出为excel文件

3.以导出的excel为附件进行邮件群发



关键代码如下:

导出为excel文件

  1.     Public Sub exportExcel(ByVal dgv As DataGridView)
  2.         Try
  3.             If dgv.RowCount = 0 Then
  4.                 MessageBox.Show("列表中无数据，导出数据失败","提示")
  5.                 Return
  6.             End If
  7.             '总列数
  8.             Dim columnCount As Integer = dgv.Columns.Count
  9.  
  10.             '创建Excel对象
  11.             Dim excelApp As Microsoft.Office.Interop.Excel._Application = New ApplicationClass()
  12.  
  13.             '新建工作簿
  14.             Dim workBook As Microsoft.Office.Interop.Excel._Workbook = excelApp.Workbooks.Add(True)
  15.  
  16.             '新建工作表
  17.             Dim worksheet As Microsoft.Office.Interop.Excel._Worksheet = TryCast(workBook.ActiveSheet,Microsoft.Office.Interop.Excel._Worksheet)
  18.  
  19.             '设置标题
  20.             Dim titleRange As Microsoft.Office.Interop.Excel.Range = worksheet.Range(worksheet.Cells(1,1),worksheet.Cells(1,columnCount))
  21.  
  22.             '选取单元格
  23.             titleRange.Merge(True)
  24.             '合并单元格
  25.             titleRange.Value2 = "欧榜服饰(" & SPName & ")"
  26.             '设置单元格内容
  27.             titleRange.Font.Name = "黑体"
  28.             '设置字体
  29.             'titleRange.Font.Color = Color.Red;//设置字体颜色
  30.             titleRange.Font.Size = 20
  31.             '设置字体大小
  32.             titleRange.Font.Bold = True
  33.             '字体加粗
  34.             titleRange.HorizontalAlignment = XlHAlign.xlHAlignCenter
  35.             '水平居中
  36.             titleRange.VerticalAlignment = XlVAlign.xlVAlignCenter
  37.             '垂直居中
  38.             'titleRange.Borders.LineStyle = XlLineStyle.xlContinuous;//设置边框
  39.             'titleRange.Borders.Weight = XlBorderWeight.xlThin;//设置边框粗细
  40.  
  41.             '设置表头
  42.             For i As Integer = 0 To columnCount - 1
  43.                 Dim headRange As Range = TryCast(worksheet.Cells(2,i + 1),Range)
  44.                 '获取表头单元格
  45.                 headRange.Value2 = dgv.Columns(i).HeaderText
  46.                 '设置单元格文本
  47.                 headRange.Font.Name = "宋体"
  48.                 '设置字体
  49.                 headRange.Font.Size = 14
  50.                 '字体大小
  51.                 headRange.Font.Bold = True
  52.                 '加粗显示
  53.                 headRange.HorizontalAlignment = XlHAlign.xlHAlignCenter
  54.                 '水平居中
  55.                 headRange.VerticalAlignment = XlVAlign.xlVAlignCenter
  56.                 '垂直居中
  57.                 'headRange.ColumnWidth = dataGridView1.Columns[i].Width / 7;//设置列宽
  58.                 'headRange.EntireColumn.AutoFit();//自动调整列宽
  59.                 headRange.Borders.LineStyle = XlLineStyle.xlContinuous
  60.                 '设置边框
  61.                 '设置边框粗细
  62.                 headRange.Borders.Weight = XlBorderWeight.xlThin
  63.             Next
  64.  
  65.             '填充数据
  66.             For i As Integer = 0 To dgv.Rows.Count - 1
  67.                 For j As Integer = 0 To dgv.Columns.Count - 1
  68.                     Dim contentRange As Range = TryCast(worksheet.Cells(i + 3,j + 1),Range)
  69.                     '获取单元格
  70.                     contentRange.EntireColumn.AutoFit()
  71.                     '自动调整列宽
  72.                     contentRange.RowHeight = 20
  73.                     '设置行高
  74.                     'If j = 6 Then
  75.                     '    contentRange.Value2 = dgv(j,i).Value.ToString
  76.                     'Else
  77.                     contentRange.Value2 = dgv(j,i).Value
  78.                     'End If
  79.  
  80.                     '设置单元格文本
  81.                     contentRange.Borders.LineStyle = XlLineStyle.xlContinuous
  82.                     '设置边框
  83.                     contentRange.Borders.Weight = XlBorderWeight.xlThin
  84.                     '设置边框粗细
  85.                     '自动换行
  86.                     contentRange.WrapText = True
  87.                 Next
  88.             Next
  89.  
  90.             '加入合计行
  91.             Dim totalRange As Microsoft.Office.Interop.Excel.Range = worksheet.Range(worksheet.Cells(dgv.Rows.Count + 3,worksheet.Cells(dgv.Rows.Count + 3,columnCount))
  92.             'Dim totalRange As Range = TryCast(worksheet.Cells(10,9),Range)
  93.             totalRange.Merge(True)
  94.             totalRange.Value = "合计:  " & LblNum.Text & ":  " & TxtPiece.Text & "条      " & LblPiece.Text & ":  " & TxtAcount.Text & "件       " & LblQuan.Text & ":  " & TxtQuan.Text & "元"
  95.             '   totalRange.Font.Bold = True
  96.             totalRange.Borders.LineStyle = XlLineStyle.xlContinuous
  97.             '设置边框
  98.             totalRange.Borders.Weight = XlBorderWeight.xlThin
  99.             '设置边框粗细
  100.  
  101.             '加入注意事项
  102.             Dim Remark1 As Microsoft.Office.Interop.Excel.Range = worksheet.Range(worksheet.Cells(dgv.Rows.Count + 4,worksheet.Cells(dgv.Rows.Count + 4,columnCount))
  103.             Remark1.Merge(True)
  104.             Remark1.Value = "注意:数量为负数则表示为顾客退货或换货!"
  105.             Remark1.Font.Bold = True
  106.  
  107.             '设置每列格式
  108.             For i As Integer = 0 To dgv.Columns.Count - 1
  109.                 If i = 6 Then
  110.                     Exit For
  111.                 End If
  112.                 Dim range As Range = worksheet.Range(worksheet.Cells(3,worksheet.Cells(dgv.RowCount + 3,i + 1))
  113.                 range.HorizontalAlignment = XlHAlign.xlHAlignLeft
  114.                 '对齐方式
  115.                 '格式化文本，单元格格式设置
  116.                 range.NumberFormatLocal = "0"
  117.             Next
  118.             '保存导出的Excel
  119.             Dim fileName As String = TxtRoad.Text.Trim & "\" & DtpSendDay.Value.ToString("yyyyMMdd") & "销售日报表.xls"
  120.             workBook.SaveCopyAs(fileName)
  121.             workBook.Saved = True
  122.             '设置Excel是否可见
  123.             excelApp.Visible = False
  124.             excelApp.Quit()
  125.         Catch ex As Exception
  126.             MsgBox("导出信息过程出现异常,请关闭本程序并重新尝试!",MsgBoxStyle.Critical,"提示")
  127.         End Try
  128.     End Sub

发送邮件代码:

  1.  Public Sub SendFEmail(ByVal mailHost As String,ByVal mailFrom As String,ByVal mailAccounts As String,ByVal mailPassword As String,ByVal mailSubject As String,ByVal mailBody As String,ByVal mailAttach As String)
  2.         Try
  3.  
  4.             Dim client As New System.Net.Mail.SmtpClient
  5.             client.Host = mailHost
  6.             client.Port = 25
  7.             client.Credentials = New System.Net.NetworkCredential(mailAccounts,mailPassword)
  8.  
  9.             Dim mailMessage As New System.Net.Mail.MailMessage()
  10.  
  11.             mailMessage.From = New System.Net.Mail.MailAddress(mailFrom)
  12.             Dim mailTo As String
  13.             For i As Integer = 0 To DgvEmail.RowCount - 1
  14.                 mailTo = IIf(IsDBNull(DgvEmail.Item(2,i).Value),"",DgvEmail.Item(2,i).Value)
  15.                 If mailTo = "" Then
  16.                     Exit For
  17.                 End If
  18.                 mailMessage.To.Add(mailTo)
  19.             Next
  20.             mailMessage.Subject = mailSubject
  21.             mailMessage.SubjectEncoding = System.Text.Encoding.GetEncoding(936)
  22.  
  23.             mailMessage.Body = mailBody
  24.             mailMessage.BodyEncoding = System.Text.Encoding.GetEncoding(936)
  25.             mailMessage.IsBodyHtml = False
  26.  
  27.             If Not (mailAttach Is Nothing OrElse mailAttach = String.Empty) Then
  28.                 Dim data As New System.Net.Mail.Attachment(mailAttach,System.Net.Mime.MediaTypeNames.Application.Octet)
  29.                 Dim disposition As System.Net.Mime.ContentDisposition
  30.                 disposition = data.ContentDisposition
  31.                 disposition.CreationDate = System.IO.File.GetCreationTime(mailAttach)
  32.                 disposition.ModificationDate = System.IO.File.GetLastWriteTime(mailAttach)
  33.                 disposition.ReadDate = System.IO.File.GetLastAccessTime(mailAttach)
  34.                 mailMessage.Attachments.Add(data)
  35.             End If
  36.  
  37.             client.Send(mailMessage)
  38.         Catch ex As Exception
  39.             MsgBox(ex.Message)
  40.         End Try
  41.     End Sub
  1. 操作ini文件:
  1. Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String,ByVal lpKeyName As String,ByVal lpDefault As String,ByVal lpReturnedString As String,ByVal nSize As Int32,ByVal lpFileName As String) As Int32
  2.   '写ini API函数 
  3.   Private Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String,ByVal lpString As String,ByVal lpFileName As String) As Int32
  4.   '读取ini文件内容 
  5.   Public Function GetINI(ByVal Section As String,ByVal AppName As String,ByVal FileName As String) As String
  6.     Dim Str As String = LSet(Str,256)
  7.     GetPrivateProfileString(Section,AppName,lpDefault,Str,Len(Str),FileName)
  8.     Return Microsoft.VisualBasic.Left(Str,InStr(Str,Chr(0)) - 1)
  9.  
  10.  
  11.   End Function
  12.  
  13.  
  14.   '写ini文件操作 
  15.  
  16.  
  17.   Public Function WriteINI(ByVal Section As String,ByVal FileName As String) As Long
  18.     WriteINI = WritePrivateProfileString(Section,FileName)
  19.   End Function

猜你在找的VB相关文章

VB Format函数
Format[$] ( expr [ , fmt ] ) format 返回变体型 format$ 强制返回为文本 --------------...
vb6/ASP FORMAT MM/DD/YYYY
VB6或者ASP 格式化时间为 MM/dd/yyyy 格式，竟然没有好的办法， Format 或者FormatDateTi...
VB.net 捕获项目全局异常
在项目中添加如下代码：新建窗口来显示异常信息。 Namespace My ‘全局错误处理，新的...
实现用VB.Net/(C#)开发K/3 BOS 插件的真正可行方法
转了这一篇文章，原来一直想用C#做k3的插件开发，vb没有C#用的爽呀，这篇文章写与2011年，...
vb，wps，excel 分裂
Sub 分列() ‘以空格为分隔符，连续空格只算1个。对所选中的单元格进行处理 Dim m...
VB.NET MYSQL DataGridView 增删改查（INSERT,SELECT,UPDATE,DELETE）
  Imports MySql.Data.MySqlClient Public Class Form1 ‘ GLOBAL DECLARATIONS ...
VB.NET 使用ADODB連接資料庫滙出到EXCEL
‘導入命名空間 Imports ADODB Imports Microsoft.Office.Interop   Private Sub A1() Di...
vb.net 多线程運用 ping
Imports System.IO Imports System.Threading Imports System.Diagnostics Public Class F...
VB等待进程结束
VB运行EXE程序，并等待其运行结束 参考：https://blog.csdn.net/useway/article/details/5...
vb中去掉string数组的一部分
今天碰到一个问题，登陆的时候，如果不需要验证手机号为空，则不去验证手机号 因为登陆的时...
PHPJavaJava SEPythonC#C&C++RubyVBasp.NetGoPerlnettyDjangoDelphiJsp.NET CoreSpringFlaskSpringbootSpringMVCLuaLaravelMybatisAspGroovyThinkPHPYiiswoole
文件时间pythonm相等性PHP Warning时间问题问题解决pcntl_signal采样点wav模块动态文本调用频率限制对外暴露多个访问请求更新数据表模型结构type()方法比较速度手写体sobel算子保存模型Image类nn.Conv2dpytorch1.0kaggleDCGAN交并比range()用法打印模型反卷积卷积