进度条在.net导入Excel时的应用实例(2)

// 根据 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);