DataSet Vs Exel

最新推荐文章于 2022-02-21 17:36:00 发布

xinsuanwencun

最新推荐文章于 2022-02-21 17:36:00 发布

阅读量321

点赞数

文章标签： dataset string insert exception table excel

本文链接：https://blog.csdn.net/xinsuanwencun/article/details/6149845

版权

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();

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);

                    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();

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);

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();

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);

                    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();

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);

                    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();

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);

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());
            }
        }
    }
}

xinsuanwencun

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
DataSet Vs Exel

 using System; using System.Collections.Generic; using System.Text; using System.Data.OleDb; using System.Data; namespace DataVsExel { public class DataVsExel { private string connectionStri
复制链接

扫一扫