最近使用asp.net+sqlserver+layui前端框架完成了一个小页面，其中有一个功能是需要将行业代码表导入倒数据库。看到这个行业代码表，刚开始还真是无从下手，经过查询找到一个页面是关于行业代码处理的（word导入mysql表格_从word得到表格数据插入数据库（6位行业代码）），但是感觉这样处理不是太好，不应该让用户去填充excel表格（增加用户的工作量）。
我的解决方案如下：
1、web层
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>行业代码对应页面</title>
<link rel="stylesheet" href="../layui/css/layui.css" />
<script src="https://cdn.bootcdn.net/ajax/libs/jquery/2.2.0/jquery.js"></script>
<script type="text/javascript" src="../layui/layui.js"></script>
</head>
<body>
<div style="margin: 10px 10px 10px 10px">
<div class="layui-btn-container">
<button class="layui-btn layui-btn-normal layui-btn-sm data-add-btn" style="background: #108ee9; padding: 0 20px;" type="button" id="upload">导入行业代码 </button>
</div>
</div>
<script type="text/javascript">
//1.文件上传
layui.use('upload', function () {
var $ = layui.jquery
, upload = layui.upload;//指定允许上传的文件类型
var loadingIndex;; //添加loading,0-2三种方式
upload.render({
elem: '#upload'
, url: '../handler/UploadCode.ashx' //改成您自己的上传接口，我采用的是一般处理程序
, accept: 'file' //普通文件
, before: function () {
loadingIndex = layer.load(1);
}
, done: function (res) {
console.log(res);
layer.close(loadingIndex); //返回数据关闭loading
//如果上传失败
if (res.code > 0) {
if (res.code == 1) {
return layer.alert('上传文件为空，请选择需要上传的excel文件.', {
skin: 'layui-layer-molv' //样式类名 自定义样式
, anim: 2 //动画类型
, icon: 1 // icon
});
} else if (res.code == 2) {
return layer.alert('仅 .xlsx 或 .xls文件类型可以被导入.', {
skin: 'layui-layer-molv' //样式类名 自定义样式
, anim: 2 //动画类型
, icon: 1 // icon
});
} else if (res.code == 3) {
return layer.alert('数据源缺少必要的字段,请检查Excel数据源！', {
skin: 'layui-layer-molv' //样式类名 自定义样式
, anim: 2 //动画类型
, icon: 1 // icon
});
} else if (res.code == 4) {
return layer.alert('Excel文件中没有任何数据，请填充数据！', {
skin: 'layui-layer-molv' //样式类名 自定义样式
, anim: 2 //动画类型
, icon: 1 // icon
});
} else {
return layer.alert('导入失败，请检查网络是否正常.', {
skin: 'layui-layer-molv' //样式类名 自定义样式
, anim: 2 //动画类型
, icon: 1 // icon
});
}
} else if (res.code == 0) {
layer.open({
type: 1
, title: "是否成功" //不显示标题栏 title : false/标题
, closeBtn: true
, area: '300px;'
, shade: 0.8
, id: 'LAY_layuipro' //设定一个id，防止重复弹出
, resize: false
, btn: ['确定']
, btnAlign: 'c'
, moveType: 1 //拖拽模式，0或者1
, content: "<div style='padding: 50px; line-height: 32px; background-color: #108ee9; color: #fff; font-weight: 300;'>导入总条数：" + res.data.total + "<br>插入条数：" + res.data.flaginsert + "<br>失败条数：" + res.data.flagfail + "</div>"
, success: function (layero) {
var btn = layero.find('.layui-layer-btn');
}
});
}
}
});
});
</script>
</body>
</html>
2、一般处理程序代码
<%@ WebHandler Language="C#" Class="UploadHYCode" %>
using System;
using System.Web;
using System.Data;
using System.Data.OleDb;
using BLL;
using System.Text;
using System.Collections.Generic;
using System.Reflection;
using System.IO;
using System.Text.RegularExpressions;
public class UploadHYCode : IHttpHandler {
public void ProcessRequest (HttpContext context) {
context.Response.ContentType = "text/plain";
string Json;//声明一个json变量
//HttpContext.Current.Request.Files.Count 文件个数
if (HttpContext.Current.Request.Files.Count > 0)
{
string fileExtension = System.IO.Path.GetExtension(HttpContext.Current.Request.Files[0].FileName).ToLower();//文件类型
if (fileExtension != ".xlsx" && fileExtension != ".xls")
{
//不是excel文件做出提示
Json = "{\"code\": 2,\"msg\": \"\",\"data\": {\"src\": \"http://cdn.layui.com/123.jpg\"}}";
context.Response.Write(Json);
}
else {
//BLL层，添加工商资源信息
BllBussiness bllResource = new BllBussiness();
//得到客户端上传的文件
HttpPostedFile file = HttpContext.Current.Request.Files[0];
//服务器端要保存的路径
string filePath = HttpContext.Current.Server.MapPath("~/excel/") + file.FileName;
file.SaveAs(filePath);
//1.读取Excel文件并填写到一个DataTable中
DataTable dt = GetExcelData(filePath, fileExtension);
//2.判断excel数据是否符合要求，并构建 传向数据库的数据表
#region 判断excel数据是否符合要求，并构建 传向数据库的数据表
//定义要求的字段数据
string[] headfields = { "门类", "大类", "中类", "小类", "类别名称"};
//判断dtAllMember中是否包含全部要求的字段
for (int j = 0; j < headfields.Length; j++)
{
//只要有一个字段不被包含，则提示"数据源缺少必要的字段"，并退出循环和整个方法
if (!dt.Columns.Contains(headfields[j]))
{
Json = "{\"code\": 3,\"msg\": \"\",\"data\": {\"src\": \"http://cdn.layui.com/123.jpg\"}}";
context.Response.Write(Json);
}
}
//判断数据源中是否有数据
if (dt.Rows.Count == 0)
{
Json = "{\"code\": 4,\"msg\": \"\",\"data\": {\"src\": \"http://cdn.layui.com/123.jpg\"}}";
context.Response.Write(Json);
}
//手动创建的新数据表工商资源数据表
DataTable dtAddResource = new DataTable("dt_AddResource"); //创建一个名为dt_AddResource的DataTalbe
//为dt表内建立Column（表头）,
dtAddResource.Columns.Add(new DataColumn("id", typeof(System.Data.SqlTypes.SqlGuid)));
dtAddResource.Columns.Add(new DataColumn("parent_id", typeof(System.Data.SqlTypes.SqlGuid)));
dtAddResource.Columns.Add(new DataColumn("codename", typeof(string)));
dtAddResource.Columns.Add(new DataColumn("categoryname", typeof(string)));
dtAddResource.Columns.Add(new DataColumn("stime", typeof(DateTime)));
dtAddResource.Columns.Add(new DataColumn("isuse", typeof(int)));
int total = dt.Rows.Count;//总数据条数
int flaginsert = 0;//插入的数据条数
int flagfail = 0; //失败的数据条数
System.Data.SqlTypes.SqlGuid menid=System.Guid.NewGuid();
System.Data.SqlTypes.SqlGuid daid=System.Guid.NewGuid();
System.Data.SqlTypes.SqlGuid zhongid=System.Guid.NewGuid();
string menlei = "";
//从上传的Excel转换为的datatable表中取出数据，取出需要的数据放入工商资源表
for (int intRow = 0; intRow < dt.Rows.Count; intRow++)
{
DataRow drAddResource = dtAddResource.NewRow();
if (!string.IsNullOrEmpty(dt.Rows[intRow]["门类"].ToString().Trim()))
{
drAddResource["id"] = menid = System.Guid.NewGuid();
//drAddResource["parent_id"]= "00000000-0000-0000-0000-000000000000";
drAddResource["codename"] = menlei = dt.Rows[intRow]["门类"].ToString();
//类别名称
drAddResource["categoryname"] = dt.Rows[intRow]["类别名称"].ToString();
//添加日期
drAddResource["stime"] = DateTime.Now;
//是否可用
drAddResource["isuse"] = 1;
dtAddResource.Rows.Add(drAddResource); //将一整条数据写入表中
}
else if (!string.IsNullOrEmpty(dt.Rows[intRow]["大类"].ToString().Trim()))
{
//DataRow drAddResource = dtAddResource.NewRow();
drAddResource["id"] = daid = System.Guid.NewGuid();
drAddResource["parent_id"] = menid;
drAddResource["codename"] = dt.Rows[intRow]["大类"].ToString();
//类别名称
drAddResource["categoryname"] = dt.Rows[intRow]["类别名称"].ToString();
//添加日期
drAddResource["stime"] = DateTime.Now;
//是否可用
drAddResource["isuse"] = 1;
dtAddResource.Rows.Add(drAddResource); //将一整条数据写入表中
}
else if (!string.IsNullOrEmpty(dt.Rows[intRow]["中类"].ToString().Trim()) && !string.IsNullOrEmpty(dt.Rows[intRow]["小类"].ToString().Trim()))
{
drAddResource["id"] = zhongid = System.Guid.NewGuid();
drAddResource["parent_id"] = daid;
drAddResource["codename"] = menlei + dt.Rows[intRow]["小类"].ToString();
//类别名称
drAddResource["categoryname"] = dt.Rows[intRow]["类别名称"].ToString();
//添加日期
drAddResource["stime"] = DateTime.Now;
//是否可用
drAddResource["isuse"] = 1;
dtAddResource.Rows.Add(drAddResource); //将一整条数据写入表中
} else if (!string.IsNullOrEmpty(dt.Rows[intRow]["中类"].ToString().Trim()) && string.IsNullOrEmpty(dt.Rows[intRow]["小类"].ToString().Trim())) {
drAddResource["id"] = zhongid = System.Guid.NewGuid();
drAddResource["parent_id"] = daid;
drAddResource["codename"] = dt.Rows[intRow]["中类"].ToString();
//类别名称
drAddResource["categoryname"] = dt.Rows[intRow]["类别名称"].ToString();
//添加日期
drAddResource["stime"] = DateTime.Now;
//是否可用
drAddResource["isuse"] = 1;
dtAddResource.Rows.Add(drAddResource); //将一整条数据写入表中
}
else {
drAddResource["id"] = System.Guid.NewGuid();
drAddResource["parent_id"] = zhongid;
drAddResource["codename"] = menlei + dt.Rows[intRow]["小类"].ToString();
//类别名称
drAddResource["categoryname"] = dt.Rows[intRow]["类别名称"].ToString();
//添加日期
drAddResource["stime"] = DateTime.Now;
//是否可用
drAddResource["isuse"] = 1;
dtAddResource.Rows.Add(drAddResource); //将一整条数据写入表中
}
}
#endregion
DataColumnCollection dcc = dtAddResource.Columns; //列数
// flaginsert = dtAddResource.Rows.Count;
bool i = bllResource.ImportHycode(dtAddResource,dcc);
//3.将筛选过的数据表导入数据库
if (i==true)
{
flaginsert = dtAddResource.Rows.Count;
flagfail =dt.Rows.Count-flaginsert;
//返回结果
// Json = "{\"code\": 0,\"msg\": \"\",\"data\": {\"src\": \"http://cdn.layui.com/123.jpg\"}}";
Json = "{\"code\": 0,\"msg\": \"\",\"data\": {\"flaginsert\": "+flaginsert+",\"flagfail\": "+flagfail+",\"total\": "+total+"}}";
context.Response.Write(Json);
}
else {
//返回结果
Json = "{\"code\": 5,\"msg\": \"\",\"data\": {\"src\": \"http://cdn.layui.com/123.jpg\"}}";
context.Response.Write(Json);
}
}
}
else {
//没有文件，请选择文件
Json = "{\"code\": 1,\"msg\": \"\",\"data\": {\"src\": \"http://cdn.layui.com/123.jpg\"}}";
context.Response.Write(Json);
}
}
public bool IsReusable {
get {
return false;
}
}
private DataTable GetExcelData(string filePath, string fileExtension)
{
//DataSet ds = new DataSet();
//定义一个DataTable数据表
DataTable dt = null;
string connStr03 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;"; ;
string connStr07 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES'";
string queryStr = "SELECT * FROM [Sheet1$]";
OleDbConnection conn03 = new OleDbConnection(connStr03);
OleDbConnection conn07 = new OleDbConnection(connStr07);
if (fileExtension == ".xls")
{
OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn03);
try
{
//把Excel数据填充给DataTable
dt = new DataTable();
myAdapter.Fill(dt);
//返回数据表
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
myAdapter.Dispose();
conn03.Close();
conn03.Dispose();
//删除上传的Excel文件（因为该文件的存在会占用多余的网站空间）
if (File.Exists(filePath))
{
File.Delete(filePath);
}
}
}
else if (fileExtension == ".xlsx")
{
OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn07);
try
{
//把Excel数据填充给DataTable
dt = new DataTable();
myAdapter.Fill(dt);
//返回数据表
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
myAdapter.Dispose();
conn07.Close();
conn07.Dispose();
//删除上传的Excel文件（因为该文件的存在会占用多余的网站空间）
if (File.Exists(filePath))
{
File.Delete(filePath);
}
}
}
return dt;
}
}3、BLL业务逻辑层的方法
#region 批量导入行业代码
public bool ImportHycode(DataTable dt, DataColumnCollection dtColum)
{
return daoBussiness.ImportHycode(dt, dtColum);
}
#endregion4、DAL数据访问层代码
#region 批量导入行业代码
public bool ImportHycode(DataTable dt, DataColumnCollection dtColum)
{
//定义一个整型标记变量
bool result;
//调用sqlHelper的"批量导入datatable表"的方法
result = sqlHelper.InsertTable(dt, "chiye_hangyecode", dtColum);
//返回结果
return result;
}
#endregion5、sqlhelper方法
#region 批量导入DataTable
/// <summary>批量导入DataTable
/// 批量导入DataTable
/// </summary>
/// <param name="dt">DataTable数据表</param>
/// <param name="tableName">表名</param>
/// <param name="dtColum">数据列集合</param>
/// <return>Boolean值:true成功，false失败</return>
public Boolean InsertTable(DataTable dt, string tableName, DataColumnCollection dtColum)
{
using (TransactionScope scope1 = new TransactionScope(TransactionScopeOption.Required))
{
using (SqlBulkCopy sqlBC = new SqlBulkCopy(strConn1, SqlBulkCopyOptions.KeepIdentity))
{
//一次批量的插入的数据量：sqlBC.BatchSize = 1000;
sqlBC.BatchSize = 1000;
//设置要批量写入的表
sqlBC.DestinationTableName = tableName;
for (int i = 0; i < dtColum.Count; i++)
{
sqlBC.ColumnMappings.Add(dtColum[i].ColumnName.ToString(), dtColum[i].ColumnName.ToString());
}
try
{
//批量写入
sqlBC.WriteToServer(dt);
scope1.Complete();
return true;
}
catch (Exception ex)
{
throw ex;
}
}
}
}
#endregion至此整个行业代码数据库设计及导入就完成了，数据库数据如下：
欢迎各位高手一起探讨更好滴方法。