nuget needs to be loaded
EPPlus.Core
ExcelDataReader
ExcelDataReader.DataSet
//Extensions that need to be quoted using ExcelDataReader; using ExcelPackage = OfficeOpenXml.ExcelPackage; public static void CreateZhouPianChaTable() {<!-- --> string tbname = "zhou_pian_cha1"; //Determine whether the table exists 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#\axis limit deviation.xlsx"; //Another way to read excel into datatable // using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFilePath))) {<!-- --> // ExcelWorksheet worksheet = package.Workbook.Worksheets[1]; // // // Read data into 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) "; //Insert data 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> /// Determine whether the data table exists /// </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) // This data table does not exist {<!-- --> return false; } else //This data table exists, add data directly {<!-- --> return true; } } } catch {<!-- --> // ignored } return false; } //Create a connection to the specified database public static SQLiteConnection ConnectToDatabase(string dbName) {<!-- --> _mDbConnection = new SQLiteConnection($"Data Source={<!-- -->dbName}.db;Version=3;"); //If there is no database, it will be automatically created _mDbConnection.Open(); return _mDbConnection; } //Read 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("Unable to read file."); 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("Format error."); return null; } dataSet = excelReader.AsDataSet();//The first row is read as data excelReader.Close(); return dataSet; }