using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;
using System.Threading;
namespace GuideInto
{
public class DAL_File
{
public static string FilePath
{
get;
set;
}
/// <summary>
/// 连接Excel
/// </summary>
/// <returns></returns>
public static OleDbConnection Conn()
{
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=" + FilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(connStr);
return conn;
}
/// <summary>
/// 读取所有工作薄
/// </summary>
/// <returns></returns>
public static DataTable GetAllExeclWorkBook()
{
using (OleDbConnection conn = Conn())
{
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
return dt;
}
}
/// <summary>
/// 根据Sql读取Excel中的数据
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataSet Query(string sql)
{
using (OleDbConnection conn = Conn())
{
conn.Open();
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds, "ds");
return ds;
}
}
/// <summary>
/// 读取指定工作薄中的数据
/// </summary>
/// <param name="sheetname"></param>
/// <returns></returns>
public static DataTable GetList(string sheetname)
{
string sql = "select * from [" + sheetname + "]";
return Query(sql).Tables[0];
}
/// <summary>
/// 判断表指定工作薄是否存在
/// </summary>
/// <param name="sheetname"></param>
/// <returns></returns>
public static bool Exists(string sheetname)
{
try
{
string sql = "select top 1 * from [" + sheetname + "]";
Query(sql);
return true;
}
catch (OleDbException ex)
{
if (ex.Message.Contains("不存在"))
return false;
}
return false;
}
/// <summary>
/// 删除execl中指定工作薄
/// </summary>
/// <param name="sheetname"></param>
public static void DropSheet(string sheetname)
{
using (OleDbConnection conn = Conn())
{
using (OleDbCommand cmd = new OleDbCommand())
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "drop table " + sheetname;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 插入数据到工作薄
/// </summary>
/// <param name="dt"></param>
public static void AddDateToSheet(DataTable dt, ProgressBar bar, Label lb)
{
using (OleDbConnection conn = Conn())
{
conn.Open();
using (OleDbTransaction trans = conn.BeginTransaction())
{
try
{
OleDbCommand cmd = new OleDbCommand();
StringBuilder strSql;
if (dt.Rows.Count > 1)
{
bar.Minimum = 1;
bar.Maximum = dt.Rows.Count;
lb.Visible = bar.Visible = true;
}
for (int j = 0; j < dt.Rows.Count; j++)
{
if (j <= bar.Maximum)
{
bar.Value = j + 1;
lb.Text = (bar.Value * 100 / bar.Maximum).ToString() + "%";
}
Application.DoEvents();
cmd.Transaction = trans;
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
strSql = new StringBuilder();
strSql.Append("insert into " + dt.TableName + "(");
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i != dt.Columns.Count - 1)
strSql.Append("[" + dt.Columns[i].ColumnName + "],");
else
strSql.Append("[" + dt.Columns[i].ColumnName + "]");
}
strSql.Append(") values (");
string value;
for (int i = 0; i < dt.Columns.Count; i++)
{
value = dt.Rows[j][i].ToString() == "" ? "null" : dt.Rows[j][i].ToString();
if (!string.IsNullOrEmpty(value))
{
value = value.Replace("'", "''");
}
if (GetSheetTypeByInt(dt.Columns[i].DataType.Name) && i != dt.Columns.Count - 1 && value.Equals("null"))
strSql.Append(value + ",");
else if (GetSheetTypeByInt(dt.Columns[i].DataType.Name) && i == dt.Columns.Count - 1 && value.Equals("null"))
strSql.Append(value);
else if (GetSheetTypeByInt(dt.Columns[i].DataType.Name) && i != dt.Columns.Count - 1)
strSql.Append("'" + value + "',");
else if (GetSheetTypeByInt(dt.Columns[i].DataType.Name) && i == dt.Columns.Count - 1)
strSql.Append("'" + value + "'");
else if (!GetSheetTypeByInt(dt.Columns[i].DataType.Name) && i != dt.Columns.Count - 1)
strSql.Append(value + ",");
else if (!GetSheetTypeByInt(dt.Columns[i].DataType.Name) && i == dt.Columns.Count - 1)
strSql.Append(value);
}
strSql.Append(")");
cmd.CommandText = strSql.ToString();
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
}
}
//创建Execl工作薄
public static void CreateExecl(DataTable dt)
{
using (OleDbConnection conn = Conn())
{
conn.Open();
using (OleDbCommand cmd = new OleDbCommand())
{
StringBuilder strSql = new StringBuilder();
strSql.Append("create table " + dt.TableName + "(");
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i == dt.Columns.Count - 1)
strSql.Append("[" + dt.Columns[i].ColumnName + "] " + ReturnExeclType(dt.Columns[i].DataType.Name) + " null");
else
strSql.Append("[" + dt.Columns[i].ColumnName + "] " + ReturnExeclType(dt.Columns[i].DataType.Name) + " null,");
}
strSql.Append(")");
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSql.ToString();
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Net数据类型与Excel中的数据类型转换
/// </summary>
/// <param name="SqlType"></param>
/// <returns></returns>
private static string ReturnExeclType(string SqlType)
{
switch (SqlType.ToLower())
{
case "string":
//case "smalldatetime":
//case "datetime":
return "nvarchar(255)";
case "byte":
return "byte";
case "int32":
return "int";
case "decimal":
return "numeric";
case "tinyint":
return "byte";
case "smalldatetime":
return "datetime";
case "boolean":
return "bit";
default:
return SqlType;
}
}
/// <summary>
/// Excel中的数据类型与Net数据类型转换
/// </summary>
/// <param name="SqlType"></param>
/// <returns></returns>
private static bool GetSheetTypeByInt(string sheettype)
{
switch (sheettype.ToLower())
{
case "string":
return true;
case "smalldatetime":
return true;
case "datetime":
return true;
default:
return false;
}
}
}
}