C#将SQL数据库中数据导入Excel中，并将Excel中反导入SQL数据库中
实际的开发中，我们会经常遇到数据的转化的需要，将Excel中的数据转入到SQL中，或将SQL在数据库表中的数据导入到Excel中。代码如下：
<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->usingSystem;
usingSystem.Collections.Generic;
usingSystem.ComponentModel;
usingSystem.Data;
usingSystem.Drawing;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Windows.Forms;
usingGemBox.ExcelLite;
usingSystem.Data.SqlClient;
usingSystem.Data.OleDb;
usingSystem.Collections;
usingSystem.IO;
namespaceEncryption
{
publicpartialclassExcelDemo:Form
{
privatestaticstring_filePath=string.Empty;
publicExcelDemo()
{
InitializeComponent();
BindUser();
}
///<summary>
///绑定数据
///</summary>
privatevoidBindUser()
{
stringsql="select*fromUsers";
DataTabledt=DbHelperSQL.QueryTb(sql);
dataGridView1.DataSource=dt;
}
///<summary>
///将Users表中的数据导入Excel中
///</summary>
privatevoidbtnExcelin_Click(objectsender,EventArgse)
{
ExcelFileexcelFile=newExcelFile();
ExcelWorksheetsheet=excelFile.Worksheets.Add("Users");
intcolumns=dataGridView1.Columns.Count;
introws=dataGridView1.Rows.Count;
for(intj=0;j<columns;j++)
{
sheet.Cells[0,j].Value=dataGridView1.Columns[j].HeaderText;
}
for(inti=1;i<rows;i++)
{
for(intj=0;j<columns;j++)
{
sheet.Cells[i,j].Value=dataGridView1[j,i-1].Value.ToString().Trim();
}
}
excelFile.SaveXls("./Users.xls");
MessageBox.Show("生成成功");
}
///<summary>
///选择要向SQL数据库中导入数据的Excel文件
///</summary>
privatevoidbtnChoose_Click(objectsender,EventArgse)
{
using(OpenFileDialogdialog=newOpenFileDialog())
{
dialog.Multiselect=true;
if(dialog.ShowDialog()==DialogResult.OK)
{
try
{
txtPath.Text=dialog.FileName;
}
catch{}
}
}
}
///<summary>
///将Excel中的数据导入到SQL数据库中
///</summary>
privatevoidbtnExcelout_Click(objectsender,EventArgse)
{
DataSetds=ImportFromExcel(txtPath.Text.Trim());
DataTabledt=ds.Tables[0];
try
{
stringstrInsertComm;
for(inti=0;i<dt.Rows.Count;i++)
{
strInsertComm="";
strInsertComm="InsertINTONewUsers(LoginNum,UserName,Password,Email,UserHead,Sex,BloodType,BirthPlace,UserLevel,Education,Occupational,Contact,[Profile])";
strInsertComm+="values(";
for(intj=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(Exceptionex)
{
MessageBox.Show(ex.Message);
}
}
#regionExcel导入SQL数据库
///<summary>
///获取Excel数据表列表
///</summary>
///<returns></returns>
publicstaticArrayListGetExcelTables(stringFilePath)
{
//将Excel架构存入数据里
System.Data.DataTabledt=newSystem.Data.DataTable();
ArrayListTablesList=newArrayList();
if(File.Exists(FilePath))
{
using(OleDbConnectionconn=newOleDbConnection("Provider=Microsoft.Jet."+
"OLEDB.4.0;ExtendedProperties=\"Excel8.0\";DataSource="+FilePath))
{
try
{
conn.Open();
dt=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,newobject[]{null,null,null,"TABLE"});
}
catch(Exceptionexp)
{
MessageBox.Show(exp.Message);
}
//获取数据表个数
inttablecount=dt.Rows.Count;
for(inti=0;i<tablecount;i=i+2)
{
stringtablename=dt.Rows[i][2].ToString().Trim().TrimEnd('$');
if(TablesList.IndexOf(tablename)<0)
{
TablesList.Add(tablename);
}
}
}
}
returnTablesList;
}
///<summary>
///导入Excel数据表至DataTable（第一行作为表头）
///</summary>
///<returns></returns>
publicstaticSystem.Data.DataSetFillDataSet(stringFilePath)
{
if(!File.Exists(FilePath))
{
thrownewException("Excel文件不存在！");
}
ArrayListTableList=newArrayList();
TableList=GetExcelTables(FilePath);
if(TableList.Count<=0)
{
returnnull;
}
System.Data.DataTabletable;
System.Data.DataSetds=newDataSet();
OleDbConnectiondbcon=newOleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+FilePath+";ExtendedProperties=Excel8.0");
try
{
if(dbcon.State==ConnectionState.Closed)
{
dbcon.Open();
}
for(inti=0;i<TableList.Count;i++)
{
stringdtname=TableList[i].ToString();
try
{
OleDbCommandcmd=newOleDbCommand("select*from["+dtname+"$]",dbcon);
OleDbDataAdapteradapter=newOleDbDataAdapter(cmd);
table=newDataTable(dtname);
adapter.Fill(table);
ds.Tables.Add(table);
}
catch(Exceptionexp)
{
MessageBox.Show(exp.Message);
}
}
}
finally
{
if(dbcon.State==ConnectionState.Open)
{
dbcon.Close();
}
}
returnds;
}
///<summary>
///Excel导入数据库
///</summary>
///<returns></returns>
publicstaticDataSetImportFromExcel(stringFilePath)
{
returnFillDataSet(FilePath);
}
#endregion
}
}
代码很简单，希望大家有什么更好的方法，分享出来啊！有的时候可能需要对导入导出的数据加密，只需要定义一个加密函数，依次的对数据加密就ok了。