C#将SQL数据库中数据导入Excel中，并将Excel中反导入SQL数据库中

实际的开发中，我们会经常遇到数据的转化的需要，将Excel中的数据转入到SQL中，或将SQL在数据库表中的数据导入到Excel中。代码如下： Code using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using GemBox.ExcelLite; using System.Data.SqlClient; using System.Data.OleDb; using System.Collections; using System.IO; namespace Encryption { public partial class ExcelDemo : Form { private static string _filePath = string.Empty; public ExcelDemo() { InitializeComponent(); BindUser(); } /// /// 绑定数据 /// private void BindUser() { string sql = "select * from Users"; DataTable dt = DbHelperSQL.QueryTb(sql); dataGridView1.DataSource = dt; } /// /// 将Users表中的数据导入Excel中 /// private void btnExcelin_Click(object sender, EventArgs e) { ExcelFile excelFile = new ExcelFile(); ExcelWorksheet sheet = excelFile.Worksheets.Add("Users"); int columns = dataGridView1.Columns.Count; int rows = dataGridView1.Rows.Count; for (int j = 0; j < columns; j++) { sheet.Cells[0, j].Value = dataGridView1.Columns[j].HeaderText; } for (int i = 1; i < rows; i++) { for (int j = 0; j < columns; j++) { sheet.Cells[i, j].Value = dataGridView1[j, i - 1].Value.ToString().Trim(); } } excelFile.SaveXls("./Users.xls"); MessageBox.Show("生成成功"); } /// /// 选择要向SQL数据库中导入数据的Excel文件 /// private void btnChoose_Click(object sender, EventArgs e) { using (OpenFileDialog dialog = new OpenFileDialog()) { dialog.Multiselect = true; if (dialog.ShowDialog() == DialogResult.OK) { try { txtPath.Text = dialog.FileName; } catch { } } } } /// /// 将Excel中的数据导入到SQL数据库中 /// private void btnExcelout_Click(object sender, EventArgs e) { DataSet ds=ImportFromExcel(txtPath.Text.Trim()); DataTable dt = ds.Tables[0]; try { string strInsertComm; for (int i = 0; i < dt.Rows.Count; i++) { strInsertComm = ""; strInsertComm = "Insert INTO NewUsers(LoginNum,UserName,Password,Email,UserHead,Sex,BloodType,BirthPlace,UserLevel,Education,Occupational,Contact,[Profile])"; strInsertComm += " values("; for (int j = 1; j < dt.Columns.Count; j++) { if (j > 1) { strInsertComm += ",'" + dt.Rows[i][j].ToString().Trim() + "'"; } else { strInsertComm += "'" + dt.Rows[i][j].ToString().Trim() + "'"; } } strInsertComm += ")"; DbHelperSQL.ExecuteSql(strInsertComm); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } #region Excel导入SQL数据库 /// /// 获取Excel数据表列表 /// /// public static ArrayList GetExcelTables(string FilePath) { //将Excel架构存入数据里 System.Data.DataTable dt = new System.Data.DataTable(); ArrayList TablesList = new ArrayList(); if (File.Exists(FilePath)) { using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet." + "OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + FilePath)) { try { conn.Open(); dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); } catch (Exception exp) { MessageBox.Show(exp.Message); } //获取数据表个数 int tablecount = dt.Rows.Count; for (int i = 0; i < tablecount; i = i + 2) { string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$'); if (TablesList.IndexOf(tablename) < 0) { TablesList.Add(tablename); } } } } return TablesList; } /// /// 导入Excel数据表至DataTable（第一行作为表头） /// /// public static System.Data.DataSet FillDataSet(string FilePath) { if (!File.Exists(FilePath)) { throw new Exception("Excel文件不存在！"); } ArrayList TableList = new ArrayList(); TableList = GetExcelTables(FilePath); if (TableList.Count <= 0) { return null; } System.Data.DataTable table; System.Data.DataSet ds = new DataSet(); OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0"); try { if (dbcon.State == ConnectionState.Closed) { dbcon.Open(); } for (int i = 0; i < TableList.Count; i++) { string dtname = TableList[i].ToString(); try { OleDbCommand cmd = new OleDbCommand("select * from [" + dtname + "$]", dbcon); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); table = new DataTable(dtname); adapter.Fill(table); ds.Tables.Add(table); } catch (Exception exp) { MessageBox.Show(exp.Message); } } } finally { if (dbcon.State == ConnectionState.Open) { dbcon.Close(); } } return ds; } /// /// Excel导入数据库 /// /// public static DataSet ImportFromExcel(string FilePath) { return FillDataSet(FilePath); } #endregion } } 数据从SQL到Excel中利用了GemBox.ExcelLite.dll，网上可以下载。 代码很简单，希望大家有什么更好的方法，分享出来啊！有的时候可能需要对导入导出的数据加密，只需要定义一个加密函数，依次的对数据加密就ok了。

转载于:https://www.cnblogs.com/QQ765286788/p/4104984.html