C#中excel的导出和导入

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

实际的开发中，我们会经常遇到数据的转化的需要，将Excel中的数据转入到SQL中，或将SQL在数据库表中的数据导入到Excel中。代码如下：

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 = \; 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(\);

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(\);

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 = \oodType,BirthPlace,UserLevel,Education,Occupational,Contact,[Profile])\; strInsertComm += \;

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架构存入数据里