.net实现将Excel中的数据导入数据库 ---转 飛雪飄寒
在近期的项目中需要实现将Excel中的数据导入到数据库的功能，相信各位博友也会经常有这样的需求，现把我的实现方式与大家分享一下，如果哪位博友有更好的实现方式，欢迎补充！
实现思路：先上传Excel文件，然后用OleDB连接Excel文件，将Excel数据导入Dataset，再将Dataset数据导入数据库，最后再将上传的Excel文件删除。
前台代码：
实现思路：先上传Excel文件，然后用OleDB连接Excel文件，将Excel数据导入Dataset，再将Dataset数据导入数据库，最后再将上传的Excel文件删除。
前台代码：
<table class="Text" cellSpacing="1" cellPadding="0" width="100%" bgColor="#1d82d0" border="0">
<tr bgColor="#ffffff">
<td vAlign="top">
<table class="Text" cellSpacing="0" cellPadding="0" width="100%" border="0">
<tr>
<td width="15"> </td>
<td vAlign="top" width="100%">
<table class="Text" cellSpacing="1" cellPadding="0" width="100%" border="0">
<tr height="30">
<td style="WIDTH: 120px" width="120"><FONT face="宋体">请选择要导入的文件</FONT></td>
<td style="WIDTH: 350px" align="left" width="350"><INPUT id="FileExcel" style="WIDTH: 300px" type="file" size="42" name="FilePhoto" runat="server"><FONT color="red"></FONT></td>
<td class="hint"><FONT face="宋体"><asp:button id="BtnImport" Text="导 入" CssClass="button" Runat="server"></asp:button></FONT></td>
</tr>
</table>
</td>
</tr>
</table>
<asp:label id="LblMessage" runat="server" Font-Bold="True" ForeColor="Red"></asp:label>
</td>
</tr>
</table>
<tr bgColor="#ffffff">
<td vAlign="top">
<table class="Text" cellSpacing="0" cellPadding="0" width="100%" border="0">
<tr>
<td width="15"> </td>
<td vAlign="top" width="100%">
<table class="Text" cellSpacing="1" cellPadding="0" width="100%" border="0">
<tr height="30">
<td style="WIDTH: 120px" width="120"><FONT face="宋体">请选择要导入的文件</FONT></td>
<td style="WIDTH: 350px" align="left" width="350"><INPUT id="FileExcel" style="WIDTH: 300px" type="file" size="42" name="FilePhoto" runat="server"><FONT color="red"></FONT></td>
<td class="hint"><FONT face="宋体"><asp:button id="BtnImport" Text="导 入" CssClass="button" Runat="server"></asp:button></FONT></td>
</tr>
</table>
</td>
</tr>
</table>
<asp:label id="LblMessage" runat="server" Font-Bold="True" ForeColor="Red"></asp:label>
</td>
</tr>
</table>
后台代码：
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Text;
namespace GZPI.Service.AgenciesChannel
{
/// <summary>
/// ImportXlsToData 的摘要说明。
/// </summary>
public class ImportXlsToDataBase : GZPI.Service.AgenciesChannel.AgenciesBasePage
{
protected System.Web.UI.HtmlControls.HtmlInputFile FileExcel;
protected System.Web.UI.WebControls.Button BtnImport;
protected System.Web.UI.WebControls.Label LblMessage;
protected ZsoftDataAccess.DataAccess _da = new ZsoftDataAccess.DataAccess();
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.BtnImport.Click += new System.EventHandler(this.BtnImport_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
//// <summary>
/// 从Excel提取数据--》Dataset
/// </summary>
/// <param name="filename">Excel文件路径名</param>
private void ImportXlsToData(string fileName)
{
try
{
if (fileName == string.Empty)
{
throw new ArgumentNullException("Excel文件上传失败！");
}
string oleDBConnString = String.Empty;
oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;";
oleDBConnString += "Data Source=";
oleDBConnString += fileName;
oleDBConnString += ";Extended Properties=Excel 8.0;";
OleDbConnection oleDBConn = null;
OleDbDataAdapter oleAdMaster = null;
DataTable m_tableName=new DataTable();
DataSet ds=new DataSet();
oleDBConn = new OleDbConnection(oleDBConnString);
oleDBConn.Open();
m_tableName=oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
if (m_tableName != null && m_tableName.Rows.Count > 0)
{
m_tableName.TableName =m_tableName.Rows[0]["TABLE_NAME"].ToString();
}
string sqlMaster;
sqlMaster=" SELECT * FROM ["+m_tableName.TableName+"]";
oleAdMaster=new OleDbDataAdapter(sqlMaster,oleDBConn);
oleAdMaster.Fill(ds,"m_tableName");
oleAdMaster.Dispose();
oleDBConn.Close();
oleDBConn.Dispose();
AddDatasetToSQL(ds,14);
}
catch(Exception ex)
{
throw ex;
}
}
/// <summary>
/// 上传Excel文件
/// </summary>
/// <param name="inputfile">上传的控件名</param>
/// <returns></returns>
private string UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
{
string orifilename = string.Empty;
string uploadfilepath = string.Empty;
string modifyfilename = string.Empty;
string fileExtend = "" ;//文件扩展名
int fileSize = 0;//文件大小
try
{
if(inputfile.Value != string.Empty)
{
//得到文件的大小
fileSize = inputfile.PostedFile.ContentLength;
if(fileSize == 0 )
{
throw new Exception("导入的Excel文件大小为0，请检查是否正确！");
}
//得到扩展名
fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1);
if(fileExtend.ToLower() != "xls")
{
throw new Exception("你选择的文件格式不正确，只能导入EXCEL文件！");
}
//路径
uploadfilepath = Server.MapPath("~/Service/GraduateChannel/GraduateApply/ImgUpLoads");
//新文件名
modifyfilename = System.Guid.NewGuid().ToString();
modifyfilename += "."+inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1);
//判断是否有该目录
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
if (!dir.Exists)
{
dir.Create();
}
orifilename = uploadfilepath+"\\"+modifyfilename;
//如果存在,删除文件
if(File.Exists(orifilename))
{
File.Delete(orifilename);
}
// 上传文件
inputfile.PostedFile.SaveAs(orifilename);
}
else
{
throw new Exception("请选择要导入的Excel文件!");
}
}
catch(Exception ex)
{
throw ex;
}
return orifilename;
}
/// <summary>
/// 将Dataset的数据导入数据库
/// </summary>
/// <param name="pds">数据集</param>
/// <param name="Cols">数据集列数</param>
/// <returns></returns>
private bool AddDatasetToSQL(DataSet pds,int Cols)
{
int ic,ir;
ic = pds.Tables[0].Columns.Count;
if (pds.Tables[0].Columns.Count < Cols)
{
throw new Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");
}
ir = pds.Tables[0].Rows.Count;
if (pds != null && pds.Tables[0].Rows.Count > 0)
{
for (int i = 1;i < pds.Tables[0].Rows.Count;i++)
{
Add(pds.Tables[0].Rows[i][1].ToString(),
pds.Tables[0].Rows[i][2].ToString(),pds.Tables[0].Rows[i][3].ToString(),
pds.Tables[0].Rows[i][4].ToString(),pds.Tables[0].Rows[i][5].ToString(),
pds.Tables[0].Rows[i][6].ToString(),pds.Tables[0].Rows[i][7].ToString(),
pds.Tables[0].Rows[i][8].ToString(),pds.Tables[0].Rows[i][9].ToString(),
pds.Tables[0].Rows[i][10].ToString(),pds.Tables[0].Rows[i][11].ToString(),
pds.Tables[0].Rows[i][12].ToString(),pds.Tables[0].Rows[i][13].ToString());
}
}
else
{
throw new Exception("导入数据为空！");
}
return true;
}
/// <summary>
/// 插入数据到数据库
/// </summary>
public void Add(string B0105,string SequenceNumber,string A0101,string OldGuid,string RecordType,string BirthDay,string A0177,string MobliePhone,string TelePhone,string ContractBeginDate,string ContractEndDate,string ContractPayCharge,string TransactDate)
{
string sql="select * from PersonRecord where A0177='"+A0177+"'";
DataTable dt=_da.ExecuteDataTable(sql);
if(dt.Rows.Count==0)
{
//insert into PersonRecord
StringBuilder strSql=new StringBuilder();
strSql.Append("insert into PersonRecord(");
strSql.Append("ID,B0105,SequenceNumber,A0101,OldGuid,RecordType,BirthDay,A0177,MobliePhone,TelePhone,ContractBeginDate,ContractEndDate,ContractPayCharge,TransactDate");
strSql.Append(")");
strSql.Append(" values (");
strSql.Append("'"+System.Guid.NewGuid().ToString()+"',");
strSql.Append("'"+B0105+"',");
strSql.Append("'"+SequenceNumber+"',");
strSql.Append("'"+A0101+"',");
strSql.Append("'"+OldGuid+"',");
strSql.Append("'"+RecordType+"',");
strSql.Append("'"+BirthDay+"',");
strSql.Append("'"+A0177+"',");
strSql.Append("'"+MobliePhone+"',");
strSql.Append("'"+TelePhone+"',");
strSql.Append("'"+ContractBeginDate+"',");
strSql.Append("'"+ContractEndDate+"',");
strSql.Append("'"+ContractPayCharge+"',");
strSql.Append("'"+TransactDate+"'");
strSql.Append(")");
//insert into PersonnelAgencyInfo
string GUID=System.Guid.NewGuid().ToString();
strSql.Append(" insert into PersonnelAgencyInfo(");
strSql.Append("PersonnelAgencyID, A0101, A0177, PersonnelAgencyState, PersonnelAgencyStateCode, Checker, CheckTime");
strSql.Append(")");
strSql.Append(" values (");
strSql.Append("'"+GUID+"',");
strSql.Append("'"+A0101+"',");
strSql.Append("'"+A0177+"',");
strSql.Append("'通过',");
strSql.Append("'1',");
strSql.Append("'"+GZPI.Service.Common.AuthManager.CurrentUserNameCN.ToString()+"',");
strSql.Append("'"+DateTime.Now.ToString()+"'");
strSql.Append(")");
//insert into PersonnelAgencyRecord
strSql.Append(" insert into PersonnelAgencyRecord(");
strSql.Append("PersonnelAgencyRecordID, PersonnelAgencyID, PersonnelAgencyState, Checker, CheckTime");
strSql.Append(")");
strSql.Append(" values (");
strSql.Append("'"+System.Guid.NewGuid().ToString()+"',");
strSql.Append("'"+GUID+"',");
strSql.Append("'通过',");
strSql.Append("'系统导入数据',");
strSql.Append("'"+DateTime.Now.ToString()+"'");
strSql.Append(")");
_da.ExecuteNonQuery(strSql.ToString());
}
}
private void BtnImport_Click(object sender, System.EventArgs e)
{
string filename = string.Empty;
try
{
filename = UpLoadXls(FileExcel);//上传XLS文件
ImportXlsToData(filename);//将XLS文件的数据导入数据库
if (filename != string.Empty && System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);//删除上传的XLS文件
}
LblMessage.Text="数据导入成功！";
}
catch(Exception ex)
{
LblMessage.Text=ex.Message;
}
}
}
}
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Text;
namespace GZPI.Service.AgenciesChannel
{
/// <summary>
/// ImportXlsToData 的摘要说明。
/// </summary>
public class ImportXlsToDataBase : GZPI.Service.AgenciesChannel.AgenciesBasePage
{
protected System.Web.UI.HtmlControls.HtmlInputFile FileExcel;
protected System.Web.UI.WebControls.Button BtnImport;
protected System.Web.UI.WebControls.Label LblMessage;
protected ZsoftDataAccess.DataAccess _da = new ZsoftDataAccess.DataAccess();
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.BtnImport.Click += new System.EventHandler(this.BtnImport_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
//// <summary>
/// 从Excel提取数据--》Dataset
/// </summary>
/// <param name="filename">Excel文件路径名</param>
private void ImportXlsToData(string fileName)
{
try
{
if (fileName == string.Empty)
{
throw new ArgumentNullException("Excel文件上传失败！");
}
string oleDBConnString = String.Empty;
oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;";
oleDBConnString += "Data Source=";
oleDBConnString += fileName;
oleDBConnString += ";Extended Properties=Excel 8.0;";
OleDbConnection oleDBConn = null;
OleDbDataAdapter oleAdMaster = null;
DataTable m_tableName=new DataTable();
DataSet ds=new DataSet();
oleDBConn = new OleDbConnection(oleDBConnString);
oleDBConn.Open();
m_tableName=oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
if (m_tableName != null && m_tableName.Rows.Count > 0)
{
m_tableName.TableName =m_tableName.Rows[0]["TABLE_NAME"].ToString();
}
string sqlMaster;
sqlMaster=" SELECT * FROM ["+m_tableName.TableName+"]";
oleAdMaster=new OleDbDataAdapter(sqlMaster,oleDBConn);
oleAdMaster.Fill(ds,"m_tableName");
oleAdMaster.Dispose();
oleDBConn.Close();
oleDBConn.Dispose();
AddDatasetToSQL(ds,14);
}
catch(Exception ex)
{
throw ex;
}
}
/// <summary>
/// 上传Excel文件
/// </summary>
/// <param name="inputfile">上传的控件名</param>
/// <returns></returns>
private string UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
{
string orifilename = string.Empty;
string uploadfilepath = string.Empty;
string modifyfilename = string.Empty;
string fileExtend = "" ;//文件扩展名
int fileSize = 0;//文件大小
try
{
if(inputfile.Value != string.Empty)
{
//得到文件的大小
fileSize = inputfile.PostedFile.ContentLength;
if(fileSize == 0 )
{
throw new Exception("导入的Excel文件大小为0，请检查是否正确！");
}
//得到扩展名
fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1);
if(fileExtend.ToLower() != "xls")
{
throw new Exception("你选择的文件格式不正确，只能导入EXCEL文件！");
}
//路径
uploadfilepath = Server.MapPath("~/Service/GraduateChannel/GraduateApply/ImgUpLoads");
//新文件名
modifyfilename = System.Guid.NewGuid().ToString();
modifyfilename += "."+inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1);
//判断是否有该目录
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
if (!dir.Exists)
{
dir.Create();
}
orifilename = uploadfilepath+"\\"+modifyfilename;
//如果存在,删除文件
if(File.Exists(orifilename))
{
File.Delete(orifilename);
}
// 上传文件
inputfile.PostedFile.SaveAs(orifilename);
}
else
{
throw new Exception("请选择要导入的Excel文件!");
}
}
catch(Exception ex)
{
throw ex;
}
return orifilename;
}
/// <summary>
/// 将Dataset的数据导入数据库
/// </summary>
/// <param name="pds">数据集</param>
/// <param name="Cols">数据集列数</param>
/// <returns></returns>
private bool AddDatasetToSQL(DataSet pds,int Cols)
{
int ic,ir;
ic = pds.Tables[0].Columns.Count;
if (pds.Tables[0].Columns.Count < Cols)
{
throw new Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");
}
ir = pds.Tables[0].Rows.Count;
if (pds != null && pds.Tables[0].Rows.Count > 0)
{
for (int i = 1;i < pds.Tables[0].Rows.Count;i++)
{
Add(pds.Tables[0].Rows[i][1].ToString(),
pds.Tables[0].Rows[i][2].ToString(),pds.Tables[0].Rows[i][3].ToString(),
pds.Tables[0].Rows[i][4].ToString(),pds.Tables[0].Rows[i][5].ToString(),
pds.Tables[0].Rows[i][6].ToString(),pds.Tables[0].Rows[i][7].ToString(),
pds.Tables[0].Rows[i][8].ToString(),pds.Tables[0].Rows[i][9].ToString(),
pds.Tables[0].Rows[i][10].ToString(),pds.Tables[0].Rows[i][11].ToString(),
pds.Tables[0].Rows[i][12].ToString(),pds.Tables[0].Rows[i][13].ToString());
}
}
else
{
throw new Exception("导入数据为空！");
}
return true;
}
/// <summary>
/// 插入数据到数据库
/// </summary>
public void Add(string B0105,string SequenceNumber,string A0101,string OldGuid,string RecordType,string BirthDay,string A0177,string MobliePhone,string TelePhone,string ContractBeginDate,string ContractEndDate,string ContractPayCharge,string TransactDate)
{
string sql="select * from PersonRecord where A0177='"+A0177+"'";
DataTable dt=_da.ExecuteDataTable(sql);
if(dt.Rows.Count==0)
{
//insert into PersonRecord
StringBuilder strSql=new StringBuilder();
strSql.Append("insert into PersonRecord(");
strSql.Append("ID,B0105,SequenceNumber,A0101,OldGuid,RecordType,BirthDay,A0177,MobliePhone,TelePhone,ContractBeginDate,ContractEndDate,ContractPayCharge,TransactDate");
strSql.Append(")");
strSql.Append(" values (");
strSql.Append("'"+System.Guid.NewGuid().ToString()+"',");
strSql.Append("'"+B0105+"',");
strSql.Append("'"+SequenceNumber+"',");
strSql.Append("'"+A0101+"',");
strSql.Append("'"+OldGuid+"',");
strSql.Append("'"+RecordType+"',");
strSql.Append("'"+BirthDay+"',");
strSql.Append("'"+A0177+"',");
strSql.Append("'"+MobliePhone+"',");
strSql.Append("'"+TelePhone+"',");
strSql.Append("'"+ContractBeginDate+"',");
strSql.Append("'"+ContractEndDate+"',");
strSql.Append("'"+ContractPayCharge+"',");
strSql.Append("'"+TransactDate+"'");
strSql.Append(")");
//insert into PersonnelAgencyInfo
string GUID=System.Guid.NewGuid().ToString();
strSql.Append(" insert into PersonnelAgencyInfo(");
strSql.Append("PersonnelAgencyID, A0101, A0177, PersonnelAgencyState, PersonnelAgencyStateCode, Checker, CheckTime");
strSql.Append(")");
strSql.Append(" values (");
strSql.Append("'"+GUID+"',");
strSql.Append("'"+A0101+"',");
strSql.Append("'"+A0177+"',");
strSql.Append("'通过',");
strSql.Append("'1',");
strSql.Append("'"+GZPI.Service.Common.AuthManager.CurrentUserNameCN.ToString()+"',");
strSql.Append("'"+DateTime.Now.ToString()+"'");
strSql.Append(")");
//insert into PersonnelAgencyRecord
strSql.Append(" insert into PersonnelAgencyRecord(");
strSql.Append("PersonnelAgencyRecordID, PersonnelAgencyID, PersonnelAgencyState, Checker, CheckTime");
strSql.Append(")");
strSql.Append(" values (");
strSql.Append("'"+System.Guid.NewGuid().ToString()+"',");
strSql.Append("'"+GUID+"',");
strSql.Append("'通过',");
strSql.Append("'系统导入数据',");
strSql.Append("'"+DateTime.Now.ToString()+"'");
strSql.Append(")");
_da.ExecuteNonQuery(strSql.ToString());
}
}
private void BtnImport_Click(object sender, System.EventArgs e)
{
string filename = string.Empty;
try
{
filename = UpLoadXls(FileExcel);//上传XLS文件
ImportXlsToData(filename);//将XLS文件的数据导入数据库
if (filename != string.Empty && System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);//删除上传的XLS文件
}
LblMessage.Text="数据导入成功！";
}
catch(Exception ex)
{
LblMessage.Text=ex.Message;
}
}
}
}
