本文實(shí)例講述了asp.net實(shí)現(xiàn)將Excel中多個(gè)sheet數(shù)據(jù)導(dǎo)入到SQLSERVER中的方法。分享給大家供大家參考，具體如下：
public DataSet GetDataSet(string filePath)
{
string Connstr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");
OleDbConnection Conn = new OleDbConnection(Connstr);
//創(chuàng)建ArrayList對(duì)象 存放所有sheetname
ArrayList sheetNamelist = new ArrayList();
//獲取配置Excel中sheet總數(shù)(這里是根據(jù)項(xiàng)目需求配置的) 如果需要導(dǎo)入Excel表格所有sheet數(shù)據(jù)則將此代碼刪除
int sheetCount = Convert.ToInt32(ConfigurationManager.AppSettings["sheetCount"].ToString());
DataSet dsExcel = new DataSet();
try
{
if (Conn.State == ConnectionState.Closed)
{
Conn.Open();
}
DataTable dtExcelSchema = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
string sheetName = string.Empty;
if (dtExcelSchema.Rows.Count > sheetCount)
{
Page.RegisterStartupScript("", "mce:script type="text/javascript">!--
alert('很抱歉!你上傳Excel文件sheet總數(shù)過(guò)多不能大于10個(gè)sheet..!! ')
// -->/mce:script>");
return;
}
else
{
for (int j = 0; j dtExcelSchema.Rows.Count; j++)
{
sheetName = String.Format("Sheet{0}$", j + 1);
sheetNamelist.Add(sheetName);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString(), ex);
}
finally
{
Conn.Close();
}
try
{
string strSQL = string.Empty;
for (int i = 0; i sheetNamelist.Count; i++)
{
strSQL = "select * from [" + sheetNamelist[i].ToString() + "]";
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, Conn);
DataTable dtExcel = new DataTable(sheetNamelist[i].ToString());
da.Fill(dtExcel);
dsExcel.Tables.Add(dtExcel);
}
return dsExcel;
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString(), ex);
}
}
//從Excel 表中取出數(shù)據(jù) 將取出來(lái)的數(shù)據(jù)插入到數(shù)據(jù)庫(kù)中
public void InsertData(DataSet ds) {
string strSQL=string.Empty;
if (ds.Tables[0].Rows.Count > 0)
{
for (int j = 0; j ds.Tables.Count; j++)
{
for(int i=0;ids.Tables[j].Rows.Count;i++)
{
DataRow dr=ds.Tables[j].Rows[i];
//組名
string groupname = dr["組名"].ToString().Trim();
//聯(lián)系人
string contactName = dr["聯(lián)系人"].ToString().Trim();
//手機(jī)號(hào)碼
string mobile = dr["手機(jī)號(hào)碼"].ToString().Trim();
//公司名稱
string companyName = dr["公司名稱"].ToString().Trim();
//公辦號(hào)碼
string officeNum = dr["辦公號(hào)碼"].ToString().Trim();
//家庭號(hào)碼
string homeNum = dr["家庭號(hào)碼"].ToString().Trim();
//郵箱
string Email = dr["郵 箱"].ToString().Trim();
//聯(lián)系地址
string address = dr["聯(lián)系地址"].ToString().Trim();
//創(chuàng)建時(shí)間
string createtime = dr["創(chuàng)建時(shí)間"].ToString().Trim();
//性別
string Sex = dr["性別"].ToString().Trim();
//手機(jī)套餐類型
string mobileType = dr["手機(jī)套餐類型"].ToString().Trim();
//是否開(kāi)通通信助理
string isOpen = dr["是否開(kāi)通通信助理"].ToString().Trim();
//SQL 語(yǔ)句
strSQL = "insert into msm_Excel(groupName,Mobile,Name,companyName,officeNum,homeNum,Emial,address,Createtime,Sex,mobileType,isOpen)values('" + groupname + "','" + mobile + "','" + contactName + "','" + companyName + "','" + officeNum + "','" + homeNum + "','" + Email + "','" + address + "','" + createtime + "','" + Sex + "','" + mobileType + "','" + isOpen + "')";
try
{
int n = SQLHelper.SqlDataExecute(strSQL);
if (n > 0)
{
Page.RegisterStartupScript("", "mce:script type="text/javascript">!--
alert('數(shù)據(jù)插入成功!')
// -->/mce:script>");
Label1.Text = "一共成功插入" + ds.Tables[j].Rows.Count.ToString() + "條數(shù)據(jù)";
}
else
{
Page.RegisterStartupScript("", "mce:script type="text/javascript">!--
alert('服務(wù)器繁忙！請(qǐng)稍候再試..!')
// -->/mce:script>");
}
}
catch (Exception ex)
{
throw ex;
}
}
}
}
else {
Page.RegisterStartupScript("", "mce:script type="text/javascript">!--
alert('此Excel文件中無(wú)數(shù)據(jù)!!!')
// -->/mce:script>");
}
}
//調(diào)用
//獲取上傳文件名
string fileName = FileUpload1.FileName;
//判斷是否存在上傳文件
if (FileUpload1.PostedFile.FileName.Length == 0) {
Page.RegisterStartupScript("", "mce:script type="text/javascript">!--
alert('請(qǐng)選擇你要上傳的Excel文件!!')
// -->/mce:script>");
}
//判斷上傳的文件類型是否正確
else if (!Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower().Equals(".xls") !Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower().Equals(".xlsx"))
{
Page.RegisterStartupScript("", "script>alert('很抱歉!你上傳的文件類型不正確!只能上傳Excel類型的文件!')/script.");
}
else
{
//獲取上傳的文件路徑
filePath = Server.MapPath("TxtFiles//") + DateTime.Now.ToString("yyyyMMddhhmmss") + fileName;
this.FileUpload1.PostedFile.SaveAs(filePath);
ds = GetDataSet(filePath);
InsertData(ds);
}
