自己写的 不喜欢 不要喷, 请尊重开源精神 谢谢![code=C#]// ===================================================================
// Res Ver1.0.0.0
//====================================================================
// Austin @Copy Right2010-2011
// File： ExcelHelper.cs
// Project Name：AusExcelHelper
// DateTime：2010-5-12
// Code By：Austin
// ===================================================================
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;namespace AusExcelHelper
{
/// <summary>
/// Excel帮助文件
/// </summary>
public class ExcelHelper
{
#region [Tips:] ExcelHelper_Members : _connString, _excelStructure, ConnString, _checkData, CheckData, ExcelStruct
/// <summary>
/// 连接字符串
/// </summary>
private string _connString; /// <summary>
/// Excel结构
/// </summary>
private ExcelStructure _excelStructure = new ExcelStructure(); /// <summary>
/// Excel连接字符串
/// </summary>
public string ConnString
{
set { _connString = value; }
get { return _connString; }
} private DataTable _checkData = new DataTable(); private DataTable CheckData
{
get
{
string bookName = this.GetExcelBookName();
_checkData = this.FillDataSetFromExcel(string.Format("select * from {0}", bookName), "CheckTable");
return _checkData;
}
} public ExcelStructure ExcelStruct
{
set { _excelStructure = value; }
get { return _excelStructure; }
}
#endregion #region [Tips:] Construction Function, R:Four
public ExcelHelper() { } public ExcelHelper(string StrConnection)
{
_connString = StrConnection;
} public ExcelHelper(ExcelStructure excelStructure)
{
_excelStructure = excelStructure;
} public ExcelHelper(string StrConnection, ExcelStructure excelStructure)
{
_connString = StrConnection;
_excelStructure = excelStructure;
}
#endregion #region [Tips:] Main Function
/// <summary>
/// 设置Excel连接字符串(默认IMEX为2)
/// </summary>
/// <param name="ExcelFilePath">Excel路径</param>
#region void SetConnStringFilePath(string ExcelFilePath)
public void SetConnStringFilePath(string ExcelFilePath)
{
SetConnStringFilePath(ExcelFilePath, "1");
}
#endregion /// <summary>
/// 设置Excel连接字符串
/// </summary>
/// <param name="ExcelFilePath">Excel路径</param>
/// <param name="IMEX">IMEX类型:0只读,1读,2读写</param>
#region void SetConnStringFilePath(string ExcelFilePath, string IMEX)
public void SetConnStringFilePath(string ExcelFilePath, string IMEX)
{
if (File.Exists(ExcelFilePath))
{
ConnString = "Provider=Microsoft.Jet.OleDb.4.0;data source=" + ExcelFilePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=" + IMEX + "'";
}
else
{
ConnString = "ErrorExcelPath";
}
}
#endregion /// <summary>
/// Excel数据填充数据集
/// </summary>
/// <param name="StrSql">查询语句</param>
/// <returns>返回DataSet数据集</returns>
#region DataTable FillDataSetFromExcel(string StrSql)
public DataTable FillDataSetFromExcel(string StrSql)
{
return FillDataSetFromExcel(StrSql, "TempTable");
}
#endregion /// <summary>
/// Excel数据填充数据集
/// </summary>
/// <param name="StrSql">查询语句</param>
/// <param name="TableName">映射的源表名称</param>
/// <returns>返回DataSet数据集</returns>
#region DataTable FillDataSetFromExcel(string StrSql, string TableName)
public DataTable FillDataSetFromExcel(string StrSql, string TableName)
{
return FillDataSetFromExcel(StrSql, TableName, _connString);
}
#endregion /// <summary>
/// Excel查询
/// </summary>
/// <param name="StrSql">查询语句</param>
/// <param name="TableName">映射的源表名称</param>
/// <param name="strExcelConnection">连接字符串</param>
/// <returns>返回DataSet数据集</returns>
#region DataTable FillDataSetFromExcel(string StrSql,string TableName,string strExcelConnection)
public DataTable FillDataSetFromExcel(string StrSql,string TableName,string strExcelConnection)
{
using (OleDbConnection conn = new OleDbConnection(strExcelConnection))
{
conn.Open();
DataSet ds = new DataSet();
using (OleDbDataAdapter odda = new OleDbDataAdapter(StrSql, conn))
{
odda.Fill(ds, TableName); return ds.Tables[TableName];
}
}
}
#endregion /// <summary>
/// Excel修改
/// </summary>
/// <param name="StrSql">Sql语句</param>
/// <returns></returns>
#region int ExecuteNonQuery(string StrSql)
public int ExecuteNonQuery(string StrSql)
{
using (OleDbConnection conn = new OleDbConnection(_connString))
{
conn.Open();
DataSet ds = new DataSet();
using (OleDbCommand cmd = new OleDbCommand(StrSql, conn))
{
return cmd.ExecuteNonQuery();
}
}
}
#endregion /// <summary>
/// Excel工作簿名称获取[获取第一个工作簿名称]
/// </summary>
/// <returns></returns>
#region string GetExcelBookName()
public string GetExcelBookName()
{
return GetExcelBookName(0);
}
#endregion /// <summary>
/// Excel工作簿名称获取
/// </summary>
/// <param name="flag">第几个工作簿</param>
/// <returns>返回首行excel工作簿名称</returns>
/// <res></res>
#region string GetExcelBookName(int flag)
public string GetExcelBookName(int flag)
{
using (OleDbConnection conn = new OleDbConnection(_connString))
{
conn.Open();
DataTable dt;
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if ((dt.Rows.Count > 0))
{
return "[" + dt.Rows[flag]["TABLE_NAME"].ToString() + "]";
}
else
{
return string.Empty;
}
}
}
#endregion
// Res Ver1.0.0.0
//====================================================================
// Austin @Copy Right2010-2011
// File： ExcelHelper.cs
// Project Name：AusExcelHelper
// DateTime：2010-5-12
// Code By：Austin
// ===================================================================
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;namespace AusExcelHelper
{
/// <summary>
/// Excel帮助文件
/// </summary>
public class ExcelHelper
{
#region [Tips:] ExcelHelper_Members : _connString, _excelStructure, ConnString, _checkData, CheckData, ExcelStruct
/// <summary>
/// 连接字符串
/// </summary>
private string _connString; /// <summary>
/// Excel结构
/// </summary>
private ExcelStructure _excelStructure = new ExcelStructure(); /// <summary>
/// Excel连接字符串
/// </summary>
public string ConnString
{
set { _connString = value; }
get { return _connString; }
} private DataTable _checkData = new DataTable(); private DataTable CheckData
{
get
{
string bookName = this.GetExcelBookName();
_checkData = this.FillDataSetFromExcel(string.Format("select * from {0}", bookName), "CheckTable");
return _checkData;
}
} public ExcelStructure ExcelStruct
{
set { _excelStructure = value; }
get { return _excelStructure; }
}
#endregion #region [Tips:] Construction Function, R:Four
public ExcelHelper() { } public ExcelHelper(string StrConnection)
{
_connString = StrConnection;
} public ExcelHelper(ExcelStructure excelStructure)
{
_excelStructure = excelStructure;
} public ExcelHelper(string StrConnection, ExcelStructure excelStructure)
{
_connString = StrConnection;
_excelStructure = excelStructure;
}
#endregion #region [Tips:] Main Function
/// <summary>
/// 设置Excel连接字符串(默认IMEX为2)
/// </summary>
/// <param name="ExcelFilePath">Excel路径</param>
#region void SetConnStringFilePath(string ExcelFilePath)
public void SetConnStringFilePath(string ExcelFilePath)
{
SetConnStringFilePath(ExcelFilePath, "1");
}
#endregion /// <summary>
/// 设置Excel连接字符串
/// </summary>
/// <param name="ExcelFilePath">Excel路径</param>
/// <param name="IMEX">IMEX类型:0只读,1读,2读写</param>
#region void SetConnStringFilePath(string ExcelFilePath, string IMEX)
public void SetConnStringFilePath(string ExcelFilePath, string IMEX)
{
if (File.Exists(ExcelFilePath))
{
ConnString = "Provider=Microsoft.Jet.OleDb.4.0;data source=" + ExcelFilePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=" + IMEX + "'";
}
else
{
ConnString = "ErrorExcelPath";
}
}
#endregion /// <summary>
/// Excel数据填充数据集
/// </summary>
/// <param name="StrSql">查询语句</param>
/// <returns>返回DataSet数据集</returns>
#region DataTable FillDataSetFromExcel(string StrSql)
public DataTable FillDataSetFromExcel(string StrSql)
{
return FillDataSetFromExcel(StrSql, "TempTable");
}
#endregion /// <summary>
/// Excel数据填充数据集
/// </summary>
/// <param name="StrSql">查询语句</param>
/// <param name="TableName">映射的源表名称</param>
/// <returns>返回DataSet数据集</returns>
#region DataTable FillDataSetFromExcel(string StrSql, string TableName)
public DataTable FillDataSetFromExcel(string StrSql, string TableName)
{
return FillDataSetFromExcel(StrSql, TableName, _connString);
}
#endregion /// <summary>
/// Excel查询
/// </summary>
/// <param name="StrSql">查询语句</param>
/// <param name="TableName">映射的源表名称</param>
/// <param name="strExcelConnection">连接字符串</param>
/// <returns>返回DataSet数据集</returns>
#region DataTable FillDataSetFromExcel(string StrSql,string TableName,string strExcelConnection)
public DataTable FillDataSetFromExcel(string StrSql,string TableName,string strExcelConnection)
{
using (OleDbConnection conn = new OleDbConnection(strExcelConnection))
{
conn.Open();
DataSet ds = new DataSet();
using (OleDbDataAdapter odda = new OleDbDataAdapter(StrSql, conn))
{
odda.Fill(ds, TableName); return ds.Tables[TableName];
}
}
}
#endregion /// <summary>
/// Excel修改
/// </summary>
/// <param name="StrSql">Sql语句</param>
/// <returns></returns>
#region int ExecuteNonQuery(string StrSql)
public int ExecuteNonQuery(string StrSql)
{
using (OleDbConnection conn = new OleDbConnection(_connString))
{
conn.Open();
DataSet ds = new DataSet();
using (OleDbCommand cmd = new OleDbCommand(StrSql, conn))
{
return cmd.ExecuteNonQuery();
}
}
}
#endregion /// <summary>
/// Excel工作簿名称获取[获取第一个工作簿名称]
/// </summary>
/// <returns></returns>
#region string GetExcelBookName()
public string GetExcelBookName()
{
return GetExcelBookName(0);
}
#endregion /// <summary>
/// Excel工作簿名称获取
/// </summary>
/// <param name="flag">第几个工作簿</param>
/// <returns>返回首行excel工作簿名称</returns>
/// <res></res>
#region string GetExcelBookName(int flag)
public string GetExcelBookName(int flag)
{
using (OleDbConnection conn = new OleDbConnection(_connString))
{
conn.Open();
DataTable dt;
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if ((dt.Rows.Count > 0))
{
return "[" + dt.Rows[flag]["TABLE_NAME"].ToString() + "]";
}
else
{
return string.Empty;
}
}
}
#endregion
/// 通用全局检查 检查ExcelStruct,ConnString是否指定
/// </summary>
/// <returns></returns>
private string CheckCommon()
{
if (_excelStructure == null)
{
return "未指定ExcelStruct";
}
else
{
if (!(CheckData.Rows.Count > 0))
{
return "数据表数据为空,或未指定ConnString";
}
else
{
if (_excelStructure.IsEmpty())
{
return "ExcelStruct数据为空";
}
}
}
return string.Empty;
} /// <summary>
/// 执行列检查,成功返回"OK",错误返回错误信息.
/// </summary>
/// <returns></returns>
public string CheckColumnName()
{
string CommonErrorMsg = CheckCommon();
if (string.IsNullOrEmpty(CommonErrorMsg))
{
Dictionary<string, string[]> Structure = _excelStructure.MainStructure;
List<string> columnNames = new List<string>();
DataTable _chkData = CheckData;
foreach (DataColumn column in _chkData.Columns)
{
columnNames.Add(column.ColumnName);
}
//判断列数量
if (Structure.Count == columnNames.Count)
{
foreach (string item in columnNames)
{
if (!Structure.ContainsKey(item))
{
return string.Format("{0}列不在指定的上传文档结构中!请检查Excel文档结构!", item);
}
}
}
else
{
return "Excel中的列数量和指定格式不符!请修改后重新上传!";
}
}
else
{
return CommonErrorMsg;
}
return "OK";
}
/// 执行列数据类型检查,为空检查,成功返回"OK",错误返回错误信息.
/// </summary>
/// <returns></returns>
public string CheckColumnDataType()
{
string ErrorMsg = string.Empty;
string CommonErrorMsg = CheckCommon();
if (string.IsNullOrEmpty(CommonErrorMsg))
{
Dictionary<string, string[]> Structure = _excelStructure.MainStructure;
DataTable _chkData = CheckData;
foreach (KeyValuePair<string, string[]> item in Structure)
{
if (_chkData.Columns.Contains(item.Key))
{
string thisType = item.Value[0];
bool IsNull = bool.Parse(item.Value[1]);
switch (thisType)
{
case "System.String":
{
if (!IsNull)
{
int flag = 1;
string errorLine = string.Empty;
foreach (DataRow row in _checkData.Rows)
{
if (string.IsNullOrEmpty(row[item.Key].ToString()))
{
errorLine += flag.ToString() + ",";
}
flag++;
}
if(!string.IsNullOrEmpty(errorLine))
{
ErrorMsg += string.Format("{0}列,第{0}行数据不能为空!\n", item.Key, errorLine);
}
}
break;
}
case "System.Int":
{
//本身代码的简洁性上来说,可以将检查为空和检查数字分开写(2次大循环)效率考虑 最后还是选择了1次循环2次代码编写的方式(后面检查都这么写)
if (!IsNull)
{
int flag = 1;
string errorLine = string.Empty;
string EmptyErrorLine = string.Empty;
foreach (DataRow row in _checkData.Rows)
{
if (string.IsNullOrEmpty(row[item.Key].ToString()))
{
EmptyErrorLine += flag.ToString() + ",";
}
else
{
if(!RegexVaid.IsValid(row[item.Key].ToString(),RegexVaid.ValidIntB))
{
errorLine += flag.ToString() + ",";
}
}
flag++;
}
if (!string.IsNullOrEmpty(EmptyErrorLine) || !string.IsNullOrEmpty(errorLine))
{
ErrorMsg += "" + item.Key + "列," + (string.IsNullOrEmpty(EmptyErrorLine) ? string.Empty : "第" + EmptyErrorLine + "行数据不能为空!") + (string.IsNullOrEmpty(errorLine) ? string.Empty : "第" + errorLine + "行数据不是整数!\n");
}
}
else
{
int flag = 1;
string errorLine = string.Empty;
foreach (DataRow row in _checkData.Rows)
{
if (!string.IsNullOrEmpty(row[item.Key].ToString()))
{
if (!RegexVaid.IsValid(row[item.Key].ToString(), RegexVaid.ValidIntB))
{
errorLine += flag.ToString() + ",";
}
}
flag++;
}
if ( !string.IsNullOrEmpty(errorLine))
{
ErrorMsg += "" + item.Key + "列,第" + errorLine + "行数据不是整数!\n";
}
}
break;
}
case "System.Double":
{
if (!IsNull)
{
int flag = 1;
string errorLine = string.Empty;
string EmptyErrorLine = string.Empty;
foreach (DataRow row in _checkData.Rows)
{
if (string.IsNullOrEmpty(row[item.Key].ToString()))
{
EmptyErrorLine += flag.ToString() + ",";
}
else
{
if (!RegexVaid.IsValid(row[item.Key].ToString(), RegexVaid.ValidNumeric))
{
errorLine += flag.ToString() + ",";
}
}
flag++;
}
if (!string.IsNullOrEmpty(EmptyErrorLine) || !string.IsNullOrEmpty(errorLine))
{
ErrorMsg += "" + item.Key + "列," + (string.IsNullOrEmpty(EmptyErrorLine) ? string.Empty : "第" + EmptyErrorLine + "行数据不能为空!") + (string.IsNullOrEmpty(errorLine) ? string.Empty : "第" + errorLine + "行数据不是数字!\n");
}
}
else
{
int flag = 1;
string errorLine = string.Empty;
foreach (DataRow row in _checkData.Rows)
{
if (!string.IsNullOrEmpty(row[item.Key].ToString()))
{
if (!RegexVaid.IsValid(row[item.Key].ToString(), RegexVaid.ValidNumeric))
{
errorLine += flag.ToString() + ",";
}
}
flag++;
}
if (!string.IsNullOrEmpty(errorLine))
{
ErrorMsg += "" + item.Key + "列,第" + errorLine + "行数据不是数字!\n";
}
}
break;
}
{
if (!IsNull)
{
int flag = 1;
string errorLine = string.Empty;
string EmptyErrorLine = string.Empty;
foreach (DataRow row in _checkData.Rows)
{
if (string.IsNullOrEmpty(row[item.Key].ToString()))
{
EmptyErrorLine += flag.ToString() + ",";
}
else
{
try
{
DateTime.Parse(row[item.Key].ToString());
}
catch
{
errorLine += flag.ToString() + ",";
}
}
flag++;
}
if (!string.IsNullOrEmpty(EmptyErrorLine) || !string.IsNullOrEmpty(errorLine))
{
ErrorMsg += "" + item.Key + "列," + (string.IsNullOrEmpty(EmptyErrorLine) ? string.Empty : "第" + EmptyErrorLine + "行时间数据不能为空!") + (string.IsNullOrEmpty(errorLine) ? string.Empty : "第" + errorLine + "行数据不是正确的时间格式!\n");
}
}
else
{
int flag = 1;
string errorLine = string.Empty;
foreach (DataRow row in _checkData.Rows)
{
try
{
if (!string.IsNullOrEmpty(row[item.Key].ToString()))
{
DateTime.Parse(row[item.Key].ToString());
}
}
catch
{
errorLine += flag.ToString() + ",";
}
flag++;
}
if (!string.IsNullOrEmpty(errorLine))
{
ErrorMsg += "" + item.Key + "列,第" + errorLine + "行数据不是正确的时间格式!\n";
}
}
break;
}
default:
break;
}
}
else
{
return string.Format("不存在此{0}列,数据检查无效!", item.Key);
}
}
if (!string.IsNullOrEmpty(ErrorMsg))
{
return ErrorMsg;
}
}
else
{
return CommonErrorMsg;
}
return "OK";
}