【C#MVC】使用ajaxFileUpload导入Excel,并显示在Grid中
学习了如何使用ajaxFileUpload(无刷新上传)导入Excel文件,并在页面中显示出来。C#,asp和MVC操作形似,这里重点说MVC,asp只贴出来代码。 错误提示: <script language="javascript" type="text/javascript"> function ajaxFileUpload() { $.ajaxFileUpload({ url: '/TestController/TestImportExcel',//用于文件上传的服务器端请求地址 secureuri: false,//是否需要安全协议,一般设置为false fileElementId: 'fuExcelUpload',//文件上传域的ID dataType: 'json',//返回值类型 一般设置为json success: function (data,status) { LoadGrid(); $("#tbShipmentList").datagrid('loadData',data); },error: function (data,status,e)//服务器响应失败处理函数 { alert("Error:Please contact administrator."); } }); } function LoadGrid() { $("#tbShipmentList").datagrid({ striped: true,loadMsg: "Loading",rownumbers: true,idField: "SerialNumber",checkOnSelect: false,selectOnCheck: false,singleSelect: true,frozenColumns: [[ { title: 'Shipment No.',field: 'ShipmentNumber',formatter: function (value,row,index) { if (row.Result == 0) { return '<font color="green">' + value + '</font>';; } else { return '<font color="red">' + value + '</font>';; } } } ]],columns: [[ { title: 'Origin',field: 'Origin' },{ title: 'Destination',field: 'Destination' },{ title: 'Pieces',field: 'Pieces' },{ title: 'Weight',field: 'ChargeableWeight(KG)' },{ title: 'ImportResult',field: 'ResutlMsg' } ]] }); } </script>
Step2:在后台接受,并做处理。主要是创建一个Excel文件到服务器端,然后使用Excel的数据库引擎,进行查询,最后返回一个DateTable,然后将DateTable转化为Grid可识别的Json字符串即可。 public string TestImport()
{
HttpFileCollectionBase files = Request.Files; //接受上传的文件
if (files.Count > 0)
{
string fileName = "Test" + DateTime.Now.ToString("yyyyMMddhhmmssms") + ".xls";
string filePath = System.Configuration.ConfigurationManager.AppSettings["ExcelUpload"].ToString();
if (files[0].ContentLength == 0)
{
throw new Exception("No file upload!");
}
files[0].SaveAs(filePath + fileName);// 保存上传的文件到服务器端
DataTable dt = OleDBHelper.DoOleSql(filePath + fileName,fileName);
// 使用工具类查询出Excel的数据
dt.Columns.Add("ResutlMsg");
dt.Columns.Add("Result");
for (int i = 0; i < dt.Rows.Count; i++)
{
ExcelDataValidation(dt.Rows[i]);
}
string res = JsonHelper.ToGridJson(dt,dt.Rows.Count);//转化为Json
Session["GridList"] = res;
Response.Write(res);
}
return "";
}
其中重要的工具类:OleDBHelper: public static class OleDBHelper
{
public static DataTable DoOleSql(string database,string fileName)
{
string strConn = null;
int Num = fileName.LastIndexOf('.');
string fileExt = "";
if (Num > 0)
{
fileExt = fileName.Substring(Num).ToLower();
}
if (fileExt == ".xlsx") //excel2007的文件
{
strConn = "Provider=Microsoft.Ace.OleDb.12.0;;Data Source="" + database + "";Extended Properties="Excel 12.0;HDR=YES;IMEX=1;"";
}
if (fileExt == ".xls")//excel2003的文件
{
strConn = "Provider=Microsoft.Jet.OleDb.4.0;Data Source="" + database + "";Extended Properties="Excel 8.0;HDR=YES;IMEX=1;"";
}
OleDbConnection conn;
DataSet ds = new DataSet();
conn = new OleDbConnection(strConn);
conn.Open();
//返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] { null,null,"Table" });
//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
string[] strTables = new string[dtSheetName.Rows.Count];
int k;
string temp;
string[] ids = new string[dtSheetName.Rows.Count];
for (k = 0; k < dtSheetName.Rows.Count; k++)
{
temp = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
strTables[k] = temp.Trim('$');
strTableNames[k] = temp;
}
OleDbDataAdapter myCommand = null;
DataTable dt = new DataTable();
//从指定的表明查询数据,可先把所有表明列出来供用户选择
string strExcel = "select * from [" + strTableNames[0] + "]";
myCommand = new OleDbDataAdapter(strExcel,strConn);
myCommand.Fill(dt);
conn.Close();
dt.TableName = strTables[0];
return dt;
}
}
最后将在Asp中如何使用ajaxFileUpload,贴出来: <script language="javascript" type="text/javascript"> if(typeof JSON == 'undefined'){$('head').append($("<script type='text/javascript' src='../../JS/easyui1.3.2/json2.js'>"));} function ajaxFileUpload() { $.ajaxFileUpload({ url: 'DataFile/UploadExcel.ashx',//返回值类型 一般设置为json success:function(data,status){ LoadGrid(); $("#tbShipmentList").datagrid('loadData',e)//服务器响应失败处理函数 { alert(data.responseText); } }); } function LoadGrid(){ $("#tbShipmentList").datagrid({ striped:true,loadMsg:"Loading",rownumbers:true,idField:"SerialNumber",singleSelect:true,frozenColumns:[[ {checkbox:true },{title:'Shipment No.',field:'ShipmentNumber',formatter: function(value,index){ if(row.Result == 0){ return '<font color="green">' + value + '</font>';; }else{ return '<font color="red">' + value + '</font>';; } }} ]],columns:[[ {title:'Consignee Email',field:'ConsigneeEmail'},{title:'Consignee Name',field:'ConsigneeName'},{title:'Consignee Address',field:'ConsigneeAddress'},{title:'Consignee City',field:'ConsigneeCity'},{title:'Consignee Zip',field:'ConsigneeZip'},{title:'Consignee Contact',field:'ConsigneeContact'},{title:'Consignee Tel No.',field:'ConsigneeTel'} ]] }); } </script>
Part 2: 在ashx如同Controller中一样处理Excel,并查询。 public class UploadExcel : IHttpHandler,System.Web.SessionState.IReadOnlySessionState
{
public void ProcessRequest (HttpContext context)
{
try
{
context.Response.ContentType = "text/plain";
HttpFileCollection files = context.Request.Files;
string branchCode = context.Session["BranchCode"].ToString();
if (files.Count > 0)
{
string fileName = "Shipment" + DateTime.Now.ToString("yyyyMMddhhmmssms") + ".xls";
string filePath = System.Configuration.ConfigurationManager.AppSettings["SendMailTmp"].ToString();
if (Directory.Exists(filePath) == false)//如果不存在就创建file文件夹
{
Directory.CreateDirectory(filePath);
}
if (files[0].ContentLength == 0)
{
throw new Exception("No file upload!");
}
files[0].SaveAs(filePath + fileName);
DataTable dt = DoOleSql(filePath + fileName,fileName);
string res = JsonHelper.ToGridJson(dt,dt.Rows.Count);
context.Session["GridList"] = res;
context.Response.Write(res);
Directory.Delete(filePath,true);
}
}
catch(Exception ex)
{
throw ex;
}
}
public System.Data.DataTable DoOleSql(string database,string fileName)
{
string strConn = null;
int Num = fileName.LastIndexOf('.');
string fileExt = "";
if (Num > 0)
{
fileExt = fileName.Substring(Num).ToLower();
}
if (fileExt == ".xlsx") //excel2007的文件
{
strConn = "Provider=Microsoft.Ace.OleDb.12.0;;Data Source="" + database + "";Extended Properties="Excel 12.0;HDR=YES;IMEX=1;"";
}
if (fileExt == ".xls")//excel2003的文件
{
strConn = "Provider=Microsoft.Jet.OleDb.4.0;Data Source="" + database + "";Extended Properties="Excel 8.0;HDR=YES;IMEX=1;"";
}
OleDbConnection conn;
DataSet ds = new DataSet();
conn = new OleDbConnection(strConn);
conn.Open();
//返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,"Table" });
//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
string[] strTables = new string[dtSheetName.Rows.Count];
int k;
string temp;
string[] ids = new string[dtSheetName.Rows.Count];
for (k = 0; k < dtSheetName.Rows.Count; k++)
{
temp = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
strTables[k] = temp.Trim('$');
strTableNames[k] = temp;
}
OleDbDataAdapter myCommand = null;
DataTable dt = new DataTable();
//从指定的表明查询数据,可先把所有表明列出来供用户选择
string strExcel = "select * from [" + strTableNames[0] + "]";
myCommand = new OleDbDataAdapter(strExcel,strConn);
myCommand.Fill(dt);
conn.Close();
dt.TableName = strTables[0];
return dt;
}
public bool IsReusable {
get {
return false;
}
}
}
(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |