winform解析csv文件自动入库并开机自启+邮件告知执行情况

本文链接：https://blog.csdn.net/m0_37781450/article/details/71081434

个人网站：友书小说网（http://laiyoushu.com）绿色纯净无广告，欢迎大家前来看小说

前提：改程序的主要作用是用来每天定时自动解析客户上传的csv文件并将其数据入库，执行成功之后会自动发邮件给指定的人反馈执行情况，并为了防止某些情况下服务器被重启，所以，也相应的加上了开机自启功能。所有代码均为原创，欢迎大家一起讨论IT技术以及指点出本人的不足，因为这些都是很基础的代码，所以就懒得写注释了

PS:代码自己反复侧过，可直接拿去用，

using NPOI.HSSF.UserModel;

using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.Common;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using Microsoft.Win32;
using System.Net.Mail;
using System.Net;

namespace ReadExcel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
public static StreamReader sr;
public static StreamWriter sw;
private void Form1_Load(object sender, EventArgs e)
{
}
protected HSSFWorkbook hssfworkbook;
//设置加入开机启动
private void but_start_Click(object sender, EventArgs e)
{
//startread();
string path = Application.ExecutablePath;
RegistryKey rk = Registry.LocalMachine;
RegistryKey rk2 = rk.CreateSubKey(@"Software\Microsoft\Windows\CurrentVersion\Run");
rk2.SetValue("JcShutdown", path);
rk2.Close();
rk.Close();
}
//开始
public static void startread()
{
sw = new StreamWriter("C:\\堪宝CSV更新日志文件.txt", false);
sw.WriteLine("准备解析CSV文件！" + " " + DateTime.Now.ToString() + "\n");
sw.Flush();
//string path_Url = @"c:/test.csv";
string path_Url = @"C:/FTPLoad/CAMBRO.CSV";
List<MV_Excel> list = new List<MV_Excel>();
try
{
StreamReader sr = new StreamReader(path_Url, Encoding.Default);
String line;
var aa = "";
sw.WriteLine("开始解析CSV文件！" + " " + DateTime.Now.ToString() + "\n");
sw.Flush();
while ((line = sr.ReadLine()) != null)
{
MV_Excel db = new MV_Excel();
aa = line.ToString();
string[] sArray = Regex.Split(aa, ",", RegexOptions.IgnoreCase);
db.Warehouse = sArray[0];
db.Item = sArray[1];
db.Qty = sArray[2];
list.Add(db);
}
sw.WriteLine("解析CSV完成！" + " " + DateTime.Now.ToString() + "\n");
sw.Flush();
//预备插入库
try
{
string sql = "DELETE xxx;\n";
for (int i = 0; i < list.Count; i++)
{
if (i > 0)
{
sql += string.Format("INSERT INTO dbo.Order_Depot(aaa, bbb, ccc)VALUES ( '{0}','{1}','{2}');\n", list[i].Warehouse, list[i].Item, list[i].Qty);
}
}
using (SqlConnection con = new SqlConnection(@"server =.\sql2014;uid=sa;pwd=xxx;database=xxx"))
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
int status = cmd.ExecuteNonQuery();
if (status > 0)
{
int SuccessCount = SearchSuccessNo();
if (SuccessCount > 0)
{
sw.WriteLine("数据插入完成！" + " " + DateTime.Now.ToString() + "\n");
sw.Flush();
SendMailUseZj("数据执行详情\n", SuccessCount + "条数据插入成功！\n入库sql语句详情为：\n" + sql);
MessageBox.Show("插入成功！");
}
else
{
sw.WriteLine("数据插入失败！受影响行数为：" + SuccessCount + DateTime.Now.ToString() + "\n");
sw.Flush();
SendMailUseZj("数据执行详情", "数据插入失败！！受影响行数为：" + SuccessCount + "\n" + "入库sql语句详情为：\n" + sql);
MessageBox.Show("插入成功！");
}
}
else
{
sw.WriteLine("数据插入失败！\n入库sql语句详情为：\n" + sql + DateTime.Now.ToString() + "\n");
sw.Flush();
SendMailUseZj("数据执行详情", "数据插入失败！！\n入库sql语句详情为：\n" + sql);
MessageBox.Show("插入失败！");
}
}
}
catch (Exception ex)
{
SendMailUseZj("数据执行详情", "数据插入失败！请检查数据库联机字符串是否正确！\n" + ex.ToString().Substring(0, 300));
}
}
catch (Exception ex)
{
sw.WriteLine("解析CSV失败！请检查路径是否正确、csv文件是否包含非法字符！" + DateTime.Now.ToString() + "\n" + ex.ToString().Substring(0, 300));
sw.Flush();
SendMailUseZj("数据执行详情", "数据解析失败！请检查路径是否正确、csv文件是否包含非法字符！\n" + ex.ToString().Substring(0, 300));
}
}
//查询受影响的行数
public static int SearchSuccessNo()
{
object number = 0;
using (SqlConnection con = new SqlConnection(@"server =.\sql2014;uid=sa;pwd=xxx;database=xxx"))
{
con.Open();
SqlCommand cmd = new SqlCommand(" SELECT COUNT(1) FROM dbo.xxx", con);
number = cmd.ExecuteScalar();
}
return Convert.ToInt32(number);
}
//定时器，每秒执行一次
private void timer1_Tick(object sender, EventArgs e)
{
this.times.Interval = 1000;
times.Tick += new EventHandler(TimerEventProcessor);

}

//定时，每天两点启动
private static void TimerEventProcessor(Object myObject, EventArgs myEventArgs)
{
if (DateTime.Now.ToString("HH：mm：ss") == "02：00：00")
{
startread();
}
}

private class MV_Excel
{
//Warehouse,Item,Qty
public string Warehouse { get; set; }
public string Item { get; set; }
public string Qty { get; set; }
}

public static void SendMailUseZj(string email_Title, string email_Content)
{
try
{
//发信人所用邮箱的服务器
string smtp = "smtp.163.com";
//发件人的邮箱
string mailForm = "1365dd4s460@163.com";
//发件人的密码
string mailPwd = "chesdfsdfing";
SmtpClient client = new SmtpClient(smtp);
client.Port = 25;
client.UseDefaultCredentials = false;
client.Credentials = new NetworkCredential(mailForm, mailPwd);
/*指定如何处理待发的邮件*/
client.DeliveryMethod = SmtpDeliveryMethod.Network;
//MailMessage message = new MailMessage(mailForm, mailTo, mailTitle, mailContent);
MailMessage message = new MailMessage();
message.From = new MailAddress(mailForm, "xxx服务器", Encoding.UTF8);
//添加收件人
message.To.Add("xxxx@.com");
//添加抄送人
//message.CC.Add("xxx@123.com");
//邮件标题
message.Subject = email_Title;
//邮件内容
message.Body = email_Content;
//设置字符文本编码格式
message.BodyEncoding = Encoding.UTF8;
message.HeadersEncoding = Encoding.UTF8;
message.SubjectEncoding = Encoding.UTF8;
message.IsBodyHtml = true;
//发送邮件
client.Send(message);
sw.WriteLine("邮件发送成功！" + DateTime.Now.ToString());
sw.Flush();
sw.Close();
}
catch (Exception ex)
{
sw.WriteLine("邮件发送失败！" + DateTime.Now.ToString());
sw.Flush();
sw.Close();
MessageBox.Show("邮件发送失败！");
}
}
}

}

如有问题，请加我QQ：631931078或352167311