刚才看了批量Excel数据导入Oracle数据这篇文章,也忍不住把我去年写的一个测试例子拿出来供大家分享。这个例子是我去年做的一个Web项目中后台里面用到的方法,其实很简单,就是上传Execl文件,然后读取数据写入数据库用到的。当时做的时候也网上搜了一些资料,倒是有一大堆的现成代码,但复制下来用的时候问题就出现了,就是在后台操作的时候上传的Excel只能读取到第一个工作表的内容,第二个以后的就读取不到了,也就是第二个工作表以后就导入不到数据库了,当是很是郁闷,最后还是在国外的一个论坛里面看到了一个网友给的解决方法,其实代码很是简单的。我也简单的封装了下,不多说了,直接贴代码吧,完事了睡觉了。。
代码
/*
*******************************************************************
filename: UpLoadExcelToSql.cs
created: 2009/04/16
author: liaoyun
purpose: 根据上传的Excel文件将数据导入数据库
******************************************************************** */
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Text;
using System.Data.SqlClient;
/// <summary>
/// UpLoadExcelToSql 的摘要说明
/// </summary>
public class UpLoadExcelToSql
{
private string conStringExcel; // excel连接字符串
private string excelFilePath; // Excel文件路径
// 连接字符串应该从配置文件获得
private string connectionString = " Data Source=A3441787DCE54FA\\SQL2005;Initial Catalog=Northwind;Integrated Security=True " ;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="excelFileName"> Excel文件名 </param>
public UpLoadExcelToSql( string excelFilePath)
{
this .excelFilePath = excelFilePath;
this .conStringExcel = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source =' " +
excelFilePath + " ';Extended Properties=Excel 8.0 " ;
}
/// <summary>
/// 获得Excel表中的表名
/// 值是类似这样的:Sheet1$表1$表2$表3$
/// </summary>
/// <returns> Excel文件所有工作表名(工作簿) </returns>
private List < string > ExcelSheetName()
{
List < string > sheetNames = new List < string > ();
string conString = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " +
excelFilePath + " ;Extended Properties=Excel 8.0; " ; // 连接字符串
using (OleDbConnection con = new OleDbConnection(conString))
{
con.Open();
DataTable sheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object [] { null , null , null , " table " });
con.Close();
foreach (DataRow var in sheetName.Rows)
{
sheetNames.Add(var[ 2 ].ToString());
}
}
return sheetNames;
}
/// <summary>
/// 执行插入操作
/// </summary>
/// <param name="sqls"> SQL语句集合 </param>
/// <returns> 成功true,失败false </returns>
private bool InsertExcelDataToSql(List < string > sqls)
{
bool flag = false ;
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
SqlTransaction tran = con.BeginTransaction();
SqlCommand cmd = new SqlCommand( "" , con, tran);
try
{
foreach ( string var in sqls)
{
cmd.CommandText = var;
cmd.ExecuteNonQuery();
}
tran.Commit();
flag = true ;
}
catch (Exception)
{
tran.Rollback();
}
}
return flag;
}
/// <summary>
/// 拼接SQL语句
/// </summary>
/// <param name="ds"> Excel文件数据集 </param>
/// <returns> SQL语句集 </returns>
/* *****此方法可以根据实际需要修改,只需要修改DataSet填充的汉字***** */
private List < string > CreateSqlFromExcel(DataSet ds)
{
List < string > sqls = new List < string > ();
for ( int i = 0 ; i < ds.Tables[ 0 ].Rows.Count; i ++ )
{
StringBuilder sql = new StringBuilder();
string isbn = ds.Tables[ 0 ].Rows[i][ " ISBN " ].ToString(); // ISBN号
string name = ds.Tables[ 0 ].Rows[i][ " 书名 " ].ToString(); // 书名
string cong_name = ds.Tables[ 0 ].Rows[i][ " 从书名 " ].ToString(); // 丛书名
string author = ds.Tables[ 0 ].Rows[i][ " 著者 " ].ToString(); // 著者
string pubAddress = ds.Tables[ 0 ].Rows[i][ " 出版地 " ].ToString(); // 出版地
string publish = ds.Tables[ 0 ].Rows[i][ " 出版社 " ].ToString(); // 出版社
string price = ds.Tables[ 0 ].Rows[i][ " 单价 " ].ToString(); // 单价
string reader = ds.Tables[ 0 ].Rows[i][ " 读者对象 " ].ToString(); // 读者对象
string format = ds.Tables[ 0 ].Rows[i][ " 开本 " ].ToString(); // 开本
string pageSize = ds.Tables[ 0 ].Rows[i][ " 页数 " ].ToString(); // 页数
string zhuangZhen = ds.Tables[ 0 ].Rows[i][ " 装帧 " ].ToString(); // 装帧
string content = ds.Tables[ 0 ].Rows[i][ " 内容简介 " ].ToString(); // 内容简介
string pubTime = ds.Tables[ 0 ].Rows[i][ " 出版时间 " ].ToString(); // 出版时间
string zhongType = ds.Tables[ 0 ].Rows[i][ " 中图法分类 " ].ToString(); // 中图法分类
string zhengDing = ds.Tables[ 0 ].Rows[i][ " 征订号 " ].ToString(); // 征订号
sql.Append( " insert into Book values ( " );
sql.AppendFormat( " '{0}','{1}','{2}', " , isbn, name, cong_name);
sql.AppendFormat( " '{0}','{1}','{2}', " , author, pubAddress, publish);
sql.AppendFormat( " '{0}','{1}','{2}', " , price, reader, format);
sql.AppendFormat( " '{0}','{1}','{2}', " , pageSize, zhuangZhen, content);
sql.AppendFormat( " '{0}','{1}','{2}') " , pubTime, zhongType, zhengDing);
sqls.Add(sql.ToString());
}
return sqls;
}
/// <summary>
/// 把Excel文件的数据导入到数据库
/// </summary>
/// <param name="fails"> 导入失败的表 </param>
/// <returns> 导入成功的表 </returns>
public List < string > InsertExcelDataToSql( out List < string > fails)
{
List < string > sheets = new List < string > ();
List < string > sheetNames = ExcelSheetName();
fails = new List < string > ();
OleDbConnection cnnxls = new OleDbConnection(conStringExcel);
for ( int i = 0 ; i < sheetNames.Count; i ++ )
{
DataSet ds = new DataSet();
OleDbDataAdapter myDa = new OleDbDataAdapter( " select * from [ " + sheetNames[i] + " ] " , cnnxls);
myDa.Fill(ds);
// 再调用一个方法拼接SQL
List < string > sqls = CreateSqlFromExcel(ds);
// 再调用一个方法插入数据库
if (InsertExcelDataToSql(sqls))
{
sheets.Add(sheetNames[i]);
}
else
{
fails.Add(sheetNames[i]);
}
}
return sheets;
}
/// <summary>
/// 已知Excel文件工作表名直接插入,但只能是一张表
/// </summary>
/// <param name="sheetName"> 工作表名 </param>
/// <returns> 成功true失败false </returns>
public bool InsertExcelDataToSql( string sheetName)
{
bool flag = false ;
DataSet ds = new DataSet();
OleDbConnection con = new OleDbConnection(conStringExcel);
OleDbDataAdapter myDa = new OleDbDataAdapter( " select * from [ " + sheetName + " $] " , con);
myDa.Fill(ds);
con.Close();
List < string > sqls = CreateSqlFromExcel(ds);
return flag = InsertExcelDataToSql(sqls);
}
}
filename: UpLoadExcelToSql.cs
created: 2009/04/16
author: liaoyun
purpose: 根据上传的Excel文件将数据导入数据库
******************************************************************** */
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Text;
using System.Data.SqlClient;
/// <summary>
/// UpLoadExcelToSql 的摘要说明
/// </summary>
public class UpLoadExcelToSql
{
private string conStringExcel; // excel连接字符串
private string excelFilePath; // Excel文件路径
// 连接字符串应该从配置文件获得
private string connectionString = " Data Source=A3441787DCE54FA\\SQL2005;Initial Catalog=Northwind;Integrated Security=True " ;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="excelFileName"> Excel文件名 </param>
public UpLoadExcelToSql( string excelFilePath)
{
this .excelFilePath = excelFilePath;
this .conStringExcel = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source =' " +
excelFilePath + " ';Extended Properties=Excel 8.0 " ;
}
/// <summary>
/// 获得Excel表中的表名
/// 值是类似这样的:Sheet1$表1$表2$表3$
/// </summary>
/// <returns> Excel文件所有工作表名(工作簿) </returns>
private List < string > ExcelSheetName()
{
List < string > sheetNames = new List < string > ();
string conString = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " +
excelFilePath + " ;Extended Properties=Excel 8.0; " ; // 连接字符串
using (OleDbConnection con = new OleDbConnection(conString))
{
con.Open();
DataTable sheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object [] { null , null , null , " table " });
con.Close();
foreach (DataRow var in sheetName.Rows)
{
sheetNames.Add(var[ 2 ].ToString());
}
}
return sheetNames;
}
/// <summary>
/// 执行插入操作
/// </summary>
/// <param name="sqls"> SQL语句集合 </param>
/// <returns> 成功true,失败false </returns>
private bool InsertExcelDataToSql(List < string > sqls)
{
bool flag = false ;
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
SqlTransaction tran = con.BeginTransaction();
SqlCommand cmd = new SqlCommand( "" , con, tran);
try
{
foreach ( string var in sqls)
{
cmd.CommandText = var;
cmd.ExecuteNonQuery();
}
tran.Commit();
flag = true ;
}
catch (Exception)
{
tran.Rollback();
}
}
return flag;
}
/// <summary>
/// 拼接SQL语句
/// </summary>
/// <param name="ds"> Excel文件数据集 </param>
/// <returns> SQL语句集 </returns>
/* *****此方法可以根据实际需要修改,只需要修改DataSet填充的汉字***** */
private List < string > CreateSqlFromExcel(DataSet ds)
{
List < string > sqls = new List < string > ();
for ( int i = 0 ; i < ds.Tables[ 0 ].Rows.Count; i ++ )
{
StringBuilder sql = new StringBuilder();
string isbn = ds.Tables[ 0 ].Rows[i][ " ISBN " ].ToString(); // ISBN号
string name = ds.Tables[ 0 ].Rows[i][ " 书名 " ].ToString(); // 书名
string cong_name = ds.Tables[ 0 ].Rows[i][ " 从书名 " ].ToString(); // 丛书名
string author = ds.Tables[ 0 ].Rows[i][ " 著者 " ].ToString(); // 著者
string pubAddress = ds.Tables[ 0 ].Rows[i][ " 出版地 " ].ToString(); // 出版地
string publish = ds.Tables[ 0 ].Rows[i][ " 出版社 " ].ToString(); // 出版社
string price = ds.Tables[ 0 ].Rows[i][ " 单价 " ].ToString(); // 单价
string reader = ds.Tables[ 0 ].Rows[i][ " 读者对象 " ].ToString(); // 读者对象
string format = ds.Tables[ 0 ].Rows[i][ " 开本 " ].ToString(); // 开本
string pageSize = ds.Tables[ 0 ].Rows[i][ " 页数 " ].ToString(); // 页数
string zhuangZhen = ds.Tables[ 0 ].Rows[i][ " 装帧 " ].ToString(); // 装帧
string content = ds.Tables[ 0 ].Rows[i][ " 内容简介 " ].ToString(); // 内容简介
string pubTime = ds.Tables[ 0 ].Rows[i][ " 出版时间 " ].ToString(); // 出版时间
string zhongType = ds.Tables[ 0 ].Rows[i][ " 中图法分类 " ].ToString(); // 中图法分类
string zhengDing = ds.Tables[ 0 ].Rows[i][ " 征订号 " ].ToString(); // 征订号
sql.Append( " insert into Book values ( " );
sql.AppendFormat( " '{0}','{1}','{2}', " , isbn, name, cong_name);
sql.AppendFormat( " '{0}','{1}','{2}', " , author, pubAddress, publish);
sql.AppendFormat( " '{0}','{1}','{2}', " , price, reader, format);
sql.AppendFormat( " '{0}','{1}','{2}', " , pageSize, zhuangZhen, content);
sql.AppendFormat( " '{0}','{1}','{2}') " , pubTime, zhongType, zhengDing);
sqls.Add(sql.ToString());
}
return sqls;
}
/// <summary>
/// 把Excel文件的数据导入到数据库
/// </summary>
/// <param name="fails"> 导入失败的表 </param>
/// <returns> 导入成功的表 </returns>
public List < string > InsertExcelDataToSql( out List < string > fails)
{
List < string > sheets = new List < string > ();
List < string > sheetNames = ExcelSheetName();
fails = new List < string > ();
OleDbConnection cnnxls = new OleDbConnection(conStringExcel);
for ( int i = 0 ; i < sheetNames.Count; i ++ )
{
DataSet ds = new DataSet();
OleDbDataAdapter myDa = new OleDbDataAdapter( " select * from [ " + sheetNames[i] + " ] " , cnnxls);
myDa.Fill(ds);
// 再调用一个方法拼接SQL
List < string > sqls = CreateSqlFromExcel(ds);
// 再调用一个方法插入数据库
if (InsertExcelDataToSql(sqls))
{
sheets.Add(sheetNames[i]);
}
else
{
fails.Add(sheetNames[i]);
}
}
return sheets;
}
/// <summary>
/// 已知Excel文件工作表名直接插入,但只能是一张表
/// </summary>
/// <param name="sheetName"> 工作表名 </param>
/// <returns> 成功true失败false </returns>
public bool InsertExcelDataToSql( string sheetName)
{
bool flag = false ;
DataSet ds = new DataSet();
OleDbConnection con = new OleDbConnection(conStringExcel);
OleDbDataAdapter myDa = new OleDbDataAdapter( " select * from [ " + sheetName + " $] " , con);
myDa.Fill(ds);
con.Close();
List < string > sqls = CreateSqlFromExcel(ds);
return flag = InsertExcelDataToSql(sqls);
}
}
暂时还不知道博客园怎么上传附件,本想上传我那个测试例子给大家分享的,很晚了,就睡觉了吧,需要的话,直接email联系吧。