C#將SQL資料庫中資料匯入Excel中，並將Excel中反匯入SQL資料庫中

標籤：datagridview   io   ar   os   sp   for   檔案   資料   on   

實際的開發中，我們會經常遇到資料的轉化的需要，將Excel中的資料轉入到SQL中，或將SQL在資料庫表中的資料匯入到Excel中。代碼如下：Codeusing 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了。

C#將SQL資料庫中資料匯入Excel中，並將Excel中反匯入SQL資料庫中