在ASP.NET中经常会遇到Excel文件导入数据库的问题，遇到数据量比较大的时候，最好显示进度条。
进度条设计是参考网上某牛人提供的代码，利用JS实现的，谢谢这个大牛了（忘记了当时记录下他的大名了，:(）。具体思路：首先将后台服务器上 ProgressBar.htm 页面内容（其中有html和js代码）完全读取出来，并write到前台。然后在数据导入前，添加js调用，如下：
jsBlock = "<script>BeginTrans('开始处理...');</script>";
Response.Write(jsBlock); Response.Flush();
其次，在处理每条数据导入的过程中，添加如下js调用：
System.Threading.Thread.Sleep(20);
float cposf = 0; cposf = 100 * i / maxrows; int cpos = (int)cposf;
jsBlock = "<script>SetPorgressBar('" + "第" + i.ToString() + "条','" + cpos.ToString() + "');</script>"; Response.Write(jsBlock); Response.Flush();
*******************************************************
以下是完整的实现过程......
前台.aspx部分代码：
<tr> <td align="center" style="height: 25px; 100px;">父分类</td> <td valign="middle" style="height: 25px"> <asp:TextBox ID="tbParent" runat="server" CssClass="textinput" Text="" Width="150px"></asp:TextBox> <asp:TextBox ID="tbParentId" runat="server" CssClass="textinput" Text=""Width="19px" ></asp:TextBox> <asp:Label ID="Label1" runat="server" Font-Bold="True" ForeColor="Red" Text="不能为空" Visible="False"></asp:Label></td> < /tr> < tr> <td align="center" style="height: 24px; 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> < /tr>
... ... ...
<asp:Button ID="btnImport" runat="server" CssClass="mybotton" Text="导 入" Width="60px" OnClick="btnImport_Click" ></asp:Button>
以下是在FileUpload控件中，选择了相应文件后点击“导入”.aspx.cs文件中响应事件。
protected void btnImport_Click(object sender, EventArgs e) { string cparentname = this.tbParent.Text.Trim(); string cparentid = this.tbParentId.Text.Trim(); if (cparentname == "") { Label1.Visible = true; return; } else { Label1.Visible = false; }
string cfilename = this.fuGlossaryXls.FileName; if (cfilename == "") { Label2.Visible = true; return; } else { Label2.Visible = false; }
//////////////首先将文件上传到服务器/////////////////////////////////////////////////////////// string tempfilename = Guid.NewGuid().ToString(); String filepath = System.Configuration.ConfigurationSettings.AppSettings["SaveFilePath"] + tempfilename + ".xls"; try { fuGlossaryXls.SaveAs(filepath); } catch (Exception ex) { //Response.Write("<script>alert('数据文件上传出错！')</script>"); Response.Write(ex.Message); return; } //////////////文件上传到服务器////////////////////////////////////////////////////////////
//////////////显示进度///////////////////////////////////////////////////////////////////////////// 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();
///-------------------------------------------------- object missing = Missing.Value; DateTime beforeTime; DateTime afterTime;
beforeTime = DateTime.Now; Excel.Application cexcelapp = new Excel.Application(); cexcelapp.Visible = false; afterTime = DateTime.Now;
Excel.Workbook workBook = cexcelapp.Workbooks.Open(filepath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//得到WorkSheet对象 Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);
int maxrows = 0; int maxcolumns = 0; maxrows = sheet.UsedRange.Rows.Count; maxcolumns = sheet.UsedRange.Columns.Count; ///--------------------------------------------------
System.Threading.Thread.Sleep(200);
bool err = false;
// 根据处理任务处理情况更新进度条 for (int i = 1; i <= maxrows; i++) { ///-------------处理------------------------------------- Range curentCell = (Range)sheet.Cells[i, 1]; string cvalue = curentCell.Text.ToString().Trim(); if (cvalue != "") { //bool exist = CommFun.DataItemExistCheck("GLOSSARY", "NODE_NAME", cvalue); string cwherestr = " parent_id = '" + cparentid + "'"; bool exist = CommFun.DataItemExistCheck("GLOSSARY", "NODE_NAME", cvalue, cwherestr); if (!exist) { string newnodeid = Glossary.GetNewnodeId(cparentid); int subresult = Glossary.AddNode(newnodeid, cvalue, cparentid); if (subresult != 0) { jsBlock = "<script>EndTrans('数据写入错误。');</script>"; Response.Write(jsBlock); Response.Flush(); err = true; break; } } } ///----------------------------------------------------- System.Threading.Thread.Sleep(20);
float cposf = 0; cposf = 100 * i / maxrows; int cpos = (int)cposf;
jsBlock = "<script>SetPorgressBar('" + "第" + i.ToString() + "条','" + cpos.ToString() + "');</script>"; Response.Write(jsBlock); Response.Flush(); }
if (!err) { // 处理完成 jsBlock = "<script>EndTrans('处理完成。');</script>"; Response.Write(jsBlock); Response.Flush(); } // 用时 endTime = DateTime.Now; jsBlock = "<script>SetTimeInfo('用时" + GetTimeSpan(startTime, endTime) + "');</script>"; Response.Write(jsBlock); Response.Flush(); //////////////////显示进度/////////////////////////////////////////////////////////////////////////////////////
try { workBook.Close(null, null, null); cexcelapp.Workbooks.Close(); cexcelapp.Application.Quit(); cexcelapp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(cexcelapp);
workBook = null; cexcelapp = null;
GC.Collect(); } catch (Exception e1) { throw e1; } finally { Process[] myProcesses; DateTime startTime1; myProcesses = Process.GetProcessesByName("Excel");
//得不到Excel进程ID，暂时只能判断进程启动时间 foreach (Process myProcess in myProcesses) { startTime1 = myProcess.StartTime;
if (startTime1 > beforeTime && startTime < afterTime) { myProcess.Kill(); } } }
System.IO.File.Delete(filepath); }
ProgressBar.htm 文件内容如下：
<html> < head> < title></title> < script language="javascript">
//开始处理 function BeginTrans(msg) { WriteText("Msg1",msg); }
//设置进度条进度 function SetPorgressBar(msg, pos) { ProgressBar.style.width= pos + "%"; WriteText("Msg1",msg + " 已完成" + pos + "%"); }
//处理结束 function EndTrans(msg) { if(msg=="") WriteText("Msg1","完成。"); else WriteText("Msg1",msg); }
//设置时间信息 function SetTimeInfo(msg) { WriteText("Msg2",msg); }
// 更新文本显示信息 function WriteText(id, str) { var strTag = '<font face="Verdana, Arial, Helvetica" size="2" color="#ea9b02"><B>' + str + '</B></font>'; if (document.all) document.all[id].innerHTML = strTag; } < /script> < /head> < body> < table align="center" style="height:100%"> <tr style="height:45%"><td></td></tr> <tr> <td> <div id="Msg1" style="height:16px;"> <font face="Verdana, Arial, Helvetica" size="2" color="#ea9b02"><b>正在加载...</b></font></div> <div id="ProgressBarSide" style="300px; color:Silver;border-1px; border-style:Solid;"> <div id="ProgressBar" align="center" style="height:20px; 0%; background-color:#316AC5;"></div> </div> <div id="Msg2" style="height:16px;"><font face="Verdana, Arial, Helvetica" size="2" color="#ea9b02"><b></b></font></div> </td> </tr> <tr style="height:50%"><td></td></tr> < /table> < /body> < /html>