在程序開發過程中，往往會涉及到將Excel表格導入到數據庫中的需求，而當excel表格內容很多的時候，我們往往會很難去捕捉它的執行過程進度和一些錯誤信息，此時我們便可以通過以下方法去解決這些難題，具體實現過程分析如下：
//1.這里為了簡便，我只寫出了前端頁面中的body體部分供參考：
form id="forms" runat = "server">
table align="center" style="height:100%">
tr style="height:45%">td>/td>/tr>
tr>
td align="center" style="height: 24px; width: 100px;"> Excel文件/td>
td style="height: 24px">
asp:FileUpload ID="fuGlossaryXls" runat="server"/>
asp:Label ID="Label2" runat="server" Font-Bold="True" ForeColor="Red" Text="不能為空"
Visible="False">/asp:Label>/td>
td>
asp:Button ID="Button1" runat="server" CssClass="mybotton" Text="導入" Width="60px" onclick="Button1_Click"/>/td>
/tr>
/table>
/form>
//2.后端部分代碼如下：
//這里是激發導入按鈕點擊事件
protected void Button1_Click(object sender, EventArgs e)
{
string cfilename = this.fuGlossaryXls.FileName;//獲取準備導入的文件名稱
if (cfilename == "")
{
Label2.Visible = true;
return;
}
else
{
Label2.Visible = false;
}
//////////////顯示進度/////////////////////////////////////////////////////////////////////////////
DateTime startTime = System.DateTime.Now;
DateTime endTime = System.DateTime.Now;
// 根據 ProgressBar.htm 顯示進度條界面
string templateFileName = Path.Combine(Server.MapPath("."), "ProgressBar.htm");
StreamReader reader = new StreamReader(@templateFileName, System.Text.Encoding.GetEncoding("gb2312"));
string html = reader.ReadToEnd();
reader.Close();
Response.Write(html);
Response.Flush();
System.Threading.Thread.Sleep(1000);
string jsBlock;
// 處理完成
jsBlock = "script>BeginTrans('正在加載數據，請耐心等待...');/script>";
Response.Write(jsBlock);
Response.Flush();
string fileName = fuGlossaryXls.PostedFile.FileName.Substring(fuGlossaryXls.PostedFile.FileName.LastIndexOf("\\") + 1);//獲取準備導入文件的文件名
string suffix = fileName.Substring(fileName.LastIndexOf(".") + 1);//獲取準備導入文件的后綴名
System.Threading.Thread.Sleep(200);
int maxrows = 0;//用來記錄需要加載的數據總行數
bool err = false;//用來記錄加載狀態
int errcount = 0;//用來記錄加載錯誤行數
if (fuGlossaryXls.HasFile)//判斷當前是否有選取文件
{
if (suffix == "xlsx")
{
DataTable dt = ExcelImport(fileName);
for (int i = 0; i dt.Rows.Count; i++)
{
maxrows++;
}
//////////拓展////////////////////////////////////////////////////////
//DataView myView = new DataView(dt);
//myView.RowFilter = "name is not null";
//int t = myView.Count;//獲取滿足RowFilter 條件的數據行
//////////拓展////////////////////////////////////////////////////////
string sqlconnect = "Data Source=.;Initial Catalog=test;User ID=sa;Password=123456;";//本地數據庫鏈接
SqlConnection conn = new SqlConnection(sqlconnect);
SqlTransaction myTrans = null;
try
{
SqlCommand cmd = new SqlCommand(null, conn);
conn.Open();
myTrans = conn.BeginTransaction();
cmd.Transaction = myTrans;
cmd.CommandText = "delete from test";
cmd.ExecuteNonQuery();//首先執行清除表內容操作
for (int j = 0; j dt.Rows.Count; j++)//循環向數據庫中插入excel數據
{
if (string.IsNullOrEmpty(dt.Rows[j][0].ToString()))
{
jsBlock = "script>EndTrans('第" + j.ToString() + "行數據寫入錯誤。');/script>";
Response.Write(jsBlock);
Response.Flush();
err = true;
errcount++;
}
else
{
cmd.CommandText = string.Format("insert into test values('{0}','{1}','{2}','{3}')", dt.Rows[j][0], dt.Rows[j][1], dt.Rows[j][2], dt.Rows[j][3]);
cmd.ExecuteNonQuery();//逐行向表中插入數據，注意字段的對應
}
System.Threading.Thread.Sleep(1000);
float cposf = 0;
cposf = 100 * (j + 1) / maxrows;
int cpos = (int)cposf;
jsBlock = "script>SetPorgressBar('已加載到第" + (j + 1).ToString() + "條','" + cpos.ToString() + "');/script>";
Response.Write(jsBlock);
Response.Flush();
}
myTrans.Commit();//提交
}
catch (Exception ex)
{
myTrans.Rollback();//回滾
ClientScript.RegisterStartupScript(this.GetType(), "alert", "script>alert('" + ex.Message + "');/script>");
}
finally
{
conn.Dispose();
conn.Close();//關閉數據庫連接
}
}
else
{
ClientScript.RegisterStartupScript(GetType(), "", "alert('請選擇Excel文件!');", true);
}
}
else
{
ClientScript.RegisterStartupScript(GetType(), "", "alert('請選擇要導入的Excel!');", true);
}
if (!err)//加載中并沒有出現錯誤
{
// 處理完成
jsBlock = "script>EndTrans('處理完成。');/script>";
Response.Write(jsBlock);
Response.Flush();
}
else
{
jsBlock = "script>EndTrans('共有"+maxrows.ToString()+"條數據需要加載，其中 有"+errcount.ToString()+"條數據錄入錯誤！');/script>";
Response.Write(jsBlock);
Response.Flush();
}
System.Threading.Thread.Sleep(1000);
endTime = DateTime.Now;//錄入完成所用時間
TimeSpan ts1 = new TimeSpan(startTime.Ticks);
TimeSpan ts2 = new TimeSpan(endTime.Ticks);
TimeSpan ts = ts2.Subtract(ts1).Duration(); //取開始時間和結束時間兩個時間差的絕對值
String spanTime = ts.Hours.ToString() + "小時" + ts.Minutes.ToString() + "分" + ts.Seconds.ToString() + "秒";
jsBlock = "script>SetTimeInfo('加載完成，共用時" + spanTime + "');/script>";
Response.Write(jsBlock);
Response.Flush();
}
public DataTable ExcelImport(string fileName) //建立Excel表鏈接，返回Excel表數據
{
//EXCEL 的連接串
string sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=C:\\Documents and Settings\\Administrator\\桌面\\" + fileName + ";" +
"Extended Properties='Excel 8.0;IMEX=1';";
//string sConnectionString = "Microsoft.ACE.OLEDB.4.0;" +
//"Data Source=C:\\Documents and Settings\\Administrator\\桌面\\" + fileName + ";" +
//"Extended Properties='Excel 8.0;IMEX=1';";
OleDbConnection objConn = new OleDbConnection(sConnectionString);//建立EXCEL的連接
//說明：程序運行到這里的時候有時會出錯“未在本地計算機上注冊“Microsoft.ACE.OLEDB.12.0”提供程序”，此時大多數情況下我們只需要去http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe下載一個AccessDatabaseEngine.exe安裝即可，原因在于你的office沒有安裝ACCESS組件
objConn.Open();
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();
objAdapter1.Fill(objDataset1, "XLData");
DataTable dt = objDataset1.Tables[0];
//DataView myView = new DataView(dt);
objConn.Close();//關閉EXCEL的連接
return dt;
}
