c# 将excel导入 sqlite

excel导入 sqlite"/>

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();}}/// <summary>/// 判断数据表是否存在/// </summary>/// <param name="tableName"></param>/// <returns></returns>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;}//读取excelpublic 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;}