在VS2003中:
前台HTML:
<form id="form1" runat="server" enctype="multipart/form-data"><input type="file" id="FileUpLoad1" runat="server" unselectable="on"><a href="#" οnclick="return checkType()">
<asp:button id="btnExcelImport" οnclick="btnExcelImport_Click" runat="server" Width="65px" Text="上传"></asp:button></a><asp:label id="lblMessage" runat="server"></asp:label>
JAVASCRIPT代码:
function checkType() {
//得到上传文件的值
var fileName = document.getElementById('FileUpLoad1').value;
if (Trim(fileName) == "") {
alert("尚未选择文件!");
return false;
}
//返回String对象中字符串最后出现的位置。
var seat = fileName.lastIndexOf(".");
//返回位于String对象中指定位置的子字符串并转换为小写
var extension = fileName.substring(seat).toLowerCase();
var allowed = [".xls", ".xlsx"];
for (var i = 0; i < allowed.length; i++) {
if (!(allowed[i] != extension)) {
return true;
}
}
alert("不支持" + extension + "格式");
return false;
}
function Trim(string) {
return string.replace(/(^\s*)|(\s*$)/g, "");
}
后台代码:把excel里的内容读取到DATASET里面。
protected void btnExcelImport_Click(object sender,EventArgs e) { //判断文件是否小于4MB if (FileUpLoad1.PostedFile.ContentLength < 4194304) { HttpPostedFile file = FileUpLoad1.PostedFile; string str=file.FileName; string filename2 = str.Substring(str.LastIndexOf(".")).ToString().Trim(); string filename1 = DateTime.Now.Ticks.ToString()+filename2; file.SaveAs(Server.MapPath("./upload/"+filename1));//将用户上传的文件保存到服务器上.
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("./upload/"+filename1) + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\""; //此连接只能操作Excel2007之前(.xls)文件
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + Server.MapPath("./upload/"+filename1) + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此连接可以操作.xls与.xlsx文件 OleDbConnection Oleconn = new OleDbConnection(strConn); string strExcel = ""; OleDbDataAdapter excelCommand = null; DataSet excel_ds = new DataSet(); strExcel = "select * from [sheet1$]"; try { Oleconn.Open(); excelCommand = new OleDbDataAdapter(strExcel, Oleconn); excelCommand.Fill(excel_ds, "exdtSource");//得到dataset //if (this.DropDownList_ChangeType.SelectedValue == "") //{ // this.ShowMessage("请选择变动类型!!"); // return; //} //if (this.DropDownList_ChangeType.SelectedValue == "2") //{ // if (this.DropDownList_InDept.SelectedValue == "") // { // this.ShowMessage("请选择转入部门!!"); // return; // } // if (this.DropDownList_InMan.SelectedValue == "") // { // this.ShowMessage("请选择转入保管人!!"); // return; // } //} //else if (this.DropDownList_ChangeType.SelectedValue == "15") //{ // if (this.DropDownList_InMan.SelectedValue == "") // { // this.ShowMessage("请选择转入保管人!!"); // return; // } //} //记录错误消息 string sErrMsg = string.Empty; int i = 1; string sLineMsg = string.Empty; bool lineError = false; try { //每行检查数据,正确且无重复才添加到可申请table中 foreach (DataRow dr in excel_ds.Tables[0].Rows) { i++; sLineMsg = "- 第" + i.ToString() + "行,"; lineError = false; //获取名称 string _strAssetName = dr[0].ToString().Replace("'", "").Trim(); if (_strAssetName == "") { sErrMsg += sLineMsg + "名称不允许为空!\\n"; lineError = true; } if (lineError) //该行数据错误,alert错误信息,无需检查重复 { continue; } #region 重复记录 int j = 1; bool isRepeated = false; foreach (DataRow preDr in excel_ds.Tables[0].Rows) { j++; if (j >= i) //只与本行前面的行比较 { break; } if ((dr[0].ToString().Trim() == preDr[0].ToString().Trim())) { sErrMsg += "- 第" + i + "行,与第" + j + "行,资产编号重复,请删除后重新上传!\\n"; isRepeated = true; continue; } } #endregion #region FillData if (isRepeated) //重复 { lineError = true; } if (!lineError) //无错误,才添加到页面 { //准备封装起 往数据库里插入相关数据 InsertDetailInfo(mainData); } #endregion } } catch (Exception ex) { this.ShowMessage("- 导入的Excel文件错误:" + ex.Message); return; } this.BindGridView_SUB1(); if (sErrMsg != "") { this.ShowMessage(sErrMsg); return; } } catch (Exception exx) { this.ShowMessage("出现异常,无法上传!"); return; } finally { Oleconn.Close(); Oleconn.Dispose(); } } else { //lblMessage.Text = "上传文件不能大于4MB!"; this.ShowMessage("上传文件不能大于4MB!"); return; }
over.
注意:
可能转成Microsoft.Ace.OleDb.12.0,程序执行到Oleconn.Open();突然发生异常了。请跟踪异常信息,如果发现是“"System.InvalidOperationException: 未在本地计算机上注册“Microsoft.Ace.OleDb.12.0”提供程序。 ”的错误消息。那么,去下载这个:AccessDatabaseEngine.exe
地址如下:
GOOD LUCK!