.net实现将Excel中的数据导入数据库
阅读: 1240 评论: 24 作者: 飛雪飄寒 发表于 2009-12-23 16:32 原文链接
在近期的项目中需要实现将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;
}
}
}
}