C#layui实现Excel⽂件导⼊，并显⽰数据
⼀、实现思路【重点】
⼆、不⾜⽅⾯
三、代码实现
四、我的收获【重点】
⼀、实现思路
点击按钮的时候，将⽂件上传并指定上传路径(我这⾥是上传到服务器上的⽂件⾥⾯)
将⽂件上传成功后，该⽂件就在服务器中的⼀个⽂件⾥⾯了
将上传成功的⽂件当成⼀个“移动数据库”，利⽤“OleDbConnection和DataSet”连接该“移动数据库并将读取到的数据全部暂时存储进DataSet”⾥⾯
在将DataSet⾥⾯的数据循环添加进Sqlserver数据库
注意：也就是说，将上传成功的⽂件当做⼀个“数据库”，将“DataSet”当作⼀个将⽂件数据存储进SqlServer数据库的⼀个中间过渡
⼆、不⾜的⽅⾯
我的思路基本上是没有问题的，毕竟实践过
但是，在实际实现⽅⾯有⼀个部分耗内存效率较低的
三、代码实现
<div class="layui-fluid">
<div class="layui-row layui-col-space15">
<div class="layui-row">
<div class="layui-card">
<div class="layui-card-header">
<div>
<input type="text" name="username" placeholder="请输⼊搜索条件" autocomplete="off"class="layui-input">
<button class="layui-btn" lay-submit="" lay-filter="sreach"><i class="layui-icon"></i></button>
<button type="button"class="layui-btn layui-btn-normal layui-btn-radius" id="test3"><i class="layui-icon">< /i>导⼊Excel</button>
<button type="button"class="layui-btn layui-btn-normal layui-btn-radius" id="showData"><i class="layui-ico n"></i>显⽰数据</button>
</div>
</div>
<!--下拉菜单-->
<div class="layui-card-body">
<!--显⽰数据-->
<table id="demo" lay-filter="test"></table>
<!--显⽰分页-->
</div>
</div>
</div>
</div>
</div>
<script>
layui.use('upload', function (){
var $ = layui.jquery, upload = layui.upload;
//指定允许上传的⽂件类型
elem:'#test3'
, url:'localhost:63720/api/ImportExcel'//改成您⾃⼰的上传接⼝
, accept:'file'//允许上传⽂件的类型
, auto:true//是否⾃动完成⽂件上传
, exts:'xls|xlsx'//指定⽂件类型上传
, method:'POST'
, done: function (res){getsavefilename
layer.msg('上传成功');
console.log(res);
}, error: function (){
layer.msg('上传失败');
console.log(res);
}
});
});//layui end
</script>
/// <summary>
/// 导⼊
/// </summary>
/// <param name="organizationId"></param>
/// <returns></returns>
[HttpPost,Route("api/ImportExcel")]
public HttpResponseMessage ImportExcel()
{
HttpPostedFile file = HttpContext.Current.Request.Files[0];
string FileName;
string savePath;
if(file ==null|| file.ContentLength <=0)
{
showError("⽂件为空!");
}
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 = DateTime.Now.ToString("yyyyMMddhhmmss")+ fileEx;
if(!FileType.Contains(fileEx))
return showError("⽂件类型不对，只能导⼊xls和xlsx格式的⽂件！");
if(filesize >= Maxsize)
return showError("上传⽂件超过4M，不能上传！");
string path = HostingEnvironment.MapPath("~/files/");
if(!Directory.Exists(path))
Directory.CreateDirectory(path);//根据路径创建指定⽂件
savePath = Path.Combine(path, FileName);
file.SaveAs(savePath);
string strConn;
if(fileEx ==".xls")
strConn ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ savePath +";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
else
strConn ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ savePath +";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
OleDbConnection conn =new OleDbConnection(strConn);//OleDbConnection：相当于数据库连接，只是这个可以连接所有类型的数据库
conn.Open();
OleDbDataAdapter myCommand =new OleDbDataAdapter("select * from [Sheet1$]", strConn);
DataSet myDataSet =new DataSet();//DataSet：相当于⼀个移动的数据库
//Fill⽅法隐式执⾏OleDbDataAdapter的SelectCommand中的SQL查询，查询的结果⽤于定义DataSet表的结构，并⽤数据来填充表。
myCommand.Fill(myDataSet,"ExcelInfo");//ExcelInfo：表名
conn.Close();
DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();//DataTable：相当于数据库的⼀个表
//当指定到第三⾏的时候，才开始将⼀⾏的数据全部录⼊到数据库的对应的表中
int number =0;
for(int i =0; i < table.Rows.Count; i++)
{
number +=1;
if(number <3)
continue;
string MCId = table.Rows[i][0].ToString();
string MCNumber = table.Rows[i][1].ToString();
string MCSurveyVessel = table.Rows[i][2].ToString();
string MCVoyage = table.Rows[i][3].ToString();
string MSeaArea = table.Rows[i][4].ToString();
string MStandBy = table.Rows[i][5].ToString();
string MCoordinatesX = table.Rows[i][6].ToString();
string MCoordinatesY = table.Rows[i][7].ToString();
string MDetails = table.Rows[i][8].ToString();
string MCDepth = table.Rows[i][9].ToString();
string MCVLength = table.Rows[i][10].ToString();
string MCStorageLocation = table.Rows[i][11].ToString();
string MCPreservationStatus = table.Rows[i][12].ToString();
string MCNotes = table.Rows[i][13]as string;
if(MCId ==""|| MCId ==null)
continue;
if(MCPreservationStatus ==""|| MCPreservationStatus ==null)
MCPreservationStatus ="0.00";
Model.bj_MasterCommodities commodities =new Model.bj_MasterCommodities();
commodities.MCId =int.Parse(MCId);
commodities.MCNumber = MCNumber;
commodities.MCSurveyVessel = MCSurveyVessel;
commodities.MCVoyage = MCVoyage;
commodities.MCDepth = Decimal.Parse(MCDepth);
commodities.MCVLength = Decimal.Parse(MCVLength);
commodities.MCStorageLocation = MCStorageLocation;
commodities.MCPreservationStatus = Double.Parse(MCPreservationStatus);
commodities.MCNotes = MCNotes as string;
getMasterCommoditiesData(commodities);
getMerchandiseData(new Model.bj_Merchandise()
{
MId =getGUID(),
MCNumber = MCNumber,
MSeaArea = MSeaArea,
MStandBy = MStandBy,
MCoordinatesX = Decimal.Parse(MCoordinatesX),
MCoordinatesY = Decimal.Parse(MCoordinatesY),
MDetails = MDetails as string,
});
}//for end
return showError("导⼊成功！");
}//else end
return showError("导⼊失败！");
}//function end
/// <summary>
///
/// </summary>
/// <param name="message"></param>
/
// <returns></returns>
public HttpResponseMessage showError(string message)
{
JavaScriptSerializer serializer =new JavaScriptSerializer();
ExceptionUrl exceptionUrl =new ExceptionUrl(){ code =0, msg = message };
string str = serializer.Serialize(exceptionUrl);
HttpResponseMessage result =new HttpResponseMessage
{
Content =new StringContent(str, Encoding.GetEncoding("UTF-8"),"text/html")
};
return result;
}
四、我的收获
其实，导⼊Excel⽂件的本质就是：上传⽂件到指定⽂件夹内，将该⽂件夹内的⽂件当作⼀个“移动数据库”，再通过“DataSet”将该移动数据库和SqlServer数据库建⽴联系。
以上就是这样啦，好好学习⾯对现实§(￣▽￣)§
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论