Silencejql
0%

中牟日产打印更改

发表于 更新于 Valine：
本文字数： 3.1k 阅读时长 ≈ 3 分钟

读取Excel保存数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
void ReadAndSaveCarInfoFromExcel(string vin)
{
    try
    {
        //string serverConnString = "$Npgsql$Database=LocalDB;Host=127.0.0.1;Port=5432;Username=postgres;Password=123";
        string sql = string.Format("select * from [OBD检查表] where [VIN]='{0}' order by [测试时间] desc", vin);
        DbDataAdapter adap = BConnString.CreateDataAdapter(sql, serverConnString);
        DataTable dt = new DataTable();
        adap.Fill(dt);
        DataRow dr = null;
        if (dt.Rows.Count > 0)
            dr = dt.Rows[0];
        else
            return;
        string VIDCode = dr["VID"].ToString();
        // string vid = "1111111111111   SDFVDFSD----    SDAVV6515151  AFDFASFSA  ASDFS  S  20200707     SDFSF      111255544    "
        Regex re = new Regex(@"(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+)\s*");
        Match match = re.Match(VIDCode);

        string CarEI = match.Groups[2].Value;
        string EngineNo = match.Groups[9].Value;
        if (EngineNo.Length == 0 || EngineNo == null)
        {
            re = new Regex(@"(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+)\s*");
            match = re.Match(VIDCode);
            EngineNo = match.Groups[8].Value;
        }
        if (EngineNo.Length < 4)
        {
            re = new Regex(@"(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+?)\s+(\S+)\s*");
            match = re.Match(VIDCode);
            EngineNo = match.Groups[10].Value;
        }
        //1909889    TYBGLCFDP0DXA---A  L1234567891234567  1H5GPRCOP Y23A         S  202008154                    M9T                     M1014785             
        string carType = "";
        string productEnterprise = "";
        GetCarInfoByEI(CarEI, ref carType, ref productEnterprise);
        string sqlCarInfo = string.Format("select * from [OBD车型表] where [VIN]='{0}' order by [ID] desc", vin);
        DbDataAdapter adapCarInfo = BConnString.CreateDataAdapter(sqlCarInfo, serverConnString);
        DataTable dtCarInfo = new DataTable();
        adapCarInfo.Fill(dtCarInfo);
        DataRow drCarInfo = null;
        if (dtCarInfo.Rows.Count > 0)
            drCarInfo = dtCarInfo.Rows[0];
        else
            drCarInfo = dtCarInfo.Rows.Add();
        drCarInfo["VIN"] = vin;
        drCarInfo["发动机号"] = EngineNo;
        drCarInfo["Cartype车辆型号"] = carType;
        drCarInfo["车辆生产企业"] = productEnterprise;
        drCarInfo["更新时间"] = DateTime.Now;
        adapCarInfo.Update(dtCarInfo);
    }
    catch (ExcelException ex)
    {
        MessageBox.Show(ex.Message);
    }
}

void GetCarInfoByEI(string EI, ref string CarType, ref string ProductEnterprise)
{
    // connect Excel
    //string excelPath = @"D:\工作\现场工作文件整理\中牟日产\EI-车辆型号.xlsx";
    string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source ={0}; Extended Properties = 'Excel 12.0; HDR=Yes; IMEX=0'", excelPath);
    DataSet ds = new DataSet();
    OleDbConnection conn = new OleDbConnection(strConn);
    string sql = string.Format("select * from [Sheet1$] where [Code18/EI] like '%{0}%'", EI);
    OleDbDataAdapter oada = new OleDbDataAdapter(sql, conn);
    oada.Fill(ds);
    DataRow dr = null;
    if (ds.Tables[0].Rows.Count > 0)
        dr = ds.Tables[0].Rows[0];
    else
    {
        MessageBox.Show("EI车辆型号Excel中没有当前车型，请检查");
        return;
    }
    CarType = dr["Cartype/车辆型号"].ToString();
    ProductEnterprise = dr["生产企业"].ToString();
}