namespace HW.Utility
|
{
|
using System;
|
using System.Collections;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.OleDb;
|
using System.IO;
|
using System.Runtime.CompilerServices;
|
using System.Text;
|
|
public sealed class ExcelOle : IDisposable
|
{
|
public const int EXCEL_MAXROW = 0x10000;
|
private string excelPath;
|
private string specifyTableName;
|
|
public event RenderDataFromDataRowHandler OnFillDataRowRender;
|
|
public ExcelOle() : this("")
|
{
|
}
|
|
public ExcelOle(string excelPath)
|
{
|
this.excelPath = excelPath;
|
}
|
|
private string CleanStringForSql(object obj)
|
{
|
return this.CleanStringForSql(obj.ToString());
|
}
|
|
private string CleanStringForSql(string str)
|
{
|
return str.Replace("'", "''");
|
}
|
|
public void Dispose()
|
{
|
this.excelPath = null;
|
}
|
|
public void FillDataByList(DataTable dt)
|
{
|
List<string> list = new List<string>();
|
for (int i = 0; i < dt.Columns.Count; i++)
|
{
|
list.Add(dt.Columns[i].ColumnName);
|
}
|
this.FillDataByList(dt, list.ToArray());
|
}
|
|
public void FillDataByList(DataTable dt, string[] columns)
|
{
|
if ((columns == null) || (columns.Length == 0))
|
{
|
this.FillDataByList(dt);
|
}
|
if (dt.Rows.Count > 0x10000)
|
{
|
while (dt.Rows.Count >= 0x10000)
|
{
|
dt.Rows.RemoveAt(dt.Rows.Count - 1);
|
}
|
}
|
ArrayList alSql = new ArrayList();
|
int length = columns.Length;
|
foreach (DataRow row in dt.Rows)
|
{
|
StringBuilder builder = new StringBuilder();
|
builder.Append("INSERT INTO {0} ({1}) VALUES (");
|
for (int i = 0; i < length; i++)
|
{
|
string str = "";
|
string columnName = columns[i];
|
if (this.OnFillDataRowRender != null)
|
{
|
str = this.OnFillDataRowRender(row, row[columnName], columnName, row.Table.Columns[columnName].DataType);
|
}
|
else if ((row[columnName] == null) || (row[columnName] is DBNull))
|
{
|
str = "";
|
}
|
else
|
{
|
str = row[columns[i]].ToString();
|
}
|
builder.Append("'" + str + "',");
|
}
|
builder.Remove(builder.Length - 1, 1);
|
builder.Append(");");
|
alSql.Add(builder.ToString());
|
}
|
this.RunSqlArray(alSql);
|
}
|
|
private void RunSqlArray(ArrayList alSql)
|
{
|
using (OleDbConnection connection = new OleDbConnection(this.ExcelConnString))
|
{
|
DataTable schemaTable;
|
connection.Open();
|
string specifyTableName = "";
|
if (string.IsNullOrEmpty(this.specifyTableName))
|
{
|
specifyTableName = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][2].ToString().Trim();
|
}
|
else
|
{
|
specifyTableName = this.specifyTableName;
|
}
|
specifyTableName = "[" + specifyTableName + "]";
|
string str3 = "";
|
OleDbCommand command = null;
|
command = new OleDbCommand(string.Format("SELECT TOP 1 * FROM {0}", specifyTableName), connection);
|
using (OleDbDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo))
|
{
|
schemaTable = reader.GetSchemaTable();
|
}
|
foreach (DataRow row in schemaTable.Rows)
|
{
|
str3 = str3 + "[" + row["ColumnName"].ToString() + "],";
|
}
|
str3 = str3.TrimEnd(new char[] { ',' });
|
foreach (string str4 in alSql)
|
{
|
new OleDbCommand(string.Format(str4, specifyTableName, str3), connection).ExecuteNonQuery();
|
}
|
}
|
}
|
|
public string SetSpecifyTableName(string specifyTableName)
|
{
|
this.specifyTableName = specifyTableName + "$";
|
return this.specifyTableName;
|
}
|
|
private string ExcelConnString
|
{
|
get
|
{
|
return string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source ={0};Extended Properties='Excel 8.0;'", this.ExcelPath);
|
}
|
}
|
|
public string ExcelPath
|
{
|
get
|
{
|
if (!(!string.IsNullOrEmpty(this.excelPath) && File.Exists(this.excelPath)))
|
{
|
throw new Exception("Excel为空");
|
}
|
return this.excelPath;
|
}
|
set
|
{
|
this.excelPath = value;
|
}
|
}
|
}
|
}
|
