using Aspose.Cells
public static string ConnectionString(string _Path)//数据库的路径
{
return @"Provider=Microsoft.ace.oledb.12.0;" + "Data Source=" + _Path + ";" + "Extended Properties=Excel 12.0;";
}
/// <summary>
/// 过滤危险字符,包括应为输入法中的单引号,分号,小划线等等
/// </summary>
/// <param name="sValue">要过滤的字符串</param>
/// <returns>安全字符串</returns>
private static string SqlFilter(string sValue)
{
sValue = sValue.Replace(";", " ");
sValue = sValue.Replace("--", " ");
sValue = sValue.Replace("xp_", " ");
sValue = sValue.Replace("/*", " ");
sValue = sValue.Replace("*/", " ");
sValue = sValue.Replace(" ", " ");
sValue = sValue.Replace(";", ";");
sValue = sValue.Replace("∷", " ");
sValue = sValue.Replace("&", "&");
return sValue.Trim();
}
/// <summary>
/// 执行Sql语句,返回DataSet
/// </summary>
/// <param name="Path">Excel路径</param>
/// <param name="SqlStr">Sql语句</param>
/// <returns>数据集合</returns>
public static DataSet ExecuteDataSet(string Path, string SqlStr)
{
DataSet Ds = null;
OleDbDataAdapter Da;
OleDbCommand Cmd;
using (OleDbConnection Conn = new OleDbConnection(ConnectionString(Path)))
{
try
{
Cmd = new OleDbCommand(SqlFilter(SqlStr), Conn);
Da = new OleDbDataAdapter(Cmd);
Ds = new DataSet();
Da.Fill(Ds);
}
catch
{ }
return Ds;
}
}
/// <summary>
/// 执行Sql语句,返回DataTable
/// </summary>
/// <param name="Path">Excel路径</param>
/// <param name="SqlStr">Sql语句</param>
/// <returns>数据集合</returns>
public static DataTable ExecuteDataTable(string Path, string SqlStr)
{
DataTable dt = new DataTable();
try
{
dt = ExecuteDataSet(Path, SqlStr).Tables[0];
}
catch { }
return dt;
}
/// <summary>
/// 获取Excel数据集getExcelDT/// </summary>
/// <param name="_Path">Excel路径</param>
/// <returns>结果集</returns>
public static DataTable getExcelTable(string _Path)
{
string _SqlStr = "SELECT * FROM [" + getExcelTablName(_Path) + "]";
DataTable _dt = ExecuteDataTable(_Path, _SqlStr);
return _dt;
}
/// <summary>
/// 获取Excel表名
/// </summary>
/// <param name="Path">Excel路径</param>
/// <returns>表明集合字符串</returns>
public static string getExcelTablName(string Path)
{
string _SheetName = "";
DataTable _dt = GetExcelTableName(Path);
if(_dt.Rows.Count>0)
{
_SheetName = _dt.Rows[0]["TABLE_NAME"].ToString();
}
return _SheetName;
}
/// <summary>
/// 读取Excel表名
/// </summary>
/// <param name="Path">Excel路径</param>
/// <returns>表名数据集合</returns>
public static DataTable GetExcelTableName(string Path)
{
DataTable _Table = new DataTable();
try
{
if (System.IO.File.Exists(Path))
{
OleDbConnection _ExcelConn = new OleDbConnection("Provider=Microsoft.ace.oledb.12.0;Extended Properties=\"Excel 12.0\";Data Source=" + Path);
_ExcelConn.Open();
_Table = _ExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
_ExcelConn.Close();
}
return _Table;
}
catch
{
return _Table;
}
}
/ // <summary>
/// 获取EXCEL表格----------不需要OFFCIE组建
/// </summary>
/// <param name="Path">EXCEL路径</param>
/// <returns></returns>
public static DataTable getExcelDT(string Path)
{
Workbook wb = new Workbook(); //定义
wb.LoadData(Path);//记载EXCEL
Worksheet ws = wb.Worksheets[0];//获取到EXCEL 一个 工作薄
DataTable dt = ws.Cells.ExportDataTable(0, 0, ws.Cells.MaxDataRow + 1, ws.Cells.MaxColumn + 1, true);//获取到工作 薄里的 表结构 数据 第一行作为 表头名称
return dt;
}