.net导入Excel 并显示进度条

最新推荐文章于 2024-07-31 04:38:25 发布

东边的小山

最新推荐文章于 2024-07-31 04:38:25 发布

阅读量3.9k

点赞数

分类专栏： easyUI

easyUI 专栏收录该内容

75 篇文章 2 订阅

订阅专栏

在程序开发过程中，往往会涉及到将Excel表格导入到数据库中的需求，而当excel表格内容很多的时候，我们往往会很难去捕捉它的执行过程进度和一些错误信息，此时我们便可以通过以下方法去解决这些难题，具体实现过程分析如下：

一、建立一个web应用程序，在程序中首先创建一个html文件命名为ProgressBar，文件内容如下：

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title></title>
<script type="text/javascript">
//开始处理
function BeginTrans(msg) {
WriteText(msg);
}

//设置进度条进度
function SetPorgressBar(msg, pos) {
ProgressBar.style.width = pos + "%";
WriteText(msg + " 已完成" + pos + "%");
}

//处理结束
function EndTrans(msg) {

if (msg == "")

WriteText("完成。");

else

WriteText(msg);

}

//设置时间信息

function SetTimeInfo(msg) {
WriteText(msg);

}

// 更新文本显示信息
function WriteText(str) {
var strTag = '<font face="Verdana, Arial, Helvetica" size="2" color="#ea9b02"><B>' + str + '</B></font>';
document.getElementById("Msg2").innerHTML = strTag;
}
</script>
</head>
<body>
<table align="center" style="height:100%">
<tr style="height:45%"><td></td></tr>
<tr>
<td>
<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>
</td>
<td>
<div id="Msg2" style="height:16px;"></div>
</td>
</tr>
<tr style="height:50%"><td></td></tr>
</table>
</body>
</html>

二、创建一个aspx页面，前后端代码分别如下：

//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" οnclick="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;

}