去出差的时候应客户要求要要将Excel 文件内的数据批量导入到数据库中，而且有各种不同种类的表格，如果每一个表格多对应一个数据表的话，
按照正常的方法应该是创建数据表，创建数据库中映射的数据模型，然后数据访问层，业务层，在为每个表创建一个展示页面......这样一套下来花费
的时间多不说在以后客户又有新的表格需要导入那么是不是也要按照三层的步骤一步一步的来做呢？答案是否定的。这里我向大家介绍一个比较灵活
方式来完成所有的功能，在拓展性上也很好，可以移植到任何程序中使用：
不多废话直接看程序，程序是最好的解释：
本程序是通过XML 来保存数据库字段和需要在界面上显示的表头
首先在网站的根目录下创建一个XML 文件来保存动态生成的数据表以及每一列和表头对应的中文名：
<?xml version="1.0" encoding="utf-8"?>
<DBTable>
<table>
<CName>任职资格台账</CName>
<EName>rzzgtz</EName>
<Identity>R_Id</Identity>
<columns>
<eColumn eName="xuhao" eType="nvarchar(MAX)" cName="序号" eLength="varchar(10)" />
<eColumn eName="Ztype" eType="nvarchar(MAX)" cName="证书类别" eLength="varchar(10)" />
<eColumn eName="bM" eType="nvarchar(MAX)" cName="部门" eLength="varchar(10)" />
<eColumn eName="Name" eType="nvarchar(MAX)" cName="姓名" eLength="varchar(10)" />
<eColumn eName="Gender" eType="nvarchar(MAX)" cName="性别" eLength="varchar(10)" />
<eColumn eName="CID" eType="nvarchar(MAX)" cName="省份证号" eLength="varchar(10)" />
<eColumn eName="tTemp" eType="nvarchar(MAX)" cName="文化程度" eLength="varchar(10)" />
<eColumn eName="ziwu" eType="nvarchar(MAX)" cName="职务/职称" eLength="varchar(10)" />
<eColumn eName="fzjg" eType="nvarchar(MAX)" cName="发证机关" eLength="varchar(10)" />
<eColumn eName="XCID" eType="nvarchar(MAX)" cName="证书编号" eLength="varchar(10)" />
<eColumn eName="Qtime" eType="nvarchar(MAX)" cName="取证时间" eLength="varchar(10)" />
<eColumn eName="fTime" eType="nvarchar(MAX)" cName="复审时间" eLength="varchar(10)" />
<eColumn eName="Other" eType="nvarchar(MAX)" cName="备注" eLength="varchar(10)" />
</columns>
</table>
<DBTable>
DBTable 节点是根节点 内部的table 保存的是数据表中的相关属性。
节点 <CName></CName>保存的是中文名称,也是菜单名称。
节点<EName></EName>保存的是名称和数据表中的表明一一对应。
节点 <Identity></Identity>保存的是表的主键名称。
节点<columns> </columns> 保存的是数据表中对应的数据列属性
内部节点 <eColumn eName="xuhao" eType="nvarchar(MAX)" cName="序号" eLength="varchar(10)" />
属性：eName [数据库中名称]，eType[数据类型] cName [表头名称] eLength 可有可无的一个属性
以上是XML 表结构的一些说明;
下面需要一个类来专门操作XML对节点的一些操作和检索
/**
*张国强 2014-02-19 15:06
*XML文件的读写方法
*编写目的：将中文表名和数据中的表名进行绑定
*/ using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//
using System.Xml;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration; namespace CommonLib
{
public class XMLBase : Page
{
//声明一个XMLDom对象
XmlDocument xmlDoc = null;
XmlNode root = null;
/// <summary>
/// 文件地址
/// </summary>
string xmlPath = ConfigurationManager.ConnectionStrings["XMLPath"].ConnectionString;
/// <summary>
/// 第一步加载xml文件
/// </summary>
public void LoadDome()
{
xmlDoc = new XmlDocument();
string path = Server.MapPath(xmlPath);//转换为物理路径
xmlDoc.Load(path);//加载文件
root = xmlDoc.SelectSingleNode("DBTable");
}
/// <summary>
/// 向XML中添加一个节点
/// </summary>
/// <param name="cName">中文名称</param>
/// <param name="eName">对应的数据表名称</param>
public void InsertNode(string cName, string eName, string id)
{
XmlElement table = xmlDoc.CreateElement("table");
//添加中文名
XmlElement c_Name = xmlDoc.CreateElement("CName");
c_Name.InnerText = cName;
table.AppendChild(c_Name);
//添加英文名
XmlElement e_Name = xmlDoc.CreateElement("EName");
e_Name.InnerText = eName;
table.AppendChild(e_Name);
//添加主键
XmlElement iditent = xmlDoc.CreateElement("Identity");
iditent.InnerText = id;
table.AppendChild(iditent);
root.AppendChild(table);
string path = Server.MapPath(xmlPath);//转换为物理路径
xmlDoc.Save(path);//将添加的数据保存到文件中
} /// <summary>
/// 向子节点中添加列名数据
/// </summary>
/// <param name="cTable">中文名称表明</param>
/// <param name="eTableName">英文名称表明</param>
/// <param name="eName">列名英文名</param>
/// <param name="eType">数据类型</param>
/// <param name="cName">列名中文名</param>
/// <param name="eLenght">数据长度</param>
public void InsertNode(string cTable, string eTableName, string eName, string eType, string cName, string eLenght)
{
//找到父节点名
XmlNode fatherNode = xmlDoc.SelectSingleNode("DBTable");
XmlNode nodeRoot = null; for (int i = ; i < fatherNode.ChildNodes.Count; i++)
{
nodeRoot = fatherNode.ChildNodes[i];
var c_Name = nodeRoot.ChildNodes[].InnerText;
var e_Name = nodeRoot.ChildNodes[].InnerText;
if (cName == c_Name && e_Name == eName)
{
break;
}
}
//想父节点添加数据
var se = (nodeRoot as XmlNode).SelectSingleNode("columns");
XmlElement columns = null;
if (se == null)
{
columns = xmlDoc.CreateElement("columns");
}
else
{
columns = (se as XmlElement);
} XmlElement eColumn = xmlDoc.CreateElement("eColumn");
eColumn.SetAttribute("eName", eName);
eColumn.SetAttribute("eType", eType);
eColumn.SetAttribute("cName", cName);
eColumn.SetAttribute("eLength", eLenght);
columns.AppendChild(eColumn);
nodeRoot.AppendChild(columns);
string path = Server.MapPath(xmlPath);//转换为物理路径
xmlDoc.Save(path);//将添加的数据保存到文件中 } /// <summary>
/// 检查节点绑定时候有重复的内容
/// </summary>
/// <param name="cName">中文名称</param>
/// <param name="eName">对应的数据表名称</param>
/// <returns>存在返回false 不存在返回true</returns>
public bool CheckNode(string cName, string eName)
{
bool resout = true;
XmlNode nodeRoot = xmlDoc.SelectSingleNode("DBTable");//找到根节点
for (int i = ; i < nodeRoot.ChildNodes.Count; i++)
{
var tab = nodeRoot.ChildNodes[i];
var c_Name = tab.ChildNodes[].InnerText;
var e_Name = tab.ChildNodes[].InnerText; if (cName == c_Name || e_Name == eName)
{
resout = false;
}
}
return resout;
}
/// <summary>
/// 删除节点
/// </summary>
/// <param name="cName"></param>
/// <param name="eName"></param>
/// <param name="res"></param>
public void DeleteNode(string cName, string eName, bool res)
{
XmlNode node = GetXMLNode(cName, eName);
if (node != null)
{
XmlNode par = node.ParentNode;
if (res)
{
string sql = " drop table " + eName;
par.RemoveChild(par);
}
else
{
par.RemoveChild(node);
}
}
string path = Server.MapPath(xmlPath);//转换为物理路径
xmlDoc.Save(path);//将添加的数据保存到文件中
}
/// <summary>
/// 检查是否存在值相同的节点
/// </summary>
/// <param name="cName">中文表明</param>
/// <param name="eName">英文表明</param>
/// <returns>查找到的节点</returns>
public XmlNode GetXMLNode(string cName, string eName)
{
XmlNode resout = null;
XmlNode nodeRoot = xmlDoc.SelectSingleNode("DBTable");//找到根节点
for (int i = ; i < nodeRoot.ChildNodes.Count; i++)
{
var tab = nodeRoot.ChildNodes[i];
var c_Name = tab.ChildNodes[].InnerText;
var e_Name = tab.ChildNodes[].InnerText; if (cName == c_Name && e_Name == eName)
{
resout = tab;
break;
}
}
return resout;
} /// <summary>
/// 修改节点
/// </summary>
/// <param name="cName">y中文名</param>
/// <param name="EName">英文名</param>
/// <returns></returns>
public bool UpdateNode(string cName, string eName)
{
bool resoult = false;
XmlNode nodeRoot = xmlDoc.SelectSingleNode("DBTable");//找到根节点
for (int i = ; i < nodeRoot.ChildNodes.Count; i++)
{
var tab = nodeRoot.ChildNodes[i];
if (tab.ChildNodes[].InnerText == eName)
{
tab.ChildNodes[].InnerText = cName;
string path = Server.MapPath(xmlPath);//转换为物理路径
xmlDoc.Save(path);//将添加的数据保存到文件中
resoult = true;
} }
return resoult; } /// <summary>
/// 获取数据表字段
/// </summary>
/// <param name="eName">英文表名</param>
/// <param name="cName">中文表名</param>
/// <returns>用’¦‘ 分割 第一个数据列为因为列表名 用‘，’ 号隔开 第二个数据列为备注名用‘[’ 隔开</returns>
public string Check(string eName, string cName)
{
string resout = ""; XmlNode node = GetXMLNode(cName, eName); if (node != null)
{
//获取列集合
var columns = node.SelectSingleNode("columns");
for (int i = ; i < columns.ChildNodes.Count; i++)
{
resout += columns.ChildNodes[i].Attributes[].Value.ToString() + ",";
}
resout = resout.Substring(, resout.Length - );
resout += "¦";
for (int i = ; i < columns.ChildNodes.Count; i++)
{
resout += columns.ChildNodes[i].Attributes[].Value.ToString() + "[";
}
resout = resout.Substring(, resout.Length - );
} return resout;
} #region 张国强 2014-02-24 /// <summary>
/// 获取所有的数据表名称
/// </summary>
/// <returns>获取xml数据中的中文名和英文名格式【中文名，英文名】结果需要拆分以逗号拆分</returns>
public List<XMLTableInfo> GetTableList()
{
List<XMLTableInfo> tableList = new List<XMLTableInfo>();
var node = xmlDoc.SelectSingleNode("DBTable");
for (int i = ; i < node.ChildNodes.Count; i++)
{
XMLTableInfo tb = new XMLTableInfo();
tb.CName = node.ChildNodes[i].ChildNodes[].InnerText.ToString();
tb.EName = node.ChildNodes[i].ChildNodes[].InnerText.ToString();
tableList.Add(tb);
}
return tableList;
} public string GetIdentity(string eName, string cName)
{
string iditent = "";
//获取节点
XmlNode node = GetXMLNode(cName, eName);
if (node != null)
{
iditent = node.SelectSingleNode("Identity").InnerText;
}
return iditent;
}
#endregion } /// <summary>
/// XML 数据表名称结构
/// </summary>
public class XMLTableInfo
{
/// <summary>
/// 中文名称
/// </summary>
public string CName { get; set; }
/// <summary>
/// 英文名称
/// </summary>
public string EName { get; set; }
}}
Web.Config
<?xml version="1.0" encoding="utf-8"?> <!--
有关如何配置 ASP.NET 应用程序的详细信息，请访问
http://go.microsoft.com/fwlink/?LinkId=169433
--> <configuration>
<connectionStrings>
<!--数据库连接字段-->
<add name="SqlServer" connectionString="server=192.168.1.124;database=ExcelToSqlserver; uid=sa; pwd=123;"/>
<!--XML文件路径-->
<add name="XMLPath" connectionString="~/DBTablePage.xml" />
<!--EXCEL 数据导入表文件-->
<add name="ExcelUpLoadPath" connectionString="~/upload/EXCEL/" />
<add name="RecorddPath" connectionString="../upload/attached/" /> </connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.0" />
<httpRuntime requestValidationMode="2.0" />
</system.web> </configuration>
还需要一个EXCEL的Helper类
/**
*张国强 2014-02-10
*目的管理excel文件的读写
*数据读取，表头检查
*数据类型检查
*/ using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;
using System.IO; using System.Configuration;
using System.Web.UI.WebControls; namespace CommonLib
{
public class ExcelHelper : System.Web.UI.Page
{
static string conStr = ConfigurationManager.ConnectionStrings["SqlServer"].ToString();
static SqlConnection con = new SqlConnection(conStr); /// <summary>
/// 通过不同的文件名称获取不同的链接对象
/// </summary>
/// <param name="path">上传路径地址</param>
/// <returns>返回链接对象</returns>
public static OleDbConnection GetConnection(string path)
{
/*Office 2007*/
string ace = "Microsoft.ACE.OLEDB.12.0";
/*Office 97 - 2003*/
string jet = "Microsoft.Jet.OLEDB.4.0";
string xl2007 = "Excel 12.0 Xml";
string xl2003 = "Excel 8.0";
string imex = "IMEX=1";
/* csv */
string text = "text";
string fmt = "FMT=Delimited";
string hdr = "NO";
string conn = "Provider={0};Data Source={1};Extended Properties=\"{2};HDR={3};{4}\";";
string ext = Path.GetExtension(path); DataTable dt = new DataTable("data");
switch (ext.ToLower())
{
case ".xlsx":
conn = String.Format(conn, ace, Path.GetFullPath(path), xl2007, hdr, imex);
break;
case ".xls":
conn = String.Format(conn, jet, Path.GetFullPath(path), xl2003, hdr, imex);
break;
case ".csv":
conn = String.Format(conn, jet, Path.GetDirectoryName(path), text, hdr, fmt);
//sheet = Path.GetFileName(path);
break;
default:
throw new Exception("File Not Supported!");
}
OleDbConnection con = new OleDbConnection(conn);
return con;
} /// <summary>
/// 根据excel路径和sheet名称，返回excel的DataTable
/// </summary>
public static DataTable GetExcelDataTable(string path, string tname)
{
string select = string.Format("SELECT * FROM [{0}$]", tname);
string ext = Path.GetExtension(path);
OleDbDataAdapter oda;
DataTable dt = new DataTable("data");
OleDbConnection con = GetConnection(path);
con.Open();
oda = new OleDbDataAdapter(select, con); oda.Fill(dt);
con.Close();
return dt;
} /// <summary>
/// 获取数据表中的Sheet名称
/// 2014-02-26
/// 问题后延
/// </summary>
/// /// <param name="saveName">上传文件路径名称</param>
/// <returns>Sheet数据列表</returns>
public static List<string> GetExcelSheetName(string saveName)
{
List<string> list = null;
Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbooks wbs = excelApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(saveName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
int count = wb.Worksheets.Count;
if (count > )
{
list = new List<string>();
for (int i = ; i < count; i++)
{
string name = ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i]).Name;
list.Add(name);
}
wb.Close(null, null, null);
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
excelApp = null;
wbs = null;
wb = null;
GC.Collect();
}
return list;
} /// <summary>
/// 读取Excel表中有效数据生成数据集合
/// 2014-02-26
/// </summary>
/// <param name="dTable">DataTable</param>
/// <param name="rowIndex">行有效数据开始</param>
/// <param name="colIndex">列有效数据开始</param>
/// <param name="saveName">Excel上传文件保存路径</param>
/// <param name="sheetName">Sheet名称 默认为Sheet1</param>
/// <returns>返回数据表数据用逗号隔开</returns>
public static List<string> GetExcelDate(int rowIndex, int colIndex, string saveName, string sheetName = "Sheet1")
{
List<string> list = null;
DataTable dtabe = ExcelHelper.GetExcelDataTable(saveName, sheetName);
int icolums = dtabe.Columns.Count;
int irows = dtabe.Rows.Count;
//获取数据表中数据
if (irows > rowIndex)
{
list = new List<string>(); for (int j = rowIndex; j < irows; j++)
{ string da = "";
for (int i = colIndex; i < icolums; i++)
{ da += dtabe.Rows[j][i].ToString() + ",";
} if (da.Length != icolums)//判读空数据行
{
da = da.Substring(, da.Length - );
list.Add(da); } }
}
return list;
} /// <summary>
/// 检查Excel数据表中的表头和数据库中的表头时候相符
/// 2014-02-26
/// </summary>
/// <param name="eName">数据库中的英文表名</param>
/// <param name="cName">中文备注表名名称</param>
/// <param name="saveName">上传Excel文件保存路径地址服务器地址</param>
/// <param name="sheetName">Excel数据表中Sheet名称</param>
/// <returns>返回检测结果，结果为空值验证通过，不为空值验证失败不可以正常导入</returns>
public string CheckExcelTitle(string eName, string cName, string saveName, string sheetName = "Sheet1")
{
string resout = "";
XMLBase xmlBase = new XMLBase();
xmlBase.LoadDome();
//获取数据表格字段值
string che = xmlBase.Check(eName, cName);
string[] columesInfo = null;
if (che != "")
{ columesInfo = che.Split('¦');
}
string ename = columesInfo[];
string cname = columesInfo[];
//获得数据表中列字段值
// ename = ename.Substring(0, ename.Length - 1);
//获得XML数据列名
// cname = cname.Substring(0, cname.Length - 1);
string[] TbE_name = ename.Split(',');
string[] TbC_name = cname.Split('[');
DataTable dtabe = ExcelHelper.GetExcelDataTable(saveName, sheetName);
int icolums = dtabe.Columns.Count;
int irows = dtabe.Rows.Count;
//获取表头
List<string> list = new List<string>();
string na = "";
for (int i = ; i < icolums; i++)
{
na += dtabe.Rows[][i].ToString() + "+";
list.Add(dtabe.Rows[][i].ToString());
} if (list.Count != TbC_name.Length)
{
resout = "列名和数据库中列名长度不一样！";
}
else
{
for (int i = ; i < TbC_name.Length; i++)
{
if (TbC_name[i] != list[i])
{
string mes = "第" + (i + ) + "列表头不是【" + TbC_name[i] + "】请重新调整";
resout += mes;
}
}
} return resout; } /// <summary>
/// 获取SQl数据集
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sql)
{
if (sql == "")
{
return null;
}
SqlConnection con = new SqlConnection();
con.ConnectionString = conStr;
con.Open();
SqlDataAdapter adp = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
adp.Fill(ds, "table");
con.Close();
return ds;
} #region 2014-02-28 生成执行语句
/// <summary>
/// 生成添加数据Sql语句
/// 2014-02-26
/// </summary>
/// <returns>通过传入参数返回添加Sql语句最后需要添</returns>
public static string InserToSqlQuerystring(string eName, string cName)
{
string sql = "";
XMLBase xml = new XMLBase();
xml.LoadDome();
//获取列名
string che = xml.Check(eName, cName);
if (che != "")
{
sql = "insert into " + eName + " (";
//截取数据
string colums = che.Split('¦')[];
sql += colums + ") values ";
}
return sql;
} /// <summary>
/// 获取删除语句
/// </summary>
/// <param name="eName">英文名称</param>
/// <param name="cName">中文名称</param>
/// <param name="id">需要删除的Id编号</param>
/// <returns></returns>
public static string DeleteQuerystring(string eName, string cName, string id)
{
string sql = "";
//获取数据表数据列
XMLBase xml = new XMLBase();
xml.LoadDome();
//获取列名
string che = xml.GetIdentity(eName, cName);
if (id.IndexOf(',') == -)
{
sql = "delete " + eName + " where " + che + "= " + id;
}
else
{
sql = "delete " + eName + " where " + che + " in ( " + id + " )";
} return sql;
}
/// <summary>
/// 动态生成修改语句
/// </summary>
/// <param name="eName">英文名</param>
/// <param name="cName">中文名</param>
/// <param name="id">编号ID</param>
/// <returns> 语句</returns>
public static string UpdateQueryString(string eName, string cName, int id)
{
string sql = "";
XMLBase xml = new XMLBase();
xml.LoadDome();
//获取列名
string che = xml.Check(eName, cName);
sql = "update " + eName + " set ";
if (che != "")
{
string[] colums = che.Split('¦')[].Split(',');
for (int i = ; i < colums.Length; i++)
{
sql += colums[i] + "= {" + i + "} ,";
}
}
sql = sql.Substring(, sql.Length - );
sql += " where " + xml.GetIdentity(eName, cName) + "=" + id;
return sql; } /// <summary>
/// 查询出一条数据
/// </summary>
/// <param name="eName">中文表明</param>
/// <param name="cName">英文表明</param>
/// <param name="id">主键Id</param>
/// <returns></returns>
public static string GetModelQueryString(string eName, string cName, int id)
{
string sql = "";
XMLBase xBase = new XMLBase();
xBase.LoadDome();
sql = "select * from " + eName + " where " + xBase.GetIdentity(eName, cName) + " =" + id;
return sql;
}
//2014-03-03 生成执行语句
/// <summary>
/// 获取查询数据总数量
/// </summary>
/// <param name="eName"></param>
/// <param name="cName"></param>
/// <returns></returns>
public static string GetCountQueryString(string eName, string where)
{
string countsql = "select count(*) from " + eName + " ";
if (where != "")
{
countsql += " where " + where;
} return countsql;
} /// <summary>
///动态生成分页语句
/// </summary>
/// <param name="eName"></param>
/// <param name="cName"></param>
/// <param name="strWhere">查询条件</param>
/// <param name="orderby">派</param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static string GetPageQueryStrong(string eName, string cName, string strWhere, string orderby, int pageIndex, int pageSize)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by T." + orderby);
}
else
{
XMLBase ba = new XMLBase();
ba.LoadDome(); strSql.Append("order by T." + ba.GetIdentity(eName, cName) + " desc");
}
strSql.Append(")AS Row, T.* from " + eName + " T ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" WHERE " + strWhere);
}
strSql.Append(" ) TT");
strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", (pageIndex - ) * pageSize + , pageIndex * pageSize);
return strSql.ToString();
}
#endregion #region 2014-02-28 执行结果
/// <summary>
/// 向数据库中添加数据
/// 2014-02-26
/// </summary>
/// <param name="sql">执行的Sql语句返回影响数据库中的行数</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql)
{ int resour = ; try
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
resour = cmd.ExecuteNonQuery();
con.Close();
GC.Collect();
}
catch (Exception)
{
con.Close(); }
return resour; } /// <summary>
/// 添加 修改，数据库返回情况
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ExecSclaer(string sql)
{
int resour = ;
try
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
object obj = cmd.ExecuteScalar();
resour = Convert.ToInt32(obj);
con.Close();
GC.Collect();
}
catch (Exception)
{
con.Close();
GC.Collect();
}
return resour;
} /// <summary>
/// 获取一条数据
/// </summary>
/// <param name="sql"></param>
/// <returns>返回单挑数据'¦'隔开</returns>
public static string SelectToSqlModel(string sql)
{
string vals = "";
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
SqlDataReader read = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (read.HasRows)
{ while (read.Read())
{
for (int i = ; i < read.FieldCount; i++)
{
vals += read[i].ToString() + "¦";
}
}
}
read.Close();
con.Close();
GC.Collect();
vals = vals.Substring(, vals.Length - );
return vals;
}
#endregion #region Excel 模板导出
public static void exportTemper(string eName, string cName, string saveName, string sql)
{
//自行拓展
}
#endregion }
}
在有的电脑生进行数据导入 出现未在本地计算机上注册“microsoft.ACE.oledb.12.0”提供程序 错误
处理方法：解决办法：
去http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe下载。
然后安装就行了。
安装此插件以后将会支持到office2010 的的文件
关键页面代码：
创建数据表页面
前台界面;
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ExcelToSqlserver.Default" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>XML操作处理</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h3>创建表格：</h3>
<p>
中文表名：<asp:TextBox ID="txtCname" runat="server"></asp:TextBox>
<br />
<br />
数据表名：<input type="text" id="txtEName" runat="server" onkeyup="this.value=this.value.replace(/[^a-zA_Z_]/,'');" />提示：接受输入字母和下划线
<br />
<br />
数据主键：<input type="text" runat="server" id="txtIditent" size="20" onkeyup="this.value=this.value.replace(/[^a-zA-Z_]/,'');" /> 提示：接受输入字母和下划线
<br />
<br />
<asp:Button ID="btnAdd" runat="server" OnClick="Button1_Click" Enabled="false" Text="添加" Style="height: 21px" />
<asp:Button ID="btnChange" runat="server" OnClick="Button2_Click" Enabled="false" Text="修改" />
<asp:Button ID="btnDel" runat="server" OnClick="Button3_Click" Enabled="false" Text="删除" />
<asp:Button ID="btnCheck" runat="server" OnClick="btnCheck_Click" Text="检查" />
<br />
<asp:Label ID="Label1" runat="server"></asp:Label>
</p>
</div> </form>
</body>
</html>
后台代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls; using CommonLib; namespace ExcelToSqlserver
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{ } /// <summary>
/// 添加节点
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
XMLBase xmlBase = new XMLBase();
xmlBase.LoadDome();
bool res = xmlBase.CheckNode(txtCname.Text, txtEName.Value);
if (res)
{
xmlBase.InsertNode(txtCname.Text, txtEName.Value, txtIditent.Value);
//跳转向数据表中添加数据列
Response.Redirect("IniTableColums.aspx?cName=" + txtCname.Text + "&eName=" + txtEName.Value);
}
else
{
Label1.Text = "该表存在请重新命名";
}
} protected void Button2_Click(object sender, EventArgs e)
{
XMLBase xmlBase = new XMLBase();
xmlBase.LoadDome();
bool res = xmlBase.UpdateNode(txtCname.Text, txtEName.Value);
Label1.Text = res.ToString();
} protected void Button3_Click(object sender, EventArgs e)
{
XMLBase xb = new XMLBase();
xb.LoadDome();
xb.DeleteNode(txtCname.Text, txtEName.Value, true);
} protected void btnCheck_Click(object sender, EventArgs e)
{
//获取数据
string cName = txtCname.Text.Trim();
string eName = txtEName.Value.Trim();
string identrty = txtIditent.Value.Trim();
if (cName=="")
{
Label1.Text = "提示：表名不能为空！";
return;
}
if (eName=="")
{
Label1.Text = "提示：数据表名不能为空！";
return;
}
if (identrty=="")
{
Label1.Text = "提示：主键不能为空！";
return;
} XMLBase xmlBase = new XMLBase();
xmlBase.LoadDome();
bool res=xmlBase.CheckNode(txtCname.Text, txtEName.Value);
if (res)
{
Label1.Text = "提示：可以添加表";
btnAdd.Enabled = true;
}
else
{ btnChange.Enabled = true;
btnDel.Enabled = true;
Label1.Text = "提示：用户表存在,请修改表名";
} }
}
}
添加表后将或跳转到动态添加数据列页面
前台界面
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="IniTableColums.aspx.cs" Inherits="ExcelToSqlserver.IniTableColums" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>数据库表头设计</title>
<script type="text/javascript" src="Scripts/jquery-1.8.2.min.js"></script>
</head>
<body>
<form id="form1" runat="server">
当前位置：添加表头<br />
<br />
数据表名称：<asp:TextBox ID="txtCName" runat="server" ReadOnly="True"></asp:TextBox>
<input type="hidden" runat="server" id="hiddTableName" />
<br />
<br />
<h3>提示：英文名是需要在数据库中保存的由字母和下划线组成</h3>
<div class="divCoulm">
<hr />
英文名：<input class="txtEName" type="text" onkeyup="this.value=this.value.replace(/[^a-zA-Z]/,'');" />
对应数据类型：<select class="rdType">
<option value="nvarchar(MAX)" selected="selected">文字类型</option>
<option value="float">小数类型</option>
<option value="int">整数类型</option>
</select>
<br />
<br />
备注名：<input type="text" class="txtBeizhu" />
数据字节长度：<select class="txtLenght">
<option value="varchar(10)">10</option>
<option value="varchar(20)">20</option>
<option value="nvarchar(MAX)">最大</option>
</select>
<br />
<br />
<input type="button" onclick="del(this)" class="delColum" value="删除" />
<br />
<hr />
</div>
<input type="button" value="继续添加数据列" id="btnGo" />
<input type="button" value="确定" id="btnOK" />
</form>
</body>
</html>
<script type="text/javascript">
$(function () { //声明列名录入界面
var addDocument = " <div class='divCoulm'> <hr /> 英文名：<input class='txtEName' onkeyup=\"this.value=this.value.replace(/[^a-zA-Z]/,'');\" type='text' /> 英文名有字母组成，没有下划线<br/> 对应数据类型：<select class='rdType'>"; addDocument += " <option value='nvarchar(MAX)' selected='selected'>文字类型</option> <option value='float'>小数类型</option>"; addDocument += " <option value='varchar(9)'>整数类型</option></select><br /> <br /> 备注名：<input type='text' class='txtBeizhu' />"; addDocument += " 数据字节长度：<select class='txtLenght'> <option value='varchar(10)'>10</option> <option value='varchar(20)'>20</option><option value='nvarchar(MAX)'>最大</option></select> <br /> <br /> <input type=\"button\" class=\"delColum\" onclick=' del(this)' value=\"删除\" /> <hr /> </div>"; $("#btnGo").click(function () {
var isGo;
var div = $(this).prev("div");
var eName = div.children(".txtEName").val();//英文名
var eType = div .children(".rdType").val();//数据类型
var cName = div .children(".txtBeizhu").val();//中文名称
var cLength = div.children(".txtLenght").val();//字段长度
isGo = CheckVoide(eName, eType, cName, cLength); if (!isGo) { alert("数据有误"); } else {
$(".divCoulm").last().append(addDocument);
}
}); $("#btnOK").click(function () {
//获取数据
var mess = "";
$(".divCoulm").each(function () {
var eName = $(this).children(".txtEName").val();//英文名
var eType = $(this).children(".rdType").val();//数据类型
var cName = $(this).children(".txtBeizhu").val();//中文名称
var cLength = $(this).children(".txtLenght").val();//字段长度
var isGo = CheckVoide(eName, eType, cName, cLength);
if (isGo) {
mess += eName + "_" + eType + "_" + cName + "_" + cLength + "|";
}
});
debugger;
var go = confirm("确认添加添加后不得修改");
if (go) {
var tableName = $("#hiddTableName").val();//英文名称
var tableCName = $("#txtCName").val();//中文名称;
$.post("Ajax/CreatInfoToXML.ashx", { tableName: tableName, tableCName: tableCName, mess: mess }, function () {
window.location.href = "EnterExcel.aspx";
});
}
}); $(".delColum").click(function () {
debugger;
$(this).parent("div").html("");
}); });
//检查数据是否为空
function CheckVoide(eName, eType, cName, cLength) {
if (eName == "" || eType == "" || cName == "" || cLength == "") {
return false;
} else if (eName != "" || eType != "" || cName != "" || cLength != "") {
return true;
}
}
function del(id)
{
$(id).parent("div").remove();
}
</script>
后台
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
txtCName.Text = Request.QueryString["cName"].ToString();
hiddTableName.Value = Request.QueryString["eName"].ToString();
}
}
在进行添加数据列是通过异步进行添加的
Ajax/CreatInfoToXML.ashx
/**
*张国强 2014-02-20
*将excel中的数据表结构序列化到XML中
*同时在数据库中创建数据表结构方便数据导入到数据中。
*InsertXML(HttpContext context) 将界面录入的数据列名导入到XML数据结构中
*/ using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
//
using System.Configuration;
using CommonLib;
using System.Data;
using System.Data.SqlClient; namespace ExcelToSqlserver.Ajax
{
/// <summary>
/// CreatInfoToXML 的摘要说明
/// </summary>
public class CreatInfoToXML : IHttpHandler
{
XMLBase xmlBase = new XMLBase(); public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
InsertXML(context); } //序列化数据到XML文件中
private void InsertXML(HttpContext context)
{
//获取值
string tableEName = context.Request.Form["tableName"];
string tableCName = context.Request.Form["tableCName"];
string mess = context.Request.Form["mess"];
//拆分列名
if (mess == "") return;
mess = mess.Substring(0, mess.Length - 1);
//第一次拆分数据
string[] split1 = mess.Split('|');
xmlBase.LoadDome();
string identity = xmlBase.GetIdentity(tableEName, tableCName);
string sql = "create table " + tableEName + "(" + identity + " int identity(1,1) primary key ,";
for (int i = 0; i < split1.Length; i++)
{
string mess2 = split1[i];
string[] split2 = mess2.Split('_');
//获取值
string eName = split2[0].ToString();
string eType = split2[1].ToString();
string cName = split2[2].ToString();
string eLenght = split2[3].ToString();
//生成sql语句
sql += eName + " " + eType + " ,";
try
{
xmlBase.LoadDome();
xmlBase.InsertNode(tableCName, tableEName, eName, eType, cName, eLenght);
//生成数据表 }
catch (Exception ex)
{ throw ex;
}
}
CreatTable(sql);
} private void CreatTable(string tableSql)
{
string constr = ConfigurationManager.ConnectionStrings["SqlServer"].ConnectionString;
SqlConnection con = new SqlConnection(constr);
if (tableSql != "")
tableSql = tableSql.Substring(0,tableSql.Length-1);
string sql = tableSql + ")";
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
} public bool IsReusable
{
get
{
return false;
}
}
}
}
创建表格完成后就可以进行数据导入了
数据导入界面
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="EnterExcel.aspx.cs" Inherits="ExcelToSqlserver.EnterExcel" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title>EXCEL数据导入</title>
<style type="text/css">
li {
display: inline;
list-style: none;
display: inline;
list-style: none;
padding-left: 15px;
}
</style>
<script type="text/javascript" src="Scripts/jquery-1.8.2.min.js"></script> </head>
<body>
<form id="form1" runat="server">
<div>
<div>
<ul>
<li><a id="a_insert" runat="server">手动录入数据</a></li>
<li>批量导入数据</li> </ul>
</div>
<br />
导入数据：<asp:DropDownList ID="dropTableInfo" runat="server" Height="16px" Width="140px" AutoPostBack="True" OnSelectedIndexChanged="dropTableInfo_SelectedIndexChanged">
</asp:DropDownList>
<input type="hidden" runat="server" id="hiddTableName" />
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="删除节点测试" />
<br />
<br />
选择EXCEL数据表：<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Label ID="lblMess" runat="server" Text=""></asp:Label>
<br />
<br />
选择有效数据行：第<asp:DropDownList ID="dropch" runat="server" Height="16px" Width="61px">
<asp:ListItem>1</asp:ListItem>
<asp:ListItem Selected="True">2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem Value="5"></asp:ListItem>
<asp:ListItem>6</asp:ListItem>
</asp:DropDownList>行为有效数据
<br />
<br />
<asp:Button ID="btnCheck" runat="server" Text="检查" OnClick="btnCheck_Click" />
<asp:Button ID="btnOK" runat="server" Text="确认导入" OnClick="btnOK_Click" Enabled="False" />
<asp:Button ID="btnCon" runat="server" Enabled="false" Text="取消" OnClick="btnCon_Click" />
</div>
<div id="divShow" runat="server" visible="false">
<h4>表头格式：参照以下表头来制作表格</h4>
<strong>你也可以</strong>
<br />
<table cellpadding="1px" border="1px" >
<tbody id="head" runat="server">
<tr>
<th style="border:1px solid;" >asdfafd</th>
<th style="border:1px solid;" >asdfafd</th>
</tr>
</tbody>
</table>
</div>
</form>
</body>
</html>
后台代码
/*
*张国强 2014-02-20
*excel 表头检查
*数据表读取数据
*SQlserver数据表表头检查
*主要操：EXCEL 数据表读取
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
///
using CommonLib;
using System.Configuration;
using System.Data; namespace ExcelToSqlserver
{
public partial class EnterExcel : System.Web.UI.Page
{ XMLBase xmlBase = new XMLBase(); protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ViewState["excelPath"] = null;
ViewState["start"] = 0;
ViewState["end"] = 0;
xmlBase.LoadDome();
List<XMLTableInfo> datesource = xmlBase.GetTableList();
if (datesource.Count > 0)
{
dropTableInfo.DataSource = datesource;
dropTableInfo.DataTextField = "CName"; ;
dropTableInfo.DataValueField = "EName";
dropTableInfo.DataBind();
dropTableInfo.SelectedIndex = 0; }
dropTableInfo.Items.Add(new ListItem() { Text="没有数据表新建", Value="-1" });
string eName = dropTableInfo.SelectedItem.Value;
string cName = dropTableInfo.SelectedItem.Text;
a_insert.Attributes.Add("href", "InsertInfo.aspx?cmd=insert&eName=" + eName + "&cName=" + cName);
}
} /// <summary>
/// 检查数据列表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnCheck_Click(object sender, EventArgs e)
{
//上传到服务器上
string upFulPath = ConfigurationManager.ConnectionStrings["ExcelUpLoadPath"].ConnectionString;
if (!FileUpload1.HasFile)
{
Response.Write("<script>alert('请选择上传文件')</script>");
return;
}
string fileName = FileUpload1.FileName;
string filetype = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
if (filetype == ".xlsx" || filetype == ".xls")
{
upFulPath = Server.MapPath(upFulPath);
string saveName = upFulPath + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + fileName;
FileUpload1.SaveAs(saveName);
lblMess.Text = "文件已上传";
lblMess.Attributes.Add("style", "color:green;");
ViewState["excelPath"] = saveName;
btnCon.Enabled=true;
xmlBase.LoadDome();
string cName = dropTableInfo.SelectedItem.Text;
string eName = dropTableInfo.SelectedValue;
///检查表头
string cheInfo = new ExcelHelper().CheckExcelTitle(eName, cName, saveName, "Sheet1");
if (cheInfo == "")
{
Response.Write("<script>alert('验证通过')</script>");
btnOK.Enabled = true; }
else
{
//Excel数据表表头格形式提示
xmlBase.LoadDome();
string tab = xmlBase.Check(eName, cName);
string[] tblist = tab.Split('¦')[1].Split('[');
string st = "<tr>";
for (int i = 0; i < tblist.Length; i++)
{
st += "<td>"+tblist[i]+"</td>";
}
st+="</tr>";
this.head.InnerHtml = st;
divShow.Visible = true; Response.Write("<script>alert('" + cheInfo + "')</script>");
}
}
else
{
lblMess.Text = "不是有效的excel文件！请重选";
return; }
FileUpload1.Enabled = false;
btnCheck.Enabled = false; } /// <summary>
/// 导入数据
/// 2014-02-16
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnOK_Click(object sender, EventArgs e)
{ string path = ViewState["excelPath"].ToString();
int rowIndex = Convert.ToInt32(dropch.SelectedValue);
List<string> list = ExcelHelper.GetExcelDate(rowIndex - 1, 0, path, "Sheet1");
string cName = dropTableInfo.SelectedItem.Text;
string eName = dropTableInfo.SelectedValue;
string sql = ExcelHelper.InserToSqlQuerystring(eName, cName);
int inseCount = 0;
for (int i = 0; i < list.Count; i++)
{
string vals = "";
for (int j = 0; j < list[i].Split(',').Length; j++)
{
vals += "'" + list[i].Split(',')[j] + "' ,";
}
vals = vals.Substring(0, vals.Length - 1);
string inser = sql + " (" + vals + " );select @@IDENTITY"; if (i==0)
{
ViewState["start"] = ExcelHelper.ExecSclaer(inser);
}
else if(i==list.Count-1)
{
ViewState["end"] = ExcelHelper.ExecSclaer(inser);
}
else
{
ExcelHelper.ExecSclaer(inser);
}
}
string mess = "Excel有效数据：" + list.Count + " 条 已经成功导入数据：" + inseCount + "条";
Response.Write("<script>alert('" + mess + "')</script>");
Response.Redirect("ExcelListShow.aspx?tbname=" + dropTableInfo.SelectedValue + "&start="+ViewState["start"].ToString()+"&end="+ViewState["end"].ToString()+"&cName="+dropTableInfo.SelectedItem.Text+"&eName="+dropTableInfo.SelectedValue);
} //取消删除数据表
protected void btnCon_Click(object sender, EventArgs e)
{
string path = ViewState["excelPath"].ToString();
bool re = System.IO.File.Exists(path);
if (re)
{
System.IO.File.Delete(path);
lblMess.Text = "上传文件已删除";
lblMess.Attributes.Add("style","color:green;");
btnOK.Enabled = false;
ViewState["excelPath"] = "";
divShow.Visible = false;
btnCon.Enabled = false;
btnCheck.Enabled= true;
FileUpload1.Enabled = true;
}
} protected void dropTableInfo_SelectedIndexChanged(object sender, EventArgs e)
{
string eName = dropTableInfo.SelectedItem.Value;
string cName = dropTableInfo.SelectedItem.Text;
lblMess.Text = "";
if (eName=="-1")
{
Response.Redirect("Default.aspx");
}
else
{
a_insert.Attributes.Add("href", "InsertInfo.aspx?cmd=insert&eName=" + eName + "&cName=" + cName);
} } protected void btnOutTemp_Click(object sender, EventArgs e)
{
string eName = dropTableInfo.SelectedItem.Value;
string cName = dropTableInfo.SelectedItem.Text; } protected void Button1_Click(object sender, EventArgs e)
{
XMLBase xb = new XMLBase();
xb.LoadDome();
xb.DeleteNode("中文名", "china", false);
}
}
}
数据到导入完成后将会将最新的导入数据展示到另一个页面上
前台
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ExcelListShow.aspx.cs" Inherits="ExcelToSqlserver.ExcelListShow" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title>最新导入的数据列表查看</title> <script type="text/javascript" src="Scripts/jquery-1.8.2.min.js"></script> </head>
<body>
<form id="form1" runat="server">
<div>
<input type="hidden" id="hiddcName" runat="server" />
<input type="hidden" id="hiddeName" runat="server" />
<input type
="hidden" id="hiddcmd" runat="server" />
<a id="a_Add" runat="server">添加数据</a>
<a id="a_Del" href="#" runat="server">删除数据</a> </div>
<div style="text-align:center;">
<asp:GridView ID="NewGrid" runat="server">
<Columns> </Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
<script type="text/javascript">
$(function () {
//全选择 全部不选择
$(".heck_box").click(function () {
var t = $(this).attr("checked");
if (t) {
$(".CheckBox").attr("checked", t);
} else {
$(".CheckBox").attr("checked", false);
}
});
//删除数据隐藏行
$(".A_DelBind").click(function () {
$(this).parent("td").parent("tr").hide();
});
//删除事件
$("#a_Del").click(function () { //获取表明
var eName = $("#hiddeName").val();
var cName = $("#hiddcName").val();
var id = "";
//获取主键ID
$(".CheckBox").each(function () {
var t = $(this).attr("checked");
if (t) {
id += $(this).val() + ",";
}
});
if (id=="") {
alert("没有选中删除数据！");
return false;
}
if (id.length>2) {
id = id.substring(0, id.length - 1);
}
var con = confirm("即将删除数据，删除后不能回复！");
if (con) {
$.get("Ajax/AjaxDateToSql.ashx", { cmd: "delete", id: id, cName: cName, eName: eName }, function () { });
} }); });
</script>
后台主要是数据绑定
后台
/*
* 张国强
* 2014-02-26
* 动态生成GridView数据列表
* 创建超链接
*
* **/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
//
using CommonLib; namespace ExcelToSqlserver
{
public partial class ExcelListShow : System.Web.UI.Page
{
XMLBase xBase = new XMLBase(); protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string tbname = Request.QueryString["tbname"].ToString();
ViewState["start"] = Request.QueryString["start"].ToString();
ViewState["end"] = Request.QueryString["end"].ToString();
string cName = Request.QueryString["cName"].ToString();
ViewState["cName"] = cName;
string eName = Request.QueryString["eName"].ToString(); //////测试
//string tbname = "RZZGTZ"; //Request.QueryString["tbname"].ToString();
//string top = "20";// Request.QueryString["top"].ToString();
//string cName = "任职资格台账";// Request.QueryString["cName"].ToString();
//ViewState["cName"] = cName;
//string eName = "RZZGTZ";// Request.QueryString["eName"].ToString(); hiddcName.Value = cName;
hiddeName.Value = eName;
ViewState["eName"] = eName;
xBase.LoadDome(); ViewState["identity"] = xBase.GetIdentity(eName, cName);
a_Add.Attributes.Add("href", "InsertInfo.aspx?cName=" + cName + "&eName=" + eName + "&cmd=insert");
xBase.LoadDome();
//加载数据数据库中列名和备注名
string tbInfo = xBase.Check(eName, cName);
ViewState["tableInfo"] = null;
if (tbInfo != null)
{
ViewState["tableInfo"] = tbInfo;
}
//数据绑定
string sql = "select * from " + eName + " where " + xBase.GetIdentity(eName,cName)+" between "+ ViewState["start"].ToString()+" and "+ ViewState["end"].ToString();
new GridViewTemplate().LoadInfo(this.NewGrid, eName, cName, sql);
}
} }
}
前台主操作主要用到的是异步操作
/*2014-02-27
* 张国强
* Email:imei8service@gmail.com
* 前台页面提交数据处理
* 包括添加数据
* 修改数据
* 删除数据
* 三种操作
*
* **/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
//
using CommonLib; namespace ExcelToSqlserver.Ajax
{
/// <summary>
/// AjaxDateToSql 的摘要说明
/// </summary>
public class AjaxDateToSql : IHttpHandler
{
XMLBase xBase = new XMLBase();
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
string cmd = context.Request.QueryString["cmd"].ToString();
switch (cmd)
{
case "insert"://添加数据
Insert(context);
break;
case "delete"://删除数据
Delete(context);
break;
case "editor":
Editor(context);
break;
default:
break;
}
} /// <summary>
/// 修改数据
/// </summary>
/// <param name="context"></param>
private void Editor(HttpContext context)
{
try
{
string cName = context.Request.QueryString["cName"].ToString();
string eName = context.Request.QueryString["eName"].ToString();
string id = context.Request.QueryString["id"].ToString();
string values = context.Request.QueryString["values"].ToString();
string sql = ExcelHelper.UpdateQueryString(eName, cName, Convert.ToInt32(id));
string[] val = values.Split('¦');
string sql2 = string.Format(sql, val);
int res = ExcelHelper.ExecuteNonQuery(sql2);
if (res > )
{
context.Response.Write("修改成功");
}
else
{
context.Response.Write("NO");
}
}
catch (Exception ex)
{
context.Response.Write("NO"); } } /// <summary>
/// 删除数据
/// </summary>
/// <param name="context"></param>
private void Delete(HttpContext context)
{
//cName=""&eName=&id='
string cName = context.Request.QueryString["cName"].ToString();
string eName = context.Request.QueryString["eName"].ToString();
string id = context.Request.QueryString["id"].ToString();
//获取删除语句
string s = ExcelHelper.DeleteQuerystring(eName, cName, id);
int resout = ExcelHelper.ExecuteNonQuery(s);
if (resout > )
{
context.Response.Redirect("../ShowList.aspx?cName=" + cName + "&eName=" + eName);
} }
/// <summary>
/// 添加数据
/// </summary>
/// <param name="context"></param>
private void Insert(HttpContext context)
{
string sql = context.Request.QueryString["values"].ToString();
string eName = context.Request.QueryString["eName"].ToString();
string cName = context.Request.QueryString["cName"].ToString();
if (sql != "")
{
xBase.LoadDome();
string s = ExcelHelper.InserToSqlQuerystring(eName, cName);
string[] va = sql.Split('¦');
string v = "";
for (int i = ; i < va.Length; i++)
{
v += va[i] + ",";
}
v = v.Substring(, v.Length - );
s += " (" + v + " );select @@IDENTITY";
int t = ExcelHelper.ExecSclaer(s);
if (t > )
{
context.Response.Write("添加成功");
}
else
{
context.Response.Write("NO");
}
}
else
{
context.Response.Write("NO");
} } public bool IsReusable
{
get
{
return false;
}
}
}
}
后台运用的的是动态绑定数据
将数据库中的列名通过后台绑定到Griview中并给出并且绑定操作列
/*
* 2014-02-27
* GridView数据列绑定帮助文档
* 用于动态添查询数据绑定到数据表中
* 提供各种类型的绑定
*
* **/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//
using System.Web.UI;
using System.Web.UI.WebControls; namespace CommonLib
{
/// <summary>
/// GridView数据列绑定中兴
/// </summary>
public class GridViewTemplate : ITemplate
{
public delegate void EventHandler(object sender, EventArgs e);
public event EventHandler eh; private DataControlRowType templateType;
private string columnName;
private string controlID;
private XMLTableInfo xmTable = null;
private BindType bind = BindType.label;
public GridViewTemplate()
{ }
/// <summary>
/// 构造函数
/// </summary>
/// <param name="type">绑定列类型</param>
/// <param name="colname">绑定列名称或者需要绑定的数据库字段</param>
/// <param name="colname">绑定字段类型控件</param>
public GridViewTemplate(DataControlRowType type, string colname, BindType bin, XMLTableInfo tab)
{
templateType = type;
columnName = colname;
bind = bin;
this.xmTable = tab;
}
/// <summary>
/// 绑定事件
/// </summary>
/// <param name="type"></param>
/// <param name="controlID"></param>
/// <param name="colname"></param>
public GridViewTemplate(DataControlRowType type, string controlID, string colname)
{
templateType = type;
this.controlID = controlID;
columnName = colname;
}
public void InstantiateIn(System.Web.UI.Control container)
{
switch (templateType)
{
case DataControlRowType.Header://标题绑定
if (bind == BindType.label)
{
Literal lc = new Literal();
lc.Text = columnName;
container.Controls.Add(lc);
}
if (bind == BindType.checkbok)
{
Literal lc = new Literal();
lc.Text = "<input type='checkbox' class='heck_box' />全选";
container.Controls.Add(lc);
}
if (bind == BindType.editor)
{
Literal lc = new Literal();
lc.Text = "编辑";
container.Controls.Add(lc);
}
if (bind == BindType.delete)
{
Literal lc = new Literal();
lc.Text = "";// "删除";
container.Controls.Add(lc);
} break;
case DataControlRowType.DataRow://普通列绑定
if (bind == BindType.label)
{
Label tb = new Label();
tb.DataBinding += tb_DataBinding;
container.Controls.Add(tb);
}
if (bind == BindType.checkbok)
{
Literal lic = new Literal();
lic.DataBinding += lic_DataBinding;
container.Controls.Add(lic);
}
if (bind == BindType.editor)
{
Literal lec = new Literal();
lec.DataBinding += lec_DataBinding;
container.Controls.Add(lec);
}
if (bind == BindType.editor)
{
Literal del = new Literal();
del.DataBinding += del_DataBinding;
container.Controls.Add(del);
} break;
default:
break;
}
} void del_DataBinding(object sender, EventArgs e)
{ Literal tb = (Literal)sender; GridViewRow row = (GridViewRow)tb.NamingContainer;
tb.ID = columnName;
tb.Text = " <a class='A_DelBind' href='Ajax/AjaxDateToSql.ashx?cmd=delete&cName=" + xmTable.CName + "&eName=" + xmTable.EName + "&id=" + DataBinder.Eval(row.DataItem, columnName).ToString() + "'>删除</>";
} void lec_DataBinding(object sender, EventArgs e)
{
Literal tb = (Literal)sender; GridViewRow row = (GridViewRow)tb.NamingContainer;
tb.ID = columnName;
tb.Text = "<a href='InsertInfo.aspx?cmd=editor&cName=" + xmTable.CName + "&eName=" + xmTable.EName + "&id=" + DataBinder.Eval(row.DataItem, columnName).ToString() + "'>编辑</>";
} void lic_DataBinding(object sender, EventArgs e)
{ Literal btn = (Literal)sender; GridViewRow row = (GridViewRow)btn.NamingContainer;
btn.ID = columnName;
btn.Text = "<input type='checkbox' class='CheckBox' value='" + DataBinder.Eval(row.DataItem, columnName).ToString() + "'/>"; }
/// <summary>
/// 2014-02-27
/// 张国强
/// 绑定字段数据列事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void tb_DataBinding(object sender, EventArgs e)
{
Label tb = (Label)sender; GridViewRow row = (GridViewRow)tb.NamingContainer;
tb.ID = columnName;
tb.Text = DataBinder.Eval(row.DataItem, columnName).ToString();
} #region 提取公共部分 /// <summary>
/// 绑定数据到GridView控件
/// </summary>
/// <param name="NewGrid">GridViews控件</param>
/// <param name="eName">英文名称</param>
/// <param name="cName">中文名称</param>
/// <param name="sqlOrTop">需要查询的数据字符串</param>
public void LoadInfo(GridView NewGrid, string eName, string cName,string sqlOrTop)
{
NewGrid.Columns.Clear();
XMLTableInfo tb = new XMLTableInfo();
tb.CName = cName;
tb.EName = eName;
XMLBase xBase = new XMLBase();
xBase.LoadDome();
string tableInfo = xBase.Check(eName, cName);
//1，拆分数据
string[] Ename = tableInfo.Split('¦')[].Split(',');
string[] Cname = tableInfo.Split('¦')[].Split('[');
xBase.LoadDome();
string id = xBase.GetIdentity(eName, cName);
//循环数据
for (int i = ; i < Ename.Length; i++)
{
//BoundColumn cu = new BoundColumn();
//cu.DataField = Ename[i];
//cu.HeaderText = Cname[i];
TemplateField temp = new TemplateField();
temp.ShowHeader = true;
if (i == )
{
temp.HeaderTemplate = new GridViewTemplate(DataControlRowType.Header, Cname[i], BindType.checkbok, tb);
temp.ItemTemplate = new GridViewTemplate(DataControlRowType.DataRow, id, BindType.checkbok, tb);
}
else
{
temp.HeaderTemplate = new GridViewTemplate(DataControlRowType.Header, Cname[i], BindType.label, tb);
temp.ItemTemplate = new GridViewTemplate(DataControlRowType.DataRow, Ename[i], BindType.label, tb);
} NewGrid.Columns.Add(temp);
}
//添加编辑列
TemplateField tempEditor = new TemplateField();
tempEditor.HeaderTemplate = new GridViewTemplate(DataControlRowType.Header, "操作", BindType.editor, tb);
tempEditor.ItemTemplate = new GridViewTemplate(DataControlRowType.DataRow, id, BindType.editor, tb);
NewGrid.Columns.Add(tempEditor); TemplateField tempDelete = new TemplateField();
tempDelete.HeaderTemplate = new GridViewTemplate(DataControlRowType.Header, "删除", BindType.delete, tb);
tempDelete.ItemTemplate = new GridViewTemplate(DataControlRowType.DataRow, id, BindType.delete, tb);
NewGrid.Columns.Add(tempDelete); string sql = "";
if (sqlOrTop.Length<)
{
sql = "select top " + sqlOrTop + " * from " + eName + " order by " + id + " DESC";
NewGrid.DataSource = ExcelHelper.GetDataSet(sql);
}
else
{
NewGrid.DataSource = ExcelHelper.GetDataSet(sqlOrTop);
} NewGrid.AutoGenerateColumns = false;
NewGrid.DataBind(); }
#endregion } public enum BindType
{
label, checkbok, editor, delete
} }
以上代码提供了不同类型的数据绑定 表头列数据展示列操作列等都有提供。BindType 提供的枚举对应的绑定列类型
public enum BindType
{
label, checkbok, editor, delete
}
在以上用的的一些约定
特殊类名
|
类名 |
作用 |
出现位置 |
|
heck_box |
控制 全选 全部选 |
会出现在dateGridView表头位置控制整张表的复选框。 |
|
CheckBox |
绑定数据主键值 |
出现在GridView数据列位置在表头位置，用来确定数据是否被选中。 |
以上是两个比较特殊的标签类名；
特的页面和特殊的参数：
|
页面路径 |
参数 |
作用 |
|
InsertInfo.aspx |
cmd:操作指令 [insert,delede,editor] 分别对应增加，删除 ，编辑功能 eName:数据表对应的英文名 cName:对应的中文名 id:对应的数据主键编号 |
添加页面和编辑页面为同一个页面，提供添添加数据录入界面。 提供加载数据编辑界面。 |
|
Ajax/AjaxDateToSql.ashx |
需要传递参数同上 |
提供异步处理程序。 提供手动增删改方法。 |
|
参考： |
href='Ajax/AjaxDateToSql.ashx?cmd=delete&cName=CName&eName=EName&id=id' InsertInfo.aspx?cmd=editor&cName=CName &eName=EName&id=id' |
|
|
小节 |
cName 和eName 两个参数贯穿整个程序 为了保证中文表民和数据库中的表名一致采用双重验证 |
|
到了这里已经差不多核心代码都展示完毕
最后一步做个小结
展示数据
前台
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ShowList.aspx.cs" Inherits="ExcelToSqlserver.ShowList" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>数据控制台账</title>
<script type="text/javascript" src="Scripts/jquery-1.8.2.min.js"></script>
</head>
<body>
<form id="form1" runat="server">
<div>
<p>当前界面：<label id="lblTitle" runat="server">XXX台账</label></p>
关键字查询：<input type="text" id="txtKey" runat="server" /><asp:Button ID="btnSech" runat="server" Text="查询" OnClick="btnSech_Click" />
<br />
<br />
<div>
<input type="hidden" id="hiddcName" runat="server" />
<input type="hidden" id="hiddeName" runat="server" />
<input type
="hidden" id="hiddcmd" runat="server" />
<a id="a_Add" runat="server">添加数据</a>
<a id="a_Del" href="#" runat="server">删除数据</a>
<br /> </div>
</div>
<div>
<asp:GridView ID="gridView" runat="server">
<Columns></Columns>
</asp:GridView>
</div>
<div style="width: 600px; margin-top: 10px; text-align: center;">
<asp:Button ID="shouye" runat="server" Text="首页" OnClick="shouye_Click" />
<asp:Button ID="btnUpPage" runat="server" Text="上一页" OnClick="btnUpPage_Click" />
<asp:Button ID="btnNextPage" runat="server" Text="下一页" OnClick="btnNextPage_Click" />
<asp:Button ID="weiye" runat="server" Text="尾页" OnClick="weiye_Click" />
共<asp:Label ID="lbl_courrentPage" runat="server" Text="1"></asp:Label>
页 第
<input id="currentpage" type="text" runat="server" style="width: 32px; height: 15px;" />页
<asp:Button ID="tiaozhuan" runat="server" Text="跳转" OnClick="tiaozhuan_Click" />
每页<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" Height="22px"
Width="39px" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
<asp:ListItem Value="10">10</asp:ListItem>
<asp:ListItem>20</asp:ListItem>
<asp:ListItem>30</asp:ListItem>
</asp:DropDownList>
个
</div>
</form>
</body>
</html>
<script type="text/javascript">
$(function () {
//全选择 全部不选择
$(".heck_box").click(function () {
var t = $(this).attr("checked");
if (t) {
$(".CheckBox").attr("checked", t);
} else {
$(".CheckBox").attr("checked", false);
}
});
//删除数据隐藏行
$(".A_DelBind").click(function () {
$(this).parent("td").parent("tr").hide();
});
//删除事件
$("#a_Del").click(function () { //获取表明
var eName = $("#hiddeName").val();
var cName = $("#hiddcName").val();
var id = "";
//获取主键ID
$(".CheckBox").each(function () {
var t = $(this).attr("checked");
if (t) {
id += $(this).val() + ",";
}
});
if (id == "") {
alert("没有选中删除数据！");
return false;
}
if (id.length > 2) {
id = id.substring(0, id.length - 1);
}
var con = confirm("即将删除数据，删除后不能恢复！");
if (con) {
$.get("Ajax/AjaxDateToSql.ashx", { cmd: "delete", id: id, cName: cName, eName: eName }, function () {
history.go(0); });
} }); });
</script>
后台
/*2014-3-3
* email:imei8service@gmail.com
* 动态显示数据
* 生成查询方法
* 自动分页
*
* **/ using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
//
using CommonLib; namespace ExcelToSqlserver
{
public partial class ShowList : System.Web.UI.Page
{
XMLBase xb = new XMLBase();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
shouye.Enabled = false;
btnUpPage.Enabled = false;
btnNextPage.Enabled = false;
weiye.Enabled = false;
ViewState["pageIndex"] = ;
ViewState["pageSize"] = ;
ViewState["pageCount"] = ;
ViewState["where"] = "";
ViewState["cName"] =Request.QueryString["cName"].ToString();
ViewState["eName"] =Request.QueryString["eName"].ToString();
a_Add.Attributes.Add("href", "InsertInfo.aspx?cName=" + ViewState["cName"].ToString() + "&eName=" + ViewState["eName"].ToString() + "&cmd=insert");
hiddcmd.Value = ViewState["cName"].ToString();
hiddeName.Value = ViewState["eName"].ToString();
lblTitle.InnerHtml = ViewState["cName"].ToString();
selctInfo();
} }
/// <summary>
///查询方法
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSech_Click(object sender, EventArgs e)
{
xb.LoadDome(); string[] keowo = xb.Check(ViewState["eName"].ToString(), ViewState["cName"].ToString()).Split('¦')[].Split(',');
string sql ="";
string key = txtKey.Value;
if (key != "")
{
for (int i = ; i < keowo.Length; i++)
{
if (i==keowo.Length-)
{
sql += " " + keowo[i] + " like '%" + key + "%'";
}
else
{
sql += " " + keowo[i] + " like '%" + key + "%'" + " or";
} }
sql = sql.Trim().Substring(, sql.Length - );
ViewState["where"] = sql;
}
else
{
ViewState["where"] = "";
}
selctInfo();
}
private void ControlManager(int index, int count)
{ if (index == && count == ) return; ;//总页数未一，当前页第一页
if (index >= && index < count)//第一页不是尾页
{ shouye.Enabled = false;
btnUpPage.Enabled = false;
btnNextPage.Enabled = true;
weiye.Enabled = true;
tiaozhuan.Enabled = true;
}
if (index >= && index < count)//不是首页不是尾页
{ shouye.Enabled = true;
btnUpPage.Enabled = true;
btnNextPage.Enabled = true;
weiye.Enabled = true;
tiaozhuan.Enabled = true;
}
if (index >= && index == count)
{
shouye.Enabled = true;
btnUpPage.Enabled = true;
btnNextPage.Enabled = false;
weiye.Enabled = false;
} } #region 页面控制
//首页
protected void shouye_Click(object sender, EventArgs e)
{
ViewState["pageIndex"] = ; selctInfo(); this.btnUpPage.Enabled = false;
btnNextPage.Enabled = true; }
//尾页
protected void weiye_Click(object sender, EventArgs e)
{
ViewState["pageIndex"] = ViewState["pageCount"]; selctInfo(); this.btnUpPage.Enabled = true;
btnNextPage.Enabled = false;
}
//跳转
protected void tiaozhuan_Click(object sender, EventArgs e)
{
int page = Convert.ToInt32(currentpage.Value);
int pageCount = Convert.ToInt32(ViewState["pageCount"]);
if (page > pageCount)
{
currentpage.Value = pageCount.ToString();
ViewState["pageIndex"] = pageCount;
selctInfo(); }
else
{ ViewState["pageIndex"] = page;
selctInfo();
}
}
//显示页项目
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
ViewState["pageSize"] = DropDownList1.SelectedValue;
ViewState["pageIndex"] = ;
selctInfo();
}
//上页
protected void btnUpPage_Click(object sender, EventArgs e)
{
int pageindex = Convert.ToInt32(ViewState["pageIndex"]);
ViewState["pageIndex"] = pageindex - ;
if ((pageindex - ) <= )
{
ViewState["pageIndex"] = ;
this.btnUpPage.Enabled = false; selctInfo();
}
else
{
selctInfo();
}
btnNextPage.Enabled = true; }
//下页
protected void btnNextPage_Click(object sender, EventArgs e)
{ int pageIndex = Convert.ToInt32(ViewState["pageIndex"]) + ;
if (pageIndex == Convert.ToInt32(ViewState["pageCount"]) || pageIndex > Convert.ToInt32(ViewState["pageCount"]))
{ ViewState["pageIndex"] = ViewState["pageCount"];
btnNextPage.Enabled = false; selctInfo();
}
else
{
ViewState["pageIndex"] = pageIndex;
selctInfo();
}
this.btnUpPage.Enabled = true; } #endregion /// <summary>
/// 加载数据
/// </summary>
private void selctInfo()
{
//ViewState["cName"]
//ViewState["eName"]
// ViewState["where"]
//得到查询总数据条数的Sql
string sql = ExcelHelper.GetCountQueryString(ViewState["eName"].ToString(), ViewState["where"].ToString());
//获取分页插叙语句
xb.LoadDome();
string identity = xb.GetIdentity(ViewState["eName"].ToString(), ViewState["cName"].ToString());
string sqlPage = ExcelHelper.GetPageQueryStrong(ViewState["eName"].ToString(), ViewState["cName"].ToString(), ViewState["where"].ToString(), identity, Convert.ToInt32(ViewState["pageIndex"]), Convert.ToInt32(ViewState["pageSize"]));
int recordCount = ExcelHelper.ExecSclaer(sql);
new GridViewTemplate().LoadInfo(this.gridView, ViewState["eName"].ToString(), ViewState["cName"].ToString(), sqlPage);
int pageIndex = Convert.ToInt32(ViewState["pageIndex"]);
int pageSize = Convert.ToInt32(ViewState["pageSize"]);
ViewState["pageCount"] = recordCount % pageSize == ? recordCount / pageSize : (recordCount / pageSize) + ;
lbl_courrentPage.Text = ViewState["pageCount"].ToString();
currentpage.Value = pageIndex.ToString(); ControlManager(pageIndex, Convert.ToInt32(ViewState["pageCount"]));
} }
}
在例中用的了XML 的读写检索，数据拆分，等操作，动态的形成查询语句，添加语句搜索语句等常用的操作语句，
最后编辑和添加页面
前台
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="InsertInfo.aspx.cs" Inherits="ExcelToSqlserver.InsertInfo" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>手动录入数据</title>
<style type="text/css">
li {
display: inline;
list-style: none;
display: inline;
list-style: none;
padding-left: 15px;
} </style>
<script type="text/javascript" src="Scripts/jquery-1.8.2.min.js"></script> </head>
<body>
<form id="form1" runat="server">
<div>
<ul>
<li>手动录入数据</li>
<li>
<asp:Button ID="btnEnterExcel" runat="server" Text="批量导入数据" OnClick="btnEnterExcel_Click" /></li> </ul>
</div>
<br />
<input type="hidden" runat="server" id="hiddEname" />
<input type="hidden" runat="server" id="hiddCname" />
<input type="hidden" runat="server" id="hiddCmd" />
<input type="hidden" runat="server" id="hiddRecordId" /> <div>
<table>
<tbody id="tbody" runat="server">
</tbody>
<tfoot>
<tr>
<td>
<input type="reset" id="reset" value="取消" /></td>
<td style="margin-left: 30px;">
<input type="button" runat="server" id="btnOK" value="添加" /> </td>
</tr>
</tfoot>
</table>
</div>
</form>
</body>
</html>
<script type="text/javascript"> var Values = "";
//检查数据录入是否正常
function checkInput() {
$(".txtVal").each(function () {
var val = $(this).val();
if (val == "") {
$(this).parent("td").next(".mess").css("color", "red").html("录入信息不能为为空");
return false;
} else {
$(this).parent("td").next(".mess").css("color", "green").html("已录入信息");
Values += "'" + $(this).val() + "',";
return true;
}
});
} $(function () {
checkInput();
$(".txtVal").blur(function () {
var va = $(this).val();
if (va == "") {
$(this).parent("td").next(".mess").css("color", "red").html("信息不能为空"); return false;
} else {
$(this).parent("td").next(".mess").css("color", "green").html("已录入信息");
}
});
$("#reset").click(function () {
$(".mess").html("");
}); $("#btnOK").click(function () { //判断数据完整性
var i = 1 * 1;
var values = "";
var res =true;
$(".mess").each(function () {
var t = $(this).html().trim();
if (t != "已录入信息") {
var ask = confirm("第" + i + "项数据还未填写");
res = false;
return; }
i++;
}); //获取数据
$(".txtVal").each(function () {
values += "'" + $(this).val() + "' ¦";//防止出现逗号用特殊符号分割
}); //分解数据
if (values != "") {
values = values.substring(0, values.length - 1);
}
//异步提交
//获取值
var eName = $("#hiddEname").val();
var cName = $("#hiddCname").val();
var cmd = $("#hiddCmd").val();
var id = $("#hiddRecordId").val();
if (!res)
return;
$.get("Ajax/AjaxDateToSql.ashx", { eName: eName, cName: cName, values: values,id:id,cmd: cmd }, function (date) {
if (date != "NO") {
alert(date);
$("#reset").click();
} else {
alert("添加失败！请检查数据");
}
}); });
});
</script>
后台
/*2014-02-27
* 动态生成界面控件接受参数录入。
* 提供手动录入数据
* **/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
//
using CommonLib; namespace ExcelToSqlserver
{
public partial class InsertInfo : System.Web.UI.Page
{
XMLBase xBase = new XMLBase();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string eName = Request.QueryString["eName"].ToString();
string cName = Request.QueryString["cName"].ToString();
string cmd = Request.QueryString["cmd"].ToString();
hiddCmd.Value = cmd;
hiddEname.Value = eName;
hiddCname.Value = cName;
if (Request.QueryString["id"]!=null)
{
hiddRecordId.Value = Request.QueryString["id"].ToString();
//加载数据
LoadDocument(eName, cName, hiddRecordId.Value);
}
else
{
LoadDocument(eName, cName, "");
} }
}
/// <summary>
/// 动态创建页面控件n
/// </summary>
/// <param name="eName"></param>
/// <param name="cName"></param>
private void LoadDocument(string eName, string cName, string id)
{
xBase.LoadDome();
string che = xBase.Check(eName, cName);
string[] colums = che.Split('¦')[].Split(',');
string[] showInfo = che.Split('¦')[].Split('['); //新添加
if (id == "")
{
for (int i = ; i < showInfo.Length; i++)
{
string tr = "<tr> <td style='text-align:right;'>" + showInfo[i] + "：</td> <td><input class='txtVal' type='text' /></td> <td class='mess'></td><td><input class='colu' type='hidden' value='" + colums[i] + "' /></td> </tr>"; tbody.InnerHtml += tr; }
}
else if (id != "")
{
//获取数据值
string values = GetModel(eName, cName, id);
string[] info = values.Split('¦');
btnOK.Value = "确定修改";
for (int i = ; i < showInfo.Length; i++)
{
string tr = "<tr> <td style='text-align:right;'>" + showInfo[i] + "：</td> <td><input class='txtVal' type='text' value='" + info[i + ] + "'/></td> <td class='mess'></td><td><input class='colu' type='hidden' value='" + colums[i] + "' /></td> </tr>";
tbody.InnerHtml += tr; }
} } private string GetModel(string eName, string cName, string id)
{
string value = "";
//获取查询语句
string sql = ExcelHelper.GetModelQueryString(eName, cName, Convert.ToInt32(id));
value = ExcelHelper.SelectToSqlModel(sql);
return value;
} protected void btnEnterExcel_Click(object sender, EventArgs e)
{
string eName= hiddEname.Value;
string cName = hiddCname.Value; ;
string url = "EnterExcel.aspx?eName=" + eName + "&cName=" + cName ;
Response.Redirect(url);
} }
}