1.首先,我们来个页面
<fieldset id="myfieldset1">
选择文件:<input id="FileUpload" type="file" name="files" style="width: 250px; height: 24px;
background: White" class="easyui-validatebox" />
<p style="color: Red; text-align: left;">@ViewBag.error</p>
<input id="btnImport" type="submit" value="导入" οnclick="sbtest()" style="width: 60px; height: 28px;" />
</fieldset>
我们再加个导入时候的效果
function sbtest() {
$("#btnImport").attr("readonly", "true");
$("#myfieldset1 p").text("数据导入中.....");
}
2.我们要execl数据干什么呢?显示在页面上?导入到数据库?首先我们就需要把execl中的数据提取出来,我们保存到DataTable中,直接复制到你的方法中就ok了,自动找路径
HttpPostedFileBase file = Request.Files["files"];
string FileName;
string savePath;
string strConn = String.Empty;
if (file == null || file.ContentLength <= 0)
{
ViewBag.error = "文件不能为空";
return View();
}
else
{
string filename = Path.GetFileName(file.FileName);
int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M
string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
if (!FileType.Contains(fileEx))
{
ViewBag.error = "文件类型不对,只能导入xls和xlsx格式的文件";
return View();
}
if (filesize >= Maxsize)
{
ViewBag.error = "上传文件超过4M,不能上传";
return View();
}
string path = Server.MapPath("/uploads/excel/");
savePath = Path.Combine(path, FileName);
//检查文件夹是否存在
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
file.SaveAs(savePath);
//strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +savePath+ ";" + "Extended Properties=Excel 8.0";
if (fileEx.Equals(".xlsx"))
strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + savePath + ";Extended Properties='Excel 12.0 Xml; HDR=YES; IMEX=1'";
else if (fileEx.Equals(".xls"))
strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + savePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
}
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
//动态读取工作表
DataTable excelShema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string firstSheetName = excelShema.Rows[0]["TABLE_NAME"].ToString();
string query = string.Format("Select * from [{0}]", firstSheetName);
//
OleDbDataAdapter myCommand = new OleDbDataAdapter(query, strConn);
//OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
DataSet myDataSet = new DataSet();
try
{
myCommand.Fill(myDataSet, "ExcelInfo");
}
catch (Exception ex)
{
ViewBag.error = ex.Message;
return View();
}
DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
3.既然我们已经把数据保存到了datatable 中,那么现在我们就可以读取它,把他添加到数据库里面去!如何呢?
table.Rows[i][j].ToString();//取到我们想要的字段
使用for(;table.Rows.Count;),foreach()循环来一次次的insert吧!这样数据就ok了!