Jasonwu的专栏

山在那儿,我在路上...

在ASP.NET中将Excel文件中数据导入数据库并显示进度条

在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)
                    {
                        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="width:300px; color:Silver;border-width:1px; border-style:Solid;">
                <div id="ProgressBar" align="center" style="height:20px; width: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>

阅读更多
个人分类: ASP.NET
想对作者说点什么? 我来说一句

excel导入数据库

2011年12月29日 8KB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭