using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
namespace DataVsExel
{
public class DataVsExel
{
private string connectionString;
private string cmdText;
private OleDbConnection oleConn;
private OleDbCommand oleCmd;
//private OleDbDataAdapter oleDAdapter;
//private OleDbDataReader oleDReader;
public DataVsExel() { }
/// <summary>
/// InitialDataSetToExcel
/// </summary>
/// <param name="connectionStr"></param>
/// <param name="ds"></param>
/// <returns></returns>
public bool InitialDataSetToExcel(string connectionStr, DataSet ds)
{
try
{
connectionString = connectionStr;
oleConn = new OleDbConnection(connectionString);
oleCmd = new OleDbCommand();
oleCmd.Connection = oleConn;
oleConn.Open();
int cCount = ds.Tables[0].Columns.Count;
int rCount = ds.Tables[0].Rows.Count;
#region Create table sheet for all
cmdText = "create table [ALL] (";
for (int i = 0; i < cCount; i++)
{
if (i < cCount - 1)
{
cmdText += ds.Tables[0].Columns[i].ColumnName + " char(255),";
}
else
{
cmdText += ds.Tables[0].Columns[i].ColumnName + " char(255));";
}
}
oleCmd.CommandText = cmdText;
oleCmd.ExecuteNonQuery();
// Insert Data to Excel
for (int i = 0; i < rCount; i++)
{
cmdText = "";
string colNames = "(", colValues = "(";
// OleDbParameterCollection olePC = new OleDbParameterCollection();
DataRow dr = ds.Tables[0].Rows[i];
oleCmd = new OleDbCommand();
oleCmd.Connection = oleConn;
for (int j = 0; j < dr.ItemArray.Length; j++)
{
string parameterName = ":" + ds.Tables[0].Columns[j].ColumnName;
object value = dr.ItemArray[j];
oleCmd.Parameters.AddWithValue(parameterName, value);
if (j < dr.ItemArray.Length - 1)
{
colNames += ds.Tables[0].Columns[j].ColumnName + ",";
colValues += ":" + ds.Tables[0].Columns[j].ColumnName + ",";
}
else
{
colNames += ds.Tables[0].Columns[j].ColumnName + ")";
colValues += ":" + ds.Tables[0].Columns[j].ColumnName + ")";
}
}
cmdText = "insert into [ALL$] " + colNames + " values " + colValues;
oleCmd.CommandText = cmdText;
oleCmd.ExecuteNonQuery();
}
#endregion
#region Create table sheet for array
cmdText = "create table [ARRAY] (";
for (int i = 0; i < cCount; i++)
{
if (i < cCount - 1)
{
cmdText += ds.Tables[0].Columns[i].ColumnName + " char(255),";
}
else
{
cmdText += ds.Tables[0].Columns[i].ColumnName + " char(255));";
}
}
oleCmd.CommandText = cmdText;
oleCmd.ExecuteNonQuery();
// Insert Data to Excel
for (int i = 0; i < rCount; i++)
{
cmdText = "";
string colNames = "(", colValues = "(";
// OleDbParameterCollection olePC = new OleDbParameterCollection();
DataRow dr = ds.Tables[0].Rows[i];
if (!dr[0].ToString().StartsWith("1A"))
continue;
oleCmd = new OleDbCommand();
oleCmd.Connection = oleConn;
for (int j = 0; j < dr.ItemArray.Length; j++)
{
string parameterName = ":" + ds.Tables[0].Columns[j].ColumnName;
object value = dr.ItemArray[j];
oleCmd.Parameters.AddWithValue(parameterName, value);
if (j < dr.ItemArray.Length - 1)
{
colNames += ds.Tables[0].Columns[j].ColumnName + ",";
colValues += ":" + ds.Tables[0].Columns[j].ColumnName + ",";
}
else
{
colNames += ds.Tables[0].Columns[j].ColumnName + ")";
colValues += ":" + ds.Tables[0].Columns[j].ColumnName + ")";
}
}
cmdText = "insert into [ARRAY$] " + colNames + " values " + colValues;
oleCmd.CommandText = cmdText;
oleCmd.ExecuteNonQuery();
}
#endregion
#region Create table sheet for cell
cmdText = "create table [CELL] (";
for (int i = 0; i < cCount; i++)
{
if (i < cCount - 1)
{
cmdText += ds.Tables[0].Columns[i].ColumnName + " char(255),";
}
else
{
cmdText += ds.Tables[0].Columns[i].ColumnName + " char(255));";
}
}
oleCmd.CommandText = cmdText;
oleCmd.ExecuteNonQuery();
// Insert Data to Excel
for (int i = 0; i < rCount; i++)
{
cmdText = "";
string colNames = "(", colValues = "(";
// OleDbParameterCollection olePC = new OleDbParameterCollection();
DataRow dr = ds.Tables[0].Rows[i];
if (!dr[0].ToString().StartsWith("1C"))
continue;
oleCmd = new OleDbCommand();
oleCmd.Connection = oleConn;
for (int j = 0; j < dr.ItemArray.Length; j++)
{
string parameterName = ":" + ds.Tables[0].Columns[j].ColumnName;
object value = dr.ItemArray[j];
oleCmd.Parameters.AddWithValue(parameterName, value);
if (j < dr.ItemArray.Length - 1)
{
colNames += ds.Tables[0].Columns[j].ColumnName + ",";
colValues += ":" + ds.Tables[0].Columns[j].ColumnName + ",";
}
else
{
colNames += ds.Tables[0].Columns[j].ColumnName + ")";
colValues += ":" + ds.Tables[0].Columns[j].ColumnName + ")";
}
}
cmdText = "insert into [CELL$] " + colNames + " values " + colValues;
oleCmd.CommandText = cmdText;
oleCmd.ExecuteNonQuery();
}
#endregion
#region Create table sheet for cf
cmdText = "create table [CF] (";
for (int i = 0; i < cCount; i++)
{
if (i < cCount - 1)
{
cmdText += ds.Tables[0].Columns[i].ColumnName + " char(255),";
}
else
{
cmdText += ds.Tables[0].Columns[i].ColumnName + " char(255));";
}
}
oleCmd.CommandText = cmdText;
oleCmd.ExecuteNonQuery();
// Insert Data to Excel
for (int i = 0; i < rCount; i++)
{
cmdText = "";
string colNames = "(", colValues = "(";
// OleDbParameterCollection olePC = new OleDbParameterCollection();
DataRow dr = ds.Tables[0].Rows[i];
if (!dr[0].ToString().StartsWith("1F"))
continue;
oleCmd = new OleDbCommand();
oleCmd.Connection = oleConn;
for (int j = 0; j < dr.ItemArray.Length; j++)
{
string parameterName = ":" + ds.Tables[0].Columns[j].ColumnName;
object value = dr.ItemArray[j];
oleCmd.Parameters.AddWithValue(parameterName, value);
if (j < dr.ItemArray.Length - 1)
{
colNames += ds.Tables[0].Columns[j].ColumnName + ",";
colValues += ":" + ds.Tables[0].Columns[j].ColumnName + ",";
}
else
{
colNames += ds.Tables[0].Columns[j].ColumnName + ")";
colValues += ":" + ds.Tables[0].Columns[j].ColumnName + ")";
}
}
cmdText = "insert into [CF$] " + colNames + " values " + colValues;
oleCmd.CommandText = cmdText;
oleCmd.ExecuteNonQuery();
}
#endregion
return true;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
return false;
}
/// <summary>
/// InitialDataSetToExcel By Fab
/// </summary>
/// <param name="connectionStr"></param>
/// <param name="ds"></param>
/// <param name="fab"></param>
/// <returns></returns>
public bool InitialDataSetToExcelByFab(string connectionStr, DataSet ds, string fab)
{
try
{
connectionString = connectionStr;
oleConn = new OleDbConnection(connectionString);
oleCmd = new OleDbCommand();
oleCmd.Connection = oleConn;
oleConn.Open();
int cCount = ds.Tables[0].Columns.Count;
int rCount = ds.Tables[0].Rows.Count;
#region Create table sheet for all
cmdText = "create table [" + fab + "] (";
for (int i = 0; i < cCount; i++)
{
if (i < cCount - 1)
{
cmdText += ds.Tables[0].Columns[i].ColumnName + " char(255),";
}
else
{
cmdText += ds.Tables[0].Columns[i].ColumnName + " char(255));";
}
}
oleCmd.CommandText = cmdText;
oleCmd.ExecuteNonQuery();
// Insert Data to Excel
for (int i = 0; i < rCount; i++)
{
cmdText = "";
string colNames = "(", colValues = "(";
// OleDbParameterCollection olePC = new OleDbParameterCollection();
DataRow dr = ds.Tables[0].Rows[i];
if (!CheckFab(fab, dr[0].ToString()))
continue;
oleCmd = new OleDbCommand();
oleCmd.Connection = oleConn;
for (int j = 0; j < dr.ItemArray.Length; j++)
{
string parameterName = ":" + ds.Tables[0].Columns[j].ColumnName;
object value = dr.ItemArray[j];
oleCmd.Parameters.AddWithValue(parameterName, value);
if (j < dr.ItemArray.Length - 1)
{
colNames += ds.Tables[0].Columns[j].ColumnName + ",";
colValues += ":" + ds.Tables[0].Columns[j].ColumnName + ",";
}
else
{
colNames += ds.Tables[0].Columns[j].ColumnName + ")";
colValues += ":" + ds.Tables[0].Columns[j].ColumnName + ")";
}
}
cmdText = "insert into [" + fab + "$] " + colNames + " values " + colValues;
oleCmd.CommandText = cmdText;
oleCmd.ExecuteNonQuery();
}
#endregion
return true;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
return false;
}
public bool CheckFab(string fab, string eqpID)
{
try
{
bool flag = false;
switch (fab)
{
case "CIM":
return true;
case "ARRAY":
flag = eqpID.StartsWith("1A");
break;
case "CELL":
flag = eqpID.StartsWith("1C");
break;
case "CF":
flag = eqpID.StartsWith("1F");
break;
}
return flag;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
return false;
}
/// <summary>
/// InitialDataSetToExcel
/// </summary>
/// <param name="connectionStr"></param>
/// <param name="ds"></param>
/// <returns></returns>
public bool InitialDataSetToExcel_backup(string connectionStr, DataSet ds)
{
try
{
connectionString = connectionStr;
oleConn = new OleDbConnection(connectionString);
oleCmd = new OleDbCommand();
oleCmd.Connection = oleConn;
oleConn.Open();
int cCount = ds.Tables[0].Columns.Count;
int rCount = ds.Tables[0].Rows.Count;
// Create table sheet
cmdText = "create table [HSMS_CONNECTION_HIS] (";
for (int i = 0; i < cCount; i++)
{
if (i < cCount - 1)
{
cmdText += ds.Tables[0].Columns[i].ColumnName + " char(255),";
}
else
{
cmdText += ds.Tables[0].Columns[i].ColumnName + " char(255));";
}
}
oleCmd.CommandText = cmdText;
oleCmd.ExecuteNonQuery();
// Insert Data to Excel
for (int i = 0; i < rCount; i++)
{
cmdText = "";
string colNames = "(", colValues = "(";
// OleDbParameterCollection olePC = new OleDbParameterCollection();
DataRow dr = ds.Tables[0].Rows[i];
oleCmd = new OleDbCommand();
oleCmd.Connection = oleConn;
for (int j = 0; j < dr.ItemArray.Length; j++)
{
string parameterName = ":" + ds.Tables[0].Columns[j].ColumnName;
object value = dr.ItemArray[j];
oleCmd.Parameters.AddWithValue(parameterName, value);
if (j < dr.ItemArray.Length - 1)
{
colNames += ds.Tables[0].Columns[j].ColumnName + ",";
colValues += ":" + ds.Tables[0].Columns[j].ColumnName + ",";
}
else
{
colNames += ds.Tables[0].Columns[j].ColumnName + ")";
colValues += ":" + ds.Tables[0].Columns[j].ColumnName + ")";
}
}
cmdText = "insert into [HSMS_CONNECTION_HIS$] " + colNames + " values " + colValues;
oleCmd.CommandText = cmdText;
oleCmd.ExecuteNonQuery();
}
return true;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
return false;
}
public void CloseOleConnection()
{
try
{
oleConn.Close();
oleConn.Dispose();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
}
}
}