前言：
上篇部落格介紹了SqlBulkCopy類批量運算元據庫的相關操作，最後提到了可以使用這個類實現excel檔案匯入資料庫，接下來我做簡單介紹。
首先說一下思路：
- 把excel中的資料讀出來並放入到DataTable中。
- 使用SqlBulkCopy類的方法批量匯入資料庫。
雖然只用簡單的兩部。但是我們需要考慮的問題還是用很多的，其中很重要的一點就是判斷需要匯入即excel中的資料是否合法，包括：是不是為空，是不是太長，是不是有重複，匯入的欄位是否對應。看起來挺嚇人的，其實思路還是最重要的。只要敢想什麼都能實現。
下面我簡單的寫一個小例子來說明一下。
多餘的不說，直接到點選匯入按鈕：
protected void btnImportTeacher_Click(object sender, EventArgs e)
{
//成員業務邏輯層
MemberInfoBLL memberInfo = new MemberInfoBLL() ;
//BLL層把Excel轉化為datatable類
CreateExcelDataBLL createExcelData = new CreateExcelDataBLL();
//將錯誤資訊匯出到Excel
DataTableToExcel dataTableToExcel = new DataTableToExcel();
/*-------------------------------------準備Excel檔案--------------------------------------*/
//獲取上傳檔案地址
string url = fupImportTeacher.PostedFile.FileName.ToString();
if (url == "")
{
//資料來源為空，彈出提示：請選擇Excel檔案！
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('請選擇Excel檔案！');</script>");
return;
}
string urlLocation = url.Substring(url.LastIndexOf("\\") + 1);//獲取檔名
DataTable dtAllMember;
//在系統中建資料夾up,並將excel檔案另存
this.fupImportTeacher.SaveAs(Server.MapPath("~\\UploadFile") + "\\" + urlLocation);//記錄檔名到伺服器相對應的資料夾中
//獲得檔案路徑
string strpath = Server.MapPath("~\\UploadFile") + "\\" + urlLocation;
//把上傳的Excel轉換為datatable
dtAllMember = createExcelData.CreateExcelDataSource(strpath);
以上程式碼涉及到的類包括CreateExcelDataBLL 、DataTableToExcel
/*******************************************************************************
*文 件：CreateExcelDataBLL.cs
*作 者：韓義
*所屬小組：圖書館維修管理系統
*檔案說明：基礎系統-把excel轉化為datatable
*建立日期：2013年1月23日9:43:16
*修改作者：
*修改日期：
*修改描述：
*版 本 號：V1.0
*版本號變更記錄：
********************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//引用各名稱空間
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
namespace BLL
{
public class CreateExcelDataBLL
{
//構造方法
public CreateExcelDataBLL()
{
}
/// <summary>
/// 傳入excel路徑，轉換為datatable
/// </summary>
/// <param name="url"></param>
/// <returns></returns>
public DataTable CreateExcelDataSource(string url)
{
//定義一個DataTable資料表
DataTable dt = null;
//獲得excel資料
string connetionStr = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + url + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
//從Excel表的Sheet1單元格獲取資料
string strSql = "select * from [Sheet1$]";
OleDbConnection oleConn = new OleDbConnection(connetionStr);
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(strSql, connetionStr);
try
{
//把Excel資料填充給DataTable
dt = new DataTable();
oleAdapter.Fill(dt);
//返回資料表
return removeEmpty(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
oleAdapter.Dispose();
oleConn.Close();
oleConn.Dispose();
//刪除上傳的Excel檔案（因為該檔案的存在會佔用多餘的網站空間）
if (File.Exists(url))
{
File.Delete(url);
}
}
}
/// <summary>
/// 迴圈去除datatable中的空行
/// </summary>
/// <param name="dt"></param>
protected DataTable removeEmpty(DataTable dt)
{
List<DataRow> removelist = new List<DataRow>();
for (int i = 0; i < dt.Rows.Count; i++)
{
bool rowdataisnull = true;
for (int j = 0; j < dt.Columns.Count; j++)
{
if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
{
rowdataisnull = false;
}
}
if (rowdataisnull)
{
removelist.Add(dt.Rows[i]);
}
}
for (int i = 0; i < removelist.Count; i++)
{
dt.Rows.Remove(removelist[i]);
}
return dt;
}
}
}
/*******************************************************************************
*文 件：DataTableToExcel.cs
*作 者：韓義
*所屬小組：圖書館維修管理系統
*檔案說明：基礎系統-把datatable轉化為excel
*建立日期：2013年1月23日10:43:16
*修改作者：
*修改日期：
*修改描述：
*版 本 號：V1.0
*版本號變更記錄：
********************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//匯入名稱空間
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
namespace BLL
{
public class DataTableToExcel
{
/// <summary>
/// 匯出Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="ExportFileName"></param>
public void ToExcel(DataTable dt)
{
DataGrid dgExcel = new DataGrid();
dgExcel.DataSource = dt;
dgExcel.DataBind();
HttpContext.Current.Response.Charset = "GB2312";
string fileName = HttpUtility.UrlEncode(Guid.NewGuid().ToString(), System.Text.Encoding.UTF8);
string str = "attachment;filename=" + fileName + ".xls";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.AppendHeader("content-disposition", str);
StringWriter sw = new StringWriter();
HtmlTextWriter htmTextWriter = new HtmlTextWriter(sw);
dgExcel.RenderControl(htmTextWriter);
HttpContext.Current.Response.Write("<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />");
string style = "<style>td{mso-number-format:\"\\@\";}</style>";//防止匯出excel時將以0開頭的全數字資料的0去掉
HttpContext.Current.Response.Write(style);
HttpContext.Current.Response.Write("</head><body>");
HttpContext.Current.Response.Write(sw);
HttpContext.Current.Response.Write("</body></html>");
HttpContext.Current.Response.End();
}
}
}
兩個類的作用我想通過上面的解釋大家都應該明白他們的作用了。
驗證excel中資料的有效性：
下面介紹如何驗證excel中資料的有效性。
/*-------------------------------------判斷資料來源是否合法--------------------------------------*/
//定義要求的欄位資料
string[] headfields = { "成員姓名","學號","身份等級","小組名稱","密碼","性別","年齡","手機號","QQ號","郵箱","備註"};
//判斷dtAllTeacher中是否包含全部要求的欄位
for (int i = 0; i < headfields.Length; i++)
{
//只要有一個欄位不被包含，則提示"資料來源缺少必要的欄位"，並退出迴圈和整個方法
if (!dtAllMember.Columns.Contains(headfields[i]))
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('資料來源缺少必要的欄位,請檢查Excel資料來源！');</script>");
//退出方法
return;
}
}
//判斷資料來源中是否有資料
if (dtAllMember.Rows.Count == 0)
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('Excel檔案中沒有任何資料，請填充資料！');</script>");
//退出方法
return;
}
//判斷是否有相同學號的行
DataView dvTeacher = new DataView(dtAllMember);
if (dvTeacher.Count != dvTeacher.ToTable(true, "學號").Rows.Count)
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('Excel中有相同的學號，學號不能相同！');</script>");
//退出方法
return;
}
DataSet dsMember = new DataSet("dt_Member"); //建立一個名為dt_Member的DataSet
//手動建立的新資料表-學生資料表
DataTable dtAddMember = new DataTable("dt_AddMember"); //建立一個名為dt_AddMember的DataTalbe
//為dt_AddMember表內建立Column（表頭）,新增資料列:
dtAddMember.Columns.Add(new DataColumn("memberName", typeof(string)));
dtAddMember.Columns.Add(new DataColumn("memberStudentID", typeof(string)));
dtAddMember.Columns.Add(new DataColumn("level", typeof(string)));
dtAddMember.Columns.Add(new DataColumn("groupID", typeof(string)));
dtAddMember.Columns.Add(new DataColumn("memberPW", typeof(string)));
dtAddMember.Columns.Add(new DataColumn("sex", typeof(string)));
dtAddMember.Columns.Add(new DataColumn("age", typeof(string)));
dtAddMember.Columns.Add(new DataColumn("phone", typeof(string)));
dtAddMember.Columns.Add(new DataColumn("qq", typeof(string)));
dtAddMember.Columns.Add(new DataColumn("email", typeof(string)));
dtAddMember.Columns.Add(new DataColumn("explian", typeof(string)));
//新建資料表用於存放錯誤資料
DataTable dtErrorRow = new DataTable();
//為dtErrorRow建立列
dtErrorRow.Columns.Add(new DataColumn("成員姓名", typeof(string)));
dtErrorRow.Columns.Add(new DataColumn("學號", typeof(string)));
dtErrorRow.Columns.Add(new DataColumn("身份等級", typeof(string)));
dtErrorRow.Columns.Add(new DataColumn("小組名稱", typeof(string)));
dtErrorRow.Columns.Add(new DataColumn("密碼", typeof(string)));
dtErrorRow.Columns.Add(new DataColumn("性別", typeof(string)));
dtErrorRow.Columns.Add(new DataColumn("年齡", typeof(string)));
dtErrorRow.Columns.Add(new DataColumn("手機號", typeof(string)));
dtErrorRow.Columns.Add(new DataColumn("QQ號", typeof(string)));
dtErrorRow.Columns.Add(new DataColumn("郵箱", typeof(string)));
dtErrorRow.Columns.Add(new DataColumn("備註", typeof(string)));
dtErrorRow.Columns.Add(new DataColumn("錯誤原因", typeof(string)));
//從上傳的Excel轉換為的datatable表中取出資料，放入成員資訊。
for (int intRow = 0; intRow < dtAllMember.Rows.Count; intRow++)
{
//成員姓名
strMemberName = dtAllMember.Rows[intRow]["成員姓名"].ToString();
//成員姓名
memberStudentID = dtAllMember.Rows[intRow]["學號"].ToString();
strLevel = dtAllMember.Rows[intRow]["身份等級"].ToString();
strGroupName = dtAllMember.Rows[intRow]["小組名稱"].ToString();
strmemberPW = dtAllMember.Rows[intRow]["密碼"].ToString();
strSex = dtAllMember.Rows[intRow]["性別"].ToString();
strAge = dtAllMember.Rows[intRow]["年齡"].ToString();
strPhone = dtAllMember.Rows[intRow]["手機號"].ToString();
strQQ = dtAllMember.Rows[intRow]["QQ號"].ToString();
strEmail = dtAllMember.Rows[intRow]["郵箱"].ToString();
strExplian=dtAllMember.Rows[intRow]["備註"].ToString();
//根據小組名稱判斷，小組是否存在
Boolean bFlag = false;
//判斷輸入的組名是否存在
bFlag = new BLL.GroupInfoBLL().ExistsByName(strGroupName);
// ID
if (bFlag ==true )
{
//小組ID為--韓義
strGroupID = new BLL.GroupInfoBLL().GetModelByGroupName(strGroupName).groupID;
}
//否則將當前行新增到錯誤列表並跳出當前迴圈
else
{
//向錯誤列表中新增當前行
AddErrorRow(dtErrorRow, "指定的組名稱不存在");
//跳出當前迴圈
continue;
}
//判斷成員ID是否存在
bFlag = false;//定義標識變數
//判斷學號是否存在
bFlag = new BLL.MemberInfoBLL().Exists(memberStudentID);
//如果指定的成員程式碼已存在則將當前行新增到錯誤列表，並跳出當前迴圈
if (bFlag==true )
{
//向錯誤列表中新增當前行
AddErrorRow(dtErrorRow, "指定的成員已存在");
//跳出當前迴圈
continue;
}
//判斷性別型別是否正確
if (strSex != "男" && strSex != "女")
{
//向錯誤列表中新增當前行
AddErrorRow(dtErrorRow, "成員性別錯誤");
//跳出當前迴圈
continue;
}
//判斷身份等級是否正確
if (strLevel !="組員")
{
//向錯誤列表中新增當前行
AddErrorRow(dtErrorRow, "匯入成員身份級別只能為“組員”，組長請在系統中指定");
//跳出當前迴圈
continue;
}
//判斷是否包含密碼資訊
if (strmemberPW =="")
{
//向錯誤列表中新增當前行
AddErrorRow(dtErrorRow, "成員密碼不能為空");
//跳出當前迴圈
continue;
}
//判斷是否包含電話資訊
if (strPhone =="")
{
//向錯誤列表中新增當前行
AddErrorRow(dtErrorRow, "成員手機號不能為空");
//跳出當前迴圈
continue;
}
//判斷是否包含郵箱資訊
if (strEmail =="")
{
//向錯誤列表中新增當前行
AddErrorRow(dtErrorRow, "成員郵箱不能為空");
//跳出當前迴圈
continue;
}
//判斷是否包含學號資訊
if (memberStudentID == "")
{
//向錯誤列表中新增當前行
AddErrorRow(dtErrorRow, "成員學號不能為空");
//跳出當前迴圈
continue;
}
//判斷是否包含姓名資訊
if (strMemberName == "")
{
//向錯誤列表中新增當前行
AddErrorRow(dtErrorRow, "成員姓名不能為空");
//跳出當前迴圈
continue;
}
//新增成員資訊表的新行
DataRow drAddMember = dtAddMember.NewRow();//注意這邊建立dt的新行的方法。指定型別是DataRow而不是TableRow，然後不用new直接的用建立的DataTable下面的NewRow方法。
//學生資訊表對應的各列值
drAddMember["memberName"] = strMemberName;
drAddMember["memberStudentID"] = memberStudentID;
drAddMember["level"] = strLevel;
drAddMember["groupID"] = strGroupID;
drAddMember["memberPW"] = strmemberPW;
drAddMember["sex"] = strSex;
drAddMember["age"] = strAge;
drAddMember["phone"] = strPhone;
drAddMember["qq"] = strQQ;
drAddMember["email"] = strEmail;
drAddMember["explian"] = strExplian;
dtAddMember.Rows.Add(drAddMember); //將一整條資料寫入表中
}
//將表加入DataSet中:成員資訊
dsMember.Tables.Add(dtAddMember);
//將DataSet中資料表匯入資料庫
Boolean flagImportMember = memberInfo.ImportMember(dsMember);
//判斷成員是否匯入成功
if (true == flagImportMember && dtErrorRow.Rows.Count == 0)
{
//匯入成功，彈出提示
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('成員資訊匯入成功！');</script>");
}
else if (false == flagImportMember)
{
//匯入失敗，彈出提示
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('成員資訊匯入失敗！');</script>");
}
else if (true == flagImportMember && dtErrorRow.Rows.Count != 0)
{
//只有部分資料匯入成功，彈出提示
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('成員資訊只有部分匯入成功，有錯誤的部分已匯出為EXCEL，請檢視新匯出的EXCEL；新匯出的Excel修改錯誤後可作為資料來源重新匯入');</script>");
//將錯誤資料匯出到Excel並開啟顯示
dataTableToExcel.ToExcel(dtErrorRow);
}
}
說明：以上程式碼中建立了兩個表一個用於匯入：欄位改為與資料庫對應，另一個表用於匯出：欄位改為漢字說明。其中memberInfo.ImportMember方法就是實現資料庫的批量匯入，這在上一篇部落格中已經有介紹了這裡就不再累述了。
總結：
原來感覺挺難的東西，現在都已經完整的總結了出來。體會挺深的，什麼東西首先不能被嚇住，平常心態對待每一個新知識，總會找到熟悉的身影，總有那麼個頭緒讓你一點一點屢清楚了。