c# 将excel导入 sqlite

nuget 须要加载
EPPlus.Core
ExcelDataReader
ExcelDataReader.DataSet

//需要引用的扩展
using ExcelDataReader;
using ExcelPackage = OfficeOpenXml.ExcelPackage;
public static void CreateZhouPianChaTable()
        {
            string tbname = "zhou_pian_cha1";

            //判断表是否存在
            bool isExist = TableIsExist(tbname);
            if (!isExist)
            {
                SQLiteConnection db = ConnectToDatabase(SqLite1);
                _sql = $"create table {tbname} ( ID integer NOT NULL PRIMARY KEY AUTOINCREMENT,size_min real(255),size_max real(255),tolerance_class Text(255),ES real(255),El real(255) )";
                _command = new SQLiteCommand(_sql, db);
                _command.ExecuteNonQuery();
                
                
                string excelFilePath = @"D:\c#\轴的极限偏差.xlsx";
            

                //另外一种将excel 读取到 datatable 的方式
                // using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFilePath))) {
                //     ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                //     
                //     // 读取数据到 DataTable 中
                //     DataTable dataTable = new DataTable(worksheet.Name);
                //     foreach (var firstRowCell in worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column]) {
                //         dataTable.Columns.Add(firstRowCell.Text);
                //     }
                //     for (int rowNumber = 2; rowNumber <= worksheet.Dimension.End.Row; rowNumber++) {
                //         var row = worksheet.Cells[rowNumber, 1, rowNumber, worksheet.Dimension.End.Column];
                //         DataRow dr = dataTable.Rows.Add();
                //         foreach (var cell in row) {
                //             dr[cell.Start.Column - 1] = cell.Text;
                //         }
                //     }
                //     
                // }
            
            
            
                var data = ReadExcelToDataSet(excelFilePath);
                DataTable dataTable = data.Tables[0];
                
                
                string addsql = $"INSERT INTO {tbname} (ID, size_min, size_max,tolerance_class,ES,El) VALUES (@Column1, @Column2, @Column3,@Column4,@Column5,@Column6)";
                
                // 插入数据
                using (var bulkInsertCommand = new SQLiteCommand(addsql, db))
                {
                    bulkInsertCommand.Parameters.AddWithValue("@Column1", dataTable.Rows[0][0]);
                    bulkInsertCommand.Parameters.AddWithValue("@Column2", dataTable.Rows[0][1]);
                    bulkInsertCommand.Parameters.AddWithValue("@Column3", dataTable.Rows[0][2]);
                    bulkInsertCommand.Parameters.AddWithValue("@Column4", dataTable.Rows[0][3]);
                    bulkInsertCommand.Parameters.AddWithValue("@Column5", dataTable.Rows[0][4]);
                    bulkInsertCommand.Parameters.AddWithValue("@Column6", dataTable.Rows[0][5]);
                            
                            
                    for (int i = 1; i < dataTable.Rows.Count; i++)
                    {
                        bulkInsertCommand.Parameters["@Column1"].Value = dataTable.Rows[i][0];
                        bulkInsertCommand.Parameters["@Column2"].Value = dataTable.Rows[i][1];
                        bulkInsertCommand.Parameters["@Column3"].Value = dataTable.Rows[i][2];
                        bulkInsertCommand.Parameters["@Column4"].Value = dataTable.Rows[i][3];
                        bulkInsertCommand.Parameters["@Column5"].Value = dataTable.Rows[i][4];
                        bulkInsertCommand.Parameters["@Column6"].Value = dataTable.Rows[i][5];
                
                        bulkInsertCommand.ExecuteNonQuery();
                    }
                }
                db.Close();
            }
        }

/// 

        /// 判断数据表是否存在
        /// 
        /// 
        /// 
        public static bool TableIsExist(String tableName)
        {
            try
            {
                SQLiteConnection db = ConnectToDatabase(SqLite1);

                using (SQLiteCommand cmd = db.CreateCommand())
                {
                    cmd.CommandText = "SELECT count(*) from sqlite_master where type='table' and name='" + tableName +
                                      "'; ";
                    if (Convert.ToInt32(cmd.ExecuteScalar()) == 0) // 不存在此数据表
                    {
                        return false;
                    }
                    else //存在此数据表，直接加数据
                    {
                        return true;
                    }

                }
            }
            catch
            {
                // ignored
            }

            
            return false;
        }


//创建一个连接到指定数据库
        public static SQLiteConnection ConnectToDatabase(string dbName)
        {
            _mDbConnection = new SQLiteConnection($"Data Source={dbName}.db;Version=3;"); //没有数据库则自动创建
            _mDbConnection.Open();
            return _mDbConnection;
        }

//读取excel
        public static DataSet ReadExcelToDataSet(string fileNmaePath)
        {
            FileStream stream = null;
            IExcelDataReader excelReader = null;
            DataSet dataSet = null;
            try
            {
                //stream = File.Open(fileNmaePath, FileMode.Open, FileAccess.Read);
                stream = new FileStream(fileNmaePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            }
            catch
            {
                MessageBox.Show("无法读取文件.");
                return null;
            }
            string extension = Path.GetExtension(fileNmaePath);

            if (extension.ToUpper() == ".XLS")
            {
                excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            }
            else if (extension.ToUpper() == ".XLSX")
            {
                excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            }
            else
            {
                MessageBox.Show("格式错误.");
                return null;

            }
            dataSet = excelReader.AsDataSet();//第一行当作数据读取
            
            excelReader.Close();
            return dataSet;
        }

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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156