在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; width: 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; 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>
</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)
{